IDS Forum

Re: Long transaction aborted in alter table

Posted By: jorge valenzuela
Date: Sunday, 4 March 2018, at 6:22 p.m.

In Response To: RE: Long transaction aborted in alter table (Mike Walker)

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 discussion forum.
>

Messages In This Thread

• Long transaction aborted in alter table
  jorge valenzuela -- Saturday, 3 March 2018, at 11:37 a.m.
  • Re: Long transaction aborted in alter table
    Jack Parker -- Saturday, 3 March 2018, at 11:42 a.m.
    • Re: Long transaction aborted in alter table
      jorge valenzuela -- Saturday, 3 March 2018, at 10:15 p.m.
  • RE: Long transaction aborted in alter table
    Mike Walker -- Saturday, 3 March 2018, at 1:36 p.m.
    • Re: Long transaction aborted in alter table
      jorge valenzuela -- Saturday, 3 March 2018, at 6:46 p.m.
      • Re: Long transaction aborted in alter table
        Jack Parker -- Saturday, 3 March 2018, at 7:06 p.m.
        • Re: Long transaction aborted in alter table
          Jack Parker -- Saturday, 3 March 2018, at 7:12 p.m.
          • Re: Long transaction aborted in alter table
            jorge valenzuela -- Saturday, 3 March 2018, at 10:14 p.m.
          • Re: Long transaction aborted in alter table
            jorge valenzuela -- Sunday, 4 March 2018, at 3:27 p.m.
            • RE: Long transaction aborted in alter table
              Mike Walker -- Sunday, 4 March 2018, at 3:37 p.m.
              • Re: Long transaction aborted in alter table
                jorge valenzuela -- Sunday, 4 March 2018, at 6:22 p.m.
                • Re: Long transaction aborted in alter table
                  Marcus Haarmann -- Monday, 5 March 2018, at 10:53 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.