Progress 10.2B07/08. (Some Type I and some Type II areas... working on that one!)
Windows servers
We have a routine we run on a regular basis that assesses each storage area and looks at how much space is used. It's basically just looking at the high watermark though. We have one database in particular (audit) that is regularly purged. As a result the figure that's reported isn't actually a reflection of how much space there is available as there are empty blocks that can be reused.
I know I can run a dbanalys to get the information about how much space is free, but in a 24/7 environment there's never really a window to run this without causing performance impacts.
So is there some sort of trick I can employ to get the information I'm after easily from the VSTs etc, or do I just have to track area growth over time?
Bearing in mind I'm not the one actually monitoring this, rather someone in support with very little understanding of how things work at a structure level.
Dbanalys reports the information that mainly is not stored anywhere. That is why VSTs are not able to substitute dbanalys.
Workarounds:
1) Track the changes /after/ dbanalys using _TableStat/_IndexStat. Save the results before every db shutdown;
2) Parse the contents of the object blocks. It's sad that prostrct statistics does no report the block statistics per objects in SAT2;
3) You can almost instantly estimate the number of index levels. It's an indirect and very rough estimation of the table's sizes.
If you don't want DB Analysis to affect production then restore a backup of the database somewhere and run DB Analysis against the copy. I'm guessing you don't have OE Replication enabled otherwise you could run DB Analysis against one of the targets.
Thanks George and Paul. Using a backup isn't a bad shout - the DBs aren't massive. It's just a case of finding somewhere to restore it to. And it's not something you could automate that easily for 90 odd sites. As a one-off "let's check the state of play" it's a useful trick.
Running against replication is a good idea Paul, but yes most of the sites are without replication which is a shame, but understandable due to licensing costs.
James, if you just need to count the records then idxfix/2. Scan indexes /without/ recid validation would be a fastest way and with the least performance impact on database.
Validate recids for index entries.
Is this correct? (y/n)
n
Index 15 (PUB.Customer, Name): 83 keys.
1 indexes, 83 keys checked.
This would be a good time to remind [mention:7dc6ee8d33b54aa696ccb0bdd581e3b1:e9ed411860ed4f2ba0265705b8793d05] that there's an enhancement request floating around to add DB Analysis as an option to online backup...you know...since we're reading the whole DB anyways.
> to add DB Analysis as an option to online backup
It's not possible due to the free cluster chains. Their blocks contain data from the deleted objects. On block levels they look like a real data. Backup reads them and save them. But these data are just a garbage. Dbanalys always scans the free cluster chains and store their list in its own memory. We would not like for online backup to waste the time scanning the free cluster chains.
I'm not sure I understand George. If the DBA enables the optional DB Analysis flag to probkup online then he is consciously accepting the additional cost. Presumably the cost of the combined probkup+dbanalys would be less than running each separately.
> when you read a book do you read from front to back or random pages throughout the book?
The order of reading does not matter when you need to count the letters. ;-)
> If the DBA enables the optional DB Analysis flag to probkup online then he is consciously accepting the additional cost.
When online backup should scan the free cluster chains? When it locks BI file? The time to scan the chains (not only free cluster chains) can be much longer than the time of the sequential reads of the whole database.
> When online backup should scan the free cluster chains?
That's Rich's problem! :-)
IMHO, Progress development could implement a db daemon (like ai archiver or keyevent) that will update the results of the recent dbanalys stored in database itself. I don't think it would be hard to do.
And very simple enhancement would be to extend prostrct statistics with information from object blocks in SAT2. Plus to add it to VST.
> I don't think it would be hard to do.
A developer's favourite statement. ;)
"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:
CLISTBLK:
0040 nextBlock: 0x0000000000000000 0
prevBlock: 0x0000000000000000 0
numClusters: 0x00000000 0
clusterStart: 0x0000000000000000 0
lastDbkey: 0x0000000000000000 0
clist:
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?
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". ;-)
www.youtube.com/watch