Question about faroffpos

N. - Neil - Briggs

Question about faroffpos
Dear All,

I have just ran the query that is listed in the administration guide that
will help you determine when a tablespace/index space will require
reorganizing.

SELECT I.DBNAME, I.NAME
FROM SYSIBM.SYSINDEXES I,
SYSIBM.SYSINDEXPART P
WHERE
I.CREATOR = 'SQLUSRH'
AND I.NAME = P.IXNAME
AND I.CREATOR = P.IXCREATOR
AND (P.FAROFFPOSF > 0 OR NEAROFFPOSF > 0)
;

I understand the basic theory that this figure indicates the rows that are
either near off or far off from their optimal position. But I have just
performed a reorg on a tablespace (index all). Now I notice that the second
index on the table (not the clustering index) still has a faroffposf greater
than 0. Do I have to increase the pctfree of the index in order to have the
perfect situation?

The reason for the question is that the DB2 housekeeping is based on these
queries, from which the reorgs are generated (by use of a rexx), and there
seems little point in reorging the same tablespace over and over again for
no reason when all other statistics look good ( i.e. leafdist and the
cluster ratio of the clustering index).

Any thoughts on this would be greatly appreciated.

Kindest Regards
Neil



Marcus Davage

Re: Question about faroffpos
(in response to N. - Neil - Briggs)
Neil,

To quote my esteemed colleague, Mike Holmans,
"FAROFFPOS is a measure of the organisation of the data in the underlying
table. It assumes that the index in question is the clustering index, and
then tells you how many of the rows in the table are ill-placed given that
assumption. In other words, if the index is not the clustering index, the
value of FAROFFPOS tells you absolutely nothing of value."

See also Chapter 2-17 of Utility Guide and Reference.

Marcus

-----Original Message-----
From: Briggs, N. - Neil - [mailto:[login to unmask email]
Sent: 05 January 2000 10:20
To: [login to unmask email]
Subject: Question about faroffpos


Dear All,

I have just ran the query that is listed in the administration guide that
will help you determine when a tablespace/index space will require
reorganizing.

SELECT I.DBNAME, I.NAME
FROM SYSIBM.SYSINDEXES I,
SYSIBM.SYSINDEXPART P
WHERE
I.CREATOR = 'SQLUSRH'
AND I.NAME = P.IXNAME
AND I.CREATOR = P.IXCREATOR
AND (P.FAROFFPOSF > 0 OR NEAROFFPOSF > 0)
;

I understand the basic theory that this figure indicates the rows that are
either near off or far off from their optimal position. But I have just
performed a reorg on a tablespace (index all). Now I notice that the second
index on the table (not the clustering index) still has a faroffposf greater
than 0. Do I have to increase the pctfree of the index in order to have the
perfect situation?

The reason for the question is that the DB2 housekeeping is based on these
queries, from which the reorgs are generated (by use of a rexx), and there
seems little point in reorging the same tablespace over and over again for
no reason when all other statistics look good ( i.e. leafdist and the
cluster ratio of the clustering index).

Any thoughts on this would be greatly appreciated.

Kindest Regards
Neil






---------------------------------------------------------------------------
This Email is intended for the exclusive use of the addressee only.
If you are not the intended recipient, you should not use the
contents nor disclose them to any other person and you should
immediately notify the sender and delete the Email.

Lloyds TSB Bank plc is registered in England and Wales no. 2065.
Registered office: 71 Lombard Street, London, EC3P 3BS.

Lloyds TSB Scotland plc is registered in Scotland no. 95237.
Registered office: Henry Duncan House, 120 George Street,
Edinburgh, EH2 4LH.
---------------------------------------------------------------------------



Michael Ebert

Re: Question about faroffpos
(in response to Marcus Davage)
For REORGing, these statistics make sense ONLY in relation to the clustering
index (the utilities guide refers to this only obliquely as "...statistics may
appear even worse after reorging for other indexes"). NEAR/FAROFFPOSF and
CLUSTERRATIO essentially are measures for out-of-sort-orderness (it also says
somewhere that the formula for calculating CLUSTERRATIO has not been published),
and the sort order achieved by REORG is determined by the clustering index only.
So for an automated job you should make sure that 1) you have an IXC for each
TS, and 2) that you disregard statistics for non-clustering IXs when determining
whether to reorg the TS.
Having an IXC also makes for the simplest, most effective REORG JCL (no work
datasets) & parameters (SORTDATA SORTKEYS NOSYSREC).
Having more than one table per TS will make these numbers more difficult to
interpret. The IXC will determine the sort order for ALL tables, even if it
relates only to a small table (on second thought, this should be applicable only
if you use the SORTDATA option - it should be different if you unload in index
order). Maybe someone with inside knowledge of the REORG utility can explain
more on the effect REORG has on these statistics, depending on SORTDATA YES/NO
and the TS structure (IXC yes/no, one table/n tables).

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: "Briggs, N. - Neil -" <[login to unmask email]> on 05/01/2000 10:19 GMT



Please respond to DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]


cc: (bcc: Michael Ebert/MUC/AMADEUS)




Subject: Question about faroffpos




Dear All,

I have just ran the query that is listed in the administration guide that
will help you determine when a tablespace/index space will require
reorganizing.

SELECT I.DBNAME, I.NAME
FROM SYSIBM.SYSINDEXES I,
SYSIBM.SYSINDEXPART P
WHERE
I.CREATOR = 'SQLUSRH'
AND I.NAME = P.IXNAME
AND I.CREATOR = P.IXCREATOR
AND (P.FAROFFPOSF > 0 OR NEAROFFPOSF > 0)
;

I understand the basic theory that this figure indicates the rows that are
either near off or far off from their optimal position. But I have just
performed a reorg on a tablespace (index all). Now I notice that the second
index on the table (not the clustering index) still has a faroffposf greater
than 0. Do I have to increase the pctfree of the index in order to have the
perfect situation?

The reason for the question is that the DB2 housekeeping is based on these
queries, from which the reorgs are generated (by use of a rexx), and there
seems little point in reorging the same tablespace over and over again for
no reason when all other statistics look good ( i.e. leafdist and the
cluster ratio of the clustering index).

Any thoughts on this would be greatly appreciated.

Kindest Regards
Neil