LOAD utility followup

[clilush]

I'm trying to fix up a database on my server by transferring all the
readable data to a new database, and then upgrading it from db2-7 to
db2-8. I'm using the export command to dump the tables that I'm having
a hard time with to a file, then running the LOAD command on those
exports to load them back into the new clean database table by table.
I'm wondering if there is anything that needs to be done after all the
LOAD commands are finnished in order to get the database in a
production state (aside from usual routines of backups, reorgs,
runstats, etc...) that might be specific to having run the LOAD
command?

(btw, it's running on a win2k3 server -- and go easy on the db2
terminology... i'm a long time interbase admin ;) )

[jdokos]

You may need to run SET INTEGRITY on any tables that are in CHECK
PENDING state.

[clilush]

jdokos wrote:

You may need to run SET INTEGRITY on any tables that are in CHECK
PENDING state.

Would it be a better idea to run this command after each LOAD command,
or can I save it up for after having run them (should only be running
about 6 LOAD statements in total)?

I searched the db2 docs on "SET INTEGRITY" and came up with this
sample:

SET INTEGRITY FOR <tablenameIMMEDIATE CHECKED

Does that sound right, or would "SET INTEGRITY" on it's own do the
entire database?

[Shashi Mannepalli]

You cannot run SET INTEGRITY for the entire database. You have to do it
one table at a time.

After all the LOAD's are done do this to find out which table's are in
check pending

db2 "select tabname from syscat.tables where status='C'"

cheers...
Shashi Mannepalli
clilush wrote:

jdokos wrote:

You may need to run SET INTEGRITY on any tables that are in CHECK
PENDING state.

Would it be a better idea to run this command after each LOAD command,
or can I save it up for after having run them (should only be running
about 6 LOAD statements in total)?

I searched the db2 docs on "SET INTEGRITY" and came up with this
sample:

SET INTEGRITY FOR <tablenameIMMEDIATE CHECKED

Does that sound right, or would "SET INTEGRITY" on it's own do the
entire database?

[j_ammann]

Shashi Mannepalli schrieb:

You cannot run SET INTEGRITY for the entire database. You have to do it
one table at a time.

After all the LOAD's are done do this to find out which table's are in
check pending

db2 "select tabname from syscat.tables where status='C'"

cheers...
Shashi Mannepalli

thats not completely true.

you can list more than 1 table.

SET INTEGRITY FOR TAB1,TAB2,TAB3 IMMEDIATE CHECKED

this has the advantage that you don't have to worry about the checking
order of the tables.

also:
SEQUENCES:
if you used sequences in the old DB for prim-key generation, you must
set the seq in the new DB higher than the last used value (ALTER
SEQUENCE .... RESTART WITH XX) otherwise you will get already generated
values

IDENTITY COLUMNS:
same there . . .
(alter table ${TABSCHEMA}.${TABNAME} alter column ${COLNAME} restart
with ${VALUE} )

[clilush]

j_******@yahoo.com wrote:

Shashi Mannepalli schrieb:

You cannot run SET INTEGRITY for the entire database. You have to do it
one table at a time.

After all the LOAD's are done do this to find out which table's are in
check pending

db2 "select tabname from syscat.tables where status='C'"

cheers...
Shashi Mannepalli

thats not completely true.

you can list more than 1 table.

SET INTEGRITY FOR TAB1,TAB2,TAB3 IMMEDIATE CHECKED

this has the advantage that you don't have to worry about the checking
order of the tables.

also:
SEQUENCES:
if you used sequences in the old DB for prim-key generation, you must
set the seq in the new DB higher than the last used value (ALTER
SEQUENCE .... RESTART WITH XX) otherwise you will get already generated
values

IDENTITY COLUMNS:
same there . . .
(alter table ${TABSCHEMA}.${TABNAME} alter column ${COLNAME} restart
with ${VALUE} )

Thanks! This helps a tonne!
There is actually a cool app that the software designers sent with the
database that will allow me to transfer the generators to a new
database but I have found int he past that I have to delete a record
from the table before being able to insert it -- which follows the same
comment about sequences that "j" mentions above.

I'm actually just finishing up the load commands now and will be
applying the SET INTEGRITY commands soon...

[Mark A]

<j_******@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

thats not completely true.

you can list more than 1 table.

SET INTEGRITY FOR TAB1,TAB2,TAB3 IMMEDIATE CHECKED

this has the advantage that you don't have to worry about the checking
order of the tables.

I may be wrong, but my recollection is that if you set integrity with more
than one table in the same statement (as above), then if any one of the
tables is not in check pending state, then it throws an error and the entire
statement fails. Please correct me if I am wrong.

[j_ammann]

Mark A schrieb:

<j_******@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

thats not completely true.

you can list more than 1 table.

SET INTEGRITY FOR TAB1,TAB2,TAB3 IMMEDIATE CHECKED

this has the advantage that you don't have to worry about the checking
order of the tables.

I may be wrong, but my recollection is that if you set integrity with more
than one table in the same statement (as above), then if any one of the
tables is not in check pending state, then it throws an error and the entire
statement fails. Please correct me if I am wrong.

the meaning was not to list ALL the tables in the database! only the
ones CHECK PENDING (i'm using a script (unix) which selects the tables
in CHECK PENDING and generates from there a list of tables to include
in the SET INTEGRITY).
another problem with including several tables ist that if the there is
a vialoation in one table (wrong data) the stmt fails for all...