JR60261: ORACLE CONNECTOR INCORRECTLY CALCULATES DECIMAL FIELD SIZE WHEN CC_ORA_BIND_DECIMAL_AS_CHAR=TRUE FOR BULK LOAD MODE

Download the mod pack for IBM InfoSphere Information Server 11.7.1.0 In-Place Upgrade

APAR status

• Closed as program error.

Error description

• When binding a decimal as a character string, the direct path
  load buffer typically requires more storage. When calculating
  the number of bytes needed for a decimal column bound as a
  character string, the Oracle Connector incorrectly uses the
  size of the column as if it were still decimal.
  The environment variable CC_ORA_BIND_DECIMAL_AS_CHAR was
  introduced for multiple reasons
  > The datatype SQLT_PDN does not working for precision greater
  than 43.
    The Oracle Client throws an error, either ORA-01487 or
  ORA-03149 depending on the client version.
  > Although the datatype SQLT_PDN is faster, using it truncates
  values rather than rounds the values.
  

Local fix

• Declare the decimal column size as 44 or more. This will
  trigger the correct behavior.
  

Problem summary

• Truncation is observed when rounding is expected doing a bulk
  load of a numeric field. This is due to the use of the SQLT_PDN
  data type for handling DECIMAL columns. SQLT_PDN is faster, but
  Oracle truncates this datatype instead of rounds. To prevent the
   truncation, use the environment variable
  CC_ORA_BIND_DECIMAL_AS_CHAR.
  CC_ORA_BIND_DECIMAL_AS_CHAR
  Use this environment variable to specify use of the SQLT_CHR
  external data type instead of SQLT_PDN. Set this to TRUE or
  FALSE in the job or project.
  

Problem conclusion

• Calculation of buffer space for a Bulk Load with
  CC_ORA_BIND_DECIMAL_AS_CHAR was corrected.
  

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:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• RB50 PSY

     UP

• RB70 PSY

     UP