PQ88582: ODBC SUPPORT FOR 2MB SQL STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Currently, the maximum size in bytes of an SQL statement string
  (including white spaces) allowed on the SQLPrepare,
  SQLExecDirect and SQLNativeSql APIs is 32765.  DB2 V8 running in
  NFM supports a maximum statement size of 2MB (2097152).
  V8 DB2 ODBC will now support SQL statements up to 2MB in size
  when connected to z/OS DB2 V8 servers running in NFM.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 UDB for z/OS Version 8      *
  *                 ODBC/CLI.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. Currently, the maximum statement     *
  *                         size that DB2 ODBC supports is       *
  *                         32765 bytes, while DB2 V8 in New     *
  *                         Function Mode accepts SQL            *
  *                         statements up to 2MB.                *
  *                      2. The ODBC DBRMs are coded in          *
  *                         CCSID 1047.  However the DBRMs       *
  *                         are precompiled with the default     *
  *                         CCSID value which could be           *
  *                         different from 1047.  SQLCODE        *
  *                         -104 could occur as a result.        *
  *                         Furthermore, a DBRM precompiled      *
  *                         with NEWFUN(YES) can be bound only   *
  *                         with DB2 z/OS V8 and later           *
  *                         releases.  For the downward          *
  *                         compatible ODBC driver, users        *
  *                         need to be able to bind to DB2 for   *
  *                         z/OS V7 and below.                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1. DB2 ODBC will support a maximum statement size of
     2097152 bytes (2MB) when connected to DB2 V8 for z/OS in
     New Function Mode,  during which time the new maximum SQL
     statement size will take effect for SQLPrepare(),
     SQLExecDirect(), SQLNativeSql() and SQLGetInfo()'s
     InfoType SQL_MAX_STATEMENT_LEN.
  
     If a SQL statement on a SQLPrepare() or SQLExecDirect()
     call has a length greater than 32765 bytes and the server
     that the application is connected to does not support a
     maximum statement size greater than 32KB, SQLPrepare() or
     SQLExecDirect will fail with SQLCODE -30005 and
     SQLSTATE 56072.
  
  2. The CCSID precompiler option specifies the CCSID in which
     the source program is written.  Since all ODBC DBRMs are
     coded in CCSID 1047, they will be precompiled with the
     CCSID(1047) option.
  
     The NEWFUN precompiler option indicates whether to accept
     the syntax for DB2 Version 8 functions. A DBRM precompiled
     with NEWFUN(YES) can be bound only with Version 8 and later
     releases, even if the DBRM does not use any Version 8
     syntax.  ODBC users need to be able to find ODBC DBRMs to
     DB2 for z/OS Version 7 and below, therefore all ODBC DBRMs
     will be precompiled with NEWFUN(NO).  A DBRM precompiled
     with NEWFUN(NO) can be bound with any release of DB2 for
     z/OS, including DB2 Version 8.
  

Problem conclusion

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:

  UQ91257

Modules/Macros

• CLI      CLIALLOC CLIDST   CLIEXEC  CLIMISC
  CLIQRY   CLIRETR  CLISCN   CLIUNI   CLIUTL   DSN@LIAS DSN@LICS
  DSN@LIC1 DSN@LIC2 DSN@LIF4 DSN@LIMS DSN@LINC DSN@LIQR DSN@LIRR
  DSN@LIRS DSN@LIUR DSN@LIVM DSN@LIV1 DSN@LIV2 DSNAOCLI DSNAOC0A
  DSNAOC0E DSNAOC0F DSNAOC0G DSNAOC0H DSNAOC0L DSNAOC0P DSNAOC0Q
  DSNAOC0R DSNAOC0T DSNAOC0X DSNAOC0Y DSNAOC0Z DSNAOC01 DSNAOC02
  DSNAOC07 DSNAOC08 DSNAOC1V DSNAOC10 DSNAOC11 DSNAOC12 DSNAOC17
  DSNAOC18 DSNAOSDK DSNSQL
  

Fix information

• Fixed component name

  DB2 ODBC/JDBC/S

• Fixed component ID

  5740XYR02

Applicable component levels

• R817 PSY UQ91257

     UP04/09/09 P F409

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.

IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.

Product categories:

Software

Data Management

Data Servers (Database Management Systems)

DB2 for z/OS

SQLJ/JDBC

Software version:

817

Reference #:

PQ88582

IBM Group:

Software Group

Modified date:

2004-10-05