Solved

z/OS DB2 V8 SQL Stored Procedure is returning 0's from Stored Proc Blder or nulls from assembler

Posted on 2008-10-29
24
1,077 Views
Last Modified: 2012-05-05
We are running DB2 V8 on z/OS 1.7. For the first time, we are trying to get SQL Stored Procedures to run. When we run the code pasted below from Stored Procedure Builder, it returns zeroes for both output values.  If we run it from an assembler program, we get a -305 (although the column we are returning is not a nullable column).  My guess is that I've got a parameter set wrong or I'm missing something in the SQL code of the stored procedure.   Any help is appreciated.   Obviously, this is not useful code; we are simpy trying to get SQL stored procs to work.  Here's the code:
CREATE PROCEDURE                          
  DGTR.DGTRR5S(                            
   OUT    MARY_TEST_OUT INTEGER,          
   OUT    LAST_UPDT_USER_ID_OUT INTEGER)  
             LANGUAGE SQL                  
             READS SQL DATA                
             DYNAMIC RESULT SET 1          
             STAY RESIDENT NO              
             NOT DETERMINISTIC            
             NO DBINFO                    
             COLLID DGTR                  
             WLM ENVIRONMENT DB2TDGP2      
 BEGIN                                      
     SET LAST_UPDT_USER_ID_OUT =            
        (SELECT LAST_UPDT_USER_ID          
            FROM DGTR001.MVH500            
             WHERE PLCRD_QLFY_ID = 12);    
     SET MARY_TEST_OUT = 12345;            
 END                                        

Here's proof that the row does exist (of course, you'd expect it to return the "12345" even if a row did not exist).  PLCRD_QLFY_ID and LAST_UPDT_USER_ID are both defined on the table as integers.

For Table   => DGTR001.MVH500                 >  Row number=> 1 OF 8
Browse Mode => C                                 Max Char  => 070  
SSID: DB2T ----------FETCH STATUS: COMPLETE-------------------------
      PLCRD_QLFY_ID   LAST_UPDT_DTE              LAST_UPDT_USER_ID  
                12    2008-09-25-15.13.22.154000          115      

0
Comment
Question by:techofficials
  • 13
  • 11
24 Comments
 

Author Comment

by:techofficials
ID: 22832742
.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832743
i would remove  DYNAMIC RESULT SET 1  from the create statement but i don't think this is what causing your problem
can you tell if the procedure begins to execute or not?
just insert a handler for -305 into the procedure
if you still get it, we will know it's an issue with the way you pass the parameters
otherwise, there is something wrong with the procedure itself
0
 

Author Comment

by:techofficials
ID: 22832749
;
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832765
what happens if you change the procedure body to

BEGIN                                      
     SET LAST_UPDT_USER_ID_OUT =  123456;
     SET MARY_TEST_OUT = 12345;            
 END  

?
0
 

Author Comment

by:techofficials
ID: 22832805
No, the DYNAMIC result set 1 is the same as just RESULT SET 1 (I've tried it both ways anyway).  Can you elaborate on inserting an error handler for the -305?   I get the 0's when invoking this thru Stored Procedure Builder, even if I don't have a select (for example, if i just say  SET MARY_TEST_OUT = 12345.  Doesn't that suggest it's something in the parameter list?  Also, I've tried it with just the bare minimum parameters (Language SQL, Result Set, WLM, COLLID) and I still get the same thing.  Any suggestions? Thanks  
   
0
 

Author Comment

by:techofficials
ID: 22832815
If I do the SET as posted above, I still get the same result (you were typing your message at the same time I was; please see previous post). Thanks!
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22832863
BEGIN                                      

DECLARE  SQLEXEPTION CONDITION for SQLCODE -305;
 DECLARE CONTINUE HANDLER FOR SQLEXEPTION
 BEGIN
    SET LAST_UPDT_USER_ID_OUT =  1;
    SET MARY_TEST_OUT = 1;            
   END;

    SET LAST_UPDT_USER_ID_OUT =  12345;
    SET MARY_TEST_OUT = 12345;            
 END  
0
 

Author Comment

by:techofficials
ID: 22832908
Thanks, I'll try that and let you know.  Also, do you know anything about RUN OPTIONS?

 RUN OPTIONS 'NOTEST(NONE,*,*,*)'  

Please see this post:
http://groups.google.com/group/comp.databases.ibm-db2/browse_thread/thread/93d0163112f31c20
0
 

Author Comment

by:techofficials
ID: 22832939
Error (the % is because I'm doing this in SPUFI and use this as a terminator character):
  BEGIN                                                                        
  DECLARE  SQLEXEPTION CONDITION FOR SQLCODE -305;                            
   DECLARE CONTINUE HANDLER FOR SQLEXEPTION                                    
   BEGIN                                                                      
      SET LAST_UPDT_USER_ID_OUT =  1;                                          
      SET MARY_TEST_OUT = 1;                                                  
     END;                                                                      
      SET LAST_UPDT_USER_ID_OUT =  12345;                                      
      SET MARY_TEST_OUT = 12345;                                              
  END%                                                                        
--------+---------+---------+---------+---------+---------+---------+---------+
SNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT    
        BE LEGAL ARE: VALUE <CHARSTRING>                                      
SNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                                
SNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                    
SNT416I SQLERRD    = 502 0  0  -1  983  0 SQL DIAGNOSTIC INFORMATION          
SNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF'        
        X'000003D7'  X'00000000' SQL DIAGNOSTIC INFORMATION                    
--------+---------+---------+---------+---------+---------+---------+---------+
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22833015
BEGIN                                      

DECLARE  SQLEXEPTION CONDITION for SQLSTATE '22002';
 DECLARE CONTINUE HANDLER FOR SQLEXEPTION
 BEGIN
    SET LAST_UPDT_USER_ID_OUT =  1;
    SET MARY_TEST_OUT = 1;            
   END;

    SET LAST_UPDT_USER_ID_OUT =  12345;
    SET MARY_TEST_OUT = 12345;            
 END  
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22833046
run options allows you to pass parameters to LE, i don't think you need to use it, i never did
0
 

Author Comment

by:techofficials
ID: 22833112
The spufi runs ok, but when I try to compile, it gives me the following errors:

DSNH4777I E     DSNHSMS2 LINE 16 COL 5  NESTED COMPOUND STATEMENTS NOT ALLOWED  

13        BEGIN                                                  
14        DECLARE  SQLEXEPTION CONDITION FOR SQLSTATE '22002';  
15         DECLARE CONTINUE HANDLER FOR SQLEXEPTION              
16         BEGIN                                                
17            SET LAST_UPDT_USER_ID_OUT =  1;                    
18            SET MARY_TEST_OUT = 1;                            
19           END;                                                
20            SET LAST_UPDT_USER_ID_OUT =  12345;                
21            SET MARY_TEST_OUT = 12345;                        
22         END                                                  
.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .

I don't see the problem, do you? Thanks
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22833146
try

BEGIN                                      

DECLARE  SQLEXEPTION CONDITION for SQLSTATE '22002';
 DECLARE CONTINUE HANDLER FOR SQLEXEPTION
 BEGIN
    SET LAST_UPDT_USER_ID_OUT =  1;
    SET MARY_TEST_OUT = 1;            
   END;

BEGIN
    SET LAST_UPDT_USER_ID_OUT =  12345;
    SET MARY_TEST_OUT = 12345;            
 END  ;

END
0
 

Author Comment

by:techofficials
ID: 22833234
He doesn't like that either:
DSNH4777I E     DSNHSMS2 LINE 16 COL 5  NESTED COMPOUND STATEMENTS NOT ALLOWED  
CREATE PROCEDURE DGTR.DGTRR5S(OUT MARY_TEST_OUT INTEGER,OUT LAST_UPDT_USER_ID_OU
 STAY RESIDENT NO NOT DETERMINISTIC NO DBINFO COLLID DGTR WLM ENVIRONMENT DB2TDG
E'22002'; DECLARE CONTINUE HANDLER FOR SQLEXEPTION BEGIN SET                    

13        BEGIN                                                
14        DECLARE  SQLEXEPTION CONDITION FOR SQLSTATE '22002';  
15         DECLARE CONTINUE HANDLER FOR SQLEXEPTION            
16         BEGIN                                                
17            SET LAST_UPDT_USER_ID_OUT =  1;                  
18            SET MARY_TEST_OUT = 1;                            
19           END;                                              
20        BEGIN                                                
21            SET LAST_UPDT_USER_ID_OUT =  12345;              
22            SET MARY_TEST_OUT = 12345;                        
23         END  ;                                              
24        END                                                  
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22834536
can you post here the create statement you are using?
0
 

Author Comment

by:techofficials
ID: 22834813
Here it is:
 1      CREATE PROCEDURE                                                
 2        DGTR.DGTRR5S(                                                
 3         OUT    MARY_TEST_OUT INTEGER,                                
 4         OUT    LAST_UPDT_USER_ID_OUT INTEGER)                        
 5                   LANGUAGE SQL                                      
 6                   COLLID DGTR                                        
 7                   WLM ENVIRONMENT DB2TDGP2                          
 8        BEGIN                                                        
 9        DECLARE  SQLEXEPTION CONDITION FOR SQLSTATE '22002';          
10         DECLARE CONTINUE HANDLER FOR SQLEXEPTION                    
11         BEGIN                                                        
12            SET LAST_UPDT_USER_ID_OUT =  1;                          
13            SET MARY_TEST_OUT = 1;                                    
14           END;                                                      
15        BEGIN                                                        
16            SET LAST_UPDT_USER_ID_OUT =  12345;                      
17            SET MARY_TEST_OUT = 12345;                                
18         END  ;                                                      
19        END                                                          
DSNH4777I E     DSNHSMS2 LINE 11 COL 5  NESTED COMPOUND STATEMENTS NOT ALLOWED
         
I have run one successfully now with NO DB2:
CREATE PROCEDURE                            
  DGTR.DGTRR4S (OUT RETURN_THIS INTEGER)    
      LANGUAGE SQL                          
      COLLID DGTR                            
      WLM ENVIRONMENT DB2TDGP2              
  BEGIN                                      
      SET RETURN_THIS = 999;                
  END                                        

But, I can't get any of them with DB2 to run.  I even get errors if I try to put "for fetch only" or "with UR", etc.  This is true even if I specify Modifies SQL Data.

Any idea what the compiler is complaining about?
Thanks,
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22836458
try this one and tell me what it returns

CREATE PROCEDURE                          
  DGTR.DGTRR5S(                            
   OUT    MARY_TEST_OUT INTEGER,          
   OUT    LAST_UPDT_USER_ID_OUT INTEGER)  
             LANGUAGE SQL                  
             READS SQL DATA                
             DYNAMIC RESULT SET 1          
             STAY RESIDENT NO              
             NOT DETERMINISTIC            
             NO DBINFO                    
             COLLID DGTR                  
             WLM ENVIRONMENT DB2TDGP2      
 BEGIN                                      
     SELECT 12345 into MARY_TEST_OUT from sysibm.sysdummy1;            
 END              
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22836462
this one

CREATE PROCEDURE                          
  DGTR.DGTRR5S(                            
   OUT    MARY_TEST_OUT INTEGER,          
   OUT    LAST_UPDT_USER_ID_OUT INTEGER)  
             LANGUAGE SQL                  
             READS SQL DATA                
             DYNAMIC RESULT SET 1          
             STAY RESIDENT NO              
             NOT DETERMINISTIC            
             NO DBINFO                    
             COLLID DGTR                  
             WLM ENVIRONMENT DB2TDGP2      
 BEGIN                                      
     SELECT 12345,1 into MARY_TEST_OUT,LAST_UPDT_USER_ID_OUT  from sysibm.sysdummy1;            
 END      
0
 

Author Comment

by:techofficials
ID: 22840284
Ran it (second one):  Copied your code above.  Here's the o/p:
DGTR.DGTRR6S - The value(s) of the output parameters:
      MARY_TEST_OUT             = 0        
      LAST_UPDT_USER_ID_OU      = 0        
Thanks for your help.

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22840303
so, i think there is something wrong with how you pass the parameters from your assembley code
did you succseed in passing input parameters into a procedure?
maybe you should pass them differently since they are output parameters, so they should be passed by reference and not by value
0
 

Author Comment

by:techofficials
ID: 22840419
I'm using Stored Procedure Builder.  I had the Systems Programmer try it once from an Assmebly Program, but he got the same results I am getting in Stored Procuedure Builder.  Also, since it returns correctly if you run it like the one below, Iwhy it is failing when you ask it to get DB2 data?

CREATE PROCEDURE                            
  DGTR.DGTRR4S (OUT RETURN_THIS INTEGER)    
      LANGUAGE SQL                          
      COLLID DGTR                            
      WLM ENVIRONMENT DB2TDGP2              
  BEGIN                                      
      SET RETURN_THIS = 999;                
  END            
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 475 total points
ID: 22841835
if this works
CREATE PROCEDURE                            
  DGTR.DGTRR4S (OUT RETURN_THIS INTEGER)    
      LANGUAGE SQL                          
      COLLID DGTR                            
      WLM ENVIRONMENT DB2TDGP2              
  BEGIN                                      
      SET RETURN_THIS = 999;                
  END            

but this doesn't
CREATE PROCEDURE                          
  DGTR.DGTRR5S(                            OUT RETURN_THIS INTEGER)  
             LANGUAGE SQL                  
             READS SQL DATA                
             COLLID DGTR                  
             WLM ENVIRONMENT DB2TDGP2      
 BEGIN                                      
     SELECT 999 into return_this from sysibm.sysdummy1;            
 END      

and you have permissinos to perform select from sysibm.sysdummy1 than i would contact IBM and report a bug
0
 

Author Comment

by:techofficials
ID: 22845403
We got this to work.  It was something on the bind panel!  Thanks for your help.
0
 

Author Closing Comment

by:techofficials
ID: 31511251
thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now