PK55261: MULTI ROW INSERTS USING ODBC FAILS WITH SQLCODE -312 WHEN PARAMETER MARKERS ARE PART OF EXPRESSIONS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In V9, DB2 for z/OS started to enforce the restriction that a
  host variable array cannot be used in an expression for
  multi-row insert.  An INSERT statement with host variable arrays
  (such as the one shown below) that used to work on V8 would no
  longer work on V9.
  INSERT INTO PS_RS_DF_WRKR_AVA                                  
  (EMPLID ,                                                      
  DATE_ENTERED ,                                                
  SEQ_NBR ,                                                    
   START_DTTM,                                                    
   END_DTTM)                                                      
   VALUES (:1 , :2 , :3 ,                                        
   TIMESTAMP(CAST(:4 as DATE), CAST(:5 as TIME)) ,                
  TIMESTAMP(CAST(:6 as DATE), CAST(:7 as TIME)))
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 UDB for z/OS Version 9      *
  *                 ODBC/CLI.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Multi-row inserts using the ODBC's      *
  *                      array input interface fails with        *
  *                      SQLCODE -312 when parameter markers     *
  *                      are part of expressions.                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 ODBC provides an array input method for applications
  performaing bulk inserts.  The method allows the binding of
  host variable arrays to parameter markers with no restriction
  on the syntax surrounding the parameter markers in the SQL.
  
  However V9 DB2 for z/OS started to enforce the restriction that
  host variable arrays cannot be used in an expression for multi
  row insert statements.  An INSERT statement with host variable
  arrays, such as the one shown below, that used to work on V8
  would no longer work on V9 and would generate the SQLCODE -312:
  
  INSERT INTO PS_RS_DF_WRKR_AVA
  (EMPLID ,
   DATE_ENTERED ,
   SEQ_NBR ,
   START_DTTM,
   END_DTTM)
  VALUES (? , ? , ? ,
  TIMESTAMP(CAST(? as DATE), CAST(? as TIME)) ,
  TIMESTAMP(CAST(? as DATE), CAST(? as TIME)))
  
  Since ODBC uses DB2's multi-row insert SQL to send rows of
  data to DB2 for insert, this causes regression for ODBC
  customers with existing V8 applications that performs
  multi-row inserts where parameters are part of expressions.
  

Problem conclusion

• Code has been changed to process multi-row insert statements
  differently, when they involve parameters that are part of
  expressions.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK31806

Modules/Macros

•    CLISQL   DSNAOCLI DSNAOC0W DSNAOSDK
  

Fix information

• Fixed component name

  DB2 ODBC/JDBC/S

• Fixed component ID

  5740XYR02

Applicable component levels

• R917 PSY UK31806

     UP07/12/18 P F712

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.