Mainframe, MVS and zOS Discussion

     //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'

• 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:

     //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;

Anuj Dhawan wrote: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.