Transaction log when populating a raw table with rowids?
Bispo on Informix 7.31.FD9/AIX hit a long transaction copying a 600-million-row table into a round-robin fragmented RAW table with rowids via insert into select. Jack Parker noted extent allocation is logged even for raw tables, but 22 allocations should not cause it. Surfer! pointed to the long-transaction documentation. Cause not established.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, everybody. I got an 'aborting long transaction' when copying a 64GB non fragmented table (600 million records) to a RAW table fragmented by round-robin over three dbspaces, with rowids. I used a "insert into table1 select * from table2" statement. I would like to know the reason for the long transaction. Is it because the system-rowid index ? I am using informix 7.31 FD9 with AIX 5.3 64 bits. Thanks in advance.
Was the table sized appropriately? Table extent allocation is logged - even for raw tables. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Bispo Sent: Thursday, December 03, 2009 7:13 AM To: informix-list@iiug.org Cc: eduardo.bispo@sky.com.br Subject: Transaction log when populating a raw table with rowids? Hi, everybody. I got an 'aborting long transaction' when copying a 64GB non fragmented table (600 million records) to a RAW table fragmented by round-robin over three dbspaces, with rowids. I used a "insert into table1 select * from table2" statement. I would like to know the reason for the long transaction. Is it because the system-rowid index ? I am using informix 7.31 FD9 with AIX 5.3 64 bits. Thanks in advance. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On 3 dez, 10:50, "Jack Parker" <jack.park...@verizon.net> wrote: > Was the table sized appropriately? Table extent allocation is logged - even > for raw tables. > > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > -----Original Message----- > From: informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Bispo > Sent: Thursday, December 03, 2009 7:13 AM > To: informix-l...@iiug.org > Cc: eduardo.bi...@sky.com.br > Subject: Transaction log when populating a raw table with rowids? > > Hi, everybody. > I got an 'aborting long transaction' when copying a 64GB non > fragmented table (600 million records) to a RAW table fragmented by > round-robin over three dbspaces, with rowids. I used a "insert into > table1 select * from table2" statement. I would like to know the > reason for the long transaction. Is it because the system-rowid > index ? I am using informix 7.31 FD9 with AIX 5.3 64 bits. > > Thanks in advance. > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list Thanks for the insight, Jack. The first extent was set to 2GB (informix 7 allocation limit) and the next was set to 512 MB. When the long transaction occurred the table was aprox. 13GB and 180 million inserted rows. I did not notice the number of extents at this point and table was dropped after that. Since I was not using explicit log transaction (begin work) table extent allocation should be commited right after completion, right ? Best Regards
Right after completion of the entire insert statement, not after each allocation. But 22 allocations is not enough to cause a long transaction. Your first thought is probably better. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Bispo Sent: Thursday, December 03, 2009 8:10 AM To: informix-list@iiug.org Subject: Re: Transaction log when populating a raw table with rowids? On 3 dez, 10:50, "Jack Parker" <jack.park...@verizon.net> wrote: > Was the table sized appropriately? Table extent allocation is logged - even > for raw tables. > > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > -----Original Message----- > From: informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Bispo > Sent: Thursday, December 03, 2009 7:13 AM > To: informix-l...@iiug.org > Cc: eduardo.bi...@sky.com.br > Subject: Transaction log when populating a raw table with rowids? > > Hi, everybody. > I got an 'aborting long transaction' when copying a 64GB non > fragmented table (600 million records) to a RAW table fragmented by > round-robin over three dbspaces, with rowids. I used a "insert into > table1 select * from table2" statement. I would like to know the > reason for the long transaction. Is it because the system-rowid > index ? I am using informix 7.31 FD9 with AIX 5.3 64 bits. > > Thanks in advance. > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list Thanks for the insight, Jack. The first extent was set to 2GB (informix 7 allocation limit) and the next was set to 512 MB. When the long transaction occurred the table was aprox. 13GB and 180 million inserted rows. I did not notice the number of extents at this point and table was dropped after that. Since I was not using explicit log transaction (begin work) table extent allocation should be commited right after completion, right ? Best Regards _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
In message <a765c415-af45-47bd-976a-9313f155cb79@p32g2000vbi.googlegroups.com>, Bispo <ealvesbispo@gmail.com> writes >On 3 dez, 10:50, "Jack Parker" <jack.park...@verizon.net> wrote: >> Was the table sized appropriately? 'Table extent allocation is logged - even >> for raw tables. >> >> j. >> >> Sane ego te vocavi. Forsitan capedictum tuum desit. >> >> -----Original Message----- >> From: informix-list-boun...@iiug.org >> >> [mailto:informix-list-boun...@iiug.org]On Behalf Of Bispo >> Sent: Thursday, December 03, 2009 7:13 AM >> To: informix-l...@iiug.org >> Cc: eduardo.bi...@sky.com.br >> Subject: Transaction log when populating a raw table with rowids? >> >> Hi, everybody. >> I got an 'aborting long transaction' when copying a 64GB non >> fragmented table (600 million records) to a RAW table fragmented by >> round-robin over three dbspaces, with rowids. I used a "insert into >> table1 select * from table2" statement. I would like to know the >> reason for the long transaction. Is it because the system-rowid >> index ? I am using informix 7.31 FD9 with AIX 5.3 64 bits. >> >> Thanks in advance. >> _______________________________________________ >> Informix-list mailing list >> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > >Thanks for the insight, Jack. > >The first extent was set to 2GB (informix 7 allocation limit) and the >next was set to 512 MB. When the long transaction occurred the table >was aprox. 13GB and 180 million inserted rows. I did not notice the >number of extents at this point and table was dropped after that. >Since I was not using explicit log transaction (begin work) table >extent allocation should be commited right after completion, right ? > >Best Regards Have you checked the documentation for what causes long transaction problems? Suspect you will find your answer there. http://www-01.ibm.com/support/docview.wss?uid=swg21177608 Also I presume your three dbspaces you are fragmenting over are all the same size, or almost the same. -- Surfer!