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

alex.mcshane@btinternet.com wrote:
> 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"
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
>

Phil Sherman wrote:
>
>
> alex.mcshane@btinternet.com wrote:
>
>> 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"
>
> 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

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