DSN8ES3
Accepts the schema and name of an external stored procedure and returns a result set that contains the CREATE PROCEDURE statement.
-- DSN8ES3: SOURCE MODULE FOR THE SAMPLE NATIVE SQL PROCEDURE 00010000
-- 00020000
-- LICENSED MATERIALS - PROPERTY OF IBM 00022000
-- 5635-DB2 00024000
-- (C) COPYRIGHT 2006 IBM CORP. ALL RIGHTS RESERVED. 00026000
-- 00028000
-- STATUS = VERSION 9 00030000
-- 00040000
-- Function: Accepts the schema and name of an external stored 00050000
-- procedure and returns a result set that contains the 00060000
-- CREATE PROCEDURE statement. 00070000
-- 00080000
-- Notes: 00090000
-- Dependencies: 00100000
-- - Requires support for native SQL procedures 00110000
-- - Requires a global temporary table (created in sample job 00120000
-- DSNTEJ66) for returning the result. 00130000
-- 00140000
-- Restrictions: 00150000
-- 00160000
-- Module Type: SQL Procedure 00170000
-- Processor: DB2 for z/OS Version 9 00180000
-- or a subsequent release 00190000
-- 00200000
-- Entry Point: DSN8ES3 00210000
-- Purpose: See Function, above 00220000
-- 00230000
-- Parameters: 00240000
-- - Input: spSCHEMA VARCHAR(128) 00250000
-- spNAME VARCHAR(128) 00260000
-- - Output: (None) 00270000
-- 00280000
-- Normal Exit: 00290000
-- Error Exit: 00300000
-- 00310000
-- 00320000
-- External References: 00330000
-- - SYSIBM.SYSROUTINES : DB2 catalog table for routines 00340000
-- - SYSIBM.SYSPARMS : DB2 catalog table for routine parameters 00350000
-- - DSN8.DSN8ES3_RS_TBL: Global Temporary Table for result set 00360000
-- 00370000
-- Pseudocode: 00380000
-- - Clear any residual from result set table 00390000
-- - Get the stored proc properties from SYSIBM.SYSROUTINES 00400000
-- - If not found, return SQLSTATE 38602 and the message: 00410000
-- 'Requested object not found' 00420000
-- - If not a stored proc, return SQLSTATE 38603 and the message: 00430000
-- 'Object is not a stored procedure' 00440000
-- - If not an external stored proc, return SQLSTATE 38604 and the 00450000
-- message: 'Object is not an external stored procedure' 00460000
-- - Open a cursor on the SYSPARMS table 00470000
-- - Fetch the first row 00480000
-- - If a row is found, insert the CREATE PROCEDURE clause in the 00490000
-- result set 00500000
-- - For each row in the SYSPARMS cursor, build a parameter clause: 00510000
-- - Start with the parameter type (IN, OUT, or INOUT) 00520000
-- - Append the parameter name 00530000
-- - Append the parameter data type 00540000
-- - For string data types, add the CCSID clause 00550000
-- - Insert the entry in the result set table 00560000
-- - Build the remaining clauses and insert each in the result set 00570000
-- - Build and insert the RESULTS SETS clause 00580000
-- - Build and insert the EXTERNAL NAME clause 00590000
-- - Build and insert the LANGUAGE clause 00600000
-- - Build and insert the SQL data access type clause 00610000
-- - Build and insert the PARAMETER STYLE clause 00620000
-- - Build and insert the DETERMINISTIC clause 00630000
-- - Build and insert the FENCED clause 00640000
-- - Build and insert the COLLID clause 00650000
-- - Build and insert the WLM ENVIRONMENT clause 00660000
-- - Build and insert the ASUTIME clause 00670000
-- - Build and insert the STAY RESIDENT clause 00680000
-- - Build and insert the PROGRAM TYPE clause 00690000
-- - Build and insert the EXTERNAL SECURITY clause 00700000
-- - Build and insert the AFTER FAILURE clause 00710000
-- - Build and insert the RUN OPTIONS clause 00720000
-- - Build and insert the COMMIT ON RETURN clause 00730000
-- - Build and insert the SPECIAL REGISTERS clause 00740000
-- - Build and insert the CALLED ON NULL INPUT clause 00750000
-- - Open the cursor to the result set 00760000
-- 00762000
-- CHANGE ACTIVITY 00764000
-- 10/31/2013 Ignore SYSPARMS rows where ORDINAL = 0 PM98341 00766000
-- 00768000
-- 00770000
CREATE PROCEDURE DSN8.DSN8ES3 00780000
( IN spSCHEMA VARCHAR(128), 00790000
IN spNAME VARCHAR(128) ) 00800000
PARAMETER CCSID EBCDIC 00810000
RESULT SET 1 00820000
NOT DETERMINISTIC 00830000
MODIFIES SQL DATA 00840000
ASUTIME NO LIMIT 00850000
COMMIT ON RETURN NO 00860000
00870000
P1: BEGIN NOT ATOMIC 00880000
DECLARE hvLANGUAGE VARCHAR(24) CCSID EBCDIC; 00890000
DECLARE hvCOLLID VARCHAR(128) CCSID EBCDIC; 00900000
DECLARE hvDETERMINISTIC VARCHAR(17) CCSID EBCDIC; 00910000
DECLARE hvNULL_CALL CHAR(1) CCSID EBCDIC; 00920000
DECLARE hvPARAMETER_STYLE VARCHAR(18) CCSID EBCDIC; 00930000
DECLARE hvFENCED CHAR(1) CCSID EBCDIC; 00940000
DECLARE hvASUTIME INTEGER DEFAULT 0; 00950000
DECLARE hvCOMMIT_ON_RETURN VARCHAR(3) CCSID EBCDIC; 00960000
DECLARE hvEXTERNAL_NAME VARCHAR(762) CCSID EBCDIC; 00970000
DECLARE hvEXTERNAL_SECURITY VARCHAR(7) CCSID EBCDIC; 00980000
DECLARE hvMAX_FAILURE SMALLINT DEFAULT 0; 00990000
DECLARE hvORIGIN CHAR(1) CCSID EBCDIC; 01000000
DECLARE hvPROGRAM_TYPE VARCHAR(4) CCSID EBCDIC; 01010000
DECLARE hvRESULT_SETS SMALLINT DEFAULT 0; 01020000
DECLARE hvROUTINETYPE CHAR(1) CCSID EBCDIC; 01030000
DECLARE hvRUNOPTS VARCHAR(762) CCSID EBCDIC; 01040000
DECLARE hvSPECIAL_REGS VARCHAR(25) CCSID EBCDIC; 01050000
DECLARE hvSQL_DATA_ACCESS VARCHAR(17) CCSID EBCDIC; 01060000
DECLARE hvSTAYRESIDENT VARCHAR(3) CCSID EBCDIC; 01070000
DECLARE hvWLM_ENVIRONMENT VARCHAR(54) CCSID EBCDIC; 01080000
01090000
DECLARE hvENCODING_SCHEME VARCHAR(7) CCSID EBCDIC; 01100000
DECLARE hvLENGTH INTEGER DEFAULT 0; 01110000
DECLARE hvORDINAL SMALLINT DEFAULT 0; 01120000
DECLARE hvPARMNAME VARCHAR(128) CCSID EBCDIC; 01130000
DECLARE hvROWTYPE VARCHAR(6) CCSID EBCDIC; 01140000
DECLARE hvSCALE SMALLINT DEFAULT 0; 01150000
DECLARE hvSUBTYPE VARCHAR(15) CCSID EBCDIC; 01160000
DECLARE hvTYPENAME VARCHAR(128) CCSID EBCDIC; 01170000
01180000
DECLARE RETURN_POINT CHAR(4) CCSID EBCDIC; 01190000
01200000
DECLARE LINE VARCHAR(384) CCSID EBCDIC; 01210000
DECLARE LINE_LENGTH INT DEFAULT 0; 01220000
DECLARE END_TABLE INT DEFAULT 0; 01230000
01240000
DECLARE OPERATION VARCHAR(12) CCSID EBCDIC; 01250000
01260000
DECLARE ROW CHAR(80) CCSID EBCDIC; 01270000
DECLARE ROW_SEQUENCE SMALLINT DEFAULT 1; 01280000
01290000
-- Cursor for result set (CREATE PROCEDURE statement) 01300000
DECLARE DSN8ES3_RS_CSR CURSOR WITH RETURN WITH HOLD FOR 01310000
SELECT RS_SEQUENCE, 01320000
RS_LINE 01330000
FROM DSN8.DSN8ES3_RS_TBL 01340000
ORDER BY RS_SEQUENCE; 01350000
01360000
-- Cursor to fetch proc parm properties from SYSIBM.SYSPARMS 01370000
DECLARE SYSPARMS_CURSOR CURSOR FOR 01380000
SELECT PARMNAME 01390000
,CASE ROWTYPE 01400000
WHEN 'P' THEN 'IN ' 01410000
WHEN 'O' THEN 'OUT ' 01420000
WHEN 'B' THEN 'INOUT ' 01430000
END 01440000
,ORDINAL 01450000
,TYPENAME 01460000
,LENGTH 01470000
,SCALE 01480000
,CASE SUBTYPE 01490000
WHEN 'B' THEN ' FOR BIT DATA' 01500000
WHEN 'M' THEN ' FOR MIXED DATA' 01510000
WHEN 'S' THEN ' FOR SBCS DATA' 01520000
WHEN ' ' THEN ' ' 01530000
END 01540000
,CASE ENCODING_SCHEME 01550000
WHEN 'A' THEN ' ASCII' 01560000
WHEN 'E' THEN ' EBCDIC' 01570000
WHEN 'U' THEN ' UNICODE' 01580000
WHEN ' ' THEN ' ' 01590000
END 01600000
FROM SYSIBM.SYSPARMS 01610000
WHERE SCHEMA = spSCHEMA 01620000
AND SPECIFICNAME = spNAME 01630000
AND ORDINAL <> 0 01635000
ORDER BY ORDINAL 01640000
FOR FETCH ONLY; 01650000
01660000
DECLARE CONTINUE HANDLER FOR NOT FOUND 01670000
SET END_TABLE = 1; 01680000
01690000
DECLARE EXIT HANDLER FOR SQLEXCEPTION 01700000
SIGNAL SQLSTATE '38601' 01710000
SET MESSAGE_TEXT = 'Unexpected SQLCODE ' 01720000
|| CHAR(SQLCODE) 01730000
|| ' from ' 01740000
|| OPERATION; 01750000
01760000
-- Clean residual from the result set table 01770000
DELETE FROM DSN8.DSN8ES3_RS_TBL; 01780000
01790000
-- Fetch the stored proc properties from SYSIBM.SYSROUTINES 01800000
SET END_TABLE = 0; 01810000
SET OPERATION = 'SELECT INTO'; 01820000
SELECT LANGUAGE 01830000
,COLLID 01840000
,CASE DETERMINISTIC 01850000
WHEN 'N' THEN 'NOT DETERMINISTIC' 01860000
WHEN 'Y' THEN 'DETERMINISTIC' 01870000
WHEN ' ' THEN ' ' 01880000
END 01890000
,NULL_CALL 01900000
,CASE PARAMETER_STYLE 01910000
WHEN 'D' THEN 'DB2SQL' 01920000
WHEN 'G' THEN 'GENERAL' 01930000
WHEN 'N' THEN 'GENERAL WITH NULLS' 01940000
WHEN 'J' THEN 'JAVA' 01950000
WHEN ' ' THEN ' ' 01960000
END 01970000
,FENCED 01980000
,CASE SQL_DATA_ACCESS 01990000
WHEN 'C' THEN 'CONTAINS SQL' 02000000
WHEN 'M' THEN 'MODIFIES SQL DATA' 02010000
WHEN 'N' THEN 'NO SQL' 02020000
WHEN 'R' THEN 'READS SQL DATA' 02030000
WHEN ' ' THEN ' ' 02040000
END 02050000
,CASE STAYRESIDENT 02060000
WHEN 'N' THEN 'NO' 02070000
WHEN 'Y' THEN 'YES' 02080000
WHEN ' ' THEN ' ' 02090000
END 02100000
,ASUTIME 02110000
,WLM_ENVIRONMENT 02120000
,CASE PROGRAM_TYPE 02130000
WHEN 'M' THEN 'MAIN' 02140000
WHEN 'S' THEN 'SUB' 02150000
WHEN ' ' THEN ' ' 02160000
END 02170000
,CASE EXTERNAL_SECURITY 02180000
WHEN 'D' THEN 'DB2' 02190000
WHEN 'U' THEN 'USER' 02200000
WHEN 'C' THEN 'DEFINER' 02210000
WHEN ' ' THEN ' ' 02220000
END 02230000
,CASE COMMIT_ON_RETURN 02240000
WHEN 'N' THEN 'NO' 02250000
WHEN 'Y' THEN 'YES' 02260000
WHEN ' ' THEN ' ' 02270000
END 02280000
,RESULT_SETS 02290000
,EXTERNAL_NAME 02300000
,RUNOPTS 02310000
,CASE SPECIAL_REGS 02320000
WHEN 'D' THEN 'DEFAULT SPECIAL REGISTERS' 02330000
WHEN 'I' THEN 'INHERIT SPECIAL REGISTERS' 02340000
WHEN ' ' THEN ' ' 02350000
END 02360000
,MAX_FAILURE 02370000
INTO hvLANGUAGE 02380000
,hvCOLLID 02390000
,hvDETERMINISTIC 02400000
,hvNULL_CALL 02410000
,hvPARAMETER_STYLE 02420000
,hvFENCED 02430000
,hvSQL_DATA_ACCESS 02440000
,hvSTAYRESIDENT 02450000
,hvASUTIME 02460000
,hvWLM_ENVIRONMENT 02470000
,hvPROGRAM_TYPE 02480000
,hvEXTERNAL_SECURITY 02490000
,hvCOMMIT_ON_RETURN 02500000
,hvRESULT_SETS 02510000
,hvEXTERNAL_NAME 02520000
,hvRUNOPTS 02530000
,hvSPECIAL_REGS 02540000
,hvMAX_FAILURE 02550000
FROM SYSIBM.SYSROUTINES 02560000
WHERE SCHEMA = spSCHEMA 02570000
AND NAME = spNAME; 02580000
02590000
02600000
CASE 02610000
WHEN END_TABLE = 1 THEN 02620000
SIGNAL SQLSTATE '38602' 02630000
SET MESSAGE_TEXT = 'Requested object "' 02640000
|| spSCHEMA 02650000
|| '"."' 02660000
|| spNAME 02670000
|| '" not found'; 02680000
WHEN hvROUTINETYPE <> 'P' THEN 02690000
SIGNAL SQLSTATE '38603' 02700000
SET MESSAGE_TEXT = 'Object is not a stored procedure'; 02710000
WHEN hvORIGIN <> 'E' THEN 02720000
SIGNAL SQLSTATE '38604' 02730000
SET MESSAGE_TEXT = 'Object is not an external stored procedure'; 02740000
ELSE -- NOOP below provided to satisfy requirement for ELSE clause 02750000
SET ROW_SEQUENCE = ROW_SEQUENCE; 02760000
END CASE; 02770000
02780000
SET END_TABLE = 0; 02790000
SET OPERATION = 'OPEN CURSOR'; 02800000
OPEN SYSPARMS_CURSOR; 02810000
02820000
SET OPERATION = 'FIRST FETCH'; 02830000
FETCH SYSPARMS_CURSOR 02840000
INTO hvPARMNAME 02850000
,hvROWTYPE 02860000
,hvORDINAL 02870000
,hvTYPENAME 02880000
,hvLENGTH 02890000
,hvSCALE 02900000
,hvSUBTYPE 02910000
,hvENCODING_SCHEME; 02920000
02930000
-- Output the CREATE PROCEDURE clause 02940000
IF END_TABLE = 0 THEN 02950000
SET LINE = 'CREATE PROCEDURE ' || spSCHEMA || '.' || spNAME; 02960000
SET RETURN_POINT = 'A100'; 02970000
GOTO INSERTLINE; 02980000
END IF; 02990000
03000000
A100: -- Build and output the parameter list 03010000
SET LINE = ' ( '; 03020000
WHILE END_TABLE = 0 DO 03030000
-- Output the parameter type (IN, OUT, or INOUT) 03040000
SET LINE = LINE 03050000
|| hvROWTYPE || ' ' 03060000
|| hvPARMNAME || ' ' 03070000
|| RTRIM(hvTYPENAME); 03080000
CASE 03090000
WHEN hvTYPENAME = 'DECIMAL' 03100000
OR hvTYPENAME = 'DEC' 03110000
OR hvTYPENAME = 'NUMERIC' THEN 03120000
SET LINE = LINE || '(' || VARCHAR(hvLENGTH) 03130000
|| ',' || VARCHAR(hvSCALE) || ')'; 03140000
03150000
WHEN hvTYPENAME = 'FLOAT' THEN 03160000
SET LINE = LINE || '(' || VARCHAR(hvLENGTH) || ')'; 03170000
03180000
WHEN hvTYPENAME = 'CHARACTER' 03190000
OR hvTYPENAME = 'CHAR' 03200000
OR hvTYPENAME = 'CHARACTER VARYING' 03210000
OR hvTYPENAME = 'CHAR VARYING' 03220000
OR hvTYPENAME = 'VARCHAR' 03230000
OR hvTYPENAME = 'CHARACTER LARGE OBJECT' 03240000
OR hvTYPENAME = 'CHAR LARGE OBJECT' 03250000
OR hvTYPENAME = 'CLOB' 03260000
OR hvTYPENAME = 'GRAPHIC' 03270000
OR hvTYPENAME = 'VARGRAPHIC' 03280000
OR hvTYPENAME = 'DBCLOB' 03290000
OR hvTYPENAME = 'BINARY LARGE OBJECT' 03300000
OR hvTYPENAME = 'BLOB' THEN 03310000
SET LINE = LINE || '(' || VARCHAR(hvLENGTH) || ')'; 03320000
03330000
ELSE -- busy statement below required to handle ELSE case 03340000
SET ROW_SEQUENCE = ROW_SEQUENCE; 03350000
END CASE; 03360000
03370000
IF hvSUBTYPE <> ' ' THEN 03380000
SET LINE = LINE || hvSUBTYPE; 03390000
END IF; 03400000
IF hvENCODING_SCHEME <> ' ' THEN 03410000
SET LINE = LINE || ' CCSID' || RTRIM(hvENCODING_SCHEME); 03420000
END IF; 03430000
SET RETURN_POINT = 'B100'; 03440000
GOTO INSERTLINE; 03450000
03460000
B100: -- Fetch the next parameter 03470000
SET OPERATION = 'FETCH'; 03480000
FETCH SYSPARMS_CURSOR 03490000
INTO hvPARMNAME 03500000
,hvROWTYPE 03510000
,hvORDINAL 03520000
,hvTYPENAME 03530000
,hvLENGTH 03540000
,hvSCALE 03550000
,hvSUBTYPE 03560000
,hvENCODING_SCHEME; 03570000
03580000
SET LINE = ' ,'; 03590000
END WHILE; 03600000
03610000
SET OPERATION = 'CLOSE CURSOR'; 03620000
CLOSE SYSPARMS_CURSOR; 03630000
-- Close the parameter list 03640000
SET LINE = ' )'; 03650000
SET RETURN_POINT = 'C100'; 03660000
GOTO INSERTLINE; 03670000
03680000
C100: -- Build remaining clauses for the CREATE PROCEDURE statement 03690000
03700000
-- Output the RESULTS SETS clause 03710000
IF hvRESULT_SETS > 0 THEN 03720000
SET LINE = 'DYNAMIC RESULT SETS ' || VARCHAR(hvRESULT_SETS); 03730000
SET RETURN_POINT = 'D100'; 03740000
GOTO INSERTLINE; 03750000
END IF; 03760000
03770000
D100: -- Output the EXTERNAL NAME clause 03780000
SET LINE = 'EXTERNAL NAME ' || RTRIM(hvEXTERNAL_NAME); 03790000
SET RETURN_POINT = 'E100'; 03800000
GOTO INSERTLINE; 03810000
03820000
E100: -- Output the LANGUAGE clause 03830000
SET LINE = 'LANGUAGE ' || RTRIM(hvLANGUAGE); 03840000
SET RETURN_POINT = 'F100'; 03850000
GOTO INSERTLINE; 03860000
03870000
F100: -- Output the SQL data access type clause 03880000
IF hvSQL_DATA_ACCESS <> ' ' THEN 03890000
SET LINE = hvSQL_DATA_ACCESS; 03900000
SET RETURN_POINT = 'G100'; 03910000
GOTO INSERTLINE; 03920000
END IF; 03930000
03940000
G100: -- Output the PARAMETER STYLE clause 03950000
IF hvPARAMETER_STYLE <> ' ' THEN 03960000
SET LINE = 'PARAMETER STYLE ' || hvPARAMETER_STYLE; 03970000
SET RETURN_POINT = 'H100'; 03980000
GOTO INSERTLINE; 03990000
END IF; 04000000
04010000
H100: -- Output the DETERMINISTIC clause 04020000
IF hvDETERMINISTIC <> ' ' THEN 04030000
SET LINE = hvDETERMINISTIC; 04040000
SET RETURN_POINT = 'I100'; 04050000
GOTO INSERTLINE; 04060000
END IF; 04070000
04080000
I100: -- Output the FENCED clause 04090000
IF hvFENCED <> ' ' THEN 04100000
SET LINE = 'FENCED'; 04110000
SET RETURN_POINT = 'J100'; 04120000
GOTO INSERTLINE; 04130000
END IF; 04140000
04150000
J100: -- Output the COLLID clause 04160000
IF hvCOLLID <> ' ' THEN 04170000
SET LINE = 'COLLID ' || RTRIM(hvCOLLID); 04180000
ELSE 04190000
SET LINE = 'NO COLLID'; 04200000
END IF; 04210000
SET RETURN_POINT = 'K100'; 04220000
GOTO INSERTLINE; 04230000
04240000
K100: -- Output the WLM ENVIRONMENT clause 04250000
SET LINE = 'WLM ENVIRONMENT ' || RTRIM(hvWLM_ENVIRONMENT); 04260000
SET RETURN_POINT = 'L100'; 04270000
GOTO INSERTLINE; 04280000
04290000
L100: -- Output the ASUTIME clause 04300000
IF hvASUTIME <> 0 THEN 04310000
SET LINE = 'ASUTIME ' || VARCHAR(hvASUTIME); 04320000
ELSE 04330000
SET LINE = 'ASUTIME NO LIMIT'; 04340000
END IF; 04350000
SET RETURN_POINT = 'M100'; 04360000
GOTO INSERTLINE; 04370000
04380000
M100: -- Output the STAY RESIDENT clause 04390000
IF hvSTAYRESIDENT <> ' ' THEN 04400000
SET LINE = 'STAY RESIDENT ' || hvSTAYRESIDENT; 04410000
SET RETURN_POINT = 'N100'; 04420000
GOTO INSERTLINE; 04430000
END IF; 04440000
04450000
N100: -- Output the PROGRAM TYPE clause 04460000
IF hvPROGRAM_TYPE <> ' ' THEN 04470000
SET LINE = 'PROGRAM TYPE ' || hvPROGRAM_TYPE; 04480000
SET RETURN_POINT = 'O100'; 04490000
GOTO INSERTLINE; 04500000
END IF; 04510000
04520000
O100: -- Output the EXTERNAL SECURITY clause 04530000
IF hvEXTERNAL_SECURITY <> ' ' THEN 04540000
SET LINE = 'SECURITY ' || hvEXTERNAL_SECURITY; 04550000
SET RETURN_POINT = 'P100'; 04560000
GOTO INSERTLINE; 04570000
END IF; 04580000
04590000
P100: -- Output the AFTER FAILURE clause 04600000
IF hvMAX_FAILURE = -1 THEN 04610000
SET LINE = 'STOP AFTER SYSTEM DEFAULT FAILURES'; 04620000
ELSEIF hvMAX_FAILURE = 0 THEN 04630000
SET LINE = 'CONTINUE AFTER FAILURE'; 04640000
ELSE 04650000
SET LINE = 'STOP AFTER ' || VARCHAR(hvMAX_FAILURE) || ' FAILURES'; 04660000
END IF; 04670000
SET RETURN_POINT = 'Q100'; 04680000
GOTO INSERTLINE; 04690000
04700000
Q100: -- Output the RUN OPTIONS clause 04710000
IF hvRUNOPTS <> ' ' THEN 04720000
SET LINE = 'RUN OPTIONS ''' || hvRUNOPTS || ''''; 04730000
SET RETURN_POINT = 'R100'; 04740000
GOTO INSERTLINE; 04750000
END IF; 04760000
04770000
R100: -- Output the COMMIT ON RETURN clause 04780000
IF hvCOMMIT_ON_RETURN <> ' ' THEN 04790000
SET LINE = 'COMMIT ON RETURN ' || hvCOMMIT_ON_RETURN; 04800000
SET RETURN_POINT = 'S100'; 04810000
GOTO INSERTLINE; 04820000
END IF; 04830000
04840000
S100: -- Output the SPECIAL REGISTERS clause 04850000
IF hvSPECIAL_REGS <> ' ' THEN 04860000
SET LINE = hvSPECIAL_REGS; 04870000
SET RETURN_POINT = 'T100'; 04880000
GOTO INSERTLINE; 04890000
END IF; 04900000
04910000
T100: -- Output the CALLED ON NULL INPUT clause 04920000
IF hvNULL_CALL = 'Y' THEN 04930000
SET LINE = 'CALLED ON NULL INPUT'; 04940000
SET RETURN_POINT = 'U100'; 04950000
GOTO INSERTLINE; 04960000
END IF; 04970000
04980000
U100: -- Finish up 04990000
GOTO DONE; 05000000
05010000
INSERTLINE: 05020000
SET LINE_LENGTH = LENGTH(LINE); 05030000
WHILE LINE_LENGTH > 72 DO 05040000
SET ROW = SUBSTR(LINE, 1, 72) || REPEAT( ' ', 8 ); 05050000
SET LINE = SUBSTR(LINE, 73, LINE_LENGTH-72); 05060000
SET LINE_LENGTH = LENGTH(LINE); 05070000
05080000
SET ROW_SEQUENCE = ROW_SEQUENCE + 1; 05090000
INSERT INTO DSN8.DSN8ES3_RS_TBL 05100000
( RS_SEQUENCE, 05110000
RS_LINE ) 05120000
VALUES( P1.ROW_SEQUENCE, 05130000
P1.ROW ); 05140000
END WHILE; 05150000
05160000
SET ROW = SUBSTR( (LINE || REPEAT(' ', 80)), 1, 80); 05170000
SET ROW_SEQUENCE = ROW_SEQUENCE + 1; 05180000
SET OPERATION = 'INSERT'; 05190000
INSERT INTO DSN8.DSN8ES3_RS_TBL 05200000
( RS_SEQUENCE, 05210000
RS_LINE ) 05220000
VALUES( P1.ROW_SEQUENCE, 05230000
P1.ROW ); 05240000
CASE RETURN_POINT 05250000
WHEN 'A100' THEN GOTO A100; 05260000
WHEN 'B100' THEN GOTO B100; 05270000
WHEN 'C100' THEN GOTO C100; 05280000
WHEN 'D100' THEN GOTO D100; 05290000
WHEN 'E100' THEN GOTO E100; 05300000
WHEN 'F100' THEN GOTO F100; 05310000
WHEN 'G100' THEN GOTO G100; 05320000
WHEN 'H100' THEN GOTO H100; 05330000
WHEN 'I100' THEN GOTO I100; 05340000
WHEN 'J100' THEN GOTO J100; 05350000
WHEN 'K100' THEN GOTO K100; 05360000
WHEN 'L100' THEN GOTO L100; 05370000
WHEN 'M100' THEN GOTO M100; 05380000
WHEN 'N100' THEN GOTO N100; 05390000
WHEN 'O100' THEN GOTO O100; 05400000
WHEN 'P100' THEN GOTO P100; 05410000
WHEN 'Q100' THEN GOTO Q100; 05420000
WHEN 'R100' THEN GOTO R100; 05430000
WHEN 'S100' THEN GOTO S100; 05440000
WHEN 'T100' THEN GOTO T100; 05450000
WHEN 'U100' THEN GOTO U100; 05460000
ELSE GOTO DONE; 05470000
END CASE; 05480000
05490000
DONE: 05500000
-- Open the cursor to the result set 05510000
SET OPERATION = 'RS CURSOR'; 05520000
OPEN DSN8ES3_RS_CSR; 05530000
END P1 05540000