Openedge Metadata

[BigSlick]

Hi,

I am currently in the process of creating reports to determine tables that are (wrongly) stored in the schema area of a database. The data i want is available from the tabanalys command but its difficult to interpret into reports and takes forever.

I have managed to go through the VST's to get each _file from each _area, but i need to ignore _file where there are no records (as these for the time being can be kept here)

My code so far:

for each _area where _area._area-num = 6 no-lock:
for each _file where _file._ianum = _area._area-num no-lock:
display _file with 1 col width 200.
end.
end.

Could somebody point me in the right direction as to where i can get all _file where records exist?

Thanks in advance.

 

[TomBascom]

I would not assign empty objects to the schema area -- it's a bad habit to ever let anything live there. Create a dedicated "default", "unused" or "empty" area instead.

"ianum" is the initial area number -- if a tablemove or indexmove is ever executed it will be wrong. You shouldn't use it to find an object's storage area.

define variable i as integer no-undo.

define variable dummy1 as character format "x(20)" label "Object Name".
define variable dummy2 as character format "x(20)" label "Parent Object".

define buffer stobuf  for _storageobject.
define buffer areabuf for _area.

form
    _storageobject._object-number format "->>>>9" label "Obj#"
    _storageobject._object-type   format ">>>9"   label "Type"
    dummy1
    dummy2
    areabuf._area-name format "x(20)" label "Parent Area Name"
  with frame a down.

find _area no-lock where _area-name = "Schema Area".
 
display today " " string( time, "hh:mm:ss" ) " Schema Check for DB: " pdbname(1) format "x(30)" skip.
 
for each _storageobject no-lock where
    _storageobject._area-number = _area._area-number and
    _storageobject._object-num > 7:

  case _storageobject._object-type:

    when 1 then         /* table        */
      do:
        find _file no-lock where _file._file-num = _storageobject._object-num.
        if not _file._file-name begins "_" then
          do:
            display
              _storageobject._object-number format "->>>>9" label "Obj#"
              _storageobject._object-type   format ">>>9"   label "Type"
              _file._file-name @ dummy1
            with frame a.
            down with frame a.
            i = i + 1.
          end.
      end.

    when 2 then         /* index        */
      do:
        find _index no-lock where _index._idx-num = _storageobject._object-num.
        find _file no-lock where _file._file-num = _storageobject._object-associate.
        find stobuf no-lock where stobuf._object-num = _file._file-num and stobuf._object-type = 1.
        find areabuf no-lock where areabuf._area-number = stobuf._area-number.
        if not _index._index-name begins "_" and
           not _file._file-name   begins "_" then
          do:
            display
              _storageobject._object-number format "->>>>9" label "Obj#"
              _storageobject._object-type   format ">>>9"   label "Type"
              _index._index-name @ dummy1
              _file._file-name @ dummy2
              areabuf._area-name
            with frame a.
            down with frame a.
            i = i + 1.   
          end.
      end.

  end case.
    
end.
         
if i = 0 then display "The schema area is clean.".

 

[BigSlick]

Hi Tom,

Many thanks for the advise and the code - is shall try to get this implemented. The code will help with moving the tables in the schema area out to a "unused" area but is there a VST that will highlight whether the tables have data in as these will need to be moved to a more appropriate area.

Thanks

 

[Cringer]

As far as I know, the only way to establish whether a table has data is to build a dynamic query and find the first record.

 

[TomBascom]

There are no VSTs that provide record counts. As Cringer says, the easy way to find out is to build a dynamic query and probe for the first record.

 

[Cringer]

Something like this should do the job:

define variable lv-Query as handle no-undo. 
define variable lv-QueryString as character no-undo. 
define variable lv-BufferName as character no-undo.
define variable lv-TableName as character no-undo. 
define variable lv-BufferHandle as handle no-undo.


for each _file no-lock
  where _file._hidden = false:
  
  lv-QueryString = "FOR EACH " + _file._file-name + " NO-LOCK".
  lv-BufferName = _file._file-name.
  lv-TableName = _file._file-name.
  create buffer lv-BufferHandle for table lv-TableName buffer-name lv-BufferName. 
  
  create query lv-Query.
  lv-Query:add-buffer(lv-BufferHandle).
  lv-Query:query-prepare(lv-QueryString).
  lv-Query:query-open.
  
  lv-Query:get-first.
  
  message lv-Query:query-off-end view-as alert-box. 
  
  lv-Query:query-close.
  delete object lv-Query.
end.

 

[Rob Fitzpatrick]

If you want to find information on which tables to move where, you should run a proutil tabanalys or dbanalys. That gives you record counts for all the tables, as well as information like mean record size that will help you choose appropriate records-per-block settings for the areas you create (or help you decide which areas to move tables to, if the areas already exist).

I suggest you get used to parsing through tabanalys data, even though it's a pain. If it "takes forever" you can run it overnight from cron or equivalent. Alternatively, you could get table record counts from the SQL side, if your DB has SQL servers. I wrote a program that parses _File to build a SQL script where each line is "select count(*) from PUB."<this table>";" and then run the script from sqlexp. It runs in only a couple of minutes on a DB of about 15 GB on a slow machine. If you just want record counts it may be quicker and certainly is less read-heavy than a tabanalys.

Tom, regarding your code I have a couple of questions. First, can LOBs reside in the schema area? I thought I remembered hearing somewhere that they have _Object-type = 3, which is not in your case statement. My memory is a little foggy on this; I don't use LOBs in my DBs.

Second, more a comment than a question, but your code will not find tables 1 through 7, if they are in the schema area. Application indexes are numbered from 8 onwards, but table numbers start at 1.

 

[TomBascom]

How very strange! I wonder what I was thinking... (I wrote that a long time ago...) My best guess is that I must have been thinking about the schema area -- it looks a bit like the code that I use to run through areas. So I would imagine that my head was in one cloud while my fingers were busy coding something else

As for LOBs. I think you're probably right but I don't have a db with any LOBs handy to test with.

 

[Stefan]

Something like this should do the job:

define variable lv-Query as handle no-undo. 
define variable lv-QueryString as character no-undo. 
define variable lv-BufferName as character no-undo.
define variable lv-TableName as character no-undo. 
define variable lv-BufferHandle as handle no-undo.


for each _file no-lock
  where _file._hidden = false:
  
  lv-QueryString = "FOR EACH " + _file._file-name + " NO-LOCK".
  lv-BufferName = _file._file-name.
  lv-TableName = _file._file-name.
  create buffer lv-BufferHandle for table lv-TableName buffer-name lv-BufferName. 
  
  create query lv-Query.
  lv-Query:add-buffer(lv-BufferHandle).
  lv-Query:query-prepare(lv-QueryString).
  lv-Query:query-open.
  
  lv-Query:get-first.
  
  message lv-Query:query-off-end view-as alert-box. 
  
  lv-Query:query-close.
  delete object lv-Query.
end.

No need for a complete query if you only want to do simple stuff (FIND-FIRST / FIND-UNIQUE) with the buffer:

DEFINE VARIABLE hb AS HANDLE NO-UNDO.
DEFINE VARIABLE lhas_record AS LOGICAL NO-UNDO.

FOR EACH _file 
   WHERE _file._hidden = FALSE
NO-LOCK:
  
   CREATE BUFFER hb FOR TABLE _file._file-name. 
   lhas_record = hb:FIND-FIRST( "", NO-LOCK ) NO-ERROR.
   MESSAGE hb:NAME lhas_record VIEW-AS ALERT-BOX.
   DELETE OBJECT hb.

END.