SQL_DATA_ACCESS set to 'M' in SYSROUTINES even though only selects

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark A - 24 Jul 2006 19:15 GMT

DB2 for LUW fixpack 11.

If I create a SP with only a select (no insert, updates, or deletes), and do
NOT use MODIFIES SQL DATA clause in the SP, the column SQL_DATA_ACCESS in
SYSCAT.ROUTINES is set to 'M';

C = CONTAINS SQL: only SQL that does not read or modify SQL data is allowed
M = MODIFIES SQL DATA: all SQL allowed in routines is allowed.
N = NO SQL: SQL is not allowed.
R = READS SQL DATA: only SQL that reads SQL data is allowed.

What is the performance hit of having SQL_DATA_ACCESS set to 'M' when there
are only selects in the SP?

Reply to this Message

Knut Stolze - 25 Jul 2006 10:04 GMT

> DB2 for LUW fixpack 11.
>
> If I create a SP with only a select (no insert, updates, or deletes), and
> do NOT use MODIFIES SQL DATA clause in the SP, the column SQL_DATA_ACCESS
> in SYSCAT.ROUTINES is set to 'M';

MODIFIES SQL DATA is the default if you don't specify anything else. Have a
look at the syntax diagram: http://tinyurl.com/zm653

> C = CONTAINS SQL: only SQL that does not read or modify SQL data is
> allowed M = MODIFIES SQL DATA: all SQL allowed in routines is allowed.
[quoted text clipped - 3 lines]
> What is the performance hit of having SQL_DATA_ACCESS set to 'M' when
> there are only selects in the SP?

There shouldn't be any impact on performance. However, you can't use
MODIFIES SQL DATA procedures in certain contexts. For example, you cannot
call it from a CONTAINS SQL procedure.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Mark A - 25 Jul 2006 21:23 GMT

> > DB2 for LUW fixpack 11.
> >
[quoted text clipped - 21 lines]
> DB2 Information Integration Development
> IBM Germany

Yes, that is what I said. MODIFIES SQL DATA is the default since
SQL_DATA_ACCESS in SYSCAT.ROUTINES is set to 'M' when the clause is not
included..

Here is a quote from page 63 of the "Application Development Guide:
Programming Server Applications, V8.2"

"By default SQL procedures are created with SQL access level MODIFIES
SQL DATA. This can be modified to a lower level of access such as READS
SQL DATA or CONTAINS SQL if no table data is modified by the SQL
statement within the procedure. This is done by specifying the
appropriate SQL access level clause in the CREATE statement of the
procedure. Optimal performance of routines is achieved when the most
restrictive SQL access clause that is valid is specified in the CREATE
statement."

To repeat my question (in light of the last sentence above), "What is
the performance hit of having SQL_DATA_ACCESS set to 'M' when there are
only selects in the SP?"

Reply to this Message

Serge Rielau - 25 Jul 2006 22:06 GMT

> To repeat my question (in light of the last sentence above), "What is
> the performance hit of having SQL_DATA_ACCESS set to 'M' when there are
> only selects in the SP?"

Normally zero.
However, if you call a procedure which is defined as READS SQL DATA
DETERMINISTIC NO EXTERNAL ACTION from a trigger DB2 may decide not to
re-execute the procedure if the arguments don't change.
Matter of fact DB2 decide to drop the entire CALL from the plan if it
figures the result isn't used.
Today, there is no such optimization for nested procedure calls, but it
may be added in the future, so it's worth being specific.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message