Stored Procedure Options: Modifies SQL Data and Results Set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alex.mcshane@btinternet.com - 28 Dec 2005 13:30 GMT

Hi - I would be grateful for any knowledge regarding the following.

Whilst QAing Stored Procedures developed by colleagues, I noticed the
following 'superflous' parameter specifications:-

1) "Modifies SQL Data" - where the Stored Procedure is Select only.
In other words, the parameter should have been specified "Reads SQL
Data"

2) Result Sets 'n', where 'n' is > 0 - where the Stored Procedure does
not contain a Cursor.
In other words, the parameter should have been specified "Result Sets
0".

Clearly, the above incorrect parameter specifications still work.

However, can either of them have any adverse impact on Performance,
Concurrency or any other operational attribute?

Thanks

Reply to this Message

Phil Sherman - 28 Dec 2005 14:16 GMT

> Hi - I would be grateful for any knowledge regarding the following.
>
[quoted text clipped - 4 lines]
> In other words, the parameter should have been specified "Reads SQL
> Data"

You've told UDB that you will be changing table data but aren't. Not a
problem but UDB will allow the procedure to perform updates.

> 2) Result Sets 'n', where 'n' is > 0 - where the Stored Procedure does
> not contain a Cursor.
> In other words, the parameter should have been specified "Result Sets
> 0".

This is an estimate of how many result sets will be returned. There is
no requirement to actually have result sets. I'd expect resources to be
allocated to manage the result sets to avoid potential delays when
actually creating them. If you don't use them, then the resources should
be released when the procedure terminates.

Phil Sherman

> Clearly, the above incorrect parameter specifications still work.
>
> However, can either of them have any adverse impact on Performance,
> Concurrency or any other operational attribute?
>
> Thanks

Reply to this Message

Serge Rielau - 28 Dec 2005 15:45 GMT

>> Hi - I would be grateful for any knowledge regarding the following.
>>
[quoted text clipped - 7 lines]
> You've told UDB that you will be changing table data but aren't. Not a
> problem but UDB will allow the procedure to perform updates.

Any procedure created with MODIFIES SQL DATA is notr allowed to be
called in a BEFORE TRIGGER or a READS SQL DATA function.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message