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
