Saturday, March 19, 2016

DB2 unload utility using the program DSNTIAUL

IBM provides two methods of unloading data from a DB2 table. One is DSNTIAUL program that is run under IKJEFT01 or IKJEFT1A, or 1B and the other being  DSNUTILB program. DSNUTILB is a fairly new and is considered IBM’s Fast Unload Utility. Each has unique functionality that you may want to explore prior to choosing one. 

Outlined below are functions of DSNTIAUL process. 

1. DSNTIAUL Program: The DSNTIAUL program allows two type of unloads to be invoked against a DB2 table. When invoking DSNTIAUL you may specify an entire table unload or a selective SQL unload. The key DD statements that are being used are the SYSIN and the SYSTSIN. Both types of unloads are being run under IKJEFT1B.

Basic JCL example:
//UNLOAD EXEC PGM=IKJEFT1B, DYNAMNBR=20
//STEPLIB DD DSN=SYSDB2.DQA0.SDSNLOAD, DISP=SHR
// DD DSN=SUBSYS.DB2.DSNEXIT, DISP=SHR
//*************************************************************
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSREC00 DD DSN=userid.tablerecords.dataset,
//          DISP=(,CATLG,DELETE),
//       UNIT=SYSDA,SPACE=(CYL,(300,300),RLSE)
//*
//SYSPUNCH DD DSN=user-id.tablelayout.dataset,
//     UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),DISP=(,CATLG,DELETE)
//SYSTSIN DD *
SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) -
LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’)
END
//SYSIN DD *
DBCREATOR.TABLENAME 
/*
//*


For selective Unload, like when you are using a where clause or using select statements (like SELECT * from user_table;)  we need to use PARM('SQL') in SYSTSIN statement; else you may endup with SQLCODE -104.
Your SYSTSIN would look like

//SYSTSIN DD *
SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL)
         PLAN (DSNTIAUL) -
LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’) PARM('SQL')
END 
//SYSIN DD *
SELECT * FROM schema.Tablename ;

1 comment:

  1. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete