Feedback
Did this article resolve your question/issue?

   

Article

Calculating values for -tablerangesize and -indexrangesize

« Go Back

Information

 
TitleCalculating values for -tablerangesize and -indexrangesize
URL Name000048306
Article Number000168454
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Other: VST
Question/Problem Description
How to verify the current values of -basetable -tablerangesize ?
How many indexes statistics are currently being tracked by -baseindex -indexrangesize ?
How to scope database startup parameters: -tablerangesize and -indexrangesize for the current database's Schema?
What do I need to track all table and index Activity and Status?
What are the number of tables needed to collect _TableStat statistics?
Where can I find the number of indexes needed to track all _IndexStat statistics?
Steps to Reproduce
Clarifying Information
Database startup parameters for collecting _TableStat, _UserTableStat and _IndexStat, _UserIndexStat statistics:
  • -basetable -tablerangesize
  • -baseindex -indexrangesize
Error Message
Defect Number
Enhancement Number
Cause
Resolution
Using the -basetable / -baseindex parameter
  • -basetable <starting table number> -tablerangesize <number_of_tables>
  • -baseindex <starting index number> -indexrangesize <number_of_indexes>
The basetable / baseindex value provides the starting number for the range of numbers to collect stats, it is not the total number of objects. For example:
  • -basetable and -tablerangesize parameters are for table number range, not total number of tables. 
  • To calculate the range, find the smallest table number and the largest table number of the tables to collect statistics.
  • Using -basetable 1 and -tablerangesize 200, statistics of tables with table numbers between 1 - 200 will be collected. 
metaschema tables (whose numbers are negative) are not included in the statistics. For example:
  • _TableStat will not report meta-schema activity on (_file) if this statistic is of interest.
  • _IndexStat will report on some meta-schema activity (_file-name, _field-name, _field) but not Indexes that have negative index numbers for schema tables introduced after OpenEdge 10 (auditing, encryption, MT etc.)   
Example ABL Code:  The following sample ABL code needs to be run against an online database in order to find the current values of these database startup parameters against the current schema definitions determine the right values needed  to collect _TableStat, _UserTableStat  and _IndexStat, _UserIndexStat statistics.  
RUN CheckStatBase.

DEFINE VARIABLE vMinTableID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMaxTableID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMinIndexID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMaxIndexID AS INTEGER NO-UNDO.

/* ------------------------------------------------------------------------- */
PROCEDURE CheckStatBase.

/* Check vMaxTableID: */
FOR LAST DICTDB._TableStat NO-LOCK.
    FOR EACH DICTDB._File NO-LOCK
        WHERE DICTDB._File._File-Number LT 32768 /* exclude SQL92 tables */
        BY DICTDB._File._File-Number DESCENDING:

ASSIGN vMaxTableID = MIN(DICTDB._TableStat._TableStat-id,
                                DICTDB._File._File-Number).

IF DICTDB._TableStat._TableStat-id LT DICTDB._File._File-Number  THEN
    MESSAGE LDBNAME(NUM-DBS)
    "Statistics for tables with numbers higher than"
    DICTDB._TableStat._TableStat-id SKIP
    "will be missed."
    "To get full statistics start a database with" SKIP
    "-tablerangesize"
      ( DICTDB._File._File-Number
      - DICTDB._TableStat._TableStat-ID
      + INTEGER(RECID(DICTDB._TableStat))
      ) VIEW-AS ALERT-BOX WARNING BUTTONS OK.

        ELSE MESSAGE LDBNAME(NUM-DBS)
            "CURRENT -tablerangesize SET TO:" DICTDB._TableStat._TableStat-id  SKIP
            "Which IS sufficient FOR CURRENT SCHEMA Tables: " vMaxTableID
             VIEW-AS ALERT-BOX INFO BUTTONS OK.
        LEAVE.
    END. /* FOR EACH _File */
END. /* FOR LAST _TableStat */


/* Check vMinTableID: */
FOR FIRST DICTDB._TableStat NO-LOCK.
    FOR EACH DICTDB._File NO-LOCK
        WHERE DICTDB._File._File-Number GT 0
        BY DICTDB._File._File-Number /* ASCENDING */:

        ASSIGN vMinTableID = MAX(DICTDB._TableStat._TableStat-id,
                               DICTDB._File._File-Number).

        IF DICTDB._TableStat._TableStat-id GT DICTDB._File._File-Number  THEN
            MESSAGE LDBNAME(NUM-DBS)
            "Statistics for tables with numbers lower than"
            DICTDB._TableStat._TableStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-basetable" DICTDB._File._File-Number
            VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE LDBNAME(NUM-DBS)
                    "-basetable = " vMinTableID " which IS sufficient FOR CURRENT SCHEMA"
                        VIEW-AS ALERT-BOX INFO BUTTONS OK.
            LEAVE.
    END. /* FOR EACH _File */
END. /* FOR FIRST _TableStat */


/* Check vMaxIndexID: */
FOR LAST DICTDB._IndexStat NO-LOCK.
    FOR EACH DICTDB._Index NO-LOCK
    WHERE NOT _index-name BEGINS "_"
        BY DICTDB._Index._Idx-num DESCENDING:

        ASSIGN vMaxIndexID = MIN(DICTDB._IndexStat._IndexStat-id,
                         DICTDB._Index._Idx-num).

        IF DICTDB._IndexStat._IndexStat-id LT DICTDB._Index._Idx-num  THEN
            MESSAGE LDBNAME(NUM-DBS)
            "Statistics for indices with numbers higher than"
            DICTDB._IndexStat._IndexStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-indexrangesize"
            ( DICTDB._Index._Idx-num
              - DICTDB._IndexStat._IndexStat-id  /*-baseindex = _IndexStat-id - recid*/
              + INTEGER(RECID(DICTDB._IndexStat))
              ) VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE LDBNAME(NUM-DBS)
            "CURRENT -indexrangesize SET TO:" DICTDB._IndexStat._IndexStat-id  SKIP
            "Which IS sufficient FOR CURRENT SCHEMA Indexes: " vMaxIndexID
             VIEW-AS ALERT-BOX INFO BUTTONS OK.

                LEAVE.
    END. /* FOR EACH _Index */
END. /* FOR LAST _IndexStat */

/* Check vMinIndexID: */
FOR FIRST DICTDB._IndexStat NO-LOCK.
    FOR EACH DICTDB._Index NO-LOCK
        WHERE DICTDB._Index._Idx-num GT 0
        BY DICTDB._Index._Idx-num /* ASCENDING */:

        ASSIGN vMinIndexID = MAX(DICTDB._IndexStat._IndexStat-id,
                                 DICTDB._Index._Idx-num).

        IF DICTDB._IndexStat._IndexStat-id GT DICTDB._Index._Idx-num  THEN
            MESSAGE LDBNAME(NUM-DBS)
            "Statistics for indices with numbers lower than"
            DICTDB._IndexStat._IndexStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-baseindex" DICTDB._Index._Idx-num
            VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE LDBNAME(NUM-DBS)
                    "-baseindex = " vMinIndexID " which IS sufficient FOR CURRENT SCHEMA"
                    VIEW-AS ALERT-BOX INFO BUTTONS OK.
        LEAVE.
    END. /* FOR EACH _Index */
END. /* FOR FIRST _IndexStat */

END PROCEDURE. /* CheckStatBase */


EXAMPLE output:
 
Statistics for tables with numbers higher than 50 will be missed. (current -tablerangesize = 50)
To get full statistics start the database with -tablerangesize 913 

basetable = 1 is sufficient for current schema

Statistics for indices with numbers higher than 50 will be missed. (current -indexrangesize = 50)
To get full statistics start the database with -indexrangesize 607 

baseindex = 1 is sufficient for current schema

 
Once these database startup parameters have been correctly sized, review the related -omsize database startup parameter.  Refer to Article How To set Storage Object Cache Size (-omsize)  
Workaround
Notes
Last Modified Date4/13/2021 4:39 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.