Sample JCL for UNLOAD & LOAD utility for DB2 TABLE - What is CHECK & COPY PENDING

Below are the Sample JCLs for unload and load utility in DB2.
Loading and Unloading of data from DB2 TABLE

The below JCL shows how to UNLOAD data from one table to a dataset using IKJEFT01.

//STEP02   EXEC PGM=IKJEFT01,DYNAMNBR=50
//SYSTSPRT DD     SYSOUT=$
//SYSPRINT DD     SYSOUT=$
//SYSUDUMP DD  SYSOUT=$
//SYSTSIN  DD DSN=TEST.PROCLIB(UNLD),DISP=SHR
//SYSPUNCH DD DSN=TEST.TABLE.STRTCTURE,
//                                       DISP=(NEW,CATLG,DELETE),
//                                       UNIT=DISK,SPACE=(CYL,(1,1),RLSE)
//SYSREC00 DD DSN=TEST.TABLE.LOAD,
//                                     DISP=(,CATLG,DELETE),
//                                     UNIT=CART,
//                                     DCB=(SYS3.DSCB),LABEL=EXPDT=99000
//SYSIN    DD *
SELECT *  FROM TST.CLIENT ;
/*
//*
SYSIN DD* contains the condition for the SQL query we want to use for fetching the data.
SYSTSIN  DD DSN=TEST.PROCLIB(UNLD) ->  This will contain the details like DB2 level(test or prod) ie,from where we are dowloading. Also the utility, plan , and the library used to pickup the utility like below.SYSREC00 will contain the actual unloaded records from DB2.

The above JCL  one loads the table CLIENT into TAPE. For loading to DISK use below.

//SYSREC00 DD DSN=TEST.TABLE.LOAD,
//                                     DISP=(,CATLG,DELETE),
//                                     UNIT=DISK,SPACE=(CYL,(100,100),RLSE)

Load the downlaoded data from dataset to test DB2 Table
 
This JCL shows how we can upload the data from Flat File back to DB2 Table.

//LOADTB    EXEC DB2LOAD,LDNM=TBLOAD1,DB2=DB2T
//LOAD.SYSREC00 DD  DSN=TEST.TABLE.LOAD,DISP=SHR
//*
//LOAD.SYSIN    DD  DSN=TEST.TABLE.STRTCTURE,DISP=SHR
//*

DB2LOAD is the utility used to execute the load function.
LBNM=TBLOAD1 ::  We can use any name here inplace of TBLOAD1
DB2=DB2T:: indicates a test database region.
TEST.TABLE.LOAD  contains the data to be loaded into the table which was downloaded in the unload step.
LOAD.SYSIN contains the table structure along with additional parameters to load the table.
like
LOAD DATA REPLACE LOG NO ENFORCE NO INDDN SYSREC00
INTO TABLE TST.CLIENT.

NOTE: We Get the table structure in the dataset with DD name SYSPUNCH while we unload the data in previous step

CHECK PENDING AND COPY PENDING:

Check pending is a tablespace/index status set when DB2 has reason to believe that an object might be in an inconsistent state. Usually happens as  a result of running LOAD on a tablespace containing a table that has Referential Integrity constraints, but  that was  not enforced during the LOAD OPERATION (ENFORCE NO). (like we used in above example). Recovering and RI tablespace to a previous point in time can also set it. None of this is directly related to the design of the application.If CHECK PENDING is set you can run the CHECK utility to instruct DB2 to verify that the data is consistent and switch off the pending status.
 To remove the Checkpending condition we need to  execute the following commands on tablespace
for all the affected table space.

REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLA00DB.TBLB01TS
NOCHECKPEND
REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLB00DB.TBLB02TS
NOCHECKPEND

COPYPENDING
Any table space will be in copy pending status after  load is performed on the DB2 table,
with log no parameter
or
if load is performed without taking an image copy
or
if the job abend during load
or
if NOCOPYPEND parameter is not specified in syspunch dataset 
To remove the Copypending  condition we need to  execute the following commands on tablespace for all the affected table space. 

REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLA00DB.TBLB01TS
NOCOPYPEND
REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLB00DB.TBLB02TS
NOCOPYPEND 

Is There any way to check which operation has put the table on pending status?? 

Yes,We can know this from SYSIBM.SYSCOPY Table.
If we run a query against this table, the ULITY values can help us in determining the desired operation.
example:  I want to check what last operation was performed by my id or any id.
So, i Can query the SYSIBM.SYSCOPY Table like below
SELECT DSNAME SPACE, ICTYPE AS UTILITY, TIMESTAMP
FROM SYSIBM.SYSCOPY WHERE AUTHID='my-id'
ORDER BY TIMESTAMP
If we check the output, check for UTILITY  values

UTILITY Can have these values like 'A', 'S' .. like below.  

A-Alter
B-REBUILD IX
D-CHECKDATA LOG(NO)
F-full IC
I-Incremental IC
P-partial recovery point
Q-Quiesce
R-LOAD REPLACE LOG(YES)
S-LOAD REPLACE LOG(NO)
T-terminated utility
W-REORG LOG(NO)
X-REORG LOG(YES)
Y-LOAD RESUME LOG(NO)
Z-LOAD RESUME LOG(YES)

Thus we can check the operation last performed. 

Common errors encountered while loading a table

UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'0B37'

Check the spool for which dataset it is abending with SB37.
Then we need to change the UNIT parameter.
If it is using SYSDA, we can use multivolume coding the SYSDA like
UNIT=(SYSDA,59),
SPACE=(CYL,(200,200),RLSE)

DSNURSIX - KEY COUNT INCONSISTENT FOR SORT-RD PHASE,NUMBER OF KEYS EXPECTED =72379826, NUMBER OF KEYS PROCESSED=8681906

DSNUGSOR - SORT PHASE STATISTICS - 
NUMBER OF RECORDS=0
ELAPSED TIME=00:00:00
 DSNURSIX - KEY COUNT INCONSISTENT FOR SORT-RD PHASE, NUMBER OF KEYS
PROCESSED=0
DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E4030A

Resolution:
1. Increase the SORT datafiles used internally in the load utility.
2. Terminate the UTLITY
How to terminate?
use the command -TERM UTIL(UTIL-NAME)
What is this UTIL-NAME?
Its just the LDNM name we use in our load job. In the above example, the utility name is
TBLOAD1 
where to use this command?
Go to  DB2I PRIMARY OPTION MENU -> option 7(DB2 COMMANDS)
3. Restart the load job

ERROR LOADING INDEX, ERROR = X'00E40322' INDEX = Bth00.D4xx30X1
DSNUGBAC -RESOURCE UNAVAILABLE
      REASON 00D70014
      TYPE 00000220
      NAME TEX2.DSNDBC.DXXXXDB.D890000X1.I0001.A001
UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40322'

Explnation: The load phase of the load job worked, but the rebuilding index failed.
00D70014 - An attempt to extend a data set failed, because a problem was detected in media manager services.

Resolution:
1.Check the allocations for the index of the table.It must be too small.
We need to increase it, Both primary and seconday quantity for all the index available for the table.
2. Terminate the utility using the same command  -TERM UTIL(UTIL-NAME)
3. restart the load job again.