AS/400 SQL optimizer issue?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bart Kummel - 20 Feb 2008 12:58 GMT

Hi all,

I noticed some strange behavior when executing a SQL query on our AS/
400 (V5R3) DB2 database. The query is like this:

WITH
R AS (SELECT t1.ID, t2.ID
FROM SCHEMA.TABLE1 t1, SCHEMA.TABLE2 t2
WHERE t1.ID = t2.T1ID
ORDER BY t1.ID DESC, t2.ID ASC ),
Q AS (SELECT counter() cntr, R.*
FROM R)
SELECT *
FROM Q
WHERE Q.cntr > 0

counter() is a stored procedure, identical to the counter() example in
the IBM documentation on stored procedure. We use it because there is
now row_number() function in V5R3. I'd expect every row in the result
to have a unique integer number, starting from 1. This is true when I
leave out the "WHERE Q.cntr > 0". But when I add that, counting starts
at a higher number, and the counter is only incremented per TABLE1
record. Perhaps an example is more clear. I expect this:

CNTR T1_ID T2_ID
---- ----- -----
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1
7 3 2
8 3 3 etc...

But I get this:

CNTR T1_ID T2_ID
---- ----- -----
9 1 1
9 1 2
9 1 3
10 2 1
10 2 2
11 3 1
11 3 2
11 3 3 etc...

I don't understand what's going on. I did not expect the results to be
different when adding the "WHERE Q.cntr > 0" clause. Perhaps something
with the SQL optimizer? Off course I'd appreciate it if someone can
suggest how to get the behavior I was expecting.

Best regards,
Bart Kummel

Reply to this Message

Serge Rielau - 20 Feb 2008 14:17 GMT

> Hi all,
>
[quoted text clipped - 48 lines]
> with the SQL optimizer? Off course I'd appreciate it if someone can
> suggest how to get the behavior I was expecting.

DB2 pushes the predicate down as close to the table as possible.
Now, under some circumstances that is not proper. One of them being if
the function is defined as EXTERNAL ACTION. Thsi is what you have here.
One execution of the function impacts the results it will return for
other rows...
So mark the function as EXTERNAL ACTION/SIDEEFFECTS and see if that does
the job.
If it doesn't open a PMR.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Bart Kummel - 20 Feb 2008 14:58 GMT

> > Hi all,
>
[quoted text clipped - 65 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Hi Serge,

Thanks for your quick reply. I dropped the function and recreated it
as follows:

CREATE FUNCTION COUNTER()
RETURNS INT
SCRATCHPAD
NOT DETERMINISTIC
NO SQL
EXTERNAL ACTION
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'WEBDTADEV/MATH(ctr)'
DISALLOW PARALLEL

Before it was indeed defined as "NO EXTERNAL ACTION". I don't see any
difference in te result. I also tried SIDEEFFECTS, but DB2 doesn't
seem to understand that word. If SIDEEFFECTS should be recognised, can
you please explain how to use it? (Perhaps you can give a complete
CREATE statement?)

Best regards,
Bart Kummel

Reply to this Message

Serge Rielau - 20 Feb 2008 15:56 GMT

>>> Hi all,
>>> I noticed some strange behavior when executing a SQL query on our AS/
[quoted text clipped - 79 lines]
> you please explain how to use it? (Perhaps you can give a complete
> CREATE statement?)

Your function definition looks sound. SIDEEFFECTS is a synonym for
EXTERNAL ACTION. I think you have done what you can here.
Next step is support.
One of the problems is that the SQL Standard does not understand the
concept of external action. So it doesn't define semantics.
Within the DB2 family we do lean towards preserving external actions,
with sometimes detrimental effects on performance.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Karl Hanson - 21 Feb 2008 03:06 GMT

> Your function definition looks sound. SIDEEFFECTS is a synonym for
> EXTERNAL ACTION. I think you have done what you can here.
[quoted text clipped - 6 lines]
> Cheers
> Serge

While investigating the problem via service/support, a possible
circumvention is to unbundle the common table expressions and use temp
tables, something like:

declare global temporary table r as
( SELECT t1.ID, t2.ID
FROM TABLE1 t1, TABLE2 t2
WHERE t1.ID = t2.T1ID
ORDER BY t1.ID DESC, t2.ID ASC ) with data
declare global temporary table q as
( SELECT counter() cntr, R.*
FROM SESSION.r R ) with data
SELECT *
FROM SESSION.q Q
WHERE Q.cntr > 0

--
Karl Hanson

Reply to this Message

Bart Kummel - 21 Feb 2008 08:30 GMT

> > Your function definition looks sound. SIDEEFFECTS is a synonym for
> > EXTERNAL ACTION. I think you have done what you can here.
[quoted text clipped - 25 lines]
> --
> Karl Hanson

@Karl: thanks for you suggestion. It works, but it's unfortunately not
what we're looking for. Perhaps it's a good idea to tell a bit more
about what we're doing. We're building a Java EE application on top of
an existing database on the AS/400 / iSeries system. We need some sort
of pagination mechanism, because our dataset s are really large. The
ORM framework we use (JPA / EclipseLink) does have pagination, but it
does not know how to paginate for DB2 databases. This means that our
Java programs can set pagination parameters ("Only return records
starting from record 30 and return no more than 15 records.") The
framework will return the requested records, but under the hood
fetches ALL records from the database. Of course, this is very bad for
the performance. So what I'm trying to do is extend that framework so
that it knows how to get just the records it needs from the database.
Since the application server uses connection pooling, we don't have a
guarantee that a certain connection is only to be used to serve a
given user. So we can't rely on temporary tables stored on a session.
Re-creating the temporary tables for every request is also not an
option, since this it takes the AS/400 several seconds to create the
temporary tables...

@Serge: Does "next step is support" mean that you suspect this to be a
bug? If so, is there any chance it can be fixed on V5R3, or will the
advise be to upgrade to V5R4 or something like that? The thing is,
upgrading is not an option for us (at least at the moment). The reason
we're building a Java EE application is that we have to replace an
application that's written in Delphi. And that's because as of V5R4
that Delphi application won't work anymore. Given the size of the
application, there's no chance we can do a "big bang" scenario,
switching from the Delphi app. to the Java app. overnight. So there
will be a period of several months (or perhaps even years) where both
application will be used...

@all: Thanks for your help so far. I'll keep you posted when I make
progress with this. Please let me know if anyone has a smart
suggestion for a work around...

Best regards,
Bart Kummel

Reply to this Message

Bart Kummel - 26 Feb 2008 07:28 GMT

> > > Your function definition looks sound. SIDEEFFECTS is a synonym for
> > > EXTERNAL ACTION. I think you have done what you can here.
[quoted text clipped - 64 lines]
> Best regards,
> Bart Kummel

Hi all,

I contacted IBM support last week. The problem has to do something
with the query engine. I'm not really into AS/400 stuff, but it turns
out that the CQE engine is used because we have indexes (logicals in
AS/400 terms) on our tables. IBM support was not able to reproduce our
problem on the latest PTF level of V5R3 and we're somewhat behind on
PTF level. So our administrator has ordered a CD with the latest
cumulative patches. When our machine is on the latest patch level
we'll see if this solves the problem. (In the meantime, we can remove
the indexes/logicals temporarily.) Thank you all for you support!

Best regards,
Bart Kummel

Reply to this Message