BIND ERROR SQLCODE109 TOKENS=INTO in Version 9 conversion mode or Version 10 conversion mode
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
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.
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.