DB2 Unload and Load JCL using IBM utility

40 pts.
Tags:
DB2
IBM Mainframe
JCL
Hi,

Can anyone provide me a JCL to unload and load DB2 tables using IBM utility.

I'm new to DB2 and need to unload the Syspunch dataset for a table, as I need to load a the table with data.

I just only have the file with the data to be loaded.

So I need to get the syspunch file created first and then using it need to load the data. How can I create the Syspunch data sate using unload? and then load the table.

 

 



Software/Hardware used:
IBM mainframe.
ASKED: January 15, 2010  2:47 AM
UPDATED: April 19, 2013  12:32 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The first place you should look at the manuals and your coworkers. You need some education. You cannot be expected to know how to use DB2 just off the street. DB2 on z/OS is about the best there is, but it is complicated. Go to your boss and explain that you do not know DB2. Go to IBMs web site and download and print the manuals and READ them. The best manual (and probably the least used) is the Appl Programming and SQL Guide. But for utilities, you need the Utilities Guide.

But to get you started… here are sample UNLOAD and LOAD…

//** DB2 UNLOAD UTILITY UNLOAD TABLE(S) TO SEQ DATA SET
//** (DNSTIAUL) BUILD REQUIRED LOAD STMNT FOR DB2 LOAD UTI
//**
//** OUTPUT RECORDS MUST BE FIXED. MAKE THEM BIG ENOUGH.
//**
//*****
//UNLOAD1 EXEC PGM=IKJEFT01 INVOKE TSO
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DSN=PRATTST.CTL(TSLPXXX), LOAD STMNTS WRITTEN HERE
// DISP=(SHR)
//SYSREC00 DD DSN=PRATTST.UNLOAD1, FIRST TABLE WRITTEN HERE
// DISP=(NEW,CATLG,CATLG),UNIT=SYSDA,
// SPACE=(10000,(100,100),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=10000)
//SYSREC01 DD DSN=PRATTST.UNLOAD2, SECND TABLE WRITTEN HERE
// DISP=(NEW,CATLG,CATLG),UNIT=SYSDA,
// SPACE=(10000,(100,100),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=10000)
//SYSREC02 DD DSN=PRATTST.UNLOAD3, THIRD TABLE WRITTEN HERE
// DISP=(NEW,CATLG,CATLG),UNIT=SYSDA,
// SPACE=(10000,(100,100),RLSE),
// DCB=(RECFM=FB,LRECL=100,BLKSIZE=10000)
//SYSTSIN DD * TERMINAL INPUT
PROFILE PREFIX(PRATTST)
DSN SYSTEM(DSNT)
RUN PROGRAM(DSNTIAUL) LIB('DB2T.RUNLIB.LOAD') PLAN(DSNTIAUL)
END
//SYSIN DD * SYSIN NAMES THE TABLES
UISTDB.UIS_BDA_T
STTTDB.STT_CIT_CHP_T
SYSIBM.SYSPLAN
/*
//
//STEP01 EXEC PGM=DSNUTILB,PARM='DSNT,DB2910',COND=(EVEN)
//*
//* THE DB2 UTILIY ID ABOVE WILL NEED TO BE CHANGED
//*
//*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSERR DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUT1 DD DSN=&&SYSUT1,DISP=(MOD,DELETE,DELETE),
// SPACE=(CYL,(10,10))
//SORTOUT DD DSN=&&SORTOUT,DISP=(MOD,DELETE,DELETE),
// SPACE=(CYL,(10,10))
//SYSMAP DD DSN=&&SYSMAP,DISP=(MOD,DELETE,DELETE),
// SPACE=(CYL,(10,10))
//LOADIN DD DSN=IBRN.IBRIAA.MUF.EXTRACT,DISP=SHR
//**
//** THE DD STMNT ABOVE IS THE INPUT DATASET
//** IT IS A SEQUENTIAL DATASET
//** THE PARAMETER CARDS FOLLOWING NEED TO BE TAILORED FOR
//** EACH DIFFERENT LOAD. THE PARAMETERS SPECIFIED HERE MAY
//** NOT BE SUITABLE FOR YOUR TABLE.
//**
//** IN THIS EXAMPLE THE INPUT DATA EXACTLY MATCHES THE TABLE
//** WITH THE EXECPTION THAT THE INPUT IS "DB2 EXTERNALIZED"
//** FORMAT (EXAMPLE WOULD BE A DATE).
//**
//** A BIT ABOUT THE PARAMETERS:
//** REPLACE MEANS OPEN THE TABLESAPCE OUTPUT
//** LOG NO MEANS THAT AN IMAGE COPY IS REQUIRED BEFORE UPDATE
//** ENFORCE NO MEANS A CHECK DATA IS REQUIRED BEFORE ACCESS
//**
//**
//SYSIN DD *
LOAD DATA INDDN LOADIN <-- THE INPUT DD NAME
REPLACE <-- REPLACE ALL ROWS IN TBL SP
LOG NO <-- DO NOT LOG THE LOADED ROWS
WORKDDN (SYSUT1,SORTOUT) <-- WORK FILE DD NAMES
ENFORCE NO <-- DO NOT LOOK AT R.I. CONTRAIN
ERRDDN SYSERR <-- DDNAME OF ERRORS
MAPDDN SYSMAP <-- DDNAME OF MORE ERRORS
INTO TABLE XXXTDB.TIBR900_AGY_AST <-- TBL NAME
(
CDE_DAT_SRC CHAR(2) , <-- MATCH
NUM_IDT_IBR_SEQ DECIMAL ZONED , -- THE
NUM_ORI_AST CHAR(9) , -- TABLE
NUM_PID_ADD DECIMAL, -- WITH
NUM_PID_ADD_LC CHAR(6), -- THE
DTE_ADD_REC CHAR(8), -- INPUT
DTE_LC_REC CHAR(8), -- FILE
DTS_LC_REC TIMESTAMP EXTERNAL(26),
NUM_NTE INTEGER
)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • elchami743
    //********************************************************
    //* DESCARGA TABLA UGDTEVA - EVALUACION RESOLUCIÓN       
    //********************************************************
    //UGH30120 EXEC UNLOAD,COND=(4,LT),                      
    //   PARM='DB2P,PUGDCH30UGH30120,NEW/RESTART'            
    //DSSPRINT DD  SYSOUT=*                                  
    //SYSPRINT DD  SYSOUT=*                                  
    //SYSUDUMP DD  SYSOUT=*                                  
    //SYSABEND DD  SYSOUT=*                                  
    //UTPRINT  DD  SYSOUT=*                                  
    //SYSCNTL  DD  SYSOUT=*                                  
    //SYSOUT   DD  SYSOUT=*                                  
    //SYSREC   DD  DSN=COBP.UG.FJSD1.UGDCH30.UGH30120.UGEVA, 
    //             DISP=(NEW,CATLG,DELETE),BLKSIZE=0,        
    //             SPACE=(CYL,(100,50,),RLSE),UNIT=SYSDA     
    //SYSIN    DD  *                                         
      UNLOAD SHRLEVEL CHANGE CONSISTENT NO                   
      SELECT  ENTIOFI,CUENTA,INDEVAL,INDELERE,VALOR_APROBADO,
              TIMESTAMP                                      
      FROM  PADB2.UGDTEVA                                    
      ORDER BY ENTIOFI, CUENTA
    10 pointsBadges:
    report
  • MBDINOSAUR
    WOW. REALLY. Nice answer. MBDINOSAUR.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following