Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Stored Procedure Returning Incorrect value
Goto page Previous  1, 2
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

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

Please let me know , how did u run it ?
through COBOL ?
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:45 pm    Post subject:
Reply with quote

I don't do cobol anymore.
I'll try to get a cobol test pgm when I have the time.

At least now you/I know it's not your SP.
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:50 pm    Post subject:
Reply with quote

Thanks Guyc for your support.
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 6:09 pm    Post subject:
Reply with quote

extract :
Code:
WORKING-STORAGE.
...
01  ws-misc.                                             
    05  ws-invar                pic  x(09).             
...
PROCEDURE DIVISION.
    move 'XXX' to ws-invar             
    display 'Befor[' ws-invar ']'     
    exec sql                           
        CALL myuserid.GUYCTEST(:ws-invar)
    end-exec                           
    perform sqlstate-check       
    display 'After[' ws-invar ']'     
    goback
    .
...

returns :
Code:
Befor[XXX      ]
After[10       ]
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Fri Nov 12, 2010 11:53 am    Post subject:
Reply with quote

Thanks GuyC..

It does not run it my shop.. Did you bind WLM (store procedure environment) while registering the SP ?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 15, 2010 2:14 pm    Post subject:
Reply with quote

bind wlm ?
The stored procedure is native, so no extra binding steps are necessary.
I precompiled/compiled/linked/bound the calling program just like any other DB2 program.
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Tue Nov 16, 2010 11:45 am    Post subject:
Reply with quote

Thanks GuyC,

Infact i did the same. It is not working in my system. Instead of cobol , we tried to hit the SP from Java , it is working fine.

Seems that it is issue with COBOL program.

Thoughts?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Nov 16, 2010 3:03 pm    Post subject:
Reply with quote

not if your sqlcode is still 000.
Could be something with (descending plausiblility):
- parameter style : GENERAL WITH NULLS vs PARAMETER STYLE GENERAL
- different versions of the same SP.
- APF-authorized WLM Environments
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Thu Nov 18, 2010 8:46 am    Post subject:
Reply with quote

Thanks GuyC and Others.

Finally it is working now.

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

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 18, 2010 12:52 pm    Post subject:
Reply with quote

thx for letting us know.
What was the final problem?
Back to top
View user's profile Send private message
raj1984

New User


Joined: 28 Oct 2010
Posts: 16
Location: India

PostPosted: Thu Nov 18, 2010 12:59 pm    Post subject:
Reply with quote

Solution suggested by our DBA was to change the variable name.
I declared V_ACT_ID in SP and declared same variable in program as well.
But he wanted to change the name of variable . Basically same variable (name ) should not be used in both SP and COBOL pgm.

Seems to be strange but it is working .

Thanks
Rajesh
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 Previous  1, 2
Page 2 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Procedure Division Section socker_dad COBOL Programming 2 Wed Jun 12, 2019 12:21 am
No new posts How to DEBUG DB2 Stored Procedures Us... thesumitk DB2 0 Mon Jun 03, 2019 8:26 pm
No new posts Regarding mroe data to be processed i... vinu78 DB2 3 Mon May 27, 2019 7:31 pm
No new posts Identify who, from where, sign on CIC... Weilin Wen CICS 1 Thu May 09, 2019 1:23 pm
No new posts Can we pass cobol pointer to cobol-db... rohanthengal DB2 4 Tue Mar 19, 2019 1:04 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us