long transaction - raw table

IDS 11.70.fc3xc Aix 6.1 I have a large table that I'm trying to load ... about 1.8B rows ... the table has no indexes ... is fragmented over 44 dbspaces ... altered to raw .. While trying to insert into the table ... insert into table_1 select * from database2@instance2:table_2; At differing intervals I have hit a long transaction .. ie. -458. I'm confused as to why my raw table is getting this ... happend one time at 500M rows ... another time at 186M rows. Lots of other stuff going on the server ... but nothing else getting long transactions ... Peter Logan Senior Database Administrator Phone: 616/878-8309

Did you do a begin work or is the database in ANSI logging mode which would imply a begin work? If so that's the problem. Long transaction has less to do with the volume of log records than it does on the percent of logs used by other sessions since the log entry for the oldest begin work that was not committed or rolled back. Solutions: 1. Make sure there is no BEGIN WORK in the session. 2. Use the new dbmove.ec utility in my utils2_ak package. It will commit every few thousand rows (configurable) eliminating the possibility of long transactions. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 7, 2013 at 2:58 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.fc3xc > Aix 6.1 > > I have a large table that I'm trying to load ... about 1.8B rows ... the > table has no indexes ... is fragmented over 44 dbspaces ... altered to > raw .. > > While trying to insert into the table ... > > insert into table_1 > select * from database2@instance2:table_2;> > At differing intervals I have hit a long transaction .. ie. -458. > > I'm confused as to why my raw table is getting this ... happend one time > at 500M rows ... another time at 186M rows. Lots of other stuff going on > the server ... but nothing else getting long transactions ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c29b4463318504e82b632b

Original post: IDS 11.70.fc3xc Aix 6.1 I have a large table that I'm trying to load ... about 1.8B rows ... the table has no indexes ... is fragmented over 44 dbspaces ... altered to raw .. While trying to insert into the table ... insert into table_1 select * from database2@instance2:table_2; At differing intervals I have hit a long transaction .. ie. -458. I'm confused as to why my raw table is getting this ... happend one time at 500M rows ... another time at 186M rows. Lots of other stuff going on the server ... but nothing else getting long transactions ... Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: Well, even if table_1 isn't logged, I suspect that since it's in a logged database, there is still a transaction that's getting created for the insert. So if there's a transaction getting started, it's going to have a begin log position. I suspect that this transaction is open, and with all the other activity you are doing on the system, you are filling your logs at various rates, and eventually this transaction that is open ends up spanning your long transaction highwater mark. Thus it gets rolled back, even though it isn't generating log records itself. I didn't test this before posting, but I'd guess that's what's happening. You could check this yourself looking at onstat -u and onstat -x output while you insert is running. You should be able to find the session doing the insert and if it's opened a transaction and what the log begin position is for it and compare that to the log that is active when it encounters the long transaction rollback. Jacques Renaut IBM Informix Advanced Support APD Team

table extent allocation is always logged, even if the table is raw. |------------> | From: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |"Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com> = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | To: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids@iiug.org, = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Date: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |10/07/2013 12:58 PM = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Subject: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |long transaction - raw table [31639] = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |------------> | Sent by: | |------------> >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| |ids-bounces@iiug.org = = | >--------------------------------------------------------------------= -----------------------------------------------------------------------= -------| IDS 11.70.fc3xc Aix 6.1 I have a large table that I'm trying to load ... about 1.8B rows ... th= e table has no indexes ... is fragmented over 44 dbspaces ... altered to raw .. While trying to insert into the table ... insert into table_1 select * from database2@instance2:table_2; At differing intervals I have hit a long transaction .. ie. -458. I'm confused as to why my raw table is getting this ... happend one tim= e at 500M rows ... another time at 186M rows. Lots of other stuff going o= n the server ... but nothing else getting long transactions ... Peter Logan Senior Database Administrator Phone: 616/878-8309 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

ok .. thanks ... no begin work ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org, Date: 10/07/2013 03:09 PM Subject: Re: long transaction - raw table [31640] Sent by: ids-bounces@iiug.org Did you do a begin work or is the database in ANSI logging mode which would imply a begin work? If so that's the problem. Long transaction has less to do with the volume of log records than it does on the percent of logs used by other sessions since the log entry for the oldest begin work that was not committed or rolled back. Solutions: 1. Make sure there is no BEGIN WORK in the session. 2. Use the new dbmove.ec utility in my utils2_ak package. It will commit every few thousand rows (configurable) eliminating the possibility of long transactions. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 7, 2013 at 2:58 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.fc3xc > Aix 6.1 > > I have a large table that I'm trying to load ... about 1.8B rows ... the > table has no indexes ... is fragmented over 44 dbspaces ... altered to > raw .. > > While trying to insert into the table ... > > insert into table_1 > select * from database2@instance2:table_2;> > At differing intervals I have hit a long transaction .. ie. -458. > > I'm confused as to why my raw table is getting this ... happend one time > at 500M rows ... another time at 186M rows. Lots of other stuff going on > the server ... but nothing else getting long transactions ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c29b4463318504e82b632b ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Ya, however, after I wrote that, I realized that even without ANSI mode, the INSERT statement itself is considered a standalone transaction and so does an implied BEGIN WORK and that's what's causing the problem. Another option would be to add lots more logical logs so the other activity doesn't use then past the LTXHWM level during the copy. My rule of thumb is that you should have enough lots to get through 4 days without having to back up the logs. If you have that much log space then it's unlikely that a data copy to a RAW table will cause a long transaction rollback unless it runs for more than 48 hours or your LTXHWM is set too high. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 7, 2013 at 3:42 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > ok .. thanks ... no begin work ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org, > Date: 10/07/2013 03:09 PM > Subject: Re: long transaction - raw table [31640] > Sent by: ids-bounces@iiug.org > > Did you do a begin work or is the database in ANSI logging mode which > would > imply a begin work? If so that's the problem. Long transaction has less > to do with the volume of log records than it does on the percent of logs > used by other sessions since the log entry for the oldest begin work that > was not committed or rolled back. > > Solutions: > > 1. Make sure there is no BEGIN WORK in the session. > 2. Use the new dbmove.ec utility in my utils2_ak package. It will commit > every few thousand rows (configurable) eliminating the possibility of long > > transactions. > > Art > > Art S. Kagel, Principal Consultant > > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated > nor > those of the entities themselves. > > On Mon, Oct 7, 2013 at 2:58 PM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > IDS 11.70.fc3xc > > Aix 6.1 > > > > I have a large table that I'm trying to load ... about 1.8B rows ... the > > > table has no indexes ... is fragmented over 44 dbspaces ... altered to > > raw .. > > > > While trying to insert into the table ... > > > > insert into table_1 > > select * from database2@instance2:table_2;> > > > At differing intervals I have hit a long transaction .. ie. -458. > > > > I'm confused as to why my raw table is getting this ... happend one time > > > at 500M rows ... another time at 186M rows. Lots of other stuff going on > > > the server ... but nothing else getting long transactions ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c29b4463318504e82b632b > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3836c637dab04e82d05df