How to capture the rows rejected by DB2 API Stage.

somu_june · Post by **somu_june** » Wed Apr 19, 2006 9:18 pm

Hi,

I read in documentation in DB2/API stage plugin . The rows that are rejected due to unique constraint Violations or Data type mismatches can be captured by setting the Array size to 1 and I made the reject link in transformer and sending to sequential file . But no records are sending to Reject link i.e to sequential file if there is a unique constraint error. In my job Iam using two DB2 API stages one for update and one for insert and then to reject link . Will this work if have two DB2 /API stage. Can any one tell me how to capture rejected rows by DB2/API stage .

Thanks,
Somaraju.

kumar_s · Post by **kumar_s** » Thu Apr 20, 2006 1:59 am

Have you tried the Other wise option?
Try the reject link on the same API stage.

somu_june · Post by **somu_june** » Thu Apr 20, 2006 8:08 am

Hi Kumar,

Please correct me if Iam wrong . Otherwise option is only if the constraint of other link is not satisfied then it goes to this link . I want to catch the records that are failed to insert in to the DB2 table due to any error like DB2 errors and errors that I mentioned above.

Thanks,
Somaraju

kumar_s · Post by **kumar_s** » Thu Apr 20, 2006 8:23 am

Iam too not sure about the change in Array size option in DB2 stage and the reject in a transformer prior to that stage. Have you tried the reject link directly from the DB2 stage?

somu_june · Post by **somu_june** » Thu Apr 20, 2006 8:51 am

Hi kumar_s,

Iam using DB2 /API stage and I couldnot find this option reject link directly from the DB2 stage?[/quote]. I think option available for DB2 enterprise stage and not for DB2 /API stage.

Thanks,
Somaraju.

vmcburney · Post by **vmcburney** » Thu Apr 20, 2006 2:43 pm

No reason to use the API stage in parallel jobs, you should be using the enterprise stage instead.

koolnitz · Post by **koolnitz** » Thu Apr 20, 2006 8:51 pm

Vincent,

There can be reasons - like in our case, we have our target DB non-partitioned (it's DB2). We cannot use DB2 EE stage unless the database it is pointing to is partitioned. And that's what has been told by IBM support.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 20, 2006 9:11 pm

IBM India or worldwide support?

koolnitz · Post by **koolnitz** » Fri Apr 21, 2006 12:35 am

IBM support, North America

ray.wurlod · Post by **ray.wurlod** » Fri Apr 21, 2006 12:39 am

I've learned something today.

bmadhav · Post by **bmadhav** » Fri Apr 21, 2006 2:19 pm

we have the same experience. we cannot use the DB2 EE stage, since the DB2 server on the mainframe is not DB2 enterprise edition.
we are forced to use DB2 API stage.

surojeet · Post by **surojeet** » Sun Apr 30, 2006 2:14 pm

Hi,

I too had some horrible experience with DB2 API stage. I work with DS ver 7x on Unix with DB2 on Mainframe as back end, and thats why forced to use DB2 API stage, but to my disappointment its really slow. Especially at inserting records. Its hardly 500-600 rows per second, which takes hours to complete a job handling 4-5 million records.

I want to get a general conception, is DB2 API stage really that slow ? or my particular environment is sluggish ?

Also I would ask Mr. Somaraju, finally how did he handled the Reject records issue in DB2 API stage. Please share , it will help me a lot.

Regards,
Surojeet

bmadhav · Post by **bmadhav** » Sun Apr 30, 2006 5:47 pm

surojeet

DB2 API stage is a sequential stage and based on the network bandwith u have betn the mainframe and AIX server, it will further slow it down.
For inserting large volumes of data, it will be faster to FTP the sequential files to the mainframe and load it thru a DB2 load utility, which slams the data into the target table.
we use this process and get to load 2-3 million rows of data in less than 30 minutes.

DSXchange

How to capture the rows rejected by DB2 API Stage.

How to capture the rows rejected by DB2 API Stage.

DB2API Stage really sluggish