Need workaround - No error codes being returned from DB2

[asorrell]

This is a problem with some Server jobs that are created on the EE version of DataStage (version 7.5.1.a) with DB2 8.2.2 on AIX.

What is happening is that we aren't getting any reject rows from the DB2 table inserts on AIX. We've also checked all of the error codes and they are coming back zero or blank. A quick check of the Ascential eServices knowledgebase informed me that this is a "known issue" - and at this point no workaround or fix is posted.

Has anyone encountered this before and figured out a workaround?

[ArndW]

Andy,

are you using the reject link functionality with an error link coming out of the DB/2 stage in your job? Also, what is triggering the rejects - just one error code/reason or several? I know I did some jobs that used this type of functionality and would be worried if nothing were reported since the design relies upon this feature.

[ray.wurlod]

Would you be prepared to try using an Enterprise ODBC stage, just to determine whether the symptom is database related or client software related?

[Jai_sahaj]

Would you be prepared to try using an Enterprise ODBC stage, just to determine whether the symptom is database related or client software related?

I think I have seen this problem. Try to set the commit level (or whatever it is called in DB2) to 1 and run the job again....

[asorrell]

Arnd -

I'm using a simple transform to move data from a seqential file to its equivalent in DB2. The reject box is checked. We've also tried several other different ways to get it to reject based on the SQL return codes that are mentioned as standard return codes from the interface. And yes - this worries me too - especially since it seems to be a "known" issue on something as critical as this. Everyone was assuming the loads were just fine - I was brought on last week and put in a test just make sure rejects were working and was dismayed to see nothing reporting back.

I'm currently sending it a block of good records with a single "bad" record (one with nulls in a no-null field) to get it to fail. It does not write the bad record (9,764 into transform -> 9763 into DB2 file), but it doesn't send anything down the reject link.

Jai_Sahaj -

The commit is currently set to 1000 - I will try setting it to one and see if it fails, but that will NOT make the DBA's happy.

Ray -

We are sort of under the gun for the next two days - we have an entire group of testers waiting on us to finish some loads (sounds familiar I'm sure) so we are already doing 18 hour days for a bit. Once that is done I'll setup an Enterprise ODBC stage to help isolate the issue.

[ArndW]

Andy,

sorry I couldn't help you on this. If you are doing loads, you might switch to the bulk load method and run it outside of DS and capture any errors from inside a script; that is the only workaround I can think of.

-Arnd.

[vmcburney]

So you are talking about a server job with the DB2 API stage right? Have you tried the same with a parallel job and the enterprise DB2 stage? It creates reject codes in a different way by propogating the SQL Code so it may be more reliable, I have found it handles larger commit sizes more effectively. You might be able to use server job containers in parallel jobs to load your DB2 data.

I love it when a post lets me say this: see my latest blog Are you suffering from row leakage? for the info and link to the DB2 Enterprise Stage reject row capture.

[asorrell]

Vincent,

I think I'll be spending some time over the weekend trying it out in PX. The Project Architect probably won't get a good night of sleep until we find a solution!

Thanks for the blog entry, it should be helpful when we try that route. I also forwarded your "Guide to Ignoring Data Quality" to the IBM project team here and they alternated between laughing and crying as they read the items (laughing because its funny, crying because it hits way too close to home!).

*EDIT* I was bone-tired and waiting for a job to finish unit test so I played around with the PX version a bit. I got the DB2 table to load using the DB2/API interface, but then realized that apparently there isn't any reject link when you use the API interface! So back to square one - no tracking of rejects.

Next I tried a DB2/Enterprise table and I can't seem to get it to connect. The options are slightly different from the API stage for the connection information, and not being conversant at all with DB2 settings (and very frazzled) I've decided to give it up until I can either get some help from here or from a DB2 analyst tomorrow on the "client instance name" setting, something that wasn't asked for (at least in that manner) when I used the API interface. I'm not quite sure what it is looking for...

Calling it a night for now - more later I'm sure - thanks!

[asorrell]

Well, you can tell I wasn't exactly hitting on all cylinders at midnight last night. I got up this morning and the first thing that hit me was "Doh - the Reject link on the DB2 API stage comes off the Transformer Stage! not the table". Idiot me (or just very, very tired me).

However, I just finished adding the Reject Link, and set the table Array Size and Commit to 1, and I'm STILL not getting any rejects down the link... Is there anything else I can try for the API? Any DB2 settings come to mind? When I run the job it shows 20 rows hitting the table, but a count shows 19 in the table (which it should since one test record is bad). I am getting warnings in the log for the file:

PRICE_CHG_FACT,0: Warning: DB2_PX_Load_API.PRICE_CHG_FACT: [IBM][CLI Driver][DB2/6000] SQL0530N The insert or update value of the FOREIGN KEY "EMDEV.PRICE_CHG_FACT.R_561" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
PRICE_CHG_FACT,0: Error: At row 10, link "Out_to_PRICE_CHG_FACT"
Row rejected.

I'm still having problems getting connected via the DB2 Enterprise stage. The DBA's tell me they think I'm answering everything correctly but it still won't connect - I still suspect that it has something to do with the "Client Instance Name" which is the main thing I'm not asked in the API interface. So far the folks I've talked to said they were never able to get connected with the DB2 Enterprise stage either. Any suggestions there would help as well. I'm going to try to setup the environment variables the documentation references as well, to see if that helps.

I'll keep you posted - keep those suggestions coming!

[asorrell]

One of the locals that used to work at The Gap informed me that they encountered the same problem there. He said that they found that the error codes being returned from the Enterprise stage were inconsistent at best. They ended up setting DB2 load jobs to fail on warnings and then started parsing logs to determine what happened.

Vincent - In reading some of your blogs, it looks like you got this to work consistently. What release were you on? The "Gap" developer says he thought it was a 7.5 issue that broke the error codes, because it worked until they upgraded.

*EDIT* - Oh - and we can't do bulk loads at this point. The DBA's won't allow us to ignore constraints, etc.

[vmcburney]

Bulk loads errors can be harder to find, you need to parse through the bulk load statistics file and find specific error codes. There is no differentiation in the bulk load log files between information messages and error messages, they look the same and have the same format. No bulk load errors turn up in the DataStage log.

I have found the reject codes from DB2 upserts to be okay. We are on 7.5.1A and recently upgraded to DB2 8. Also note that sometimes rows are dropped without warnings so fail on warnings is not reliable.

If you are using the DB2 Enterprise stage for the first time at that site make sure you can view data in all your source databases through the plugin, don't leave it until the weekend as you may need DB2 support to configure and get it working.

[asorrell]

Ok, here's the scoop, straight from Ascential:

In a PX Server job, you can get the error codes (hence the reject link) to work if you set the transaction size to 1 and the array size to 1. Basically this means that you have to perform a commit after every write. If you are transferring more than a small amount of records, this also means that performance is going to go right into the toilet.

SO

The alternate solution from Ascential is to try the DB2 Enterprise stage. However, everyone was telling me that they could never get it to work with remote (networked) DB2 files (which is what we have here). After some dialogue, the level 2 tech support from IBM supplied me with a 20-page guide "DataStage EE Remote DB2 Configuration Guide" that was hot off the presses (late January).

The reason for 20 pages? It is non-trivial to get the remote access working. It requires quite a bit of DB2, DS and Unix reconfiguration as well as a copy of DataStage on each DB2 node.

My customer does believe that this workaround will work, and the error codes and reject links will start working. However they can't make substantial config changes since they are already into testing / certification of the project. IBM Tech Support verified it worked with remote files and the customer will probably implement it in "Phase 2" of the warehouse.

If you want a copy of the guide, send me an email at andy@strategies2.com and I'll send it to you. I'd post it here but it's got an IBM legal disclaimer longer than my arm that indicates I'd probably lose parts of my anatomy if I did that.

[alexysflores]

[quote="asorrell"]This is a problem with some Server jobs that are created on the EE version of DataStage (version 7.5.1.a) with DB2 8.2.2 on AIX.

What is happening is that we aren't getting any reject rows from the DB2 table inserts on AIX. We've also checked all of the error codes and they are coming back zero or blank. A quick check of the Ascential eServices knowledgebase informed me that this is a "known issue" - and at this point no workaround or fix is posted.

Has anyone encountered this before and figured out a workaround?[/quote]

"Try using ODBC plug-ins in dealing with this issue, ODBC return more info about success and failures of DB2 activity'