Page 1 of 1
Testing a DB2 native stored procedure.
Posted: Mon Jul 04, 2016 12:30 pm
by Shriram
Hi,
I have made some changes in one of the table a DB2 stored procedure access to. How do I test now to see if the DB2 native stored procedure had picked up the changes? Can it not be done with out an add-on tool?
Re: Testing a DB2 native stored procedure.
Posted: Tue Jul 05, 2016 10:24 am
by Chandan Yadav
I have used IBM Data Studio for testing Native Stored procedure
Thanks and regards,
Chandan
Re: Testing a DB2 native stored procedure.
Posted: Tue Jul 05, 2016 11:23 am
by Anuj Dhawan
Re: Testing a DB2 native stored procedure.
Posted: Tue Jul 05, 2016 11:31 am
by Shriram
Thanks all. I haveheard that we can call the stored procedure using a COBOL program and can test. But I was not able to locate an example program to do so. If anyone has such an example to share that will really help.
Re: Testing a DB2 native stored procedure.
Posted: Tue Jul 05, 2016 1:49 pm
by nicc
If that is how it is done then it will have been done in your shop to your shop's standards. So go look/ask in your shop.
Re: Testing a DB2 native stored procedure.
Posted: Fri Jul 08, 2016 10:52 am
by Shriram
I have asked it in my shop but ot much to help me. But I have got some reference and working with them:
Code: Select all
IDENTIFICATION DIVISION.
PROGRAM-ID. CALPRML.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT REPOUT
ASSIGN TO UT-S-SYSPRINT.
DATA DIVISION.
FILE SECTION.
FD REPOUT
RECORD CONTAINS 127 CHARACTERS
LABEL RECORDS ARE OMITTED
DATA RECORD IS REPREC.
01 REPREC PIC X(127).
WORKING-STORAGE SECTION.
*****************************************************
* MESSAGES FOR SQL CALL *
*****************************************************
01 SQLREC.
02 BADMSG PIC X(34) VALUE
' SQL CALL FAILED DUE TO SQLCODE = '.
02 BADCODE PIC +9(5) USAGE DISPLAY.
02 FILLER PIC X(80) VALUE SPACES.
01 ERRMREC.
02 ERRMMSG PIC X(12) VALUE ' SQLERRMC = '.
02 ERRMCODE PIC X(70).
02 FILLER PIC X(38) VALUE SPACES.
01 CALLREC.
02 CALLMSG PIC X(28) VALUE
' GETPRML FAILED DUE TO RC = '.
02 CALLCODE PIC +9(5) USAGE DISPLAY.
02 FILLER PIC X(42) VALUE SPACES.
01 RSLTREC.
02 RSLTMSG PIC X(15) VALUE
' TABLE NAME IS '.
02 TBLNAME PIC X(18) VALUE SPACES.
02 FILLER PIC X(87) VALUE SPACES.
*****************************************************
* WORK AREAS *
*****************************************************
01 PROCNM PIC X(18).
01 SCHEMA PIC X(8).
01 OUT-CODE PIC S9(9) USAGE COMP.
01 PARMLST.
49 PARMLEN PIC S9(4) USAGE COMP.
49 PARMTXT PIC X(254).
01 PARMBUF REDEFINES PARMLST.
49 PARBLEN PIC S9(4) USAGE COMP.
49 PARMARRY PIC X(127) OCCURS 2 TIMES.
01 NAME.
49 NAMELEN PIC S9(4) USAGE COMP.
49 NAMETXT PIC X(18).
77 PARMIND PIC S9(4) COMP.
77 I PIC S9(4) COMP.
77 NUMLINES PIC S9(4) COMP.
*****************************************************
* DECLARE A RESULT SET LOCATOR FOR THE RESULT SET *
* THAT IS RETURNED. *
*****************************************************
01 LOC USAGE SQL TYPE IS
RESULT-SET-LOCATOR VARYING.
*****************************************************
* SQL INCLUDE FOR SQLCA *
*****************************************************
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
*------------------
PROG-START.
OPEN OUTPUT REPOUT.
* OPEN OUTPUT FILE
MOVE 'DSN8EP2 ' TO PROCNM.
* INPUT PARAMETER -- PROCEDURE TO BE FOUND
MOVE SPACES TO SCHEMA.
* INPUT PARAMETER -- SCHEMA IN SYSROUTINES
MOVE -1 TO PARMIND.
* THE PARMLST PARAMETER IS AN OUTPUT PARM.
* MARK PARMLST PARAMETER AS NULL, SO THE DB2
* REQUESTER DOES NOT HAVE TO SEND THE ENTIRE
* PARMLST VARIABLE TO THE SERVER. THIS
* HELPS REDUCE NETWORK I/O TIME, BECAUSE
* PARMLST IS FAIRLY LARGE.
EXEC SQL
CALL GETPRML(:PROCNM,
:SCHEMA,
:OUT-CODE,
:PARMLST INDICATOR :PARMIND)
END-EXEC.
* MAKE THE CALL
IF SQLCODE NOT EQUAL TO +466 THEN
* IF CALL RETURNED BAD SQLCODE
MOVE SQLCODE TO BADCODE
WRITE REPREC FROM SQLREC
MOVE SQLERRMC TO ERRMCODE
WRITE REPREC FROM ERRMREC
ELSE
PERFORM GET-PARMS
PERFORM GET-RESULT-SET.
PROG-END.
CLOSE REPOUT.
* CLOSE OUTPUT FILE
GOBACK.
PARMPRT.
MOVE SPACES TO REPREC.
WRITE REPREC FROM PARMARRY(I)
AFTER ADVANCING 1 LINE.
GET-PARMS.
* IF THE CALL WORKED,
IF OUT-CODE NOT EQUAL TO 0 THEN
* DID GETPRML HIT AN ERROR?
MOVE OUT-CODE TO CALLCODE
WRITE REPREC FROM CALLREC
ELSE
* EVERYTHING WORKED
DIVIDE 127 INTO PARMLEN GIVING NUMLINES ROUNDED
* FIND OUT HOW MANY LINES TO PRINT
PERFORM PARMPRT VARYING I
FROM 1 BY 1 UNTIL I GREATER THAN NUMLINES.
GET-RESULT-SET.
*****************************************************
* ASSUME YOU KNOW THAT ONE RESULT SET IS RETURNED, *
* AND YOU KNOW THE FORMAT OF THAT RESULT SET. *
* ALLOCATE A CURSOR FOR THE RESULT SET, AND FETCH *
* THE CONTENTS OF THE RESULT SET. *
*****************************************************
EXEC SQL ASSOCIATE LOCATORS (:LOC)
WITH PROCEDURE GETPRML
END-EXEC.
* LINK THE RESULT SET TO THE LOCATOR
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :LOC
END-EXEC.
* LINK THE CURSOR TO THE RESULT SET
PERFORM GET-ROWS VARYING I
FROM 1 BY 1 UNTIL SQLCODE EQUAL TO +100.
GET-ROWS.
EXEC SQL FETCH C1 INTO :NAME
END-EXEC.
MOVE NAME TO TBLNAME.
WRITE REPREC FROM RSLTREC
AFTER ADVANCING 1 LINE.
Re: Testing a DB2 native stored procedure.
Posted: Fri Jul 08, 2016 11:06 am
by Shriram
The aattached pdf has also been helpful:
Native SQL Stored Procedures.pdf
Re: Testing a DB2 native stored procedure.
Posted: Sat Jul 09, 2016 11:37 am
by Anuj Dhawan
Thanks for sharing the resources which helped you, they might someone later.