APAR status
Closed as documentation error.
Error description
current expected behaviour is as below: As default (I mean DS_NO_FF=0), Oracle DATE is imported to Date , Oracle TIMESTAMP is imported to Timestamp on DS. But only for DATE column, we need consideration. Since DS Date has still only year, month and date information, it is NOT possible to map Oracle DATE column to DS Date data type. So For Date type, after import of metadata, we need to change the data type from Date to Timestamp by manually, or we need to use custom SQL. But documentation says that Oracle DATE and TIMESTAMP are registered as TIMESTAMP after metadata import.
Local fix
Problem summary
**************************************************************** USERS AFFECTED: Readers of book Connectivity Guide to Oracle Databases, Chapter 2. Oracle OCI Stages, section DATE Data Type Considerations **************************************************************** PROBLEM DESCRIPTION: The book Connectivity Guide to Oracle Databases, Chapter 2. Oracle OCI Stages, section DATE Data Type Considerations INCORRECTLY says this: DATE Data Type Considerations An Oracle DATE data type contains date and time information (there is no TIME data type in Oracle). IBM WebSphere DataStage maps the Oracle DATE data type to a Timestamp data type. This is the default WebSphere DataStage data type when you import the Oracle metadata type of DATE. WebSphere DataStage uses a conversion of YYYY-MM-DD HH24:MI:SS when reading or writing an Oracle date. If the WebSphere DataStage data type is Timestamp, WebSphere DataStage uses the to_date function for this column when it generates the INSERT statement to write an Oracle date. If the WebSphere DataStage data type is Timestamp or Date, WebSphere DataStage uses the to_char function for this column when it generates the SELECT statement to read an Oracle date. The following example creates a table with a DATE data type on an Oracle server. The imported WebSphere DataStage data type is Timestamp. create table dsdate (one date); The results vary, depending on whether the Oracle OCI stage is used as an input or an output link: - Input link. The stage generates the following SQL statement: insert into dsdate(one) values(TO_DATE(:1, 'yyyy-mm-dd hh24:mi:ss')) - Output link. The stage generates the following SQL statement: select TO_CHAR(one, 'YYYY-MM-DD HH24:MI:SS') FROM dsdate **************************************************************** RECOMMENDATION: See technotes for Information Server v8.1 and see updated online documentation for Information Server 8.5. ****************************************************************
Problem conclusion
It is no longer true that DATE columns are imported as TIMESTAMP.
Temporary fix
Comments
APAR Information
APAR number
JR33043
Reported component name
WIS DATASTAGE
Reported component ID
5724Q36DS
Reported release
810
Status
CLOSED DOC
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-05-15
Closed date
2009-05-25
Last modified date
2009-05-25
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
25 May 2009