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 |
| Reset environment variables in a job.sql This gist comes from client requests to have a mechanism to "reset" environment variables within a job. |
Environment Variables |
| coolstuff.read_joblog.sql Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic.... |
Job logs |
| Stay Current.sql The age old problem is this... IBM recommends that IBM i clients stay up to date on software updates... but how does a client get current and stay current? This confluence of technologies highlights one approach to consider. |
PTFs |
| Data driven emails from IBM i.sql The request... send emails using data driven email recipient specifications. The implementation is 100% SQLcandoit. |
| 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 |
| Don't be Fooled by Spool Spool, we all have it. What and how we deal with it are the real questions. In this session Scott and Tim will look at some ways to first actually understand what spool is on your system as well as who is generating it and then we will take it a step further showing you how to actually clean up and delete spool files. The last thing we will discuss is actually looking in to the spool files to find common strings, and details to help you actually understand the contents. |
Spooled files |
| Never Run out of Storage System storage is never a problem right up until it is and then its a major problem. There are controls on the system that can help give you that early warning. In this video, Scott and Tim take a look at these early warning setting, as well as some fantastic SQL that will help you parse, understand, and review the messages that are created when you reach an out or storage threshold. This SQL is a great building block for any query you might want to use when looking at any messages that get sent to places like QSYSOPR or the History Log. |
Storage |
| PTF Compare How do you PTF ? How do you determine if your system has the latest PTFs on it ? There are several options of course! But not to go about things the easy way, lets toss a wrench in the mix... I want to see if my partition is on the latest PTFs, BUT, this partition does not have access to the Internet! Now what ? Leveraging 3 part naming, you have a magic solution. In this session we will be looking at using 3 part naming to build a 'service' that can call to the IBM Service Planning Web Page, and compare to the list of PTFs currently on your partition and show you what missing. |
PTFs |
| SQL Being Used by Navigator Scott and Tim show you some great SQL using IBM i Services, and how these are the backbone for the new Navigator. We will have fun with talking a new look at how SQL can be used by both IBM and how we continue to make it even easier for you to use in your own activities. |
System management |
| Fabulous Tools in SysTools When you buy a car, often its the accessaries that help make the car easier to driver, more fun, comfortable, in short it makes better. Your IBM i is no different, there are all sort of 'Accessories' that have been added to the operating system. One of these best kept secrets is SYSTOOLS. The database team continues to deliver some pretty interesting tools that can help you with gaining a better understanding of the system in some cases, better security practices, or just help you with general system management in faster and better ways. In this session Scott and Tim will play with a few of these newer SYSTOOLS showing you how they work and what they can do for you. |
System management |
| Printer Devices Using Magic Today there is NO SQL service for getting the list of printer devices. If you want to automatically integrate your devices.. it's just not easy. One of our ISVs asked us is there a way to do that with SQL. At first the thought was no...there is no SQL services. BUT, upon review we thought, we have a toolbox of services that can be used and if you combine a few...walla! You have your list devices in an automated manner. In this iSee we will unwrap how to use a CL command that only creates spool data, use some nifty SQL to pull apart that spool file to build a table with the data we are after. This iSee while sure it give you the printer devices, it's far more, it's a learning guide to some very powerful SQL techniques. |
Print devices |
| Journal Receiver Growth Monitor We all have Journal Receivers, and I am sure you all at some point have wondered how large are they. Which of course is nice to know, but what if you could watch the and monitor the growth of your Journals over time.... Now, that might not only be investing, but could prove enlightening! In this session, Scott and Tim look at how to solve this problem. This iSee is really a template that you could use to watch and monitor growth of not just Journal, but pretty much anything you might find interesting. We are putting Temporal tables to work in a practical way. This video shows you how simple it is to accomplish. When you are done you have working framework, today showing Journal receiver growth, but easily leveraged for your favorite topic in the future. |
Journal receivers |
| iSee Libraries, IASP, Spaceships and Galactic Monsters In this iSee video Scott and Tim are going to tackle the topic of understanding what is in your libraries and IASP. We get asked on a regular basis, Can I tell how large a library is? What is the biggest object in the library ? Should I be concerned about what’s filling up my IASP ? We are going to explore some interesting options for diving in and seeing if you have cute little space cruisers, or huge ugly galactic monsters hiding in your libraries. As always, we are providing the SQL so you can adjust to meet your specific needs. |
Library management |
| iSee What Jobs are using the IFS? Recently, we had an IBM i administrator ping us with a request from an Auditor. They needed to find out all the jobs on the system that were using the IFS, and then of course more specially, what in the IFS! So Scott dove into his toolbox of SQL services to answer this question and more! This iSee looks at the active jobs and IFS and shows some pretty interesting details. |
IFS jobs |
| iSee Rest APIs, from Dad Jokes to sending Text messages to your phone! In this session Scott and Tim have some fun with Rest APIs. The IBM i operating system has a built in Rest API engine combined with SQL you have a simple powerful way to call a Rest API out in the InterWeb. Yea… this means that you can do this with RPG and COBOL by simply imbedding the SQL in your ILE programs. We show you a fun API that returns a ‘Dad joke’. Think of how that can be used!!! Add a fun bit of humor to your application! Then we dive in looking at Geo position data. Using a street address, find your geo-location. Last we really spice it up by showing you a simple service that can be leveraged to send text messages to your phone from the IBM i!!! Bet you can't do that with your AS/400…. |
REST APIs Send text message |
| iSee System Management Toys During the PowerUp 2022 event, Scott and Tim recorded some iSee videos. In this video we take a look at a 4 separate system management items. We will review who is creating Spool Files and how to easily manage. We will look at Job Management. Have you ever wondered if the data in your database is correct ? For example, if you have a field that is numeric, are all the values in the DB all actually numbers ? Do you have character data? We will show you how to take a look at this. The last items is a way to see who is touching what objects. Not just putting auditing to work, but using auditing in amazing new ways. |
Spooled files Job management Object usage |
| iSee how SQL can go Beyond WRKACTJOB Work active job is for sure a key CL command and its SQL counterpart Active Job Info we have made the basis of many examples along the way. In this iSee, we are going to keep it simple, start with Active Job Info, and then combine it with one additional service to perform from useful regular task that we have been asked about. The idea is to not only give you useful scripts, but to help you with some basic combining of services to really help you unleash the power of these services. |
Active jobs |
| iSee Live from St Louis – Understanding Jobs Through the History Log During the Fall Navigate Conference Scott and Tim did a full length iSee session. Multiple topics were covered, this is the recording of one of these topics. Jobs come and go on your system. But do you know what jobs ? and how long they run ? Do you suspect that you have jobs that are running too long ? By interrogating the history log, you can get a view into the life and death of jobs on your system. So much to learn from this interesting technique. |
History log |
| iSee Do I Have Defective PTFs on my System? Defective PTFs, not something we talk much about and certainly not something that happens very often, but from time to time a PTF is created that we are required to mark as defective. When that happens, best practice is to get the replacement PTF on and approved at soonest possible time. Best to be sure what ever the problem of the defective does not affect you! This iSee will give you a script you can start using today. But it also take you though the journey, step by step, call a web page, digest the data on the page and turn into relational data. Compare that data to the PTFs currently on your system to see if you have Defective PTFs with no corrective PTFs applied |
PTFs |
| iSee Finding Old Large Non Used Objects We all have lots of objects on our systems, and I am sure we have some plans and practices in place to help with dealing with storage on your system. This iSee is something you can use to help with that. We will create a ‘data-mart’. A database, that has key bits of Meta data about all the objects on your system. Once this ‘data-mart’ exists, you can now quickly start to review the data. Do you have Large objects ? Who owns these objects ? Are they important ..ie when was the last time they were actually used!!! What about *SAVF objects. I know lots of people that like to create them ‘just incase’ and they just lay about forever after that. |
Object management |
| See Undeclared Intentions Our fine system is made up of applications. Many are ILE applications (RPG / COBOL). What happens in those applications when variables are not initialized ? Great question! Hard to know. Sure there are tools that can help scan your code to try and find these, but what if there was a way to possibly find these in Test? In this iSee, we will reveal an undocumented trick within the optimizing translator. Turn it on (QA only please…) set ALL uninitialized space to a known value, run your tests to see what shows up! |
Debugging |
| iSee Un-Documented UDTFs User Defined Table Functions are known to many of us as UDTFs. The QSYS2 library is home to many, undocumented, UDTFs, which serve as the foundation of IBM i (SQL) Services views. In this iSee episode, you will learn about these behind-the-scenes hidden gems, and learn how to leverage them for a purpose. |
System management |
| iSee – How to Automate the Extraction of Special Instructions from PTF Cover Letters How many of you actually read the PTF Cover letters before you applying them to your IBM i? Guessing not many read all of them. This iSee will help you by showing you how to use SQL to see the PTF Special Instructions for those PTFs about to be applied! |
PTFs |
| iSee – How to Publish SQL Services Data into JSON We have shown you all sorts of ways to call SQL and get data returned. Nice right? What about taking that data output to the next level, why not return the data as a JSON document. This iSee will give you a generic way to call virtually any SQL Service and have the results published as a JSON document. |
System management |
| iSee Manage Save Files Save files are an important object on our IBM i. They serve as the IBM i version of Zip files. It’s a way to scoop up objects (including their security attributes!) put them into a container where you can restore them some place else at a later time. While that is all well and good, I think many of us don’t really have an appreciation for how many save files we have out on a system ? How Large are they ? Did they come from this system ? or someplace else ? So many great questions that we can ask about the management of these files. This iSee will cover both the basics, as well as a deep dive on learning the inner details of these hidden mysteries. |
Save files |
| iSee the Data within Spool Files Spool files are core to the IBM i. Jobs run, Spool files are created. The question, how often do you consider looking at what was actually written to these files ? I am guessing not too often (unless you encounter a problem of course). Then how do you go about looking at that data in these things? I sure hope its not our friend the 5250 interface. This iSee will take a look at using SQL to unravel the secrets these files contain. You could even consider searching every file on the system in a proactive manner!! But, let not go too crazy. |
Spooled files |
| iSee Collection Services in a New Light Collection Services runs on all our systems. How often do you actually leverage the insights that may lie beneath the surface ? In this iSee we will take a look at some database health things that are easy to see once you unlock the hidden details within the collection services data. |
Collection services |
| iSee Go Save 21 Details If you use Go Save 21 this iSee is for you! There is a data area on the system that keeps track of the past several GO Save activities. This iSee will sort of the details of this data area and bring to light the number of saves, and the length of time each save took. Not only useful from the GO Save view, but some interesting SQL on how to parse text data and a little OLAP to help organize this data into something highly readable. |
System save |
| iSee Who is Messing With Environment Variables Environment variables are a powerful tool that can be leveraged to change the behavior of applications and processes. These objects are often over looked when it comes to the security of a system. In this iSee we will review all the environment variables, as well as see who is making changes to them. |
Environment variables |
| iSee Using VsCode with Code for i Developing code on IBM i is key for many companies, Either business applications or even programs for helping with managing the system. The interface for developing those programs needs to be considered. I know too many that are still using the green screen SEU line editor as their primary interface. Its time to move on… forward…step out of 1984 (yea.. I know the hair styles were something back then…) and into 2024. VsCode is an amazing interface for building and creating programs for IBM i. Complete with the amazing Code for i plugins, everything you need for developing programs on IBM i is there. Learn how to install, and some basic how to use steps in this iSee. |
VS Code |
| iSee Awareness for Old Things on your System The IBM i is celebrating 36 years now. With that much heritage, there could be some pretty old objects on your system. In this iSee we will scan the entire system for things like System 36 & 38 RPG and COBOL, PL1 (what !!!), or even Basic (did you know that even ran on IBM i?). You can see when these objects were last compiled, when it was last used and so much more that just might be useful. Awareness of what is really on your system and used can help guide you into the future. |
Object management |
| iSee VsCode Db2 for i Style In the previous iSee we learned the basics on how to use and install VsCode for your IBM i development needs. In this session we will venture down a path near and deer ( wonderful creature that likes to run out in front of your cars) to Scott, creating SQL for IBM i. This includes accessing databases, as well as getting the most out of your IBM i Services leveraging SQL. We will show you the power of this Db2 support that the Code for i team has been working on. Buckle up! |
VS Code |
| iSee Dead Jobs Dead Jobs! Seems a bit dark. In this iSee we are going to shine some light into the darkness. When a Job on your system ends, there are all sorts of nuggets of information about the life of the job, things like CPU usage, temp storage, how long did a job run, how long did it wait before running and such. Using some time tested SQL magic, we are going to do a postmortem on the jobs on the system to learn a great deal! Should be a fun! |
Jobs |
| iSee System Values System values on the IBM i are core system properties that control behaviors of many aspects of your IBM i. Things like Security, National Language, SQL runtime, Dates and Times, Auditing, and so much more. This iSee will dive into the latest updates to the System_Value_Info SQL Service. This service has been enhanced to show values that make sense to humans! I know.. what a weird concept. What the shipped default values were. We will give you some nifty SQL that shows you the values that are different than the shipped values, as well as how to easily compare the system values between 2 different systems. |
System values |
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"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:
12 June 2024
UID
ibm16340287