Kbase P25656: What storage area does a table or index reside in ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Verified
GOAL:
What storage area does a table or index reside in ?
GOAL:
How to determine what storage area a table or index resides in?
GOAL:
How can to obtain a list that show the storage areas for all tables and indexes?
GOAL:
How to determine which areas tables are stored in?
GOAL:
How to determine which areas indexes are stored in?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
The storage area that a table or index resides in can be obtained by running one of the proutil utilities or by querying the VST's. Use the method that you are most comfortable with.
1. Use the Dbanalys tool. This tool is a combination of tabanalys and idxanalys. It displays statistical information about index, record, and free chain blocks.
Syntax: proutil dbname -C dbanalys
2. Query the information contained in the _StorageObject table VST using 4GL.
A. Below is a sample of 4GL code that will take a TableName as input and display the Area-Name and Area-Number for that table as well as the Tables Index-Name, and the Area-Name and Area-Number where the indexes exist.
DEF VAR cTableName AS CHAR FORMAT 'X(30)' LABEL 'Table Name'.
UPDATE cTableName WITH SIDE-LABELS.
FIND _file WHERE _file-name = cTableName NO-LOCK NO-ERROR.
IF NOT AVAILABLE(_file) THEN MESSAGE "Table does not exist" VIEW-AS ALERT-BOX.
ELSE
DO:
FIND _storageobject WHERE _storageobject._object-type = 1 AND _storageobject._object-number = _file._file-num NO-LOCK NO-ERROR.
FIND _Area WHERE _Area._Area-number = _storageobject._Area-number NO-LOCK NO-ERROR.
DISPLAY _Area-name _Storageobject._Area-number.
FOR EACH _index OF _file:
FIND _storageobject WHERE _storageobject._object-type = 2 AND _storageobject._object-number = _index._idx-num NO-LOCK NO-ERROR.
FIND _Area WHERE _Area._Area-number = _storageobject._Area-number NO-LOCK NO-ERROR.
DISPLAY _index-name FORMAT 'x(20)' _Area-name _Storageobject._Area-number.
END.
END.
The _object-type is returned as 1 for tables and 2 for indexes.
B. The below sample of code will list all the Index-Names , and their corresponding Area-name and Area-number for the database.
/* The following 4GL code lists all Index Names, Numbers and Areas */
FOR EACH _storageobject WHERE _storageobject._object-type = 2 NO-LOCK:
FIND _Index WHERE _index._idx-num = _storageobject._Object-number
NO-LOCK NO-ERROR.
FIND _area WHERE _area._area-number = _storageobject._area-number
NO-LOCK NO-ERROR.
DISPLAY _index-name FORMAT "x(25)"_idx-num _area._area-name FORMAT
"X(25)".
END.
C. The below sample of code will list all the Table-Names, and their corresponding Area-names, and Area-number for the database.
/* The following 4GL code lists all table Names, Numbers and Areas */
FOR EACH _storageobject WHERE _storageobject._object-type = 1 NO-LOCK:
FIND _File WHERE _File._File-Number = _storageobject._Object-number
NO-LOCK NO-ERROR.
FIND _area WHERE _area._area-number = _storageobject._area-number
NO-LOCK NO-ERROR.
DISPLAY _File-name FORMAT "x(25)"_File-Number _area._area-name
FORMAT "X(25)".
END.