Professional Web Applications Themes

SYSIBM Query to Find Cardinality of Indexed Columns - IBM DB2

We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view the cardinality hasn't had its REXX proc updated to pick up changes from v6 so it just show -1 under the cardinality field. Does anyone have a query I can use to get the cardinality from the SYSIBM tables? Thanks....

  1. #1

    Default SYSIBM Query to Find Cardinality of Indexed Columns

    We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    the cardinality hasn't had its REXX proc updated to pick up changes from
    v6 so it just show -1 under the cardinality field.


    Does anyone have a query I can use to get the cardinality from the
    SYSIBM tables?


    Thanks.

    Richard Winston Guest

  2. #2

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:QAi8b.4164656$mA4.575658news.easynews.com...
    > We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    > the cardinality hasn't had its REXX proc updated to pick up changes from
    > v6 so it just show -1 under the cardinality field.
    >
    >
    > Does anyone have a query I can use to get the cardinality from the
    > SYSIBM tables?
    >
    >
    > Thanks.
    >
    Sounds like you are using a 3rd party tool (BMC or Platinum) to do
    statistics. If you run the DB2 supplied runstats utility and look in the
    real DB2 catalog, you will find the correct answer.


    Mark A Guest

  3. #3

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:QAi8b.4164656$mA4.575658news.easynews.com...
    >
    >>We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    >>the cardinality hasn't had its REXX proc updated to pick up changes from
    >>v6 so it just show -1 under the cardinality field.
    >>
    >>
    >>Does anyone have a query I can use to get the cardinality from the
    >>SYSIBM tables?
    >>
    >>
    >>Thanks.
    >>
    >
    > Sounds like you are using a 3rd party tool (BMC or Platinum) to do
    > statistics. If you run the DB2 supplied runstats utility and look in the
    > real DB2 catalog, you will find the correct answer.
    >
    >
    I believe runstats have already been run, that's why I'm looking to pull
    the information the SYSIBM queries.

    Richard Winston Guest

  4. #4

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:8%j8b.1029785$Bf5.147368news.easynews.com...
    > Mark A wrote:
    >
    > > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > > news:QAi8b.4164656$mA4.575658news.easynews.com...
    > >
    > >>We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    > >>the cardinality hasn't had its REXX proc updated to pick up changes from
    > >>v6 so it just show -1 under the cardinality field.
    > >>
    > >>
    > >>Does anyone have a query I can use to get the cardinality from the
    > >>SYSIBM tables?
    > >>
    > >>
    > >>Thanks.
    > >>
    > >
    > > Sounds like you are using a 3rd party tool (BMC or Platinum) to do
    > > statistics. If you run the DB2 supplied runstats utility and look in the
    > > real DB2 catalog, you will find the correct answer.
    > >
    > >
    >
    > I believe runstats have already been run, that's why I'm looking to pull
    > the information the SYSIBM queries.
    >
    I don't know what is going on here. If you have run the runstats utility,
    then the value will not be -1 (which means that runstats has never been run
    on this object). Some shops use mirror catalog tables to reduce contention
    on the real catalog, but if IBM runstats has been run, the cardinality will
    be available in the real catalog.


    Mark A Guest

  5. #5

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:8%j8b.1029785$Bf5.147368news.easynews.com...
    >
    >>Mark A wrote:
    >>
    >>
    >>>"Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    >>>news:QAi8b.4164656$mA4.575658news.easynews.com ...
    >>>
    >>>
    >>>>We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    >>>>the cardinality hasn't had its REXX proc updated to pick up changes from
    >>>>v6 so it just show -1 under the cardinality field.
    >>>>
    >>>>
    >>>>Does anyone have a query I can use to get the cardinality from the
    >>>>SYSIBM tables?
    >>>>
    >>>>
    >>>>Thanks.
    >>>>
    >>>
    >>>Sounds like you are using a 3rd party tool (BMC or Platinum) to do
    >>>statistics. If you run the DB2 supplied runstats utility and look in the
    >>>real DB2 catalog, you will find the correct answer.
    >>>
    >>>
    >>
    >>I believe runstats have already been run, that's why I'm looking to pull
    >>the information the SYSIBM queries.
    >>
    >
    > I don't know what is going on here. If you have run the runstats utility,
    > then the value will not be -1 (which means that runstats has never been run
    > on this object). Some shops use mirror catalog tables to reduce contention
    > on the real catalog, but if IBM runstats has been run, the cardinality will
    > be available in the real catalog.
    >
    >
    According to our DBA, in the transition from v6 to v7, the catalog field
    in which the cardinality is stored changed formats so that it is now a
    floating point number (not sure what it used to be) and the Rexx proc
    that displays the cardinality does not handle floating point numbers.


    I just need to find the cardinality. But I'm not going to run runstats
    because as I've mentioned, it's already been done. The tool we use to
    view the information is not showing the correct cardinality, according
    to our DBA.

    Richard Winston Guest

  6. #6

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    >
    > According to our DBA, in the transition from v6 to v7, the catalog field
    > in which the cardinality is stored changed formats so that it is now a
    > floating point number (not sure what it used to be) and the Rexx proc
    > that displays the cardinality does not handle floating point numbers.
    >
    >
    > I just need to find the cardinality. But I'm not going to run runstats
    > because as I've mentioned, it's already been done. The tool we use to
    > view the information is not showing the correct cardinality, according
    > to our DBA.
    >
    If the tool that displays the DB2 catalog data is not working, then write
    your own SQL statement against the catalog table. If you don't have select
    access to the catalog, you can hassle your DBA about it. The catalog tables
    and their layouts are described in the SQL Reference manual (appendix D in
    the version 7 manual). You can download DB2 manuals at this site in case you
    don't have one:
    [url]http://www-3.ibm.com/software/data/db2/os390/v7books.html[/url]

    BTW, what tool do you use to view the catalog (that is broken)?



    Mark A Guest

  7. #7

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    >>According to our DBA, in the transition from v6 to v7, the catalog field
    >>in which the cardinality is stored changed formats so that it is now a
    >>floating point number (not sure what it used to be) and the Rexx proc
    >>that displays the cardinality does not handle floating point numbers.
    >>
    >>
    >>I just need to find the cardinality. But I'm not going to run runstats
    >>because as I've mentioned, it's already been done. The tool we use to
    >>view the information is not showing the correct cardinality, according
    >>to our DBA.
    >>
    >
    > If the tool that displays the DB2 catalog data is not working, then write
    > your own SQL statement against the catalog table.

    Isn't that what I asking for help on ?

    > If you don't have select
    > access to the catalog, you can hassle your DBA about it. The catalog tables
    > and their layouts are described in the SQL Reference manual (appendix D in
    > the version 7 manual). You can download DB2 manuals at this site in case you
    > don't have one:
    > [url]http://www-3.ibm.com/software/data/db2/os390/v7books.html[/url]
    >

    Ok, I was looking for a quick query but it looks like I'll have to do
    some research. Thanks.

    > BTW, what tool do you use to view the catalog (that is broken)?
    >
    I don't actually know. It's option D off our ISPF menu. But it doesn't
    have a vendor's name anywhere after that. I'm wondering if it's our own
    front end to the IBM utilities. I don't imagine that IBM wouldn't have
    updated the Rexx procs to display the correct cardinality.

    Richard Winston Guest

  8. #8

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns


    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:_Bk8b.1031330$Bf5.147600news.easynews.com...
    > Mark A wrote:
    >
    > >>According to our DBA, in the transition from v6 to v7, the catalog field
    > >>in which the cardinality is stored changed formats so that it is now a
    > >>floating point number (not sure what it used to be) and the Rexx proc
    > >>that displays the cardinality does not handle floating point numbers.
    > >>
    > >>
    > >>I just need to find the cardinality. But I'm not going to run runstats
    > >>because as I've mentioned, it's already been done. The tool we use to
    > >>view the information is not showing the correct cardinality, according
    > >>to our DBA.
    > >>
    > >
    > > If the tool that displays the DB2 catalog data is not working, then
    write
    > > your own SQL statement against the catalog table.
    >
    >
    > Isn't that what I asking for help on ?
    >
    Sorry, there are lots of cardinalities in the catalog. Cardinality of the
    table, or various columns of the index (first key, full key, etc.). If you
    are more specific I can probably help.


    Mark A Guest

  9. #9

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:_Bk8b.1031330$Bf5.147600news.easynews.com...
    >
    >>Mark A wrote:
    >>
    >>
    >>>>According to our DBA, in the transition from v6 to v7, the catalog field
    >>>>in which the cardinality is stored changed formats so that it is now a
    >>>>floating point number (not sure what it used to be) and the Rexx proc
    >>>>that displays the cardinality does not handle floating point numbers.
    >>>>
    >>>>
    >>>>I just need to find the cardinality. But I'm not going to run runstats
    >>>>because as I've mentioned, it's already been done. The tool we use to
    >>>>view the information is not showing the correct cardinality, according
    >>>>to our DBA.
    >>>>
    >>>
    >>>If the tool that displays the DB2 catalog data is not working, then
    >
    > write
    >
    >>>your own SQL statement against the catalog table.
    >>
    >>
    >>Isn't that what I asking for help on ?
    >>
    >
    > Sorry, there are lots of cardinalities in the catalog. Cardinality of the
    > table, or various columns of the index (first key, full key, etc.). If you
    > are more specific I can probably help.
    >
    >

    Mark, I know you're trying to be helpful in your own way, but what
    exactly is not specific about wanting a query "to find cardinality of
    indexed columns" ?

    Richard Winston Guest

  10. #10

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:vOl8b.4174665$cI2.588729news.easynews.com...
    > Mark, I know you're trying to be helpful in your own way, but what
    > exactly is not specific about wanting a query "to find cardinality of
    > indexed columns" ?
    >
    I apologize, I lost track of your details contained in the subject line, and
    got sidetracked on your problems with the tool.

    It might be easier (and more accurate) to write SQL statements to go against
    the tables to determine the cardinality. They will be fairly efficient if
    they use indexed columns.

    This example assumes two columns taken together as might be with a composite
    index:

    with temp as
    (
    select distinct col1, col2 from table_name
    )
    select count(*) from temp ;


    The SYSIBM.SYSINDEXSTATS table has the following information if you know the
    names of the indexes.

    FIRSTKEYCARDF - number of distinct values of the first key column.

    FULLKEYCARDF - number of distinct values of the entire key (for multi-column
    keys).

    Use OWNER (Authorization ID of the owner of the index) and NAME (Name of the
    index) in your WHERE clause to find the index you want. Index names for a
    table can be found in the SYSIBM.SYSINDEXES table (you can join this table
    with the other one).

    A value of -1 means the statistics have not been gathered since the object
    was last created. Remember that if a change is made to an object, it may
    have been dropped and recreated.


    Mark A Guest

  11. #11

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Another good url link to the catalog tables.
    SYSIBM.SYS*STATS

    [url]http://publib.boulder.ibm.com/cgi-bin/bookmgr/SEARCH?SEARCHREQUEST=catalog+tables&SHELF=dsnshha4 &Type=FUZZY&Book=DSNSQH13&DN=SC26-9944-03&DT=20030509185119&searchTopic=TOPIC&searchText= TEXT&searchIndex=INDEX&rank=RANK[/url]



    "Richard Winston" <rwinstonNOPSPAM.ORG> a écrit dans le message de
    news:QAi8b.4164656$mA4.575658news.easynews.com...
    > We use DB2 OS/390 v7.1 and the DBA told me that the tool we use to view
    > the cardinality hasn't had its REXX proc updated to pick up changes from
    > v6 so it just show -1 under the cardinality field.
    >
    >
    > Does anyone have a query I can use to get the cardinality from the
    > SYSIBM tables?
    >
    >
    > Thanks.
    >

    PM \(pm3iinc-nospam\) Guest

  12. #12

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:vOl8b.4174665$cI2.588729news.easynews.com...
    >
    >>Mark, I know you're trying to be helpful in your own way, but what
    >>exactly is not specific about wanting a query "to find cardinality of
    >>indexed columns" ?
    >>
    >
    > I apologize, I lost track of your details contained in the subject line, and
    > got sidetracked on your problems with the tool.
    >
    > It might be easier (and more accurate) to write SQL statements to go against
    > the tables to determine the cardinality. They will be fairly efficient if
    > they use indexed columns.
    >
    > This example assumes two columns taken together as might be with a composite
    > index:
    >
    > with temp as
    > (
    > select distinct col1, col2 from table_name
    > )
    > select count(*) from temp ;
    >
    >
    > The SYSIBM.SYSINDEXSTATS table has the following information if you know the
    > names of the indexes.
    >
    > FIRSTKEYCARDF - number of distinct values of the first key column.
    >
    > FULLKEYCARDF - number of distinct values of the entire key (for multi-column
    > keys).
    >
    > Use OWNER (Authorization ID of the owner of the index) and NAME (Name of the
    > index) in your WHERE clause to find the index you want. Index names for a
    > table can be found in the SYSIBM.SYSINDEXES table (you can join this table
    > with the other one).
    >
    > A value of -1 means the statistics have not been gathered since the object
    > was last created. Remember that if a change is made to an object, it may
    > have been dropped and recreated.
    >
    >

    This is what I came up with. It seems to give me what I'm looking for:

    SELECT DISTINCT K.COLNAME, INT(C.COLCARDF)
    FROM SYSIBM.SYSINDEXES I,
    SYSIBM.SYSKEYS K,
    SYSIBM.SYSCOLUMNS C
    WHERE I.TBNAME = <your table name>
    AND K.IXCREATOR = <your auth id>
    AND I.CREATOR = K.IXCREATOR
    AND I.NAME = IXNAME
    AND K.COLNAME = C.NAME
    AND I.CREATOR = C.TBCREATOR
    AND I.TBNAME = C.TBNAME
    ORDER BY 2 DESC
    ;

    Richard Winston Guest

  13. #13

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:WMm8b.4175305>
    >
    > This is what I came up with. It seems to give me what I'm looking for:
    >
    > SELECT DISTINCT K.COLNAME, INT(C.COLCARDF)
    > FROM SYSIBM.SYSINDEXES I,
    > SYSIBM.SYSKEYS K,
    > SYSIBM.SYSCOLUMNS C
    > WHERE I.TBNAME = <your table name>
    > AND K.IXCREATOR = <your auth id>
    > AND I.CREATOR = K.IXCREATOR
    > AND I.NAME = IXNAME
    > AND K.COLNAME = C.NAME
    > AND I.CREATOR = C.TBCREATOR
    > AND I.TBNAME = C.TBNAME
    > ORDER BY 2 DESC
    > ;
    >
    I believe that capturing stats on all columns is optional on the runstats,
    but if they are being requested and are up to date, then you are OK. But you
    "may" also want to know the cardinality of combined columns so that you will
    know the cardinality of an index with multiple columns.


    Mark A Guest

  14. #14

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:WMm8b.4175305>
    >
    >>This is what I came up with. It seems to give me what I'm looking for:
    >>
    >>SELECT DISTINCT K.COLNAME, INT(C.COLCARDF)
    >>FROM SYSIBM.SYSINDEXES I,
    >> SYSIBM.SYSKEYS K,
    >> SYSIBM.SYSCOLUMNS C
    >>WHERE I.TBNAME = <your table name>
    >>AND K.IXCREATOR = <your auth id>
    >>AND I.CREATOR = K.IXCREATOR
    >>AND I.NAME = IXNAME
    >>AND K.COLNAME = C.NAME
    >>AND I.CREATOR = C.TBCREATOR
    >>AND I.TBNAME = C.TBNAME
    >>ORDER BY 2 DESC
    >>;
    >>
    >
    > I believe that capturing stats on all columns is optional on the runstats,
    > but if they are being requested and are up to date, then you are OK. But you
    > "may" also want to know the cardinality of combined columns so that you will
    > know the cardinality of an index with multiple columns.
    >
    >

    Mark, I took a look at SYSINDEXSTATS, but that table only holds
    information for partioned indexes. Where would I find cardinality of
    comhined columns for non partioned indexes ?

    Richard Winston Guest

  15. #15

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    > >>This is what I came up with. It seems to give me what I'm looking for:
    > >>
    > >>SELECT DISTINCT K.COLNAME, INT(C.COLCARDF)
    > >>FROM SYSIBM.SYSINDEXES I,
    > >> SYSIBM.SYSKEYS K,
    > >> SYSIBM.SYSCOLUMNS C
    > >>WHERE I.TBNAME = <your table name>
    > >>AND K.IXCREATOR = <your auth id>
    > >>AND I.CREATOR = K.IXCREATOR
    > >>AND I.NAME = IXNAME
    > >>AND K.COLNAME = C.NAME
    > >>AND I.CREATOR = C.TBCREATOR
    > >>AND I.TBNAME = C.TBNAME
    > >>ORDER BY 2 DESC
    > >>;
    > >>
    > >
    > > I believe that capturing stats on all columns is optional on the
    runstats,
    > > but if they are being requested and are up to date, then you are OK. But
    you
    > > "may" also want to know the cardinality of combined columns so that you
    will
    > > know the cardinality of an index with multiple columns.
    > >
    > >
    >
    >
    > Mark, I took a look at SYSINDEXSTATS, but that table only holds
    > information for partioned indexes. Where would I find cardinality of
    > comhined columns for non partioned indexes ?
    >
    I believe that partition number is zero when the index is not partitioned,
    so it can be ignored in a query if you don't have partitions. This is
    explained in the definition of partition on the SYSIBM.SYSINDEXPART table,
    and I am not sure why it is not explained that way in some other tables.


    Mark A Guest

  16. #16

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    Mark A wrote:
    >>>>This is what I came up with. It seems to give me what I'm looking for:
    >>>>
    >>>>SELECT DISTINCT K.COLNAME, INT(C.COLCARDF)
    >>>
    >>>>FROM SYSIBM.SYSINDEXES I,
    >>>
    >>>> SYSIBM.SYSKEYS K,
    >>>> SYSIBM.SYSCOLUMNS C
    >>>>WHERE I.TBNAME = <your table name>
    >>>>AND K.IXCREATOR = <your auth id>
    >>>>AND I.CREATOR = K.IXCREATOR
    >>>>AND I.NAME = IXNAME
    >>>>AND K.COLNAME = C.NAME
    >>>>AND I.CREATOR = C.TBCREATOR
    >>>>AND I.TBNAME = C.TBNAME
    >>>>ORDER BY 2 DESC
    >>>>;
    >>>>
    >>>
    >>>I believe that capturing stats on all columns is optional on the
    >
    > runstats,
    >
    >>>but if they are being requested and are up to date, then you are OK. But
    >
    > you
    >
    >>>"may" also want to know the cardinality of combined columns so that you
    >
    > will
    >
    >>>know the cardinality of an index with multiple columns.
    >>>
    >>>
    >>
    >>
    >>Mark, I took a look at SYSINDEXSTATS, but that table only holds
    >>information for partioned indexes. Where would I find cardinality of
    >>comhined columns for non partioned indexes ?
    >>
    >
    > I believe that partition number is zero when the index is not partitioned,
    > so it can be ignored in a query if you don't have partitions. This is
    > explained in the definition of partition on the SYSIBM.SYSINDEXPART table,
    > and I am not sure why it is not explained that way in some other tables.
    >
    >

    I don't know about your site, but at mine there are no rows in
    SYSINDEXSTATS where PARTITION = 0.

    I'll look at the other tables to see if I can figure it out.

    Richard Winston Guest

  17. #17

    Default Re: SYSIBM Query to Find Cardinality of Indexed Columns

    > > I believe that partition number is zero when the index is not
    partitioned,
    > > so it can be ignored in a query if you don't have partitions. This is
    > > explained in the definition of partition on the SYSIBM.SYSINDEXPART
    table,
    > > and I am not sure why it is not explained that way in some other tables.
    >
    > I don't know about your site, but at mine there are no rows in
    > SYSINDEXSTATS where PARTITION = 0.
    >
    > I'll look at the other tables to see if I can figure it out.
    >
    You are correct. My mistake. SYSIBM.SYSINDEXSTATS only contains a row for
    each partition of a partitioning index.


    Mark A Guest

Similar Threads

  1. SYSIBM Query to Find Which Columns Are Indexed ?
    By Richard Winston in forum IBM DB2
    Replies: 4
    Last Post: May 19th, 09:00 PM
  2. Specify Query Columns from From
    By SincityViper in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 27th, 07:43 AM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. displaying query in 2 columns
    By simprini in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: March 9th, 07:59 PM
  5. find max length of data in all columns?
    By Mark D Powell in forum Oracle Server
    Replies: 0
    Last Post: December 11th, 06:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139