Discerning how full a storage area actually is? - Forum - OpenEdge RDBMS - Progress Community

Discerning how full a storage area actually is?


Discerning how full a storage area actually is?

  • "Daemon" that updates dbanalys statistics is an easy task not only for Progress development but for 4GL progammers. Some sites use such approach. We need it as a built-in feature.

  • Does anybody in Progress World use keyevent daemon that populates the _KeyEvent system table from a database log file? Would it be "hard" for another daemon to populate the another system tables from the _TableStat/_IndexStat VSTs? How useful would it be to have an up-to-date dbanalys statistics directly in your database?

  • My 2 cents... Most modern databases keep track of at least space usage/row counts in near real time. Many will track much more detailed information than that either by default or by flipping a switch.

    This is one of those things that should just happen in an OE database. Once the initial analysis happens it is "just" tracking incremental changes.

    I realize it would take some work to accomplish but it would be nice to have some of the basic DB functions available in an OE database so I don't miss them as I switch back and forth from other databases :-)

  • >> When online backup should scan the free cluster chains?


    > That's Rich's problem!  :-)

    I guess Rich has a solution: Object Cluster List Block (a.k.a. "Cluster List Block", CLISTBLK, OBJ_CLIST_BLK).

    It's a bk_type 16:

    0040 nextBlock:        0x0000000000000000 0
         prevBlock:        0x0000000000000000 0
         numClusters:      0x00000000     0
         clusterStart:     0x0000000000000000 0
         lastDbkey:        0x0000000000000000 0

    They exist in database but they are not used. I guess the aim was to store in these blocks the list of the cluster's addresses. Not only for free cluster chain but for any database objects. If Cluster List Blocks would be implemented then dbanalys or probkup would not need to scan the chains (except for verification).

    Dbpr/13/3 shows the list of 19 block types. 4 of them never existed, another 4 types were never used. In other words some ideas were not implemented [due to ...].

  • > there's an enhancement request floating around to add DB Analysis as an option to online backup...

    Another enhancement request for idxcompact to report the index statistics. Utility reads the headers of index blocks and the blocks on the index delete chain - like ixanalys does. But it would be a better than ixanalys because we can run it individually per index while ixanalys can be run individually only per areas. Idxcompact reads the blocks in the native index order while idxanalys reads blocks sequentially. Hence idxcompact can report an additional statistics that ixanalys is unable to provide. If Progress will remove a limit in 50% for the degree of index compaction then 'proutil db -C idxcompact table.index 0' could, for example, mean "report the statistics but do not change the index blocks". Is it hard to implement?

  • There is an option in dbutil doing what you describe.  Try this and see if it works for you, it uses the idxcompact code and reports about the index and its blocks.  I don’t remember how detailed it is but it has been in existence as long as idxcompact.
    proutil dbname -C idxblockreport [owner.]tablename.indexname
                            [tenant tenantname | group groupname |
                             partition partitionname | composite name]
  • Yes, idxblockreport exists since V9.0A. Is it get officially supported in the recent Progress versions? It has some issues (at least a couple) in past. I did not check if they are fixed now.

    Yes, it reports the information from the headers of index blocks - one line per block:

    >proutil sports2000 -C idxblockreport Customer.name
    OpenEdge Release 11.6 as of Fri Oct 16 19:02:26 EDT 2015
    BlockSize = 4096  Block Capacity = 4068
                    Number  Length  On      Length  Delete
                    of      of      Delete  of      Chain           Percent
    DBKEY   Level   Entries Entries Chain   Size    Type            Utilized
    192     1       8       80      0       0       root            1
    288     2       162     3197    0       0       leaf            78
    544     2       142     3000    0       0       leaf            73
    320     2       132     2919    0       0       leaf            71
    576     2       144     3119    0       0       leaf            76
    256     2       112     2464    0       0       leaf            60
    672     2       124     2589    0       0       leaf            63
    352     2       187     3004    0       0       leaf            73
    512     2       115     2657    0       0       leaf            65
    Index Block Report completed successfully.

    But dbrpr since V10, for example, can report the contents of /any/ db blocks but it does not make dbanalys utility obsolete. In most cases we need a summary report rather than the detailed information. Sometimes idxblockreport is very useful but it can't be a replacement for ixanalys.

    > it uses the idxcompact code and reports about the index and its blocks.

    When I said "it's not hard to implement" I meant that Progress already has all "ingredients" (codes) to implement a feature. I would not say that table partitioning was easy to implement. It has required very large changes in database structure and in the existent code. Nevertheless table partitioning was implemented. The "little features" are not required such large efforts from development. So it's a matter of the priorities. But personally I believe that "little things mean a lot". ;-)