IY92782: ALTERING UDB TIMESTAMP COLUMN WILL FAIL WITH ORACLE DATE COLUMN

DB2 UDB Version 8.1 FixPak 16 (also known as Version 8.2 FixPak 9 )

APAR status

• Closed as program error.

Error description

• When the remote Oracle column is of type DATE, it is mapped to
  UDB TIMESTAMP in the create nickname process.  If we then alter
  this nickname column from TIMESTAMP to TIME, predicates such as
  WHERE time_col = '12:34:56' will fail to return any rows.  This
  is because Oracle DATE columns also include the date portion in
  addition to the time portion.
  

Local fix

• Enable the casting logic in the federated compiler if both of
  the following conditions are true:
  1. The server attribute db2_enforce_local_time_type is set to
  'Y'.
  2. The local type is TIME and the default type mapping is
  TIMESTAMP.
  

Problem summary

• ERROR DESCRIPTION:
  When the remote Oracle column is of type DATE, it is mapped to
  UDB TIMESTAMP in the create nickname process.  If we then alter
  this nickname column from TIMESTAMP to TIME, predicates such as
  WHERE time_col = '12:34:56' will fail to return any rows.  This
  is because Oracle DATE columns also include the date portion in
  addition to the time porti
  

Problem conclusion

• Fist fixed in V8.2 FP15
  

Temporary fix

• LOCAL FIX:
  Enable the casting logic in the federated compiler if both of
  the following conditions are true:
  1. The server attribute db2_enforce_local_time_type is set to
  'Y'.
  2. The local type is TIME and the default type mapping is
  TIMESTAMP.
  

Comments

APAR Information

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

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

  JR27715 JR27884

Fix information

• Fixed component name

  WS II ORACLE WR

• Fixed component ID

  5724C7408

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP