RE: optimizer decision to ship the table. -- URGENT.

This actually is a data ware house environment and it has circular
logging enabled. The business does not require it to have archival
logging and so I believe, I cannot create cache tables.
I was referring to an article and it says explicitly that I need to have
archival logging enabled to create cache tables.

Prerequisites 7
7 7 Set the FEDERATED database manager configuration parameter 7 for the
DB2 UDB server to YES. 7 To access Informix data sources, install and
configure the Informix Client 7 SDK software in the federated server. 7
To cache data from a DB2 UDB for Linux, UNIX, and Windows tables, you 7
must configure the database that the table is stored in for log
retention logging . Log retention logging is a type of archive logging.
7 To configure the database for log retention logging, you set the
LOGRETAIN 7 value to RECOVER. 7 The federated database or the source
database must be on the computer 7 from which you are creating the cache
tables. If the federated database or 7 the source database are not
local, you must catalog the databases on the local 7 computer. The alias
name that you use when you catalog the database must be 7 the same name
as the database name. 7 The user ID in the user mapping between the
databases must have the authority 7 to create tables in the source
database.

Let me know if I am missing something.
Thanks.

-----Original Message-----
From: Kosaraju Venkata
[mailto:db2udbdba-ezmlmshield-x93027823.[Email address protected]
Sent: Monday, October 23, 2006 7:22 AM
To: LazyDBA Discussion
Subject: RE: optimizer decision to ship the table. -- URGENT.

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.

---------------------------------------------------------------------
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