By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,159 Members | 1,880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,159 IT Pros & Developers. It's quick & easy.

Tips and tricks for fast SQL procedures

P: n/a
Shameless self promotion ;-)
http://www-106.ibm.com/developerwork...dm-0501rielau/

I've now reached the end of my "mini-series" around SQL Procedures.
If anyone has ideas on SQL related topics that need illuination I'm all
ears.
MERGE has come up a couple times so far....
Anything else?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...
Shameless self promotion ;-)
http://www-106.ibm.com/developerwork...dm-0501rielau/
I've now reached the end of my "mini-series" around SQL Procedures.
If anyone has ideas on SQL related topics that need illuination I'm all
ears.
MERGE has come up a couple times so far....
Anything else?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Hi Serge,

GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses
what is the best to do in DB2 to treat Transact SQL like (Sybase ...)

select type, title_id, avg(price), avg(advance) from titles group by type
select type, title_id from titles group by type having date=max(date)

Regards,

JM


Nov 12 '05 #2

P: n/a
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...
Shameless self promotion ;-)

http://www-106.ibm.com/developerwork...dm-0501rielau/
I've now reached the end of my "mini-series" around SQL Procedures.
If anyone has ideas on SQL related topics that need illuination I'm all
ears.
MERGE has come up a couple times so far....
Anything else?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge,

GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses

With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)
what is the best to do in DB2 to treat Transact SQL like (Sybase ...) Not clear what you mean?
select type, title_id, avg(price), avg(advance) from titles group by type
select type, title_id from titles group by type having date=max(date)

Neither here.... other than that you will get an error on title_id...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...
Shameless self promotion ;-)

http://www-106.ibm.com/developerwork...dm-0501rielau/
I've now reached the end of my "mini-series" around SQL Procedures.
If anyone has ideas on SQL related topics that need illuination I'm all
ears.
MERGE has come up a couple times so far....
Anything else?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge,

GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses

With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)

==> With respect to GROUP BY/HAVING ;-)
what is the best to do in DB2 to treat Transact SQL like (Sybase ...) Not clear what you mean?

==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is the
best way to translate into DB2 ?

select type, title_id, avg(price), avg(advance) from titles group by type select type, title_id from titles group by type having date=max(date)

Neither here.... other than that you will get an error on title_id...

==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and says
title_id is missing.
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4

P: n/a
Thanks,

Jean-Marc

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:41**********************@news.wanadoo.fr...
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:35*************@individual.net...

>Shameless self promotion ;-)
>

http://www-106.ibm.com/developerwork...dm-0501rielau/

>I've now reached the end of my "mini-series" around SQL Procedures.
>If anyone has ideas on SQL related topics that need illuination I'm all>ears.
>MERGE has come up a couple times so far....
>Anything else?
>
>Cheers
>Serge
>--
>Serge Rielau
>DB2 SQL Compiler Development
>IBM Toronto Lab
Hi Serge,

GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses

With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)

==> With respect to GROUP BY/HAVING ;-)
what is the best to do in DB2 to treat Transact SQL like (Sybase ...)

Not clear what you mean?

==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is the
best way to translate into DB2 ?

select type, title_id, avg(price), avg(advance) from titles group by type select type, title_id from titles group by type having date=max(date)

Neither here.... other than that you will get an error on title_id...

==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and

says title_id is missing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Nov 12 '05 #5

P: n/a
Jean-Marc Blaise wrote:
GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses


With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)


==> With respect to GROUP BY/HAVING ;-)
what is the best to do in DB2 to treat Transact SQL like (Sybase ...)


Not clear what you mean?


==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is the
best way to translate into DB2 ?
select type, title_id, avg(price), avg(advance) from titles group by
type
select type, title_id from titles group by type having date=max(date)


Neither here.... other than that you will get an error on title_id...


==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and says
title_id is missing.

OK, I'm hopelessly lost. Can you explain what you expect title_id to
produce in this case?
DB2 supports GROUP BY and HAVING so I'm simply drawing a complete blank
here. The only request I ever came across is to allow column numbers in
GROUP BY (as supported in ORDER BY), that's an IDS thing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:36*************@individual.net...
Jean-Marc Blaise wrote:
GROUP BY ... HAVING ?

SQL92 clauses
SQL99 clauses

With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)


==> With respect to GROUP BY/HAVING ;-)
what is the best to do in DB2 to treat Transact SQL like (Sybase ...)

Not clear what you mean?


==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is the best way to translate into DB2 ?
select type, title_id, avg(price), avg(advance) from titles group by


type
select type, title_id from titles group by type having date=max(date)

Neither here.... other than that you will get an error on title_id...


==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and says title_id is missing.

OK, I'm hopelessly lost. Can you explain what you expect title_id to
produce in this case?
DB2 supports GROUP BY and HAVING so I'm simply drawing a complete blank
here. The only request I ever came across is to allow column numbers in
GROUP BY (as supported in ORDER BY), that's an IDS thing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Hi Serge,

Apparently, Sybase is the only one to tolerate extra columns in the select
list, that do not participate in the GROUP BY clause (Transac SQL
extension).

In my sample, I would retrieve each row that have the max(date) by type, and
extract other information from those rows such as title_id. That would
translate into DB2 something like - 1 possibility: select type, title_id
from titles where (type, date) in (select type, max(date) from titles group
by type).

Regards,

Jean-Marc


Nov 12 '05 #7

P: n/a
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:36*************@individual.net...
Jean-Marc Blaise wrote:

>GROUP BY ... HAVING ?
>
>SQL92 clauses
>SQL99 clauses

With respect to GROUP BY/HAVING? or general.
There are whole books about SQL-92 and SQL-99 ;-)

==> With respect to GROUP BY/HAVING ;-)
>what is the best to do in DB2 to treat Transact SQL like (Sybase ...)

Not clear what you mean?

==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is
the
best way to translate into DB2 ?
>select type, title_id, avg(price), avg(advance) from titles group by

type
>select type, title_id from titles group by type having date=max(date)

Neither here.... other than that you will get an error on title_id...

==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and
says
title_id is missing.


OK, I'm hopelessly lost. Can you explain what you expect title_id to
produce in this case?
DB2 supports GROUP BY and HAVING so I'm simply drawing a complete blank
here. The only request I ever came across is to allow column numbers in
GROUP BY (as supported in ORDER BY), that's an IDS thing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge,

Apparently, Sybase is the only one to tolerate extra columns in the select
list, that do not participate in the GROUP BY clause (Transac SQL
extension).

In my sample, I would retrieve each row that have the max(date) by type, and
extract other information from those rows such as title_id. That would
translate into DB2 something like - 1 possibility: select type, title_id
from titles where (type, date) in (select type, max(date) from titles group
by type).

Regards,

Jean-Marc

Ah... the SQL Standard provides a set of OLAP functions which can do
that. Your example makes a lot more sense with MAX than with AVG though.
Not sure what TYPE_ID should be chosen....
In DB2, if you just want one row back you can use ORDER BY with FETCH
FIRST 1 ROW for MAX()/MIN().

For more general stuff OLAP does the job:

SELECT x, y, z FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY X ORDER BY Y) AS rn, x, y, z
FROM T) AS S
WHERE rn = 1

The standard also provides functions for LAST and FIRST. Oracle has
added a lot of that support AFAIK.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

P: n/a
very interesting, thank you Serge.

Just wanted to add one comment on "The cache also remembers previously
executed statements, so that after the first compilation of the SQL
statement. Subsequent invocations will simply keep executing the same
execution plan."

I strongly believe that executing one and the same execution plan is
not always efficient. For data skew situations, range queries and very
complex queries, we sometimes are better off with dynamic SQL (but only
for large tables). What do you think?

Nov 12 '05 #9

P: n/a
ak************@yahoo.com wrote:
very interesting, thank you Serge.

Just wanted to add one comment on "The cache also remembers previously
executed statements, so that after the first compilation of the SQL
statement. Subsequent invocations will simply keep executing the same
execution plan."

I strongly believe that executing one and the same execution plan is
not always efficient. For data skew situations, range queries and very
complex queries, we sometimes are better off with dynamic SQL (but only
for large tables). What do you think?

Absolutely. For that purpose DB2 V8.2 supports REOPT(ALWAYS). It will,
recompile a statement every time.
The next "crank at the handle" so to speak is REOPT(AUTOMATIC). In this
case the optimizer knows for which value ranges the plan is good and
will kick of recompilation if the safe-zone is left. This capability is
not yet in the product :-)
REOPT(ONCE), which is supported, waits for the first set of values,
assuming that it is representative. E.g. when you have a query to find
executives above a certain salary REOPT(ONCE) is quite sufficient for
the optimizer to figure out that salary is going to be highly filtering.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #10

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:36*************@individual.net...
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:36*************@individual.net...
Jean-Marc Blaise wrote:
>>GROUP BY ... HAVING ?
>>
>>SQL92 clauses
>>SQL99 clauses
>
>With respect to GROUP BY/HAVING? or general.
>There are whole books about SQL-92 and SQL-99 ;-)

==> With respect to GROUP BY/HAVING ;-)
>>what is the best to do in DB2 to treat Transact SQL like (Sybase ...)
>
>Not clear what you mean?

==> Any tricks about GROUP BY/HAVING to simulate competitors ? What is


the
best way to translate into DB2 ?
>>select type, title_id, avg(price), avg(advance) from titles group by

type
>>select type, title_id from titles group by type having date=max(date)>
>Neither here.... other than that you will get an error on title_id...

==> Yes on DB2, but not on Sybase. The MTK 1.3 translates partially and


says
title_id is missing.

OK, I'm hopelessly lost. Can you explain what you expect title_id to
produce in this case?
DB2 supports GROUP BY and HAVING so I'm simply drawing a complete blank
here. The only request I ever came across is to allow column numbers in
GROUP BY (as supported in ORDER BY), that's an IDS thing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge,

Apparently, Sybase is the only one to tolerate extra columns in the select list, that do not participate in the GROUP BY clause (Transac SQL
extension).

In my sample, I would retrieve each row that have the max(date) by type, and extract other information from those rows such as title_id. That would
translate into DB2 something like - 1 possibility: select type, title_id from titles where (type, date) in (select type, max(date) from titles group by type).

Regards,

Jean-Marc

Ah... the SQL Standard provides a set of OLAP functions which can do
that. Your example makes a lot more sense with MAX than with AVG though.
Not sure what TYPE_ID should be chosen....
In DB2, if you just want one row back you can use ORDER BY with FETCH
FIRST 1 ROW for MAX()/MIN().

For more general stuff OLAP does the job:

SELECT x, y, z FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY X ORDER BY Y) AS rn, x, y, z
FROM T) AS S
WHERE rn = 1

The standard also provides functions for LAST and FIRST. Oracle has
added a lot of that support AFAIK.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Thanks Serge,

JM
Nov 12 '05 #11

P: n/a

Serge Rielau wrote:
ak************@yahoo.com wrote:
very interesting, thank you Serge.

Just wanted to add one comment on "The cache also remembers previously executed statements, so that after the first compilation of the SQL
statement. Subsequent invocations will simply keep executing the same execution plan."

I strongly believe that executing one and the same execution plan is not always efficient. For data skew situations, range queries and very complex queries, we sometimes are better off with dynamic SQL (but only for large tables). What do you think?
Absolutely. For that purpose DB2 V8.2 supports REOPT(ALWAYS). It

will, recompile a statement every time.
The next "crank at the handle" so to speak is REOPT(AUTOMATIC). In this case the optimizer knows for which value ranges the plan is good and
will kick of recompilation if the safe-zone is left. This capability is not yet in the product :-)
REOPT(ONCE), which is supported, waits for the first set of values,
assuming that it is representative. E.g. when you have a query to find executives above a certain salary REOPT(ONCE) is quite sufficient for the optimizer to figure out that salary is going to be highly filtering.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

as usual, the download from *ibm*.com won't finish (why is that,
anyway??)

question:

i've read through most of the Yip, et al book, but i don't find any
specification of the scope of the dynamic SQL Cache: user, connection,
schema, instance??

thanks,
robert

Nov 12 '05 #12

P: n/a
gn*****@rcn.com wrote:
as usual, the download from *ibm*.com won't finish (why is that,
anyway??) I've never had that problem. I used to have trouble with download
accelerators downloading from the IBM site (corrupt files on unzip).
i've read through most of the Yip, et al book, but i don't find any
specification of the scope of the dynamic SQL Cache: user, connection,
schema, instance??

The cache is DB wide. Statements are matched based on their text,
usage of certain special registers (like PATH, CURRENT SCHEMA) etc.
So it is common for many connections to share the same cached statement.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #13

P: n/a
i'm home now. tried to read your article. came up immediately. must
be something with the CubeLand net.

found the 2nd edition at Borders. grabbed it: covers os/360 <G>
version. that's a big help.

some guy named Serge is prominent in the preface as being helpful in
bringing the book out.

back to the question: by DB wide, on mainframe, that means instance i
suppose.

thanks,
robert

Nov 12 '05 #14

P: n/a
gn*****@rcn.com wrote:
i'm home now. tried to read your article. came up immediately. must
be something with the CubeLand net.

found the 2nd edition at Borders. grabbed it: covers os/360 <G>
version. that's a big help.

some guy named Serge is prominent in the preface as being helpful in
bringing the book out. Another way of saying I'm loud ;-)
back to the question: by DB wide, on mainframe, that means instance i
suppose.

Uh, oh... I dare not comment on internal optimizations in DB2 zOS land.
It is reasonable to expect that Db2 for zOS behaves the same.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.