Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query performance problems

Status
Not open for further replies.
theStefan

theStefan

Programmer
Joined
Jul 24, 2001
Messages
4
Location
GB
I do have some problems concerning a query, which looks like the following:

--------------
select
a.ATTSTATUSTEXT,
a.PKCSTATUSKRZL, [...]
b.ATTNAME as ANFORDERERNAME,
c.ATTNAME as ERSTELLERNAME

from
WSBEAAPP.QRYBEAHALF as a
JOIN
ORGAPP.TBLCSGDIR as b
ON a.fkcpnranforderer=b.pkcpnr
JOIN
ORGAPP.TBLCSGDIR as c
ON a.fkcpnrersteller=c.pkcpnr

GROUP BY a.ATTSTATUSTEXT, [...]
--------
this query takes approx. 3.5 secs to run, which is quite to long in my opinion (TBLCSGDIR contains 2853 records)
When the view QRYBEAHALF is run alone, it will take 1,3s to finish.
I substituted the view QRYBEAHALF with a table, containing the same information (3702 records) and ran the query above with this table (WSBEAPP.QRYBEAHALF substituted with WSBEAAPP.TBLBEAHALF)
Now it takes only 0,6 sec to finish, so my sense tells me, that since QRYBEAHALF only takes 1,3s the overall time of the query above should not be over 2secs !!

Any suggestion where I could gather more information, why this query takes that long ?

 
Sort by date Sort by votes
Apart from the fact that your time-calculation is questionable why are you using a group by?
Group by is mandatory if your SQL contains one or more aggregates, if you want to sort data you should use ORDER BY instead. Sorting a resultset is quite often the biggest performance killer.

Furthermore DB2 uses a special parameter that governs the behavior of the optimizer. (DFT_QUERY_OPT)
It can be set in a range of 1-9. The higher the value, the longer DB2 uses to estimate the best query path. Default this is set at 5 i think, you could lower it to see if this gives better results. This is only advisable if you have only very simple queries running.

Also try and test query performance without sorting, this may well prove to be very fast...........

T. Blom
Information analyst
tbl@shimano-eu.com
 
Upvote 0 Downvote
sorry, GROUP BY was only left, because the original query did some aggregates, I forgot to remove it writing this easier example.
Anyway -> when replacing it by SORT BY times changes but these big time differences still exist.

By the way: timing is done by db2batch (several times and average taken), the server is a testserver (only db2 running) with me active on it as the only person, so I think the timing values are a far better measure than timerons.

 
Upvote 0 Downvote
Hi,
What's the performance like if you wrote the SQL in the more usual fashion? eg.
select
a.ATTSTATUSTEXT,
a.PKCSTATUSKRZL, [...]
b.ATTNAME as ANFORDERERNAME,
c.ATTNAME as ERSTELLERNAME

from
WSBEAAPP.QRYBEAHALF a,
ORGAPP.TBLCSGDIR b,
ORGAPP.TBLCSGDIR c

Where a.fkcpnranforderer=b.pkcpnr
and a.fkcpnrersteller=c.pkcpnr

Any difference?

Marc
 
Upvote 0 Downvote
HI,

Marc's query above would reduce run time. The 2 ON clauses would possibly materialize the queries twice causing increase in run times.

Do you have the Explain output for the query?

-PK

I.T.Analyst
Tata Consultancy Services
 
Upvote 0 Downvote
no - the change doesn't reduce the time, because the optimizer changes it to the WHERE clause already :(

OK - here is the explain output, I reduced the queries a bit, problem still exists, but it's a bit less output (but still a lot)

------- QRYBEAHALF1 -----

SELECT
B.FKSSTATUS,
B.PKSBEA,
B.PKCBEANR,
B.FKCPNRERSTELLER,
C.FKCPNRANFORDERER,
C.FKCBELASTUNGSKST,
Count(A.ATTMENGE) AS AGGMENGE,
Sum(A.ATTMENGE*A.ATTEPREIS) AS AGGSUMME

FROM BEAAPP.TBLPOSINFO AS C
INNER JOIN
(
BEAAPP.TBLBEA AS B
INNER JOIN
BEAAPP.TBLPOSITION AS A
ON B.PKSBEA = A.FKSBEA
)
ON C.PKSPOSINFO = A.FKSPOSINFO

GROUP BY
B.FKSSTATUS,
B.PKSBEA,
B.PKCBEANR,
B.FKCPNRERSTELLER,
C.FKCPNRANFORDERER,
C.FKCBELASTUNGSKST;
---------------

and explain output of the original query:

----------------
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.7
SOURCE_NAME: TOOL1DFR
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2003-07-29-16.16.09.046001
EXPLAIN_REQUESTER: WSBEAAPP

Database Context:
----------------
Parallelism: None
CPU Speed: 4,369185e-007
Comm Speed: 0
Buffer Pool size: 2500
Sort Heap size: 256
Database Heap size: 600
Lock List size: 50
Maximum Lock List: 22
Average Applications: 1
Locks Available: 1243

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Repeatable Read



---------------- STATEMENT 1 SECTION 1 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select A.FKSSTATUS, A.PKSBEA, A.PKCBEANR, A.FKCPNRERSTELLER,
A.FKCPNRANFORDERER, A.FKCBELASTUNGSKST, A.AGGMENGE, A.AGGSUMME,
b.ATTNAME as ANFORDERERNAME, c.ATTNAME as ERSTELLERNAME
from WSBEAAPP.QRYBEAHALF1 as a JOIN ORGAPP.TBLCSGDIR as b ON
a.fkcpnranforderer=b.pkcpnr JOIN ORGAPP.TBLCSGDIR as c ON
a.fkcpnrersteller=c.pkcpnr
order by A.FKSSTATUS


Optimized Statement:
-------------------
SELECT Q5.$C0 AS "FKSSTATUS", Q5.$C1 AS "PKSBEA", Q5.$C2 AS "PKCBEANR",
Q5.$C3 AS "FKCPNRERSTELLER", Q5.$C4 AS "FKCPNRANFORDERER", Q5.$C5 AS
"FKCBELASTUNGSKST", Q5.$C6 AS "AGGMENGE", Q5.$C7 AS "AGGSUMME",
Q6.ATTNAME AS "ANFORDERERNAME", Q7.ATTNAME AS "ERSTELLERNAME"
FROM
(SELECT Q4.$C0, Q4.$C1, Q4.$C2, Q4.$C3, Q4.$C4, Q4.$C5, COUNT(1),
SUM((Q4.$C6 * Q4.$C7))
FROM
(SELECT Q2.FKSSTATUS, Q2.PKSBEA, Q2.PKCBEANR, Q2.FKCPNRERSTELLER,
Q3.FKCPNRANFORDERER, Q3.FKCBELASTUNGSKST, Q1.ATTMENGE,
Q1.ATTEPREIS
FROM BEAAPP.TBLPOSITION AS Q1, BEAAPP.TBLBEA AS Q2, BEAAPP.TBLPOSINFO
AS Q3
WHERE (Q2.PKSBEA = Q1.FKSBEA) AND (Q3.PKSPOSINFO = Q1.FKSPOSINFO)) AS
Q4
GROUP BY Q4.$C5, Q4.$C4, Q4.$C3, Q4.$C2, Q4.$C1, Q4.$C0) AS Q5,
ORGAPP.TBLCSGDIR AS Q6, ORGAPP.TBLCSGDIR AS Q7
WHERE (Q5.$C4 = Q6.PKCPNR) AND (Q5.$C3 = Q7.PKCPNR)
ORDER BY Q5.$C0

Access Plan:
-----------
Total Cost: 52715,1
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1,6e+006
NLJOIN
( 2)
52715,1
1386
/---------+-------- 40000 40
NLJOIN TBSCAN
( 3) ( 25)
3604,14 5,37297
852 501
/----------+---------\ |
1000 40 1000
RQUERY TBSCAN TEMP
( 4) ( 21) ( 26)
1903,68 5,37297 4,1445
318 501 501
+---------------+ +-------------+ | |
1000 1000 1000 1000 1000
NK: BEAAPP NK: BEAAPP NK: BEAAPP TEMP RQUERY
TBLBEA TBLPOSIN TBLPOSIT ( 22) ( 27)
4,1445 3,6152
501 501
| |
1000 1000
RQUERY NK: ORGAPP
( 23) TBLCSGDI
3,6152
501
|
1000
NK: ORGAPP
TBLCSGDI




1) RETURN: (Return Result)
Cumulative Total Cost: 52715,1
Cumulative CPU Cost: 1,14322e+011
Cumulative I/O Cost: 1386
Cumulative Re-Total Cost: 51117,4
Cumulative Re-CPU Cost: 1,14296e+011
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 1913,63
Estimated Bufferpool Buffers: 66
Remote communication cost: 1963,23

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.77 : n030303

Input Streams:
-------------
14) From Operator #2

Estimated number of rows: 1,6e+006
Number of columns: 10
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+ERSTELLERNAME
+ANFORDERERNAME+AGGSUMME+AGGMENGE


2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 52715,1
Cumulative CPU Cost: 1,14322e+011
Cumulative I/O Cost: 1386
Cumulative Re-Total Cost: 51117,4
Cumulative Re-CPU Cost: 1,14296e+011
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 1913,63
Estimated Bufferpool Buffers: 66
Remote communication cost: 1963,23

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: Yes
Filter Factor: 0,04

Predicate Text:
--------------
(Q5.$C4 = Q6.PKCPNR)


Input Streams:
-------------
9) From Operator #3

Estimated number of rows: 40000
Number of columns: 10
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+$C7+$C6+ATTNAME+PKCPNR

13) From Operator #25

Estimated number of rows: 40
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
14) To Operator #1

Estimated number of rows: 1,6e+006
Number of columns: 10
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+ERSTELLERNAME
+ANFORDERERNAME+AGGSUMME+AGGMENGE


3) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 3604,14
Cumulative CPU Cost: 3,01896e+009
Cumulative I/O Cost: 852
Cumulative Re-Total Cost: 2491,26
Cumulative Re-CPU Cost: 3,0023e+009
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 1908,45
Estimated Bufferpool Buffers: 33
Remote communication cost: 1302,28

Arguments:
---------
EARLYOUT: (Early Out flag)
FALSE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
JN INPUT: (Join input leg)
OUTER

Predicates:
----------
3) Predicate used in Join
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0,04

Predicate Text:
--------------
(Q5.$C3 = Q7.PKCPNR)


Input Streams:
-------------
4) From Operator #4

Estimated number of rows: 1000
Number of columns: 8
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+$C7+$C6

8) From Operator #21

Estimated number of rows: 40
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
9) To Operator #2

Estimated number of rows: 40000
Number of columns: 10
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+$C7+$C6+ATTNAME+PKCPNR


4) RQUERY: (Remote Query)
Cumulative Total Cost: 1903,68
Cumulative CPU Cost: 2,26997e+008
Cumulative I/O Cost: 318
Cumulative Re-Total Cost: 1275,61
Cumulative Re-CPU Cost: 2,19966e+008
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 1903,27
Estimated Bufferpool Buffers: 0
Remote communication cost: 641,328

Arguments:
---------
CSERQY : (Remote common subexpression)
FALSE
JN INPUT: (Join input leg)
OUTER
RMTQTXT : (Remote statement)
SELECT A2."FKSSTATUS", A2."PKSBEA", A2."PKCBEANR", A2."FKCPNRERSTELLER", A1."FKCPNRANFORDERER", A1."FKCBELASTUNGSKST", COUNT(*), SYSIBM.SUM( (A0."ATTMENGE" * A0."ATTEPREIS")) FROM "BEAAPP"."TBLPOSITION" A0, "BEAAPP"."TBLPOSINFO" A1, "BEAAPP"."TBLBEA" A2 WHERE (A1."PKSPOSINFO" = A0."FKSPOSINFO") AND (A2."PKSBEA" = A0."FKSBEA") GROUP BY A2."FKSSTATUS", A2."PKSBEA", A2."PKCBEANR", A2."FKCPNRERSTELLER", A1."FKCPNRANFORDERER", A1."FKCBELASTUNGSKST" ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC
RMTSEVER: (Remote server)
TBEATE
STREAM : (Remote stream)
FALSE

Input Streams:
-------------
1) From Object BEAAPP.TBLBEA

Estimated number of rows: 1000
Number of columns: 5
Subquery predicate ID: Not Applicable

2) From Object BEAAPP.TBLPOSIN

Estimated number of rows: 1000
Number of columns: 6
Subquery predicate ID: Not Applicable

3) From Object BEAAPP.TBLPOSIT

Estimated number of rows: 1000
Number of columns: 15
Subquery predicate ID: Not Applicable


Output Streams:
--------------
4) To Operator #3

Estimated number of rows: 1000
Number of columns: 8
Subquery predicate ID: Not Applicable

Column Names:
------------
+FKSSTATUS(A)+PKSBEA(A)+PKCBEANR(A)
+FKCPNRERSTELLER(A)+FKCPNRANFORDERER(A)
+FKCBELASTUNGSKST(A)+$C7+$C6


21) TBSCAN: (Table Scan)
Cumulative Total Cost: 5,37297
Cumulative CPU Cost: 1,22974e+007
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 1,21565
Cumulative Re-CPU Cost: 2,78234e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 5,1834
Estimated Bufferpool Buffers: 33
Remote communication cost: 660,953

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD

Predicates:
----------
3) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0,04

Predicate Text:
--------------
(Q5.$C3 = Q7.PKCPNR)


Input Streams:
-------------
7) From Operator #22

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
8) To Operator #3

Estimated number of rows: 40
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


22) TEMP : (Create Temporary Table)
Cumulative Total Cost: 4,1445
Cumulative CPU Cost: 9,48576e+006
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 0
Cumulative Re-CPU Cost: 0
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 4,1445
Estimated Bufferpool Buffers: 33
Remote communication cost: 660,953

Arguments:
---------
CSETEMP : (Temp over common sub-expression flag)
FALSE
SLOWMAT : (Slow Materialization flag)
FALSE
TEMPSIZE: (Temporary Table Page Size)
4096

Input Streams:
-------------
6) From Operator #23

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
7) To Operator #21

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


23) RQUERY: (Remote Query)
Cumulative Total Cost: 3,6152
Cumulative CPU Cost: 8,27432e+006
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 0,753126
Cumulative Re-CPU Cost: 1,72372e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0,0228574
Estimated Bufferpool Buffers: 501
Remote communication cost: 660,953

Arguments:
---------
CSERQY : (Remote common subexpression)
FALSE
RMTQTXT : (Remote statement)
SELECT A0."PKCPNR", A0."ATTNAME" FROM "ORGAPP"."TBLCSGDIR" A0
RMTSEVER: (Remote server)
CSGORG
STREAM : (Remote stream)
FALSE

Input Streams:
-------------
5) From Object ORGAPP.TBLCSGDI

Estimated number of rows: 1000
Number of columns: 37
Subquery predicate ID: Not Applicable


Output Streams:
--------------
6) To Operator #22

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


25) TBSCAN: (Table Scan)
Cumulative Total Cost: 5,37297
Cumulative CPU Cost: 1,22974e+007
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 1,21565
Cumulative Re-CPU Cost: 2,78234e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 5,1834
Estimated Bufferpool Buffers: 33
Remote communication cost: 660,953

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: Yes
Filter Factor: 0,04

Predicate Text:
--------------
(Q5.$C4 = Q6.PKCPNR)


Input Streams:
-------------
12) From Operator #26

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
13) To Operator #2

Estimated number of rows: 40
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


26) TEMP : (Create Temporary Table)
Cumulative Total Cost: 4,1445
Cumulative CPU Cost: 9,48576e+006
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 0
Cumulative Re-CPU Cost: 0
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 4,1445
Estimated Bufferpool Buffers: 33
Remote communication cost: 660,953

Arguments:
---------
CSETEMP : (Temp over common sub-expression flag)
FALSE
SLOWMAT : (Slow Materialization flag)
FALSE
TEMPSIZE: (Temporary Table Page Size)
4096

Input Streams:
-------------
11) From Operator #27

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Output Streams:
--------------
12) To Operator #25

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


27) RQUERY: (Remote Query)
Cumulative Total Cost: 3,6152
Cumulative CPU Cost: 8,27432e+006
Cumulative I/O Cost: 501
Cumulative Re-Total Cost: 0,753126
Cumulative Re-CPU Cost: 1,72372e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0,0228574
Estimated Bufferpool Buffers: 501
Remote communication cost: 660,953

Arguments:
---------
CSERQY : (Remote common subexpression)
FALSE
RMTQTXT : (Remote statement)
SELECT A0."PKCPNR", A0."ATTNAME" FROM "ORGAPP"."TBLCSGDIR" A0
RMTSEVER: (Remote server)
CSGORG
STREAM : (Remote stream)
FALSE

Input Streams:
-------------
10) From Object ORGAPP.TBLCSGDI

Estimated number of rows: 1000
Number of columns: 37
Subquery predicate ID: Not Applicable


Output Streams:
--------------
11) To Operator #26

Estimated number of rows: 1000
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+ATTNAME+PKCPNR


Objects Used in Access Plan:
---------------------------

Schema: BEAAPP
Name: TBLBEA
Type: Nickname
Time of creation: 2003-07-23-17.01.21.921005
Last statistics update:
Number of columns: 5
Number of rows: 1000
Width of rows: 85
Number of buffer pool pages: 29
Distinct row values: No
Tablespace name:
Tablespace overhead: 24,100000
Tablespace transfer rate: 0,900000
Prefetch page count: 16
Container extent page count: 32

Schema: BEAAPP
Name: TBLPOSIN
Type: Nickname
Time of creation: 2003-07-23-17.01.22.468000
Last statistics update:
Number of columns: 6
Number of rows: 1000
Width of rows: 55
Number of buffer pool pages: 92
Distinct row values: No
Tablespace name:
Tablespace overhead: 24,100000
Tablespace transfer rate: 0,900000
Prefetch page count: 16
Container extent page count: 32

Schema: BEAAPP
Name: TBLPOSIT
Type: Nickname
Time of creation: 2003-07-23-17.01.22.578000
Last statistics update:
Number of columns: 15
Number of rows: 1000
Width of rows: 84
Number of buffer pool pages: 201
Distinct row values: No
Tablespace name:
Tablespace overhead: 24,100000
Tablespace transfer rate: 0,900000
Prefetch page count: 16
Container extent page count: 32

Schema: ORGAPP
Name: TBLCSGDI
Type: Nickname
Time of creation: 2002-09-13-13.50.35.703000
Last statistics update:
Number of columns: 37
Number of rows: 1000
Width of rows: 129
Number of buffer pool pages: 501
Distinct row values: No
Tablespace name:
Tablespace overhead: 0,000000
Tablespace transfer rate: 0,000000
Prefetch page count: 0
Container extent page count: 0

------------------------
By the way - the explain output states, that there are no statistics run on the table.
That's not true, they are all up to date, but the database where the query runs is a federated one.
Could that be the problems - that he doesn't get the statistics from the original database ???

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MohanV
  • Locked
  • Question Question
Using table functions in Query
Replies
5
Views
647
fredericofonseca
fredericofonseca
hugoscp1
  • Locked
  • Question Question
sql query
Replies
2
Views
369
hugoscp1
hugoscp1
Kuldeepg
  • Locked
  • Question Question
While writing query to convert deci
Replies
1
Views
441
johnherman
johnherman
THEROL
  • Locked
  • Question Question
Error: CLI0115E Invalid cursor state. SQLSTATE=24000 while querying programmatically
Replies
0
Views
664
THEROL
THEROL
msol25
  • Locked
  • Question Question
Please help for query list all tables in DB2 using squirrel tool
Replies
0
Views
309
msol25
msol25

Part and Inventory Search

Sponsor

Back
Top