DB2 load for a table

Vivek Anand · New User Joined: 04 Jun 2013 Posts: 19 Location: India

Hi All,
I have a requirement to load a DB2 table (using the unload of the table from a previous date) using a batch job such that
1. The records from the table should be replaced with those from the
unload file
2. No other tables in the tablespace should be affected

I tried the below options with DSNUTILB but none seem to work for me
1. Replace - this deletes all the rows from other tables in the tablespace
2. Resume Yes - this adds up the records from the unload file to the table but does not delete the records in the table that are not in the unload file
3. Resume No (Default) - this does not work as the tablespace is not empty

Could any one suggest if this is possible and if so how. Thanks in advance.

Pandora-Box · Posted: Fri Nov 15, 2013 7:36 pm

1.How many records you wanted to load ?
2.How many records are in table?
3.How many tables are there in the tablespace?
4.Is there a extract of other tables in the tablespace?
5.What version of db2 are you using?

Vivek Anand · New User Joined: 04 Jun 2013 Posts: 19 Location: India

GuyC · Posted: Fri Nov 15, 2013 9:04 pm

one way is to
- truncate the table beforehand
- Load resume yes.

another is to
- unload all tables in the tablespace except the forementioned table.
- Load Replace the concatenated files

Vivek Anand · New User Joined: 04 Jun 2013 Posts: 19 Location: India

GuyC,
Thanks for your valuable inputs.

Vivek Anand · New User Joined: 04 Jun 2013 Posts: 19 Location: India

Hi GuyC/All,
I am faced with another challenge here. Suppose i use a delete query to clean up the table under question (before load) it might delete rows from other tables as well due to referential constraint. Is there a way i can ensure the constraint is not enforced while deleting rows from a table?

dick scherrer · Posted: Mon Nov 18, 2013 7:51 pm

Hello,

Suggest you reconsider your approach . . .

If the constraints are needed, they should probably Not be bypassed.

Vivek Anand · New User Joined: 04 Jun 2013 Posts: 19 Location: India

Hi,
This should not be a problem as the tables might be out of sync only for a brief period of time. After that, i will revert back the table contents to what it was before the load and they will become consistent again.

GuyC · Posted: Tue Nov 19, 2013 1:47 pm

that's why I said truncate. It's not allowed when there is R.I.
Although it is not advisable you could drop the foreign key, do your thing and then recreate the foreign key. I 've seen processes who do this, but the problems are numerous and sometimes hard to solve.

In case of R.I. the second method is safer. although usually more CPU/Elapsed/space requirements.