PM86374: SQLCODE 420 RECEIVED FOR IMPLICIT CAST OF STRING TO NUMERIC

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2DDF DCR DM1743
  SQLCODE420 received for implicit cast of string to numeric
  
  
  Additional keywords and symptoms:
  *********************************
  DDFCOMPAT DSN6FAC
  

Local fix

Problem summary

• 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.
  

Problem conclusion

• 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.
  

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:

  UK94766

Modules/Macros

• DSN@XAZP DSNDQWPZ DSNDQW05 DSNLXRSS DSNTIDXA DSNTIDXB DSNTINST
  DSNTXAZP DSNWZIFA DSNXRIHB DSNXRIHD DSNXRIHR DSNXRIHS DSN6FAC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

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.