xml records drop while loading into oracle target table

DSRajesh · Post by **DSRajesh** » Sat Jan 08, 2011 12:48 am

Dear Experts,

rejected Records are dropping while loading into reject table. but same datastage job is able to load in dev /test environment with same structure of Rejecetd table.

Please help me in resolving this.

DSRajesh · Post by **DSRajesh** » Sat Jan 08, 2011 1:37 am

any one to help please

ray.wurlod · Post by **ray.wurlod** » Sat Jan 08, 2011 3:44 pm

Learn patience. You waited less than an hour, and on a weekend. Would your official support provider have responded in under an hour on a weekend?

chulett · Post by **chulett** » Sat Jan 08, 2011 5:02 pm

Never mind the fact that there is an extreme lack of detail here so not sure how anyone could help straight away. Are you getting errors / warnings in your log that you can post? Can you describe your job design? Any other information you can provide that may help people help you would be appreciated.

DSRajesh · Post by **DSRajesh** » Sat Jan 08, 2011 10:14 pm

there are no warnings or errors in the log.

But reject link is showing the records and not inserting the records.

Reeject table properties:

upsert mode : user defined update only

write method: upsert

DSRajesh · Post by **DSRajesh** » Mon Jan 10, 2011 7:32 am

Looking farward for help!

chulett · Post by **chulett** » Mon Jan 10, 2011 8:17 am

More information, please. For example, what exactly is your "user defined update" sql? From what I recall, there should also be a final entry logged for the target stage in question here with details on records written, etc - can you post that as well?

DSRajesh · Post by **DSRajesh** » Mon Jan 10, 2011 7:11 pm

Update SQL Query:

INSERT INTO TB_REJECT (FILE_NAME, TABLE_NAME, SOURCE, ROW_NUM, RECORD, ERROR_DESC, DATA_DATETIME, CREATED_DATETIME, MODIFIED_DATETIME, CREATED_BY, MODIFIED_BY) VALUES (ORCHESTRATE.FILE_NAME, ORCHESTRATE.TABLE_NAME, ORCHESTRATE.SOURCE, ORCHESTRATE.ROW_NUM, ORCHESTRATE.RECORD, ORCHESTRATE.ERROR_DESC, ORCHESTRATE.DATA_DATETIME, ORCHESTRATE.CREATED_DATETIME, ORCHESTRATE.MODIFIED_DATETIME, ORCHESTRATE.CREATED_BY, ORCHESTRATE.MODIFIED_BY)

DSRajesh · Post by **DSRajesh** » Mon Jan 10, 2011 7:33 pm

the only the update sql query is using along with other two properties for write method and upsert mode

chulett · Post by **chulett** » Mon Jan 10, 2011 8:25 pm

Right, got that... what about the other piece of information I asked about? Were you able to locate that?

DSRajesh · Post by **DSRajesh** » Mon Jan 10, 2011 11:00 pm

below are the log information for rejected records:

orc_TB_REJECT_ins,1: Records inserted: 0
Records used for update: 0
Records rejected: 1
Total records: 1.

orc_TB_REJECT_ins,2: Records inserted: 0
Records used for update: 0
Records rejected: 0
Total records: 0.

orc_TB_REJECT_ins,3: Records inserted: 0
Records used for update: 0
Records rejected: 0
Total records: 0.

orc_TB_REJECT_ins,0: Records inserted: 0
Records used for update: 0
Records rejected: 0
Total records: 0.

it is showing 1 record is rejectedin the log,but in the reject table ,that record is not rejected/inserted in the recject table.

please help me here to trouble shoot this

ray.wurlod · Post by **ray.wurlod** » Mon Jan 10, 2011 11:09 pm

Please show us your job design. In particular, does the Oracle stage have a reject-handling link (painted with long dashes)?