Tuesday, July 9, 2013

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. 

13 comments:

  1. Thanks for sharing this informative article in loading and off loading. I am sure it will be of help to a lot of online users. I hope we will be continually updated with new information by the authors especially on current and emerging issues in our society. In case you are in need of urgent but accurate programming services, feel free to hire our editors by clicking on Proofreading Website.

    ReplyDelete
  2. Nice explanation I have read so far... also, it would be great if you can give some inputs on the SYSPUNCH and SYSREC and their role in LOAD and UNLOAD utilities....
    Cheers !!

    ReplyDelete
  3. Many residents, who are shifting from one home to another, wish to hire packers and movers in Vadodara. These packers and movers Vadodara offer their services at a less expensive rate as compared to their partners. What's more, that is justifiable; it is human propensity all things considered! Also, you would likewise be answerable to the missus if you surpass the financial limit. Nonetheless, just like the case with most services and products which are less expensive aren't always better. Things are the same on account of house moving service specialists. So we are here with a couple of reasons why you shouldn't settle on less expensive movers and packers Vadodara for your home moving needs.

    ReplyDelete
  4. Starway International Packers and Movers
    We take pleasure to introduce ourselves as one of the leading team of packers and movers in India.

    For Domestic & International packers and movers across the Globe. Being a professional & experienced packers, Star Way International Packers And Movers are here to shift your valuable & precious house hold goods to your destination - safely and in time.

    By different carrier vehicles, We move your household items to reach the destination. We have huge warehouses to store your goods if necessary.

    On your confirmation, our supervisor along with packers will Come and inspect the Goods and give the estimate for packing moving transportation Loading and unloading unpacking rearranging the House hold Goods / Corporate relocation / Commercial goods as per your requirement we will quote the quotation. We have good experience in this field we do proper and neat work orderly, each & every item in a safe manner. Each item will be marked and a inspection list will be prepared. Utmost care will be taken for all the Goods.

    ReplyDelete
  5. Your blogs looks good and visit our website for more details
    Loading and Unloading | Moving masters

    ReplyDelete