Federated Join EE to EEE

[stabbert]

I am attempting to join two tables together on two different unix
servers. Here is some relevant info about the tables.

TABLE 1 Setup

-----------------------

DB2 UDB 7.2 EE

AIX

CBS_STG.S_BAT_I N

Number of Rows About 200,000

No Indexes

CREATE TABLE CBS_STG.S_BAT_I N(

NUMBER_1_3 SMALLINT NOT NULL,

NUMBER_4_6 SMALLINT NOT NULL,

NUMBER_7_9 SMALLINT NOT NULL,

);

TABLE 2 Setup

------------------------

DB2 UDB 7.2 EEE

AIX

8 Nodes

BILL_DW.BILL_AC CT

Number of Rows about 15,000,000

Partitioning Key On BILL_ACCT_KEY

Index on BILL_ACCT_NUM

CREATE TABLE BILL_DW.BILL_AC CT(

BILL_ACCT_KEY INTeger NOT NULL,

BILL_ACCT_NUM CHARacter(9) NOT NULL

);

CREATE INDEX BILL_ACCT_X1 ON BILL_DW.BILL_AC CT(BILL_ACCT_NU M);

Utilizing federation I am now attempting to join the following tables
together utilizing the following SQL Statement.

SELECT BILL_ACCT.BILL_ ACCT_KEY, BILL_ACCT.BILL_ ACCT_NUM

FROM CBS_STG.S_BAT_I N S_BAT_IN,

BILL_DW.BILL_AC CT BILL_ACCT

WHERE BILL_ACCT.BILL_ ACCT_NUM = CAST(RIGHT(
DIGITS(S_BAT_IN .NUMBER_1_3),3) ||

RIGHT( DIGITS(S_BAT_IN .NUMBER_4_6),3) || RIGHT(

DIGITS(S_BAT_IN .NUMBER_7_9),3) as CHAR(9));

Running this through explain plan yields the following "Optimized
Statement".

SELECT Q2.$C0 AS "BILL_ACCT_KEY" , Q2.$C1 AS "BILL_ACCT_ NUM"

FROM

(SELECT Q1.BILL_ACCT_KE Y, Q1.BILL_ACCT_NU M

FROM BILL_DW.BILL_AC CT AS Q1) AS Q2, CBS_STG.S_BAT_I N AS Q3

WHERE (Q2.$C1 = CHAR(("SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_1_ 3),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_4_ 6),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_7_ 9),

4000), 3)), 9))

Notice in the optimized query the following statement (SELECT
Q1.BILL_ACCT_KE Y, Q1.BILL_ACCT_NU M FROM BILL_DW.BILL_AC CT AS Q1) AS Q2
Why is it doing this? This will produce an inline view with no
indexes which means I am then joining a 200,000 row table to a
15,000,000 table together through table scan. Why can it not
effectively push down the CBS_STG.S_BAT_I N table to create a temp table
and then join to the BILL_DW.BILL_AC CT table utilizing an index. The
query is being run from the system that contains BILL_DW.BILL_AC CT and
the reason being is we though it would only have to push down the
smallest tables contents. The cost on the query is 3.23844e+10 and as
you can guess doesn't ever finish executing. How can I better optimize
this join through federation?

Spencer
--
Posted via http://dbforums.com

[Aakash Bordia]

Hi Spencer, Can you post the optimizer plan too? What SQL is in the RQUERY
or SHIP operator? Do you see any ISCAN on the local
table?(BILL_DW. BILL_ACCT)
The optimized statement that you posted is not actually the remote stmt that
we are generating. Its an intermediate form though.
Thanks
Aakash
"stabbert" <me*********@db forums.com> wrote in message
news:35******** ********@dbforu ms.com...

I am attempting to join two tables together on two different unix
servers. Here is some relevant info about the tables.

TABLE 1 Setup

-----------------------

DB2 UDB 7.2 EE

AIX

CBS_STG.S_BAT_I N

Number of Rows About 200,000

No Indexes

CREATE TABLE CBS_STG.S_BAT_I N(

NUMBER_1_3 SMALLINT NOT NULL,

NUMBER_4_6 SMALLINT NOT NULL,

NUMBER_7_9 SMALLINT NOT NULL,

);

TABLE 2 Setup

------------------------

DB2 UDB 7.2 EEE

AIX

8 Nodes

BILL_DW.BILL_AC CT

Number of Rows about 15,000,000

Partitioning Key On BILL_ACCT_KEY

Index on BILL_ACCT_NUM

CREATE TABLE BILL_DW.BILL_AC CT(

BILL_ACCT_KEY INTeger NOT NULL,

BILL_ACCT_NUM CHARacter(9) NOT NULL

);

CREATE INDEX BILL_ACCT_X1 ON BILL_DW.BILL_AC CT(BILL_ACCT_NU M);

Utilizing federation I am now attempting to join the following tables
together utilizing the following SQL Statement.

SELECT BILL_ACCT.BILL_ ACCT_KEY, BILL_ACCT.BILL_ ACCT_NUM

FROM CBS_STG.S_BAT_I N S_BAT_IN,

BILL_DW.BILL_AC CT BILL_ACCT

WHERE BILL_ACCT.BILL_ ACCT_NUM = CAST(RIGHT(
DIGITS(S_BAT_IN .NUMBER_1_3),3) ||

RIGHT( DIGITS(S_BAT_IN .NUMBER_4_6),3) || RIGHT(

DIGITS(S_BAT_IN .NUMBER_7_9),3) as CHAR(9));

Running this through explain plan yields the following "Optimized
Statement".

SELECT Q2.$C0 AS "BILL_ACCT_KEY" , Q2.$C1 AS "BILL_ACCT_ NUM"

FROM

(SELECT Q1.BILL_ACCT_KE Y, Q1.BILL_ACCT_NU M

FROM BILL_DW.BILL_AC CT AS Q1) AS Q2, CBS_STG.S_BAT_I N AS Q3

WHERE (Q2.$C1 = CHAR(("SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_1_ 3),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_4_ 6),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_7_ 9),

4000), 3)), 9))

Notice in the optimized query the following statement (SELECT
Q1.BILL_ACCT_KE Y, Q1.BILL_ACCT_NU M FROM BILL_DW.BILL_AC CT AS Q1) AS Q2
Why is it doing this? This will produce an inline view with no
indexes which means I am then joining a 200,000 row table to a
15,000,000 table together through table scan. Why can it not
effectively push down the CBS_STG.S_BAT_I N table to create a temp table
and then join to the BILL_DW.BILL_AC CT table utilizing an index. The
query is being run from the system that contains BILL_DW.BILL_AC CT and
the reason being is we though it would only have to push down the
smallest tables contents. The cost on the query is 3.23844e+10 and as
you can guess doesn't ever finish executing. How can I better optimize
this join through federation?

Spencer
--
Posted via http://dbforums.com

[stabbert]

I am attaching the entire plan. I am unfamilar on how to capture the
RQUERY and SHIP Operator?

Spencer

DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001

Licensed Material - Program Property of IBM

IBM DATABASE 2 Explain Table Format Tool



*************** ***** EXPLAIN INSTANCE *************** *****

DB2_VERSION: 07.02.8

SOURCE_NAME: SQLC2D04

SOURCE_SCHEMA: NULLID

EXPLAIN_TIME: 2003-10-30-10.53.19.562984

EXPLAIN_REQUEST ER: DB2UDB

Database Context:

----------------

Parallelism: Intra-Partition & Inter-Partition
Parallelism

CPU Speed: 1.058838e-06

Comm Speed: 100

Buffer Pool size: 250400

Sort Heap size: 7500

Database Heap size: 12000

Lock List size: 1200

Maximum Lock List: 10

Average Applications: 4

Locks Available: 13560

Package Context:

---------------

SQL Type: Dynamic

Optimization Level: 5

Blocking: Block All Cursors

Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 201 ----------------

QUERYNO: 1

QUERYTAG: CLP

Statement Type: Select

Updatable: No

Deletable: No

Query Degree: -1

Original Statement:

------------------

SELECT BILL_ACCT.BILL_ ACCT_KEY, BILL_ACCT.BILL_ ACCT_NUM

FROM CBS_STG.S_BAT_I N S_BAT_IN, BILL_DW.BILL_AC CT BILL_ACCT

WHERE BILL_ACCT.BILL_ ACCT_NUM = CAST(RIGHT(
DIGITS(S_BAT_IN .NUMBER_1_3),3) ||

RIGHT( DIGITS(S_BAT_IN .NUMBER_4_6),3) || RIGHT(

DIGITS(S_BAT_IN .NUMBER_7_9),3) as CHAR(9))

Optimized Statement:

-------------------

SELECT Q2.$C0 AS "BILL_ACCT_KEY" , Q2.$C1 AS "BILL_ACCT_ NUM"

FROM

(SELECT Q1.BILL_ACCT_KE Y, Q1.BILL_ACCT_NU M

FROM BILL_DW.BILL_AC CT AS Q1) AS Q2, CBS_STG.S_BAT_I N AS Q3

WHERE (Q2.$C1 = CHAR(("SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_1_ 3),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_4_ 6),

4000), 3) || "SYSFUN
"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_7_ 9),

4000), 3)), 9))

Access Plan:

-----------

Total Cost: 3.23844e+10

Query Degree: 0

Rows

RETURN

( 1)

Cost

I/O

|

2.21859e+06

NLJOIN

( 2)

3.23844e+10

20372

/---+---\

1.43694e+07 0.154397

DTQ TBSCAN

( 3) ( 5)

83601.9 11966.4

18913 354

| |

1.79617e+06 277324

TBSCAN TEMP

( 4) ( 6)

77388.4 9712.65

18913 354

| |

1.79617e+06 277324

TABLE: BILL_DW RQUERY

BILL_ACCT ( 7)

9360.27

354

|

277324

NK: CBS_STG

S_BAT_IN
--
Posted via http://dbforums.com

[stabbert]

1) RETURN: (Return Result)

Cumulative Total Cost: 3.23844e+10

Cumulative CPU Cost: 3.05847e+16

Cumulative I/O Cost: 20372

Cumulative Re-Total Cost: 3.23843e+10

Cumulative Re-CPU Cost: 3.05847e+16

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 24334.7

Cumulative Comm Cost: 227622

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 20018

Remote communication cost: 143984

Arguments:

---------

BLDLEVEL: (Build level)

DB2 v7.1.0.82 : s030617

ENVVAR : (Environment Variable)

DB2_HASH_JOIN = ON

Input Streams:

-------------

8) From Operator #2

Estimated number of rows:
2.21859e+06

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_NUM+ BILL_ACCT_KEY

Partition Column Names:

----------------------

+NONE

2) NLJOIN: (Nested Loop Join)

Cumulative Total Cost: 3.23844e+10

Cumulative CPU Cost: 3.05847e+16

Cumulative I/O Cost: 20372

Cumulative Re-Total Cost: 3.23843e+10

Cumulative Re-CPU Cost: 3.05847e+16

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 24334.7

Cumulative Comm Cost: 227622

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 20018

Remote communication cost: 143984

Arguments:

---------

EARLYOUT: (Early Out flag)

FALSE

FETCHMAX: (Override for FETCH MAXPAGES)

IGNORE

ISCANMAX: (Override for ISCAN MAXPAGES)

IGNORE

Predicates:

----------

2) Predicate used in Join

Relational Operator: Equal (=)

Subquery Input Required: No

Filter Factor: 5.5674e-07

Predicate Text:

--------------

(Q2.$C1 = CHAR(("SYSFUN

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_1_ 3), 4000),

"." 3) || "SYSFUN

"."

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_4_ 6), 4000),

"." 3) || "SYSFUN

"."

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_7_ 9), 4000),

"." 3)), 9))

Input Streams:

-------------

3) From Operator #3

Estimated number of rows:
1.43694e+07

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_KEY+ BILL_ACCT_NUM

Partition Column Names:

----------------------

+NONE

7) From Operator #5

Estimated number of rows: 0.154397

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

8) To Operator #1

Estimated number of rows:
2.21859e+06

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_NUM+ BILL_ACCT_KEY

Partition Column Names:

----------------------

+NONE

3) TQ : (Table Queue)

Cumulative Total Cost: 83601.9

Cumulative CPU Cost: 9.05114e+09

Cumulative I/O Cost: 18913

Cumulative Re-Total Cost: 3275

Cumulative Re-CPU Cost: 3.09301e+09

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.1882

Cumulative Comm Cost: 227622

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 18913

Arguments:

---------

JN INPUT: (Join input leg)

OUTER

LISTENER: (Listener Table Queue type)

FALSE

TQMERGE : (Merging Table Queue flag)

FALSE

TQREAD : (Table Queue Read type)

READ AHEAD

TQSEND : (Table Queue Write type)

DIRECTED

UNIQUE : (Uniqueness required flag)

FALSE

Input Streams:

-------------

2) From Operator #4

Estimated number of rows:
1.79617e+06

Partition Map ID: -100

Partitioning: (MULT )

Multiple
Partiti-
ons

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_KEY+ BILL_ACCT_NUM

Partition Column Names:

----------------------

+1: BILL_ACCT_KEY

Output Streams:

--------------

3) To Operator #2

Estimated number of rows:
1.43694e+07

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_KEY+ BILL_ACCT_NUM

Partition Column Names:

----------------------

+NONE

4) TBSCAN: (Table Scan)

Cumulative Total Cost: 77388.4

Cumulative CPU Cost: 3.18289e+09

Cumulative I/O Cost: 18913

Cumulative Re-Total Cost: 3275

Cumulative Re-CPU Cost: 3.09301e+09

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.0522

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 18913

Arguments:

---------

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

SEQUENTIAL

ROWLOCK : (Row Lock intent)

NEXT KEY SHARE

SCANDIR : (Scan Direction)

FORWARD

TABLOCK : (Table Lock intent)

INTENT SHARE

Input Streams:

-------------

1) From Object BILL_DW.BILL_AC CT

Estimated number of rows:
1.79617e+06

Partition Map ID: 6

Partitioning: (MULT )

Multiple
Partiti-
ons

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+$RID$+BILL_ACC T_NUM+BILL_ACCT _KEY

Partition Column Names:

----------------------

+1: BILL_ACCT_KEY

Output Streams:

--------------

2) To Operator #3

Estimated number of rows:
1.79617e+06

Partition Map ID: -100

Partitioning: (MULT )

Multiple
Partiti-
ons

Number of columns: 2

Subquery predicate ID: Not
Applicable

Column Names:

------------

+BILL_ACCT_KEY+ BILL_ACCT_NUM

Partition Column Names:

----------------------

+1: BILL_ACCT_KEY

5) TBSCAN: (Table Scan)

Cumulative Total Cost: 11966.4

Cumulative CPU Cost: 2.94321e+09

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 2253.7

Cumulative Re-CPU Cost: 2.12846e+09

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 11966.4

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 1105

Remote communication cost: 143984

Arguments:

---------

JN INPUT: (Join input leg)

INNER

MAXPAGES: (Maximum pages for prefetch)

ALL

PREFETCH: (Type of Prefetch)

NONE

SCANDIR : (Scan Direction)

FORWARD

Predicates:

----------

2) Sargable Predicate

Relational Operator: Equal (=)

Subquery Input Required: No

Filter Factor: 5.5674e-07

Predicate Text:

--------------

(Q2.$C1 = CHAR(("SYSFUN

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_1_ 3), 4000),

"." 3) || "SYSFUN

"."

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_4_ 6), 4000),

"." 3) || "SYSFUN

"."

"."RIGHT"($INTE RNAL_FUNC$(DIGI TS(Q3.NUMBER_7_ 9), 4000),

"." 3)), 9))

Input Streams:

-------------

6) From Operator #6

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

7) To Operator #2

Estimated number of rows: 0.154397

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

6) TEMP : (Create Temporary Table)

Cumulative Total Cost: 9712.65

Cumulative CPU Cost: 8.14717e+08

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 0

Cumulative Re-CPU Cost: 0

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 9712.65

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 1105

Remote communication cost: 143984

Arguments:

---------

CSETEMP : (Temp over common sub-expression flag)

FALSE

SLOWMAT : (Slow Materialization flag)

FALSE

TEMPSIZE: (Temporary Table Page Size)

4096

Input Streams:

-------------

5) From Operator #7

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

6) To Operator #5

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE
--
Posted via http://dbforums.com

[stabbert]

7) RQUERY: (Remote Query)

Cumulative Total Cost: 9360.27

Cumulative CPU Cost: 4.81917e+08

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 505.361

Cumulative Re-CPU Cost: 4.77279e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.0561

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 355

Remote communication cost: 143984

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

RMTQTXT : (Remote statement)

SELECT A0."NUMBER_1_3" , A0."NUMBER_4_6" ,
A0."NUMBER_7_9 " FROM "CBS_STG"."S_BA T_IN" A0

RMTSEVER: (Remote server)

STG_P01

STREAM : (Remote stream)

FALSE

Input Streams:

-------------

4) From Object CBS_STG.S_BAT_I N

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 53

Subquery predicate ID: Not
Applicable

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

5) To Operator #6

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Objects Used in Access Plan:

---------------------------

Schema: CBS_STG

Name: S_BAT_IN

Type: Nickname

Time of creation: 2003-10-27-
13.05.47.308142

Last statistics update:

Number of columns: 53

Number of rows: 277324

Width of rows: 18

Number of buffer pool pages: 4334

Distinct row values: No

Tablespace name:

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 32

Container extent page count: 32

Schema: BILL_DW

Name: BILL_ACCT

Type: Table

Time of creation: 2003-06-27-
08.37.51.360009

Last statistics update: 2003-10-27-
12.48.38.364203

Number of columns: 7

Number of rows: 1796172

Width of rows: 25

Number of buffer pool pages: 18913

Distinct row values: No

Tablespace name:
BILL_DW_BILL_AC CT

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 64

Container extent page count: 32

Table overflow record count: 0
--
Posted via http://dbforums.com

[Mauro Cazzari]

I see that a nested loop join is executed. I would make sure to run RUNSTATS
on both tables before rerunning the statement. Try and let mw know.
Mauro.

"stabbert" <me*********@db forums.com> wrote in message
news:35******** ********@dbforu ms.com...

7) RQUERY: (Remote Query)

Cumulative Total Cost: 9360.27

Cumulative CPU Cost: 4.81917e+08

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 505.361

Cumulative Re-CPU Cost: 4.77279e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.0561

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 355

Remote communication cost: 143984

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

RMTQTXT : (Remote statement)

SELECT A0."NUMBER_1_3" , A0."NUMBER_4_6" ,
A0."NUMBER_7_9 " FROM "CBS_STG"."S_BA T_IN" A0

RMTSEVER: (Remote server)

STG_P01

STREAM : (Remote stream)

FALSE

Input Streams:

-------------

4) From Object CBS_STG.S_BAT_I N

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 53

Subquery predicate ID: Not
Applicable

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

5) To Operator #6

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Objects Used in Access Plan:

---------------------------

Schema: CBS_STG

Name: S_BAT_IN

Type: Nickname

Time of creation: 2003-10-27-
13.05.47.308142

Last statistics update:

Number of columns: 53

Number of rows: 277324

Width of rows: 18

Number of buffer pool pages: 4334

Distinct row values: No

Tablespace name:

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 32

Container extent page count: 32

Schema: BILL_DW

Name: BILL_ACCT

Type: Table

Time of creation: 2003-06-27-
08.37.51.360009

Last statistics update: 2003-10-27-
12.48.38.364203

Number of columns: 7

Number of rows: 1796172

Width of rows: 25

Number of buffer pool pages: 18913

Distinct row values: No

Tablespace name:
BILL_DW_BILL_AC CT

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 64

Container extent page count: 32

Table overflow record count: 0
--
Posted via http://dbforums.com

[Aakash Bordia]

Hi, From the plan it seems that the smaller table is being entirely
shipped(rquery) to the federated server which is what you desire. Remember
in v72, federated does not exploit local parallelism, so that might be an
issue here.
May be you explore summary table over nickname (not too sure if its
available in v72) or create a local copy of the nickname if space is not an
issue. That is if you want to exploit local parallelism
Thanks
Aakash
"Mauro Cazzari" <ma***********@ sas.com> wrote in message
news:bo******** **@license1.unx .sas.com...

I see that a nested loop join is executed. I would make sure to run RUNSTATS on both tables before rerunning the statement. Try and let mw know.
Mauro.

"stabbert" <me*********@db forums.com> wrote in message
news:35******** ********@dbforu ms.com...

7) RQUERY: (Remote Query)

Cumulative Total Cost: 9360.27

Cumulative CPU Cost: 4.81917e+08

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 505.361

Cumulative Re-CPU Cost: 4.77279e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.0561

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 355

Remote communication cost: 143984

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

RMTQTXT : (Remote statement)

SELECT A0."NUMBER_1_3" , A0."NUMBER_4_6" ,
A0."NUMBER_7_9 " FROM "CBS_STG"."S_BA T_IN" A0

RMTSEVER: (Remote server)

STG_P01

STREAM : (Remote stream)

FALSE

Input Streams:

-------------

4) From Object CBS_STG.S_BAT_I N

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 53

Subquery predicate ID: Not
Applicable

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

5) To Operator #6

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )

Coordin-
ator Pa-
rtition

Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Objects Used in Access Plan:

---------------------------

Schema: CBS_STG

Name: S_BAT_IN

Type: Nickname

Time of creation: 2003-10-27-
13.05.47.308142

Last statistics update:

Number of columns: 53

Number of rows: 277324

Width of rows: 18

Number of buffer pool pages: 4334

Distinct row values: No

Tablespace name:

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 32

Container extent page count: 32

Schema: BILL_DW

Name: BILL_ACCT

Type: Table

Time of creation: 2003-06-27-
08.37.51.360009

Last statistics update: 2003-10-27-
12.48.38.364203

Number of columns: 7

Number of rows: 1796172

Width of rows: 25

Number of buffer pool pages: 18913

Distinct row values: No

Tablespace name:
BILL_DW_BILL_AC CT

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 64

Container extent page count: 32

Table overflow record count: 0
--
Posted via http://dbforums.com

[Aakash Bordia]

Here is some more information:

the big table (CBS) has to be brought to the coordinator to do the join.
This is the best we can do in db2_v72. The table scan on CBS can be turned
into index scan, but dont know how much it will help performance. The index
scan helps if it is on the inner of nljn and it is used to retrieve only
limited number of rows because of index start-stop predicates.

Thanks, Aakash

"Aakash Bordia" <a_******@hotma il.com> wrote in message
news:bo******** **@hanover.toro lab.ibm.com...

Hi, From the plan it seems that the smaller table is being entirely
shipped(rquery) to the federated server which is what you desire. Remember
in v72, federated does not exploit local parallelism, so that might be an
issue here.
May be you explore summary table over nickname (not too sure if its
available in v72) or create a local copy of the nickname if space is not an issue. That is if you want to exploit local parallelism
Thanks
Aakash
"Mauro Cazzari" <ma***********@ sas.com> wrote in message
news:bo******** **@license1.unx .sas.com...

I see that a nested loop join is executed. I would make sure to run

RUNSTATS

on both tables before rerunning the statement. Try and let mw know.
Mauro.

"stabbert" <me*********@db forums.com> wrote in message
news:35******** ********@dbforu ms.com...

7) RQUERY: (Remote Query)

Cumulative Total Cost: 9360.27

Cumulative CPU Cost: 4.81917e+08

Cumulative I/O Cost: 354

Cumulative Re-Total Cost: 505.361

Cumulative Re-CPU Cost: 4.77279e+08

Cumulative Re-I/O Cost: 0

Cumulative First Row Cost: 25.0561

Cumulative Comm Cost: 0

Cumulative First Comm Cost: 0

Estimated Bufferpool Buffers: 355

Remote communication cost: 143984

Arguments:

---------

CSERQY : (Remote common subexpression)

FALSE

RMTQTXT : (Remote statement)

SELECT A0."NUMBER_1_3" , A0."NUMBER_4_6" ,
A0."NUMBER_7_9 " FROM "CBS_STG"."S_BA T_IN" A0

RMTSEVER: (Remote server)

STG_P01

STREAM : (Remote stream)

FALSE

Input Streams:

-------------

4) From Object CBS_STG.S_BAT_I N

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )
Coordin- ator Pa- rtition
Number of columns: 53

Subquery predicate ID: Not
Applicable

Partition Column Names:

----------------------

+NONE

Output Streams:

--------------

5) To Operator #6

Estimated number of rows: 277324

Partition Map ID: -100

Partitioning: (COOR )
Coordin- ator Pa- rtition
Number of columns: 3

Subquery predicate ID: Not
Applicable

Column Names:

------------

+NUMBER_7_9+NUM BER_4_6+NUMBER_ 1_3

Partition Column Names:

----------------------

+NONE

Objects Used in Access Plan:

---------------------------

Schema: CBS_STG

Name: S_BAT_IN

Type: Nickname

Time of creation: 2003-10-27-
13.05.47.308142

Last statistics update:

Number of columns: 53

Number of rows: 277324

Width of rows: 18

Number of buffer pool pages: 4334

Distinct row values: No

Tablespace name:

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 32

Container extent page count: 32

Schema: BILL_DW

Name: BILL_ACCT

Type: Table

Time of creation: 2003-06-27-
08.37.51.360009

Last statistics update: 2003-10-27-
12.48.38.364203

Number of columns: 7

Number of rows: 1796172

Width of rows: 25

Number of buffer pool pages: 18913

Distinct row values: No

Tablespace name:
BILL_DW_BILL_AC CT

Tablespace overhead: 24.100000

Tablespace transfer rate: 0.900000

Prefetch page count: 64

Container extent page count: 32

Table overflow record count: 0
--
Posted via http://dbforums.com