insert 70 million rows & avoid long transaction error?

[WiccaChic]

Can someone give me some help? This weekend I will be copying 70 million rows from one table to another. Fore resons not worth going into, I cannot use HPL. I did some timing testing last weekend and found that I start getting long transaction errors at about 50 million rows.

Are there settings I can temporarily change to get around this long transaction issue? I am running IDS 9.40.
-thanks in advance

 

[vpshriyan]

Hi,

In a related subject thread thread179-809243 recently, you have informed us that you were to put the logging off during the insert. If you did turned it off, long transaction related issue may not propup. Do you need assistance to turn off the logging on objects?

I have written a FAQ on how to avoid long transaction in the FAQ area of this forum. Please have a look at:

Avoiding Long Transactions (LTX) faq179-2537

Regards,
Shriyan

 

[WiccaChic]

Hi vpshriyan,
As usual your info is top drawer, however I am not sure if it applies exactly to what I am doing. I am not loading a table from a flat file, I am doing an insert into a target table with a select statement pulling records out of another table. I cannot use dbload to do this I think?

Also, I do not have transaction logging on for this database, but the database does still log to some degree and I guess thats whats filling up my logical logs. I just need to set things to work around this one time insert and then I will be home free.

 

[vpshriyan]

Hi Wic,

>Also, I do not have transaction logging on for this database, but the database does still log to some degree and I guess thats whats filling up my logical logs.

Sorry, I can't bank on to your above statement. I disagree. If you have a non-logged database, it cannot generate the physical and logical logs. However, in your instance, if you have more than one databases, and if other databases are logged, it would be obvious, that those could be the one's who generating logs.

You may test the logging for a database with the following SQL:

select is_logging from sysmaster:sysdatabases where name='testdb' ;
result: 1 = logged, 0 = non logging.

I could give you two additional solutions:
1. While loading data from one table to another, specify a where clause to scale down the range of rows, or split it into multiple jobs.
2. Unload the source data into flat file, use dbload to execute massive insert job.

Regards,
Shriyan

 

[WiccaChic]

Once again, you are the man! I took a look in onmonitor and I see that all of the product databases - sysmaster, sysutils, sysuser, onpload - are set to unbuffered logging.

Would it be a bad thing if I turned logging off on these while I do my insert? The insert I ned to do is specific to a database that definitly is not logged (per onmonitor!) and thus my confusion over this!

 

[vpshriyan]

Hi Wic,

I would not suggest you to turn the logging off for the critical databases like sysmaster and sysutils. It would be better if you would leave them on their own.

Regards,
Shriyan