How can I force an error on a negative DB2 Return Code?

ray.wurlod · Post by **ray.wurlod** » Wed Jul 28, 2010 6:04 pm

If you run the reject link from the Transformer stage (on which you can capture DBMS error code using output link variable) you could use an output row limiter set to 1. That would cause the job to abort. It's not really an approach I advocate, but if that's what you want to do...

Mikey · Post by **Mikey** » Thu Jul 29, 2010 10:49 am

That solution is an option, but one that would require a Transformer Stage after every ODBC (DB2 select) Stage. This would be a lot of rework on our existing jobs, but I do appreciate your insight.

I need to confirm this, but I believe a SQL -911 on our ODBC connection to our DB2 mainframe issues a fatal error, but issues a warning error on our DB2 UDB database. This leads me to believe that some parameter setting can be set to always force a fatal error when a negative SQL return code is encountered. Don't know where to look for that parameter...

ray.wurlod · Post by **ray.wurlod** » Thu Jul 29, 2010 3:13 pm

No, it requires a Transformer stage before each ODBC stage. I imagine that you quite often have one of these, to drive the link into the ODBC stage.

Mikey · Post by **Mikey** » Thu Jul 29, 2010 3:49 pm

The ODBC stages are typically the first stage in our jobs; these stages are selecting data from DB2 for the remaining stages to manipulate, etc. Adding a Transformer before each of these stages to handle a negative SQL return code seems like a lot of work.

chulett · Post by **chulett** » Thu Jul 29, 2010 6:03 pm

That wasn't what was being suggested, the assumption it was made under was that the ODBC stages were a target in your job. I assume.

Mikey · Post by **Mikey** » Thu Jul 29, 2010 7:35 pm

Any other suggestions?

I can't believe this is an issue in the first place...why would DataStage treat some negative return codes as fatal and others as a warning? This should have been my original question posted.

kris007 · Post by **kris007** » Thu Jul 29, 2010 9:51 pm

In version 7.x, we can demote warning messages to Informational messages. Not sure if there is an option in version 8.x to add rules to the message handler in the director to promote the warning messages as a Fatal error message.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 29, 2010 10:31 pm

There isn't, in any version, a means to promote message severity. What you could do is to create an after-job subroutine to check the log for this particular message and generate a fatal error at that point, though any data processed by the job will remain processed.

chulett · Post by **chulett** » Fri Jul 30, 2010 6:06 am

kris007 wrote:In version 7.x, we can demote warning messages to Informational messages. Not sure if there is an option in version 8.x to add rules to the message handler in the director to promote the warning messages as a Fatal error message.

Not for Server jobs.

eostic · Post by **eostic** » Fri Jul 30, 2010 7:08 am

Just guessing, but since this is Server, but here's a suggesetion that might work "IF" the -911 is something that can be detected on an "input" action (update, insert, or custom SQL from an input link).

Use Ray's design suggestions above in a bogus first stage and link.....Create some sort of dummy flat file with one record that you read at the start of the Job and pass thru a transformer on the way to your ODBC Stage....then from the Transformer to the ODBC stage, create two links.......one that performs some sort of SQL that will hopefully generate the error in concern, and he other to "react" to that error.....

As Ray and others noted, inside the second link, you can test on the return code of the first link "after" that link fires [check out the link variables that are available to you in the Derivation]........if it can generate the condition you are looking for, you will be able to test it and then react in this other link and do some other processing such as abort the job, write out a file somewhere with a message, etc.

The "true" output link of your ODBC stage will not start until all the processing of the input links is complete.

Ernie

Mikey · Post by **Mikey** » Fri Jul 30, 2010 9:36 am

I appreciate the creative work-around solution, but it does not answer my question, "why would DataStage treat some negative SQL return codes as fatal and others as a warning?"

chulett · Post by **chulett** » Fri Jul 30, 2010 9:38 am

You have to go back to the vendor for that answer, I'm afraid.

eostic · Post by **eostic** » Fri Jul 30, 2010 9:56 am

Yeah..that one's a tough call. Hard to say...too many layers along the way.... Have you tried the DB2 API Stage under the exact same circumstances? Perhaps it reacts differently.

Ernie