Hello people!
I have a table with more than 30 million rows,a lot of columns and
indexes. We need to change a column which is dec(15,2) and nullable to
have a default value of zero.
As I know you cannot alter a column unless it is varchar.
I was thinking to export the whole table in ixf format, create the new
table with the new definition and then import with insert into.
Or rename the original table, create a new one and insert into new
table with select * from the original.

Which method will be better/faster?

My environment is AIX 4.3.3
DB2 UDB V7.2 fixpak 6

Thank you

"Demetris" <DMina@laiki.com> wrote in message
news:c5285638.0402012324.1f94de93@posting.google.c om...[color=blue]
> Hello people!
> I have a table with more than 30 million rows,a lot of columns and
> indexes. We need to change a column which is dec(15,2) and nullable to
> have a default value of zero.
> As I know you cannot alter a column unless it is varchar.
> I was thinking to export the whole table in ixf format, create the new
> table with the new definition and then import with insert into.
> Or rename the original table, create a new one and insert into new
> table with select * from the original.
>
> Which method will be better/faster?
>
> My environment is AIX 4.3.3
> DB2 UDB V7.2 fixpak 6
>
> Thank you[/color]

There are a lot of different options. Your main concern will probably be
speed and not having the logs fill up. Logs will fill up if there are no
intermediate commits during the process (unless you disable logging using
one of the methods below).

If you do create a new table and insert into new table with select * from
the original, then you probably want to consider the NOT LOGGED INITIALLY
keyword when you create the table.Any changes made to the table by an
Insert, Delete, Update, Create Index, Drop Index, or Alter Table operation
in the same unit of work in which the table is created are not logged.
Create the indexes after the data is loaded to the table with the insert
(this will speed things up).Make sure auto commit is turned off so that all
of these steps are in the same unit of work. Then do a final commit at the
end. Take a full off-line backup.

If you use the import utility, set the commit frequency to a reasonable
value to keep the logs from filling up (interval of about 1000 would be
fine).

If you use the load utility, do non-recoverable load and then take a backup
afterwards to prevent logging during the load. Take a full off-line backup

For either the load or import utility, I would use export in the del format,
not ixf.

Did you consider adding a new column with the correct default, and then
creating a view that omits the old DEC column? Under certain
circumstances, the old table could be renamed and the view could be
created with the table's original name.

Demetris wrote:
[color=blue]
> Hello people!
> I have a table with more than 30 million rows,a lot of columns and
> indexes. We need to change a column which is dec(15,2) and nullable to
> have a default value of zero.
> As I know you cannot alter a column unless it is varchar.
> I was thinking to export the whole table in ixf format, create the new
> table with the new definition and then import with insert into.
> Or rename the original table, create a new one and insert into new
> table with select * from the original.
>
> Which method will be better/faster?
>
> My environment is AIX 4.3.3
> DB2 UDB V7.2 fixpak 6
>
> Thank you[/color]

I would like to thank you for your responses.
I will go with the following method:
- Export in del format.
- Drop the original table.
- Create the new one.
- Import the data with frequent commits.
- Create indexes.

I will post the results :)

Thanks again

Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bvlhhh$5kc$1@hanover.torolab.ibm.com>...[color=blue]
> Did you consider adding a new column with the correct default, and then
> creating a view that omits the old DEC column? Under certain
> circumstances, the old table could be renamed and the view could be
> created with the table's original name.
>
> Demetris wrote:
>[color=green]
> > Hello people!
> > I have a table with more than 30 million rows,a lot of columns and
> > indexes. We need to change a column which is dec(15,2) and nullable to
> > have a default value of zero.
> > As I know you cannot alter a column unless it is varchar.
> > I was thinking to export the whole table in ixf format, create the new
> > table with the new definition and then import with insert into.
> > Or rename the original table, create a new one and insert into new
> > table with select * from the original.
> >
> > Which method will be better/faster?
> >
> > My environment is AIX 4.3.3
> > DB2 UDB V7.2 fixpak 6
> >
> > Thank you[/color][/color]

"Demetris" <DMina@laiki.com> wrote in message
news:c5285638.0402042358.3113bb27@posting.google.c om...[color=blue]
> I would like to thank you for your responses.
> I will go with the following method:
> - Export in del format.
> - Drop the original table.
> - Create the new one.
> - Import the data with frequent commits.
> - Create indexes.
>
> I will post the results :)
>
> Thanks again
>[/color]

How about this

- Export in del format.
- Rename the original table.
- Create the new one (table).
- Import the data with frequent commits.
- Create indexes.
- If everything checks out, drop the old table (which was renamed)

DMina@laiki.com (Demetris) wrote in message news:<c5285638.0402042358.3113bb27@posting.google. com>...[color=blue]
> I would like to thank you for your responses.
> I will go with the following method:
> - Export in del format.
> - Drop the original table.
> - Create the new one.
> - Import the data with frequent commits.
> - Create indexes.
>
> I will post the results :)
>
> Thanks again
>
> Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bvlhhh$5kc$1@hanover.torolab.ibm.com>...[color=green]
> > Did you consider adding a new column with the correct default, and then
> > creating a view that omits the old DEC column? Under certain
> > circumstances, the old table could be renamed and the view could be
> > created with the table's original name.
> >
> > Demetris wrote:
> >[color=darkred]
> > > Hello people!
> > > I have a table with more than 30 million rows,a lot of columns and
> > > indexes. We need to change a column which is dec(15,2) and nullable to
> > > have a default value of zero.
> > > As I know you cannot alter a column unless it is varchar.
> > > I was thinking to export the whole table in ixf format, create the new
> > > table with the new definition and then import with insert into.
> > > Or rename the original table, create a new one and insert into new
> > > table with select * from the original.
> > >
> > > Which method will be better/faster?
> > >
> > > My environment is AIX 4.3.3
> > > DB2 UDB V7.2 fixpak 6
> > >
> > > Thank you[/color][/color][/color]

I would consider using LOAD instead of IMPORT because with load you
don't care about the commit interval at all and essentially never run
out of log, and it's finally faster than import, IMHO. Constraints is
not an isuue as well -- you can create them after the table is loaded
so you will not have to issue SET INTEGRITY to get the table online.
However, as long as you are on V7.2, the only tablespace availability
would be your concern.

Regards,
-Eugene

"Eugene" <eugene@profitlogic.com> wrote in message
news:9573e6b2.0402050623.167c8b3d@posting.google.c om...[color=blue]
> I would consider using LOAD instead of IMPORT because with load you
> don't care about the commit interval at all and essentially never run
> out of log, and it's finally faster than import, IMHO. Constraints is
> not an isuue as well -- you can create them after the table is loaded
> so you will not have to issue SET INTEGRITY to get the table online.
> However, as long as you are on V7.2, the only tablespace availability
> would be your concern.
>
> Regards,
> -Eugene[/color]

I thought that LOAD does log unless one uses the non-recoverable option?

If this was a recurring process, it might be worth doing it that way, but I
think an IMPORT will finish in a reasonable time frame.

Mark A wrote:[color=blue]
> "Eugene" <eugene@profitlogic.com> wrote in message
> news:9573e6b2.0402050623.167c8b3d@posting.google.c om...
>[color=green]
>>I would consider using LOAD instead of IMPORT because with load you
>>don't care about the commit interval at all and essentially never run
>>out of log, and it's finally faster than import, IMHO. Constraints is
>>not an isuue as well -- you can create them after the table is loaded
>>so you will not have to issue SET INTEGRITY to get the table online.
>>However, as long as you are on V7.2, the only tablespace availability
>>would be your concern.
>>
>>Regards,
>>-Eugene[/color]
>
>
> I thought that LOAD does log unless one uses the non-recoverable option?[/color]

During LOAD, an entry is made in the logs to indicate that a load has
taken place. The individual rows are not logged -- LOAD appends complete
pages to the table.

The nonrecoverable option controls whether the tablespace will placed
into backup pending mode following the load (provided that LOGRETAIN = YES).

The fact that individual rows are not logged is why LOAD almost always
_significantly_ faster than IMPORT (which simply does an INSERT for each
row in the file). Even using the COMPOUND=x filetype modifier in INSERT,
LOAD will still generally work much faster.

(There are certain cases where IMPORT may be faster, such as with small
data sets).



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

> > I thought that LOAD does log unless one uses the non-recoverable option?[color=blue]
>
> During LOAD, an entry is made in the logs to indicate that a load has
> taken place. The individual rows are not logged -- LOAD appends complete
> pages to the table.
>
> The nonrecoverable option controls whether the tablespace will placed
> into backup pending mode following the load (provided that LOGRETAIN =[/color]
YES).[color=blue]
>
> The fact that individual rows are not logged is why LOAD almost always
> _significantly_ faster than IMPORT (which simply does an INSERT for each
> row in the file). Even using the COMPOUND=x filetype modifier in INSERT,
> LOAD will still generally work much faster.
>
> (There are certain cases where IMPORT may be faster, such as with small
> data sets).
>[/color]
Thanks. I have always done a non recoverable load and then a backup. You are
right that it is very fast.

IMO, the Command Reference manual is not very clear on these issues. I also
don't understand why all loads (since they don't log) don't place the
tablespace in backup pending (as happens on DB2 for OS/390). Makes no sense
to me.

Ian <ianbjor@mobileaudio.com> wrote in message news:<402266a7$1_1@corp.newsgroups.com>...[color=blue]
> Mark A wrote:[color=green]
> > "Eugene" <eugene@profitlogic.com> wrote in message
> > news:9573e6b2.0402050623.167c8b3d@posting.google.c om...
> >[color=darkred]
> >>I would consider using LOAD instead of IMPORT because with load you
> >>don't care about the commit interval at all and essentially never run
> >>out of log, and it's finally faster than import, IMHO. Constraints is
> >>not an isuue as well -- you can create them after the table is loaded
> >>so you will not have to issue SET INTEGRITY to get the table online.
> >>However, as long as you are on V7.2, the only tablespace availability
> >>would be your concern.
> >>
> >>Regards,
> >>-Eugene[/color]
> >
> >
> > I thought that LOAD does log unless one uses the non-recoverable option?[/color]
>
> During LOAD, an entry is made in the logs to indicate that a load has
> taken place. The individual rows are not logged -- LOAD appends complete
> pages to the table.
>
> The nonrecoverable option controls whether the tablespace will placed
> into backup pending mode following the load (provided that LOGRETAIN = YES).
>
> The fact that individual rows are not logged is why LOAD almost always
> _significantly_ faster than IMPORT (which simply does an INSERT for each
> row in the file). Even using the COMPOUND=x filetype modifier in INSERT,
> LOAD will still generally work much faster.
>
> (There are certain cases where IMPORT may be faster, such as with small
> data sets).
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]

What is "COMPOUND=x filetype modifier in INSERT" that you mention? Is
it some platform specific SQL for iSeries or zOS? (I am on the UDB for
UNIX field, otherwise my DB knowledge turns out not to be good :)

-Eugene

Go to this site: http://publib.boulder.ibm.com/infoce...help/index.jsp

And search on compound=x (look under Import Command) to read about this
modifier. It is suppored on DB2/Unix/Windows/Linux.

Eugene wrote:
[color=blue]
> Ian <ianbjor@mobileaudio.com> wrote in message news:<402266a7$1_1@corp.newsgroups.com>...
>[color=green]
>>Mark A wrote:
>>[color=darkred]
>>>"Eugene" <eugene@profitlogic.com> wrote in message
>>>news:9573e6b2.0402050623.167c8b3d@posting.googl e.com...
>>>
>>>
>>>>I would consider using LOAD instead of IMPORT because with load you
>>>>don't care about the commit interval at all and essentially never run
>>>>out of log, and it's finally faster than import, IMHO. Constraints is
>>>>not an isuue as well -- you can create them after the table is loaded
>>>>so you will not have to issue SET INTEGRITY to get the table online.
>>>>However, as long as you are on V7.2, the only tablespace availability
>>>>would be your concern.
>>>>
>>>>Regards,
>>>>-Eugene
>>>
>>>
>>>I thought that LOAD does log unless one uses the non-recoverable option?[/color]
>>
>>During LOAD, an entry is made in the logs to indicate that a load has
>>taken place. The individual rows are not logged -- LOAD appends complete
>>pages to the table.
>>
>>The nonrecoverable option controls whether the tablespace will placed
>>into backup pending mode following the load (provided that LOGRETAIN = YES).
>>
>>The fact that individual rows are not logged is why LOAD almost always
>>_significantly_ faster than IMPORT (which simply does an INSERT for each
>>row in the file). Even using the COMPOUND=x filetype modifier in INSERT,
>>LOAD will still generally work much faster.
>>
>>(There are certain cases where IMPORT may be faster, such as with small
>>data sets).
>>
>>
>>
>>-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
>>http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
>>-----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]
>
>
> What is "COMPOUND=x filetype modifier in INSERT" that you mention? Is
> it some platform specific SQL for iSeries or zOS? (I am on the UDB for
> UNIX field, otherwise my DB knowledge turns out not to be good :)
>
> -Eugene[/color]

IMPORT......MODIFIED BY COMPOUND 100 .....
Values are 1 -100
Does a block insert of 100 records from the source as opposed to retrieve,
insert, retrieve, insert... one by one.
Very useful when source file not on the server but also gives a performance
improvement even when source and target in the same box.

As stated earlier, LOAD does register the command and metadata in the logs
but not the rows. If you have unique indexes defined on the target table
and records would create duplicate (because of errors!) then the rows
inserted in the exception table and deleted from the target table when
indexes are built WILL be logged.

DB2 UDB for LUW has a logging methodology called circuler logging which does
not archive logs. In that case a successful load will leave the tblspc. and
the table in normal state and no backup is required. Only recovery
available is the full db restore!!
HTH, Pierre.
"Eugene" <eugene@profitlogic.com> a écrit dans le message de
news:9573e6b2.0402051110.60e46140@posting.google.c om...[color=blue]
> Ian <ianbjor@mobileaudio.com> wrote in message[/color]
news:<402266a7$1_1@corp.newsgroups.com>...[color=blue][color=green]
> > Mark A wrote:[color=darkred]
> > > "Eugene" <eugene@profitlogic.com> wrote in message
> > > news:9573e6b2.0402050623.167c8b3d@posting.google.c om...
> > >
> > >>I would consider using LOAD instead of IMPORT because with load you
> > >>don't care about the commit interval at all and essentially never run
> > >>out of log, and it's finally faster than import, IMHO. Constraints is
> > >>not an isuue as well -- you can create them after the table is loaded
> > >>so you will not have to issue SET INTEGRITY to get the table online.
> > >>However, as long as you are on V7.2, the only tablespace availability
> > >>would be your concern.
> > >>
> > >>Regards,
> > >>-Eugene
> > >
> > >
> > > I thought that LOAD does log unless one uses the non-recoverable[/color][/color][/color]
option?[color=blue][color=green]
> >
> > During LOAD, an entry is made in the logs to indicate that a load has
> > taken place. The individual rows are not logged -- LOAD appends[/color][/color]
complete[color=blue][color=green]
> > pages to the table.
> >
> > The nonrecoverable option controls whether the tablespace will placed
> > into backup pending mode following the load (provided that LOGRETAIN =[/color][/color]
YES).[color=blue][color=green]
> >
> > The fact that individual rows are not logged is why LOAD almost always
> > _significantly_ faster than IMPORT (which simply does an INSERT for each
> > row in the file). Even using the COMPOUND=x filetype modifier in INSERT,
> > LOAD will still generally work much faster.
> >
> > (There are certain cases where IMPORT may be faster, such as with small
> > data sets).
> >
> >
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]
>
> What is "COMPOUND=x filetype modifier in INSERT" that you mention? Is
> it some platform specific SQL for iSeries or zOS? (I am on the UDB for
> UNIX field, otherwise my DB knowledge turns out not to be good :)
>
> -Eugene[/color]

Mark A wrote:[color=blue]
>
> IMO, the Command Reference manual is not very clear on these issues. I also
> don't understand why all loads (since they don't log) don't place the
> tablespace in backup pending (as happens on DB2 for OS/390). Makes no sense
> to me.[/color]

A tablespace going into backup pending is a function of whether the load
needs to be recoverable.

Here are the situations when BACKUP PENDING does not apply:

1) LOGRETAIN=NO (i.e. circular logging).
When this is the case, rollforward is not an option, so no backup
is required.

2) LOGRETAIN=YES, load option COPY=YES
In this case, DB2 will save a copy of the input file getting loaded
into the database. During a rollforward operation, DB2 will use
the saved copy of the data file in order ot load the data into the
table again. (I'm not sure if DB2 saves the actual input file, or
if it keeps a copy of the formatted pages that were appended to the
table. Probably the latter).

3) LOGRETAIN=YES, load option RECOVERABLE=NO
In this case, you are telling DB2 that you are aware that the table
will not be recoverable but to go ahead and do the load anyway.
This is similar to #1 above. If you rollforward through a
non-recoverable load, it will be as if the load never happened.
Of course, this can have serious consequences, as I'm sure you
can imagine.




-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

"Ian" <ianbjor@mobileaudio.com> wrote in message
news:40256787$1_1@corp.newsgroups.com...[color=blue]
> Mark A wrote:[color=green]
> >
> > IMO, the Command Reference manual is not very clear on these issues. I[/color][/color]
also[color=blue][color=green]
> > don't understand why all loads (since they don't log) don't place the
> > tablespace in backup pending (as happens on DB2 for OS/390). Makes no[/color][/color]
sense[color=blue][color=green]
> > to me.[/color]
>
> A tablespace going into backup pending is a function of whether the load
> needs to be recoverable.
>
> Here are the situations when BACKUP PENDING does not apply:
>
> 1) LOGRETAIN=NO (i.e. circular logging).
> When this is the case, rollforward is not an option, so no backup
> is required.
>
> 2) LOGRETAIN=YES, load option COPY=YES
> In this case, DB2 will save a copy of the input file getting loaded
> into the database. During a rollforward operation, DB2 will use
> the saved copy of the data file in order ot load the data into the
> table again. (I'm not sure if DB2 saves the actual input file, or
> if it keeps a copy of the formatted pages that were appended to the
> table. Probably the latter).
>
> 3) LOGRETAIN=YES, load option RECOVERABLE=NO
> In this case, you are telling DB2 that you are aware that the table
> will not be recoverable but to go ahead and do the load anyway.
> This is similar to #1 above. If you rollforward through a
> non-recoverable load, it will be as if the load never happened.
> Of course, this can have serious consequences, as I'm sure you
> can imagine.
>[/color]
I don't understand number 3). Once the load is done and then a copy is made,
I assume that the tablespace can be recovered after that point in time. I
also assume that the tablespace could be recovered to a point in time
(without roll forward recovery) using a previous backup? Am I missing
something?

Mark A wrote:
[color=blue]
> "Ian" <ianbjor@mobileaudio.com> wrote in message
> news:40256787$1_1@corp.newsgroups.com...[color=green]
>>
>>3) LOGRETAIN=YES, load option RECOVERABLE=NO
>> In this case, you are telling DB2 that you are aware that the table
>> will not be recoverable but to go ahead and do the load anyway.
>> This is similar to #1 above. If you rollforward through a
>> non-recoverable load, it will be as if the load never happened.
>> Of course, this can have serious consequences, as I'm sure you
>> can imagine.
>>[/color]
>
> I don't understand number 3). Once the load is done and then a copy is made,
> I assume that the tablespace can be recovered after that point in time. I
> also assume that the tablespace could be recovered to a point in time
> (without roll forward recovery) using a previous backup? Am I missing
> something?[/color]

No copy of the data is made in the this situation. Therefore, the
tablespace can be recovered to any point in time _before_ the load took
place. A copy of the data is made when COPY=YES is specified, but that
option specifies, by definition, a recoverable load.

I used to think that a rolling forward through a non-recoverable load
would leave the tablespace in drop pending, but I was wrong. You _can_
actually roll forward through the load, and the tablespace will appear
as though the load never happened. Knowing this, I'm sure you can
imagine how the integrity of the data in the database could be
compromised (even if any RI in the database is not).




-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----