Message_handler_Issue

somu_june · Post by **somu_june** » Wed Aug 08, 2007 3:23 pm

Hi,

I created a message handler to suppress all the rows that fail due to RI . Iam doing an insert in to a Db2 table using Db2/API stage . I am suppressing the warning SQL0530N the insert or update value of the FK is not equal to any value of parent key of the parent table SQLSTATE =23503, Datastage is providing me a messageID = TODC000085 , it is ok untill here . But my problem is it is also suppressing other Db2 warning messages . For example I have another warning message CL10112E Error in assignment . SQLSTATE = 22005 , but it is not showing nay warning messages .

My question is . Datastage will generate only one messageid for all the Db2 related warnings or it will generate different ids , How to catch the other Db2 related warnings other than RI warnings .

Thanks,
SomaRaju.

Gazelle · Post by **Gazelle** » Tue Dec 16, 2008 3:37 pm

I've got a similar problem, using PX v8.0.1

Some jobs get the DB2 message:
SQL0513W The SQL statement will modify an entire table or view. SQLSTATE=01504
When adding this to the Message Handler, it has the Message-ID IIS-XXXX-XXXX-00000
However, this same IIS message-id is used for a long list of other DB2 warning messages.

Did you ever get a workaround for this, somu_june?
Or has anyone else got any ideas, on how to make the IIS-XXXX-XXXX-00000 message-id rule only filter out specific DB2 messages?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 16, 2008 7:00 pm

You can create a named message handler to handle as many message IDs as you like.

Be very careful, though. Suppressing messages may lead to vital information being lost. Prefer to demote them to informational.

Gazelle · Post by **Gazelle** » Wed Dec 17, 2008 6:22 am

Thanks for the quick response, Ray.
But I still do not understand how to fix the problem.

I understand that multiple rules/message_id's can be added to a message-handler.
I understand the dangers of suppressing error messages.

The problem is that there are many different DB2 warning messages that share the same message_id: IIS-XXXX-XXXX-00000.
When we demoted the message
SQL0513W The SQL statement will modify an entire table or view. SQLSTATE=01504
we also inadvertently demoted a lot of other DB2 messages.

Do you know of a way to make the message-id more specific?

e.g. Does the structure of the message-id include the sqlcode or sqlstate, so that we can use a specific code instead of the generic IIS-XXXX-XXXX-00000 that seems to be the default?

[Edit: Note that the message-id is as posted, with the X's and the 0's.]

ray.wurlod · Post by **ray.wurlod** » Wed Dec 17, 2008 3:41 pm

You can not demote/suppress based on anything but message ID.

Therefore it looks like you are going to need to address the cause of the warnings; check for RI constraint violations before attempting to load data.