• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1115
  • Last Modified:

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

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
techofficials
Asked:
techofficials
  • 13
  • 11
1 Solution
 
techofficialsAuthor Commented:
.
0
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
momi_sabagCommented:
what happens if you change the procedure body to

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

?
0
 
techofficialsAuthor Commented:
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
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
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
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
momi_sabagCommented:
run options allows you to pass parameters to LE, i don't think you need to use it, i never did
0
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
can you post here the create statement you are using?
0
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
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
 
momi_sabagCommented:
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
 
techofficialsAuthor Commented:
We got this to work.  It was something on the bind panel!  Thanks for your help.
0
 
techofficialsAuthor Commented:
thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now