Oracle Enterpirse Stage - Records rejected without warning
Moderators: chulett, rschirm, roy
Oracle Enterpirse Stage - Records rejected without warning
Hi,
I have a job where i am inserting records in an oracle enterprise stage, the problem is that records do not get inserted in the table. Job finishes successfully, in the log, i can see an entry that tells me n number of records were rejected.
Metadata in the job design and target table match. I can insert records into the table outside Datastage via oracle interface.
Can anyone suggest what can be the possible reason for record rejection?
p.s. sorry for a generic title of the post, could not think of anything better.
I have a job where i am inserting records in an oracle enterprise stage, the problem is that records do not get inserted in the table. Job finishes successfully, in the log, i can see an entry that tells me n number of records were rejected.
Metadata in the job design and target table match. I can insert records into the table outside Datastage via oracle interface.
Can anyone suggest what can be the possible reason for record rejection?
p.s. sorry for a generic title of the post, could not think of anything better.
Tanay
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Oracle stage does an upsert with user defined update and insert, apart from varchar, there are three other data types, timestamp, decimal, char.
I captured the rejects using a peek stage and then using these values, successfully inserted a row via oracle interface. The values for all the columns are as expected. Most of the fields with datatype as timestamp are populated by using datastage function currentTimestamp and one field with datatype as timestamp which is derived from a field from another table, i am using to_date function to convert it into the desired format.
and also I suspected a particular column to play truant (datatype - decimal) - i am using a cast function to explicitly match the correspoding column definition in target table.
Note - Column definitions were imported using orchestrate schema definitions.
I captured the rejects using a peek stage and then using these values, successfully inserted a row via oracle interface. The values for all the columns are as expected. Most of the fields with datatype as timestamp are populated by using datastage function currentTimestamp and one field with datatype as timestamp which is derived from a field from another table, i am using to_date function to convert it into the desired format.
and also I suspected a particular column to play truant (datatype - decimal) - i am using a cast function to explicitly match the correspoding column definition in target table.
Note - Column definitions were imported using orchestrate schema definitions.
Tanay
this is the insert sql as it occurs in the oracle stage,
INSERT
INTO schema.table
(
ACDNT_ID ,
ACDNT_STAT_ID ,
SUB_ID_CD ,
MBR_SFX_CD ,
ACDNT_DT ,
ACDNT_TRMNTN_DT ,
APLCTN_CREAT_USER_ID_CD,
APLCTN_UPDT_USER_ID_CD ,
REC_CREAT_DT ,
REC_CREAT_USER_ID_CD ,
REC_UPDT_DT ,
REC_UPDT_USER_ID_CD ,
RPLY_BY_DT ,
ACTV_IND ,
SEND_UPDT_IND ,
UPDT_SENT_DT ,
INCDNT_RPT_SEND_IND ,
INCDNT_RPT_SENT_DT ,
INCDNT_RPT_DOC_NUM_TXT ,
WRNG_MSG_SEND_IND ,
WRNG_MSG_SENT_DT ,
LOB_DETL_ID ,
DIAG_CD_CHGD_IND ,
EMP_IND ,
PURSUIT_STAT_ID ,
SBRGTN_RULE_ID
)
VALUES
(
9999999996 ,
5 ,
'qwerty' ,
'qwer' ,
systimestamp ,
to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS'),
'qwerty' ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'Y' ,
'Y' ,
systimestamp ,
'Y' ,
systimestamp ,
'qwerty' ,
'Y' ,
systimestamp ,
'OR' ,
'Y' ,
'Y' ,
3 ,
000000051986337.
);
and using this query i can insert records via oracle interface.
Yes, date fields in target table are being populated using timestamp data generated by datastage function.
INSERT
INTO schema.table
(
ACDNT_ID ,
ACDNT_STAT_ID ,
SUB_ID_CD ,
MBR_SFX_CD ,
ACDNT_DT ,
ACDNT_TRMNTN_DT ,
APLCTN_CREAT_USER_ID_CD,
APLCTN_UPDT_USER_ID_CD ,
REC_CREAT_DT ,
REC_CREAT_USER_ID_CD ,
REC_UPDT_DT ,
REC_UPDT_USER_ID_CD ,
RPLY_BY_DT ,
ACTV_IND ,
SEND_UPDT_IND ,
UPDT_SENT_DT ,
INCDNT_RPT_SEND_IND ,
INCDNT_RPT_SENT_DT ,
INCDNT_RPT_DOC_NUM_TXT ,
WRNG_MSG_SEND_IND ,
WRNG_MSG_SENT_DT ,
LOB_DETL_ID ,
DIAG_CD_CHGD_IND ,
EMP_IND ,
PURSUIT_STAT_ID ,
SBRGTN_RULE_ID
)
VALUES
(
9999999996 ,
5 ,
'qwerty' ,
'qwer' ,
systimestamp ,
to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS'),
'qwerty' ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'Y' ,
'Y' ,
systimestamp ,
'Y' ,
systimestamp ,
'qwerty' ,
'Y' ,
systimestamp ,
'OR' ,
'Y' ,
'Y' ,
3 ,
000000051986337.
);
and using this query i can insert records via oracle interface.
Yes, date fields in target table are being populated using timestamp data generated by datastage function.
Tanay
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Oracle reject handling is better in version 8.1 of DataStage where you can migrate to the Oracle Connector instead of Oracle Enterprise. More flexible and robust handling of different types of Oracle rejects.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn