Can we execute SQLs using JCL.

[pinball]

Hi,

Can we SQLs using JCL instead of QMF or SPUFI? Could you please share a JCL to do this, if it is possible.

Thanks for any help.

[zprogrammer]

There are quiet a few programs and utility that could be executed by JCL

For ex:
LOAD,UNLOAD,DSNTIAUL,DSNTIAD,DSNTEP2

Google for more information on each one of them

[Anuj Dhawan]

Below listed JCLs are some of the example of executing SQL queries in JCL:

Suppose you want to unload the rows for department D01 from the project table. You can fit the table specification on one line, and you do not want to execute any non-SELECT statements, so you do not need the SQL parameter. Your invocation looks like this:

Code: Select all

     //UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
     //SYSTSPRT DD  SYSOUT=*
     //SYSTSIN  DD  *
      DSN SYSTEM(DSN)
      RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
            LIB('DSN710.RUNLIB.LOAD')
     //SYSPRINT DD SYSOUT=*
     //SYSUDUMP DD SYSOUT=*
     //SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
     //            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
     //            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
     //SYSIN    DD *
     DSN8710.PROJ WHERE DEPTNO='D01'

Now suppose that you also want to use DSNTIAUL to do these things:

• Unload all rows from the project table
  Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
  Lock both tables in share mode before you unload them
  For these activities, you must specify the SQL parameter when you run DSNTIAUL. Your DSNTIAUL invocation looks like this:

Code: Select all

     //UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
     //SYSTSPRT DD  SYSOUT=*
     //SYSTSIN  DD  *
      DSN SYSTEM(DSN)
      RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') -
            LIB('DSN710.RUNLIB.LOAD')
     //SYSPRINT DD SYSOUT=*
     //SYSUDUMP DD SYSOUT=*
     //SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
     //            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
     //            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
     //SYSIN    DD *
     LOCK TABLE DSN8710.EMP IN SHARE MODE;
     LOCK TABLE DSN8710.PROJ IN SHARE MODE;
     SELECT * FROM DSN8710.PROJ;
     SELECT * FROM DSN8710.EMP
       WHERE WORKDEPT LIKE 'D%'
       ORDER BY EMPNO;

These examples are taken from DSNTIAUL documentation.

[pinball]

Below listed JCLs are some of the example of executing SQL queries in JCL:

Suppose you want to unload the rows for department D01 from the project table. You can fit the table specification on one line, and you do not want to execute any non-SELECT statements, so you do not need the SQL parameter. Your invocation looks like this:

Code: Select all

     //UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
     //SYSTSPRT DD  SYSOUT=*
     //SYSTSIN  DD  *
      DSN SYSTEM(DSN)
      RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
            LIB('DSN710.RUNLIB.LOAD')
     //SYSPRINT DD SYSOUT=*
     //SYSUDUMP DD SYSOUT=*
     //SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
     //            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
     //            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
     //SYSIN    DD *
     DSN8710.PROJ WHERE DEPTNO='D01'

Now suppose that you also want to use DSNTIAUL to do these things:

• Unload all rows from the project table
  Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
  Lock both tables in share mode before you unload them
  For these activities, you must specify the SQL parameter when you run DSNTIAUL. Your DSNTIAUL invocation looks like this:

Code: Select all

     //UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
     //SYSTSPRT DD  SYSOUT=*
     //SYSTSIN  DD  *
      DSN SYSTEM(DSN)
      RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') -
            LIB('DSN710.RUNLIB.LOAD')
     //SYSPRINT DD SYSOUT=*
     //SYSUDUMP DD SYSOUT=*
     //SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
     //            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
     //            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
     //SYSIN    DD *
     LOCK TABLE DSN8710.EMP IN SHARE MODE;
     LOCK TABLE DSN8710.PROJ IN SHARE MODE;
     SELECT * FROM DSN8710.PROJ;
     SELECT * FROM DSN8710.EMP
       WHERE WORKDEPT LIKE 'D%'
       ORDER BY EMPNO;

These examples are taken from DSNTIAUL documentation.

Thanks for the detailed examples. I have identified a job in use at our company and they also use DSNTIAUL. Very much similar to what you have showed.

[Anuj Dhawan]

Nice to know tht we had been helpful. Appreciate the feedback.

[Chandan Yadav]

One more example with DSNTEP2 if you want the query output in sysout
Thanks,
Chandan

Code: Select all

//SQL  EXEC PGM=IKJEFT1A,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
      DSN SYSTEM(<DB2SUBSYSTEM>)
      RUN  PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
            LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN    DD *
     DSN8710.PROJ WHERE DEPTNO='D01';

[pinball]

Thanks Chandan.