DSN8EPU
PASS Db2 UTILITY STATEMENTS TO BE EXECUTED BY THE STORED PROCEDURE PROGRAM DSNUTILS.
DSN8EPU: PROC OPTIONS(MAIN); 00010000
/******************************************************************** 00020000
* MODULE NAME = DSN8EPU (SAMPLE PROGRAM) * 00030000
* * 00040000
* DESCRIPTIVE NAME = STORED PROCEDURE REQUESTER PROGRAM * 00050000
* * 00060000
* LICENSED MATERIALS - PROPERTY OF IBM * 00070000
* 5625-DB2 * 00080001
* (C) COPYRIGHT 1992, 2003 IBM CORP. * 00090001
* * 00100000
* STATUS = VERSION 8 * 00110001
* * 00120000
* FUNCTION = * 00130000
* * 00140000
* PASS DB2 UTILITY STATEMENTS TO BE EXECUTED BY THE STORED * 00150000
* PROCEDURE PROGRAM DSNUTILS. GET INPUT FROM 'SYSIN'. * 00160000
* PASS THE STATEMENT AND RECEIVE THE OUTPUT RESULTS * 00170000
* VIA A RETURNED CURSOR. WRITE THE RESULTS TO 'SYSPRINT'. * 00180000
* * 00190000
* DEPENDENCIES = NONE * 00200000
* * 00210000
* RESTRICTIONS = * 00220000
* * 00230000
* INPUT = * 00240000
* * 00250000
* 1. INPUT MUST BE OF THE FORM * 00260000
* * 00270000
* Uid='',Restart='',Utility='REORG TABLESPACE', * 00280000
* CopyDSN1='SYSADM.COPYDDN.DSN8D51A.DSN8S51E', * 00290000
* CopyDEVT1='SYSDA',CopySpace1=10, * 00300000
* Utstmt= * 00310000
* ' REORG TABLESPACE DSN8D51A.DSN8S51E * 00320000
* SORTDEVT SYSDA SORTNUM 2 * 00330000
* SHRLEVEL CHANGE * 00340000
* DEADLINE 2010-2-4-03.15.00 * 00350000
* MAPPINGTABLE DSN8510.MAP_TBL * 00360000
* MAXRO 240 LONGLOG DRAIN DELAY 900 * 00370000
* '; * 00380000
* * 00390000
* MODULE TYPE = PROCEDURE * 00400000
* PROCESSOR = * 00410000
* ADMF PRECOMPILER * 00420000
* PL/I MVS/VM (FORMERLY PL/I SAA AD/CYCLE) * 00430000
* MODULE SIZE = 2K * 00440000
* ATTRIBUTES = RE-ENTERABLE * 00450000
* * 00460000
* ENTRY POINT = DSN8EPU * 00470000
* PURPOSE = SEE FUNCTION * 00480000
* LINKAGE = STANDARD MVS PROGRAM INVOCATION. * 00490000
* INPUT = PARAMETERS EXPLICITLY PASSED TO THIS FUNCTION: * 00500000
* SYMBOLIC LABEL/NAME = SYSIN * 00510000
* DESCRIPTION = DDNAME OF SEQUENTIAL DATA SET CONTAINING * 00520000
* DSNUTILS STORED PROCEDURE PARAMETERs. * 00530000
* OUTPUT = PARAMETERS EXPLICITLY RETURNED: * 00540000
* SYMBOLIC LABEL/NAME = SYSPRINT * 00550000
* DESCRIPTION = DDNAME OF SEQUENTIAL OUTPUT DATA SET TO * 00560000
* CONTAIN RESULTS OF THE UTILITIES EXECUTED. * 00570000
* * 00580000
* EXIT NORMAL = * 00590000
* * 00600000
* NORMAL MESSAGES = * 00610000
* * 00620000
* EXIT-ERROR = * 00630000
* * 00640000
* * 00650000
* ABEND CODES = NONE * 00660000
* * 00670000
* ERROR MESSAGES = * 00680000
* * 00690000
* EXTERNAL REFERENCES = * 00700000
* ROUTINES/SERVICES = NONE * 00710000
* DATA-AREAS = NONE * 00720000
* CONTROL-BLOCKS = * 00730000
* SQLCA - SQL COMMUNICATION AREA * 00740000
* * 00750000
* PSEUDOCODE = * 00760000
* * 00770000
* DSN8EPU: PROCEDURE. * 00780000
* DECLARATIONS. * 00790000
* INITIALIZE VARIABLES. * 00800000
* GET THE INPUT PARAMETERS AND COPY TO SYSPRINT. * 00810000
* EXEC SQL CALL SYSPROC.DSNUTILS. * 00820000
* DO UNTIL SQLCODE > 0. * 00830000
* EXEC SQL FETCH FROM RESULT SET. * 00840000
* PRINT RESULT SET TO SYSPRINT. * 00850000
* END. * 00860000
* * 00870000
* NOTICE = * 00880000
* THIS SAMPLE PROGRAM USES DB2 UTILITIES. SOME UTILITY FUNCTIONS* 00890000
* ARE ELEMENTS OF SEPARATELY ORDERABLE PRODUCTS. SUCCESSFUL USE* 00900000
* OF A PARTICULAR SAMPLE MAY BE DEPENDENT UPON THE OPTIONAL * 00910000
* PRODUCT BEING LICENSED AND INSTALLED IN YOUR ENVIRONMENT. * 00920000
* * 00930000
* CHANGE ACTIVITY = * 00940000
* PQ24720 - Add FILTRDSN and Fix I/O for seq #ed input * 00950000
* PQ44916 - Fix code hole closed by VA and Enterprise PL/I * 00960000
* d54292 - Check for unexpected SQLCODE in FETCH loop * 00970001
*******************************************************************/ 00980000
DCL SYSPRINT FILE OUTPUT STREAM; 00990000
01000000
DCL SYSIN FILE INPUT STREAM ENV( F RECSIZE(80) ); 01010000
01020000
DCL 01 SYSIN_REC, 01030000
05 UTIL_OPTS CHAR( 72 ), 01040000
05 SEQ_NOS CHAR( 08 ); 01050000
01060000
DCL SYSIN_EOF BIT( 01 ) INIT( '0'B ); 01070000
ON ENDFILE( SYSIN ) 01080000
SYSIN_EOF = '1'B; 01090000
01100000
DCL UTIL_OPTS_BUFF VARYING CHAR( 32760 ) INIT( '' ); 01110000
01120000
DCL ADDR BUILTIN; 01130000
DCL NULL BUILTIN; 01140000
DCL PLIRETC BUILTIN; 01150000
01160000
DCL UID CHAR(16) VARYING; /* UTILITY ID */ 01170000
DCL RESTART CHAR(8) VARYING; /* RESTART */ 01180000
DCL UTSTMT CHAR(32704) VARYING; 01190000
DCL RETCODE FIXED BIN(31); 01200000
DCL UTILITY CHAR(20) VARYING; 01210000
DCL RECDSN CHAR(44) VARYING, 01220000
RECDEVT CHAR(8), 01230000
RECSPACE FIXED BIN(15); 01240000
DCL DISCDSN CHAR(44) VARYING, 01250000
DISCDEVT CHAR(8), 01260000
DISCSPACE FIXED BIN(15); 01270000
DCL PNCHDSN CHAR(44) VARYING, 01280000
PNCHDEVT CHAR(8), 01290000
PNCHSPACE FIXED BIN(15); 01300000
DCL COPYDSN1 CHAR(44) VARYING, 01310000
COPYDEVT1 CHAR(8), 01320000
COPYSPACE1 FIXED BIN(15); 01330000
DCL COPYDSN2 CHAR(44) VARYING, 01340000
COPYDEVT2 CHAR(8), 01350000
COPYSPACE2 FIXED BIN(15); 01360000
DCL RCPYDSN1 CHAR(44) VARYING, 01370000
RCPYDEVT1 CHAR(8), 01380000
RCPYSPACE1 FIXED BIN(15); 01390000
DCL RCPYDSN2 CHAR(44) VARYING, 01400000
RCPYDEVT2 CHAR(8), 01410000
RCPYSPACE2 FIXED BIN(15); 01420000
DCL WORKDSN1 CHAR(44) VARYING, 01430000
WORKDEVT1 CHAR(8), 01440000
WORKSPACE1 FIXED BIN(15); 01450000
DCL WORKDSN2 CHAR(44) VARYING, 01460000
WORKDEVT2 CHAR(8), 01470000
WORKSPACE2 FIXED BIN(15); 01480000
DCL MAPDSN CHAR(44) VARYING, 01490000
MAPDEVT CHAR(8), 01500000
MAPSPACE FIXED BIN(15); 01510000
DCL ERRDSN CHAR(44) VARYING, 01520000
ERRDEVT CHAR(8), 01530000
ERRSPACE FIXED BIN(15); 01540000
DCL FILTRDSN CHAR(44) VARYING, 01550000
FILTRDEVT CHAR(8), 01560000
FILTRSPACE FIXED BIN(15); 01570000
DCL RESULTS SQL TYPE IS RESULT_SET_LOCATOR VARYING; 01580000
DCL SEQNO FIXED BIN(31); 01590000
DCL TEXT CHAR(122) VARYING; 01600000
EXEC SQL INCLUDE SQLCA; 01610000
Uid=''; 01620000
Restart=''; 01630000
Utstmt=''; 01640000
RetCode = 0; 01650000
Utility=''; 01660000
RecDSN=''; RecDEVT=''; RecSpace=0; 01670000
DiscDSN=''; DiscDEVT=''; DiscSpace=0; 01680000
PnchDSN=''; PnchDEVT=''; PnchSpace=0; 01690000
CopyDSN1=''; CopyDEVT1=''; CopySpace1=0; 01700000
CopyDSN2=''; CopyDEVT2=''; CopySpace2=0; 01710000
RcpyDSN1=''; RcpyDEVT1=''; RcpySpace1=0; 01720000
RcpyDSN2=''; RcpyDEVT2=''; RcpySpace2=0; 01730000
WorkDSN1=''; WorkDEVT1=''; WorkSpace1=0; 01740000
WorkDSN2=''; WorkDEVT2=''; WorkSpace2=0; 01750000
MapDSN=''; MapDEVT=''; MapSpace=0; 01760000
ErrDSN=''; ErrDEVT=''; ErrSpace=0; 01770000
FiltrDSN=''; FiltrDEVT=''; FiltrSPACE=0; 01780000
01790000
/* Collect DSNUTILS options from SYSIN records, columns 1-72 */ 01800000
GET COPY EDIT( UTIL_OPTS,SEQ_NOS ) ( A(72),A(8) ); 01810000
DO WHILE( ^SYSIN_EOF ); 01820000
UTIL_OPTS_BUFF = UTIL_OPTS_BUFF || UTIL_OPTS; 01830000
GET COPY EDIT( UTIL_OPTS,SEQ_NOS )( A(72),A(8) ); 01840000
END; /* DO WHILE( ^SYSIN_EOF ); */ 01850000
01860000
/* Assign DSNUTILS options from inputted settings in UTIL_OPTS_BUFF */ 01870000
GET STRING( UTIL_OPTS_BUFF ) DATA; 01880000
01890000
/* Call DSNUTILS stored procedure to process the inputted settings */ 01900000
EXEC SQL 01910000
CALL SYSPROC.DSNUTILS(:UID, :RESTART, 01920000
:UTSTMT, 01930000
:RETCODE, 01940000
:UTILITY, 01950000
:RECDSN ,:RECDEVT ,:RECSPACE , 01960000
:DISCDSN ,:DISCDEVT ,:DISCSPACE , 01970000
:PNCHDSN ,:PNCHDEVT ,:PNCHSPACE , 01980000
:COPYDSN1,:COPYDEVT1,:COPYSPACE1, 01990000
:COPYDSN2,:COPYDEVT2,:COPYSPACE2, 02000000
:RCPYDSN1,:RCPYDEVT1,:RCPYSPACE1, 02010000
:RCPYDSN2,:RCPYDEVT2,:RCPYSPACE2, 02020000
:WORKDSN1,:WORKDEVT1,:WORKSPACE1, 02030000
:WORKDSN2,:WORKDEVT2,:WORKSPACE2, 02040000
:MAPDSN ,:MAPDEVT ,:MAPSPACE , 02050000
:ERRDSN ,:ERRDEVT ,:ERRSPACE , 02060000
:FILTRDSN,:FILTRDEVT,:FILTRSPACE); 02070000
IF SQLCODE < 0 THEN 02080000
DO; 02090000
PUT SKIP EDIT('CALL SQLCA')(A); 02100000
PUT SKIP DATA(SQLCA); 02110000
CALL PLIRETC(8); /* SET PLI RETURN CODE */ 02120000
RETURN; 02130000
END; 02140000
02150000
EXEC SQL 02160000
ASSOCIATE LOCATOR (:RESULTS) WITH PROCEDURE SYSPROC.DSNUTILS; 02170000
IF SQLCODE < 0 THEN 02180000
DO; 02190000
PUT SKIP EDIT('ASSOCIATE LOCATOR SQLCA')(A); 02200000
PUT SKIP DATA(SQLCA); 02210000
CALL PLIRETC(8); /* SET PLI RETURN CODE */ 02220000
RETURN; 02230000
END; 02240000
02250000
EXEC SQL 02260000
ALLOCATE SYSPRINT CURSOR FOR RESULT SET :RESULTS; 02270000
IF SQLCODE < 0 THEN 02280000
DO; 02290000
PUT SKIP EDIT('ALLOCATE SYSPRINT SQLCA')(A); 02300000
PUT SKIP DATA(SQLCA); 02310000
CALL PLIRETC(8); /* SET PLI RETURN CODE */ 02320000
RETURN; 02330000
END; 02340000
02350000
FETCHLOOP: 02360000
DO UNTIL(SQLCODE < 0 | SQLCODE = 100 ); 02370001
EXEC SQL 02380000
FETCH SYSPRINT INTO :SEQNO, :TEXT; 02390000
IF (SQLCODE >= 0) 02400000
& (SQLCODE ^= 100) THEN 02410000
DO; 02420000
PUT SKIP EDIT(TEXT)(A); 02430000
END; 02440000
IF (SQLCODE ^= 0) 02450000
& (SQLCODE ^= 100) THEN 02460000
DO; 02470000
PUT SKIP EDIT('FETCH SYSPRINT SQLCA')(A); 02480000
PUT SKIP DATA(SQLCA); 02490000
END; 02500000
END FETCHLOOP; 02510000
IF SQLCODE < 0 THEN 02520000
DO; 02530000
CALL PLIRETC(8); /* SET PLI RETURN CODE */ 02540000
RETURN; 02550000
END; 02560000
02570000
PUT SKIP DATA(RetCode); 02580000
CALL PLIRETC(RetCode); /* SET PLI RETURN CODE */ 02590000
END DSN8EPU; 02600000