DSNTEJ67
This job demonstrates two important steps to follow when considering the conversion of an external SQL procedure to a native SQL procedure.
//*********************************************************************
//* Name = DSNTEJ67
//*
//* Descriptive Name =
//* DB2 Sample Application
//* Phase 6
//* REXX and SQL PL
//*
//* Licensed Materials - Property of IBM
//* 5615-DB2
//* (C) COPYRIGHT 2010, 2013 IBM Corp. All Rights Reserved.
//*
//* STATUS = Version 12
//*
//* Function = This job demonstrates two important steps to follow when
//* considering the conversion of an external SQL procedure
//* to a native SQL procedure. It all begins with a copy of
//* the external SQL procedure source:
//* 1) Modify the SQL procedure options in the source
//* a) REMOVE options that relate only to external
//* SQL procedures
//* b) ADD native SQL PL options that relate to DB2
//* precompiler options
//* c) ADD native SQL PL options that relate to DB2
//* BIND PACKAGE options
//* 2) Review the SQL procedure source logic. Address
//* any identified syntax issues or published semantic
//* incompatibilities.
//*
//* Pseudocode =
//* This sample assists in this activity by performing the following:
//*
//* PH067S00 Step
//* Define the DB2 SSID to use for this job.
//* PH067S01 Step
//* Define Input. Identify the name of an external SQL SP with
//* source saved in DB2 (SYSIBM.SYSROUTINES_SRC).
//* PH067S02 Step
//* Define Output. Specify an output data set where the extracted
//* and modified SQL SP source is to be placed.
//* PH067S03 Step
//* Setup the sample REXX services to used for this job.
//* PH067S04 Step
//* Execute the DSNTEJ67 sample conversion process.
//* - Validate the SP name (using the NAMPARTS service)
//* - Verify the output file is usable (using the CHKANYFV service)
//* - Deploy DSN8EN1, a sample native
//* SQL SP helper for use later (using the CRSQLPL service)
//* - Extract external SQL SP source (using the SQLPLSRC service)
//* - Save the source in the output file
//* - for a RECFM V output file (using the ANY2SQLV service)
//* - for a RECFM F output file (using the SQLV2F service)
//* - Validate and inspect the source (using the CHKSQLPL service)
//* - Produce a table of contents to
//* describe the DDL syntax elements
//* present in the SQL PL source (using the SQLPLTOC service)
//* - Dissect the external SQL SP source
//* removing all the SP options
//* - Get the replacement options for
//* native SQL PL use by calling the
//* helper SQL SP deployed earlier (using the SQLCALL service)
//* - Reassemble the SQL SP source as a
//* string and write it to a RECFM V
//* temporary file (aka SQLV) (using the STR2SQLV service)
//* - Update the output file
//* - for a RECFM V output (using the ANY2SQLV service)
//* - for a RECFM F output (using the SQLV2F service)
//* - Write a special format temp file
//* (aka s80) for the precompiler (using the SQLV2F service)
//* - Obtain a HOST(SQLPL) Checkout
//* precompiler listing of the SQL SP
//* source for job log output. (using the CHKSQLPL service)
//* - Set the Job step RC.
//*
//*
//* Dependencies =
//* (1) Run sample job DSNTEJ65 prior to running this job.
//* That job uses the DB2 SQL procedure processor DSNTPSMP to
//* deploy the sample external SQL procedure DSN8.DSN8ES2, which
//* is the external SQL Procedure this job processes by default.
//*
//* Note: Run this job at the same site where DSNTEJ65 created
//* DSN8.DSN8ES2. Otherwise this job will terminate in
//* job step PH067S04 with rc=8 and the following
//* messages:
//* *SQLPLSRC* Error obtaining Source, SQLPL procedure
//* was not found
//* *SQLPLSRC* RC=6
//* DSNTEJ67 Cannot extract SQL procedure source
//*
//* Notes =
//* Prior to running this job, customize it for your system:
//* (1) Add a valid job card
//* (2) Locate and change all occurrences of the following strings
//* as indicated:
//* (A) '!DSN!' to the subsystem name of your DB2. This is
//* located in Step 0.
//* (B) 'DSN!!0' to the prefix of the target library for the
//* current DB2 release. This is located in the
//* JOBLIB, Step 3 and Step 4.
//* (3) (Optional) Change either of the following to customize the
//* input and output of this job for your particular purposes:
//*
//* (A) Change the name of the external SQL procedure to be
//* processed by this job. This is defined in job Step 1.
//* The name must include the schema qualifier. It must
//* designate an operational external SQL SP which was
//* deployed using the DB2 SQL procedure processor DSNTPSMP.
//*
//* (B) Change the data set where the extracted and modified
//* SQL procdure source will be written. This is defined in
//* job Step 2. The specification can be for an existing
//* data set or data set member, qualified or not qualified.
//* or represented by a DD descriptor (in the form of
//* DD:ddname). Any existing data set or ddname allocation
//* must be for a RECFM=F,FB,V,VB sequential data set or
//* data set member. (If an unallocated ddname is provided
//* a temporary sequential data set will be allocated.)
//*
//* Change Activity = ( V11 base pm76443 )
//* Apr2013 - Add version activation of native SQL PL helper routine
//* Aug2013 - Clarify prolog notes on DSN8ES2 dependency PM92730
//*
//*********************************************************************
//JOBLIB DD DISP=SHR,DSN=DSN!!0.SDSNEXIT
// DD DISP=SHR,DSN=DSN!!0.SDSNLOAD
//*
//*********************************************************************
//* Step 0: Store the default DB2 System SSID in a temporary data set
//* for use by various steps and services that are run.
//*********************************************************************
//PH067S00 EXEC PGM=IEBGENER
//SYSUT1 DD * Enter the desired DB2 SSID or Group attachment name
!DSN!
//SYSUT2 DD DSN=&&PARM0,DISP=(NEW,PASS),SPACE=(TRK,1),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=160)
//SYSPRINT DD DUMMY
//SYSIN DD DUMMY
//*********************************************************************
//* Step 1: Store the desired external SQL SP name to processs.
//* Specify a fully qualified SP name (2-parts, schema+name).
//*********************************************************************
//PH067S01 EXEC PGM=IEBGENER
//SYSUT1 DD * For a long name, wrap the input at column 72
DSN8.DSN8ES2
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//SYSUT2 DD DSN=&&PARM1,DISP=(NEW,PASS),SPACE=(TRK,1),
// DCB=(LRECL=72,RECFM=FB,BLKSIZE=576)
//SYSPRINT DD DUMMY
//SYSIN DD *
GENERATE MAXFLDS=1
RECORD FIELD=(72)
//*********************************************************************
//* Step 2: Identify the desired data set name to store the extracted
//* and modified SQL procedure source. Must be Recfm F or V,
//* sequential or member, or a non-existing data set/member.
//* The spcification can be a qualified name, a non-qualified
//* name or a DD descriptor (in the form of DD:ddname).
//*********************************************************************
//PH067S02 EXEC PGM=IEBGENER
//SYSUT1 DD *
DD:TEMPSRC
//SYSUT2 DD DSN=&&PARM2,DISP=(NEW,PASS),SPACE=(TRK,1),
// DCB=(LRECL=72,RECFM=FB,BLKSIZE=576)
//SYSPRINT DD DUMMY
//SYSIN DD *
GENERATE MAXFLDS=1
RECORD FIELD=(72)
//*********************************************************************
//* Step 3: Populate a temporary PDS with REXX services used locally
//*********************************************************************
//PH067S03 EXEC PGM=IEBUPDTE,PARM=NEW
//SYSPRINT DD DUMMY
//SYSUT2 DD DSN=&&REXXPDS,DISP=(NEW,PASS),
// SPACE=(TRK,(5,5,2)),DCB=(LRECL=80,RECFM=FB,DSORG=PO)
//SYSIN DD DSN=DSN!!0.SDSNMACS(DSN8ERL1),
// DISP=SHR
// DD DATA,DLM='@@'
./ ADD NAME=DSNTEJ67
/* ***** REXX ******** DSNTEJ67 command **************************** */
address TSO
PREPSSID '. V9 NFM'
if rc>=8 then do;
Say 'DSNTEJ67 Unable to establish a connection to DB2';
exit 8;
end;
/* From DD:SPNAME read the stored procedure name to extract from DB2.
* The name must be a schema qualified SP name (2-part name).
* The SP must be for an external SQL procedure, with source in DB2.
*/
'EXECIO * DISKR SPNAME (OPEN FINIS STEM TEMP.';
spname='';
do i = 1 to TEMP.0;
spname = spname || TEMP.i;
end;
spname = "STRIP"(spname,'B');
/* Process the passed name to get the name parts,
* plus the string and delimited forms.
*/
parse value "NAMPARTS"( spname ) with p# . namSpec
if p#<>2 then do;
say 'DSNTEJ67 the passed SP name' spname,
'was not schema qualified (2-parts)'
exit 8;
end;
parse var namSpec a b c d e . ':' +1 sNam +(a) sSch +(b) . +(c),
qNam +(d) qSch +(e)
spname = qSch'.'qNam /* 2-part fully qualified form now */
/* From DD:SOURCEDS read the data set specification for where the
* extracted and modified SQL proc source should be written at JOB end.
*/
'EXECIO * DISKR SOURCEDS (OPEN FINIS STEM TEMP.';
sourceFile='';
do i = 1 to TEMP.0;
sourceFile = sourceFile || TEMP.i;
end;
sourceFile = "STRIP"(sourceFile,'B');
/* Find the status of the target data set for the source. It must be a
* Sequential data set, F or V record format (or capable of same).
*/
parse value "CHKANYFV"(sourceFile) with oRecfm oLrecl oEmpty;
if oRecfm='' then do;
say 'DSNTEJ67 Cannot use the designated data set' sourceFile,
'for SQL PL source'
exit 8;
end;
prepConv:
/* From DD:HELPRSP1 deploy the native SQL SP DSN8.DSN8EN1 that will
* provide native options for the external SQL proc to be migrated.
* Use the subroutine form of the CRSQLPL service, asking for return
* of a version activation statement, for processing after deployment.
*/
Say 'Setting up the native SQL PL helper routine...'
call "CRSQLPL" 'DD:HELPRSP1', , , 'RETACTIVATE';
parse var result rc . 1 tok1 VerStmt ;
if "DATATYPE"(rc,'W')=0 then select; /* OK, 1st word not a number */
when tok1='/*CP*/' then rc=0; /* Skip ACTIVATE for CREATE */
when "WORDPOS"(tok1,'/*APR*/ /*APA*/')>0 /* ALTER REP, ALTER ADD */
then rc = ActivateRtnVer( VerStmt );
otherwise rc=8; /* Problem, force an error. */
end /* select */;
if RC>4 then do;
say 'DSNTEJ67 cannot deploy the native SP used for migration.';
exit 8;
end;
allocList=''; /* List of DDnames we allocate */
extractSQLproc:
/* Extract the SP source to a temporary SQLV file. Also get an S80
* format edition to use with the precompiler for inspection purposes.
*/
'SQLPLSRC' spname 'DD:SQLVE' 'DD:S80E' 'ASIS';
if RC>4 then do;
say 'DSNTEJ67 Cannot extract SQL procedure source';
exit 8;
end;
allocList='SQLVE,S80E'; /* allocated FOR us */
/* Write extracted source ASIS now to the output data set.
* We will rewrite it again later after reaching the point of editing.
*/
if oRecfm='F' then do;
if oLrecl=80 then seq='SEQ'; else seq='';
"SQLV2F" 'DD:SQLVE' sourceFile seq
end;
else "ANY2SQLV" 'DD:SQLVE' sourceFile 'EXTEND'
if RC<>0 then do;
say 'DSNTEJ67 Cannot write extracted source to data set' sourceFile
exit 8;
end;
else say 'Source for SP' spname 'written to data set' sourceFile
verifyExtSQL:
/* Verify the extracted source is valid external SQL procedure source
* before going to far. Use the HOST(SQL) precompiler.
*/
/* Keep DD:LISTING active till then end. */
'ALLOCATE DDNAME(LISTING) NEW REUSE';
'CHKSQLPL' 'DD:S80E' 'DD:LISTING' '.' 'MAR(1,80) HOST(SQL)'
if RC>4 then do;
msg='DSNTEJ67 Extracted external SQL procedure source has errors';
call endWithListing msg, allocList;
end;
chkoutSQLPL1:
/* Inspect the extracted external SQL procedure source without change
* using the HOST(SQLPL) Checkout precompiler.
* RC=8 errors are anticipated.
*/
allocList = allocList||',UT1';
'ALLOCATE DDNAME(UT1) NEW REUSE';
'CHKSQLPL' 'DD:S80E' 'DD:LISTING' 'DD:UT1' 'MAR(1,80)'
if RC>8 then do;
msg='DSNTEJ67 Fatal error running HOST(SQLPL) precompiler',
'with external SQL procedure source'
call endWithListing msg, allocList;
end;
/* Getting no UT1 content typically represents a native SQL PL syntax
* issue. In this context, that could be caused by some unforeseen
* difference between valid external SQL PL and native SQL PL syntax.
*/
parse value "CHKANYFV"('DD:UT1') with utR . utE;
if utE<>'1' then do;
msg='DSNTEJ67 Syntax error in external SQL proc source',
'when viewed as native SQL PL';
call endWithListing msg, allocList;
end;
editPrep:
/* Obtain an SQLPL TOC description, to use for editing the source. */
'SQLPLTOC' 'DD:S80E' 'DD:UT1' 'DD:TOC'
if RC<>0 then do;
msg='DSNTEJ67 Unable to prepare for source editing (no TOC).'
call endWithListing msg, allocList;
end;
allocList = allocList||',TOC';
/* Read TOC to get the OPTIONS element descriptor */
opts=''
"EXECIO * DISKR TOC (OPEN FINIS STEM TOC."
do i = 1 to TOC.0;
parse var TOC.i elem desc;
if elem='OPTS:' then do;
opts = desc;
leave;
end;
end;
parse var opts o1 o2 o3 .
parse var o1 or1 ':' oc1; parse var o2 or2 ':' oc2;
/* Bring original external source into memory now, spliting into
* three parts, Front (ahead of options), Back (after options)
* and Middle (the options which will be replaced).
*/
"EXECIO 0 DISKR SQLVE (OPEN"
/* Front: all complete lines before options, into stem FR. */
FR.=''; FR.0=0;
if or1>1
then "EXECIO" or1-1 "DISKR SQLVE (STEM FR.";
/* Middle: All records that have options on them
* This will be at least one record (where options WOULD go).
*/
if o3='0' /* no options were present */
then "EXECIO 1 DISKR SQLVE (STEM MD.";
else "EXECIO" 1+or2-or1 "DISKR SQLVE (STEM MD.";
/* Back: all the remaining complete lines */
BK.=''; BK.0=0;
"EXECIO * DISKR SQLVE (FINIS STEM BK.";
/* The Middle likely has portions of the Front, the Back, or both.
* Separate those now, and then toss the middle. Process Back first.
*/
if FRm='' then FRm=''; /* collapse existing option indentation */
i=MD.0;
if o3='0'
then j=oc1; /* When no options, the BK middle starts at oc1. */
else j=oc2+1; /* With options, the BK middle starts after oc2. */
parse var MD.i MD.i =(j) BKm
if oc1<2
then FRm='';
else parse var MD.1 FRm =(oc1) MD.1
If MD.0 > 0 then do;
say 'Removing these external SQL procedure options:'
do i = 1 to MD.0; say MD.i; end;
end;
drop MD. TOC.
/* We now have the external source in stems FR., BK.
* and strings FRm and BKm. Release the old options.
* Free our allocated data sets now. Current LISTING remains...
*/
'FREE DDNAME('allocList')';
allocList='';
/* Get the replacement options from the helper routine DSN8.DSN8EN1
* Use the FUNCTION invocation of the SQLCALL service to obtain
* the value of the last parameter (the SP output parm).
*/
call "OUTTRAP" 'TEMP.';
nat_opt=SQLCALL("DSN8.DSN8EN1('"sSch"', '"sNam"', VARCHAR('?', 5120))");
call "OUTTRAP" 'OFF';
if nat_opt='' | nat_opt='8' then do;
msg='DSNTEJ67 Unable to obtain native options for replacement';
call endWithListing msg ;
end;
say 'Inserting these native SQL PL options:';
temp=nat_opt;
do while temp<>'';
parse var temp opn '25'x temp;
say opn;
end;
/* Rewrite Original source using the new Native Options */
new_src='';
do i = 1 to FR.0; new_src=new_src || FR.i || '25'x; end; drop FR.
new_src=new_src || FRm ; drop FRm
new_src=new_src || nat_opt ; drop nat_opt
new_src=new_src || BKm ; drop BKm
do i = 1 to BK.0; new_src=new_src || BK.i || '25'x; drop BK.i; end;
call "STR2SQLV" new_src, 'DD:SQLV'
if oRecfm='F' then do;
if oLrecl=80 then seq='SEQ'; else seq='';
"SQLV2F" 'DD:SQLV' sourceFile seq
end;
else "ANY2SQLV" 'DD:SQLV' sourceFile 'EXTEND'
"SQLV2F" 'DD:SQLV' 'DD:S80' 'S80'
chkoutSQLPL2:
/* Inspect the modified procedure source one last time
* using the HOST(SQLPL) Checkout precompiler.
*/
'CHKSQLPL' 'DD:S80' 'DD:LISTING' '.' 'MAR(1,80)'
rrc=RC;
say 'Final HOST(SQLPL) Checkout Precompile ended with RC='rrc;
if rrc>0 then say 'Inspect the Listing for',
'additional SQLPL source coding issues';
'EXECIO * DISKR LISTING (OPEN FINIS STEM LISTING.';
call trimListing;
'EXECIO' listing.0 'DISKW LISTOUT (OPEN FINIS STEM LISTING.';
'FREE DDNAME(LISTING,SQLV,S80)';
exit rRC;
/* ----------------------------------------------------------------- */
activateRtnVer: procedure
/* Process the Activate Version statement passed. Returns 0 or 4. */
parse arg AVstmt ;
rcod=0;
"EXECIO * DISKR DB2SSID (OPEN FINIS STEM TEMP.";
parse var TEMP.1 ssid . ;
say 'Issuing...' AVstmt;
CALL SQLDBS 'ATTACH TO' ssid;
call SQLEXEC "EXECUTE IMMEDIATE :AVSTMT";
if result<0 then do;
say 'Trouble activating the native SQL PL routine version';
say '==>'sqlca.sqlcode'<';
say '==>'sqlca.sqlerrm'<';
call SQLEXEC "ROLLBACK";
rcod=4;
end;
else call SQLEXEC "COMMIT";
call SQLDBS 'DETACH';
return rcod;
endWithListing:
'EXECIO * DISKR LISTING (OPEN FINIS STEM LISTING.';
call trimListing;
'EXECIO' listing.0 'DISKW LISTOUT (OPEN FINIS STEM LISTING.';
'FREE DDNAME(LISTING)';
if arg(2,'E')
then 'FREE DDNAME('arg(2)')'; /* other DDnames to free */
if arg(1,'E')
then say arg(1); /* Message to end with */
exit 8;
/* trimListing: Reduce the occurance of repeated headers, CC and page
* numbers in the listing, so it appears like one
* continuious stream.
*/
trimListing: procedure expose LISTING.
hdrtypes = 'VERSION SYMBOL MESSAGES STATISTICS';
j=0; k=LISTING.0; do i = 1 to k;
parse var LISTING.i 1 cc +1 line;
if cc='1' & "LEFT"(line,19)='DB2 SQL PRECOMPILER' then do; /*Hdr*/
/* Reduce page header occurances */
key="WORD"(line,4);
loc="WORDPOS"(key,hdrtypes);
if loc>0 then do; /* First hdr usage. */
parse var line line 'PAGE' . /* Keep, w/o page num.*/
hdrtypes = "DELWORD"(hdrtypes,loc,1); /* Header now used. */
end;
else iterate; /* Skip redundant hdr.*/
end /*Hdr*/;
j=j+1; LISTING.j="STRIP"(line,'T');
end /* do i ... */;
do i=j+1 to k; drop LISTING.i; end;
LISTING.0=j;
return 1+k-j; /* lines trimmed */
/* ------------------- end DSNTEJ67 -------------------------------- */
@@
./ ENDUP
//*********************************************************************
//* Step 4: Run the sample process to extract external SQL SP source
//* from DB2, convert the source to native SQL PL, save the
//* modified source in a data set and finish with a source
//* listing written to the job log.
//*********************************************************************
//*
//PH067S04 EXEC PGM=IKJEFT01,DYNAMNBR=30
//SYSEXEC DD DSN=&&REXXPDS,DISP=(OLD,PASS)
//SYSTSPRT DD SYSOUT=*
//DB2SSID DD DSN=&&PARM0,DISP=(OLD,PASS)
//SPNAME DD DSN=&&PARM1,DISP=(OLD,PASS)
//SOURCEDS DD DSN=&&PARM2,DISP=(OLD,PASS)
//LISTOUT DD SYSOUT=*
//HELPRSP1 DD DSN=DSN!!0.SDSNIVPD(DSN8EN1),
// DISP=SHR
//SYSTSIN DD *
%DSNTEJ67
/*