APPLICATION_ID() causes optimizer fits

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Boyd - 03 Jan 2007 21:10 GMT

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.

Reply to this Message

Udo - 04 Jan 2007 09:37 GMT

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

Reply to this Message

Knut Stolze - 04 Jan 2007 09:49 GMT

> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Serge Rielau - 04 Jan 2007 13:26 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

Ian Boyd - 04 Jan 2007 14:20 GMT

> 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. :)

Reply to this Message

Serge Rielau - 04 Jan 2007 19:20 GMT

> 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. :-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

Amanda - 06 Jan 2007 00:56 GMT

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

Oh no; i've acquired a reputation! :)

Reply to this Message

Ian Boyd - 06 Jan 2007 04:19 GMT

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

>> I know how hard that is for you. :-)
>
> Oh no; i've acquired a reputation! :)

Reply to this Message

Ian Boyd - 11 Jan 2007 14:15 GMT

> 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.
[quoted text clipped - 4 lines]
> 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/developerworks/db2/library/techarticle/0302stolze/0302sto
lze.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()

Reply to this Message

Knut Stolze - 15 Jan 2007 19:42 GMT

>> 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
[quoted text clipped - 6 lines]
>
> Might want to tell someone to update this technical article

http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302sto
lze.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...

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message