Can DB2 commands be executed in a Batch JCL

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Can DB2 commands be executed in a Batch JCL, Commands like -DIS etc. If so can someone post a example.

ofer71 · Posted: Thu Sep 07, 2006 11:34 pm

Since you can invoke any DB2 command under TSO, I assume you can issue it under BATCH TSO (For example: IKJEFT01 or from within REXX).

Have you ever noticed that whenever you run a batch DB2 program with the RUN command, you are actually running a DB2 command (DSN)?

O.

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

I tried using IKJEFT01, and this did not work. SQL -104 is recieved.

priyesh.agrawal · Posted: Sat Sep 09, 2006 3:43 am

Paste here complete step alongwith SQL Stmt you are running.

-104 denotes a syntax error.

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Here is the information:

//DYNPROC PROC
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD DUMMY
//SYSIN DD DUMMY
// PEND
//S001 EXEC DYNPROC
//SYSTSIN DD *
DSN SYSTEM(DBXX)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2)
END
//SYSIN DD *
DISPLAY DATABASE(SGPADX01) SPACENAME(*)
/*

SYSPRINT MESSAGES:
***INPUT STATEMENT:
DISPLAY DATABASE(SGPADX01) SPACENAME(*)
SQLERROR ON DISPLAY COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: IS <HEXSTRING> <CHARSTRING>
<GRAPHSTRING>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 17 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000011' X'00000000' SQL DIAGNOSTIC
INFORMATION

Also i tried with -DISPLAY and recieved same message.

ofer71 · Posted: Mon Sep 11, 2006 9:28 pm

DB2 commands always start with a dash ("-").

O.

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Same result with -DISPLAY also

***INPUT STATEMENT:
-DISPLAY DATABASE(SGPADX01) SPACENAME(*)
SQLERROR ON -DISPLAY COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ( END SET CALL DROP FREE HOLD
LOCK OPEN PARM ALTER BEGIN CLOSE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 1 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000001' X'00000000' SQL DIAGNOSTIC
INFORMATION

ofer71 · Posted: Mon Sep 11, 2006 11:27 pm

No, no, no.... You already have a DB2 command in your code (RUN). Just remove the RUN line, and put the DISPLAY line instead. You don't need SYSIN (it's already DUMMYied before).

O.

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Thanks, Job worked.

ajay_diaz · New User Joined: 12 Sep 2005 Posts: 28

When I am trying the JCL below :

//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSIN DD DSN=R1LTPB.AJIT.QUERIES.TEMP,DISP=SHR
//SYSTSIN DD *
DSN SYSTEM(DB1A)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPSP) -
END
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
/*

I am getting follwong error message in SYSTSPRT

READY
DSN SYSTEM(DB1A)
IKJ56500I COMMAND DSN NOT FOUND
READY
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPSP) END
IKJ56712I INVALID KEYWORD, PLAN(DSNTEPSP)
IKJ56712I INVALID KEYWORD, END
READY
END

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Try changing PLAN(DSNTEPSP) to PLAN(DSNTEP2).

And here are some examples:

For what you are trying:

//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBxx)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2)
END
//SYSIN DD *
Give insteam SQL or Modify to give DSN
/*

After help from Forum i used following for executing DB commands:
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBxx)
-DISPLAY DATABASE(XXXXDB01) SPACENAM(*)
END
//SYSIN DD DUMMY

Also following can be used for executing SQL queries
Where output of SQL is in table download format (Raw data):

//STP0100 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSREC00 DD DSN=Give dataset or write to sysout for query result
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE),DCB=(MODEL.DSCB1,BLKSIZE=0)
//SYSPUNCH DD DUMMY
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBxx)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
END
/*
//SYSIN DD *
Select statement instream or modify to give dataset
/*
//*