Quick import or load of multiple DB2 Master/Slave tables case description

This article mainly describes how to import or load multiple DB2 Master/Slave tables correctly and quickly. If you perform the actual operation steps on import or load multiple DB2 Master/Slave tables, if you are interested, you can browse the following articles.

Problem

During routine database maintenance, database administrators sometimes need to move a large amount of data between different databases, and the data has the relationship between the master and slave tables in our business, when exporting, importing, or loading data, we may encounter errors such as SQL0530N and SQL0668N. How can we avoid these errors, to ensure that these operations can be completed correctly and quickly?

Answer

Suppose we have created the following two DB2 Master/Slave tables:

 
 

  
  
CREATE TABLE PARENTT(ID INT NOT NULL,  
  
  
NAME CHAR(20) NOT NULL,  
  
  
CONSTRAINT P_PT PRIMARY KEY(ID))  
  
  
CREATE TABLE CHILDT (ID INT NOT NULL,  
  
  
CNAME CHAR(40) NOT NULL,  
  
  
PID INT,  
  
  
CONSTRAINT P_CT PRIMARY KEY(ID),  
  
  
CONSTRAINT F_CT FOREIGN KEY (PID) REFERENCES PARENTT (ID) ON DELETE CASCADE)  
 
 

If no data is imported into the primary table PARENTT, we will encounter the SQL0530N error when using IMPORT to IMPORT data from the table CHILDT, the reason is that the foreign key constraints of IMPORT need to check the integrity of the reference. If there is no relevant record in the master table, the data cannot be imported.

 
 

  
  
db2 "import from child.del of del insert into childt"  
  
  
SQL0530N FOREIGN KEY "HUANGDK.CHILDT.F_CT" 
 
 

The insert or update value of is not equal to any parent key value of the parent table.

If you use the LOAD method, you can LOAD data because the LOAD is designed to ignore the constraints check during loading, but when querying data, you will encounter SQL0668N RC = 1

 
 

  
  
db2 "load from child.del of del insert into childt"  
  
  
db2 "select * from childt"  
 
 

SQL0668N is not allowed to perform operations on the table "HUANGDK. CHILDT", cause code "1 ".

The cause code "1" indicates that the table "HUANGDK. CHILDT" is in the "check pending" status. The reference integrity of the slave table "HUANGDK. CHILDT" is not checked forcibly, and the table content may be invalid. If the slave table is in the check pending status, the operation on the parent table or basic table that is not in the check pending status may also receive this error.

After loading the data of the primary table PARENTT, you can use the following statement to restore the table to a normal state:

 
 

  
  
db2 set integrity for huangdk.childt immediate checked 
 
 

For operations that require loading a large number of DB2 Master/Slave tables, the preceding statements will waste a lot of time on the database administrator. If the preceding statements are executed before the master table data is loaded, an error of SQL0530N is also reported.

In fact, if we load or import the data in the master table first, we will not encounter the above error, nor need to execute the set integrity statement to greatly simplify the operation. We can find the table's master-slave relationship from the System View SYSCAT. REFERENCES by using the following SQL statement:

Db2 "select substr (REFTABSCHEMA,) as master table mode, substr (REFTABNAME,) as master table, substr (TABSCHEMA,) as slave table mode, substr (TABNAME, 1, 30) as from table from SYSCAT. REFERENCES"

Master table mode
Master table
Slave table mode
Slave table

 

 
 

  
  
ASN_DOWN_TC_M6101 IBMQREP_SENDQUEUES ASN_DOWN_TC_M6101 IBMQREP_SUBS  
  
  
ASN_DOWN_TC_M6101 IBMQREP_SUBS ASN_DOWN_TC_M6101 IBMQREP_SRC_COLS  
  
  
HDK PARENTT HDK CHILDT 
 
 

The above content is an introduction to how to import or load multiple DB2 Master/Slave tables correctly and quickly. I hope you will gain some benefits.