The query:
DELETE FROM CONNECTION_USERS
WHERE CONNECTION_USERS.APPLICATION_ID = APPLICATION_ID()

causes a table scan to always happen in the unique indexed primary keyed
field "Application_ID"

But changing the query to:
DELETE FROM CONNECTION_USERS
WHERE CONNECTION_USERS.APPLICATION_ID = (
SELECT APPLICATION_ID() FROM sysibm.sysdummy1)

causes the proper index scan.


Query A takes 2.7 seconds.
Query B takes 10ms.

i'm sure there's a good reason why the optimizer is thrown into fits; but i
don't really care. Just be aware and fix it.

Is the function "APPLICATION_ID()" declared as "deterministic" ?
If not, DB2 needs to check *every* line of the "CONNECTION_USERS" Table
- and won't use the index.

Regards,
Udo

--
Speedgain for DB2 - The DB2 Monitor
http://www.itgain.de/en/index.html

Udo wrote:

> Is the function "APPLICATION_ID()" declared as "deterministic" ?
> If not, DB2 needs to check *every* line of the "CONNECTION_USERS" Table
> - and won't use the index.

No, it is not.

$ db2 "select deterministic from syscat.routines where routinename
= 'APPLICATION_ID'"

DETERMINISTIC
-------------
N


The reason is that the function is not deterministic - it does not return
the same result on each call. A different SQL session gets a different
application id.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Knut Stolze wrote:
> The reason is that the function is not deterministic - it does not return
> the same result on each call. A different SQL session gets a different
> application id.
It boils down to the fact that application_id() is deterministic within
a query, and even a session, but not constant as required to be used in
e.g. a check constraint.
Unfortunately there is only one level of granularity: DETERMINISTIC.
Adding finer control would allow for better optimizations, but also a
more difficult to use product.
The long term solution will be to provide a new class of "variables"
akin of special registers and make application id such a variable.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

> The long term solution will be to provide a new class of "variables" akin
> of special registers and make application id such a variable.

That will be a welcome improvement.

(and you can't believe how many iterations of that line i went through so it
didn't sound insulting.

"At least you're open to improvment."
"i'm grateful future developers will have that feature."
"It's a nice idea, but too late for me."

i was really trying to give a positive response; i had to ask a colleague
for help.

Ian Boyd wrote:
> i was really trying to give a positive response; i had to ask a colleague
> for help.
I know how hard that is for you. :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

> I know how hard that is for you. :-)

Oh no; i've acquired a reputation!

Wow, that's scary. i have no idea how my name got changed to "Amanda"


"Amanda" <newsjet2net@zunblvlda1.dyndns.org> wrote in message
news:enms3n0aj2@enews3.newsguy.com...
>> I know how hard that is for you. :-)
>
> Oh no; i've acquired a reputation!
>
>
>

> It boils down to the fact that application_id() is deterministic within a
> query, and even a session, but not constant as required to be used in e.g.
> a check constraint.
> Unfortunately there is only one level of granularity: DETERMINISTIC.
> Adding finer control would allow for better optimizations, but also a more
> difficult to use product.
> The long term solution will be to provide a new class of "variables" akin
> of special registers and make application id such a variable.


Might want to tell someone to update this technical article
http://www-128.ibm.com/developerwork...302stolze.html

Near the bottom in the audit log section, there is a trigger named auditT1.
Take a look at the SELECT query into the logins table filtered on
application_id()

> SELECT user_id FROM login L WHERE L.appl_id=application_id()

Ian Boyd wrote:

>> It boils down to the fact that application_id() is deterministic within a
>> query, and even a session, but not constant as required to be used in
>> e.g. a check constraint.
>> Unfortunately there is only one level of granularity: DETERMINISTIC.
>> Adding finer control would allow for better optimizations, but also a
>> more difficult to use product.
>> The long term solution will be to provide a new class of "variables" akin
>> of special registers and make application id such a variable.
>
>
> Might want to tell someone to update this technical article
>
http://www-128.ibm.com/developerwork...302stolze.html
>
> Near the bottom in the audit log section, there is a trigger named
> auditT1. Take a look at the SELECT query into the logins table filtered on
> application_id()
>
>> SELECT user_id FROM login L WHERE L.appl_id=application_id()

Actually, the function in the article is defined as DETERMINISTIC. The
article existed before DB2 incorporated its own APPLICATION_ID() function.
Therefore, the trigger works fine and would use an index if present (and
deemed as a good thing by the optimizer).

What we should update, however, is the description on the determinism of
that function...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany