Following is an example of coding the needed procedure in C. The comments in the procedure program example provide a guide to the procedure building process.
For COBOL examples see Sample JCL for z/OS for z/OS and Sample JCL for z/VSE for z/VSE. PL/I or Assembler could also have been used to code the procedure.
Before coding your first procedure, see Transaction Integrity, Restrictions, and Parameter Styles and Error Handling.
Note: Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the note above.
//TWOUP OUTPUT DEFAULT=YES,FORMDEF=010111,PAGEDEF=W120C0,CHARS=(GT20)
+INC GRB.JOBLIB3 0000010
+INC GRB.EDCC
+INC GRB.CEEVARS
//* ****************************
//* * "C" PRECOMPILE STEP ***
//* ****************************
//CPRECMP EXEC PGM=DBPLIPR,PARM='PLANNAME=ITEMKILL'
//PROCLIB DD DSN=CA90SMVS.NEWC.R3V1.PROCLIB,DISP=SHR
//SOURCE DD DSN=DCMDEV.SQL.GARBR02.SRCLIB2(ITEMKILL),DISP=SHR
//SYSUDUMP DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//OPTIONS DD DSN=DCMDEV.SQL.GARBR02.SRCLIB2(TPRCCOPT),DISP=SHR
//SRCOUT DD DSN=&&SRC,DISP=(,PASS,DELETE),UNIT=VIO,
// SPACE=(2000,(200,200)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//* ****************************************************
//*
//COMPA EXEC PROC=EDCC, (FROM SYS2.PROCLIB)
// CRUN='RENT',
// CPARM='NOMARGINS,NOSEQUENCE,LIST,SOURCE',
// CPARM2='LOCALE("POSIX"),LANGLVL(ANSI),OMVS,DLL',
// CPARM3='SSCOM,LONGNAME,SHOWINC',
// INFILE='&&SRC',
// OUTFILE='DCMDEV.SQL.GARBR02.OBJLIB(ITEMKILO)'
//*
//SYSLIB DD DSN=CEE.SCEEH.H,DISP=SHR
// DD DSN=CEE.SCEEH.SYS.H,DISP=SHR
//USERLIB DD DSN=DCMDEV.SQL.LIBRMAST,DISP=SHR,SUBSYS=LAM
//*
//* SYSCPRT DD DSN=DCMDEV.SQL.PRINT(TCPG002),DISP=SHR
//*
//STEP1 EXEC PGM=DBUTLTY,REGION=2048K,COND=EVEN
//PXX DD DSN=DCMDEV.DB.MUF3.PXX,DISP=SHR
//CXX DD DSN=DCMDEV.DB.MUF3.CXX,DISP=SHR
//SNAPER DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
REPORT AREA=PXX,DUMPS=FULL
/*
//* *********** LINK STARTS HERE **********************
//PRELINK EXEC PGM=EDCPRLK,
// PARM='POSIX(OFF)/OE,MEMORY,DUP,NOER,MAP,NOUPCASE,NONCAL'
//*
//SYSMSGS DD DSN=CEE.SCEEMSGP(EDCPMSGE),DISP=SHR
//OBJLIB DD DSN=DCMDEV.SQL.GARBR02.OBJLIB,DISP=SHR
//C8941 DD DSN=CEE.SCEEOBJ,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSPRINT DD *
//SYSMOD DD DSN=DCMDEV.SQL.GARBR02.OBJLIB(ITEMKOBJ),DISP=SHR
//SYSDEFSD DD DUMMY
//SYSIN DD *
INCLUDE OBJLIB(ITEMKILO)
/*
//LINKEDIT EXEC PGM=LINKEDIT,
// PARM=('AMODE=31,RMODE=ANY,TERM=YES,MSGLEVEL=0,MAP,DYNAM=DLL',
// 'CALL=YES,CASE=MIXED,REUS=RENT,EDIT=YES')
//SYSLIB DD DSN=CEE.SCEELKED,DISP=SHR
// DD DSN=SYS1.CSSLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSLMOD DD DISP=SHR,DSN=DCMDEV.SQL.GARBR02.LODLIB2(ITEMKILL)
//OBJLIB DD DSN=DCMDEV.SQL.GARBR02.OBJLIB,DISP=SHR
//*CALIB DD DSN=DCMDEV.DB.R100.LODLIB,DISP=SHR
//CALIB DD DSN=DCMDEV.DB.R100.LODLIB,DISP=SHR
//CEELIB DD DSN=DCMDEV.DBDT.DSYTEST.LOADLIB,DISP=SHR
//SYSLIN DD *
INCLUDE OBJLIB(ITEMKOBJ)
INCLUDE CEELIB(CEEUOPT)
INCLUDE CALIB(DBXPIPR)
/*
//*+INC GRB.URTCEE
/********** Program source starts below. ************/
/* ITEMKILL - C Procedure example. */
/*
** This procedure is triggered when a supplier cancels production of
** a product in our consumer catalog. The program checks to see how
** many open orders we need to cancel and decides, based on this
** number, whether to send apology letters to a small number of
** customers, or to generate an error message instructing us to contact
** the supplier to attempt to fill the orders. This procedure is
** passed an input parameter that determines the number of orders we
** are willing to cancel (if any).
*/
/* The procedure you write must be re-entrant. */
#pragma options(RENT)
/*
** Use of the linkage pragma is required to tell the C compiler that
** our load module is "fetched" for execution at runtime.
*/
#pragma linkage(itemKill,FETCHABLE)
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
/* The following structure maps to a VARCHAR(128) data item in SQL (FYI). */
typedef struct varChar128
short length;
char data 128รก;
SQL_VARCHAR_128;
/*
** The following structure maps to the additional parameters passed
** to your procedure when the "DATACOM SQL" parameter-style has
** been specified by the CREATE PROCEDURE. Note that the variable
** containing the sqlcode may not be named "sqlcode" because our
** precompiler generates an SQLCA that uses the name. These parameters
** enable your program to control the SQLCODE that SQL sees as
** the result of the CALL/EXECUTE PROCEDURE statement that
** was executed or triggered. Note that a negative SQLCODE-OUT
** aborts any INSERT, UPDATE, or DELETE that triggers it. See the
** "Parameter Styles and Error Handling" section for more details.
*/
typedef struct parmsDatacomSQL
int *sqlcodeOut;
SQL_VARCHAR_128 *procName;
SQL_VARCHAR_128 *specName;
SQL_VARCHAR_128 *errMsgOut; /* Truncated to 80 bytes in 10.0. */
char *dbExtCodeOut;
short *dbIntCodeOut;
SQL_PROC_PARMS_DCM;
void userDefinedErrorDoc(SQL_PROC_PARMS_DCM *dcmSqlParms, char *errMsg);
/*
** The function name used below must match both that of the load-
** module that we are going to produce, and the EXTERNAL name defined
** by the CREATE PROCEDURE statement that we execute later.
**
** Note that the data pointed to by the formal parameters
** precisely correspond to the parameter definitions specified
** in the CREATE PROCEDURE statement and appear in the
** same order. The C-language variables chosen to process the
** data must match in data-type, size, and order.
**
** When SQL regains control after execution of the procedure, it
** ignores any data that your program stored into parameters defined
** by the CREATE PROCEDURE statement to be input only ("IN").
**
** In order to minimize the confusion that a newly-defined trigger
** can cause for a preexisting application that uses the navigational
** (record-at-a-time) API rather than SQL, we handle DB return codes
** for DATACOM SQL style procedures as follows:
**
** If the SQLCODE returned from the procedure is zero or positive, a
** nonblank DB return code is ignored. If the procedure returned a
** negative SQLCODE and was explicitly called as opposed to being
** triggered, DB external and internal return codes are reset to the
** values returned through the procedure's parameter list. If the
** procedure returned a negative SQLCODE and was triggered, we store
** the SQLCODE at offset 26 decimal (signed binary fullword) into the
** user's Request Area, for the DB return code(s) to 94(100), then
** then document the DB return codes returned from the procedure
** at offsets 30 decimal (2-byte character) and 32 decimal (1-byte
** unsigned binary) into the user's Request Area for the external and
** internal return codes, respectively. This is done to allow users
** of navigational programs to differentiate between failures
** inside procedures and those related to their specific DB requests,
** since they generally do not have logic to interpret an SQLCODE.
**
** Note that the first three parameters would appear in the formal
** parameter list regardless of the PARAMETER STYLE specified
** by the CREATE PROCEDURE statement. The next three parameters
** are null indicator variables corresponding to the first three,
** and appear only under certain parameter styles (in Version
** 10.0, they appear under DATACOM SQL and GENERAL WITH NULLS).
** The "dcmSqlParms" parameter (see previous explanation) appears
** only under parameter style DATACOM SQL.
*/
int itemKill(int *canceledPartIdIn, int *vendorIdIn,
int *maxBadOrdersIn, short *canceledPartIdNull,
short *vendorIdNull, short *maxBadOrdersNull,
SQL_PROC_PARMS_DCM dcmSqlParms)
EXEC SQL BEGIN DECLARE SECTION;
int canceledPartId = *canceledPartIdIn;
int vendorId = *vendorIdIn;
int numBackOrders = 0;
int numOrdersCanceled = 0;
char *errMsg;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER NOT FOUND goto end;
EXEC SQL WHENEVER SQLERROR goto sqlError;
/*
** Initialize output parameters.
** Since triggers may not call procedures that have output
** (OUT or INOUT) parameters, and we intend to use this
** procedure as a trigger, we have coded/created it without
** output parameters other than those required for parameter
** style DATACOM SQL.
*/
*(dcmSqlParms.sqlcodeOut) = 0;
*(dcmSqlParms.dbExtCodeOut) = 0;
*(dcmSqlParms.dbIntCodeOut) = 0;
dcmSqlParms.errMsgOut->length = 0; /* SQL 10.0 maximum is 80. */
memset(dcmSqlParms.errMsgOut->data, 0, 80);
/* Handle nulls on input. */
if (*canceledPartIdNull == -1)
errMsg = "ITEM_ORDER_KILLER ABORTED: CANCELED PART ID IS NULL";
else if (*vendorIdNull == -1)
errMsg = "ITEM_ORDER_KILLER ABORTED: VENDOR ID IS NULL";
else
errMsg = NULL;
if (*maxBadOrdersNull == -1)
*maxBadOrdersIn = 0;
/* Quit if an error message was produced. */
if (errMsg)
userDefinedErrorDoc(&dcmSqlParms., errMsg);
goto end;
/* How many back orders for this item have to be canceled? */
EXEC SQL
select count(*)
into :numBackOrders
from sales.order_items
where item_id = :canceledPartId and
item_status = 'BACK-ORDERED';
/* Handle outstanding orders. */
if (numBackOrders > 0)
/*
** This cancellation by the supplier affects too many
** orders. Try to get him to honor the orders.
*/
if (numBackOrders > *maxBadOrdersIn)
userDefinedErrorDoc(&dcmSqlParms.,
"ITEM_ORDER_KILLER DETECTED EXCEEDED ORDER CANCELLATION LIMIT");
else
/*
** Cancel orders and send apology letters to customers
** whose orders are being canceled.
*/
EXEC SQL
update sales.order_items
set item_status = 'CANCELED',
comments = 'ITEM DISCONTINUED'
where item_id = :canceledPartId and
item_status = 'BACK-ORDERED';
EXEC SQL
insert into customer.apology_letters
(customer_id, order_id, item_id, quantity,
comments, problem_type)
select A.customer_id, A.order_id, B.item_id,
B.quantity, B.comments, 'ITEM DISCONTINUED'
from sales.orders A, sales.order_items B
where A.order_id = B.order_id and
B.item_id = :canceledPartId and
B.item_status = 'CANCELED';
numOrdersCanceled = numBackOrders;
/* Record the problem so we can track "problem" vendors. */
EXEC SQL
insert into vendor.problems
(vendor_id, problem_type, num_orders_affected,
num_orders_canceled, related_item_id,
problem_date, resolution_date)
values (:vendorId, 'ITEM DISCONTINUED', :numBackOrders,
:numOrdersCanceled, :canceledPartId,
CURRENT DATE, NULL);
end:
return(0);
sqlError:
/*
** Supply error information to caller using output parameters.
** Note that the precompiler automatically includes the "sqlca"
** structure in your program.
*/
*(dcmSqlParms.sqlcodeOut) = sqlca.sqlca_code;
*(dcmSqlParms.dbIntCodeOut) = sqlca.sqlca_dbcode_int;
dcmSqlParms.errMsgOut->length = sqlca.sqlca_err_len;
memcpy(dcmSqlParms.dbExtCodeOut, sqlca.sqlca_dbcode_ext, 2);
memcpy(dcmSqlParms.errMsgOut->data, sqlca.sqlca_err_msg,
sqlca.sqlca_err_len);
/*
** Note that the output of this "printf" statement would have
** appeared in a SYSOUT file attached to the output of the
** Multi-user job, so I have decided its use here is inappropriate:
** printf("ITEMKILL FAILED WITH SQLCODE = %d.", *(dcmSqlParms.sqlcodeOut));
*/
goto end;
/* Generate documentation for a user-defined error. */
void userDefinedErrorDoc(SQL_PROC_PARMS_DCM *dcmSqlParms, char *errMsg)
*(dcmSqlParms->sqlcodeOut) = -534;/* User-defined error. */
dcmSqlParms->errMsgOut->length = 80; /* SQL 10.0 maximum. */
memcpy(dcmSqlParms->errMsgOut->data, errMsg,
(strlen(errMsg) > 80 ? 80 : strlen(errMsg)));
return;
000200 /*
000201 //SYSUDUMP DD SYSOUT=*
000202 //REPORT DD SYSOUT=*
000203 //SYSPRINT DD SYSOUT=*
000204 //SYSOUT DD SYSOUT=*
000205 //SRCOUT DD DSN=&.&SRC.,DISP=(,PASS,DELETE),UNIT=VIO,
000206 // SPACE=(2000,(200,200)),
000207 // DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
000208 //*
000209 //* LE C COMPILE
000210 //COMPA EXEC PROC=EDCC, (FROM SYS2.PROCLIB)
000211 // CRUN='RENT',
000212 // CPARM='NOMARGINS,NOSEQUENCE,LIST,SOURCE',
000213 // CPARM2='LOCALE("POSIX"),LANGLVL(ANSI),OMVS,DLL',
000214 // CPARM3='SSCOM,LONGNAME,SHOWINC',
000215 // INFILE='&.&SRC'.,
000216 // OUTFILE='dsnname.OBJLIB(ITEMKILO)'
000217 //*
000218 //SYSLIB DD DSN=CEE.SCEEH.H,DISP=SHR
000219 // DD DSN=CEE.SCEEH.SYS.H,DISP=SHR
000220 //USERLIB DD DSN=DCMDEV.SQL.LIBRMAST,DISP=SHR,SUBSYS=LAM
000221 //* *********** LINK STARTS HERE **********************
000222 //PRELINK EXEC PGM=EDCPRLK,
000223 // PARM='POSIX(OFF)/OE,MEMORY,DUP,NOER,MAP,NOUPCASE,NONCAL'
000224 //*
000225 //SYSMSGS DD DSN=CEE.SCEEMSGP(EDCPMSGE),DISP=SHR
000226 //OBJLIB DD DSN=dsnname.OBJLIB,DISP=SHR
000227 //C8941 DD DSN=CEE.SCEEOBJ,DISP=SHR
000228 //SYSOUT DD SYSOUT=*
000229 //SYSPRINT DD *
000230 //SYSMOD DD DSN=dsnname.OBJLIB(ITEMKOBJ),DISP=SHR
000231 //SYSDEFSD DD DUMMY
000232 //SYSIN DD *
000233 INCLUDE OBJLIB(ITEMKILO)
000234 /*
000235 //LINKEDIT EXEC PGM=LINKEDIT,
000236 // PARM=('AMODE=31,RMODE=ANY,TERM=YES,MSGLEVEL=0,MAP,DYNAM=DLL',
000237 // 'CALL=YES,CASE=MIXED,REUS=RENT,EDIT=YES')
000238 //SYSLIB DD DSN=CEE.SCEELKED,DISP=SHR
000239 // DD DSN=SYS1.CSSLIB,DISP=SHR
000240 //SYSPRINT DD SYSOUT=*
000241 //SYSTERM DD SYSOUT=*
000242 //SYSLMOD DD DISP=SHR,DSN=dsnname.LODLIB2(ITEMKILL)
000243 //OBJLIB DD DSN=dsnname.OBJLIB,DISP=SHR
000244 //CAILIB DD DSN=xxxxxx.xx.xxxx.LODLIB,DISP=SHR
000245 //CEELIB DD DSN=xxxxxx.xxxx.xxxxxx.LOADLIB,DISP=SHR
000246 //SYSLIN DD *
000247 INCLUDE OBJLIB(ITEMKOBJ)
000248 INCLUDE CEELIB(CEEUOPT)
000249 INCLUDE CALIB(DBXPIPR)
000250 /*
Please note that the library containing the procedure's load module must be added to the STEPLIB or JOBLIB of the Multi-User Facility startup JCL.
|
Copyright © 2014 CA.
All rights reserved.
|
|