Long transaction aborted in alter table

H, I trying to add a column to a table but I get the long transaction aborted, what can I do? Thanks in advance.

Ran into that yesterday. Unloaded the table, dropped, recreated it and reloaded it. j. > On Mar 3, 2018, at 11:37 AM, jorge valenzuela <jorgervt@gmail.com> wrote: > > H, > I trying to add a column to a table but I get the long transaction aborted, > what can I do? > Thanks in advance. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

This will happen when you're doing a "slow alter" and all the pages of the table need to be rewritten. You could try changing the table to RAW (as long as you are not replicating) and trying again, but you will need to make sure that you have plenty of room in the dbspace for a copy of the table to be stored while the change is made. You could add also add more logs, but this might be more trouble than it's worth. As Jack already recommended, the easiest thing to do is likely to unload the table, recreate the table with the updated definition and reload it. If you are adding or dropping a column, then I would suggest unloading the table in the NEW format (e.g. with a blank value in the position of the new column) so that you can load it back in easily. Look at using external tables for the unload for the best performance. During the load, to avoid long transaction errors again, load the table as RAW or use a utility like dbload so that it commits regularly. Also leave off indexes and constraints until the table is loaded and recreate them after. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jorge valenzuela Sent: Saturday, March 03, 2018 9:38 AM To: ids@iiug.org Subject: Long transaction aborted in alter table [40777] H, I trying to add a column to a table but I get the long transaction aborted, what can I do? Thanks in advance. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

I think I will unload the table, will recreate and load again. In my case I will only add a char(1) column with default value of "D", so, the instrucción will be unload to filename select * from table, "D" ?Thanks in advance. > El 03/03/2018, a las 10:36, Mike Walker <mike@advancedatatools.com> escribió: > > This will happen when you're doing a "slow alter" and all the pages of the > table need to be rewritten. > > You could try changing the table to RAW (as long as you are not replicating) > and trying again, but you will need to make sure that you have plenty of > room in the dbspace for a copy of the table to be stored while the change is > made. > > You could add also add more logs, but this might be more trouble than it's > worth. > > As Jack already recommended, the easiest thing to do is likely to unload the > table, recreate the table with the updated definition and reload it. If you > are adding or dropping a column, then I would suggest unloading the table in > the NEW format (e.g. with a blank value in the position of the new column) > so that you can load it back in easily. Look at using external tables for > the unload for the best performance. During the load, to avoid long > transaction errors again, load the table as RAW or use a utility like dbload > so that it commits regularly. Also leave off indexes and constraints until > the table is loaded and recreate them after. > > Mike > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jorge > valenzuela > Sent: Saturday, March 03, 2018 9:38 AM > To: ids@iiug.org > Subject: Long transaction aborted in alter table [40777] > > H, > I trying to add a column to a table but I get the long transaction aborted, > what can I do? > Thanks in advance. > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Really, use an external table as Mike suggests - especially if it=E2=80=99= s a large table. (I love the detail Mike put into his answer). I = differ from Mike in that I think it=E2=80=99s easier to add the column = on the reload: create external table <table>_ext sameas <table> using ( datafiles ( "DISK:/<path>/<table>1.unl", "DISK:/<path>/<table>2.unl", =E2=80=A6.. "DISK:/<path>/<table>32.unl", ), format "delimited", escape on, express ); set pdqpriority 10; =E2=80=94 Important, you want to take advantage of =PDQ for the unload! This is the turbo button. insert into <table>_ext select * from <table>; (drop and recreate your table with the new column =E2=80=94 create raw = table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); =E2=80=94 Important to create the table as raw is you want optimal load = performance. insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; Alter table <table> type (standard); create your indexes now. Do a level 0 backup (even if it=E2=80=99s to /dev/null) How many files you choose to use for the external table is up to you. I = use a multiple or factor of the #cpus. This is waaaay faster than an unload statement. cheers j. > On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = wrote: >=20 > I think I will unload the table, will recreate and load again.=20 > In my case I will only add a char(1) column with default value of "D", = so, the=20 > instrucci=C3=B3n will be=20 >=20 > unload to filename=20 > select * from table, "D" ?=20> Thanks in advance.=20 >=20 >> El 03/03/2018, a las 10:36, Mike Walker <mike@advancedatatools.com>=20= > escribi=C3=B3:=20 >>=20 >> This will happen when you're doing a "slow alter" and all the pages = of the=20 >> table need to be rewritten.=20 >>=20 >> You could try changing the table to RAW (as long as you are not = replicating)=20 >> and trying again, but you will need to make sure that you have plenty = of=20 >> room in the dbspace for a copy of the table to be stored while the = change is=20 >> made.=20 >>=20 >> You could add also add more logs, but this might be more trouble than = it's=20 >> worth.=20 >>=20 >> As Jack already recommended, the easiest thing to do is likely to = unload the=20 >> table, recreate the table with the updated definition and reload it. = If you=20 >> are adding or dropping a column, then I would suggest unloading the = table in=20 >> the NEW format (e.g. with a blank value in the position of the new = column)=20 >> so that you can load it back in easily. Look at using external tables = for=20 >> the unload for the best performance. During the load, to avoid long=20= >> transaction errors again, load the table as RAW or use a utility like = dbload=20 >> so that it commits regularly. Also leave off indexes and constraints = until=20 >> the table is loaded and recreate them after.=20 >>=20 >> Mike=20 >>=20 >> -----Original Message-----=20 >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = jorge=20 >> valenzuela=20 >> Sent: Saturday, March 03, 2018 9:38 AM=20 >> To: ids@iiug.org=20 >> Subject: Long transaction aborted in alter table [40777]=20 >>=20 >> H,=20 >> I trying to add a column to a table but I get the long transaction = aborted,=20 >> what can I do?=20 >> Thanks in advance.=20 >>=20 >> = **************************************************************************= **=20 >> ***=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Why the hell doesnt email format properly? set pdqpriority 10; for the unload, this is the turbo button. create raw table <table> () raw is important if you want optimal load performance. ... insert into <table> select *, <literal value> from <table>_ext; do a level 0 backup, even if it is to /dev/null. Hope that solves some of the mysteries that my email agent introduced. cheers j. > On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: > > Really, use an external table as Mike suggests - especially if it=E2=80=99= > s a large table. (I love the detail Mike put into his answer). I = > differ from Mike in that I think it=E2=80=99s easier to add the column = > on the reload: > > create external table <table>_ext sameas <table> > using > ( > > datafiles > > ( > > "DISK:/<path>/<table>1.unl", > > "DISK:/<path>/<table>2.unl", > > =E2=80=A6.. > > "DISK:/<path>/<table>32.unl", > > ), > > format "delimited", > > escape on, > > express > ); > > set pdqpriority 10; =E2=80=94 Important, you want to take advantage of => PDQ for the unload! This is the turbo button. > insert into <table>_ext select * from <table>; > > (drop and recreate your table with the new column =E2=80=94 create raw = > table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); > =E2=80=94 Important to create the table as raw is you want optimal load = > performance. > > insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; > > Alter table <table> type (standard); > create your indexes now. > Do a level 0 backup (even if it=E2=80=99s to /dev/null) > > How many files you choose to use for the external table is up to you. I = > use a multiple or factor of the #cpus. > > This is waaaay faster than an unload statement. > > cheers > j. > >> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = > wrote: >> =20 >> I think I will unload the table, will recreate and load again.=20 >> In my case I will only add a char(1) column with default value of "D", = > so, the=20 >> instrucci=C3=B3n will be=20 >> =20 >> unload to filename=20 >> select * from table, "D" ?=20>> Thanks in advance.=20 >> =20 >>> El 03/03/2018, a las 10:36, Mike Walker <mike@advancedatatools.com>=20= > >> escribi=C3=B3:=20 >>> =20 >>> This will happen when you're doing a "slow alter" and all the pages = > of the=20 >>> table need to be rewritten.=20 >>> =20 >>> You could try changing the table to RAW (as long as you are not = > replicating)=20 >>> and trying again, but you will need to make sure that you have plenty = > of=20 >>> room in the dbspace for a copy of the table to be stored while the = > change is=20 >>> made.=20 >>> =20 >>> You could add also add more logs, but this might be more trouble than = > it's=20 >>> worth.=20 >>> =20 >>> As Jack already recommended, the easiest thing to do is likely to = > unload the=20 >>> table, recreate the table with the updated definition and reload it. = > If you=20 >>> are adding or dropping a column, then I would suggest unloading the = > table in=20 >>> the NEW format (e.g. with a blank value in the position of the new = > column)=20 >>> so that you can load it back in easily. Look at using external tables = > for=20 >>> the unload for the best performance. During the load, to avoid long=20= > >>> transaction errors again, load the table as RAW or use a utility like = > dbload=20 >>> so that it commits regularly. Also leave off indexes and constraints = > until=20 >>> the table is loaded and recreate them after.=20 >>> =20 >>> Mike=20 >>> =20 >>> -----Original Message-----=20 >>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = > jorge=20 >>> valenzuela=20 >>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>> To: ids@iiug.org=20 >>> Subject: Long transaction aborted in alter table [40777]=20 >>> =20 >>> H,=20 >>> I trying to add a column to a table but I get the long transaction = > aborted,=20 >>> what can I do?=20 >>> Thanks in advance.=20 >>> =20 >>> = > **************************************************************************= > **=20 >>> ***=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>> =20 >>> =20 >>> =20 >> = > **************************************************************************= > *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>> =20 >> =20 >> =20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >> =20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks. > El 03/03/2018, a las 16:12, Jack Parker <jack.parker4@verizon.net> escribió: > > Why the hell doesnt email format properly? > > set pdqpriority 10; for the unload, this is the turbo button.> > create raw table <table> () > raw is important if you want optimal load performance. > > .... > > insert into <table> select *, <literal value> from <table>_ext; > > do a level 0 backup, even if it is to /dev/null. > > Hope that solves some of the mysteries that my email agent introduced. > > cheers > j. > >> On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: >> >> Really, use an external table as Mike suggests - especially if it=E2=80=99= >> s a large table. (I love the detail Mike put into his answer). I = >> differ from Mike in that I think it=E2=80=99s easier to add the column = >> on the reload: >> >> create external table <table>_ext sameas <table> >> using >> ( >> >> datafiles >> >> ( >> >> "DISK:/<path>/<table>1.unl", >> >> "DISK:/<path>/<table>2.unl", >> >> =E2=80=A6.. >> >> "DISK:/<path>/<table>32.unl", >> >> ), >> >> format "delimited", >> >> escape on, >> >> express >> ); >> >> set pdqpriority 10; =E2=80=94 Important, you want to take advantage of =>> PDQ for the unload! This is the turbo button. >> insert into <table>_ext select * from <table>; >> >> (drop and recreate your table with the new column =E2=80=94 create raw = >> table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); >> =E2=80=94 Important to create the table as raw is you want optimal load = >> performance. >> >> insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; >> >> Alter table <table> type (standard); >> create your indexes now. >> Do a level 0 backup (even if it=E2=80=99s to /dev/null) >> >> How many files you choose to use for the external table is up to you. I = >> use a multiple or factor of the #cpus. >> >> This is waaaay faster than an unload statement. >> >> cheers >> j. >> >>> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = >> wrote: >>> =20 >>> I think I will unload the table, will recreate and load again.=20 >>> In my case I will only add a char(1) column with default value of "D", = >> so, the=20 >>> instrucci=C3=B3n will be=20 >>> =20 >>> unload to filename=20 >>> select * from table, "D" ?=20>>> Thanks in advance.=20 >>> =20 >>>> El 03/03/2018, a las 10:36, Mike Walker <mike@advancedatatools.com>=20= >> >>> escribi=C3=B3:=20 >>>> =20 >>>> This will happen when you're doing a "slow alter" and all the pages = >> of the=20 >>>> table need to be rewritten.=20 >>>> =20 >>>> You could try changing the table to RAW (as long as you are not = >> replicating)=20 >>>> and trying again, but you will need to make sure that you have plenty = >> of=20 >>>> room in the dbspace for a copy of the table to be stored while the = >> change is=20 >>>> made.=20 >>>> =20 >>>> You could add also add more logs, but this might be more trouble than = >> it's=20 >>>> worth.=20 >>>> =20 >>>> As Jack already recommended, the easiest thing to do is likely to = >> unload the=20 >>>> table, recreate the table with the updated definition and reload it. = >> If you=20 >>>> are adding or dropping a column, then I would suggest unloading the = >> table in=20 >>>> the NEW format (e.g. with a blank value in the position of the new = >> column)=20 >>>> so that you can load it back in easily. Look at using external tables = >> for=20 >>>> the unload for the best performance. During the load, to avoid long=20= >> >>>> transaction errors again, load the table as RAW or use a utility like = >> dbload=20 >>>> so that it commits regularly. Also leave off indexes and constraints = >> until=20 >>>> the table is loaded and recreate them after.=20 >>>> =20 >>>> Mike=20 >>>> =20 >>>> -----Original Message-----=20 >>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = >> jorge=20 >>>> valenzuela=20 >>>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>>> To: ids@iiug.org=20 >>>> Subject: Long transaction aborted in alter table [40777]=20 >>>> =20 >>>> H,=20 >>>> I trying to add a column to a table but I get the long transaction = >> aborted,=20 >>>> what can I do?=20 >>>> Thanks in advance.=20 >>>> =20 >>>> = >> **************************************************************************= >> **=20 >>>> ***=20 >>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>>> =20 >>>> =20 >>>> =20 >>> = >> **************************************************************************= >> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>>> =20 >>> =20 >>> =20 >>> = >> **************************************************************************= >> *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>> =20 >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks > El 03/03/2018, a las 08:42, Jack Parker <jack.parker4@verizon.net> escribió: > > Ran into that yesterday. Unloaded the table, dropped, recreated it and > reloaded it. > > j. > >> On Mar 3, 2018, at 11:37 AM, jorge valenzuela <jorgervt@gmail.com> wrote: >> >> H, >> I trying to add a column to a table but I get the long transaction aborted, >> what can I do? >> Thanks in advance. >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

If I delete a lot of records, can I update the table? > El 03/03/2018, a las 16:12, Jack Parker <jack.parker4@verizon.net> escribió: > > Why the hell doesnt email format properly? > > set pdqpriority 10; for the unload, this is the turbo button.> > create raw table <table> () > raw is important if you want optimal load performance. > > .... > > insert into <table> select *, <literal value> from <table>_ext; > > do a level 0 backup, even if it is to /dev/null. > > Hope that solves some of the mysteries that my email agent introduced. > > cheers > j. > >> On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: >> >> Really, use an external table as Mike suggests - especially if it=E2=80=99= >> s a large table. (I love the detail Mike put into his answer). I = >> differ from Mike in that I think it=E2=80=99s easier to add the column = >> on the reload: >> >> create external table <table>_ext sameas <table> >> using >> ( >> >> datafiles >> >> ( >> >> "DISK:/<path>/<table>1.unl", >> >> "DISK:/<path>/<table>2.unl", >> >> =E2=80=A6.. >> >> "DISK:/<path>/<table>32.unl", >> >> ), >> >> format "delimited", >> >> escape on, >> >> express >> ); >> >> set pdqpriority 10; =E2=80=94 Important, you want to take advantage of =>> PDQ for the unload! This is the turbo button. >> insert into <table>_ext select * from <table>; >> >> (drop and recreate your table with the new column =E2=80=94 create raw = >> table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); >> =E2=80=94 Important to create the table as raw is you want optimal load = >> performance. >> >> insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; >> >> Alter table <table> type (standard); >> create your indexes now. >> Do a level 0 backup (even if it=E2=80=99s to /dev/null) >> >> How many files you choose to use for the external table is up to you. I = >> use a multiple or factor of the #cpus. >> >> This is waaaay faster than an unload statement. >> >> cheers >> j. >> >>> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = >> wrote: >>> =20 >>> I think I will unload the table, will recreate and load again.=20 >>> In my case I will only add a char(1) column with default value of "D", = >> so, the=20 >>> instrucci=C3=B3n will be=20 >>> =20 >>> unload to filename=20 >>> select * from table, "D" ?=20>>> Thanks in advance.=20 >>> =20 >>>> El 03/03/2018, a las 10:36, Mike Walker <mike@advancedatatools.com>=20= >> >>> escribi=C3=B3:=20 >>>> =20 >>>> This will happen when you're doing a "slow alter" and all the pages = >> of the=20 >>>> table need to be rewritten.=20 >>>> =20 >>>> You could try changing the table to RAW (as long as you are not = >> replicating)=20 >>>> and trying again, but you will need to make sure that you have plenty = >> of=20 >>>> room in the dbspace for a copy of the table to be stored while the = >> change is=20 >>>> made.=20 >>>> =20 >>>> You could add also add more logs, but this might be more trouble than = >> it's=20 >>>> worth.=20 >>>> =20 >>>> As Jack already recommended, the easiest thing to do is likely to = >> unload the=20 >>>> table, recreate the table with the updated definition and reload it. = >> If you=20 >>>> are adding or dropping a column, then I would suggest unloading the = >> table in=20 >>>> the NEW format (e.g. with a blank value in the position of the new = >> column)=20 >>>> so that you can load it back in easily. Look at using external tables = >> for=20 >>>> the unload for the best performance. During the load, to avoid long=20= >> >>>> transaction errors again, load the table as RAW or use a utility like = >> dbload=20 >>>> so that it commits regularly. Also leave off indexes and constraints = >> until=20 >>>> the table is loaded and recreate them after.=20 >>>> =20 >>>> Mike=20 >>>> =20 >>>> -----Original Message-----=20 >>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = >> jorge=20 >>>> valenzuela=20 >>>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>>> To: ids@iiug.org=20 >>>> Subject: Long transaction aborted in alter table [40777]=20 >>>> =20 >>>> H,=20 >>>> I trying to add a column to a table but I get the long transaction = >> aborted,=20 >>>> what can I do?=20 >>>> Thanks in advance.=20 >>>> =20 >>>> = >> **************************************************************************= >> **=20 >>>> ***=20 >>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>>> =20 >>>> =20 >>>> =20 >>> = >> **************************************************************************= >> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>>> =20 >>> =20 >>> =20 >>> = >> **************************************************************************= >> *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>> =20 >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Do you mean can you get away with ALTERING the table if you delete a lot of records? The smaller the table, the smaller the transaction will be when you alter the table and the more likely it will be that the alter will complete. But whether the alter will succeed depends on many factors - the size of the table, the number of logs, the size of the logs, other things running at the time, duration of the transaction, whether there are indexes on the table, etc...so it's not really possible to say. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jorge valenzuela Sent: Sunday, March 04, 2018 1:28 PM To: ids@iiug.org Subject: Re: Long transaction aborted in alter table [40785] If I delete a lot of records, can I update the table? > El 03/03/2018, a las 16:12, Jack Parker <jack.parker4@verizon.net> escribió: > > Why the hell doesnt email format properly? > > set pdqpriority 10; for the unload, this is the turbo button.> > create raw table <table> () > raw is important if you want optimal load performance. > > .... > > insert into <table> select *, <literal value> from <table>_ext; > > do a level 0 backup, even if it is to /dev/null. > > Hope that solves some of the mysteries that my email agent introduced. > > cheers > j. > >> On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: >> >> Really, use an external table as Mike suggests - especially if >> it=E2=80=99= s a large table. (I love the detail Mike put into his >> answer). I = differ from Mike in that I think it=E2=80=99s easier to >> add the column = on the reload: >> >> create external table <table>_ext sameas <table> using ( >> >> datafiles >> >> ( >> >> "DISK:/<path>/<table>1.unl", >> >> "DISK:/<path>/<table>2.unl", >> >> =E2=80=A6.. >> >> "DISK:/<path>/<table>32.unl", >> >> ), >> >> format "delimited", >> >> escape on, >> >> express >> ); >> >> set pdqpriority 10; =E2=80=94 Important, you want to take advantage>> of = PDQ for the unload! This is the turbo button. >> insert into <table>_ext select * from <table>; >> >> (drop and recreate your table with the new column =E2=80=94 create >> raw = table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); >> =E2=80=94 Important to create the table as raw is you want optimal >> load = performance. >> >> insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; >> >> Alter table <table> type (standard); create your indexes now. >> Do a level 0 backup (even if it=E2=80=99s to /dev/null) >> >> How many files you choose to use for the external table is up to you. >> I = use a multiple or factor of the #cpus. >> >> This is waaaay faster than an unload statement. >> >> cheers >> j. >> >>> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = >> wrote: >>> =20 >>> I think I will unload the table, will recreate and load again.=20 In >>> my case I will only add a char(1) column with default value of "D", >>> = >> so, the=20 >>> instrucci=C3=B3n will be=20 >>> =20 >>> unload to filename=20 >>> select * from table, "D" ?=20>>> Thanks in advance.=20 >>> =20 >>>> El 03/03/2018, a las 10:36, Mike Walker >>>> <mike@advancedatatools.com>=20= >> >>> escribi=C3=B3:=20 >>>> =20 >>>> This will happen when you're doing a "slow alter" and all the pages >>>> = >> of the=20 >>>> table need to be rewritten.=20 >>>> =20 >>>> You could try changing the table to RAW (as long as you are not = >> replicating)=20 >>>> and trying again, but you will need to make sure that you have >>>> plenty = >> of=20 >>>> room in the dbspace for a copy of the table to be stored while the >>>> = >> change is=20 >>>> made.=20 >>>> =20 >>>> You could add also add more logs, but this might be more trouble >>>> than = >> it's=20 >>>> worth.=20 >>>> =20 >>>> As Jack already recommended, the easiest thing to do is likely to = >> unload the=20 >>>> table, recreate the table with the updated definition and reload >>>> it. = >> If you=20 >>>> are adding or dropping a column, then I would suggest unloading the >>>> = >> table in=20 >>>> the NEW format (e.g. with a blank value in the position of the new >>>> = >> column)=20 >>>> so that you can load it back in easily. Look at using external >>>> tables = >> for=20 >>>> the unload for the best performance. During the load, to avoid >>>> long=20= >> >>>> transaction errors again, load the table as RAW or use a utility >>>> like = >> dbload=20 >>>> so that it commits regularly. Also leave off indexes and >>>> constraints = >> until=20 >>>> the table is loaded and recreate them after.=20 >>>> =20 >>>> Mike=20 >>>> =20 >>>> -----Original Message-----=20 >>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf >>>> Of = >> jorge=20 >>>> valenzuela=20 >>>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>>> To: ids@iiug.org=20 >>>> Subject: Long transaction aborted in alter table [40777]=20 >>>> =20 >>>> H,=20 >>>> I trying to add a column to a table but I get the long transaction >>>> = >> aborted,=20 >>>> what can I do?=20 >>>> Thanks in advance.=20 >>>> =20 >>>> = >> ********************************************************************* >> *****= >> **=20 >>>> ***=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= >> >>>> =20 >>>> =20 >>>> =20 >>> = >> ********************************************************************* >> *****= >> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= >> >>>> =20 >>> =20 >>> =20 >>> = >> ********************************************************************* >> *****= >> *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion >>> forum.=20= >> >>> =20 >> >> >> > **************************************************************************** *** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Yes > El 04/03/2018, a las 12:37, Mike Walker <mike@advancedatatools.com> escribió: > > Do you mean can you get away with ALTERING the table if you delete a lot of > records? > > The smaller the table, the smaller the transaction will be when you alter > the table and the more likely it will be that the alter will complete. But > whether the alter will succeed depends on many factors - the size of the > table, the number of logs, the size of the logs, other things running at the > time, duration of the transaction, whether there are indexes on the table, > etc...so it's not really possible to say. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jorge > valenzuela > Sent: Sunday, March 04, 2018 1:28 PM > To: ids@iiug.org > Subject: Re: Long transaction aborted in alter table [40785] > > If I delete a lot of records, can I update the table? > >> El 03/03/2018, a las 16:12, Jack Parker <jack.parker4@verizon.net> > escribió: >> >> Why the hell doesnt email format properly? >> >> set pdqpriority 10; for the unload, this is the turbo button.>> >> create raw table <table> () >> raw is important if you want optimal load performance. >> >> .... >> >> insert into <table> select *, <literal value> from <table>_ext; >> >> do a level 0 backup, even if it is to /dev/null. >> >> Hope that solves some of the mysteries that my email agent introduced. >> >> cheers >> j. >> >>> On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: > >>> >>> Really, use an external table as Mike suggests - especially if >>> it=E2=80=99= s a large table. (I love the detail Mike put into his >>> answer). I = differ from Mike in that I think it=E2=80=99s easier to >>> add the column = on the reload: >>> >>> create external table <table>_ext sameas <table> using ( >>> >>> datafiles >>> >>> ( >>> >>> "DISK:/<path>/<table>1.unl", >>> >>> "DISK:/<path>/<table>2.unl", >>> >>> =E2=80=A6.. >>> >>> "DISK:/<path>/<table>32.unl", >>> >>> ), >>> >>> format "delimited", >>> >>> escape on, >>> >>> express >>> ); >>> >>> set pdqpriority 10; =E2=80=94 Important, you want to take advantage>>> of = PDQ for the unload! This is the turbo button. >>> insert into <table>_ext select * from <table>; >>> >>> (drop and recreate your table with the new column =E2=80=94 create >>> raw = table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); >>> =E2=80=94 Important to create the table as raw is you want optimal >>> load = performance. >>> >>> insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; >>> >>> Alter table <table> type (standard); create your indexes now. >>> Do a level 0 backup (even if it=E2=80=99s to /dev/null) >>> >>> How many files you choose to use for the external table is up to you. >>> I = use a multiple or factor of the #cpus. >>> >>> This is waaaay faster than an unload statement. >>> >>> cheers >>> j. >>> >>>> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = >>> wrote: >>>> =20 >>>> I think I will unload the table, will recreate and load again.=20 In >>>> my case I will only add a char(1) column with default value of "D", >>>> = >>> so, the=20 >>>> instrucci=C3=B3n will be=20 >>>> =20 >>>> unload to filename=20 >>>> select * from table, "D" ?=20>>>> Thanks in advance.=20 >>>> =20 >>>>> El 03/03/2018, a las 10:36, Mike Walker >>>>> <mike@advancedatatools.com>=20= >>> >>>> escribi=C3=B3:=20 >>>>> =20 >>>>> This will happen when you're doing a "slow alter" and all the pages >>>>> = >>> of the=20 >>>>> table need to be rewritten.=20 >>>>> =20 >>>>> You could try changing the table to RAW (as long as you are not = >>> replicating)=20 >>>>> and trying again, but you will need to make sure that you have >>>>> plenty = >>> of=20 >>>>> room in the dbspace for a copy of the table to be stored while the >>>>> = >>> change is=20 >>>>> made.=20 >>>>> =20 >>>>> You could add also add more logs, but this might be more trouble >>>>> than = >>> it's=20 >>>>> worth.=20 >>>>> =20 >>>>> As Jack already recommended, the easiest thing to do is likely to = >>> unload the=20 >>>>> table, recreate the table with the updated definition and reload >>>>> it. = >>> If you=20 >>>>> are adding or dropping a column, then I would suggest unloading the >>>>> = >>> table in=20 >>>>> the NEW format (e.g. with a blank value in the position of the new >>>>> = >>> column)=20 >>>>> so that you can load it back in easily. Look at using external >>>>> tables = >>> for=20 >>>>> the unload for the best performance. During the load, to avoid >>>>> long=20= >>> >>>>> transaction errors again, load the table as RAW or use a utility >>>>> like = >>> dbload=20 >>>>> so that it commits regularly. Also leave off indexes and >>>>> constraints = >>> until=20 >>>>> the table is loaded and recreate them after.=20 >>>>> =20 >>>>> Mike=20 >>>>> =20 >>>>> -----Original Message-----=20 >>>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf >>>>> Of = >>> jorge=20 >>>>> valenzuela=20 >>>>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>>>> To: ids@iiug.org=20 >>>>> Subject: Long transaction aborted in alter table [40777]=20 >>>>> =20 >>>>> H,=20 >>>>> I trying to add a column to a table but I get the long transaction >>>>> = >>> aborted,=20 >>>>> what can I do?=20 >>>>> Thanks in advance.=20 >>>>> =20 >>>>> = >>> ********************************************************************* >>> *****= >>> **=20 >>>>> ***=20 >>>>> Forum Note: Use "Reply" to post a response in the discussion >>>>> forum.=20= >>> >>>>> =20 >>>>> =20 >>>>> =20 >>>> = >>> ********************************************************************* >>> *****= >>> *****=20 >>>>> Forum Note: Use "Reply" to post a response in the discussion >>>>> forum.=20= >>> >>>>> =20 >>>> =20 >>>> =20 >>>> = >>> ********************************************************************* >>> *****= >>> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= >>> >>>> =20 >>> >>> >>> >> > **************************************************************************** > *** >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> > **************************************************************************** > *** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the dis

Make sure you have enough logical log space and your alter table should succeed. You can add new logfiles using onparams -a and drop them later on if you do not need that huge amount of log space in your daily operation (take a full backup afterwards). Marcus Haarmann Von: "jorge valenzuela" <jorgervt@gmail.com> An: "ids" <ids@iiug.org> Gesendet: Montag, 5. März 2018 00:22:36 Betreff: Re: Long transaction aborted in alter table [40787] Yes > El 04/03/2018, a las 12:37, Mike Walker <mike@advancedatatools.com> escribió: > > Do you mean can you get away with ALTERING the table if you delete a lot of > records? > > The smaller the table, the smaller the transaction will be when you alter > the table and the more likely it will be that the alter will complete. But > whether the alter will succeed depends on many factors - the size of the > table, the number of logs, the size of the logs, other things running at the > time, duration of the transaction, whether there are indexes on the table, > etc...so it's not really possible to say. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jorge > valenzuela > Sent: Sunday, March 04, 2018 1:28 PM > To: ids@iiug.org > Subject: Re: Long transaction aborted in alter table [40785] > > If I delete a lot of records, can I update the table? > >> El 03/03/2018, a las 16:12, Jack Parker <jack.parker4@verizon.net> > escribió: >> >> Why the hell doesnt email format properly? >> >> set pdqpriority 10; for the unload, this is the turbo button.>> >> create raw table <table> () >> raw is important if you want optimal load performance. >> >> .... >> >> insert into <table> select *, <literal value> from <table>_ext; >> >> do a level 0 backup, even if it is to /dev/null. >> >> Hope that solves some of the mysteries that my email agent introduced. >> >> cheers >> j. >> >>> On Mar 3, 2018, at 7:06 PM, Jack Parker <jack.parker4@verizon.net> wrote: > >>> >>> Really, use an external table as Mike suggests - especially if >>> it=E2=80=99= s a large table. (I love the detail Mike put into his >>> answer). I = differ from Mike in that I think it=E2=80=99s easier to >>> add the column = on the reload: >>> >>> create external table <table>_ext sameas <table> using ( >>> >>> datafiles >>> >>> ( >>> >>> "DISK:/<path>/<table>1.unl", >>> >>> "DISK:/<path>/<table>2.unl", >>> >>> =E2=80=A6.. >>> >>> "DISK:/<path>/<table>32.unl", >>> >>> ), >>> >>> format "delimited", >>> >>> escape on, >>> >>> express >>> ); >>> >>> set pdqpriority 10; =E2=80=94 Important, you want to take advantage>>> of = PDQ for the unload! This is the turbo button. >>> insert into <table>_ext select * from <table>; >>> >>> (drop and recreate your table with the new column =E2=80=94 create >>> raw = table <table> (=E2=80=A6=E2=80=A6=E2=80=A6) ); >>> =E2=80=94 Important to create the table as raw is you want optimal >>> load = performance. >>> >>> insert into <table> select *, =E2=80=98D=E2=80=99 from <table>_ext; >>> >>> Alter table <table> type (standard); create your indexes now. >>> Do a level 0 backup (even if it=E2=80=99s to /dev/null) >>> >>> How many files you choose to use for the external table is up to you. >>> I = use a multiple or factor of the #cpus. >>> >>> This is waaaay faster than an unload statement. >>> >>> cheers >>> j. >>> >>>> On Mar 3, 2018, at 6:46 PM, jorge valenzuela <jorgervt@gmail.com> = >>> wrote: >>>> =20 >>>> I think I will unload the table, will recreate and load again.=20 In >>>> my case I will only add a char(1) column with default value of "D", >>>> = >>> so, the=20 >>>> instrucci=C3=B3n will be=20 >>>> =20 >>>> unload to filename=20 >>>> select * from table, "D" ?=20>>>> Thanks in advance.=20 >>>> =20 >>>>> El 03/03/2018, a las 10:36, Mike Walker >>>>> <mike@advancedatatools.com>=20= >>> >>>> escribi=C3=B3:=20 >>>>> =20 >>>>> This will happen when you're doing a "slow alter" and all the pages >>>>> = >>> of the=20 >>>>> table need to be rewritten.=20 >>>>> =20 >>>>> You could try changing the table to RAW (as long as you are not = >>> replicating)=20 >>>>> and trying again, but you will need to make sure that you have >>>>> plenty = >>> of=20 >>>>> room in the dbspace for a copy of the table to be stored while the >>>>> = >>> change is=20 >>>>> made.=20 >>>>> =20 >>>>> You could add also add more logs, but this might be more trouble >>>>> than = >>> it's=20 >>>>> worth.=20 >>>>> =20 >>>>> As Jack already recommended, the easiest thing to do is likely to = >>> unload the=20 >>>>> table, recreate the table with the updated definition and reload >>>>> it. = >>> If you=20 >>>>> are adding or dropping a column, then I would suggest unloading the >>>>> = >>> table in=20 >>>>> the NEW format (e.g. with a blank value in the position of the new >>>>> = >>> column)=20 >>>>> so that you can load it back in easily. Look at using external >>>>> tables = >>> for=20 >>>>> the unload for the best performance. During the load, to avoid >>>>> long=20= >>> >>>>> transaction errors again, load the table as RAW or use a utility >>>>> like = >>> dbload=20 >>>>> so that it commits regularly. Also leave off indexes and >>>>> constraints = >>> until=20 >>>>> the table is loaded and recreate them after.=20 >>>>> =20 >>>>> Mike=20 >>>>> =20 >>>>> -----Original Message-----=20 >>>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf >>>>> Of = >>> jorge=20 >>>>> valenzuela=20 >>>>> Sent: Saturday, March 03, 2018 9:38 AM=20 >>>>> To: ids@iiug.org=20 >>>>> Subject: Long transaction aborted in alter table [40777]=20 >>>>> =20 >>>>> H,=20 >>>>> I trying to add a column to a table but I get the long transaction >>>>> = >>> aborted,=20 >>>>> what can I do?=20 >>>>> Thanks in advance.=20 >>>>> =20 >>>>> = >>> ********************************************************************* >>> *****= >>> **=20 >>>>> ***=20 >>>>> Forum Note: Use "Reply" to post a response in the discussion >>>>> forum.=20= >>> >>>>> =20 >>>>> =20 >>>>> =20 >>>> = >>> ********************************************************************* >>> *****= >>> *****=20 >>>>> Forum Note: Use "Reply" to post a response in the discussion >>>>> forum.=20= >>> >>>>> =20 >>>> =20 >>>> =20 >>>> = >>> ********************************************************************* >>> *****= >>> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= >>> >>>> =20 >>> >>> >>> >> > **************************************************************************** > *** >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> > ************