zOs/REXX/CASQL
PROC 0 SSID() /* reserved - DB2 Subsystem ID */ -
SUFFIX() /* reserved - Global Parmlib Suffix */ -
PARMLIB() /* reserved - Parmlib dsname or ddname */ -
RECURS(NO) /* reserved */
/*********************************************************************/
/* ALL RIGHTS RESERVED */
/* COPYRIGHT 2001 COMPUTER ASSOCIATES INTERNATIONAL */
/*********************************************************************/
/* */
/* System : ISQL */
/* */
/* Abstract : ISPF EDIT <==> ISQL INTERFACE */
/* */
/* Function : Jump into ISQL from an ISPF edit session. */
/* */
/* Usage : Mark the first and last lines of the SQL statement */
/* using the E or EE/EE line commands, enter SQL on the */
/* command line, and press enter. */
/* */
/* How this clist works : */
/* */
/* It is no longer necessary (or recommended) to */
/* manually allocate runtime libraries in any clist */
/* such as this one. All allocations are now handled */
/* by the CA-DB2 Tools start-up clists (RSPDEF, */
/* RSPINIT, and RSPFREE), using information in the */
/* CA-DB2 Tools parmlib_dataset. */
/* */
/* This design gives customers the ability to define */
/* multiple runtime environments, which are selectable */
/* using a parmlib_suffix ("Global Parmlib Suffix"). */
/* */
/* The typical user (probably) does not need to be */
/* concerned with the parmlib_dataset/parmlib_suffix */
/* options. */
/* Simply enter the SQL command and press enter. */
/* By default, the RSPINIT/RSPDEF clists will: */
/* a) determine the previously-used parmlib_dataset */
/* b) determine the default parmlib_suffix (ENVDEF) */
/* c) perform allocations and establish runtime */
/* environment */
/* ISQL will then be started, using settings from your */
/* previous session (including DB2 subsystem), which are */
/* stored in your Profile. */
/* */
/* In other words, the only pre-requisite to using this */
/* clist is that you must have entered the product and */
/* connected to a DB2 subsystem at least one time */
/* previously. By doing so, the parmlib_dataset name */
/* that you used would have been saved into your */
/* profile, making it available here. */
/* */
/* Installation : */
/* */
/* If you install this clist into another library, */
/* then you also must install the RSPDEF, RSPINIT, and */
/* RSPFREE clists into the same library. */
/* */
/* Limitations: */
/* */
/* Each time you enter the CA-DB2 Tools, your */
/* parmlib_dataset name is stored in your profile, */
/* making it available to subsequent sessions. */
/* This is good. */
/* */
/* However, the parmlib_suffix is NOT saved. */
/* */
/* Bottom line, the impact that this has on the usage of */
/* the SQL clist is this: */
/* => if your installation uses suffixes, and if you use */
/* any suffix other than the default suffix, then you */
/* need to specify the suffix each time you use the */
/* SQL clist... */
/* => Even if you use the SAME suffix every time, if it */
/* is not the default suffix, then you need to specify */
/* the suffix. */
/* => If you specify a parmlib_dataset name, then you */
/* should also specify a parmlib_suffix (unless you */
/* intend to use the default, in which case you */
/* should NOT specify the parmlib_suffix) */
/* => If you intend to use the default parmlib_suffix, */
/* whatever that happens to be for your shop, then */
/* DO NOT specify a parmlib_suffix on the clist... */
/* (this is the reason why we do not save and */
/* re-use the prior parmlib_suffix). */
/* */
/* */
/* Reference: Consult with your System Administrator to determine */
/* which parmlib & suffixes are valid at your */
/* installation. */
/* */
/* See the CA-DB2 Tools Installation Guide for a */
/* complete description of the Global Parmlib Suffix, */
/* and the Parmlib(DSNAME) member, for more information. */
/* */
/* */
/* Syntax : SQL subsystem parmlib_suffix parmlib_dsname */
/* . . parmlib_ddname */
/* DD:parmlib_ddname */
/* . */
/* */
/* Parameters : */
/* */
/* All arguments are optional. A period (.) may be */
/* used as a placeholder, to indicate a blank/default */
/* value. */
/* */
/* */
/* subsystem */
/* */
/* DB2 subsystem ID. */
/* */
/* If not specified, ISQL automatically connects */
/* to the DB2 subsystem ID used during the last */
/* CA DB2 Products session. */
/* */
/* parmlib_suffix */
/* */
/* "Global Parmlib Suffixes" allow you to have */
/* multiple versions of the same global parmlib */
/* member for different environments. */
/* */
/* See your System Administrator for information */
/* about SUFFIX parameters that are valid for */
/* your installation. */
/* */
/* parmlib_dsname */
/* parmlib_ddname */
/* DD:parmlib_ddname */
/* */
/* At the very minimum, a parmlib must be identified, */
/* in order to start the CA-DB2 Tools. */
/* */
/* This parameter provides the ability to */
/* specify the PARMLIB DATASET NAME (a single */
/* dataset), or the DDNAME of an existing parmlib */
/* allocation. */
/* */
/* If no parmlib is specified, then the CA-DB2 Tools */
/* will use the PARMLIB that was used the last time */
/* that you used the CA-DB2 Tools. */
/* */
/* There are 2 formats for the DDNAME specification. */
/* You may use the "DD:" prefix to indicate a ddname, */
/* or you can specify just the ddname by itself, */
/* without the "DD:" prefix. */
/* If the value does not have the "DD:" prefix, */
/* and is 8 characters or less, and contains */
/* no periods, then it is assumed to be a ddname. */
/* Otherwise it is assumed to be a dataset name. */
/* */
/* Specifying the dataset name: */
/* The dsname must be fully qualified... */
/* It does not matter if it is quoted or not... */
/* It is treated as if it were a fully qualified, */
/* quoted, dataset name in either case. */
/* */
/* Example1 : SQL */
/* */
/* Jump into ISQL, connecting to the DB2 subsystem */
/* that you used last time, using the same parmlib that */
/* you used last time, and the default suffix (blank). */
/* */
/* Example2 : SQL db2p */
/* */
/* Jump into ISQL, connecting to the DB2P subsystem, */
/* again using the same parmlib that you used last time, */
/* and the default suffix (blank). */
/* */
/* Example3 : SQL db2p 03 */
/* */
/* Jump into ISQL, connecting to the DB2P subsystem, */
/* again using the same parmlib that you used last time, */
/* but this time using the '03' suffix (which is */
/* defined by your site) and which establishes a */
/* particular set of parmlib/member options. */
/* */
/* Example4 : SQL db2p . company.parmlib.data.set.name */
/* */
/* Jump into ISQL, connecting to the DB2P subsystem, */
/* specifying a parmlib dataset name. The '.' in the */
/* 3rd argument is a placeholder, indicating the the 3rd */
/* argument (the suffix) is blank. */
/* */
/* Note: you may only specify a single dataset name. */
/* */
/* Example5 : SQL db2p . MYPARMDD */
/* or : SQL db2p . DD:MYPARMDD */
/* */
/* Same as example #4, but in this case you have already */
/* allocated the parmlib dataset(s) to the MYPARMDD */
/* ddname. */
/* */
/* Use a pre-allocated DDNAME if you need to */
/* concatenate multiple parmlib datasets. */
/* */
/* Error messages : */
/* */
/* This section provides a few hints & tips for */
/* determining the cause of some of the most common */
/* errors. This section is not exhaustive... */
/* */
/* */
/* Unable to start ISQL */
/* If the message text says */
/* "THE RSPDEF CLIST ENDED WITH A RC=12" */
/* then the most likely cause is that the */
/* RSPDEF, RSPINIT, RSPFREE clists were not installed */
/* along with this ISQL clist, or else the clist */
/* library is not allocated to SYSPROC. */
/* */
/*********************************************************************/
/* For the developer: */
/* */
/* ISQL and SQL clists are identical, except for the following: */
/* - ISQL is invoked as TSO command, SQL is an edit macro */
/* - ISQL uses keywords, SQL uses positional arguments and uses */
/* a period (.) as a placeholder */
/* - ISQL passes a 'comment' in lieu of a piece of sql text; */
/* SQL extracts a string of text from the member & passes it. */
/* */
/*********************************************************************/
/* Maintenance Log: */
/* */
/* DATE ISSUE# PROBLEM# PROGRAMMER TAPE */
/* -------- -------- -------- ---------- ------ */
/* 04/12/01 10646315 PDHULM P99F */
/* New. */
/* Total re-write. */
/* Removed all ALLOCs/LIBDEFs; replaced with */
/* calls to RSPDEF/RSPINIT/RSPFREE; added parmlib/suffix */
/* parameters, and everything else. */
/* */
/* 12/01/03 13104382-1 GEN 278 PDLIT @01 P01F */
/* */
/* Removed split screen limitation. This clist will now continue */
/* when a second instance of the CA-DB2 products is running. */
/* The split screen warning messages have also been */
/* removed since PTLDRIVM will display a split screen warning */
/* panel. */
/* */
/*********************************************************************/
IF &RECURS EQ NO THEN DO
ISREDIT MACRO (SSID,SUFFIX,PARMLIB) NOPROCESS
END
CONTROL NOFLUSH NOMSG NOPROMPT NOLIST
ISPEXEC CONTROL ERRORS RETURN
SET &NULL = &STR()
SET &RSPDEF_FATAL_ERROR = NO
/*********************************************************************/
/* Convert placeholders to blanks. */
/*********************************************************************/
IF &STR(&SSID) EQ &STR(.) THEN SET &SSID=&STR()
IF &STR(&SUFFIX) EQ &STR(.) THEN SET &SUFFIX=&STR()
IF &STR(&PARMLIB) EQ &STR(.) THEN SET &PARMLIB=&STR()
/*********************************************************************/
/* Check for rc application id. If we are currently */
/* under a different id recursively invoke ourselves. */
/*********************************************************************/
ISPEXEC VGET (ZAPPLID)
IF &ZAPPLID NE &STR(RC) THEN DO
IF &STR(&SSID) NE THEN SET &SSID=&STR(SSID(&SSID))
IF &STR(&SUFFIX) NE THEN SET &SUFFIX=&STR(SUFFIX(&SUFFIX))
IF &STR(&PARMLIB) NE THEN SET &PARMLIB=&STR(PARMLIB(&PARMLIB))
ISPEXEC SELECT -
CMD(%&SYSICMD -
&SSID &SUFFIX &PARMLIB RECURS(YES)) -
NEWAPPL(RC) -
PASSLIB
SET &SQLCC = &LASTCC
EXIT CODE(&SQLCC)
END
/*********************************************************************/
/* If PARMLIB was not passed as an argument, then we will use */
/* the PARMLIB that was established the last time they used the */
/* CA-DB2 Tools. This means that the CA-DB2 Tools must have been */
/* entered at least 1 time previously. */
/*********************************************************************/
IF &STR(&PARMLIB) EQ THEN DO
ISPEXEC VGET (PTIPARM)
IF &STR(&PTIPARM) EQ THEN DO
SET &ZEDSMSG = &STR(PARMLIB NOT SET)
SET &ZEDLMSG = &STR(You did not specify a PARMLIB,)
SET &ZEDLMSG = &STR(&ZEDLMSG and a default PARMLIB has not)
SET &ZEDLMSG = &STR(&ZEDLMSG been set.)
SET &ZEDLMSG = &STR(&ZEDLMSG You must specify a PARMLIB,)
SET &ZEDLMSG = &STR(&ZEDLMSG or, you must have entered)
SET &ZEDLMSG = &STR(&ZEDLMSG the CA Products for DB2)
SET &ZEDLMSG = &STR(&ZEDLMSG at least 1 time previously,)
SET &ZEDLMSG = &STR(&ZEDLMSG in order for this clist to work)
ISPEXEC SETMSG MSG(ISRZ001)
EXIT CODE(12)
END
END
/*********************************************************************/
/* If SSID was not passed as an argument, then we will use */
/* the SSID that was established the last time they used the */
/* CA-DB2 Tools. This means that the CA-DB2 Tools must have been */
/* entered at least 1 time previously. */
/*********************************************************************/
IF &STR(&SSID) EQ THEN DO
ISPEXEC VGET (SYS)
IF &STR(&SYS) EQ OR &STR(&SYS) EQ &STR(SSID) THEN DO
SET &ZEDSMSG = &STR(DB2 SUBSYSTEM NOT SET)
SET &ZEDLMSG = &STR(You did not specify a DB2 SSID,)
SET &ZEDLMSG = &STR(&ZEDLMSG and a default SSID has not)
SET &ZEDLMSG = &STR(&ZEDLMSG been set.)
SET &ZEDLMSG = &STR(&ZEDLMSG You must specify a DB2 SSID,)
SET &ZEDLMSG = &STR(&ZEDLMSG or, you must have entered)
SET &ZEDLMSG = &STR(&ZEDLMSG the CA Products for DB2)
SET &ZEDLMSG = &STR(&ZEDLMSG at least 1 time previously and)
SET &ZEDLMSG = &STR(&ZEDLMSG connected to a DB2 subsystem,)
SET &ZEDLMSG = &STR(&ZEDLMSG in order for this clist to work)
ISPEXEC SETMSG MSG(ISRZ001)
EXIT CODE(12)
END
END
/*********************************************************************/
/* Identify line commands to be used by this system. */
/* If they did not enter E or EE/EE, exit with msg. */
/*********************************************************************/
ISREDIT PROCESS RANGE E
IF &LASTCC ^= 0 THEN DO
SET &ZEDSMSG = &STR(BLOCK COMMAND INCOMPLETE)
SET &ZEDLMSG = &STR(SQL QUERY MUST BE MARKED WITHIN)
SET &ZEDLMSG = &STR(&ZEDLMSG PROGRAM BY EE/EE LINE COMMANDS)
ISPEXEC SETMSG MSG(ISRZ001)
EXIT CODE(12)
END
/*********************************************************************/
/* Obtain the logical data width, */
/* and substring DLEN to 3 chars. */
/*********************************************************************/
ISREDIT (DLEN) = DATA_WIDTH
SET &VLEN = &LENGTH(&STR(&DLEN))
IF &VLEN > 3 THEN -
SET &DLEN = &SUBSTR(&VLEN-2:&VLEN,&DLEN)
/*********************************************************************/
/* Isolate the desired lines of sql. */
/*********************************************************************/
ISREDIT (FLINE) = LINENUM .ZFRANGE
ISREDIT (LLINE) = LINENUM .ZLRANGE
/*********************************************************************/
/* Create 1 variable with all of the sql in it, */
/* using at most 72 bytes of data. */
/* If cobol, then use only 66 bytes of data. */
/*********************************************************************/
SET COUNT = &FLINE
SET SQLTEXT = &STR()
DO WHILE (&COUNT ^> &LLINE)
ISREDIT (SQ) = LINE &COUNT
IF &DLEN > &STR(072) THEN -
IF &DLEN = &STR(074) THEN -
SET SQLTEXT = &STR(&NRSTR(&SQLTEXT)&SUBSTR(1:66,&NRSTR(&SQ)))
ELSE -
SET SQLTEXT = &STR(&NRSTR(&SQLTEXT)&SUBSTR(1:72,&NRSTR(&SQ)))
ELSE -
SET SQLTEXT = &STR(&NRSTR(&SQLTEXT)&NRSTR(&SQ))
SET COUNT = &COUNT+1
END
/*********************************************************************/
/* If actual data width is greater than 72 use 72, */
/* except if the length is 74 in which case the edit */
/* profile is number on cobol and data width is 66. */
/*********************************************************************/
IF &DLEN > &STR(072) THEN -
IF &DLEN = &STR(074) THEN -
SET &DLEN = &STR(066)
ELSE -
SET &DLEN = &STR(072)
/********************************************************************/
/* Parse dsname/ddname argument */
/********************************************************************/
SET &PARM = &STR()
SET &CHECK_DATASET_NAME = NO
IF &STR(&PARMLIB) NE THEN DO
IF &SUBSTR(1:1,&STR(&PARMLIB)) EQ &STR(') OR +
&SUBSTR(1:1,&STR(&PARMLIB)) EQ &STR(") THEN DO
/* strip quotes */
SET &PARMLIB = &SUBSTR(2:&LENGTH(&PARMLIB)-1,&STR(&PARMLIB))
END
IF &LENGTH(&STR(&PARMLIB)) GT 3 THEN DO
/* DD:ddname ?
SET &PREFIX = &SUBSTR(1:3,&STR(&PARMLIB))
IF &STR(&PREFIX) = &STR(DD:) THEN +
SET &PARM = &STR(PARMLIB(&PARMLIB))
END
IF &STR(&PARM) EQ THEN DO
/* if the length of PARMLIB is <= 8
/* and contains no period,
/* then it is a DDNAME.
/* otherwise it is a DSNAME
SET &LEN = &LENGTH(&STR(&PARMLIB))
SET &DOT = &SYSINDEX(&STR(.),&STR(&PARMLIB))
IF &LEN LE 8 AND &DOT EQ 0 THEN DO
SET &PARM = &STR(PARMLIB(DD:&PARMLIB))
END
ELSE DO
SET &PARM = &STR(PARMLIB(&PARMLIB))
SET &CHECK_DATASET_NAME = YES
END
END
/* if dataset name given, then verify it...
/* the RSPDEF clist always treats the dataset name as
/* fully qualified, regardless whether it is quoted or not...
/* so, we'll do the same here...
IF &CHECK_DATASET_NAME = YES THEN DO
SET &SYSDSN_RESULT = &SYSDSN('&PARMLIB')
IF &STR(&SYSDSN_RESULT) NE OK THEN DO
SET &ZEDSMSG = &STR(PARMLIB DATASET ERROR)
SET &ZEDLMSG = &STR('&PARMLIB' &SYSDSN_RESULT)
ISPEXEC SETMSG MSG(ISRZ001)
EXIT CODE(12)
END
END
END
/********************************************************************/
/* Call RSPDEF to drive online allocations. */
/********************************************************************/
/* RSPDEF will end with RC=4 if split screen detected... */
/********************************************************************/
%RSPDEF 'SUFFIX(&SUFFIX) &PARM'
SET &RC = &LASTCC
IF &RC GT 4 THEN DO
SET &RSPDEF_FATAL_ERROR = YES
GOTO DONE
END
%RSPINIT
/*********************************************************************/
/* Call ISQL, let it grab the sql from clist var */
/*********************************************************************/
/* Important note: */
/* */
/* If PTLDRIVM encounters an error and does a setmsg, it will exit */
/* and the message will be displayed in the current edit window... */
/* */
/* This is good, except that there may be a .HELP panel associated */
/* with the error, and if the user hits PF1 they will get an ISPF */
/* Dialog Error "panel not found" and they will be kicked out of the */
/* edit session -- because the HELP panel library is not allocated. */
/* */
/* Unfortunately, from a programming point-of-view, there is */
/* nothing at all that we can do about this. The only work-around */
/* is for the user to permanently allocate the HELP panels in the */
/* TSO logon proc... */
/* Which is actually not a bad thing to do (hint, hint). */
/*********************************************************************/
IF &STR(&SUFFIX) EQ THEN +
DO
ISPEXEC SELECT -
PGM(PTLDRIVM) -
PARM(CI=IQLSQL/&DLEN&SSID) -
NEWAPPL(RC) -
PASSLIB
END
ELSE +
DO
ISPEXEC SELECT -
PGM(PTLDRIVM) -
PARM(CI=IQLSQL,SUFFIX=&SUFFIX/&DLEN&SSID) -
NEWAPPL(RC) -
PASSLIB
END
/*********************************************************************/
/* Cleanup: */
/* Call RSPFREE to release our allocations. */
/*********************************************************************/
DONE: &NULL
%RSPFREE
IF &RSPDEF_FATAL_ERROR = YES THEN DO
SET &ZEDSMSG = &STR(Unable to start ISQL)
SET &ZEDLMSG = &STR(The RSPDEF clist ended with a RC=&RC..)
SET &ZEDLMSG = &STR(&ZEDLMSG RSPDEF is responsible)
SET &ZEDLMSG = &STR(&ZEDLMSG for processing the parmlib/suffix,)
SET &ZEDLMSG = &STR(&ZEDLMSG and allocating runtime libraries.)
SET &ZEDLMSG = &STR(&ZEDLMSG The clist encountered an error, and)
SET &ZEDLMSG = &STR(&ZEDLMSG processing terminated.)
SET &ZEDLMSG = &STR(&ZEDLMSG Correct your parmlib/suffix)
SET &ZEDLMSG = &STR(&ZEDLMSG specification, and try again.)
ISPEXEC SETMSG MSG(ISRZ001)
END
EXIT CODE(0)