IBM Support

SQL Examples

News


Abstract

List of GitHub Gists by Scott Forstie that demonstrate specific SQL language features.

Content

You are in: IBM i Tutorials, Demos, and SQL examples > SQL Examples
GitHub Gists SQL Language Feature(s) Used
Who owns the most objects.sql
Someone asked me how to use SQL to understand who owns the most objects. The following is a progression of queries to shine a light on this topic.
UNION
Grouping
  
IFS breakdown objects by subdir.sql
Someone asked if you could count the stream file objects, by directory. This solution gets the job done and includes the total size count as well.
Grouping
Common Table Expression
Count active user jobs.sql
We were asked... how do you count the number of active user jobs "right now" and see the answer ordered by highest to lowest count. The answer is found below and I've included a version of the SQL that will work for anyone still using IBM i 7.1
Grouping
How to discern where to find todays Collection Services info.sql
If you want to automate analysis of Collection Services, you can use this approach to use SQL to discern the library in use by Collection Services (CS) and the member name in the CS files that corresponds to the current day.
ALIAS
Dynamic SQL
Compound SQL
Global variable
Data validation of a string.sql
In a classic two-for-Tuesday move, another string based and built-in function solved question has come in.
Translate
Extracting data from a string.sql
The SQL language is so robust, there's often more than one way to accomplish a task. In this Gist, I demonstrate how to extract the job user name from a qualified job name.
Built-In Global Variable
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
Common Table Expression
Send row changes to a data queue using JSON.sql
This example shows how easy it is to pull together a data streaming service using SQL, JSON, triggers, and a Data Queue.
JSON
Hidden Columns
Bringing order to the IFS.sql
This example picks on the IFS stream files found within and under the /tmp directory. How much gunk has been accumulated under /tmp and what can you do to manage it? A bit of SQL to the rescue. The IFS_OBJECT_STATISTICS() UDTF returns many elements of data for you to leverage for improved management of the IFS.
Dynamic SQL
Compound SQL
VARCHAR_FORMAT
Extract SQL DML insight from the Plan Cache.sql
I had a client ask me... how do we see who is executing SQL INSERT, UPDATE, or DELETE statements? There are different approaches to this topic, with the best of breed answers including Guardium's Database Activity Monitor support for Db2 for i. That being said, if you want instance insight, look at your SQL Plan Cache. (before IPL'ing please)
CREATE TABLE AS
Common Table Expression
Read only views.sql
Some views can be used to INSERT, UPDATE or DELETE data in an underlying physical file or SQL table. This example shows how the Database Engineer can construct the SQL view to be read only.
View
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....
Common Table Expression
Flexible view over a mmpf.sql
At a recent webinar, I was asked whether a flexible view could be constructed such that it consumed a subset of members within a multiple member physical file. This is a working example of how to leverage a Db2 for i Global Variable as the external control for such view. The member processing is encapsulated within a User Defined Table Function…
ALIAS
GLOBAL VARIABLE
Calculating an ALLOCATE value for a column.sql
Database Engineers sometimes need to identify data models with varying length columns, where the allocate clause could be improved. When you use the ALLOCATE(n) clause, you're telling the database to establish n number of bytes for the column in the fixed portion of the record. If the column value for a row has a length > n, the database uses the…
Dynamic Compound
Common Table Expression
TopN user storage report.sql
This example takes a previous example and extends it. The idea here is that you want to proactively manage user consumption of storage. For the top storage consumers, return a report that lists their largest objects (either in QSYS or IFS) and provide some contextual detail.
VARCHAR_FORMAT
Common Table Expression
Object ownership by user - total report.sql
This gist combines several IBM i (SQL) Services to produce an easy to consume UDTF. Pass in a user name and you'll get back all the QSYS and IFS objects they own, ordered by size descending.
VARCHAR_FORMAT
Common Table Expression
ALLOBJ users with default passwords.sql
Security implementations can and should be monitored closely and on a regular cadence. This is one example where SQL can be used instead of the Analyze Default Passwords (ANZDFTPWD) command.
LIKE
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.
Common Table Expression
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
Common Table Expression
SQL generated table comparison query
SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparison query.
Exception Join
LISTAGG
IS NOT DISTINCT
Mask birthdays with RCAC
This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.
MASK
Optimize journaled database files
Subtle options in how objects are journaled can have a big impact on performance.
Compound SQL
SQL Environmental Limits
Review the top consumers of SQL resources since the last IPL.
Declared Global Temporary Tables Like
3-part naming for UDTFs
For User Defined Table Functions (UDTFs), a trick needs to be employed. Include a where clause whose only purpose is to push the UDTF invocation to the remote database!
3-part Naming
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…
CREATE TABLE AS
dynamic VALUES INTO
This example shows how to use dynamic SQL (PREPARE and EXECUTE) to implement a VALUES INTO statement.
VALUES INTO
Dynamic SQL
ROUND vs QUANTIZE
This example shows that QUANTIZE can be used along with decfloat rounding mode to achieve programmer control of rounding numeric values.
ROUND
QUANTIZE
VALUES
Common Table Expression
JSON_TABLE and survival tips for shredding JSON with SQL
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
JSON_TABLE
Remove *IOSYSCFG from users and groups
This example shows the power of the REGEXP_REPLACE built-in function, when combined with IBM i Services for security and dynamic SQL.
Dynamic SQL
Regular Expression
Common Table Expression
Dates and TIMESTAMP_FORMAT
Formatting date data into true date and time date types
VARCHAR_FORMAT
TIMESTAMP_FORMAT
Retrieve details for active 5250 sessions.sql
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
Common Table Expression
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.
TIMESTAMP_FORMAT
Row permissions control for ZDA access.sql
This row permission can be used to disallow specific jobnames from selecting data over a specific file
Permission
Query Spooled File contents for a specific user.sql
What spooled files does the current user own?
Common Table Expression
MTI related Index advice
Examine the condensed index advice where Maintained Temporary Indexes (MTI) have been used since the last IPL
Common Table Expression
Publishing file contents using JSON and SQL.sql
Publish the data within a table using SQL
JSON
ARRAY_AGG
Dynamic SQL
Common Table Expression
Remove noise from text strings using TRANSLATE.sql
Remove noise from text strings using TRANSLATE
TRANSLATE
Authority Collection.sql
IBM i DB2 security - Scott's examples on create mask - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtmask.htm
CREATE MASK
CREATE PERMISSION
Smaller Index is nearing a limit.sql 
I had a client reach out this week because they encountered the maximum size for an index. (ouch) They had some indexes that were constructed to use a smaller maximum size than what's possible for SQL indexes. This query allows them to monitor when their Max 4GB sized indexes have grown to the level that they are over 60% of the max size. 
ROW_NUMBER OLAP
Common Table Expression
Largest MTIs in use today.sql
Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into this topic! 
Lateral Correlation
Common Table Expression
When a job is started, the CPF1124 message is sent to the history log. When the job end, 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.
INTERPRET
TIMESTAMP_FORMAT
Tracking ALLOBJ users through time
The idea of this Gist is to take a step beyond access to a live view of who has *ALLOBJ user special authority, to also being able to see how the topic is changing over time. With the addition of a time dimension, clients can more easily focus on the delta changes.
Exception Join
Hidden columns
MERGE Statement
Auditing Columns
Common Table Expression
Kitchen Sink for the Admin.sql
More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.
VARCHAR_FORMAT
LISTAGG
ROW_NUMBER
Dynamic Compound Statement
ALIAS
TIMESTAMPDIFF
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.
RUNSQL
GLOBAL VARIABLE
VARCHAR_FORMAT
Well isnt that special.sql
I was asked to provide a technique for the SQL user to access the special authorities granted to user and group profiles, and return the data in a non-list form for ease of reporting and analysis. To accomplish this request, I used the SYSTOOLS.SPLIT table function, but had to be careful to use the perfect split character (3 spaces), trim off…
SPLIT
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.
Lateral correlation
Common Table Expression
Interactive Users and usage.sql
This gist shows a glimpse into the realm of what's possible when using SQL and IBM i Services to monitor and manage interactive users.
Grouping
Inner join
Lateral correlation
Controlling adopted authority.sql
If you use adopted authority, how do you avoid allowing code that you call from taking a free ride on your elevated authority? One answer lies within this gist...
SET SESSION AUTHORIZATION
Auditing a job queue
I was asked, "how can you determine which user held a job queue"? While there is more than one approach to answering this question, here's an example that leverages the secure audit journal log.
INTERPRET
Gist look at the library list
I was asked how object_statistics could be used with *LIBL and *USRLIBL to produce accurate and ordered results. Gist look at this...
Inner join
Lateral correlation
Protect the IFS root for *PUBLIC
When an IFS directory includes W (write), you are exposed to malware attacks. Use this to review and overcome this topic for the all important ROOT directory.
REGEXP_REPLACE
Row level auditing.sql
I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.
Hidden columns
   Row change timestamp
SQL DDL with nc.sql
SQL DML includes the WITH NC clause to avoid having the data change participate in the transaction. SQL DDL does not include the WITH NC clause, but the savvy SQL user can leverage an AUTONOMOUS procedure to achieve the same behavior.
Dynamic SQL
 Autonomous Procedure
 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.
CASE expression
TIMESTAMP_FORMAT
prtprvaut.sql
Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit
Alias
Dynamic SQL
Compound SQL
INTERPRET
TIMESTAMP_FORMAT
VARBINARY_FORMAT
Employment Days.sql
I was asked how SQL could compute the number of days difference between two dates columns, where one column might contain NULL. The timestamp_format and timestampdiff built-in functions get the job done, with a little help from coalesce.
COALESCE
TIMESTAMPDIFF
TIMESTAMP_FORMAT
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.
CUBE
ROLLUP
GROUP BY
INTERPRET
INNER JOIN
TIMESTAMPDIFF
Common Table Expression
IFS_search_replace_and_create.sql
I was asked how ifs_read and ifs_write could be combined to build a new IFS stream file, where certain character strings are replaced.
REGEXP_REPLACE
Authority collection - split to the rescue.sql
This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.
  SPLIT
GROUPING
  RIGHT EXCEPTION JOIN
  Common Table Expressions
Extracting the IFS filename from an absolute path name.sql
The request was, if you have an absolute path, how can SQL extract the filename from the path? One approach is found below.
SUBSTR
REGEXP_INSTR
REGEXP_COUNT
Virtually done.sql
Does your physical data model include a virtual layer? If no, this gist is for you...
Dynamic SQL
SQL alternative to the command ANZDFTPWD ACTION(*NONE).sql
The request was, is there an SQL alternative to the command ANZDFTPWD ACTION(*NONE)? The answer was a resounding YES.
GROUPING
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...
UNION
ROW_NUMBER
EXCEPTION join
Common Table Expression
Searching the IFS for objects with "log4j" in the name.sql
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
RUNSQL
CREATE TABLE AS
Using lateral correlation to combine SQL services.sql
In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?
VALUES
LATERAL
UNION ALL
LEFT OUTER JOIN
Object owners with exclude authority.sql
A client asked how they could identify which objects were set to *PUBLIC *EXCLUDE (good!), but where the object owner also had *EXCLUDE (not so good). Herein lies one solution.
Common Table Expression
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...
Common Table Expression
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.
Inner Join
Exception Join
Common Table Expression
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.
WRAP
HTTP_GET
HTTP_POST
mti_info.sql
The idea... discover MTIs and replace them with permanent indexes
Left Outer Join
REPLACE
TIMESTAMPDIFF
ddm server.sql
The request... use SQL to determine if the DDM/DRDA server was active, and if not, start it.
Case Expression
Common Table Expression
Find and read the SNTP activity log.sql
The request... find and query the most recent SNTP activity log.
HEX
HEXTORAW
INTERPRET
RTRIM
Common Table Expression

[{"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:
06 June 2022

UID

ibm16347660