Mark, that was a good observation. There is also an option to cache the table in the wrapper/nickname create statement and also I believe you can build the indexes on the cached table.
-----Original Message-----
From: Gillis Mark
[mailto:db2udbdba-ezmlmshield-x68687719.[Email address protected]
Sent: Monday, October 23, 2006 6:37 AM
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table. -- URGENT.
I don't think including GROUP BY functions does you any favours.
See
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
the salient point being " having a GROUP BY operator aggregate remote data locally could also require DB2 to retrieve the entire table from the remote data source." It only says COULD, but in my limited experience the optimiser will see that and pass back the entire table.
Mark
-----Original Message-----
From: Kristipati Pavan
[mailto:db2udbdba-ezmlmshield-x47895432.[Email address protected]
Sent: 20 October 2006 15:35
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table. -- URGENT.
Access plan shows that the tables are being shipped instead of
push-down. I would highly appreciate any input.
Nicknames involved:
CDBHROT1.POL_STAT_PREMIUM_NCR -- 1778993 rows
CDBHROT1.POL_STAT_HEADER_NCR -- 71564 rows
CDBHROT1.POL_STAT_UNIT_NCR -- 152309 rows
SELECT PREM.UNITCOVERAGE_ID, PREM.VERS_CNTL_MAJ_MIN, PREM.WP
FROM
(SELECT
WP.UNITCOVERAGE_ID, PSH.VERS_CNTL_MAJ_MIN,
SUM(WP.PREM_WRIT_M) AS WP
FROM
(SELECT
UNITCOVERAGE_ID,
STAT_UNIT_ID,
PREM_WRIT_M
FROM
CDBHROT1.POL_STAT_PREMIUM_NCR
WHERE
ODS_TX_TS BETWEEN '2006-10-14 20:20:20.123457' AND
'2006-10-15 20:20:20.123456'
AND PREMIUM_SOURCE_T IN ('COVERAGE','FLAT_CHARG')
AND PREM_WRIT_M <> 0
)WP
INNER JOIN
(SELECT DISTINCT STAT_UNIT_ID, STAT_HEADER_ID FROM
CDBHROT1.POL_STAT_UNIT_NCR
)PSU
ON PSU.STAT_UNIT_ID = WP.STAT_UNIT_ID
INNER JOIN
(SELECT DISTINCT STAT_HEADER_ID, VERS_CNTL_MAJ_MIN FROM
CDBHROT1.POL_STAT_HEADER_NCR WHERE ODS_TX_TS <= '2006-10-15
20:20:20.123456'
)PSH ON PSH.STAT_HEADER_ID = PSU.STAT_HEADER_ID
GROUP BY WP.UNITCOVERAGE_ID, PSH.VERS_CNTL_MAJ_MIN
)PREM
WHERE PREM.WP <> 0
WITH UR
-----Original Message-----
From: David Baker
[mailto:db2udbdba-ezmlmshield-x53143795.[Email address protected]
Sent: Friday, October 20, 2006 10:25 AM
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table. -- URGENT.
Stats may not be the issue. Again, UDB is shipping the whole table
because
it THINKS it the easiest way to do what you asked. Getting UDB to do
what
you think is best is almost an art. In order to help at all, I will
need to
see the DDL and SQL behind the issue. The table sizes would be good as
well. I also recommend working in detail (hiring) with a performance
expert
with federation. Make sure they show you the what and WHY behind their
changes.
Good luck.
David Baker
UDB World Site Admin
www.udbworld.com
[Email address protected]
-----Original Message-----
From: Kristipati Pavan
[mailto:db2udbdba-ezmlmshield-x65077385.[Email address protected]
Sent: Friday, October 20, 2006 9:50 AM
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table. -- URGENT.
My situation has changed now. Our DBA has applied some patch on fp 12
and was able to do fix the issue of nicknames getting replicated when
dropping and re-creating nicknames. He said he did do the fed stats.
How do I confirm this ? Do I go to my local server and do a query
against sycat.tables and look for my nickname and then look for
stats_time ? Is this the way ? Does this actually show the runstats
timestamp on remote server or fed stats timestamp on federated? Please
let me know.
Assuming this is the way to confirm this, I went ahead and generated the
access plan. Interestingly, I do not see any change in timerons assuming
fed stats are done (my syscat.tables stats_time shows this early morning
time stamp for the nickname I am interested in).
So, does this mean, there was no change in timerons at all before and
after fed stats ? !!!! The tables are still being shipped instead of
push-down processing. What other factors might influence this.
Did anyone work on changing the DB2_MAXIMAL_PUSHDOWN parameter or
COLLATING_SEQUENCE .
PLEASE NOTE THAT BOTH MY DATABASES ARE ON THE SAME SERVER AND BOTH OF
THEM ARE DB2 DATABASES.
Thanks a bunch Gurus.
-----Original Message-----
From: Gillis Mark [mailto:mark.[Email address protected]
Sent: Friday, October 20, 2006 7:54 AM
To: Kristipati, Pavan
Subject: RE: optimzer decision to ship the table.
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Did you ever get an answer to your query? I've been looking at a similar
scenario in UDB V9.1 on Windows. As with yours, updating the stats by
doing RUNSTATS on the remote server (where the physical table resides)
does not update the stats on the federated data source. Only dropping
and recreating the nickname (on the local server) populates stats and
subsequent RUNSTATS on the remote server do not filter through.
I interrogate SYSCAT.NICKNAMES to see the details, but this wasn't
available before 9.1 I think; where do you look for fed stats?
Regards
Mark Gillis
-----Original Message-----
From: Kristipati Pavan
[Email address protected]
Sent: 19 October 2006 17:20
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table.
Even if I drop nickname and re-create it, I do not see that fed stats
have been updated on my nicknames. I checked this by going against the
catalog tables and checking for stats time.
What am I missing ?
-----Original Message-----
From: David Baker [mailto:[Email address protected]
Sent: Thursday, October 19, 2006 10:36 AM
To: Kristipati, Pavan
Subject: RE: optimzer decision to ship the table.
To tell you the truth, I just drop and recreate the nicknames to update
the
stats. It just seamed easier to me. I have heard about this issue with
the
8.2 stats sql proc that creates duplicate views. The only thing you
have to
do is drop these views after you run the job. It is also fixed in a
later
fix pack.
Good Luck.
David Baker
UDB World Site Admin
www.udbworld.com
[Email address protected]
-----Original Message-----
From: Kristipati Pavan [mailto:[Email address protected]
Sent: Thursday, October 19, 2006 9:08 AM
To: [Email address protected]
Subject: RE: optimzer decision to ship the table.
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Thanks David for the response.
I heard that fed stats on fix pack 12 (8.2) cause some issues on
nicknames.
I need to provide this information to my system DBA and confirm that he
would need to take counter steps to live with the nickname duplication
when fed stats are run on nicknames.
Is this something that anyone of you encountered in the past? Did you
see any other issues while dealing with nicknames in either fp 11 or fp
12 ?
Thanks a bunch.
- Pavan.
-----Original Message-----
From: David Baker
[Email address protected]
Sent: Wednesday, October 18, 2006 1:27 PM
To: LazyDBA Discussion
Subject: RE: optimzer decision to ship the table.
There are many reasons why, but the short of it is that UDB thinks
that's
the cheapest way to get it done. Some things that may influence it's
decision are:
1) The stats. If you are 8.2 or higher there is a sql proc to update
the
stats for federated objects. You can also drop and re-create the
nicknames
to update stats. Make sure stats are up to date at the data source.
2) The optimizer doesn't correctly guess how many rows qualify at the
source. The easiest way to fix this is the re-write the SQL to make it
more
obvious to the optimizer. You can also break up the sql into easier to
manage chucks.
Good luck.
David Baker
UDB World Site Admin
www.udbworld.com
[Email address protected]
-----Original Message-----
From: Kristipati Pavan
[mailto:db2udbdba-ezmlmshield-x64296883.[Email address protected]
Sent: Wednesday, October 18, 2006 1:00 PM
To: LazyDBA Discussion
Subject: optimzer decision to ship the table.
GURUS,
If somebody has worked on tuning federated SQL queries involving
homogeneous (db2) data sources, you would be doing me a big favor by
sharing your knowledge with me.
Why does the optimizer choose to ship the entire table (it is huge 30 M
rows) to the local data source instead of processing the data remotely
and then returning sub-set of data. We usually want the data to be
processed remotely and then work with the resultant data. Right?
Esp when we are dealing with tens of millions of remote data.
What factors influence optimizer's decision?
Any light on this would be helpful.
Thanks.
-----Original Message-----
From: Kristipati Pavan
[mailto:db2udbdba-ezmlmshield-x5436652.[Email address protected]
Sent: Wednesday, October 18, 2006 10:34 AM
To: LazyDBA Discussion
Subject: explain plan for nicknames.
Gurus,
I have a complex sql query that goes against 3 nicknames (of tables).
When I generate explain plan (to analyze the query performance), I do
not see what kind of joins, scans etc are happening in these nicknames.
Is there any way I can know more details about this ?
Also, is there any document that you can share with me that helps me
in tuning querys going against db2 nicknames ?
I appreciate your help.
Thanks.
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
This e-mail (and any attachments) may contain privileged and/or
confidential information. If you are not the intended recipient please
do not disclose, copy, distribute, disseminate or take any action in
reliance on it. If you have received this message in error please reply
and tell us and then delete it. Should you wish to communicate with us
by e-mail we cannot guarantee the security of any data outside our own
computer systems. For the protection of Legal & General's systems and
staff, incoming emails will be automatically scanned.
Any information contained in this message may be subject to applicable
terms and conditions and must not be construed as giving investment
advice within or outside the United Kingdom.
The following companies are subsidiary companies of the Legal & General
Group Plc which are authorised and regulated by the Financial Services
Authority for advising and arranging the products shown: Legal & General
Partnership Services Limited (insurance and mortgages), Legal & General
Insurance Limited (insurance), Legal & General Assurance Society Limited
(life assurance, pensions and investments), Legal & General Unit Trust
Managers Limited and Legal & General Portfolio Management Services
Limited (investments).
They are registered in England under numbers shown.
The registered office is Temple Court, 11 Queen Victoria Street, London
EC4N 4TP.
Legal & General Partnership Services Limited: 5045000 Legal & General
Assurance Society Limited: 166055 Legal & General (Unit Trust Managers)
Limited: 1009418 Legal & General (Portfolio Management Services)
Limited: 2457525 Legal & General Insurance Limited: 423930
They are registered with the Financial Services Authority under numbers
shown. You can check this at www.fsa.gov.uk/register
Legal & General Partnership Services Limited: 300792 Legal & General
Assurance Society Limited: 117659 Legal & General (Unit Trust Managers)
Limited: 119273 Legal & General (Portfolio Management Services) Limited:
146786 Legal & General Insurance Limited: 202050
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
-----------------------------------------
CONFIDENTIALITY NOTICE: The Ohio Public Employees Retirement System
intends this e-mail message, and any attachments, to be used only
by the person(s) or entity to which it is addressed. This message
may contain confidential and/or legally privileged information. If
the reader is not the intended recipient of this message or an
employee or agent responsible for delivering the message to the
intended recipient, you are hereby notified that you are prohibited
from printing, copying, storing, disseminating or distributing this
communication. If you received this communication in error, please
delete it from your computer and notify the sender by reply e-mail.
DB2 & UDB email list listserv db2-l LazyDBA home page