What is the DB2 equivalent of SQL Server's SET NOCOUNT ON?

Question

What is the DB2 equivalent of SQL Server's SET NOCOUNT ON?

"From the SQL Server documentation:

SET NOCOUNT ON... Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set...

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced."

my problem is if I update a row in a table, a trigger runs that update another row in a different table.

In Hibernate I get this error: "Batch update returned unexpected row count from update; actual row count: 2; expected: 1".

I think because of the trigger DB2 returns 2 instead of 1, what is correct. However, is there any way to make DB2 to return 1 without removing the trigger or can I disable the check in Hibernate? How to handle this issue? Can anyone plz tell "Set NoCount on"(sql server) equivalent in db2?

Answer 1

There is no equivalent to SET NOCOUNT in DB2 because DB2 does not produce any informational messages after a DML statement has completed successfully. Instead, the DB2 driver stores that type of information in a local, connection-specific data structure called the SQL communications area (SQLCA). It is up to the application (or whatever database framework or API the application is using) to decide which SQLCA variables to examine after executing each statement.

In your case, your application has delegated its database interaction to Hibernate, which compares the number of affected rows reported by DB2 in the SQLCA with the number of rows Hibernate expected its UPDATE statement to change. Since Hibernate isn't aware of the AFTER UPDATE trigger you created, it expects the update statement to affect only one row, but the SQLCA shows that two rows were updated (one by Hibernate's update statement, and one by the AFTER UPDATE trigger on that table), so Hibernate throws an exception to complain about the discrepancy.

This leaves you with two options:

1. Drop the trigger from that table and instead define an equivalent followup action in Hibernate. This is not an ideal solution if other applications that don't use Hibernate are also updating the table in question, but that's the sort of decision a team gets to make when they inflict Hibernate on a database.
2. Keep the AFTER UPDATE trigger where it is in DB2, and examine your options for defining Hibernate object mappings to determine if there's a way to at least temporarily disable Hibernate's row count verification logic. One approach that looks particularly encouraging is to specify the ResultCheckStyle.NONE option as part of a custom @SQLUpdate annotation.

For SQL Server and Sybase, there appears to be a third option: Hide the activity of an AFTER UPDATE trigger from Hibernate by activating SET NOCOUNT ON within the trigger. Unfortunately, there is no equivalent in DB2 (or Oracle, for that matter) that allows an application to selectively skip certain activities when tallying the number of affected rows.