Hey MChakravarty,
Did you run the code listed below?

select date(a.created) "Created", b.name "Policy Name", count(*) count
from siebel.s_escl_req a, siebel.s_escl_rule b
where a.rule_id = b.row_id
and date(a.created) >= '2008-01-22'
group by date(a.created),b.name
WITH UR;




Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Dr. Eric J. Smith is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: MChakravarty
[mailto:db2udbdba-ezmlmshield-x13520675.[Email address protected]
Sent: Thursday, January 24, 2008 10:17 AM
To: LazyDBA Discussion
Subject: Query Tunning Help

Hello All,

We have following query which is running very slow for past few days :


select date(a.created) "Created", b.name "Policy Name", count(*) count
from siebel.s_escl_req a, siebel.s_escl_rule b
where a.rule_id = b.row_id
and date(a.created) >= '2008-01-22'
group by date(a.created),b.name
for fetch only;

Information About Tables :

1. SIEBEL.S_ESCL_REQ : is a very dynamic table. Every second a lot
of
insertion and deletion happens in it. This table also contains a lot of
triggers.
Now the rcord count in this table is 267943.

It contains following indexes :
CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_REQ_U1"
ON "SIEBEL"."S_ESCL_REQ"
("REQ_ID" ASC,
"RULE_ID" ASC
)
CLUSTER
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE INDEX "SIEBEL"."S_ESCL_REQ_M1"
ON "SIEBEL"."S_ESCL_REQ"
("GROUP_ID" ASC,
"REQ_ID" ASC
)
PCTFREE 30
ALLOW REVERSE SCANS;

2. SIEBEL.S_ESCL_RULE : is a static table. Mostly used for readonly.
Current record count is 95.

It contains following indexes :
CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_U1"
ON "SIEBEL"."S_ESCL_RULE"
("NAME" ASC,
"CONFLICT_ID" ASC
)
CLUSTER
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_M1"
ON "SIEBEL"."S_ESCL_RULE"
("GROUP_ID" ASC,
"ROW_ID" ASC
)
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_P1"
ON "SIEBEL"."S_ESCL_RULE"
("ROW_ID" ASC
)
PCTFREE 30
DISALLOW REVERSE SCANS;

CREATE INDEX "SIEBEL"."S_ESCL_RULE_R1"
ON "SIEBEL"."S_ESCL_RULE"
("OBJECT_NAME" ASC
)
PCTFREE 30
DISALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_U2"
ON "SIEBEL"."S_ESCL_RULE"
("SUB_TYPE_CD" ASC,
"NAME" ASC,
"CONFLICT_ID" ASC
) PCTFREE 30
ALLOW REVERSE SCANS;

Action Taken So Far :

I've updated the statistics of the table but it is still taking a lot of

time to execute.

I've replicated the same problem in a test environment.
There I tried the db2advis tool which recomended an index for
S_ESCL_RULE.
After creating it in the test environment it did not resolve the
problem.
Is there anything we can do to improve the performance of the above
query
?

Thanks,
Mrinal



http://www.rac.co.uk
http://www.rac.co.uk/business
http://www.bsm.co.uk

Any opinions expressed in this e-mail are those of the individual and
not necessarily the company. This e-mail and any attachments are
confidential to RAC and/or BSM and are solely for use by the intended
recipient.

If you are not the intended recipient you must not disclose, copy or
distribute its contents to any other person nor use its contents in any
way.
If you have received this e-mail in error please forward a copy of this
e-mail to "[Email address protected]

RAC Motoring Services: Registered England 1424399
VAT Reg No. GB 238640945
British School of Motoring Limited: Registered England 291902
VAT Reg No. GB 239505847
RAC Financial Services Limited: Registered England & Wales 5171817
VAT Reg No. GB 238640945
Registered Office(s): 8 Surrey Street, Norwich, NR1 3NG

RAC Motoring Services and RAC Financial Services Limited are authorised
and regulated by the Financial Services Authority, both companies are
members of the Aviva group.

This e-mail and any attachments has been scanned for the presence of
computer viruses. RAC/BSM accept no responsibility for computer viruses
once this e-mail has been transmitted.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page