How to query Intersystems Caché to obtain database and license properties?

Question

Question

Hi,

I'm trying to determine if I can query Intersystems Caché to obtain database properties and license properties. For the database, I'm mostly interested in properties like the current size, maximum size, block size, and the directory associated with the database. For the licenses, I'm total authorized, current available, minimum available, current active users, and maximum active users.

Background

I know that details about the database and licenses are available using the System Management Portal, but I'm trying to automate some actions that depend on these details.

I know that the %FREECNT utility is available to display space statistics for the database, but the only way I'll be able to use this utility to obtain the info I need is to write a script using AWK or SED (the system is on a Unix server) and I'd like to avoid that since I'm not as well-versed in Unix scripting as I'd like to be.

I know the ^DATABASE routine and the $SYSTEM.License.ShowCounts() function are available, but I will have to use AWK and SED for these too to eliminate the text returned that I don't need. In all cases, straight SQL will return a set of data that I can iterate over that will eliminate the extraneous text that's included by the routines/functions.

Additional Info

I've written queries similar to the one below and I'm hoping there are equivalent tables for database and license that will allow me the same access:

Select * From %SYS.ProcessQuery Where Namespace = 'HL7'

I don't have access to Caché Studio, so I'm forced to use the command line on the server. I know I can use the SQL.Shell to enter SQL statements and, from the documentation, it looks like I can create routines from the command line, but I haven't found any documentation that will allow me to enter multiline statements for routines from the command line. If that's not possible, then I probably can't use routines in my solution.

Thanks for the help.

Answer 1

In your Caché routine, you can use Query Summary for get some license information, or other queries in class %SYSTEM.License
For Databases you can use queries from SYS.Database from %SYS namespace

Answer 2

I was able to get something close.

From the command line, I entered the following:

%SYS>SET rs = ##Class(%Library.ResultSet).%New()
%SYS>SET rs.ClassName = "SYS.Database"
%SYS>SET rs.QueryName = "FreeSpace"
%SYS>SET sc = rs.Prepare(rs.QueryName)
%SYS>SET sc = rs.Execute($LISTBUILD("/my/database/directory"), 0)
%SYS>WHILE rs.Next(){WRITE rs.Data("DatabaseName")," "_rs.Data("Size")," "_rs.Data("MaxSize"),!}

It's not quite as clean as I would have hoped because I have to write the entire loop on a single line, but that's better than nothing. At least the statement wraps across lines.