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 program 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
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
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 a 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":"LOB08","label":"Cognitive Systems"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]

Document Information

Modified date:
20 October 2021