DB2 Stored Procedure

avinash pandey · New User Joined: 10 Apr 2008 Posts: 17 Location: Bangalore

I am writting Employee Maintenance System application which has several online modules(CICS+DB2+COBOL), i have asked to use the stored procedure for fetching the record using cursor, i am newbie to the stored procedure, i know how to use the cursor and run the query fetch record one bye one, i am not understanding when and where i would be using the stored procedure for the application. Though i have successfully created a stored procedure, but i don't know how to use it.

I have created below procedure using SPUFI

CREATE PROCEDURE EMS2
(
IN USERID CHAR(8)
,OUT PASSWORD CHAR(8)
,
)
RESULT SETS 0
EXTERNAL NAME EMS2
LANGUAGE COBOL
PARAMETER STYLE GENERAL
MODIFIES SQL DATA
NO DBINFO
WLM ENVIRONMENT DB8GE
STAY RESIDENT NO
COMMIT ON RETURN NO ;

Do i require now to create its DCLGEN and then write its subprogram, please guide me how to move ahead, since i am not able to move forward, let me know the procedure

Please don't tell me go the Redboooks

I want to know step by step, what i need to do after creation of procedure.

Thanks for all help

dbzTHEdinosauer · Posted: Mon Jun 22, 2009 2:03 pm

dbzTHEdinosauer · Posted: Mon Jun 22, 2009 2:14 pm

Sorry,
here is the exact link to DB2 for z/OS SPs: Through the CALL and Beyond
and in case you are using DB2 9,
DB2 Vsn 9 for z/OS SPs: Through the CALL and Beyond

avinash pandey · New User Joined: 10 Apr 2008 Posts: 17 Location: Bangalore

I have gone through, it but i need to know the procedure

1) Creation of SP
2) Creation of DCLGEN
3) Creation of Subprogram
4) Compilation using precompiler

Let me know , I have gone through redbooks but i require the additional info on it

dbzTHEdinosauer · Posted: Mon Jun 22, 2009 3:01 pm

at some point you are going to have to talk to your db2 support personnel and
become aquainted with your site-standards.

Anuj Dhawan · Posted: Mon Jun 22, 2009 3:31 pm

You are stuck in site specific things -- how these things work at my end may not work for you.

1. What version control tool do you use? If change man, I may be of some help.

2. Do you have DATABASE 2 Interactive (DB2I) available?

3. ah..aa what probelm are you facing from sub-program per se?

4. 'gain, What version control tool do you use?

avinash pandey · New User Joined: 10 Apr 2008 Posts: 17 Location: Bangalore

Yes i am using DB2 I database, i am not using changeman

I have created a stored procedure and i want to know what all things next to be done with start up the coding of SP?

Anuj Dhawan · Posted: Mon Jun 22, 2009 3:58 pm

DCLGEN can be generated using DB2I.

I echo Dick and repeat my self -- all the questions you are asking are site specific -- please refer some other SP at your shop.