Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7

Roy Boxwell

Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
very simply put - These columns are no longer supported. You should use
the F columns eg CARDF, FAROFFPOSF etc If you look in the manual SQL
Reference you will see that these field
names no longer exist in the table descriptions. This means that the
contents "might be ok" or "might be rubbish" or "might be 10 years
old"....


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de





Neff Stephen R <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
05.01.2006 15:52
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] runstats columns faroffpos& zos/390 db2v7


Hello,
Here is my situation. I run runstats with the following syntax
RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25
SHRLEVEL(CHANGE).
I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns
still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The
STATSTIME is populated with the time the runstats job was run. Why would
these columns not have stats? I do the same syntax with other table spaces
and the columns do have stats. Does RUNSTATS populate these columns
differently and if so why.
Thank you
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Neff

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Roy Boxwell)
Hello , Thank you for your response. I looked at the stats that were
populated and compared the columns
CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF and
they were equal. It appears that RUNSTATS did populate stats correctly for
these non F columns when it did populate them.

thanks
Steve

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Roy Boxwell
Sent: Thursday, January 05, 2006 9:57 AM
To: [login to unmask email]
Subject: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7



very simply put - These columns are no longer supported. You should use the
F columns eg CARDF, FAROFFPOSF etc If you look in the manual SQL Reference
you will see that these field
names no longer exist in the table descriptions. This means that the
contents "might be ok" or "might be rubbish" or "might be 10 years old"....


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de




Neff Stephen R <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>


05.01.2006 15:52
Bitte antworten an DB2 Database Discussion list at IDUG



An: [login to unmask email]
Kopie:
Thema: [DB2-L] runstats columns faroffpos& zos/390 db2v7



Hello,

Here is my situation. I run runstats with the following syntax
RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25
SHRLEVEL(CHANGE).
I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns
still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The
STATSTIME is populated with the time the runstats job was run. Why would
these columns not have stats? I do the same syntax with other table spaces
and the columns do have stats. Does RUNSTATS populate these columns
differently and if so why.


Thank you


----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm



----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kirk Hampton

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Steve Neff)
Hi Steve,

We are DB2 V7 on z/OS as well. We have seen something like this,

and I thought that RUNSTATS was leaving some of the stats columns

un-populated on tables where we specify SAMPLE 25. But I just found this

little gotcha in the V7 Utility Guide manual,



RUNSTATS sets the following columns to -1 for large table spaces.



* CARD in SYSTABLES

* CARD in SYSINDEXPART

* FAROFFPOS in SYSINDEXPART

* NEAROFFPOS in SYSINDEXPART

* FIRSTKEYCARD in SYSINDEXES

* FULLKEYCARD in SYSINDEXES



It does not say what criteria constitutes a "large" table space. Nor

does it mention the ...F column names. Can someone from IBM please

clarify, is it the fact that Steve's table is larger than a certain

criteria, or is it because of the sampling, that some stats are not

populated ?



Kirk Hampton

DB2 z/OS Sysprog

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS

Capgemini Energy

Dallas, Texas











________________________________



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On

Behalf Of Neff Stephen R

Sent: Thursday, January 05, 2006 10:05 AM

To: [login to unmask email]

Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos&

zos/390 db2v7





Hello , Thank you for your response. I looked at the stats that were

populated and compared the columns

CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF

and they were equal. It appears that RUNSTATS did populate stats

correctly for these non F columns when it did populate them.



thanks

Steve



-----Original Message-----

From: DB2 Data Base Discussion List [mailto:[login to unmask email]

On Behalf Of Roy Boxwell

Sent: Thursday, January 05, 2006 9:57 AM

To: [login to unmask email]

Subject: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos&

zos/390 db2v7







very simply put - These columns are no longer supported. You

should use the F columns eg CARDF, FAROFFPOSF etc If you look in the

manual SQL Reference you will see that these field

names no longer exist in the table descriptions. This means that

the contents "might be ok" or "might be rubbish" or "might be 10 years

old"....





Roy Boxwell

SOFTWARE ENGINEERING GMBH

-Product Development-

Robert-Stolz-Strasse 5

40470 Duesseldorf/Germany

Tel. +49 (0)211 96149-0

Fax +49 (0)211 96149-35

E-mail [login to unmask email]

Homepage www.seg.de









Neff Stephen R <[login to unmask email]>

Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>



05.01.2006 15:52

Bitte antworten an DB2 Database Discussion list at IDUG







An: [login to unmask email]

Kopie:

Thema: [DB2-L] runstats columns faroffpos& zos/390 db2v7









Hello,



Here is my situation. I run runstats with the following

syntax

RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL)

SAMPLE 25 SHRLEVEL(CHANGE).

I then check the index statistics in SYSIBM.SYSINDEXPART.

Certain columns still have a -1 and these columns are CARD FAROFFPOS

& NEAROFFPOS. The STATSTIME is populated with the time the runstats job

was run. Why would these columns not have stats? I do the same syntax

with other table spaces and the columns do have stats. Does RUNSTATS

populate these columns differently and if so why.



Thank you



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

--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the

archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.

From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at

http://www.idugdb2-l.org. The IDUG List Admins can be reached at

[login to unmask email] Find out the latest on IDUG conferences

at http://conferences.idug.org/index.cfm



Confidentiality Notice: This email message, including any attachments, contains or may contain confidential information intended only for the addressee. If you are not an intended recipient of this message, be advised that any reading, dissemination, forwarding, printing, copying or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately by reply message and delete this email message and any attachments from your system.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Kirk Hampton)
At one time in DB2 history there was quite a diffrence between
RUNSTATS TABLESPACE db.ts TABLE ALL INDEX ALL
vs
RUNSTATS INDEX (ALL) TABLESPACE db.ts

There are many stats that the second case updates but the first case does not.
In the V8 utility manual this appears to be still documented in
section 2.27.24 Reviewing RUNSTATS output Table 101.

In the good old days if proper secondary index selection by the optimizer was desired RUNSTATS INDEX was required.

It appears that some of this may still be the case.
This comment is a bit out side of my normal work responsibilities however


Table 101. Catalog tables that RUNSTATS updates


Keyword


UPDATE option


HISTORY option

Catalog table that
RUNSTATS updates

TABLESPACE





UPDATE ALL





HISTORY ALL
(4)




SYSTABLESPACE
SYSTABLEPART(1)
SYSTABLES(1)
SYSTABSTATS(1,2)
SYSLOBSTATS(3)





INDEX







UPDATE ALL







HISTORY ALL
(4)






SYSCOLUMNS
SYSCOLDIST
SYSCOLDISTSTATS(2)
SYSCOLSTATS(2)
SYSINDEXES
SYSINDEXPART
SYSINDEXSTATS(2 )



Notice the impact column lists are not at all the same.


Kirk Hampton <[login to unmask email]> wrote:
Hi Steve,
We are DB2 V7 on z/OS as well. We have seen something like this, and I thought that RUNSTATS was leaving some of the stats columns un-populated on tables where we specify SAMPLE 25. But I just found this little gotcha in the V7 Utility Guide manual,

RUNSTATS sets the following columns to -1 for large table spaces.
CARD in SYSTABLES
CARD in SYSINDEXPART
FAROFFPOS in SYSINDEXPART
NEAROFFPOS in SYSINDEXPART
FIRSTKEYCARD in SYSINDEXES
FULLKEYCARD in SYSINDEXES

It does not say what criteria constitutes a "large" table space. Nor does it mention the ...F column names. Can someone from IBM please clarify, is it the fact that Steve's table is larger than a certain criteria, or is it because of the sampling, that some stats are not populated ?

Kirk Hampton
DB2 z/OS Sysprog
IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS
Capgemini Energy
Dallas, Texas





---------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Neff Stephen R
Sent: Thursday, January 05, 2006 10:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7



Hello , Thank you for your response. I looked at the stats that were populated and compared the columns
CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF and they were equal. It appears that RUNSTATS did populate stats correctly for these non F columns when it did populate them.

thanks
Steve

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: Thursday, January 05, 2006 9:57 AM
To: [login to unmask email]
Subject: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7



very simply put - These columns are no longer supported. You should use the F columns eg CARDF, FAROFFPOSF etc If you look in the manual SQL Reference you will see that these field
names no longer exist in the table descriptions. This means that the contents "might be ok" or "might be rubbish" or "might be 10 years old"....


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de



Neff Stephen R <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]> 05.01.2006 15:52
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] runstats columns faroffpos& zos/390 db2v7


Hello, Here is my situation. I run runstats with the following syntax
RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25 SHRLEVEL(CHANGE).
I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The STATSTIME is populated with the time the runstats job was run. Why would these columns not have stats? I do the same syntax with other table spaces and the columns do have stats. Does RUNSTATS populate these columns differently and if so why. Thank you
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm Confidentiality Notice: This email message, including any attachments, contains or may contain confidential information intended only for the addressee. If you are not an intended recipient of this message, be advised that any reading, dissemination, forwarding, printing, copying or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately by reply message and delete this email message and any attachments from your system.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Alan Smith

Re: runstats columns faroffpos& zos/390 db2v7
(in response to Avram Friedman)
The columns all have floating point equivalents - if you look at CARDF, FAROFFPOSF, and
NEAROFFPOSF, they should all be populated.

Alan

On Thu, 5 Jan 2006 09:52:37 -0500, Neff Stephen R <[login to unmask email]> wrote:

>Hello,
>
> Here is my situation. I run runstats with the following syntax
>RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25
>SHRLEVEL(CHANGE).
>I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns
>still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The
>STATSTIME is populated with the time the runstats job was run. Why would
>these columns not have stats? I do the same syntax with other table spaces
>and the columns do have stats. Does RUNSTATS populate these columns
>differently and if so why.
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Neff

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Alan Smith)
Hello, thank you for your response. The table I saw this in has a Maximum
row length : 32714 and uses a 32k buffer pool. The table is partitioned. It
was not defined as large table. We do have stats for CARD in SYSTABLES and
FIRSTKEYCARD, FULLKEYCARD in sysindexes. But no stats for CARD in
SYSINDEXPART
FAROFFPOS in SYSINDEXPART
NEAROFFPOS in SYSINDEXPART

Thank again for all the responses



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Kirk Hampton
Sent: Thursday, January 05, 2006 11:54 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390
db2v7


Hi Steve,
We are DB2 V7 on z/OS as well. We have seen something like this, and
I thought that RUNSTATS was leaving some of the stats columns un-populated
on tables where we specify SAMPLE 25. But I just found this little gotcha in
the V7 Utility Guide manual,

RUNSTATS sets the following columns to -1 for large table spaces.
CARD in SYSTABLES
CARD in SYSINDEXPART
FAROFFPOS in SYSINDEXPART
NEAROFFPOS in SYSINDEXPART
FIRSTKEYCARD in SYSINDEXES
FULLKEYCARD in SYSINDEXES
It does not say what criteria constitutes a "large" table space. Nor does it
mention the ...F column names. Can someone from IBM please clarify, is it
the fact that Steve's table is larger than a certain criteria, or is it
because of the sampling, that some stats are not populated ?

Kirk Hampton
DB2 z/OS Sysprog
IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS
Capgemini Energy
Dallas, Texas







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Neff Stephen R
Sent: Thursday, January 05, 2006 10:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390
db2v7


Hello , Thank you for your response. I looked at the stats that were
populated and compared the columns
CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF and
they were equal. It appears that RUNSTATS did populate stats correctly for
these non F columns when it did populate them.

thanks
Steve
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Roy Boxwell
Sent: Thursday, January 05, 2006 9:57 AM
To: [login to unmask email]
Subject: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7



very simply put - These columns are no longer supported. You should use the
F columns eg CARDF, FAROFFPOSF etc If you look in the manual SQL Reference
you will see that these field
names no longer exist in the table descriptions. This means that the
contents "might be ok" or "might be rubbish" or "might be 10 years old"....


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de



Neff Stephen R <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
05.01.2006 15:52
Bitte antworten an DB2 Database Discussion list at IDUG

An: [login to unmask email]
Kopie:
Thema: [DB2-L] runstats columns faroffpos& zos/390 db2v7



Hello,
Here is my situation. I run runstats with the following syntax
RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25
SHRLEVEL(CHANGE).
I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns
still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The
STATSTIME is populated with the time the runstats job was run. Why would
these columns not have stats? I do the same syntax with other table spaces
and the columns do have stats. Does RUNSTATS populate these columns
differently and if so why.
Thank you
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm Confidentiality Notice: This email
message, including any attachments, contains or may contain confidential
information intended only for the addressee. If you are not an intended
recipient of this message, be advised that any reading, dissemination,
forwarding, printing, copying or other use of this message or its
attachments is strictly prohibited. If you have received this message in
error, please notify the sender immediately by reply message and delete this
email message and any attachments from your system.
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Steve Neff)
Kirk,

the CREATE TABLESPACE statement includes the option LARGE which is required
for tablespaces which need to contain more than 64 partitions.

--Phil

On 1/5/06, Kirk Hampton <[login to unmask email]> wrote:
>
> Hi Steve,
> We are DB2 V7 on z/OS as well. We have seen something like this,
> and I thought that RUNSTATS was leaving some of the stats columns
> un-populated on tables where we specify SAMPLE 25. But I just found this
> little gotcha in the V7 Utility Guide manual,
>
>
> RUNSTATS sets the following columns to -1 for large table spaces.
>
>
> - CARD in SYSTABLES
> - CARD in SYSINDEXPART
> - FAROFFPOS in SYSINDEXPART
> - NEAROFFPOS in SYSINDEXPART
> - FIRSTKEYCARD in SYSINDEXES
> - FULLKEYCARD in SYSINDEXES
>
> It does not say what criteria constitutes a "large" table space. Nor does
> it mention the ...F column names. Can someone from IBM please clarify, is it
> the fact that Steve's table is larger than a certain criteria, or is it
> because of the sampling, that some stats are not populated ?
>
>
> Kirk Hampton
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *Neff Stephen R
> Hello , Thank you for your response. I looked at the stats that were
> populated and compared the columns
> CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF
> and they were equal. It appears that RUNSTATS did populate stats correctly
> for these non F columns when it did populate them.
>
> thanks
> Steve
>
> -----Original Message-----
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *Roy Boxwell
>
> very simply put - These columns are no longer supported. You should use
> the F columns eg CARDF, FAROFFPOSF etc If you look in the manual SQL
> Reference you will see that these field
> names no longer exist in the table descriptions. This means that the
> contents "might be ok" or "might be rubbish" or "might be 10 years old"....
>
>
> *Roy Boxwell*
>
>
> *Neff Stephen R <[login to unmask email]>*
>
> Hello,
>
> Here is my situation. I run runstats with the following syntax
> RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL) SAMPLE 25
> SHRLEVEL(CHANGE).
> I then check the index statistics in SYSIBM.SYSINDEXPART. Certain columns
> still have a -1 and these columns are CARD FAROFFPOS & NEAROFFPOS. The
> STATSTIME is populated with the time the runstats job was run. Why would
> these columns not have stats? I do the same syntax with other table spaces
> and the columns do have stats. Does RUNSTATS populate these columns
> differently and if so why.
>
> Thank you
>
>

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kirk Hampton

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Philip Sevetson)
Hi Phil,

Yes, I know about the LARGE parameter, but since the manual has

"large" in lower case, I am not sure if that is really what they are

referring to. Also, since the LARGE option of CREATE TABLESPACE is

deprecated since Version 6 in favor of the DSSIZE option, shouldn't the

manual be more specific, as in other places where it says "table spaces

that are defined with LARGE or a DSSIZE greater than 2G" ?



My other question still remains unanswered, are there any stats left

unpopulated purely as a result of specifying SAMPLE 25 in the RUNSTATS

utility ?



Kirk Hampton

DB2 z/OS Sysprog

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS

Capgemini Energy

Dallas, Texas



________________________________



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On

Behalf Of Philip Sevetson

Sent: Friday, January 06, 2006 2:47 PM

To: [login to unmask email]

Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos&

zos/390 db2v7





Kirk,



the CREATE TABLESPACE statement includes the option LARGE which is

required for tablespaces which need to contain more than 64 partitions.



--Phil



On 1/5/06, Kirk Hampton <[login to unmask email]> wrote:



Hi Steve,

We are DB2 V7 on z/OS as well. We have seen something

like this, and I thought that RUNSTATS was leaving some of the stats

columns un-populated on tables where we specify SAMPLE 25. But I just

found this little gotcha in the V7 Utility Guide manual,



RUNSTATS sets the following columns to -1 for large table

spaces.







* CARD in SYSTABLES

* CARD in SYSINDEXPART

* FAROFFPOS in SYSINDEXPART

* NEAROFFPOS in SYSINDEXPART

* FIRSTKEYCARD in SYSINDEXES

* FULLKEYCARD in SYSINDEXES



It does not say what criteria constitutes a "large" table space.

Nor does it mention the ...F column names. Can someone from IBM please

clarify, is it the fact that Steve's table is larger than a certain

criteria, or is it because of the sampling, that some stats are not

populated ?



Kirk Hampton













From: DB2 Data Base Discussion List [mailto: [login to unmask email]

<mailto:[login to unmask email]> ] On Behalf Of Neff Stephen R

Hello , Thank you for your response. I looked at the stats that

were populated and compared the columns



CARD FAROFFPOS & NEAROFFPOS with CARDF FAROFFPOSF & NEAROFFPOSF

and they were equal. It appears that RUNSTATS did populate stats

correctly for these non F columns when it did populate them.



thanks

Steve



-----Original Message-----

From: DB2 Data Base Discussion List [mailto: [login to unmask email]

<mailto:[login to unmask email]> ] On Behalf Of Roy Boxwell





very simply put - These columns are no longer supported. You

should use the F columns eg CARDF, FAROFFPOSF etc If you look in the

manual SQL Reference you will see that these field

names no longer exist in the table descriptions. This means that

the contents "might be ok" or "might be rubbish" or "might be 10 years

old"....





Roy Boxwell







Neff Stephen R <[login to unmask email]>





Hello,



Here is my situation. I run runstats with the following

syntax

RUNSTATS TABLESPACE database.tablespace INDEX(ALL) TABLE(ALL)

SAMPLE 25 SHRLEVEL(CHANGE).

I then check the index statistics in SYSIBM.SYSINDEXPART.

Certain columns still have a -1 and these columns are CARD FAROFFPOS

& NEAROFFPOS. The STATSTIME is populated with the time the runstats job

was run. Why would these columns not have stats? I do the same syntax

with other table spaces and the columns do have stats. Does RUNSTATS

populate these columns differently and if so why.



Thank you















--

--Phil Sevetson

IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for

OS/390

IBM Certified Database Administrator -- DB2 Universal Database V8.1 for

zOS

Wakefern Food Corporation

Edison, NJ, USA

ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL

ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

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

--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the

archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.

From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at

http://www.idugdb2-l.org. The IDUG List Admins can be reached at

[login to unmask email] Find out the latest on IDUG conferences

at http://conferences.idug.org/index.cfm



Confidentiality Notice: This email message, including any attachments, contains or may contain confidential information intended only for the addressee. If you are not an intended recipient of this message, be advised that any reading, dissemination, forwarding, printing, copying or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately by reply message and delete this email message and any attachments from your system.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Kirk Hampton)
Hey, Kirk --
I like your suggestion about the manual's proper phrasing. I hope Roger or
someone's listening, because I agree that the documentation as you've
described it needs fixing.

It hasn't been my experience that SAMPLE n leaves anything unpopulated in
the catalog, but I haven't done an exhaustive study. I haven't been much
worried about it, because mostly we only use SAMPLE for our monster,
over-2GB tablespaces (note that this is a rather smaller definition of
"monster" than I imply in a different thread today!).

--Phil


On 1/10/06, Kirk Hampton <[login to unmask email]> wrote:
>
> Hi Phil,
> Yes, I know about the LARGE parameter, but since the manual has
> "large" in lower case, I am not sure if that is really what they are
> referring to. Also, since the LARGE option of CREATE TABLESPACE is
> deprecated since Version 6 in favor of the DSSIZE option, shouldn't the
> manual be more specific, as in other places where it says "table spaces
> that are defined with LARGE or a DSSIZE greater than 2G" ?
>
> My other question still remains unanswered, are there any stats left
> unpopulated purely as a result of specifying SAMPLE 25 in the RUNSTATS
> utility ?
>
> Kirk Hampton
> DB2 z/OS Sysprog
> IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS
> Capgemini Energy
> Dallas, Texas
>
> ------------------------------
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *Philip Sevetson
> *Sent:* Friday, January 06, 2006 2:47 PM
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos&
> zos/390 db2v7
>
>
> Kirk,
>
> the CREATE TABLESPACE statement includes the option LARGE which is
> required for tablespaces which need to contain more than 64 partitions.
>
> --Phil
>
> On 1/5/06, Kirk Hampton <[login to unmask email]> wrote:
> >
> > Hi Steve,
> > We are DB2 V7 on z/OS as well. We have seen something like this,
> > and I thought that RUNSTATS was leaving some of the stats columns
> > un-populated on tables where we specify SAMPLE 25. But I just found this
> > little gotcha in the V7 Utility Guide manual,
> >
> >
> > RUNSTATS sets the following columns to -1 for large table spaces.
> >
> >
> > - CARD in SYSTABLES
> > - CARD in SYSINDEXPART
> > - FAROFFPOS in SYSINDEXPART
> > - NEAROFFPOS in SYSINDEXPART
> > - FIRSTKEYCARD in SYSINDEXES
> > - FULLKEYCARD in SYSINDEXES
> >
> > It does not say what criteria constitutes a "large" table space. Nor
> > does it mention the ...F column names. Can someone from IBM please clarify,
> > is it the fact that Steve's table is larger than a certain criteria, or is
> > it because of the sampling, that some stats are not populated ?
> >
> >
> > Kirk Hampton
> >
> >
>
>


--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: Antwort: [DB2-L] runstats columns faroffpos& zos/390 db2v7
(in response to Philip Sevetson)
Hello Kirk,

Here's another suggestion: Rather than rely on Roger or anybody from
IBM doc listening and taking action, you could go to IBM DB2's Reader's
comment site and share your comments.

www.ibm.com/software/data/rcf

The doc folks are a very responsive and appreciative bunch.

Dave


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Philip Sevetson
Sent: Tuesday, January 10, 2006 12:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390
db2v7


Hey, Kirk --
I like your suggestion about the manual's proper phrasing. I hope Roger or
someone's listening, because I agree that the documentation as you've
described it needs fixing.

It hasn't been my experience that SAMPLE n leaves anything unpopulated in
the catalog, but I haven't done an exhaustive study. I haven't been much
worried about it, because mostly we only use SAMPLE for our monster,
over-2GB tablespaces (note that this is a rather smaller definition of
"monster" than I imply in a different thread today!).

--Phil


On 1/10/06, Kirk Hampton <[login to unmask email]> wrote:
Hi Phil,
Yes, I know about the LARGE parameter, but since the manual has "large"
in lower case, I am not sure if that is really what they are referring to.
Also, since the LARGE option of CREATE TABLESPACE is deprecated since
Version 6 in favor of the DSSIZE option, shouldn't the manual be more
specific, as in other places where it says " table spaces that are defined
with LARGE or a DSSIZE greater than 2G" ?

My other question still remains unanswered, are there any stats left
unpopulated purely as a result of specifying SAMPLE 25 in the RUNSTATS
utility ?

Kirk Hampton
DB2 z/OS Sysprog
IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS
Capgemini Energy
Dallas, Texas




From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Philip Sevetson
Sent: Friday, January 06, 2006 2:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Antwort: [DB2-L] runstats columns faroffpos& zos/390
db2v7


Kirk,

the CREATE TABLESPACE statement includes the option LARGE which is required
for tablespaces which need to contain more than 64 partitions.

--Phil

On 1/5/06, Kirk Hampton <[login to unmask email] > wrote:
Hi Steve,
We are DB2 V7 on z/OS as well. We have seen something like this, and
I thought that RUNSTATS was leaving some of the stats columns un-populated
on tables where we specify SAMPLE 25. But I just found this little gotcha in
the V7 Utility Guide manual,

RUNSTATS sets the following columns to -1 for large table spaces.
CARD in SYSTABLES
CARD in SYSINDEXPART
FAROFFPOS in SYSINDEXPART
NEAROFFPOS in SYSINDEXPART
FIRSTKEYCARD in SYSINDEXES
FULLKEYCARD in SYSINDEXES
It does not say what criteria constitutes a "large" table space. Nor does it
mention the ...F column names. Can someone from IBM please clarify, is it
the fact that Steve's table is larger than a certain criteria, or is it
because of the sampling, that some stats are not populated ?

Kirk Hampton





--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm