Why my request is aborted ?

[suze]

I have an error when i try this request :
[CODE]
Create table a as select * from b;
Commit;
[/ CODE]
Error
[CODE]
___ Wed Jul 29 4:23:45 2010 __________________________________________________ _
1> create table a as select * from b

E_US1262 Your transaction has been "aborted due to the transaction log file
HAVING Reached one of the limits set by the system administrator.
These limits are log_full, force_abort, and 90 percent of force_abort
When using the option to start fast_commit DBMS servers.
(Thu Jul 29 8:37:05 2010)


End of Request - Terminated by Errors
[/ CODE]

My transaction log have 2000 Mbyte of space
the b table have 2163487 rows
i dont have a dual transaction log .

[padst01]

How big is your table (volume ?). If it contains around 1.5 GB, there is a 'chance' this query fills up your transaction log!

You can have further information by using IPM / Logging System monitor. It will show how your log file is filling.

[denjo02]

You can avoid logging each row inserted into the new table by:

create table a as select * from b with nojournaling;

The table, of course, will not be journalled. If your database is journalled, then you would need to:

set journaling on a; (in an SQL session)

followed by "ckpdb DBNAME" to take a checkpoint of the database.

[kschendel]

As an addendum to what John said, starting with Ingres 10 you won't have to explicitly say "with nojournaling" if the database itself is not journaled. Ingres 10 will compile a bulk-load if the table is not being journaled at the time of execution, for any reason.

[suze]

thanks my friends !
where can i find the iifile to have the volume occuped by the b table ?

[padst01]

Hi Suze

Another way to estimate your table's size is to estimate row's length (for example using HELP), then multiply by the number of rows. Not really exact since this does not take headers and other stuff into account, but this will give you a rough idea.

Following denjo2 and Karl's comments, you should first decide whether you need logging for this operation or not. If it is absolutely required, then you'll have to size your log file accordingly.

Regards

[suze]

ah yes its a good idea !!

[ekar]

What about simply direct SQL session to not use logging (system) with:

commit;\g
set nologging;\g
create table a as select * from table b;\g
commit;\g
set logging;\g

That should avoid logging for the table creation.
I can hear Support saying - "Be careful when using it as it is not recommended."
But you should be OK.

Regards,
Eugene

[quida01]

“But you should be OK” well that is unless the session returns an error at which stage the database will be marked inconsistent and require recovery of some type.

If you are going to use ‘set nologging’ ensure that you have a current backup of the database taken prior to issuing the set statement.

I wouldn’t necessarily say do not use ‘set nologging’ but what I would say is be aware of the implications of what can go wrong when using this set statement and how to recover the database afterwards if the unthinkable does happen.

[denjo02]

Originally Posted by ekar

What about simply direct SQL session to not use logging
...
That should avoid logging for the table creation.
I can hear Support saying - "Be careful when using it as it is not recommended."

And with good reason. If you use "set nologging", even for something as simple as this, you cannot rollforward a journalled database.

If you do feel the need to use a nologging query, the only sensible way to do it is to take a checkpoint immediately before issuing the command, run the nologging session, and then take another checkpoint. Otherwise you can't recover from journals.

(Note, you can use the #f flag on the rollforwarddb, but obviously any updates made to the table created with nologging cannot be processed, as the table won't exist. Your database will be marked inconsistent)

Much safer to use "with nojournaling" when creating the table.