Hello everybody,

i want to export and later import the data of two tables into a second database. These two tables are referenced together over a foreign key.

How do I have to use the db2 export and import utility to get this working. The table structure already exists in the second database. I have only to move the data.

It is a daily job two override the data in the second database.

What statements do I have to use?

Thanks for help

toette

You major concern should be making sure that the referential integrity is intact when you do the import. You do not want rows in the dependant table to be pointing to rows of the parent table that do not exist in the parent table. THis can occur if you export from the parent table first, then export from the dependant table. Someone could have entered a new row into the parent table and a dependant row pointing to the new parent row before you started the dependant export. This would leave your parent export without the new row but your dependant export with the new row. One way to prevent this is to export the dependant table first and import the parent first (actually you should always import the parent first). This will prevent referential integrity breaks, but it will not prevent data consistency problems. You could end up with the parent rows, but not the dependant rows. How you handle this depends on how this affects you. If you can live with this scenario, and the next export/import will pick up the missing dependant rows and that is OK, then this is the solution. But if you need those rows, then you are going to have to do both exports in the same unit of work and prevent others from adding/modifying to either table during the unit of work. This is usually done by putting exclusive locks on both tables, then exporting.

HTH

Andy

Would replication be an option for you? You could use an interval that is large enough to start only once a day. Additionally, replication would transfer the data changes only. If you have a lot of changes compared to the total amount of data, it may not make sense, however.

If you are doing EXPORTS in some window where no users access that table, then you should have no issues. You have to first import data into Parent table and then to the dependent table.

If thats not the case you may have to use,
LOCK TABLE TABNAME IN SHARE MODE; this puts the table in Read only mode
As ARWINNER told, you can download the data in the same unit of work by putting the SHARE lock on both the table.

When you do a LAOD on parent table, Dependent table would go to CHECK PENDING STATUS which is a restricted state. So LOAD with ENFORCE YES on dependent table would reset this flag.

Thanks for all the helpful answers.