SQLCODE302 is reported for PREPARE FROM :host-variable or EXECUTE IMMEDIATE :host-variable

Technote (FAQ)


Question

Release incompatibility: SQLCODE302 is reported for PREPARE FROM :host-variable or EXECUTE IMMEDIATE :host-variable. This error is reported in DB2 Version 8 only (not in Version 7). In this particular case, the SQLCODE -302 is issued because the defined length of the input host-variable that contains the text of the SQL statement that is to be dynamically prepared is less than the actual length of the input text string.

Answer

In Version 8, DB2® has improved validation of the attributes of the input host variable that is used in the SQL statements PREPARE and EXECUTE IMMEDIATE.

DB2 issues an error message SQLCODE -302 if the defined length of the host variable that was specified as containing the text string of the SQL statement that is to be dynamically prepared is less than the length of the actual data that is passed to DB2 from the host-variable storage area. DB2 issues the error message SQLCODE -302 only when the application is run, not during bind.

You need to correct your application programs that use this coding technique because this technique is not supported by DB2 and the DB2 precompiler. DB2 Version 7 originally had the same behavior as Version 8 but was changed by APAR PQ50494 to tolerate this coding technique with the caveat that all DB2 releases following Version 7 would not tolerate this coding technique.

PQ50494 was provided with this warning to give application programmers and vendor products an opportunity to remove this unsupported coding technique in preparation for the next DB2 release. The reason this coding technique is not supported in DB2 Version 8 is to prevent DB2 abends and unpredictable results from occurring when the application programmer incorrectly determines the size of storage required for the host-variable contents.

Example: The following code is an example of this coding technique using COBOL:

*********************                                                  
* DYNAMIC SQL STUFF *

 WORKING-STORAGE SECTION.
01 WS-STMT-AREA.
02 WS-STMT-LENGTH PIC S9(4) COMP VALUE +54.
02 WS-SQL-TEXT PIC X(54) VALUE

'SELECT NAME FROM ADMF001.EMP WHERE EMPNO = ''100000'' '.
* *
01 WS-SQL-STMT-AREA REDEFINES WS-STMT-AREA.
02 WS-SQL-STMT.
49 WS-SQL-STMT-LEN PIC S9(4) COMP.
49 WS-SQL-STMT-TEXT PIC X.

         :
:                                           
PROCEDURE DIVISION.
:
:
EXEC SQL PREPARE STMT1 FROM :WS-SQL-STMT END-EXEC.
*********************




The COBOL variable WS-SQL-STMT of redefining structure WS-SQL-STMT-AREA is used as the varying character string source for the SQL statement that is to be prepared, where variable WS-SQL-STMT-TEXT should contain the actual statement text string. However, WS-SQL-STMT-TEXT has a defined maximum length that is shorter than the original base variable, WS-STMT-AREA, and shorter than the actual character string value of the host-variable. Such definitions are valid in COBOL and other high-level languages, because both host-variables refer to the same storage location, but DB2 and the DB2 precompiler do not know about the original base variable WS-STMT-AREA and therefore can not know the length of the actual storage area.

DB2 knows only the attributes of WS-SQL-STMT-TEXT and the length of the actual character string that is passed to it on the PREPARE statement, so DB2 issues SQLCODE -302 for the PREPARE statement specifying the shorter length host-variable, WS-SQL-STMT.

To resolve the -302, do not use the host-variable WS-SQL-STMT in the PREPARE statement; the application should either specify the host-variable WS-STMT-AREA on the PREPARE or change WS-SQL-STMT-TEXT to have a maximum PIC X( ) large enough to contain the actual character string value used as the SQL statement text.

The use of the REDEFINES clause or the COBOL language is not the only method used to set-up this length conflict between the input host-variable and the actual character string value. If by any means an application gives DB2 a FROM :host-variable whose specified length is less than the actual data string passed, SQLCODE302 can be returned to the application. For example, this SQLCODE302 can also be received by vendor products that are not using COBOL or the REDEFINES clause, but instead use Assembler language for example, to accomplish this unsupported programming technique.


Related information

R710 APAR PQ50494

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS
RDS

Software version:

8.0

Operating system(s):

z/OS

Reference #:

1159584

Modified date:

2004-09-22

Translate my page

Machine Translation

Content navigation