8.1 is behaving differently than 7.0

[BritDeveloper]

Hi All,

we are currenntly having problems with a job that we have migrated from 7.0 (SE) to 8.1 (EE)

Update Query type
"update action from options and column tabs"

update action
"Insert rows without clearing"

generated sql version 7:

INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,
PRODUCT_GROUP_ID,ALCO_GROUP_ID,PRODUCT_DESCRIPTION,
LAST_UPDATE_DATE,REPAYMENT_TYPE,VERSO_PRODUCT_YN,
MONITOR_GROUP_CODE,DAILY_INTEREST_YN,FLEXIBLE_YN,
CASHBACK_YN,BONUS_YEAR,BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,PROCURATION_FEE_PERCENTAGE,
TRANCHE_AMOUNT,ADDITIONAL_TRANCHE_AMOUNT,
LATEST_INTEREST_RATE,PRODUCT_CATEGORY,FLOOR_RATE,
CAPPED_RATE,TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,
AFFINITY,FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,
CASHBACK_PERCENT,PG_DESC,REDEM_PENALTY_INCEPTION,
REDEM_PENALTY_AMOUNT,REDEM_PENALTY_PERIOD,
REDEM_PENALTY_END_DATE,FIX_DATE,FIX_PERIOD)
VALUES
(:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,
:18,:19,:20,:21,:22,:23,
:24,:25,:26,:27,:28,:29,:30,:31,TO_DATE(:32, 'YYYY-MM-DD HH24:MI:SS'),:33,
TO_DATE(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,
TO_DATE(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_DATE(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)

generated sql version 8:

INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,
PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,PRODUCT_GROUP_ID,
ALCO_GROUP_ID,PRODUCT_DESCRIPTION,LAST_UPDATE_DATE,
REPAYMENT_TYPE,VERSO_PRODUCT_YN,MONITOR_GROUP_CODE,
DAILY_INTEREST_YN,FLEXIBLE_YN,CASHBACK_YN,BONUS_YEAR,
BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,
PROCURATION_FEE_PERCENTAGE,TRANCHE_AMOUNT,
ADDITIONAL_TRANCHE_AMOUNT,LATEST_INTEREST_RATE,
PRODUCT_CATEGORY,FLOOR_RATE,CAPPED_RATE,
TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,AFFINITY,
FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,CASHBACK_PERCENT,
PG_DESC,REDEM_PENALTY_INCEPTION,REDEM_PENALTY_AMOUNT,
REDEM_PENALTY_PERIOD,REDEM_PENALTY_END_DATE,FIX_DATE,
FIX_PERIOD)
VALUES
(:1,TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_TIMESTAMP(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,
TO_TIMESTAMP(:32, 'YYYY-MM-DD HH24:MI:SS'),
:33,TO_TIMESTAMP(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,TO_TIMESTAMP(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_TIMESTAMP(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)


We are using Oracle 10g and the structure of the table is as follows:

Describing product....
NAME Null? Type
------------------------------- --------- -----
PRODUCT_SEQ_NO NOT NULL NUMBER(10,0)
TIME_STAMP NOT NULL DATE
cont.....

Its as if the DS engine is interperating the destination type as a TIMESTAMP rather than a DATE as specified in th table.

One thing to mention is that when this was originally coded in ver 7 ORAOCI8 was used and before import i "cleaned" the dsx by replacing all the ORAOCI8 to ORAOCI9

Has anyone else come up against this and/or know how to "fix" it?

[yannish]

Hi,

IBM did fix numerous bugs in version 8 concerning Oracle OCI stage and one aspect especially affected with those fixes was date-conversions. I actually stumbled myself into a similar problem and the fix was quite straight forward even though quite annoying: As the actual data type in the Oracle database is Date you need to use date as the data type also in DataStage jobs. I suggest you to import the table definition again as the import tool has also gone through some improvements and you should see the correct data types.

Jannev