SQLSTATE

[bakul]

Hi,

I am using a DB2/UDB stage for lookup. I need to access the SQLSTATE and DBMSCODE variables in the transformer.
However,even when a match is not found in the lookup, the SQLSTATE and DBMSCODE fields in the transformer are blank. How do I get the exact sqlstate in the transformer?

Code: Select all

        
       db2/udb 
         |
         | <lookup>
         |
 -----> Transformer --> Output 

Thanks and regards,
Bakul

[ray.wurlod]

Use link variables on the Output link. However these are not available until the Output link has executed its "Put" operation, so you'll need a second output link (let's say called Output2) that is executed after your Output link.

Code: Select all

      db2/udb 
         | 
         | <lookup> 
         | 
 -----> Transformer --> Output 
         |
         |
         V
       Output2

On Output2 define a column called SQLState (or Fred - it's only a name). Its derivation is the link variable Output.SQLSTATE
Similarly you can define columns derived via Output.DBMSCODE and Output.LASTERR to capture all of the information that is returned.

[bakul]

Thanks Ray!
But I have a problem. I want the DBMSCODE or SQLCODE which indicates the error to be carried forward on the same output link as the remaining data and so cannot use a second output link Output2. Any suggestions on how I could carry forward the DBMSCODE/SQLCODE on the same link as the data ?


Thanks and Regards,
Bakul

[ray.wurlod]

Not possible. Think about it. The SQLSTATE, DBMSCODE and LASTERR are generated as a result of failure of the "put" function. They're not available until AFTER the row has been sent, since they stem from the success or otherwise of sending the row.

[bakul]

Thanks Ray!
I guess I'll have to try and work around it!

Thanks and Regards,
Bakul

[bakul]

I had another doubt. If LASTERR/DBMSCODE/SQLSTATE variables aren't available till the PUT operation on the output link is over,what constraint should I use to send rows with errors on link OUTPUT2 and the correct rows on OUTPUT link?
Note: The output stage is not a static stage in both cases.

Thanks & Regards,
Bakul

[chulett]

I think Ray may have missed the point that you want to capture the 'errors' from when the DB2 lookup 'fails' - i.e. it doesn't return a record. He is answering based on the failure of the write of the Output link, not the lookup.

I think you've already gotten your answer in your first post - I don't believe they are available when doing a lookup. Nothing has failed, per se, it is just that your query has returned zero records.

[bakul]

Yep..that seems to be it!

But now I have a problem with insert/update.
The structure is something like this,

Code: Select all

      Input ------> Tfm1 ------> DB2/UDB
                     |
                     | 
                     |
                 XMLOutput 

None of the links here are lookup links.
The link ordering is such that, the insert/update to DB2 happens first and then the data is passed on to XMLOutput.
When passing data from Tfm1 to XMLOutput, I check the SQLSTATE or DBMSCode to generate appropriate status message and the variables are still blank. Shouldn't it contain valid values now that the PUT operation is over?
Is there any other way I can do this?

Thanks and regards,
Bakul

[ray.wurlod]

Selecting zero rows is not a failure as far as the database is concerned, so DBMSCODE will be 0, SQLState will be empty and LASTERR will be empty. There's no point in returning them.

SQLSTATE is specific to ODBC protocol; does the DB2/UDB stage actually return these? (You should check with a dummy job - a copy of your existing one with an additional output to a Sequential File and sending anything else you want but also link variables DB2Link.DBMSCODE, DB2Link.SQLSTATE and DB2Link.LASTERR). Make sure you test with some rows that would fail to be loaded into the DB2 table.

Note, too, that these link variables can not tell you whether a row was inserted or updated; if you're using a hybrid rule (such as upsert) one is tried then the other and if either was successful, the "Put" function returns "success" and the link variables are not set.

[bakul]

I tried writing DBMSCODE, SQLSTATE and REJECTEDCODE to a sequential file.
I had given 3 records as input, each of which had a required column missing. I also tried giving a NULL value to a NON-NULL field.

In the output sequential file the DBMSCODE and SQLSTATE are blank. The REJECTEDCODE shows a value of -100.

I changed from the DB2/UDB stage to an ODBC stage and repeated the above exercise again, but I got the same output,except that now the REJECTEDCODE is 17. Shouldn't I now get the DBMSCODE and SQLSTATE too?

Code: Select all

 Input ------> Tfm1 ------> ODBC
                |   \
                |     \
                |     Seq File  
             XMLOutput 

Thanks and regards,
Bakul

[ray.wurlod]

What's reported in the job log when you run this version? If you don't get warnings in the log, then for whatever reason the rows were accepted!
Make sure that the output link execution order is correct; that the link variable values are captured after the link has been executed.