DB2 LOAD Utility and Check Pending States

Loading data into a DB2 database using the LOAD utility has a lot of ins and outs. If a DBA has spent a lot of time working in a database without referential integrity or check constraints, then they may forget to check for tables in a check-pending state after loading data. To illustrate the points here, I’ll be using the SAMPLE database and examples you can work through to learn the concepts in detail.

The Problem

This problem is fairly immediately obvious, as a table in a check-pending state cannot be accessed, and will return this error message:

SQL0668N  Operation not allowed for reason code "1" on table 
"DB2INST1.PROJACT".  SQLSTATE=57016

Where ‘”DB2INST1.PROJACT”‘ is replaced with the table name in question. Looking up that error code to get the details of that specific reason code, we find:

$ db2 ? SQL0668N


SQL0668N  Operation not allowed for reason code "" on table
      "".

Explanation: 

Access to table "" is restricted. The cause is based on the
following reason codes "":

1        

         Reason code 1 can be returned in multiple scenarios, including
         the following examples:

          
         *  The table is in the Set Integrity Pending No Access state,
            which means the integrity of the table is not enforced and
            the content of the table might be invalid.
         *  An operation on a parent table or an underlying table that
            is not in the Set Integrity Pending No Access state may also
            receive this error if a dependent table is in the Set
            Integrity Pending No Access state.
         *  An attempt is made to issue the SET INTEGRITY statement
            against a user-maintained materialized query table without
            specifying the IMMEDIATE UNCHECKED clause.



User response: 

Respond to this error according to the reason code:

1        

         Bring the table named "" out of the Set Integrity
         Pending No Access state according to the type of table:

          
         *  For a user-maintained materialized query table, execute the
            statement with the IMMEDIATE UNCHECKED option.
         *  For materialized query tables that are not user-maintained,
            execute the SET INTEGRITY statement with the IMMEDIATE
            CHECKED option.

The suggested solution is a bit confusing here if you’re not dealing with a materialized query table. The suggested user response is not listed for plain, everyday tables.

How Tables Get Into a Check-Pending State

The most common way for a table to get into a check-pending state is for a LOAD … REPLACE to be performed on a table that has foreign key constraints defined.

There are also some cases where you manually put a table into a check pending state when adding generated columns or performing other actions – though in those cases, you’re likely to be aware of the check-pending state and the need to clear it.

Here are the official cases when a table may be placed into check pending by a LOAD operation from the IBM DB2 Knowledge Center:

• The table has table check constraints or referential integrity constraints defined on it.
• The table has generated columns and a V7 or earlier client was used to initiate the load operation.
• The table has descendent immediate materialized query tables or descendent immediate staging tables referencing it.
• The table is a staging table or a materialized query table.

To understand when tables are placed into a check-pending state, I’m using the DB2 LUW SAMPLE database.

The SALES table plays no role in any foreign key relationships. So if we load data into it, we do not get a check-pending state:

$ db2 "export to sales.ixf of ixf select * from sales"
SQL3104N  The Export utility is beginning to export data to file "sales.ixf".

SQL3105N  The Export utility has finished exporting "41" rows.


Number of rows exported: 41

$ db2 "load from sales.ixf of ixf replace into sales nonrecoverable"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file 
"/db2home/db2inst1/sales.ixf".

SQL3500W  The utility is beginning the "LOAD" phase at time "12/06/2015 
15:08:09.283448".

SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date 
"20151206", and time "150729".

SQL3153N  The T record in the PC/IXF file has name "sales.ixf", qualifier "", 
and source "            ".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "41" rows were read from the 
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "41".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "12/06/2015 
15:08:09.596726".


Number of rows read         = 41
Number of rows skipped      = 0
Number of rows loaded       = 41
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 41

select  rtrim(tabschema) || '.' || rtrim(tabname) as qual_tab
    , const_checked 
from syscat.tables 
where CONST_CHECKED like '%N%' 
    or status != 'N'
    or access_mode != 'F'
with ur;
QUAL_TAB                                                                                                                                                                                                                                                          CONST_CHECKED                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------

  0 record(s) selected.

You’ll notice that I get a backup pending warning because my sample database is using circular logging. But the table is not placed in a check-pending state because it does not meet the conditions for that.

However, if I do the same thing for a table with refferential integrity, I do get a check pending state. Note that the data I am loading into the table is exactly the data that is already there, so this state is completely independent of the data being used:

$ db2 "export to projact.ixf of ixf select * from projact"
SQL3104N  The Export utility is beginning to export data to file 
"projact.ixf".

SQL3105N  The Export utility has finished exporting "65" rows.


Number of rows exported: 65

$ db2 "load from projact.ixf of ixf replace into projact nonrecoverable"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file 
"/db2home/db2inst1/projact.ixf".

SQL3500W  The utility is beginning the "LOAD" phase at time "12/06/2015 
15:25:20.727176".

SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date 
"20151206", and time "152503".

SQL3153N  The T record in the PC/IXF file has name "projact.ixf", qualifier "",
and source "            ".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "65" rows were read from the 
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "65".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "12/06/2015 
15:25:20.936021".

SQL3500W  The utility is beginning the "BUILD" phase at time "12/06/2015 
15:25:20.941460".

SQL3213I  The indexing mode is "REBUILD".

SQL3515W  The utility has finished the "BUILD" phase at time "12/06/2015 
15:25:21.021448".


Number of rows read         = 65
Number of rows skipped      = 0
Number of rows loaded       = 65
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 65

$ db2 -tvf unchecked_query1.sql
select  rtrim(tabschema) || '.' || rtrim(tabname) as qual_tab
    , const_checked 
from syscat.tables 
where CONST_CHECKED like '%N%' 
    or status != 'N'
    or access_mode != 'F'
with ur;

QUAL_TAB                                                                                                                                                                                                                                                          CONST_CHECKED                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------
DB2INST1.PROJACT                                                                                                                                                                                                                                                  NYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

  1 record(s) selected.

In this case, the PROJACT table was placed into a check-pending state.

Identifying Tables in Check-Pending

There are several places to look for tables in a check-pending state. My favorite is the CONST_CHECKED column of SYSCAT.TABLES. If a table is in check-pending due to foriegn key constraints, then the first letter will be an N. If it is due to check constraints, then the second character will be N. The IBM DB2 Knowledge Center page on SYSCAT.TABLES explains the other possible values.

If the table is inaccessible due to an integrity state, the STATUS column will have a value other than N, and the ACCESS_MODE column will have a value other than F. So this query is really over-checking a bit, but it looks at all 3 columns that could indicate a problem:

select  rtrim(tabschema) || '.' || rtrim(tabname) as qual_tab
    , const_checked 
from syscat.tables 
where CONST_CHECKED like '%N%' 
    or status != 'N'
    or access_mode != 'F'
with ur;

Getting Tables Out of Check-Pending

To remove a table from a check-pending state, we need to first create an exception table, and then run a SET INTEGRITY statement to cause DB2 to apply all foreign key and check constraints and move exceptions to our exception table. Since LOADing data often affects more than one table, AND setting integrity for a table may put other tables in a check pending state, it is critical not to just address the one table you’re working with, but to examine the database for tables that need these actions. Because of this, I use SQL to write the SQL that I need to take these actions. It may seem like overkill in this scenario, but this is just the sample database, without much complexity.

Here is the SQL statement I use to generate the CREATE TABLE statements for exception tables:

select 'create table dba.except_' || rtrim(tabname) || ' like ' 
    || rtrim(tabschema) || '.' || rtrim(tabname) || ';' 
from syscat.tables 
where CONST_CHECKED like '%N%' 
    or status != 'N'
    or access_mode != 'F'
with ur;

I have that in a file and execute it with db2 -txf to get this in the example I’m using here:

create table dba.except_PROJACT like DB2INST1.PROJACT;

Or if it’s just one, I can also copy and paste at the command line. Execution of that looks like this:

$ db2 "create table dba.except_PROJACT like DB2INST1.PROJACT"
DB20000I  The SQL command completed successfully.

Obviously you can alter the naming here to match what works for you. I like to make it really obvious that a table is an exception table and like to put it in a separate schema to make it obvious that it’s a temporary table that I can go drop later.

After the exception table is created, you then need to generate the set integrity statement. It might be easier to create a set integrity statement for each table, but I like to do them all in one statement. The main reason here is that I’ve seen (in a WebSphere Commerce database) a case where there are circular foreign key relationships between two or three tables, and when that happens, all of the tables must be specified in the same set integrity statement. This means that if we specify all tables in set integrity pending in one statement, that kind of issue won’t trip us up.

Here’s the SQL I use to write the set integrity statement. Note that this has to be a bit complex to handle multiple tables. This SQL will only work on DB2 9.7 and higher due to the use of the LISTAGG function.

select 'set integrity for ' || listagg(rtrim(tabschema) || '.' || rtrim(tabname),', ') 
    || ' immediate checked for exception ' || listagg( 'in ' 
    || rtrim(tabschema) || '.' || rtrim(tabname) || ' use dba.except_' 
    || rtrim(tabname), ', ') || ';' 
from syscat.tables 
where CONST_CHECKED like '%N%' 
    or status != 'N'
    or access_mode != 'F'
with ur;

Again, I like to put that in a file and execute with db2 -txf and pipe it to a file. In the example we’re following here, the generated statement looks like this:

set integrity for DB2INST1.PROJACT immediate checked for exception in DB2INST1.PROJACT use dba.except_PROJACT;

If you changed the exception table naming in the previous statement, you’ll need to change it here, too. Executing that statement looks like this:

$ db2 "set integrity for DB2INST1.PROJACT immediate checked for exception in DB2INST1.PROJACT use dba.except_PROJACT"
SQL3601W  The statement caused one or more tables to automatically be placed 
in the Set Integrity Pending state.  SQLSTATE=01586

The SQL3601W is a fairly common warning message here. It tells us that setting integrity on this table caused other tables to be placed into a check-pending state. We now have to go through this process again to identify the tables and remove the check-pending state:

$ db2 -tvf unchecked_query1.sql
select  rtrim(tabschema) || '.' || rtrim(tabname) as qual_tab 
, const_checked 
from syscat.tables 
where CONST_CHECKED like '%N%' 
or status != 'N' 
or access_mode != 'F' 
with ur

QUAL_TAB                                                                                                                                                                                                                                                          CONST_CHECKED                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------
DB2INST1.EMPPROJACT                                                                                                                                                                                                                                               NYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

  1 record(s) selected.

$ db2 -txf unchecked_query.sql |tee rem_check_pending.sql
create table dba.except_EMPPROJACT like DB2INST1.EMPPROJACT;                                                                                                                                                                                                                                                                                                                                                                    

set integrity for DB2INST1.EMPPROJACT immediate checked for exception in DB2INST1.EMPPROJACT use dba.except_EMPPROJACT;   

$ db2 -tvf rem_check_pending.sql |tee rem_check_pending.out
create table dba.except_EMPPROJACT like DB2INST1.EMPPROJACT
DB20000I  The SQL command completed successfully.

set integrity for DB2INST1.EMPPROJACT immediate checked for exception in DB2INST1.EMPPROJACT use dba.except_EMPPROJACT
DB20000I  The SQL command completed successfully.

When you’re done, you’ll want to review the exception tables and see if there are any rows you need to address in them:

$ db2 list tables for all |grep EXCEPT_
EXCEPT_EMPPROJACT               DBA             T     2015-12-06-15.56.43.014510
EXCEPT_PROJACT                  DBA             T     2015-12-06-15.42.45.996397
$ db2 "select count(*) from DBA.EXCEPT_PROJACT"

1          
-----------
          0

  1 record(s) selected.

$ db2 "select count(*) from DBA.EXCEPT_EMPPROJACT"

1          
-----------
          0

  1 record(s) selected.

In this example, I did the LOAD … REPLACE with only the data that was already in the table, so there are no exceptions to worry about.

Please be aware that depending on the data size and the constraints to be checked, that the set integrity command can take quite a long time – longer even than the LOAD command itself took. When testing the duration of a LOAD be sure to always include testing of the time to run the SET INTEGRITY commands.

Script It

If you’re going to do this very often at all, it makes sense to write a script – in KSH, PERL, POWERSHELL or your language of choice – that will loop through this process for you. Especially if you’re working with a database with a web of referential integrity, you want a script that will look for any tables in check pending and repetitively work through this until there are no tables left in check-pending status.