What causes growth of the database schema area file extent? I expect there is not any data in this area so what will make it grow?
With my case in the database all the tables and indexes are type 2. There is 1 application table in the schema area due to someones mistake. That 1 table has zero records. The only indexes that show in the Schema area are default indexes for a hand full of 1 record tables that have no indexes defined.
Different customer's files have different sizes. The application schema are the same.
DBanalys shows total of the size column is 2.6 MB for the records and 430 K for the indexes.
Example database.d1 file sizes:
Customer A, 300 MB
Customer B, 1.38 GB
Customer C, 69 MB
Progress OpenEdge 11.5.1, Windows server
Do you know the history of those DBs? Is it possible the schema area contained many objects in the past, and they have been moved into Type II areas?
Those are some very large schema areas. Mine area typically 10 MB or less, with over 1300 schema objects. Given the size totals for records and indexes you've given, the extends weren't caused buy the data that's there now. What is the HWM of the area?
find dictdb._area no-lock where _area._area-number = 6. find dictdb._areastatus no-lock where _areastatus._areastatus-areanum = _area._area-number. display _area._area-blocksize _areastatus._areastatus-hiwater _areastatus._areastatus-totblocks .
Dbanalys reports LOB objects only since Progress V11.6 but their space is counted in the messages:
212 RM block(s) found in the storage area. 91.45% of the RM block space is used.
Thanks to all for your input.
Tom
No clob, no blob
Good point that the objects need to be moved.
Steve:
About SQL, what if they use any ODBC connection for any SQL client? I know they use ODBC connections.
Rob:
Yes the schema area contained objects. They were moved, perhaps months ago. That could account for the current size.
Yet with example customer B the area has grown after the objects have moved.
Example database.d1 file sizes:
Customer A, 300 MB; block size 4096, area hiwater 69,336; area total blocks 75,022
Customer B, 1.38 GB; block size 4096, area hiwater 362,991; area total block size 491,005
From DBanalysis
362991 block(s) found in the area.
Current high water mark: 362991
361921 free block(s) found in the area
753 record block(s) found in the area
314 index block(s) found in the area
0 empty block(s) found in the area
1 object block(s) found in the area
Customer C, 69 MB; block size 4096, area hiwater 17,382; area total block size 17,391
Use 'proutil db -C mvsch' and delete "Old Schema Area"
>>> Steve:
>>> About SQL, what if they use any ODBC connection for any SQL client? I know they use ODBC connections.
FYI, no SQL statistics are involved.
systblstat has zero records.
Hi All, Thank you for your replies and input.
I took a backup of one of the DB's, restored the DB, moved the default indexes and 0 record table out of the area. Then I did the move schema area, mvsch. The result is a schema area extent that is 4 MB.
I think this confirms the size of the extent was the result of the records for the tables that have been moved to a data area.
Thank you, again.
RM blocks in SAT1 can not change their type to the Free blocks. All free blocks were the index blocks in past.
> 361921 free block(s) found in the area
I am trying to find a way to see what objects are in schema area 6 from ABL. Should the following work?
I get more results than I expect. Tables that are not in the schema area are included in the result list. .
for each _file no-lock
WHERE _ianum = 6:
disp _file-name _ianum.
end.
for each _Index no-lock
WHERE _ianum = 6
AND NOT _index-name BEGINS '_':
disp _Index-name _ianum.
end.
Don't use _ianum, it isn't accurate.
Read _storageobject for _area-number = 6, join to _file and _index (based on __object-number and _object-type) to get the tables and indexes, and filter out anything that is a system object.
Simple example, assumes no LOBs:
/* application objects in schema area */
for each _storageobject no-lock where _area-number = 6: case _storageobject._object-type: when 1 then /* table */ do: find _file no-lock where _file-number = _object-number. if _tbl-type = 't' then display _file-number _file-name. end. when 2 then /* index */ do: find _index no-lock where _idx-num = _object-number. if not _index-name begins "_" then do: find _file no-lock of _index. display _file-number _file-name _idx-num _index-name. end. end. end case. end.
Solid. Thanks again.