IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Stored Procedure Returning Incorrect value


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
prahalad

New User


Joined: 14 Sep 2010
Posts: 18
Location: Pune

PostPosted: Thu Oct 28, 2010 6:56 pm
Reply with quote

Guys,

Please help me in stored procedure issue. Below is my Internal Stored procedure . I'm calling this stored procedure thorugh my cobol program .

Cobol program returns SQLCODE = 0 but the value of V_ACT_ID is coming as junk. So it is not returing a proper numeric value to my main program

Stored Procedure code:
Code:

********************************* Top of Data ********************************** .
CREATE PROCEDURE BP.PBP00010 (OUT V_ACT_ID INTEGER)                              .
    FENCED                                                                       .
    RESULT SETS 0                                                                .
    EXTERNAL NAME PBP00010                                                       .
    LANGUAGE SQL                                                                 .
    MODIFIES SQL DATA                                                            .
    COLLID CBP001BD                                                              .
    PARAMETER STYLE GENERAL WITH NULLS                                           .
    NOT DETERMINISTIC                                                            .
    ASUTIME NO LIMIT                                                             .
    PROGRAM TYPE MAIN                                                            .
    SECURITY DB2                                                                 .
    COMMIT ON RETURN NO                                                          .
    STAY RESIDENT YES                                                            .
--------------------------------------------------------------------             .
       
--                                                                               
---------------------------------------------------------------------           
BEGIN                                                                           
--                                                                               
--                                                                               
    DECLARE v_base_n       INTEGER;                                             
    DECLARE v_base_n1      INTEGER;                                             
    DECLARE v_base_n2      INTEGER;                                             
    DECLARE V_ACT_ID       INTEGER DEFAULT 0;                                   
    DECLARE v_act_upd      CHAR(09);                                             
--                                                                               
    SELECT INTEGER(I_LAST_ACCOUNT_NUMBER) INTO v_base_n                         
    FROM   BP.TBP_CONTROL where I_COMPANY_ID = 1;                               
--                                                                               
    SET v_base_n1    =  v_base_n   + 1 ;                                         
     SET v_base_n1    =  v_base_n   + 1 ;                                         .
 --                                                                               .
     UPDATE BP.TBP_CONTROL                                                        .
       SET I_LAST_ACCOUNT_NUMBER = char(v_base_n1)                                .
           where I_COMPANY_ID = 1;                                                .
 --                                                                               .
     SET v_base_n2    =  v_base_n1  * 10;                                         .
     SET V_ACT_ID     =  v_base_n2;                                               .
 --                                                                               .
 --                                                                               .
 END                                                                              .




Cobol program: Linkage section:

Code:

 LINKAGE SECTION.                                         
  01 V_ACT_ID                    PIC    S9(9) USAGE COMP-3
                                 VALUE  ZEROS.             


Procedure division:

Code:

EXEC SQL                           
  CALL BP.PBP00010(:V_ACT_ID)     
END-EXEC                           


after executing this piece of code. I do get SQL code zero , however value returned value is incorrect.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Thu Oct 28, 2010 7:18 pm
Reply with quote

And would that returned value actually be appropriate to an INTEGER (i.e., binary) field?
Back to top
View user's profile Send private message
prahalad

New User


Joined: 14 Sep 2010
Posts: 18
Location: Pune

PostPosted: Thu Oct 28, 2010 7:28 pm
Reply with quote

Hi Akatsukami ,

Yes , returned value is an integer (it is nothing but a unique 9 digit number ).

actual value eturned in my main program(spool details)

Code:

Returned value:000³00000
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Thu Oct 28, 2010 7:39 pm
Reply with quote

prahalad wrote:
Hi Akatsukami ,

Yes , returned value is an integer (it is nothing but a unique 9 digit number ).

actual value eturned in my main program(spool details)

Code:

Returned value:000³00000

So then, that's going to be incompatible with the declaration of V-ACT-ID as a COMP-3 (i.e. packed decimal) variable in the linkage section, isn't it?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Oct 28, 2010 7:54 pm
Reply with quote

Akatsukami,

good eyes, nice catch (as in catch the mistake, Robert)
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Thu Oct 28, 2010 8:13 pm
Reply with quote

Hi ,

Thanks first.

I changed the piece of code now .

Linkage section is now declared as comp instead of comp-3:

Code:

01 V_ACT_ID                    PIC    S9(9) USAGE COMP
                               VALUE  ZEROS.           


Now what i get is after executing this code :

Spool details :

Code:

AFTER EXECU   :000655360


it is now giving a standard number as 000655360 for every execution.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 28, 2010 8:45 pm
Reply with quote

Why is v-act-id defined in linkage section ? that won't work.
Host variables have to be in Working storage.
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Fri Oct 29, 2010 10:36 am
Reply with quote

I did even tried declaring the V_ACT_ID in the working storage section . Still it returned the value as zeros.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri Oct 29, 2010 7:26 pm
Reply with quote

Hello,

Have you corrected the PIC or are you still using comp-3. . .?

(As Akatsukami mentioned earlier). . .
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Fri Oct 29, 2010 7:42 pm
Reply with quote

Dick,

TS said it was switched to USAGE COMP.

I wonder why not just use PIC 9(9). With nothing else.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Fri Oct 29, 2010 8:50 pm
Reply with quote

raj1984 wrote:
it is now giving a standard number as 000655360 for every execution.

Suspicious; 655,360 decimal is X'000A0000'. I'd suspect alignment issues at a minimum.

I recommend that you take daveporcelan's suggestion of using character/zoned decimal/DISPLAY variables for inter-module communication.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri Oct 29, 2010 9:15 pm
Reply with quote

Quote:
TS said it was switched to USAGE COMP.
Thanks Dave icon_smile.gif

I missed that. . . icon_redface.gif

d
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Fri Oct 29, 2010 9:25 pm
Reply with quote

dick scherrer wrote:
Quote:
TS said it was switched to USAGE COMP.
Thanks Dave icon_smile.gif

I missed that. . . icon_redface.gif

Perhaps because the TS has also apparently changed his member ID from prahalad to raj1984?
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 1:30 pm
Reply with quote

Hi ,

It is still an issue . I even tried to get the value in char mode . still it fails .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 2:31 pm
Reply with quote

You're not supposed to declare your output variables again in the coding.
remove
Code:
    DECLARE V_ACT_ID       INTEGER DEFAULT 0;   



extra 1: instead of coding and calling a stored procedure you could just
Code:
    select  integer(I_LAST_ACCOUNT_NUMBER) * 10 from FINAL TABLE (                             
    update TBP_CONTROL
    set I_LAST_ACCOUNT_NUMBER = char(integer(I_LAST_ACCOUNT_NUMBER)+1)
    where I_COMPANY_ID = 1    )

extra 2: a function would properly be more useful than a procedure.

extra 3: consider using sequences instead of a tbp_control table which will cause locking problems.


V_ACT_ID is actually NULL because of not assigning any value to it in the proc (because of duplicate definition)
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 3:01 pm
Reply with quote

Thanks GuyC,

Above code is a sample, i'm just trying to establish the connection first . Once it is done , then actual logic of unique nbr generation will be inserted.

So i need to make this SP work.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 3:03 pm
Reply with quote

so do as I told you, and it will
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 3:11 pm
Reply with quote

Thanks GuyC,

I tried that option. I removed V_ACT_ID from the declaration (duplicate)
and declared same variable in COBOL working storage. Now it is giving a values are zeros(becuase i intialized the variable in COBOL program).


Thanks
Rajesh
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 3:23 pm
Reply with quote

publib.boulder.ibm.com wrote:
Do not include the FENCED or EXTERNAL keywords
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 3:42 pm
Reply with quote

Thanks GuyC ,

I removed "FENCED" command . Bad luck this time also..
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 4:03 pm
Reply with quote

removed "EXTERNAL NAME PBP00010 " also ?
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 4:24 pm
Reply with quote

Yes GuyC , both FENCED and EXTERNAL are removed.

Below is my SP procedure:


Code:

CREATE PROCEDURE BP.PBP00024 (INOUT V_ACT_ID char(09))                     
--  FENCED                                                                 
    RESULT SETS 0                                                           
--  EXTERNAL NAME PBP00024                                                 
    LANGUAGE SQL                                                           
    MODIFIES SQL DATA                                                       
    COLLID CBP001BD                                                         
--  PARAMETER STYLE GENERAL                                                 
    NOT DETERMINISTIC                                                       
    ASUTIME NO LIMIT                                                       
    PROGRAM TYPE MAIN                                                       
    SECURITY DB2                                                           
    COMMIT ON RETURN NO                                                     
    STAY RESIDENT NO                                                       
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 5:03 pm
Reply with quote

this works on my side :
Code:
--drop PROCEDURE GUYCTEST                   ;
CREATE PROCEDURE GUYCTEST (INOUT V_ACT_ID char(9))                     
    RESULT SETS 0                                                           
    LANGUAGE SQL                                                           
    MODIFIES SQL DATA                                                       
    COLLID CBP001BD                                                         
    NOT DETERMINISTIC                                                       
    ASUTIME NO LIMIT                                                       
    PROGRAM TYPE MAIN                                                       
    SECURITY DB2                                                           
    COMMIT ON RETURN NO                                                     
    STAY RESIDENT NO     
   
    BEGIN                                                                           
                                                                               
    DECLARE v_base_n2      INTEGER;                                             

     SET v_base_n2    =  10;
     SET V_ACT_ID     =  char(v_base_n2);                                               
 --                                                                               
 --                                                                               
 END
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 5:07 pm
Reply with quote

It works for me when i run it standalone(we have AQT tool through which i can run the SP).

However , when i call it through COBOL program , it returns no value ..
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Wed Nov 03, 2010 5:07 pm
Reply with quote

Please let me know , how did u run it ?
through COBOL ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Excuting store procedure via JCL batch JCL & VSAM 1
No new posts Executing DB2 SQL store procedure via... DB2 0
No new posts Invoke stored procedure via batch JCL. DB2 2
No new posts Calling COBOL DB2 program from a COBO... COBOL Programming 2
No new posts Cond parameter and Rc code of a proce... JCL & VSAM 5
Search our Forums:

Back to Top