PM70455: WRITE IFCID 366 TRACE RECORD FOR VARCHAR, CHAR, CAST WITH DECIMAL INPUT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Write IFCID 366 trace record for the following incompatible
  changes on Version 10. The trace record can be used to identify
  applications on Version 9 that may need to be changed after
  migration to Version 10.
  
  
  - CHAR(decimal) built-in function
  - VARCHAR(decimal) built-in function
  - CAST(decimal AS CHAR(n)) specification
  - CAST(decimal AS VARCHAR(n)) specification
  - invalid timestamp format 'dd.mm.yyyyhh.mm.ss.nnnnnn'
  - invalid timestamp format 'yyyy-mm-dd-hh:mm:ss.nnnnnn'
  

Local fix

• NA
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users migrating to        *
  *                 DB2 10 for z/OS                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: This PTF adds a new IFCID 366 trace     *
  *                      record to Version 9.  The trace record  *
  *                      can be used to identify applications    *
  *                      that may be affected by the following   *
  *                      changes in DB2 Version 10.              *
  *                                                              *
  *                        1.  String formatting of decimal data *
  *                                                              *
  *                        2.  -180 SQLCODE for unsupported      *
  *                            character string representations  *
  *                            of a timestamp value              *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  To help with migration to DB2 10 for z/OS, IFCID 366 will be
  retrofit to Version 9 with this PTF. The trace records can
  be used to identify applications that may be affected due to
  the following changes in Version 10.
  
  1. Applications that use SQL to format decimal data as a
  character string may be affected.  Refer to the paragraph
  titled "Changes to string formatting of decimal data" in the
  V10 DB2 Application Programming and SQL Guide section titled
  "Application and SQL release incompatibilities for migration
  from Version 9.1" for details on the changes.
  
  The trace record will be written for any SQL that executes
  one of the following expressions:
  
     CHAR(decimal)
     VARCHAR(decimal)
     CAST(decimal as CHAR(n))
     CAST(decimal as VARCHAR(n))
  
  The trace records can be used to find applications that may
  potentially be affected by the incompatible change on Version
  10.  The application programmer needs to determine if actual
  changes are necessary to the SQL statement identified by the
  366 trace record.
  
  2. The trace record will also be written for some unsupported
  timestamp strings that DB2 did not correctly issue SQLCODE
  -180 in DB2 releases prior to Version 10.  After migration to
  Version 10, DB2 will start issuing SQLCODE -180.  The
  applications identified need to be corrected to use a supported
  timestamp string.  The changes should be made on Version 9
  before the migration to 10.  Refer to the section titled "String
  representations of datetime values" in the V9 or V10 DB2
  SQL Reference for correct formats of timestamp strings.
  
  The format for the IFCID 366 records follows :
  
  QW0366       DSECT
  QW0366FN     F               The value '1' indicates the
                               SYSIBM.CHAR(decimal-expr)
                               function has been executed.
  
                               The value '2' indicates the
                               SYSIBM.VARCHAR(decimal-expr),
                               CAST(decimal-expr AS CHAR) or
                               CAST(decimal-expr AS VARCHAR)
                               function has been executed.
  
                               The value '3' indicates an
                               unsupported character string
                               representation of a timestamp
                               has been used.
  
  QW0366SN     F               Statement number of the query
  QW0366PL     DS CL8          Plan name for this query
  QW0366TS     DS CL8          Timestamp for this query
  QW0366SI     DS CL8          Statement Identifier
                               (Statement ID is only valid for
                               SQL statements that are dynamically
                               cached)
  QW0366TY     DS XL2          Statement information
  QW0366DY     EQU X'8000'     Statement is dynamic
  QW0366SC     EQU X'4000'     Statement is static
  QW0366SE     DS H            Section number
  QW0366PC_Off DS H            Offset from QW0366 to Package
                               Collection ID
  QW0366PN_Off DS H            Offset from QW0366 to
                               Program name
  QW0366VL DS  H               Version length
  QW0366VN DS  CL64            Version
  
  QW0366PC_D   DSECT
  QW0366PC_Len DS    H         Length of Package Collection ID
  QW0366PC_Var CL128           %U Package Collection ID
  QW0366PN_D   DSECT
  QW0366PN_Len DS    H         Length of Program Name
  QW0366PN_Var CL128           %U Program Name
  
  
  The trace can be started using the command :
  
    -start trace(p) class(32) ifcid(366).
  

Problem conclusion

Temporary fix

Comments

• IFCID 366 trace record is retrofit to Version 9 with this PTF.
  Please see related Version 10 apars/PTFs PM29124 / UK67578 ,
  PM66095 / UK81581 and PM48741 / UK74765 for additional details.
  
  Additional keywords : IFCID366 SQLCHAR SQLVARCHAR SQLCAST
                        SQLTIMESTAMP SQLMIGRATION SQLDECIMAL
  

APAR Information

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

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

  UK92860

Modules/Macros

• DSNDQW04 DSNWVINT DSNXOLTD DSNXRDEC DSNXRGPL
  DSNXRIHB DSNXRIHD DSNXRIHR DSNXRIHS DSNXRINT DSNXRITV DSNXRLET
  DSNXRSBC DSNXRSB2 DSNXRSPL DSNXRTIM DSNXVCCS DSNXVDTT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK92860

     UP13/04/10 P F304

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.