Folks,
Thanks for repltying back to my query. Our DBA talked to IBM regarding the
issue we are having with SQL0437W Performance of this complex query may be
sub-optimal. Reason > code: "6". SQLSTATE=01602.
They said there is a issue which IBM are going to Fix in Fixpack 9 coming
out in April.
We were doing RUNSTATS on the Tables with the DISTRIBUTION AND DETAILED
INDEXES ALL.
IBM recommended to do RUNSTATS without DISTRIBUTION option. Aparently DB2
get confused with the DISTRIBUTION option. Once I ran the RUNSTATS with
INDEX option, the message went away and my script does not error out any
more.
I am not sure what impact is there on performance if I do the RUNSTATS
without the DISTRIBUTION option as the our system constantly processes data
and we have to do RUNSTATS on table regularly.
BTW, this warning message was introduced when we upgrade DB2 Server
Version to 8.2 from 7.2. I have run my script in version 7.2 and they have
worked fine.
Here is the comment from the IBM
"the problem seems to be in the DB2 statistic gathering process. See the
following text from another customer incident and let me know if the
problem of having a character column with many entries where the first 33
characters are the same is lso your case.
"The inconsistency in stats (SQL0437 RC6) is between distribution stats and
index stats is the problem ere. There is a limitation on runstats where we
only consider the uniqueness of the first 33 characters in the
table. This is due to performance reasons. Hence, when you gather
distribution stats, the colvalue being checked will only be checked for the
uniqueness of first 33 chars and hence the VALCOUNT may not reflect
properly. The FIRSTKEYCARD of the table is lower for column 'NAME' 12219,
then VALCOUNT for some values in this table however running a query to
check the uniqueness of 'NAME' column we see that there are 12302 unique
values. As well, there are VALCOUNTS for distribution stats which
contradict with the index stats, i.e. VALCOUNT > FIRSTKEYCARD.
Changing the COLVALUE and VALCOUNT as follows for this column also bypasses
the problem:
UPDATE SYSSTAT.COLDIST
SET COLVALUE = NULL, VALCOUNT= -1
WHERE VALCOUNT <> -1 AND COLNAME = 'NAME' AND TABNAME = 'T1'
AND TABSCHEMA = 'DB2INST3';
So if you want to run distribution stats so that other columns don't get
affected by just running runstats with only indexes all option, then you
can update the sysstat.coldist for this column 'NAME' and set the valcount
to -1 and COLVALUE to NULL for the time being and this will also avoid the
problem.
The inconsistency is due to the current limitation of checking just first
'33' characters due to performance reasons. "
-----------------------------------
IBM has already opened a defect :
IY65765 SQL0437W RC=6 WHEN RUNSTATS WITH DISTRIBUTION
the plan is to change the way DB2 gathers the statistics on column
uniqueness. DB2 Development is trying to correct this problem in the
fixpack 9 due in April. Meanwhile the following workarounds can be used:
db2 "runstats on table <table_name> and indexes all"
or if you absolutely want with distribution you need to specify the
runstats specifying all column names but the problem one. (runstats on
table <table_name> on columns(col1, col2, col3...); )"
Sumeet Agarwal
Technology & Solutions
Ph: 312-293-4976
sumeet.[Email address protected]
Fax: 312-461-7333
|---------+-------------------------------------------------------->
| | thirumaran |
| | <db2udbdba-ezmlmshield-x2917687.[Email Address Removed] | azyDBA.com> |
| | |
| | 01/28/2005 07:37 AM |
| | |
|---------+-------------------------------------------------------->
>----------------------------------------------------------------------------------------------|
| |
| To: LazyDBA Discussion <[Email address protected] |
| cc: |
| Subject: Re: SQL SUBOPTIMAL help please... |
>----------------------------------------------------------------------------------------------|
Sumeet,
This BUG / issue is reported in APAR list
see this link
http://www-306.ibm.com/software/data/db2/udb/support/downloadv8.html
V810 FixPak 7a for AIX 5 32-bit & 64-bit -> select APAR list
other bug report link for fixpack 7a :
It turns out that this is a APAR, which has been fixed recently. APAR
IY66171
Ref:
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&dc=DB520&dc=D600&dc=DB530&dc=D700&dc=DB500&dc=DB540&dc=DB510&dc=DB550&q1=IY66171&uid=swg21195837&loc=en_US&cs=utf-8&lang=en
Thanks
Thirumaran
----- Original Message -----
From: "sumeet" <db2udbdba-ezmlmshield-x20898676.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Friday, January 28, 2005 3:24 AM
Subject: RE: SQL SUBOPTIMAL help please...
>
> Hey Folks,
>
> Since we have upgraded from Db7.2 to DB2 8.2 to DB2 8.2 fix pack 7a we
> are getting the This SQL Error. We run DB2 on AIX 5.1 Unix Server.
>
> insert into PS_HBC_CUSTR_LKUP_wk select * from PS_HBC_CUSTR_LKUP a where
> substr(a.FI_INSTRUMENT_ID,1,3) IN ('XIM','XAM','XST','XCL') and
> a.fi_instrument_id = ( select b.HBC_INSTRUMENT_FR from PS_HBC_INSTRUMENT
b
> WHERE b.HBC_INSTRUMENT_FR = a.fi_instrument_id)
> SQL0437W Performance of this complex query may be sub-optimal. Reason
> code:
> "6". SQLSTATE=01602
>
> Other than this message We also out ETL Processes which load data into
> Tables from Flat Files have been running 4 to 5itmes faster.
>
> Can someone please help us in this issue?
>
> Thank You,
>
> Sumeet Agarwal
> Technology & Solutions
> Ph: 312-293-4976
> sumeet.[Email address protected]
> Fax: 312-461-7333
>
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page