Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu Feb 17, 2005 11:00 am Post subject: Performance improvment help
I have this query I run in SPUFI, It runs about 2 hours. Is there a better way to do this in SPUFI? BTW we do not have a DBA, myself(Senior Programmer) and the systems guy is it for DB2 V7.2. going to 8.?
Thanks!
Code:
CREATE TABLE HOURS.EMPRATES APPROX. 50,000 rows
(EFFDATE_BEGIN DATE NOT NULL,
EFFDATE_END DATE NOT NULL,
RATEFUND SMALLINT NOT NULL,
MEMTYPE CHAR(01) NOT NULL,
MEMCLASS CHAR(08) NOT NULL,
PLANTYPE CHAR(01) NOT NULL,
EMPRNO INTEGER NOT NULL,
EMPRSUB SMALLINT NOT NULL,
SW_SPLIT SMALLINT NOT NULL,
RATE_1ST99 DECIMAL(06,4) NOT NULL,
RATE_OVER99 DECIMAL(06,4) NOT NULL,
RATE_FLAT DECIMAL(06,4) NOT NULL,
SUS_1ST99 SMALLINT NOT NULL,
SUS_OVER99 SMALLINT NOT NULL,
SUS_FLAT SMALLINT NOT NULL,
FEATURES_EXCLUDED CHAR(01) NOT NULL,
ACCTNO SMALLINT ,
ALT_MEMTYPE CHAR(01) ,
USERID CHAR(40) ,
LASTUPDT TIMESTAMP)
IN FDBHRS.FTSEMPRT
;
CREATE UNIQUE INDEX HOURS.XEMPRTE1
ON HOURS.EMPRATES
(EFFDATE_BEGIN, EFFDATE_END, RATEFUND, MEMTYPE,
MEMCLASS, PLANTYPE, EMPRNO, EMPRSUB)
USING STOGROUP FSGALLP
PRIQTY 12000
SECQTY 400
CLUSTER
BUFFERPOOL BP2
CLOSE NO;
CREATE INDEX HOURS.XEMPRTE2
ON HOURS.EMPRATES
(ACCTNO)
USING STOGROUP FSGALLP
PRIQTY 12000
SECQTY 400
BUFFERPOOL BP2
CLOSE NO;
CREATE INDEX HOURS.XEMPRTE3
ON HOURS.EMPRATES
(ACCTNO,EFFDATE_BEGIN, EFFDATE_END, RATEFUND)
USING STOGROUP FSGALLP
PRIQTY 12000
SECQTY 400
BUFFERPOOL BP2
CLOSE NO;
CREATE TABLE HOURS.RALPHDTL APPROX. 562,000 rows
(SSNO INTEGER NOT NULL
PSTPERIOD DATE NOT NULL
ACCTNO SMALLINT NOT NULL
RATEFUND SMALLINT NOT NULL
HOURS DECIMAL(11,2)
NAME CHAR(20)
EFFDATE_BEGIN DATE
EFFDATE_END DATE
SW_SPLIT SMALLINT
RATE_1ST99 DECIMAL(06,4)
SUS_1ST99 SMALLINT
RATE_OVER99 DECIMAL(06,4)
SUS_OVER99 SMALLINT
RATE_FLAT DECIMAL(06,4)
SUS_FLAT SMALLINT
IN FDBHRS.FTSRALT
;
CREATE UNIQUE INDEX HOURS.XRALPHDTL1
ON HOURS.RALPHDTL
(SSNO,PSTPERIOD,ACCTNO,RATEFUND)
USING STOGROUP FSGALLP
PRIQTY 10000
SECQTY 7500
CLUSTER
BUFFERPOOL BP5
CLOSE NO;
CREATE INDEX HOURS.XRALPHDTL2
ON HOURS.RALPHDTL
(PSTPERIOD,ACCTNO,RATEFUND)
USING STOGROUP FSGALLP
PRIQTY 20000
SECQTY 2000
BUFFERPOOL BP5
CLOSE NO;
UPDATE HOURS.RALPHDTL A
SET (EFFDATE_BEGIN
,EFFDATE_END
,SW_SPLIT
,RATE_1ST99
,SUS_1ST99
,RATE_OVER99
,SUS_OVER99
,RATE_FLAT
,SUS_FLAT) =
(SELECT B.EFFDATE_BEGIN
,B.EFFDATE_END
,B.SW_SPLIT
,B.RATE_1ST99
,B.SUS_1ST99
,B.RATE_OVER99
,B.SUS_OVER99
,B.RATE_FLAT
,B.SUS_FLAT
FROM HOURS.EMPRATES B
WHERE B.ACCTNO = A.ACCTNO
AND B.EFFDATE_BEGIN <= A.PSTPERIOD
AND B.EFFDATE_END >= A.PSTPERIOD
AND B.RATEFUND = A.RATEFUND)
WHERE A.SSNO > 700000000;
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu Feb 17, 2005 11:36 am Post subject:
Kolusu, This was defined by my predecessor, I have made sure the ACCTNO column does not contain Nulls.
Do my Index(s) look ok?
Could Table Space be an issue?
The mainframe is usually So fast, maybe I'm just spoiled.
I realize a COBOL program with logic to eliminate redundant reads would be much faster, I just get request from time that need to be done ASAP.
Thanks!
Joined: 17 Jun 2004 Posts: 36 Topics: 14 Location: westminster, california
Posted: Thu Feb 17, 2005 11:55 am Post subject:
Koluso,
I'm the non-DBA systems guy that Nascar9 works with. I want to add to his note that this query is running on z/OS 1.4 running as the only operating system guest under z/VM 4.4 on a z/800-0a1 with 4 gig central storage allocated to this guest. This system normally runs at approximately 40% CPU utilization and 25% central frame storage allocated, so CPU and storage is probably not the bottleneck. Most SPUFI based queries and updates run lightning fast, but this one does not. Using statistics pulled from Omegamon/DB2, I determined that this query is more than likely doing a "table space scan", judging by the number of pages being pulled in and the low number of rows being updated (approx. 80 rows per second). I'm at a loss so any help here would be appreciated.
Thanks.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Tue Feb 22, 2005 11:14 am Post subject:
bauer,
In our currnet environment 'Explain' was never setup. In the new install of 8.? it will be setup. After reading this board I see where it is very helpful.
I included the indexes so if they are wrong somone can point it out. I don't claim to be a DBA.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
