IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 5 replies
  • Latest Post - ‏2019-03-15T12:05:14Z by stiruvee
CGRIERE
CGRIERE
41 Posts

Pinned topic Number of entries of EVI symbol table

‏2019-03-11T18:12:54Z |

Hi,

I would like to get the number of entries of an EVI symbol table. 

View  qsys2.syspartitionindexes is a good starting point: 

SELECT table_schema, table_name, system_table_name,  number_keys, index_name, system_index_name, column_names, substr(unique_partial_key_values, 1, 24),  overflow_values, evi_code_size 
FROM qsys2.syspartitionindexes 
WHERE table_schema  in ('MyLib')  and accpth_type = 'EVI' 

but

- number_keys  contains the number of lines of the table not the number of keys.

- overflow_values contains the number of symbols that are in overflow area.

- unique_partial_key_values (96 char) seems contain that information but in a  way I am not used to. For example : '8  -1   -1  -1' 

Any idea?

By the way a modified version of this select could help to know which EVI's should be recreated because their overflow areas are almost full. 

 

Regards.

 

Updated on 2019-03-11T18:13:20Z at 2019-03-11T18:13:20Z by CGRIERE
  • stiruvee
    stiruvee
    48 Posts
    ACCEPTED ANSWER

    Re: Number of entries of EVI symbol table

    ‏2019-03-15T12:05:14Z  
    • CGRIERE
    • ‏2019-03-15T10:25:01Z

    Hi,

    Thanks for this information.

    I made additional tests on a table using 4 bin indexes (1 key, 2 keys, 4 keys and 5 keys) and 2 EVI (1 key and 2 keys)

     

    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    -- Index with 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;

    -- Index with more than 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_ORDERKEY_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (ORDERKEY, SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;
     

    select count(distinct shipmode) from db2ipfr.item_fact;  -- Delivers : 8
    select count(distinct suppkey) from db2ipfr.item_fact; --  Delivers : 1000
    select count(distinct shipmode|| suppkey) from db2ipfr.item_fact; -- Delivers : 7001

    select count( distinct SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact; -- Delivers 600 273

    select count (distinct ORDERKEY) from db2ipfr.item_fact ; -- Delivers : 15000
    select count (distinct ORDERKEY||SHIPMODE) from db2ipfr.item_fact ; -- Delivers 455944 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY) from db2ipfr.item_fact ; -- Delivers 600422
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY) from db2ipfr.item_fact ; -- Delivers 600570 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact ; -- Delivers 600 572

     

    Then I look at syspartitionindexstat view using :

    select index_name, firstkeycard, first2keycard, first3keycard, first4keycard, fullkeycard from qsys2.syspartitionindexstat where table_schema='DB2IPFR'  order by index_name;

    See figure 1 in attached document.

      

    What we can see :

    - For EVI  indexes FIRSTKEYCARD column is only used and gives cardinality of all the keys of EVI. That is the number of entries in symbol table. What I am looking for.

    - For BIN indexes FISRTKEYCARD gives the cardinality of first key, FIRST2KEYCARD gives cardinality of the first 2 keys and so on ... FULLKEYCARD gives the cardinality of all the keys if the number of key is  equal or less than 4.

    According to these tests I find the IBM document is correct (see figure 2 in attached document).  

     

    Hope that helps.

    The bottom line is: I should have read IBM documentation before opening this topic.

     

    Glad you found out the information you are looking for.  I verified encoded vector indexes with multiple columns again and found out that IBM documentation is correct. All EVI indexes on our system were single column indexes. I might have created normal index earlier while testing for multiple columns and got wrong results. I should have double checked when my results differed from documentation.

    Updated on 2019-03-15T12:06:09Z at 2019-03-15T12:06:09Z by stiruvee
  • stiruvee
    stiruvee
    48 Posts

    Re: Number of entries of EVI symbol table

    ‏2019-03-12T11:35:39Z  

     

    * 4 numbers in column unique_partial_key_values correspond to distinct key values for columns 1 to 4 in EVI index.

    * '8 -1 -1 -1' means EVI index has only one column and the column has  8 distinct key values. 

    * Check qsys2.syspartitionindexstat instead of qsys2.syspartitionindexes for better presentation of key cardinality information.

  • CGRIERE
    CGRIERE
    41 Posts

    Re: Number of entries of EVI symbol table

    ‏2019-03-12T12:40:17Z  
    • stiruvee
    • ‏2019-03-12T11:35:39Z

     

    * 4 numbers in column unique_partial_key_values correspond to distinct key values for columns 1 to 4 in EVI index.

    * '8 -1 -1 -1' means EVI index has only one column and the column has  8 distinct key values. 

    * Check qsys2.syspartitionindexstat instead of qsys2.syspartitionindexes for better presentation of key cardinality information.

    Hi,

     

    Thanks for this valuable information.

    If the EVI has only one key I get the number of vectors. But if there is more than one key I can not guess the number. of vectors

     

    Regards.

    Updated on 2019-03-12T14:53:22Z at 2019-03-12T14:53:22Z by CGRIERE
  • stiruvee
    stiruvee
    48 Posts

    Re: Number of entries of EVI symbol table

    ‏2019-03-15T06:15:58Z  
    • CGRIERE
    • ‏2019-03-12T12:40:17Z

    Hi,

     

    Thanks for this valuable information.

    If the EVI has only one key I get the number of vectors. But if there is more than one key I can not guess the number. of vectors

     

    Regards.

     

    Use qsys2.syspartitionindexstat. IBM documentation is showing wrong information.

     

    * firstkeycard contains total number of unique values for column 1.

    * first2keycard contains total number of unique values for column 1 + column 2 .

    * first3keycard contains total number of unique values  for column 1 +  column 2 + column 3

    * Fullkeycard contains total number of unique values for the entire index key..

     

    Correction to my previous post:

    * 4 numbers in column unique_partial_key_values correspond to total number of unique values values for first column, first two columns, first three columns and first 4 columns in EVI index.

  • CGRIERE
    CGRIERE
    41 Posts

    Re: Number of entries of EVI symbol table

    ‏2019-03-15T10:25:01Z  

    Hi,

    Thanks for this information.

    I made additional tests on a table using 4 bin indexes (1 key, 2 keys, 4 keys and 5 keys) and 2 EVI (1 key and 2 keys)

     

    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    -- Index with 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;

    -- Index with more than 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_ORDERKEY_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (ORDERKEY, SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;
     

    select count(distinct shipmode) from db2ipfr.item_fact;  -- Delivers : 8
    select count(distinct suppkey) from db2ipfr.item_fact; --  Delivers : 1000
    select count(distinct shipmode|| suppkey) from db2ipfr.item_fact; -- Delivers : 7001

    select count( distinct SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact; -- Delivers 600 273

    select count (distinct ORDERKEY) from db2ipfr.item_fact ; -- Delivers : 15000
    select count (distinct ORDERKEY||SHIPMODE) from db2ipfr.item_fact ; -- Delivers 455944 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY) from db2ipfr.item_fact ; -- Delivers 600422
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY) from db2ipfr.item_fact ; -- Delivers 600570 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact ; -- Delivers 600 572

     

    Then I look at syspartitionindexstat view using :

    select index_name, firstkeycard, first2keycard, first3keycard, first4keycard, fullkeycard from qsys2.syspartitionindexstat where table_schema='DB2IPFR'  order by index_name;

    See figure 1 in attached document.

      

    What we can see :

    - For EVI  indexes FIRSTKEYCARD column is only used and gives cardinality of all the keys of EVI. That is the number of entries in symbol table. What I am looking for.

    - For BIN indexes FISRTKEYCARD gives the cardinality of first key, FIRST2KEYCARD gives cardinality of the first 2 keys and so on ... FULLKEYCARD gives the cardinality of all the keys if the number of key is  equal or less than 4.

    According to these tests I find the IBM document is correct (see figure 2 in attached document).  

     

    Hope that helps.

    The bottom line is: I should have read IBM documentation before opening this topic.

     

    Attachments

    Updated on 2019-03-15T10:32:33Z at 2019-03-15T10:32:33Z by CGRIERE
  • stiruvee
    stiruvee
    48 Posts

    Re: Number of entries of EVI symbol table

    ‏2019-03-15T12:05:14Z  
    • CGRIERE
    • ‏2019-03-15T10:25:01Z

    Hi,

    Thanks for this information.

    I made additional tests on a table using 4 bin indexes (1 key, 2 keys, 4 keys and 5 keys) and 2 EVI (1 key and 2 keys)

     

    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE)   ;
    CREATE ENCODED VECTOR INDEX DB2IPFR.ITEM_EVI_SHIPMODE_SUPPKEY 
        ON DB2IPFR.ITEM_FACT ( SHIPMODE ASC, SUPPKEY )   ;

    -- Index with 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;

    -- Index with more than 4 keys
    CREATE  INDEX DB2IPFR.ITEM_BIN_ORDERKEY_SHIPMODE_SUPPKEY_PARTKEY_SHIPDATE
        ON DB2IPFR.ITEM_FACT (ORDERKEY, SHIPMODE, SUPPKEY ,PARTKEY, SHIPDATE )   ;
     

    select count(distinct shipmode) from db2ipfr.item_fact;  -- Delivers : 8
    select count(distinct suppkey) from db2ipfr.item_fact; --  Delivers : 1000
    select count(distinct shipmode|| suppkey) from db2ipfr.item_fact; -- Delivers : 7001

    select count( distinct SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact; -- Delivers 600 273

    select count (distinct ORDERKEY) from db2ipfr.item_fact ; -- Delivers : 15000
    select count (distinct ORDERKEY||SHIPMODE) from db2ipfr.item_fact ; -- Delivers 455944 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY) from db2ipfr.item_fact ; -- Delivers 600422
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY) from db2ipfr.item_fact ; -- Delivers 600570 
    select count (distinct ORDERKEY||SHIPMODE||SUPPKEY||PARTKEY||SHIPDATE) from db2ipfr.item_fact ; -- Delivers 600 572

     

    Then I look at syspartitionindexstat view using :

    select index_name, firstkeycard, first2keycard, first3keycard, first4keycard, fullkeycard from qsys2.syspartitionindexstat where table_schema='DB2IPFR'  order by index_name;

    See figure 1 in attached document.

      

    What we can see :

    - For EVI  indexes FIRSTKEYCARD column is only used and gives cardinality of all the keys of EVI. That is the number of entries in symbol table. What I am looking for.

    - For BIN indexes FISRTKEYCARD gives the cardinality of first key, FIRST2KEYCARD gives cardinality of the first 2 keys and so on ... FULLKEYCARD gives the cardinality of all the keys if the number of key is  equal or less than 4.

    According to these tests I find the IBM document is correct (see figure 2 in attached document).  

     

    Hope that helps.

    The bottom line is: I should have read IBM documentation before opening this topic.

     

    Glad you found out the information you are looking for.  I verified encoded vector indexes with multiple columns again and found out that IBM documentation is correct. All EVI indexes on our system were single column indexes. I might have created normal index earlier while testing for multiple columns and got wrong results. I should have double checked when my results differed from documentation.

    Updated on 2019-03-15T12:06:09Z at 2019-03-15T12:06:09Z by stiruvee