473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Tips and tricks for fast SQL procedures

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
14 5402
"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
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
"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
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
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
"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
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
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
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
"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

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: travolta003 | last post by:
Windows XP tips and tricks. Learn how to bypass very common windows problems, speed up your system and make it more reliable with useful tips and tricks. http://windowsxpsp2pro.blogspot.com
0
by: travolta004 | last post by:
Windows XP tips and tricks. Learn how to bypass very common windows problems, speed up your system and make it more reliable with useful tips and tricks. http://windowsxpsp2pro.blogspot.com
68
bartonc
by: bartonc | last post by:
I've decide to compile a bunch of your favorite tips and tricks for the Articles section. I found a post yesterday by Chrisjc that is a perfect example. I copied his post over to create Dealing with...
2
bartonc
by: bartonc | last post by:
I've decide to compile a bunch of your favorite tips and tricks for the Articles section. Post your favorite tips and tricks here, in this thread, and I'll copy the best ones to a Tips and Tricks...
0
by: smartfix | last post by:
Windows XP tips and tricks. Learn how to bypass very common windows problems, to speed up your system and make it more reliable with useful tips and tricks. http://windowsxpsp2pro.blogspot.com
0
by: kamalpp | last post by:
hi check http://aspnet-tips-tricks.blogspot.com/ for tips and tricks Thanks
1
by: JosAH | last post by:
Greetings, Introduction This week's tip describes a few old tricks that are almost forgotten by most people around here. Sometimes there's no need for these tricks anymore because processors...
20
Nepomuk
by: Nepomuk | last post by:
As Linux and the various flavours of Unix are slowly spreading into the world of personal computers, I thought we could collect a few Tips and Tricks here to help each other making the best out of...
8
Frinavale
by: Frinavale | last post by:
Edit Many times we spend hours and hours trying to solve a problem. When we finally figure it out, we want to share it to keep others from suffering the same way! That's why we have this "Tips...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.