Schema area extent

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"

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.