News
Abstract
List of Github Gists by Scott Forstie that demonstrate writing SQL PL Routines and related concepts
Content
You are in: IBM i Tutorials, Demos, and SQL examples > SQL Procedural Language (SQL PL)
| Github Gists | Routine Type | Concept |
|---|---|---|
| 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. |
TRIGGER | Multiple Event |
| 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 … |
FUNCTION | PIPE |
| 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 th record. If the column value for a row has a length > n, the database uses the… |
FUNCTION | PIPE |
| 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 establist an exit program for ZDA traffic (ODBC users). Parse_statement provides the realiable technique for getting the job done. |
PROCEDURE | |
| 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. |
FUNCTION | |
| 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. |
FUNCTION | |
| 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. |
PROCEDURE | |
| SWAP_DYNUSRPRF.sql With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach… |
PROCEDURE | |
| Temporalize a library.sql System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library. |
PROCEDURE | |
| SQL generated table comparison query SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparison query. |
FUNCTION | |
| Numbify Packed Decimals using SQL SQL scalar functions can transform data into information |
FUNCTION | INCLUDE |
| 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. |
FUNCTION | |
| Glengarry Glen Ross - SQL Style Ok movie buffs, here is a fictional before and after, with a happy ending. Use these techniques to bring database engineering to bear in your data center. |
TRIGGER | |
| 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. |
PROCEDURE | |
| GO SAVE Option 21 history via SQL.sql Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history. |
FUNCTION | PIPE |
| Change trigger programs in production.sql This example shows how to use the ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option within a specific job to avoid the requirement of needing an exclusive lock for the *FILE object, before making a change to a trigger program. |
TRIGGER | |
| Reuseable SQL code segments with INCLUDE Locate reusable pieces of SQL code into the IFS. Then, use INCLUDE to pull those segments into your SQL routines or triggers, and even ACS's Run SQL Scripts or RUNSQLSTM. |
Any | INCLUDE |
| Microsecond DLYJOB Delay job for fractions of a second |
PROCEDURE |
INCLUDE
BINDOPT
|
| printf to the Joblog using SQL.sql printf to the Joblog using SQL |
PROCEDURE |
INCLUDE
BINDOPT
|
| Restoring libraries that begin with the letter E.sql Restoring libraries that begin with the letter E |
PROCEDURE |
INCLUDE
BINDOPT
|
| Sending an E-mail via SQL.sql Sending an E-mail via SQL |
TRIGGER | |
| Publishing file contents using JSON and SQL.sql Publishing file contents using JSON and SQL |
FUNCTION | |
| 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. |
FUNCTION | |
| 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). |
FUNCTION | |
| 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. |
PROCEDURE | |
| Kitchen Sink for the Admin.sql More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell. |
FUNCTION | PIPE |
| 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... |
PROCEDURE | INCLUDE |
| 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. |
TRIGGER | INCLUDE |
| 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. |
PROCEDURE | AUTONOMOUS |
| 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. |
PROCEDURE | |
| 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. |
FUNCTION | |
| 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. |
FUNCTION | |
| Virtually done.sql Does your physical data model include a virtual layer? If no, this gist is for you... |
FUNCTION
PROCEDURE
|
|
| 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... |
TRIGGER | |
| 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. |
FUNCTION | |
| 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. |
FUNCTION | |
| 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. |
FUNCTION | PIPE |
| 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. |
FUNCTION | PIPE |
| 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. |
FUNCTION | PIPE |
| db2_to_json.sql The request... Is it possible to extract data from IBM i into JSON format with a Db2 service? |
FUNCTION | PIPE |
| 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. |
FUNCTION | PIPE |
| Reactive index strategy.sql Database and SQL performance tuning is a persistent focus, which many times leads into the indexing strategy. In this gist, I show how some of the existing tools can be tied together to achieve an automated "DBE in a box". |
PROCEDURE | |
| 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. |
FUNCTION | PIPE |
[{"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
ibm16348030