Tech Refreshes Bring SQL-Based Services Galore For IBM i and Db2
October 16, 2023 Alex Woodie
With the latest Technology Refreshes, IBM unleashed another round of new and updated IBM i Services aimed simplifying life for administrators, developers, and other IBM i professionals. The company also refreshed its slate of Db2 for i Services.
Over the past decade, IBM has been busy rejiggering how users interact with core components of the operating system. Instead of issuing traditional CL commands or working with low-level APIs, users can tap into new SQL-based IBM i services via Access Client Solutions (ACS) to accomplish the same tasks.
Db2 for i Database Architect Scott Forstie and his team have created hundreds of IBM i Services and Db2 for i Services over the years. Both types of services are written in SQL and execute using the Db2 for i database’s SQL Query Engine (SQE). The main difference between them is that IBM i Services are aimed primarily at simplifying operating system-related tasks whereas the Db2 for i Services are aimed at helping with the managing the database and database development tasks.
Big Blue delivered eleven new IBM i services and enhanced another twelve more with 7.5 TR3, according to details found at the IBM i Technology Updates webpage. Meanwhile, it delivered one new Db2 for i service and enhanced three others.
New IBM i Services
We already told you about some of the new IBM i Services in last week’s story on the launch of IBM i 7.5 TR3 and 7.4 TR9, including the QSYS2.DB_TRANSACTION_JOURNAL_INFO table function in QSYS2, which is useful for determining how much longer a commitment control rollback will take. We also told you about a pair of new scalar functions in SYSTOOLS for automatically creating a spreadsheet from a file in the IFS (GENERATE_SPREADSHEET) and then emailing it to your boss (SEND_EMAIL).
Other new IBM i Services include new audit journal entry table functions for AU (Auditing Change) and LD (Link, Unlink, Search Directory) audit journal types. These new table functions exist in SYSTOOLS, and join many other IBM i Services for other types of audit journal entries. Click here for a full list of IBM i Services for the various audit journal entry types in IBM i 7.5.
Another new IBM i Service is ENDED_JOB_INFO, which is a table function that returns information about jobs that have ended. This information is gathered from CPF1164 messages recorded in the history log, according to the IBM Support page. The new JOB_QUEUE_ENTRIES IBM i Service is a table function that returns one row for each entry in a job queue. Both live in SYSTOOLS.
If you’re tired of using the Qp0lRenameUnlink() – Rename File or Directory, Unlink “new” If It Exists and Qp0lRenameKeep() – Rename File or Directory, Keep “new” If It Exists APIs to rename files or directories in the IFS (and really, who isn’t?), then you’ll be thrilled about the new IFS_RENAME scalar function in SYSTOOLS.
Testing your network connection can now be done with a new PING table function in SYSTOOLS. It replaces–or complements, rather – the PING or Verify TCP/IP Connection (VFYTCPCNN) CL command. The new PTF_COVER_LETTER table function in SYSTOOLS returns the cover letter content for a PTF.
Another new IBM i Service goes by the name REPLY_INQUIRY_MESSAGES. It is a scalar function in SYSTOOLS that replies to one or more inquiry messages in the QSYSOPR message queue. Its functionality is similar to the Send Reply (SNDRPY) CL command.
Enhanced IBM i Services
Administrators trying to track program temporary fixes (PTFs) through ACS will be happy with the enhancement to PTF_INFO, which IBM says was the original SQL service. With IBM i 7.5 TR3 and 7.4 TR9, the QSYS2 view (an IBM i Service, really) now shows whether there are any superseding PTFs, or replacement PTFs, for a given PTF.
“We never had returned what’s the latest superseding PTF for this PTF,” Forstie tells IT Jungle. “So it’s something to allow you, from a program perspective, to see the supersede chain. How does one PTF relate to something that came after it? That’s what the supersede is, and now we have that information, so you can detect that. You can know whether a PTF has been superseded, whether it hasn’t, and what it is. And then you could go on to the next one and ask the same question. So going up the chain like that is now possible.”
Another IBM i Service Enhancement that users have been clamoring for is the addition to CHANGE_USER_PROFILE, a table function that lives in SYSTOOLS. With this release, administrators can now an easy way to make changes special authorities in multiple user profiles from a central location.
“CHANGE_USER_PROFILE is a table function in SYSTOOLS. We already have that. But our COMMON America Advisory Council and others asked for more,” Forstie says. “They loved it. So now it’s enhanced so you can use that table function to add and subtract special authorities from multiple users in one query.”
While there are security tools for doing specific things with special authorities, the system has until now lacked a general-purpose way to make changes, Forstie says. For instance, the need to change special authorities may not be related to security at all.
“Some people are using it for more pragmatic things, like they want . . . users to stay off [a certain partition], so they have a way of downgrading or disabling users, but then making sure you bring them back,” Forstie says.
“Special authorities violate the rules of normalization,” he continues. “They have all these data values and one bit of information – ALLOBJ, one for print [SPLCTL], and one for SECADM – and all these other things. So you can say which one you want to remove or add without messing up the other ones. And each user might be different.”
IBM i automatically tracks important system limits. In IBM i 7.5 TR3 and IBM i 7.4 TR9, the automatic tracking system has been enhanced to detect when the maximum number of deleted rows in a partition has been exceeded.
The DB_TRANSACTION_INFO view in QSYS2 has been enhanced to let users see details about their transactions. In this case, the view returns one row for each commitment definition. It likely will be used in conjunction with the new DB_TRANSACTION_JOURNAL_INFO in QSYS2.
Another enhanced IBM i Service is the CHANGE_DISK_PATHS procedure in QSYS2, which has been updated to enable the administrator to specify exactly which path to a specific external SCSI disk adapter to keep open.
The DISPLAY_JOURNAL table function, which displays information about journal entries and is a complement to the DSPJRN CL command and QjoRetrieveJournalEntries API, has been enhanced. Administrators now have the option of specifying COMMIT_CYCLE and INCLUDE_INTERNAL input parameters, according to the IBM Support page on the IBM i Service.
Another enhanced IBM i Service is NETSTAT_JOB_INFO, a view that lives in QSYS2 that returns info about network connections. The view now includes info about the job name, job user, and job number.
IBM also added a new IGNORE_ERRORS parameter to REMOVE_TRACKED_JOB_QUEUE, which is an IBM i Service that’s part of the Submitted Job Tracker work management feature in QSYS2.
IBM has cleaned up the information that’s returned with the SYSTEM_VALUE_INFO view, which is an IBM i Service that lives in QSYS2. The view, which is similar to the QWCRSVAL API, has sports better formatting and new columns for TEXT_DESCRIPTION, CATEGORY, CHANGEABLE, and SHIPPED_DEFAULT_VALUE.
Users will be able to see the serial number and virtual serial number when using SYSTEM_STATUS_INFO, SYSTEM_STATUS_INFO_BASIC, and SYSTEM_STATUS views, which are QSYS2-based IBM i Services that are similar to the WRKSYSSTS and WRKSYSACT CL commands.
The previously shipped IBM i Service dubbed ADD_TRACKEDJOB_QUEUE, which is a QSYS2 procedure that makes it easy to add a job to job queue list (JQL), has been bolstered with the addition of a REMOVE_TRACKED_JOB_QUEUE procedure. Because sometimes you just need to remove the job.
Security has been enhanced in the USER_INFO_BASIC view, an IBM i Service in QSYS2 that returns information about user profiles. With this release, the password column always returns null values.
The LPRINTF procedure in SYSTOOLS has been enhanced. Users can now print strings that are up to 30,720 characters long.
New and Enhanced Db2 for i Services
The fall 2023 TR brings one new Db2 for i Service: SQLCODE_INFO. This SYSTOOL service is a table function that returns the SQL message associated with the specified SQLCODE value.
If you’re trying to cancel your SQL job using CANCEL_SQL, a table function in QSYS2, you’re in luck, because with this release, you no longer require authority to cancel your own jobs.
An enhancement to PARSE_STATEMENT, which is a table function in QSYS2 that returns a list of object and column names that are used in most SQL statements, adds support for most objects in DDL statements. IBM has added support for BUFFER_LENGTH and BUFFER_POSITION columns in SYSCOLUMNS2, the QSYS2 view that returns column attributes defined through DDS.
As always, you can find a plethora of information about all the IBM i TRs at the IBM i Technology Update page.
