DB2 connector stage not writing rejected data

nagikondaveti · Post by **nagikondaveti** » Tue Mar 04, 2014 2:25 pm

DB2 connector is connected to reject link and then to a dataset to store reject records. When a record is rejected (due to a Foreign Key relationship issue) by DB2 stage, it should be written to dataset. This is my expectation. But job is getting aborted without writing data to dataset. Please advise what I am missing to get rejected data. this is job design -

Input dataset --> Copy --> DB2 connector --> DataSet (to collect Rejects)

DS version - 8.7
DB2 version - 9.1
Write Mode - Insert

Please let me know if any additional information needed

ray.wurlod · Post by **ray.wurlod** » Tue Mar 04, 2014 7:56 pm

Show us the error message(s) associated with the abort. Also show us the settings in the DB2 Connector for the rejects link. In particular, what kinds of rejects are you capturing and which kinds are you ignoring?

nagikondaveti · Post by **nagikondaveti** » Tue Mar 04, 2014 8:30 pm

here are more details -

DB2 Connector : Db2_ServiceCode_Insert

VariantName: 9.1
VariantLibrary: ccdb2
VariantVersion: 1.0
SupportedVariants: 9.1
SupportedVariantsLibraries: ccdb2
SupportedVariantsVersions: 1.0
Orientation: link
RejectFromLink: -1
RejectThreshold: 1
RejectNumber: 91
RejectUsesPercentage: No
ConnectorName: DB2Connector
Engine: EE
Context: target
ConnectionString: /Connection/Database
Username: /Connection/Username
Password: ********
Instance: /Connection/Instance
DB2ClientLibFile: /Connection/DB2ClientLibFile
Conductor: /Connection/Conductor
ConductorInstance: /Connection/Conductor/Instance
ConductorDatabase: /Connection/Conductor/Database
ConductorUsername: /Connection/Conductor/Username
ConductorPassword: /Connection/Conductor/Password
ConductorDB2ClientLibFile: /Connection/Conductor/DB2ClientLibFile
Common/Context: Target
Common/Variant: 9.1
Common/Descriptor version: 1.0
Common/Partition type: Auto
!Common/RCP!: 0
Common/Reject/Reject error conditions/Reject error condition: SQL error,Row not updated.
Connection/Database: #IDSParameterSet.IDS_DSN#
Connection/User name: #IDSParameterSet.$env_IDS_TGT_USR#
Connection/Password: ********
Connection/Alternate conductor settings: No
Connection/Use direct connections: No
Connection/Keep conductor connection alive: Yes
Usage/Write mode: Insert new rows only
Usage/Generate SQL: Yes
Usage/Table name: #IDSParameterSet.IDS_SCHEMA#.SERVICE
Usage/Enable quoted identifiers: No
Usage/Prefix for expression columns: EXPR
Usage/Table action: Append
Usage/Transaction/Record count: 2000
Usage/Session/Isolation level: Cursor stability
Usage/Session/Auto commit mode: Off
Usage/Session/Array size: 2
Usage/Session/Schema reconciliation/Fail on size mismatch: No
Usage/Session/Schema reconciliation/Fail on type mismatch: No
Usage/Session/Schema reconciliation/Drop unmatched fields: Yes
Usage/Logging/Log column values on first row error: No
Usage/Before/After SQL: No
Usage/Reoptimization: None
Usage/Lock wait mode: Use the lock timeout database configuration parameter
RejectConditions: SQL error Row not updated.
XMLRejectSettings: <Reject><RejectErrorConditions><RejectErrorCondition type='int'>0</RejectErrorCondition><RejectErrorCondition type='int'>1</RejectErrorCondition></RejectErrorConditions><RejectDataElements></RejectDataElements></Reject>

I'm trying to capture any reject record because of any SQL errors like Foreign key issues or Table not available or any kind of RDBMS related rejects.

Please let me know in case of any additional details required.

atul9806 · Post by **atul9806** » Wed Mar 05, 2014 12:01 am

Why you want to collect rejects in DataSet ? they have no use as need to dump them again to seq file to see them.
Better, follow this approch....

your design.........target db ---> Copy stage ---> seq file ( reject )

Using copy stage, u can collect sqlcode also.
hope this help.

nagikondaveti · Post by **nagikondaveti** » Wed Mar 05, 2014 4:57 am

I am writing these records to dataset because another job is going to handle all these rejectes based on SQL error. Is there any difference in writing data to sequential file and dataset? I think both are files and reject rows should be written to it.

any thoughts?

atul9806 · Post by **atul9806** » Wed Mar 05, 2014 6:40 am

try to use copy stage between db and dataset

nagikondaveti · Post by **nagikondaveti** » Wed Mar 05, 2014 1:55 pm

As you said, I tried with copy stage and then DataSet it worked fine per my expectation. I developed another job without copy stage with no change in settings, job is still writing rejects without copy stage. Not sure why rejects are not written before I post it here.

Anyway, I am closing this topic as "resolved" and let you know if I notice anything different in future

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Wed Mar 05, 2014 4:29 pm

One big difference - you can't write NULL values to a sequential file. If the records contained NULL's they'd all bounce. They have to be handled explicitly prior to writing them to sequential files.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2014 6:09 pm

asorrell wrote:One big difference - you can't write NULL values to a sequential file. If the records contained NULL's they'd all bounce. They have to be handled explicitly prior to writing them to sequential files.

... or within the Sequential File Stage using Null Field Value properties, for example.