BIND ERROR SQLCODE109 TOKENS=INTO in Version 9 conversion mode or Version 10 conversion mode

Technote (FAQ)


Question

We encountered an issue with a SQL statement in Version 9 (or Version 10) that did not occur
in Version 8.

In Version 8, the following SQL statement was valid when coded within
parentheses:

EXEC SQL  
                            
     (SELECT  COUNT(*)                                                          
        INTO  :WS-NONREG-PEND-COUNT                                             
        FROM  MAMT_NONREG_SVC                                                   
       WHERE  MEMBER_ID_TD = :MPLST-MEMBER-ID-TD                                
        AND   STATUS IN ('B', 'C', 'D')                                         
        AND   DELETE_IND = 'N')                                                 
                                                                                
   END-EXEC.                                                       



However, in Version 9 (or Version 10) we see the following bind error:

                                        
 DSNX200I -DB27 BIND SQL ERROR                          
 USING DBA AUTHORITY                                            
 PLAN=(NOT APPLICABLE)                                                          
 DBRM=MBRPD470                                                                  
 STATEMENT=4407                                                                 
 SQLCODE=-109                                                                   
 SQLSTATE=42601                                                                 
 TOKENS=INTO                                                                    
 CSECT NAME=DSNHSM1                                                             
 RDS CODE=44                                                                    
 DSNX200I -DB27 BIND SQL ERROR                                                  
 USING DBA AUTHORITY                                                            
 PLAN=(NOT APPLICABLE)                                                          
 DBRM=MBRPD470                                                                  
 STATEMENT=4429                                                          
 SQLCODE=-109                                                              
 SQLSTATE=42601                                                            
 TOKENS=INTO                                                            
 CSECT NAME=DSNHSM1                                                             
 RDS CODE=44                                                                    
 DSNT233I -DB27 UNSUCCESSFUL BIND FOR                                           
 PACKAGE = DB27LOC.SAVCOLL.MBRPD470.()                                 


Or message DSNH109I as a symptom for the same invalid syntax when using the precompiler.

Answer

Internal users discovered that the INTO clause was being allowed in a SELECT statement when it should not have been. The problem was fixed in the DB2 9 for z/OS code. However, the documentation was not changed for Version 9, because the syntax has not changed. In fact, invalid syntax was being allowed in Version 8, which resulted in some incorrect results.

Customers who perform a skip release migration from Version 8 to Version 10 may also encounter this in Version 10 conversion mode.

The syntax diagram for the SELECT INTO statement shows that the statement must begin with select-clause, and shows that the first token must be the keyword SELECT; it cannot be preceded by a left parenthesis.

The statement should work if you remove the outer parentheses. Also, the INTO clause might not be used if the SELECT is in a scalar fullselect.

Related information

SELECT INTO

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS
RDS

Software version:

9.1, 10.0

Operating system(s):

z/OS

Reference #:

1322024

Modified date:

2013-04-08

Translate my page

Machine Translation

Content navigation