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.
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!