Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Stored Procedure Returning Incorrect value
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Stored Procedure Returning Incorrect value
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: 1738
Location: Bloomington, IL

PostPosted: Thu Oct 28, 2010 7:18 pm    Post subject:
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    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Thu Oct 28, 2010 7:39 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: Stored Procedure Returning Incorrect value
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: 1278
Location: Belgium

PostPosted: Thu Oct 28, 2010 8:45 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Oct 29, 2010 7:26 pm    Post subject:
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: 645
Location: Pennsylvania

PostPosted: Fri Oct 29, 2010 7:42 pm    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Fri Oct 29, 2010 8:50 pm    Post subject: Re: Reply to: Stored Procedure Returning Incorrect value
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

Site Director


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

PostPosted: Fri Oct 29, 2010 9:15 pm    Post subject: Reply to: Stored Procedure Returning Incorrect value
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: 1738
Location: Bloomington, IL

PostPosted: Fri Oct 29, 2010 9:25 pm    Post subject: Re: Reply to: Stored Procedure Returning Incorrect value
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Nov 03, 2010 2:31 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Nov 03, 2010 3:03 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Nov 03, 2010 3:23 pm    Post subject:
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    Post subject:
Reply with quote

Thanks GuyC ,

I removed "FENCED" command . Bad luck this time also..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm
This topic is locked: you cannot edit posts or make replies. Stored procedure on Federated thru DB2 arif677 DB2 6 Tue May 31, 2016 11:48 pm
No new posts READQ TS returning inconsistent addre... moezbud CICS 14 Wed May 04, 2016 1:09 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us