News
Abstract
List of Scott Forstie's Gists and the IBM i topics covered in the examples.
Content
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… |
STRQSH SNDSMTPEMM |
| 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 |
PTFs |
| 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. |
STRQSH SNDSMTPEMM |
| 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. |
STRQSH SNDSMTPEMM |
| 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. |
Journals |
| 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. |
Libraries |
| Optimize journaled database files Subtle options in how objects are journaled can have a big impact on performance. |
Journals CHGJRNOBJ |
| Optimize local journals Search for journals that can be easily improved |
Journals CHGJRN |
| 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. |
DSPSFWRSC |
| 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 |
RSTLIB |
| Sending an E-mail via SQL.sql Sending an E-mail via SQL |
SNDSMTPEMM |
| 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 PTFs Classes 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 |
| QAPMJOBL.sql 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. |
Storage |
| 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... |
QMAXJOB |
| 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 |
| send_sms.sql 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 QRWTSRVR QRWTLSTN |
| Find and read the SNTP activity log.sql The request... find and query the most recent SNTP activity log. |
SNTP |
| 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. |
PTFs 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 QRPLOBJ Library |
| 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. |
PTFs |
| 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. |
Debugging 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"}]
Was this topic helpful?
Document Information
Modified date:
17 September 2023
UID
ibm16340287