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 |
| 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… |
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 |
| Job End and the CPF1164 message.sql 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 |
Searching the IFS by name or date.sql
|
LOCATE_IN_STRING RTRIM Common Table Expression |
| 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. |
SPLIT RTRIM POSSTR HTTP_GET IN and NOT IN predicates Common Table Expression |
| 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. |
ORDER BY GROUP BY Common Table Expression |
| Bringing ORDER to a VIEW.sql The request... change Db2 for i to allow ORDER BY on CREATE VIEW. Well, we aren't going to do that because its non-standard. This gist shows a path forward using the existing support. |
View ORDER BY Dynamic SQL ROW_NUMBER OLAP Common Table Expression |
| Audit Journal Management.sql I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit |
TRIM SPLIT ORDER BY VARCHAR_FORMAT Common Table Expression |
| Undocumented IBM i Services UDTFs.sql This request has come in many times: Provide a UDTF alternative to a an SQL View for some of the IBM i (SQL) Services. My response is that the UDTFs already exist, are sometimes not documented, and all times are OK for users to query directly. |
LIKE ORDER BY Lateral Correlation |
| Decimal column checker upper.sql The request was this... I want to see how close some internal identification columns are to maxing out their maximum value. For example a counter that’s defined as DECIMAL(7,0) has a high value of 9,995,000 would indicate that we need to intercede ASAP. |
LIKE RPAD ORDER BY CASE expression Lateral Correlation Common Table Expression |
| 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. |
ALIAS ORDER BY Lateral Correlation Common Table Expression |
| db2_to_json.sql The request... Is it possible to extract data from IBM i into JSON format with a Db2 service? |
CAST RTRIM LISTAGG ORDER BY Dynamic SQL JSON_OBJECT |
| 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. |
LIKE ALIAS LTRIM RTRIM Dynamic SQL |
| 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. |
LIKE LIMIT VALUES ORDER BY |
| 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. |
LIKE ALIAS LIMIT OFFSET ORDER BY GROUP BY JSON_TABLE Lateral Correlation ROW_NUMBER OLAP Common Table Expression |
| Reset environment variables in a job.sql This gist comes from client requests to have a mechanism to "reset" environment variables within a job. |
ORDER BY IN and NOT IN predicate |
| coolstuff.read_joblog.sql Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic.... |
Global variable LISTAGG ORDER BY Common Table Expression |
| 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. |
CONCAT VALUES ORDER BY IN predicate Common Table Expression CREATE OR REPLACE TABLE |
[{"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:
24 April 2024
UID
ibm16347660