CL using SQL
CL allows multiple ways to incorporate SQL into jobs.
Simplify Command Execution with CL
CL offers a commmand RUNSQLSTM, which is based on source file data. This is simple, but if the SQL string is predictable, without extensive variables, it is simple and effective. In V7.1 of the OS IBM introduced the RUNSQL command allowing users to run a single SQL statement. It had the same limitations as RUNSQLSTM; non-SELECT statements. INSERT, UPDATE, DELETE, DROP and CREATE may be executed with RUNSQL, but not the SELECT.
However, you can create a version of the RUNSQL statement that can make use of the SELECT statement. See the example below. This program has a user-created command that executes an SQL statement generating an output file based on data selected.
PGM
DCLPRCOPT DFTACTGRP(*NO) ACTGRP(TRACE) +
BNDSRVPGM((ISI000SV))
DCL VAR(&IMPFOLDER) TYPE(*CHAR) LEN(200)
DCL VAR(&ARCFOLDER) TYPE(*CHAR) LEN(200)
DCL VAR(&MSGKEY) TYPE(*CHAR) LEN(4)
DCL VAR(&PGMNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&FUNCTION) TYPE(*CHAR) LEN(25)
DCL VAR(&PROGRAM) TYPE(*CHAR) LEN(15)
DCL VAR(&SENDER) TYPE(*CHAR) LEN(80)
DCL VAR(&FILEPATH) TYPE(*CHAR) LEN(200)
DCL VAR(&FILENAME) TYPE(*CHAR) LEN(25)
DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1)
DCL VAR(&JOBDATE) TYPE(*CHAR) LEN(6) /* sac01 */
DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(8)
DCL VAR(&LIBRARY) TYPE(*CHAR) LEN(10) +
VALUE('*LIBL')
DCL VAR(&EXCEPTION) TYPE(*CHAR) LEN(25) /* sac01 */
DCL VAR(&DOCUMENT) TYPE(*CHAR) LEN(15) /* sac01 */
DCL VAR(&EXPFOLDER) TYPE(*CHAR) LEN(200) /* sac01 */
DCL VAR(&ERRPATH) TYPE(*CHAR) LEN(200) /* sac01 */
DCL VAR(&ERRNAME) TYPE(*CHAR) LEN(17) /* sac01 */
DCL VAR(&ERRFOLDER) TYPE(*CHAR) LEN(200) /* sac02 */
DCL VAR(&SCANREC) TYPE(*DEC) LEN(10 0)
DCL VAR(&XREFREC) TYPE(*DEC) LEN(10 0)
DCL VAR(&POS) TYPE(*UINT) LEN(2)
DCL VAR(&SUBJECT) TYPE(*CHAR) LEN(50) /* sac02 */
dcl var(&message) type(*char) len(80) /* sac02 */
dcl var(&reason) type(*char) len(80) /* sac02 */
dcl var(&ERROR) type(*LGL) len(1) /* sac02 */
RTVJOBA TYPE(&JOBTYPE) DATE(&JOBDATE)
RTVNETA SYSNAME(&SYSNAME)
/*-------------------------------------------------------------------*/
/* Get the program name */
/*-------------------------------------------------------------------*/
SNDPGMMSG MSG(' ') TOPGMQ(*SAME) MSGTYPE(*INFO) +
KEYVAR(&MSGKEY)
RCVMSG PGMQ(*SAME) MSGTYPE(*INFO) RMV(*YES) +
SENDER(&SENDER)
CHGVAR VAR(&PGMNAME) VALUE(%SST(&SENDER 56 10))
CHGVAR VAR(&PROGRAM) VALUE(&PGMNAME)
/*-------------------------------------------------------------------*/
/* Get the folder where the data is located */
/*-------------------------------------------------------------------*/
CHGVAR VAR(&PROGRAM) VALUE(&PGMNAME)
CHGVAR VAR(&FUNCTION) VALUE('IMPFOLDER')
CALL PGM(ISI005RP) PARM(&PROGRAM &FUNCTION &IMPFOLDER)
CHGVAR VAR(&FUNCTION) VALUE('ARCFOLDER')
CALL PGM(ISI005RP) PARM(&PROGRAM &FUNCTION &ARCFOLDER)
CHGVAR VAR(&FUNCTION) VALUE('ERRFOLDER') /* sac02 */
CALL PGM(ISI005RP) PARM(&PROGRAM &FUNCTION +
&ERRFOLDER)
/*-------------------------------------------------------------------*/
/* Read the files in the folder and move them to archive */
/*-------------------------------------------------------------------*/
LIST: CALL PGM(ISI010RP) PARM(&IMPFOLDER)
READ: DOUNTIL COND(&FILEPATH *EQ '*****')
CALL PGM(ISI020RP) PARM(&FILEPATH)
/*-------------------------------------------------------------------*/
/* Copy the current file to the mail trace file */
/*-------------------------------------------------------------------*/
STRCOPY: IF COND(&FILEPATH *NE '*****') THEN(DO)
CLRPFM FILE(ISIIMBWF)
DLTF FILE(QTEMP/DUPSCANS) /* sac01 */
MONMSG MSGID(CPF0000)
DLTF FILE(QTEMP/NOPOXREF)
MONMSG MSGID(CPF0000)
CHGVAR VAR(&SCANREC) VALUE(0)
CHGVAR VAR(&XREFREC) VALUE(0)
CPYFRMIMPF FROMSTMF(&FILEPATH) TOFILE(ISIIMBWF) +
RCDDLM(*LF) STRDLM(*NONE) FLDDLM('|') +
RPLNULLVAL(*FLDDFT)
EXECSQL STMT('SELECT +
count(*),IMZIPP,IMROUT,IMZIPC,imddtm FROM +
ISIimbwf GROUP BY +
imzipp,imrout,imzipc,imddtm HAVING +
count(*) > 1') OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/DUPSCANS)
RTVMBRD FILE(DUPSCANS) NBRCURRCD(&SCANREC)
IF COND(&SCANREC *GT 0) THEN(DO)
CALLSUBR SUBR(EXCEPTION)
GOTO CMDLBL(ENDCOPY)
ENDDO
EXECSQL STMT('select distinct aa.imdesc, bb.uspcod +
from ISIimbwf aa left join ISIpocpf bb +
on aa.imdesc = bb.uspdsc where +
bb.uspcod is null') OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/NOPOXREF)
RTVMBRD FILE(NOPOXREF) NBRCURRCD(&XREFREC)
IF COND(&XREFREC *GT 0) THEN(DO)
CALLSUBR SUBR(EXCEPTION)
GOTO CMDLBL(ENDCOPY)
ENDDO
MOV OBJ(&FILEPATH) TODIR(&ARCFOLDER)
MONMSG MSGID(CPFA0A0) EXEC(DO)
CHGVAR VAR(&SUBJECT) VALUE(' ')
CHGVAR VAR(&message) VALUE(' ')
CHGVAR VAR(&reason) VALUE(' ')
CALLSUBR SUBR(ARCHIVESR)
ENDDO
CALL PGM(ISIG071RP)
/*-------------------------------------------------------------------*/
/* Move the data file to archive */
/*-------------------------------------------------------------------*/
ARCHIVE:
ENDCOPY: ENDDO
ENDREAD: ENDDO
TERMINATE:
RETURN
/*-----------------------------------------------------------------*/
/* subroutine added to manage file exceptions */
/*-----------------------------------------------------------------*/
ERRFILE: SUBR SUBR(EXCEPTION)
CHGVAR VAR(&DOCUMENT) VALUE('POLICY')
CHGVAR VAR(&EXCEPTION) VALUE('EXCEPTION')
CALL PGM(ISI005RP) PARM(&DOCUMENT &EXCEPTION +
&EXPFOLDER)
MOV OBJ(&FILEPATH) TODIR(&EXPFOLDER)
CHGVAR VAR(&DOCUMENT) VALUE('ISIFTP')
CHGVAR VAR(&EXCEPTION) VALUE('OUTBOUNDFOLDER')
CALL PGM(ISI005RP) PARM(&DOCUMENT &EXCEPTION +
&ERRFOLDER)
CHGVAR VAR(&POS) VALUE(%SCAN('.7640' &FILEPATH))
CHGVAR VAR(&ERRNAME) VALUE(%SST(&FILEPATH &POS 18))
IF COND(&SCANREC *GT 0) THEN(DO)
CHGVAR VAR(&ERRFOLDER) VALUE(&ERRFOLDER *TCAT +
'DUPSCANS' *TCAT &ERRNAME *TCAT '.TXT')
CPYTOIMPF FROMFILE(DUPSCANS) TOSTMF(&ERRFOLDER) +
MBROPT(*REPLACE) STMFCCSID(1252) +
RCDDLM(*CRLF) STRDLM(*NONE) FLDDLM('|')
ENDDO
IF COND(&XREFREC *GT 0) THEN(DO)
CHGVAR VAR(&ERRFOLDER) VALUE(&ERRFOLDER *TCAT +
'NOPOXREF' *TCAT &ERRNAME *TCAT '.TXT')
CPYTOIMPF FROMFILE(NOPOXREF) TOSTMF(&ERRFOLDER) +
MBROPT(*REPLACE) STMFCCSID(1252) +
RCDDLM(*CRLF) STRDLM(*NONE) FLDDLM('|')
ENDDO
ENDSUBR
/*==================================================================*/
/* sac02 - move file to archive */
/*==================================================================*/
SUBR SUBR(ARCHIVESR)
RETRY:
CHGVAR VAR(&ERROR) VALUE('0')
MOV OBJ(&FILEPATH) TODIR(&ARCFOLDER)
MONMSG MSGID(CPFA0A0) EXEC(DO)
CHGVAR VAR(&ERROR) VALUE('1')
CALL PGM(ISI305RP) PARM(&FILEPATH &ARCFOLDER)
ENDDO
IF COND(&ERROR) THEN(GOTO CMDLBL(RETRY))
CHGVAR VAR(&SUBJECT) VALUE('Return file error +
occurred')
CHGVAR VAR(&MESSAGE) VALUE('Error occurred when +
attempting to move to the archive +
folder.')
CHGVAR VAR(&REASON) VALUE('Check the job log of +
ISIIMBTRC for details.')
CALL PGM(ISI415CL) PARM(&SUBJECT &MESSAGE &REASON)
ENDSUBR
ENDPGM
EXECSQL Command
Here is the EXECSQL command, a wrapper over a Query Manager query (STRQMQRY) which will allow you to include a SELECT statement in SQL string.
CMD PROMPT('EXECUTE SQL STATEMENT')
PARM KWD(STMT) TYPE(*CHAR) LEN(1430) MIN(1) +
EXPR(*YES) PROMPT('SQL statement')
PARM KWD(OUTPUT) TYPE(*CHAR) LEN(10) RSTD(*YES) +
DFT(*) VALUES(* *PRINT *OUTFILE) +
PROMPT('Output')
PARM KWD(OUTFILE) TYPE(QUAL1) PMTCTL(PROMPT1) +
PROMPT('Output file name')
PARM KWD(MEMBER) TYPE(QUAL2) PMTCTL(PROMPT1) +
PROMPT('Output member options')
PARM KWD(PRTFILE) TYPE(QUAL3) PMTCTL(PROMPT2) +
PROMPT('Printer file to use')
QUAL1: QUAL TYPE(*NAME) LEN(10)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) SPCVAL((*LIBL +
*N) (*CURLIB *N)) PROMPT('Library')
QUAL2: QUAL TYPE(*NAME) LEN(10) DFT(*FIRST) +
SPCVAL((*FIRST *N))
QUAL TYPE(*NAME) LEN(10) RSTD(*YES) DFT(*REPLACE) +
SPCVAL((*REPLACE *N) (*ADD *N)) +
PROMPT('Replace or add records')
QUAL3: QUAL TYPE(*NAME) LEN(10) DFT(QSYSPRT)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) SPCVAL((*LIBL +
*N) (*CURLIB *N)) PROMPT('Library')
PROMPT1: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE))
PROMPT2: PMTCTL CTL(OUTPUT) COND((*EQ *PRINT))