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?
8.1 is behaving differently than 7.0
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 9
- Joined: Thu May 13, 2010 4:58 am
-
- Charter Member
- Posts: 23
- Joined: Mon Dec 29, 2003 7:38 am
- Location: Finland, Northern Europe
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
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