Extraction Routine - Ordering of Tables

[Former Member]

When you extract a remote site, the extraction routine pulls out the tables in an order so as to not run into integrity issues. How does it know what order to place into the reload file?

I need to write some routines to pull out data from certain tables, but I also need to reload them in order. I'm hoping there is something in the system tables that can help me accomplish this.

Thanks.

[Breck_Carter]

Have a look at the WAIT_FOR_COMMIT option... that might make ordering the inserts unnecessary.

[VolkerBarth]

@Breck: However, then one can't use LOAD TABLE as that does an automatic commit...

[VolkerBarth]

AFAIK, the DBXTRACT tool (and DBUNLOAD, too) create and load tables in chronological order, i.e. sorted by *systable.table_id* (and the table_id is used as file name for the unloaded data).

The "not running into integrity issues" feature arises from the fact that FKs are built via "ALTER TABLE t ADD FOREIGN KEY ..." after the load table statements, so that the order of LOADING TABLES is not important at all. (Cf. this question)

However, when you have to extract data in already existing tables with already declared FKs then you should build the order based on FKs. For that, you will have to query the system catalog, primarily table SYSFOREIGNKEY.