Topic
  • 14 replies
  • Latest Post - ‏2011-08-10T00:39:13Z by keico
keico
keico
12 Posts

Pinned topic Oracle DATE type on extracting

‏2011-06-21T04:36:29Z |
I have a problem on extracting Oracle DATE type.
Oracle DATE type is extracted as the following format.
2011-06-20 07:07:07.0
It should be extracted as the format of '2011-06-20 07:07:07'.
Because of it, I receive enormous messages on data loading.
Why does Oracle DATE type convert to Timestamp(1) type?
Updated on 2011-08-10T00:39:13Z at 2011-08-10T00:39:13Z by keico
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-06-21T10:43:06Z  
    We're seeing exactly the same problem. Because of this, we get some 21GB of warning messages during the load... :-)
    Any idea?

    Carmine
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-06-21T11:07:05Z  
    We're seeing exactly the same problem. Because of this, we get some 21GB of warning messages during the load... :-)
    Any idea?

    Carmine
    We have many Oracle tables that have fields with type DATE that are sometimes unloaded with extra ".0" appended to the end of the date/timestamp field .

    e.g

    on Oracle

    describe nst.bill_filter_member_lists
    Name Null? Type

    --------
    BILL_FILTER_ID NOT NULL NUMBER(8)
    MEMBER_LIST_ID NOT NULL NUMBER(8)
    CREATED_DATE NOT NULL DATE
    CREATED_PARTY_ID NOT NULL NUMBER(8)
    LAST_UPDATE_DATE NOT NULL DATE
    LAST_UPDATE_PARTY_ID NOT NULL NUMBER(8)

    on DB2

    db2 describe table nst.bill_filter_member_lists

    Data type Column
    Column name schema Data type name Length Scale Nulls

    ---------
    ----------
    ------
    BILL_FILTER_ID SYSIBM DECIMAL 8 0 No
    MEMBER_LIST_ID SYSIBM DECIMAL 8 0 No
    CREATED_DATE SYSIBM TIMESTAMP 7 0 No
    CREATED_PARTY_ID SYSIBM DECIMAL 8 0 No
    LAST_UPDATE_DATE SYSIBM TIMESTAMP 7 0 No
    LAST_UPDATE_PARTY_ID SYSIBM DECIMAL 8 0 No

    6 record(s) selected.
    the unloaded data file

    2~913~2003-09-03 17:49:59*.0*~7801~2003-09-03 17:49:59*.0*~7801
    100199~190~2009-07-10 17:21:32*.0*~30853~2009-07-10 17:57:14*.0*~30853
    100200~190~2009-07-10 17:57:14*.0*~30853~2009-07-10 17:57:14*.0*~30853
    101264~202~2010-02-12 13:59:11*.0*~428~2010-02-12 15:57:15*.0*~428

    the generated load command works but produces truncation warning messages due to the extra characters on the end of the Oracle timestamp/date field.
    --#SET :LOAD:NST:BILL_FILTER_MEMBER_LISTS
    LOAD FROM
    "/twssdb/tms/nst/data_tms/data/nst_bill_filter_member_lists.txt"
    OF DEL
    MODIFIED BY CODEPAGE=1208 COLDEL~ ANYORDER USEDEFAULTS CHARDEL"" DELPRIORITYCHAR DUMPFILE="/twssdb/tms/nst/data_tms/dump/nst_bill_filter_member_lists.txt"
    METHOD P (1,2,3,4,5,6)
    MESSAGES "/twssdb/tms/nst/data_tms/msg/nst_bill_filter_member_lists.txt"
    REPLACE INTO "NST"."BILL_FILTER_MEMBER_LISTS"
    (
    "BILL_FILTER_ID",
    "MEMBER_LIST_ID",
    "CREATED_DATE",
    "CREATED_PARTY_ID",
    "LAST_UPDATE_DATE",
    "LAST_UPDATE_PARTY_ID"
    )
    FOR EXCEPTION "NST_EXP"."BILL_FILTER_MEMBER_LISTS"
    --STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
    NONRECOVERABLE
    INDEXING MODE AUTOSELECT
    ;

    produces these messages

    =================================================================
    Messages start for NST.BILL_FILTER_MEMBER_LISTS
    =================================================================
    SQL3501W The table space(s) in which the table resides will not be placed in
    backup pending state since forward recovery is disabled for the database.
    SQL3109N The utility is beginning to load data from file
    "/twssdb/tms/nst/data_tms/data/nst_bill_filter_member_lists.txt".
    SQL3500W The utility is beginning the "LOAD" phase at time "06/20/2011
    20:42:52.300714".
    SQL3519W Begin Load Consistency Point. Input record count = "0".
    SQL3520W Load Consistency Point was successful.
    SQL3125W The character data in row "F0-1" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-1" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-2" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-2" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3110N The utility has completed processing. "4" rows were read from the
    input file.
    SQL3519W Begin Load Consistency Point. Input record count = "4".
    SQL3520W Load Consistency Point was successful.
    SQL3515W The utility has finished the "LOAD" phase at time "06/20/2011
    20:42:52.355120".
    SQL3500W The utility is beginning the "BUILD" phase at time "06/20/2011
    20:42:52.355345".
    SQL3213I The indexing mode is "REBUILD".
    SQL3515W The utility has finished the "BUILD" phase at time "06/20/2011
    20:42:52.429540".
    SQL3107W There is at least one warning message in the message file.
    =================================================================
    And this was the easy part. Now, here's where the fun starts...
    In some cases, when a table has multiple DATE fields, some of them are actually extracted properly. Here's an example:

    Oracle table definition:

    describe nst.aud_equip_tag_descs
    Name Null? Type

    --------
    AUDIT_TYPE VARCHAR2(1)
    PROCESS_FLAG VARCHAR2(1)
    TIME_STAMP DATE
    UPD_COUNT NUMBER(4)
    DESC_ID NOT NULL NUMBER(8)
    TYPE_ID NUMBER(8)
    LANGUAGE_CODE VARCHAR2(6)
    SHORT_DESC VARCHAR2(60)
    LONG_DESC VARCHAR2(255)
    EXTERNAL_REFERENCE VARCHAR2(20)
    START_DATE DATE
    END_DATE DATE
    DB2 table definition:

    db2 describe table nst.aud_equip_tag_descs

    Data type Column
    Column name schema Data type name Length Scale Nulls

    ---------
    ----------
    ------
    AUDIT_TYPE SYSIBM VARCHAR 1 0 Yes
    PROCESS_FLAG SYSIBM VARCHAR 1 0 Yes
    TIME_STAMP SYSIBM TIMESTAMP 7 0 Yes
    UPD_COUNT SYSIBM DECIMAL 4 0 Yes
    DESC_ID SYSIBM DECIMAL 8 0 No
    TYPE_ID SYSIBM DECIMAL 8 0 Yes
    LANGUAGE_CODE SYSIBM VARCHAR 6 0 Yes
    SHORT_DESC SYSIBM VARCHAR 60 0 Yes
    LONG_DESC SYSIBM VARCHAR 255 0 Yes
    EXTERNAL_REFERENCE SYSIBM VARCHAR 20 0 Yes
    START_DATE SYSIBM TIMESTAMP 7 0 Yes
    END_DATE SYSIBM TIMESTAMP 7 0 Yes

    12 record(s) selected.

    Extracted data:

    "I"~"N"~2011-04-19 11:14:40*.0*~0~1074~54~"ZH""¿¿¿""Multi-compartment""MC"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1075~29~"ZH""¿¿¿¿¿¿""Trailer,Electric Heat""TQ"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1076~5~"ZH""¿¿¿-¿¿""Tanker-Gas""TG"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1077~61~"ZH""¿¿""Side Curtains""SC"2000-01-01 00:00:00~2020-12-31 00:00:00

    Any idea?

    Carmine
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-06-21T11:38:30Z  
    We have many Oracle tables that have fields with type DATE that are sometimes unloaded with extra ".0" appended to the end of the date/timestamp field .

    e.g

    on Oracle

    describe nst.bill_filter_member_lists
    Name Null? Type

    --------
    BILL_FILTER_ID NOT NULL NUMBER(8)
    MEMBER_LIST_ID NOT NULL NUMBER(8)
    CREATED_DATE NOT NULL DATE
    CREATED_PARTY_ID NOT NULL NUMBER(8)
    LAST_UPDATE_DATE NOT NULL DATE
    LAST_UPDATE_PARTY_ID NOT NULL NUMBER(8)

    on DB2

    db2 describe table nst.bill_filter_member_lists

    Data type Column
    Column name schema Data type name Length Scale Nulls

    ---------
    ----------
    ------
    BILL_FILTER_ID SYSIBM DECIMAL 8 0 No
    MEMBER_LIST_ID SYSIBM DECIMAL 8 0 No
    CREATED_DATE SYSIBM TIMESTAMP 7 0 No
    CREATED_PARTY_ID SYSIBM DECIMAL 8 0 No
    LAST_UPDATE_DATE SYSIBM TIMESTAMP 7 0 No
    LAST_UPDATE_PARTY_ID SYSIBM DECIMAL 8 0 No

    6 record(s) selected.
    the unloaded data file

    2~913~2003-09-03 17:49:59*.0*~7801~2003-09-03 17:49:59*.0*~7801
    100199~190~2009-07-10 17:21:32*.0*~30853~2009-07-10 17:57:14*.0*~30853
    100200~190~2009-07-10 17:57:14*.0*~30853~2009-07-10 17:57:14*.0*~30853
    101264~202~2010-02-12 13:59:11*.0*~428~2010-02-12 15:57:15*.0*~428

    the generated load command works but produces truncation warning messages due to the extra characters on the end of the Oracle timestamp/date field.
    --#SET :LOAD:NST:BILL_FILTER_MEMBER_LISTS
    LOAD FROM
    "/twssdb/tms/nst/data_tms/data/nst_bill_filter_member_lists.txt"
    OF DEL
    MODIFIED BY CODEPAGE=1208 COLDEL~ ANYORDER USEDEFAULTS CHARDEL"" DELPRIORITYCHAR DUMPFILE="/twssdb/tms/nst/data_tms/dump/nst_bill_filter_member_lists.txt"
    METHOD P (1,2,3,4,5,6)
    MESSAGES "/twssdb/tms/nst/data_tms/msg/nst_bill_filter_member_lists.txt"
    REPLACE INTO "NST"."BILL_FILTER_MEMBER_LISTS"
    (
    "BILL_FILTER_ID",
    "MEMBER_LIST_ID",
    "CREATED_DATE",
    "CREATED_PARTY_ID",
    "LAST_UPDATE_DATE",
    "LAST_UPDATE_PARTY_ID"
    )
    FOR EXCEPTION "NST_EXP"."BILL_FILTER_MEMBER_LISTS"
    --STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
    NONRECOVERABLE
    INDEXING MODE AUTOSELECT
    ;

    produces these messages

    =================================================================
    Messages start for NST.BILL_FILTER_MEMBER_LISTS
    =================================================================
    SQL3501W The table space(s) in which the table resides will not be placed in
    backup pending state since forward recovery is disabled for the database.
    SQL3109N The utility is beginning to load data from file
    "/twssdb/tms/nst/data_tms/data/nst_bill_filter_member_lists.txt".
    SQL3500W The utility is beginning the "LOAD" phase at time "06/20/2011
    20:42:52.300714".
    SQL3519W Begin Load Consistency Point. Input record count = "0".
    SQL3520W Load Consistency Point was successful.
    SQL3125W The character data in row "F0-1" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-1" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-2" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-2" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "3" was truncated
    because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "5" was truncated
    because the data is longer than the target database column.
    SQL3110N The utility has completed processing. "4" rows were read from the
    input file.
    SQL3519W Begin Load Consistency Point. Input record count = "4".
    SQL3520W Load Consistency Point was successful.
    SQL3515W The utility has finished the "LOAD" phase at time "06/20/2011
    20:42:52.355120".
    SQL3500W The utility is beginning the "BUILD" phase at time "06/20/2011
    20:42:52.355345".
    SQL3213I The indexing mode is "REBUILD".
    SQL3515W The utility has finished the "BUILD" phase at time "06/20/2011
    20:42:52.429540".
    SQL3107W There is at least one warning message in the message file.
    =================================================================
    And this was the easy part. Now, here's where the fun starts...
    In some cases, when a table has multiple DATE fields, some of them are actually extracted properly. Here's an example:

    Oracle table definition:

    describe nst.aud_equip_tag_descs
    Name Null? Type

    --------
    AUDIT_TYPE VARCHAR2(1)
    PROCESS_FLAG VARCHAR2(1)
    TIME_STAMP DATE
    UPD_COUNT NUMBER(4)
    DESC_ID NOT NULL NUMBER(8)
    TYPE_ID NUMBER(8)
    LANGUAGE_CODE VARCHAR2(6)
    SHORT_DESC VARCHAR2(60)
    LONG_DESC VARCHAR2(255)
    EXTERNAL_REFERENCE VARCHAR2(20)
    START_DATE DATE
    END_DATE DATE
    DB2 table definition:

    db2 describe table nst.aud_equip_tag_descs

    Data type Column
    Column name schema Data type name Length Scale Nulls

    ---------
    ----------
    ------
    AUDIT_TYPE SYSIBM VARCHAR 1 0 Yes
    PROCESS_FLAG SYSIBM VARCHAR 1 0 Yes
    TIME_STAMP SYSIBM TIMESTAMP 7 0 Yes
    UPD_COUNT SYSIBM DECIMAL 4 0 Yes
    DESC_ID SYSIBM DECIMAL 8 0 No
    TYPE_ID SYSIBM DECIMAL 8 0 Yes
    LANGUAGE_CODE SYSIBM VARCHAR 6 0 Yes
    SHORT_DESC SYSIBM VARCHAR 60 0 Yes
    LONG_DESC SYSIBM VARCHAR 255 0 Yes
    EXTERNAL_REFERENCE SYSIBM VARCHAR 20 0 Yes
    START_DATE SYSIBM TIMESTAMP 7 0 Yes
    END_DATE SYSIBM TIMESTAMP 7 0 Yes

    12 record(s) selected.

    Extracted data:

    "I"~"N"~2011-04-19 11:14:40*.0*~0~1074~54~"ZH""¿¿¿""Multi-compartment""MC"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1075~29~"ZH""¿¿¿¿¿¿""Trailer,Electric Heat""TQ"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1076~5~"ZH""¿¿¿-¿¿""Tanker-Gas""TG"2000-01-01 00:00:00~2020-12-31 00:00:00
    "I"~"N"~2011-04-19 11:14:41*.0*~0~1077~61~"ZH""¿¿""Side Curtains""SC"2000-01-01 00:00:00~2020-12-31 00:00:00

    Any idea?

    Carmine
    This problem has been fixed in the new build but I am not able to post that build yet on the download site due to the incomplete testing. I do not have a testing infrastructure and I rely upon our customers and other IBMers to do the testing for me. Normally, bugs and issues are fixed on a priority basis and new build is put the download site ASAP. This time, it is an exception due to huge changes that have occured in the tool to make Netezza and z/OS DB2 as a target.

    Meanwhile, you can overcome this problem by setting

    norowwarning=true in the IBMExtract.properties file and you will not see these warning messages as they are suppressed. But, this is only an interim workaround. For a long time, norowwarning was used in the LOAD statement and that was incorrect thing to do as we did not know if truncation occured or not. The DATE column extracted has .0 in it and the data gets loaded OK but this should have been fixed long time back if I had not used NOROWWARNING. Any way, we learn from our past mistakes.

    The new build should be on the download site in few days as I am getting feedback and issues from other customers.

    Thanks
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-06-22T00:28:25Z  
    To ccristal
    Thank you for explaining in detail. :)

    To Anabas
    Thank you for your answer.
    Hearing the probelm has been fixed and new build will be released soon, I was relieved. I wait for it.
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-07T07:55:12Z  
    Has this fixed build already released?
    I downloaded new one today (jar file was modified at July 5th) and retried, but the probelm don't improve.
    Or do I use it the wrong way?

    By the way, how can I know the build no?

    Thanks.
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-07T12:03:24Z  
    • keico
    • ‏2011-07-07T07:55:12Z
    Has this fixed build already released?
    I downloaded new one today (jar file was modified at July 5th) and retried, but the probelm don't improve.
    Or do I use it the wrong way?

    By the way, how can I know the build no?

    Thanks.
    Hmm. It is strange that you still see the issue. So, please try this.

    1. Copy the new downloaded tool in a new directory.
    2. From the command line, run ./IBMDataMovementTool.sh -version or IBMDataMovementTool.cmd -version or if you run the GUI, the version is located on the tool bar or you can check Help>About.
    3. Run the tool again on the affected table having DATE and let me know if you still see this issue.
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-08T02:03:50Z  
    Hmm. It is strange that you still see the issue. So, please try this.

    1. Copy the new downloaded tool in a new directory.
    2. From the command line, run ./IBMDataMovementTool.sh -version or IBMDataMovementTool.cmd -version or if you run the GUI, the version is located on the tool bar or you can check Help>About.
    3. Run the tool again on the affected table having DATE and let me know if you still see this issue.
    I downloaded new one today again, and I confirmed the version is 2.00-b2106.
    Then I retried, but still see the same issue.
    For confirmation I compared the results between old and new with diff tool, and still I found no difference.

    And also I noticed that some are right format, some are bad format.
    In the former case, original data format is 'yyyy-MM-dd', and in the latter case original format is 'yyyy-MM-dd hh:mm:ss'.

    Oracle data '2011-07-08' ---> IDMT extract data '2011-07-08 00:00:00'
    Oracle data '2011-07-08 11:23:45' ---> IDMT extract data '2011-07-08 11:23:45.0'
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-08T13:36:56Z  
    • keico
    • ‏2011-07-08T02:03:50Z
    I downloaded new one today again, and I confirmed the version is 2.00-b2106.
    Then I retried, but still see the same issue.
    For confirmation I compared the results between old and new with diff tool, and still I found no difference.

    And also I noticed that some are right format, some are bad format.
    In the former case, original data format is 'yyyy-MM-dd', and in the latter case original format is 'yyyy-MM-dd hh:mm:ss'.

    Oracle data '2011-07-08' ---> IDMT extract data '2011-07-08 00:00:00'
    Oracle data '2011-07-08 11:23:45' ---> IDMT extract data '2011-07-08 11:23:45.0'
    Hmm. This is troubling that you are still seeing this issue whereas I just did a test and I do not see it.

    It will be very helpful for me to see why this anomaly if you provide a sample Oracle table ddl and with one or two insert statement so that I can reproduce it. That way, it will be much easier to determine why you are still seeing the same issue.

    Thanks
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-11T01:46:50Z  
    Hmm. This is troubling that you are still seeing this issue whereas I just did a test and I do not see it.

    It will be very helpful for me to see why this anomaly if you provide a sample Oracle table ddl and with one or two insert statement so that I can reproduce it. That way, it will be much easier to determine why you are still seeing the same issue.

    Thanks
    Thanks for your reply.
    I'll attach 3 files, which are ddl, sample insert statement and extracted data with IDMT (actually the table has more records).

    I appreciate your investigation.
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-12T13:50:18Z  
    • keico
    • ‏2011-07-11T01:46:50Z
    Thanks for your reply.
    I'll attach 3 files, which are ddl, sample insert statement and extracted data with IDMT (actually the table has more records).

    I appreciate your investigation.
    Thanks for the testcase and I tested with the 11g and idmt build # 2105 and it seems to work ok.

    Can you please confirm the build # of the tool and source oracle version? Please download the latest build from the same download site and try again
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-07-13T01:29:10Z  
    Thanks for the testcase and I tested with the 11g and idmt build # 2105 and it seems to work ok.

    Can you please confirm the build # of the tool and source oracle version? Please download the latest build from the same download site and try again
    I'm sorry I didn't tell you my environment. I test with the Oracle9i and ojdbc14.jar.
    Then I tried again with the latest build #2108, but the problem didn't improve.
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-08-03T09:13:31Z  
    • keico
    • ‏2011-07-13T01:29:10Z
    I'm sorry I didn't tell you my environment. I test with the Oracle9i and ojdbc14.jar.
    Then I tried again with the latest build #2108, but the problem didn't improve.
    Today I tried again with idmt build no 2111.
    I found that GUI mode is OK but console mode is NG.
    On console mode, "dstVendor" value isn't saved.
    Though I choose "DB2 LUW with compatibility : 1", "Your Target database is 'db2luw'" is displayed on the screen and value of "dstVendor" is "db2luw".

    I tried to rewrite "dstVendow" value and execute "unload", but the problem didn't change.
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: Oracle DATE type on extracting

    ‏2011-08-09T14:23:15Z  
    • keico
    • ‏2011-08-03T09:13:31Z
    Today I tried again with idmt build no 2111.
    I found that GUI mode is OK but console mode is NG.
    On console mode, "dstVendor" value isn't saved.
    Though I choose "DB2 LUW with compatibility : 1", "Your Target database is 'db2luw'" is displayed on the screen and value of "dstVendor" is "db2luw".

    I tried to rewrite "dstVendow" value and execute "unload", but the problem didn't change.
    Sorry to jump in on this thread. I'm also attempting to migrate Oracle 11gR2 using b2111 and we're seeing odd date issues with "old" dates.
    Oracle date is 01-MAR-02 is translated to 0000-2-03-1 00:00:00.
    This, of course results in a SQL3125W due to the data being in a non TIMESTAMP format.

    Thanks

    Meir Zohar
  • keico
    keico
    12 Posts

    Re: Oracle DATE type on extracting

    ‏2011-08-10T00:39:13Z  
    Sorry to jump in on this thread. I'm also attempting to migrate Oracle 11gR2 using b2111 and we're seeing odd date issues with "old" dates.
    Oracle date is 01-MAR-02 is translated to 0000-2-03-1 00:00:00.
    This, of course results in a SQL3125W due to the data being in a non TIMESTAMP format.

    Thanks

    Meir Zohar
    Just yesterday I solved this problem in the following ways.
    I modified the last line of the generated "unload" file and the tool became to work well.
    At the last line, java command is written with the wrong argument if you use console mode.

    Before modifying: $JAVA_HOME/bin/java -Xmx990m -DTARGET_DB=db2luw -DOUTPUT_DIR.......

    Afetr modifying: $JAVA_HOME/bin/java -Xmx990m -DTARGET_DB=db2luw_compatibility -DOUTPUT_DIR.......

    I wish this helps.