Testing a DB2 native stored procedure.

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Shriram
Registered Member
Posts: 21
Joined: Fri Aug 16, 2013 3:13 pm

Testing a DB2 native stored procedure.

Post 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?
Chandan Yadav
Website Team
Website Team
Posts: 70
Joined: Wed Jul 31, 2013 10:19 pm

Re: Testing a DB2 native stored procedure.

Post by Chandan Yadav »

I have used IBM Data Studio for testing Native Stored procedure

Thanks and regards,
Chandan
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Testing a DB2 native stored procedure.

Post by Anuj Dhawan »

Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Shriram
Registered Member
Posts: 21
Joined: Fri Aug 16, 2013 3:13 pm

Re: Testing a DB2 native stored procedure.

Post 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.
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Testing a DB2 native stored procedure.

Post 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.
Regards
Nic
Shriram
Registered Member
Posts: 21
Joined: Fri Aug 16, 2013 3:13 pm

Re: Testing a DB2 native stored procedure.

Post 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.
Shriram
Registered Member
Posts: 21
Joined: Fri Aug 16, 2013 3:13 pm

Re: Testing a DB2 native stored procedure.

Post by Shriram »

The aattached pdf has also been helpful:
Native SQL Stored Procedures.pdf
You do not have the required permissions to view the files attached to this post.
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Testing a DB2 native stored procedure.

Post by Anuj Dhawan »

Thanks for sharing the resources which helped you, they might someone later.
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”