sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hello Guyz,
I wanted to create a simple stored procedure.
I read the manual, took a sample from SDSNSAMP. Altered a few things according to my shop.
But still, no success. This is the JCL JOB.
Code: |
//******************************************************************** 00010000
//* NAME = DSNTEJ6S 00020000
//* 00030000
//******************************************************************** 00270000
//JOBLIB DD DISP=SHR,DSN=DB2.DB2D.DSNEXIT 00280000
// DD DISP=SHR,DSN=DB2.DB2D.DSNLOAD 00286000
//* 00300000
//******************************************************************** 00310000
//* STEP 1: DROP ANY EXISTING STORED PROCEDURE CALLED DSN.DSN8EP2 00320000
//******************************************************************** 00340000
//PH06SS01 EXEC PGM=IKJEFT01,DYNAMNBR=20 00350000
//SYSTSPRT DD SYSOUT=* 00360000
//SYSTSIN DD * 00370000
DSN SYSTEM(DB2D) 00380000
RUN PROGRAM(DSNTIAD) PLAN(BOBPLAN) - 00390000
LIB('DB2.DB2D.RUNLIB.LOAD') - 00400000
PARM('RC0') 00405000
//SYSPRINT DD SYSOUT=* 00410000
//SYSUDUMP DD SYSOUT=* 00420000
//SYSIN DD * 00430000
00440000
DROP PROCEDURE HXSULL.UPDATSAL RESTRICT; 00450000
00470000
//* 00630000
//******************************************************************** 00640000
//* STEP 2: CREATE SAMPLE STORED PROCEDURE DSN8.DSN8EP2 00650000
//******************************************************************** 00660000
//PH06SS02 EXEC PGM=IKJEFT01,DYNAMNBR=20 00670000
//SYSTSPRT DD SYSOUT=* 00670200
//SYSTSIN DD * 00670400
DSN SYSTEM(DB2D) 00670600
RUN PROGRAM(DSNTIAD) PLAN(BOBPLAN) - 00670800
LIB('DB2.DB2D.RUNLIB.LOAD') 00671000
//SYSPRINT DD SYSOUT=* 00671300
//SYSUDUMP DD SYSOUT=* 00671600
//SYSIN DD * 00671900
CREATE PROCEDURE HXSULL.UPDATSAL ( IN EMPID VARCHAR(6), 00672000
OUT NETSALARY DECIMAL(8, 2) ) 00673000
PARAMETER CCSID EBCDIC 00674000
FENCED 00675000
RESULT SET 1 00676000
LANGUAGE SQL 00677000
NOT DETERMINISTIC 00678000
MODIFIES SQL DATA 00678100
NO DBINFO 00678200
COLLID BOBC 00678300
WLM ENVIRONMENT DB2DW1SP 00678400
ASUTIME NO LIMIT 00678500
STAY RESIDENT NO 00678600
PROGRAM TYPE MAIN 00678700
SECURITY DB2 00678800
COMMIT ON RETURN YES 00678900
------------------------------------------------------------------------00679000
-- SQL STORED PROCEDURE 00679100
-- EMPNO 00679200
-- SALARY 00679300
------------------------------------------------------------------------00679400
P1: BEGIN 00679500
-- DECLARE VARIABLES 00679600
00679700
DECLARE SALARYINCREMNT DECIMAL(8,2) DEFAULT 30.0; 00679800
DECLARE TOTALSALARY DECIMAL(8,2) DEFAULT 0.0; 00679900
00680000
P2: BEGIN 00680100
DECLARE CURRENTSALARY DECIMAL(8,2) DEFAULT 0.0; 00680200
00680300
-- DECLARE CURSOR 00680400
DECLARE CURSOR1 CURSOR FOR 00680500
SELECT SALARY FROM SHANDB.DEMPLOYEE 00680600
WHERE EMPNO= EMPID; 00680700
00680800
OPEN CURSOR1; 00680900
00681000
FETCH CURSOR1 INTO CURRENTSALARY; 00681100
SET TOTALSALARY = SALARYINCREMNT + CURRENTSALARY; 00681200
00681300
END P2; 00681400
00681500
UPDATE SHANDB.DEMPLOYEE SET SALARY = TOTALSALARY 00681600
WHERE EMPNO = EMPID; 00681700
END P1 00681800
//* 00681900
//******************************************************************** 00682000
//* STEP 3: PRE-COMPILE, COMPILE, AND LINK-EDIT THE STORED PROCEDURE 00682100
//******************************************************************** 00682200
//PH06SS03 EXEC DSNHPLI,MEM=UPDATSAL,COND=(4,LT), 00682300
// PARM.PC='HOST(PLI),CCSID(37),STDSQL(NO),CONNECT(2)' 00683000
//PPLI.SYSIN DD DSN=HXSULL.DB2.JCL(UPDATSAL), 00690000
// DISP=SHR 00700000
//PC.DBRMLIB DD DSN=NLV0.DLMF.DBRM.HXSULL(UPDATSAL), 00710000
// DISP=SHR 00720000
//PC.SYSLIB DD DSN=DB2.POPR.V81.SRCLIB.DATA, 00730000
// DISP=SHR 00740000
//LKED.SYSLMOD DD DSN=NLV0.DLMF.LOAD.HXSULL(UPDATSAL), 00750000
// DISP=SHR 00760000
//LKED.SYSIN DD * 00770000
INCLUDE SYSLIB(DSNRLI) 00780000
INCLUDE SYSLIB(DSNTIAR) 00790000
// 00800000
//******************************************************************* 00810000
//* STEP 4: BIND THE STORED PROCEDURE PACKAGE 00820000
//* NOTE: THIS STEP IS COMMENTED OUT FOR THE STORED 00821000
//* PROCEDURE SAMPLE APPLICATION BECAUSE IT CONTAINS 00822000
//* NO SQL STATEMENTS. IF YOUR STORED PROCEDURE 00824000
//* CONTAINS SQL STATEMENTS, YOU MUST BIND IT AS 00826000
//* A PACKAGE. 00828000
//******************************************************************* 00830000
//PH06SS04 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) 00840000
//DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA, 00848000
// DISP=SHR 00856000
//SYSTSPRT DD SYSOUT=* 00865000
//SYSTSIN DD * 00874000
DSN SYSTEM(DB2D) 00883000
BIND PACKAGE(BOBC) - 00893000
MEMBER(UPDATSAL) ACT(REP) ISO(CS) ENCODING(EBCDIC) 00901000 |
The error iam getting is in the PRE-COMPILATION steps like
Code: |
006820 //********************************************************************
006821 //* STEP 3: PRE-COMPILE, COMPILE, AND LINK-EDIT THE STORED PROCEDURE
006822 //********************************************************************
006823 //PH06SS03 EXEC DSNHPLI,MEM=UPDATSAL,COND=(4,LT),
006830 // PARM.PC='HOST(PLI),CCSID(37),STDSQL(NO),CONNECT(2)'
==MSG> XXSTEPLIB DD DSN=IEL.V1R1M1.SIELCOMP,DISP=SHR
==MSG> CAY6066E DATA SET "IEL.V1R1M1.SIELCOMP" NOT FOUND ON VOLUME "1N2SR1"
==MSG>
006900 //PPLI.SYSIN DD DSN=HXSULL.DB2.JCL(UPDATSAL),
007000 // DISP=SHR
007100 //PC.DBRMLIB DD DSN=NLV0.DLMF.DBRM.HXSULL(UPDATSAL),
==MSG> //PC.DBRMLIB DD DSN=NLV0.DLMF.DBRM.HXSULL(UPDATSAL),
007200 // DISP=SHR
==MSG> // DISP=SHR
==MSG> X/DBRMLIB DD DSN=&USER..DBRMLIB.DATA(&MEM),
==MSG> SUBST JCL - DSN=DB2.DB2Z.DBRMLIB.DATA(UPDATSAL),
==MSG> X/ DISP=SHR
==MSG> CAY6102W MEMBER "UPDATSAL" NOT FOUND IN LIBRARY "NLV0.DLMF.DBRM.HXSULL" |
In my shop we use PLATINUM TOOLS & VERSION CONTROL is LMF, all the cobol-db2 program are compiled by LMF only.
If LMF is capable of compiling DB2 stored procedure also i don't know.....
So, Let me know, if you know.
How you do it, in your shop.....
Thank You,
Sushanth Bobby |
|