Sprezzatura :: Making Databases Happen

By Sprezz | Tuesday 2 June 2009 00:45 | 4 Comments

Following on from our post about extra long row ids causing trouble with pre 4.6 versions of the UDH and LHVerify it became apparent that we'd have to write a utility to scan every single table on the system for aberrant ids. So this is all very straightforward right? Open SYSTABLES, build a table list, loop through opening and select the table checking id length. A job for a junior programmer right?

Wrong!

Whenever you open a table, the handle for the table is put into field 5 of that table's row in SYSTABLES. Regretfully although we refer to a "row" in a "table", SYSTABLES is actually an in memory variable manipulated by the RTP50 BFS. So as it is an in memory variable in AREV this has a maximum length of 64K. So if this gets to the limit you're going to see an RTP50 crash! On BIG systems the strong likelihood is that you've never had all of your tables open before - and guess what? When you do.... !RTP50 Variable Exceeds Maximum Length'...

When I first started selling Revelation C one of the joys was explaining to programmers of other systems that "You don't need to CLOSE files after opening them, the system does all that for you". How I have come to rue those words... (and yes, we did say files then, not tables. Catch me on an off day and I'd probably do the same now).

What we needed to prevent this RTP50 problem was to be able to close the table when we'd finished processing it (thereby keeping the SYSTABLES table small and manageable) but the BFS doesn't expose such a call - which is slightly strange given that BFSs work with any kind of filing architecture.

Originally we developed our own routine to achieve close functionality but a comment to this blog post from M@ drew our attention to an undocumented OI routine called CLOSE_TABLE which was actually introduced in AREV 3.11 with a syntax of

CLOSE_TABLE(lpTableName, bCloseAssociatedTablesAsWell, bSuccessFlag))

Usage is straightforward. To close the table, simply pass the table name to this function and the handle will be removed from attribute 5 of the SYSTABLES row for the table. To close the table AND the dictionary and index table, add a second parameter of True$ to the call. To see if the call was successful examine the third parameter.

Now the only problem you'll face is working out which tables are safe to detach without shooting yourself in the foot.

Labels: Close, CloseTable, RTP50

4 Comments:

What about CLOSE_TABLE() in OI - what does that do?
- M@

By Anonymous, At 2 June 2009 at 01:15
The original posting referenced a Sprezz routine called ZZX_CLOSETABLE which we developed to "close" an AREV table. In M@'s comment above he queried the OI routine CLOSE_TABLE() which made us retrace our steps.

One of Revelation's oft repeated laments is that people don't read the release notes enough. Well, guilty as charged. Not only is CLOSE_TABLE in OI, it was docced in the AREV 3.11 release notes.

The original blog posting has been modified to reflect this info and a big thumbs up to M@!

By Sprezz, At 2 June 2009 at 09:24
Ahh - Arev 3.11! Thanks for that - I couldn't remember where on earth I'd learnt about that routine, let alone what the additional parameters where for.
Cheers - M@

By Matt Crozier, At 2 June 2009 at 22:56
In fact we've gone back to using zzx_CloseTable as Close_Table also detaches the table which is not something we required in our utility - quite the opposite in fact.

By Sprezz, At 2 June 2009 at 23:29

4 Comments:

Post a Comment