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
{
$TmpTableStmt1H andle = $DbHandle->prepare($TmpTa bleStmt1);
$TmpTableStmt1H andle->execute;
$TmpTableStmt1H andle->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.
$InsertFinalStm t = "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.DEPENDE NCY D, GBLCODE.SOURCEN AME 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_NUMBE R),
RTRIM(SWITCH)
FROM GBLCODE.MODCONT ROL M, GBLCODE.SOURCEN AME S
WHERE M.SRC_NUM IN
(SELECT SRC_NUM FROM GBLCODE.DEPENDE NCY
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";
$DependsOnStmtH andle->execute($Pass, $Pass,$Pass,$Pa ss);
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.
