SQLTools for the IBM i Admin

We've built a pretty robust collection of SQL Functions and Procedures over the last two years in our #SQLTools product. Now we are using those tools to create easy-to-use IBM i System Admin features.

One of the popular SQL functions is the USER_LIST() table function. It allows you to list or retrieve some or all the information for one or more User Profiles on your system. Another is ACTJOB_LIST that lists WRKACTJOB-style information for the specified subsystems.

There is a performance advantage in using an SQL Function, such as USER_LIST or ACTJOB_LIST because you can pass in as parameters information that helps refine the resultSet list that is returned. So for example, if you only want to see users whose user profile beings with 'R' you can do that, likewise, you can specify that only QINTER jobs are returned instead of all jobs. Other non-SQL Tools methods often build the entire list of a objects and then throw away what isn't wanted by processing the WHERE clause. This is not efficient.

Using SQL Table Functions in RPG or SQL iQuery in code that is compiled or interpreted and run repeatedly is a great choice. You get the flexibility of and performance in your relatively static code. But what about when you need information interactively or want to create a listing to email to someone and you are typing it in by hand? That's where SQL Tools VIEWS come in.

SQL Tools VIEWS are a set of SQL VIEWS built on top of our SQL Tools table functions. The initial set was designed mostly for Systems Admin users with some for the RPG developer who has access to an SQL tool such as SQL iQuery, STRSQL, ACS Run SQL Scripts, or 3rd party tools such as dBeaver.

One of the first IBM i System Admin views we created is QLASTSIGNON. This view lists the last sign-on date/time along with a handful of user-related information for each enabled User Profile.

The standard we choose for naming these VIEWS is to start with the letter 'Q' (with one or two notable exceptions), followed by the rest of the VIEW name, while being as terse as possible. For example, to read your library list, the VIEW named QLIBL is used, while to read each User Profile's last signon date/time, the VIEW named QLASTSIGNON is queried.

Using VIEWS where practical is so much easier for the non-developer and certain technical users such as Systems Admins. With a VIEW you do get all the data returned to the internal workspace and then the WHERE clause is processed to filter out what you don't want. So these can be slower to run than the pure Table functions. But for Systems Admin and Developer queries, they're fantastic. For example, to produce a list of the last signon date/time for each user profile, all you have to enter is:

SELECT * FROM sqlTools.QLastSignon;
        

The output is pretty straight forward; it is a list of user profiles and the last time each user profile signed onto the system, the number of days since that last signon, and the number of invalid signon attempts have occurred since the last successful signon. It includes enabled user profiles only and only if those enabled users have a password (that is their password is not *NOPWD) and have actually signed on at some point to filter out the *USRPRF that can't sign on. On my system with a handful of users, the following list is generated.

I used SQL iQuery to produce this listing by running it from the Command Entry screen as follows:

RUNiQRY 'select * from sqltools.QLastSignon'        

You can use SQL iQuery or your favorite SQL processor that supports the SELECT statement.

Most SQL Tools VIEWS are unique and work with all IBM i SQL queries regardless of which SQL interface you're happy using. That's because whether you're using STRSQL, SQL Embedded in RPG IV, ACS Run SQL Scripts (ARSS), SQL iQuery, or even STRSQL, under the covers it all runs the same SQL engine.

IBM i does ship with some VIEWS of their own. One is the USER_INFO and another the USER_INFO_BASIC. If you wanted to replicate what we provide with our SQL Tools QLastSignon VIEW, using purely stuff that's included in your IBM i license, you could enter the following:

SELECT AUTHORIZATION_NAME AS USER_NAME
?????? CAST(PREVIOUS_SIGNON AS TIMESTAMP(0)) AS LAST_SIGNON,
?????? DAYS(current_timeStamp) - DAYS(PREVIOUS_SIGNON) AS DAYS_SINCE_LAST_SIGNON,
?????? SIGN_ON_ATTEMPTS_NOT_VALID AS INVALID_SIGNON_COUNT,
?????? TEXT_DESCRIPTION
??? FROM qsys2.user_info_basic
??? WHERE STATUS = '*ENABLED'
????????? AND PREVIOUS_SIGNON IS NOT null
????????? AND NO_PASSWORD_INDICATOR = 'NO';,        

The advantage of using a SQL Tools VIEWs over the default SQL Functions or some of the IBM VIEWS should be obvious. The syntax is cleaner, brief and consistent. Plus we include column headings!

Another major advantage in using SQL Tools VIEWS is that you can use SQL 3-level naming with these Views. For example, suppose I want to check the last time Users on our Chicago system or partition signed on (I like doing that at 7:30 AM to see who is running late <g>); using 3-level naming I can smply launch ARSS and type in something like this:

select * from chicago.sqltools.qlastsignon;        

Without the VIEW, the syntax for remote partitions gets a bit perplexing; It requires a WHERE EXISTS clause with a dummy subquery. I prefer the 3-level naming syntax because users are more likely to actually use the VIEW in this way.

VIEWs vs Functions

While the use of a VIEW is clearly easier from a syntax perspective, the SQL Tools Table Functions provide more flexibility and performance. They have a performance advantage due to passing in parameters that refine the resultSet being generated and returned. For example, if you have 8000+ User Profiles on your system, the use of the IBM-supplied QSYS2.USER_INFO View is painfully slow. Do you want your end-users waiting several seconds to a minute to have your RPG app check if a certain Group Profile assigned to the Current User? I don't. That was why I asked them to do better which they did, and provided the USER_INFO_BASIC View. It returns faster because it returns much less information. But you still end up with a view that shows every user profile. Which may not be what you want.

With SQL Tools, we have a USER_LIST() Table function that allows you to pass in one or more user profile names and it limits the scope of the resultSet to those *USRPRF objects. Typically Table Function results are produced in less time due to the limited resultSet. Basically to equate it to a CL command... If you did DSPOBJD *ALL *USRPRF vs DSPOBJD XXX* *USRPRF then the 2nd form would complete much faster. Same is true with IBM USER_INFO VIEW vs SQL Tools USER_LIST() table function.

New SQL Tools Views for #IBMi Systems Admin Users

As mentioned we've created several new SQL Tools VIEWS that simplify access to information often returned with our own SQL Functions or by calling IBM i APIs. These views have concise names and being with the letter 'Q'. There's even a View named QVIEWS that returns all the SQL Tools VIEWS there were installed.

Here's another example: To see the Active Jobs in the QINTER subsystem, just run an SQL SELECT statement over the SQL Tools QINTER View. The results are similar to the WRKACTJOB command, but in an SQL resultSet. Great when used with SQL iQuery or ARSS and you're working with local or remote partitions or systems. We've created a view for QINTER, QBATCH, QUSRNOMAX, and QHTTPSVR. Here's how easy it is to use:

SELECT * from sqlTools.QInter;        

Two others Systems Admin VIEWs I use regularly are QJOBLOG and QDISK. QJOBLOG simply lists the joblog in a much nicer looking format, while QDISK reports the status of your disk drives and checks for RAID Parity. It works on V7R2 and later. If you have just one system/partition then QDISK is about the same as using the WRKDSKSTS CL command. However, again when you have multiple systems/partitions using that 3-level naming really pays off:

SELECT * from CHICAGO.sqlTools.QDisk;
SELECT * from BAHAMAS.sqlTools.QDisk;
SELECT * from ARIZONA.sqlTools.QDisk;        

Well... you get it. Here's the list of the SQL Tools VIEWs we are shipped today. To see what we've added recently, just query the QVIEWS view and you'll get a similar resultSet with the latest VIEWs.

For decades CL Commands, such as those in COZTOOLS and the examples shipped in QUSRTOOL (named QATT tools) were the best way to be productive and get to system information. But today you can embrace SQL Functions to get to information and APIs quickly. You can still write directly to low-level APIs if you want to deal with that hot mess, but I sure don't. Today, in RPG if I need to process a list of Member name, I'll code a loop that reads the result set of our MBR_LIST() table function; no messy QUSLMBR API necessary.

I prefer to use SQL Tools because they work on all releases of IBM i from V7R2 through V7R5 so you can start using them today and rely on them in the future.

SQL Tools is one of the best packages we've created for customers using IBM i. It has all the SQL functions and procedures your IBM i shop needs to be productive and not reinvent the wheel. The addition of SQL Tools VIEWs has opened up this toolset to IBM i Systems Admin Users for the first time. They can now get to the information they need, with simple straightforward query statements instead of installing and learning myriad toolsets. Check it out at https://www.SQLiQuery.com/SQLTools

Leave a comment if you have an questions or don't see a View that you'd really enjoy having us add it to SQL Tools.

That's all there is to it.