IBM Support

IBM i Topics



List of Scott Forstie's Gists and the IBM i topics covered in the examples.


You are in: IBM i Tutorials, Demos, and SQL examples > IBM i topics
GitHub Gists Topic
Activation Groups.sql
In this Gist, we see how we can query the Db2 for i Health Center and inquire what Environmental Limits shows as the jobs that consumed the most activation groups within a single job, where the activation group executed SQL. 
Activation Groups
Spreadsheets and Emails with SQL & ACS.sql
In this Gist, I show how you can leverage ACS's jar that is shipped on your IBM i to do your bidding. In this case, we can automate the creation and downloading of a spreadsheet to an IFS file where the contents of the spreadsheet is controlled with an SQL query. After the spreadsheet is created in the IFS, we use SQL once more to email the spre… 
Have we answered the questions.sql
Inquiry messages to the QSYSOPR message queue might be worth answering. Use this query to see which questions have not been answered today. #SQLcandoit
Inquiry Messages
HIPER PTFs and your IBM i.sql
In this Gist, there are two queries to tell you 1) If there are IBM i HIPER PTF Group levels missing from your IBM i and 2) Which IBM i HIPER PTFs are not installed? #SQLcandoit 
Job queue closing in on max active jobs.sql
How do you manage your job queues? Do you have caps on the maximum active jobs? Here's a technique for studying this topic....
Job Queue
Parse_statement udtf.sql
The parse_statement() UDTF has gone largely unnoticed. I'm adding it to my Hidden Gems of Db2 for i presentation. This gist is another fully functional example, which shows how to establish an exit program for ZDA traffic (ODBC users). Parse_statement provides the reliable technique for getting the job done.
Exit Program
Use ACS on your IBM i to build spreadsheets.sql
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS. 
Generating spreadsheets with SQL.sql
In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer.
Journal receivers attached or not.sql
I was asked to provide a way to find journal receivers and discern whether or not they are attached or detached.
Counting objects in a library.sql
There's frequently more than one way to code a solution. Today I was asked how to use SQL to count the number of objects within a library. There are two examples... one is very fast but requires IBM i 7.4.
Optimize journaled database files
Subtle options in how objects are journaled can have a big impact on performance.
Optimize local journals
Search for journals that can be easily improved
Display Software Resources
The Display Software Resources (DSPSFWRSC) command allows you to show, print, or write to an output file the list of installed software resources. This SQL example shows how to externalize the same detail by extracting message text and transforming numerics into integer form.
ZDA mystery solved
This example shows several things worthy of attention. System managers can utilize exit programs to establish improved auditing, understanding, and real time business rules using SQL. For QZDASOINIT jobs, it can be easily considered an unsolvable mystery. With the help of Db2 for i Client Special Registers, we can understand a great deal about ZD
Exit Program
GO SAVE Option 21 history via SQL.sql
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail there within into consumable history.
Data Area
Query Spooled File contents for a specific user.sql
What spooled files does the current user own?
Spooled Files
Microsecond DLYJOB
Delay job for fractions of a second
Delay Job
Restoring libraries that begin with the letter E.sql
Restoring libraries that begin with the letter E
Sending an E-mail via SQL.sql
Sending an E-mail via SQL
Job End and the CPF1164 message.sql
When a job is started, the CPF1124 message is sent to the history log. When the job ends, the CPF1164 message is sent to the history log. With SQL built-in functions, this example shows how SQL can extract and transform the CPF1164 job end message tokens into a useful form.
History Log Messages
Find save files.sql
I was asked to provide an SQL approach that could be used to identify save files that are needlessly chewing up storage. The following examples find those pesky save file and provide some context as to their size and usage.
Save Files
Generate pdf.sql
IBM i Access Client Solutions (ACS) includes many nifty features, like being able to save a spooled file as a PDF. This Gist shows how SQL can be used to programmatically take one or more spooled files and generate PDFs for them into the Integrated File System (IFS).
Spooled Files
Kitchen Sink for the Admin.sql
More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
Save Files
Inquiry Messages
Who am i.sql
I was asked to provide a query that pulls together some of the basic identity detail for the IBM i you're connected to... which got me to this....
System Information
Library sizes and more.sql
With Db2 PTF Group SF99703 level 22 and Db2 PTF Group SF99704 level 10 (aka TR9 and TR3 timed enhancements), the LIBRARY_INFO UDTF has optional input parameters to provide better performing queries for library specific questions.
Library Management
Managing MSGW jobs.sql
For this gist, I was asked to provide a query that would find jobs that have been stuck on Message Wait (MSGW) status for > 90 minutes. There's a 3 part progression to reach the solution.
Jobs in Message Wait
I was asked how SQL could transform Collection Services data, in this case Job Performance Data, into a more consumable form. SQL built-in functions and CASE expressions get the job done.
 Collection Services
QBATCH job study.sql
I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.
QBATCH Subsystem Analysis
Change command default.sql
Before an upgrade, rollswap, or just for good hygiene, its good to know which CL commands have had their command defaults changed. Here's an approach that works all the way back to IBM i 7.2.
Changed Commands
Compare the contents of two spooled files.sql
The challenge was simple... can SQL be used to compare the contents of two spooled files? The solution follows...
Spooled Files
Dashboarding storage capacity.sql
The request... return a simple to understand dashboard showing the basic storage detail, by database, with a percentage of storage used.
Alerting on high levels of jobs.sql
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
Responding to an inquiry message.sql
The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
Message Handling
The idea... open up SQL to sending text (SMS) messages. Surely this idea is well within our grasp. To capture the idea fully implies that the complexity needs to be encapsulated.
HTTP Functions
ddm server.sql
The request... use SQL to determine if the DDM/DRDA server was active, and if not, start it.
DRDA/DDM server
Find and read the SNTP activity log.sql
The request... find and query the most recent SNTP activity log.
Defective PTF Currency.sql
PTFs should help, not hurt. That's the credo, goal, and expectation. But... sometimes things go the wrong way. This gist shows how to use SQL to consume an IBM provided resource, compare what you have locally and most importantly, tell you if you are exposed to a known defective PTF.
HTTP Functions
Are programs in QRPLOBJ being used.sql
The request here was simple, are there active jobs that had objects in QRPLOBJ on the stack? The solution was a little tricky, because jobs can end in the middle of doing the analysis.
Active Jobs
Audit Journal Management
I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit
Audit Journal Management
Query Supervisor - Holding a job.sql
The request... show how Query Supervisor could be used to HOLD a job. The criteria for which situations merit a job being held are left to the reader. The example shows how QS could react to a long running query issued by an interactive user.
Job Management
PTF Cover Letters.sql
The request... show how SQL can be used to narrow the field down to those PTFs that have special instructions, and only return the special instructions. The following example focuses on what an admin might do AFTER loading PTFs, but BEFORE applying them.
Using LICOPT and initAuto.sql
The inspiration for this Gist came from a client. A piece of code had a long-standing problem where a variable was not initialized. Given the unpredictable nature of uninitialized made the topic hard to approach. This Gist shows how the IBM i Optimizing Translator can be used to find such problems within a dev or test environment.
IBM i Optimizing Translator
Collection Services made easy with SQL.sql
The Collection Services (CS) config and CS data hold a goldmine of operational insight about the IBM i. This Gist shows how a little bit of SQL can open the door to gaining insight and value from this data.
Collection Services
iSee Video Tutorials Topic
Convert Spool files to PDF easy with SQL
There are many ways to convert spool files to PDFs.  Many are interactive though, which of course makes doing some in a scheduled or programmatic manner difficult!   There is NOW an easy solution to the issue, the Generate PDF helper function delivered in SYSTOOLS.  This means that now with a simple SQL statement you can find and convert spool files to PDF simple as pie.  In this session Scott and Tim show you this new tool with a live demo.
Spooled files

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000C4BAAU","label":"IBM i"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Document Information

Modified date:
17 September 2023