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

Temporary table performance problem on UDB 8 on Linux

P: n/a

Folks,

I have a perl script that creates and uses global termporary table.
This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the
database to UDB 8 on Linux. The select statement that used to take less
than a minute now take 10 minutes. Do I have to tune something on Linux
and/or DB2?

I tried two different ways to create temporary tablespace but both of
them give same slow performance:

Method 1:

db2 "create user temporary tablespace TEMPSPACE2 managed by system using ('/usr/local/home/db2gblcd/db2gblcd/UserTempSpace')"
db2 grant use of tablespace tempspace2 to public

Method 2:

db2 "create bufferpool tempspace2pool size 5 pagesize 32k"
db2 "create user temporary tablespace TEMPSPACE2 pagesize 32k managed by system using ('/usr/local/home/db2gblcd/db2gblcd/UserTempSpace') bufferpool tempspace2pool"
db2 grant use of tablespace tempspace2 to public
Here is the fragment of the code I am using:

$TmpTableStmt1 = "DECLARE GLOBAL TEMPORARY TABLE SESSION.FINAL (
PASS INTEGER,
SRC_NUM INTEGER
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
IN TEMPSPACE2
;
CREATE INDEX FIDX1 ON SESSION.FINAL (SRC_NUM ASC);
CREATE INDEX FIDX2 ON SESSION.FINAL (PASS ASC);
";

eval
{
$TmpTableStmt1Handle = $DbHandle->prepare($TmpTableStmt1);
$TmpTableStmt1Handle->execute;
$TmpTableStmt1Handle->finish;
};

The code loops and inserts rows into the SESSION.FINAL table and
executeds a COMMIT statement. The code inserts between 70 and 100
rows into the table.

$InsertFinalStmt = "INSERT INTO SESSION.FINAL (PASS, SRC_NUM)
VALUES (?,?)";


This is the statement that now takes almost 10 minutes to execute.
$DependsOnStmt = "(SELECT LINE_NUM,
D.SRC_NUM AS SN,
D.DEPENDS_NUM,
'NONE',
'NONE'
FROM GBLCODE.DEPENDENCY D, GBLCODE.SOURCENAME S
WHERE DEPENDS_NUM IN
(SELECT SRC_NUM
FROM SESSION.FINAL
WHERE PASS = ?)
AND S.SRC_NUM NOT IN
(SELECT SRC_NUM
FROM SESSION.FINAL
WHERE PASS < ?)
AND D.SRC_NUM = S.SRC_NUM
UNION ALL
SELECT LINE_NUM,
M.SRC_NUM AS SN,
-1,
RTRIM(MOD_NUMBER),
RTRIM(SWITCH)
FROM GBLCODE.MODCONTROL M, GBLCODE.SOURCENAME S
WHERE M.SRC_NUM IN
(SELECT SRC_NUM FROM GBLCODE.DEPENDENCY
WHERE DEPENDS_NUM IN
(SELECT SRC_NUM
FROM SESSION.FINAL
WHERE PASS = ?)
AND S.SRC_NUM NOT IN
(SELECT SRC_NUM
FROM SESSION.FINAL
WHERE PASS < ?)
)
AND M.SRC_NUM = S.SRC_NUM
)
ORDER BY SN, LINE_NUM
FOR READ ONLY";

$DependsOnStmtHandle->execute($Pass,$Pass,$Pass,$Pass);
I checked stats on the tables in the above query and everything is looks
O.K. I even re-org'd the tables but no improvement.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
While stranded on information super highway Hemant Shah wrote:

Folks,

I have a perl script that creates and uses global termporary table.
This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the
database to UDB 8 on Linux. The select statement that used to take less
than a minute now take 10 minutes. Do I have to tune something on Linux
and/or DB2?


I was monitoring the Linux system (2.4.18-14 kernel) and noticed that
the load average went up dramatically when I was running the query.

# top
1:14pm up 16 days, 15:44, 6 users, load average: 15.90, 13.86, 11.02
140 processes: 137 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 1.5% user, 10.5% system, 0.0% nice, 87.8% idle
Mem: 2582136K av, 2569444K used, 12692K free, 0K shrd, 6220K buff
Swap: 1542160K av, 131652K used, 1410508K free 2336268K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
14713 db2gblcd 16 0 44984 31M 26520 R 2.9 1.2 36:02 db2sysc
6901 db2gblcd 16 0 45560 28M 27468 D 2.7 1.1 10:38 db2sysc
6903 db2gblcd 15 0 44392 30M 28824 D 0.9 1.2 13:09 db2sysc
2338 db2gblcd 15 0 43896 30M 26968 D 0.5 1.2 27:16 db2sysc

# uptime
1:16pm up 16 days, 15:45, 6 users, load average: 17.11, 14.67, 11.52

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #2

P: n/a
Ian
Hemant Shah wrote:
Folks,

I have a perl script that creates and uses global termporary table.
This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the
database to UDB 8 on Linux. The select statement that used to take less
than a minute now take 10 minutes. Do I have to tune something on Linux
and/or DB2?

[...]

I checked stats on the tables in the above query and everything is looks
O.K. I even re-org'd the tables but no improvement.


Have you checked the access plan?

What about RUNSTATS on the GTT after it's been populated?


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #3

P: n/a
What about the explain? Did it change between V7 and V8.
Could it be you get sort-overflows? Memory management has shifted a bit.
So it is a possibility that you receive an overflow where you didn't
before.....

Cheers
Serge
Nov 12 '05 #4

P: n/a
While stranded on information super highway Ian wrote:
Hemant Shah wrote:
Folks,

I have a perl script that creates and uses global termporary table.
This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the
database to UDB 8 on Linux. The select statement that used to take less
than a minute now take 10 minutes. Do I have to tune something on Linux
and/or DB2?

> [...]

I checked stats on the tables in the above query and everything is looks
O.K. I even re-org'd the tables but no improvement.
Have you checked the access plan?


How do you check access plan on a dynamic SQL statement with GTT?

What about RUNSTATS on the GTT after it's been populated?
I did that too but same bad performance.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #5

P: n/a
While stranded on information super highway Serge Rielau wrote:
What about the explain? Did it change between V7 and V8.
Could it be you get sort-overflows? Memory management has shifted a bit.
So it is a possibility that you receive an overflow where you didn't
before.....
How do I do explain on SQL statement with global temporary table?

Cheers
Serge


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #6

P: n/a
Easiest way is:

db2 connect to test
db2 DECLARE GLOBAL ....
db2 EXPLAIN PLAN FOR SELECT ......
db2exfmt -d test
<default> everything but the output file

Cheers
Serge
Nov 12 '05 #7

P: n/a
While stranded on information super highway Hemant Shah wrote:
While stranded on information super highway Ian wrote:
Hemant Shah wrote:
Folks,

I have a perl script that creates and uses global termporary table.
This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the
database to UDB 8 on Linux. The select statement that used to take less
than a minute now take 10 minutes. Do I have to tune something on Linux
and/or DB2?

> [...]

I checked stats on the tables in the above query and everything is looks
O.K. I even re-org'd the tables but no improvement.
Have you checked the access plan?


How do you check access plan on a dynamic SQL statement with GTT?

What about RUNSTATS on the GTT after it's been populated?


I did that too but same bad performance.


I did runstats on regular tbles but no success. How do you do runstats
on a GTT from perl script?



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #8

P: n/a
While stranded on information super highway Serge Rielau wrote:
What about the explain? Did it change between V7 and V8.
Could it be you get sort-overflows? Memory management has shifted a bit.
So it is a possibility that you receive an overflow where you didn't
before.....

Cheers
Serge


I did some further testing and instead of using global temporary tables
I created another table in regular tablespace and used that in my query.

With regular table the query ran very fast.

There seems to be problem in the way global temporary tables are implemented
on Linux.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #9

P: n/a
Hemand,

The code is independent of the platform.
Compare the plans of the good and the bad query.

Cheers
Serge
Nov 12 '05 #10

P: n/a
While stranded on information super highway Serge Rielau wrote:
Hemand,

The code is independent of the platform.
Compare the plans of the good and the bad query.
How do I get plan for a dynamic query that uses global temporary table?

The query is same, the only difference is that when I use GTT it runs very
slow. The query is executed about three times. With GTT it takes about
10 to 15 minutes per iteration.

I printed out the number inserts into the table and found out that in first
iteration there were 74 rows and still took 10 minutes for select statement
using GTT, by the third pass there were 15000 rows in GTT.

With regular tables it takes 2 minutes for three iterations.

The inserts are very fast, I printed the count on each insert. The select
takes too long.

For regular tables I can execute "db2 runstats" command from perl.
How can I do runstats on global temporary tables from Perl DBI interface?

Exact same query used to run fast on AIX and UDB 7.2 using GTT.

Cheers
Serge


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #11

P: n/a
I answred that question earlier in this thread:

db2 connect to test
db2 declare global .....
db2 explain plan for <your statement>
db2exfmt -f test
<accept default for all input exxcept output file name>

Then run the same but with the persistent table.

Cheers
Serge
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.