The alternative to doing things this way is to use a corelated subquery.
The use of the temporary table means we build the table only once but it will
be "bigger" since we collect all max(c_price) values for all publishers. In
order to know which is faster, it is probably a good idea to execute both and see
which runs faster.
Once this tablespace exists and you are granted use of this space (by me),
There are a couple of ways you can define a temporary table. A complete list
of the possible syntaxes can be found in the SQL Reference Manual under declare
global temporary table. For now we will look at only two.
If we were to
answer the same question these notes began with using a global temporary table
the SQL syntax would be
For example, suppose we want to print out the most expensive books published by
each publisher ( pub_name, isbn, author, title, c_price) and to put an "*" beside the
most expensive books in the entire library. The output should look like
So the value of the
with-clause is that you can have several temporary tables
and refer to one in the construction of some other. You also have the advantage of
simplifying the final select statement so it is easier to read.
One last comment about the case statement used. In C, C++ and Java
there is a statement of the form
An observation. Using the case statement gives you a means of taking some of the
search logic, which normally appears within the where-clause, and moving it into
the select-list. There are moments when this can be very useful.
1: Find the most expensive books sold by each publisher.
select pub_name, author, title, c_price
from book bk, table(select pub_name, max(c_price) as maxprice
from book group by pub_name) as temp1 t
where bk.pub_name = t.pub_name and bk.c_price = t.maxprice
The table temp1 is a temporary table which will not exist after the execution
of the query (t is its alias). We use temp1 to break the query logic up into two steps - first
find the highest book price for each publisher and second find the books published
by that publisher that cost that much.
1': Find the most expensive books sold by each publisher.
select pub_name, author, title, c_price
from book bk
where bk.c_price = ( select max(c_price) from book bk1
where bk1.pub_name = bk.pub_name)
This query, since it is corelated on pub_name, will need to have its subquery reexecuted
for every row in book and so theoretically, if there are many books published by the same
publisher, we may need to recalculate the exactly same max price over and over again
instead of once per publisher.
CREATE USER TEMPORARY TABLESPACE some_name PAGESIZE 4 K
MANAGED BY SYSTEM USING ('/home/db2inst1/db2inst1/NODE0000/container_name')
EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32
TRANSFERRATE 0.9 BUFFERPOOL IBMDEFAULTBP;
This code has already been run on avalon.
db2 grant use of tablespace some_name to user some_user
you are able to create temporary tables which can be used to hold temporary,
non-logged data for the duration of your session (until you terminate the
database connection). To terminate a session just
execute
db2 connect reset
or
db2 terminate
Upon doing this all temporary tables you created during the recent session are dropped.
declare global temporary table table_name1
LIKE <table_name2> on commit preserve rows not logged
or
declare global temporary table table_name
(column1 some_data_type, column2 some_data_type, ...)
on commit preserve rows not logged
Neither of these statements actually puts any rows into the temporary table. To do that
you must use the normal insert into table_name syntax. However to do this
you need to know what schema the temporary tables are stored in. The schema is
called SESSION. This schema name can not be left off whenever you are
using a global temporary table. This is because no reference to such a table is
to be found in SYSIBM.SYSTABLES so if you leave off the schema name and a table
exists in your current schema with the same name then that table will be used
instead.
1'': Find the most expensive books sold by each publisher.
set current schema = LIBRARY;
declare global temporary table Temp1
(pub_name varchar(7), maxprice numeric(8,2))
on commit preserve rows not logged ;
insert into SESSION.Temp1 (pub_name, maxprice )
select pub_name, max(c_price) from book group by pub_name;
select bk.pub_name, bk.author, bk.title, bk.c_price
from book bk, SESSION.Temp1 t
where bk.pub_name = t.pub_name and bk.c_price = t.maxprice
One curious characteristic of the declare global ... command is that
if you do not include the on commit preserve rows section then the
default behaviour is to wipe out the contents of the temporary table whenever a commit
statement is issued (explicitly or implicitly) which would otherwise write all
table data to disk. Since a commit transaction statement might be executed
by db2 between the insert into and select statements above you
might end up with an empty answer set of you don't explicitly save the data in
the temporary table.
1 PUB_NAME ISBN AUTHOR TITLE C_PRICE
- -------- ---- ------- ------ -------
AW 1-52 Date DB 28.00
CSP 7-45 Baer Arch 35.00
* PH 2-34 T'baum Netw 37.00
Wiley 3-56 K'rock Queue 25.00
4 record(s) selected.
The SQL is:
with
temp1 as (select pub_name, max(c_price) as maxprice
from book group by pub_name),
temp2 as (select pub_name, maxprice from temp1
where maxprice = (select max(maxprice) from temp1) )
select case when t.maxprice = t2.maxprice then '*' else '' END,
k.pub_name, isbn, author, title, c_price
from book k, temp1 t , temp2 t2
where k.pub_name = t.pub_name and
k.c_price = t.maxprice
order by k.pub_name;
Notice how we referred to the table temp1 in the query which defined table temp2.
This would not be possible if we had used our first approach of putting both temp1
and temp2 in the from-clause of the select statement as follows:
select case when temp1.maxprice = temp2.maxprice then '*' else '' END,
k.pub_name, isbn, author, title, c_price
from book k,
table (select pub_name, max(c_price) as maxprice
from book group by pub_name) as temp1 ,
table (select pub_name, maxprice from temp1
where maxprice = (select max(maxprice) from temp1) )
as temp2
where k.pub_name = temp1.pub_name and
k.c_price = temp1.maxprice
order by k.pub_name;
This is because the table temp1 does not exist in the schema we are currently
using - db2inst1 or pletcha - but only in the query currently being
executed. However, in the definition of temp2 that
is precisely where the query looks for the temp1 mentioned. Since it doesn't find it
we get an error message (SQL0204N).
(condition) ? <expression_1> : <expression_2>;
This statement evaluates (condition), if it is true it returns <expression_1>
else it returns <expression_2>. This is precisely the behaviour of the
case statement. The statement
case when temp1.maxprice = temp2.maxprice then '*' else '' END
evaluates temp1.maxprice = temp2.maxprice and if it is true then the
case statement returns '*'; otherwise it returns the empty string.
pletcha@newpaltz.edu