A fix is available
Closed as program error.
DB2DDF DCR DM1743 SQLCODE420 received for implicit cast of string to numeric Additional keywords and symptoms: ********************************* DDFCOMPAT DSN6FAC
USERS AFFECTED: All Distributed Data Facility (DDF users accessing DB2 10 for z/OS in new function mode from a non-Java IBM Data Server Client Driver where the client application has input host variables that undergo implicit casting. PROBLEM DESCRIPTION: A client application using a non-Java IBM Data Server Driver accesses a DB2 10 for z/OS server in New Function Mode (NFM , passing in a character input value that is an empty string or contains all blank characters where the target has a numeric SQL type. The statement correctly fails with SQLCODE -420, due to the invalid character value, which cannot be implicitly cast to a numeric target. However, this application successfully executed when accessing a DB2 server at version 9 and below or when accessing DB2 10 Conversion Mode (CM . RECOMMENDATION: The remote statement being processed by DB2 contains invalid character data that cannot be implicitly cast. The statement executed correctly against DB2 9 and below and against DB2 10 CM because those levels do not provide implicit cast support. As a result, the input data was correctly flagged as being in error and the statement failed with SQLCODE -301. The non-Java IBM Data Server Client driver provides a property called RetryOnError that allows for the statement to be retried with corrected user data when SQLCODE -301 is received. The client driver corrects the invalid character data, converting to a numeric, before sending to DB2. With implicit cast support in DB2 10 NFM, this corrective action is not being performed by the client driver since the statement fails with SQLCODE -420 instead. The client driver does not have support to redrive the statement for SQLCODE -420. . One solution is for the application to correct the invalid inputs. . Another solution is provided by the IBM Data Server Driver. See APAR IC92430. Refer to the APAR for details.
To help customers determine whether they have applications that may fail with the same error as described above, the DB2 10 for z/OS server is providing new trace support and a new option for the DDF_COMPATIBILITY ZPARM. The trace support allows you to detect applications that may encounter the same failures as described above. The ZPARM allows you to disable implicit casting for such application running in V10 NFM while you implement corrective actions either through changes to your application or through the use of client driver support. If you are currently in V10 Conversion Mode (CM , with this PTF, you can enable the trace to identify applications that may have the same problem as described here after migration to New Function Mode (NFM . If you are currently in V10 NFM or are moving to V10 NFM, with this new ZPARM option, you can restore the server bind-in processing behavior without implicit cast. If the ZPARM option to disable implicit cast is specified, you can continue to use the trace to monitor and identify applications that may be problematic, as described above. If you have an application with a statement that fails with SQLCODE -420 in DB2 10 NFM due to invalid character input data that cannot be implicitly cast to numeric targets, then if you specify the new DDF_COMPATIBILITY ZPARM option, as described below, that statement will now successfully execute as it did when it was executed against DB2 version 9 and below or against DB2 10 CM. The ZPARM and trace details: 1. This PTF adds a new option, DISABLE_IMPCAST_NJV, to the DB2 subsystem parameter DSN6FAC.DDF_COMPATIBILITY. You can use the DDF_COMPATIBILITY parameter to enable DB2 10 DDF compatibility with older client drivers. The new DDF_COMPATIBILITY option is: - DISABLE_IMPCAST_NJV: Use the pre-V10 server compatibility behavior which is not to implicitly cast input host variables during server host variable bind-in processing. This option provides such compatibility only for non-Java IBM Data Server drivers at or below Version 10.5. It is ignored for any non-Java IBM Data Server driver at any higher level. This option is deprecated in DB2 10. If the DISABLE_IMPCAST_NJV option is specified on any member of a data sharing environment, it is recommended to specify it on all members. DDF_COMPATIBILITY options can be specified individually or together. Examples: DDF_COMPATIBILITY= DDF_COMPATIBILITY=DISABLE_IMPCAST_NJV DDF_COMPATIBILITY=SP_PARMS_NJV DDF_COMPATIBILITY=(SP_PARMS_NJV,DISABLE_IMPCAST_NJV The default for DDF_COMPATIBILITY remains null (as shown in the first example above meaning that DB2 10 DDF behavior is maintained with all clients. 2. The PTF also provides a way to detect when SQLCODE -301 is generated for incompatible data type conversions occurring during server host variable bind in when the client application is using a non-Java IBM Data Server Driver and implicit cast is not in effect. When IFCID 0366 is enabled on DB2 10 for z/OS server when implicit casting is not in effect, and when the application is using a non-Java IBM Data Server Driver at or below version 10.5, then DB2 10 server will produce the IFCID 0366 trace records indicating, by the following new value in the QW0366FN field, when SQLCODE -301 error for incompatible data type conversion during host variable bind is raised. QW0366FN = 7 A QW0366FN 7 record indicates that DB2 for z/OS server issued a SQLCODE -301 for incompatible data type conversion from string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC etc. to numeric data type in V10 CM if implicit cast is not supported or if the server is in V10 NFM when the DDF_COMPATIBILITY ZPARM is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to disable implicit cast, and the client is as specified above. When examining IFCID 0366 trace records collected, an application programmer who finds any records with a QW0366FN value of 7 can take a close look at the input string which is converted to a numeric data and make sure it does not contain any invalid numeric character string (e.g. string with all blank, or empty string with length 0 etc. . Once the application is free of those invalid numeric character string, DB2 can convert them into a numeric target data when implicit cast feature is enabled.
Reported component name
DB2 OS/390 & Z/
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
DSN@XAZP DSNDQWPZ DSNDQW05 DSNLXRSS DSNTIDXA DSNTIDXB DSNTINST DSNTXAZP DSNWZIFA DSNXRIHB DSNXRIHD DSNXRIHR DSNXRIHS DSN6FAC
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
Applicable component levels
RA10 PSY UK94766
UP13/06/17 P F306
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.
24 January 2014