Db2

 View Only

 DB2 iSeries V12R1 Stored Procedure SQL Compile Error SQLCODE=-20165, SQLSTATE=428FL

Cal Pishak's profile image
Cal Pishak posted Wed November 13, 2024 11:24 AM

Hi everyone. Hopefully this is the right place to find the answers...

I am trying to create the following stored procedure:

    CREATE PROCEDURE GET_NEXT_CONTROL_VALUE              
   (IN  KEYCODE  CHARACTER(8),                           
    OUT KEYVALUE INTEGER )                               
     LANGUAGE SQL                                        
     WLM ENVIRONMENT FOR DEBUG MODE <wlm env name>             
     DETERMINISTIC                                       
     AUTONOMOUS                                          
                                                         
  SET KEYVALUE =                                         
  (                                                      
     SELECT KEYVALUE_TLPCNTRL FROM FINAL TABLE           
     (                                                   
        UPDATE TLPCNTRL                                  
           SET KEYVALUE_TLPCNTRL = KEYVALUE_TLPCNTRL + 1 

         WHERE KEYCODE_TLPCNTRL = KEYCODE         
     )                                            
  )                                               
  ;                                               

I am getting the following results:

 SQLERROR ON   CREATE    COMMAND, EXECUTE   FUNCTION                            
  RESULT OF SQL STATEMENT:                                                      
  DSNT408I SQLCODE = -20165, ERROR:  AN SQL DATA CHANGE STATEMENT WITHIN A FROM CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH
           IT WAS SPECIFIED                                                     
  DSNT418I SQLSTATE   = 428FL SQLSTATE RETURN CODE              

If I run the SQL query directly (substituting a constant for the keyword parameter) everything works as expected, so I don't think there is an issue with the statement itself.

Any ideas what is causing this? The documentation on this error (-20165 - IBM Documentation) is not very helpful.

Thanks in advance for any insights.

Cal

Mark Barinstein's profile image
Mark Barinstein posted Tue November 19, 2024 05:13 AM

Hi Cal,

You run DB2 for z/OS, not DB2 for iSeries.
This is a group for DB2 for LUW, not for DB2 for z/OS.

I don't have DB2 for z/OS at hand, but you may try the following instead:

     SELECT KEYVALUE_TLPCNTRL INTO KEYVALUE
     FROM FINAL TABLE           
     (                                                   
        UPDATE TLPCNTRL                                  
        SET KEYVALUE_TLPCNTRL = KEYVALUE_TLPCNTRL + 1 
        WHERE KEYCODE_TLPCNTRL = KEYCODE         
     )


Both statement work in DB2 for LUW, but there are some additional restrictions on "select from data-change statements" functionality in DB2 for z/OS probably...

Regards,
Mark.