Last active
April 10, 2023 19:05
-
-
Save forstie/a05cfa239ce8f907d96885bd7ac7bc6e to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Subject: Undocumented IBM i Services UDTFs | |
-- Author: Scott Forstie | |
-- Date : March, 2023 | |
-- Features Used : This Gist uses undocumented UDTFs for IBM i (SQL) Services | |
-- | |
-- Notes: | |
-- =============================================== | |
-- 1) IBM i (SQL) Services frequently have SQL Views, which are documented here: | |
-- https://www.ibm.com/docs/en/i/7.5?topic=optimization-i-services | |
-- 2) In many cases, there are one or more UDTFs referenced in the SQL View definition. | |
-- IBM does not document all of these "behind the scenes" UDTFs, because the View is typically better than the UDTF. | |
-- The views return more columns and sometimes the view transforms the data returned into a more complete form. | |
-- 3) In most cases, the UDTF will perform better than the view | |
-- 4) Even though the UDTFs are undocumented, IBM i users can choose to code directly to the UDTFs. | |
-- 5) Like the views, the UDTFs reside within the QSYS2 schema. | |
-- 6) To discover the UDTFs used by a view: | |
-- select view_definition from qsys2.sysviews where table_schema = 'QSYS2' and table_name = <name of view>; | |
-- 7) To discover the UDTFs used by a view: (bonus version) | |
-- select distinct ps.schema, ps.name | |
-- from qsys2.sysviews, lateral ( | |
-- select * | |
-- from table ( | |
-- qsys2.parse_statement(SQL_STATEMENT => view_definition) | |
-- ) | |
-- ) ps | |
-- where table_schema = 'QSYS2' and | |
-- table_name = 'OBJECT_LOCK_INFO' and | |
-- name_type = 'FUNCTION' and | |
-- schema is not null; | |
-- | |
-- The remainder of this script shows how to code to the UDTF alternative to the OBJECT_LOCK_INFO view. | |
-- | |
stop; | |
-- The OBJECT_LOCK_INFO UDTF returns fewer columns than the OBJECT_LOCK_INFO view. | |
-- If you prefer to code directly to the UDTF, you need to pass in 4 parameters. | |
-- | |
-- 1) LIBRARY_NAME => ?, /* VARCHAR(10) */ | |
-- 2) OBJECT_NAME => ?, /* VARCHAR(10) */ | |
-- 3) OBJECT_TYPE => ?, /* VARCHAR(10) */ | |
-- 4) IASP_NUMBER => ? /* INTEGER where 0 is used for *SYSBAS */ | |
-- | |
-- OBJECT_LOCK_INFO view documentation: | |
-- https://www.ibm.com/docs/en/i/7.5?topic=services-object-lock-info-view | |
stop; | |
-- | |
-- The following 2 queries return the same information... take your pick on which style to use | |
-- | |
select job_name, lock_state, MEMBER_LOCK_TYPE, LOCK_SCOPE, SYSTEM_TABLE_MEMBER as member_name | |
from qsys2.object_lock_info | |
where object_type = '*FILE ' and | |
system_object_schema = 'QIWS' and | |
system_object_name = 'QCUSTCDT' | |
order by lock_state, lock_type; | |
stop; | |
select JOB_NAME, LOCK_STATE, MEMBER_LOCK_TYPE, LOCK_SCOPE, MEMBER_NAME | |
from table ( | |
qsys2.object_lock_info( | |
library_name => 'QIWS', object_name => 'QCUSTCDT', object_type => '*FILE', iasp_number => 0 | |
) | |
) | |
order by lock_state, MEMBER_LOCK_TYPE; | |
stop; | |
-- | |
-- The iasp_number for a *FILE object is dictacted by the IASP number of the library | |
-- | |
select IASP_NUMBER from table(qsys2.object_statistics('TOYSTORE3', '*LIB')); | |
stop; | |
-- | |
-- Which users and jobs are holding locks over this file? | |
-- | |
select * | |
from table ( | |
qsys2.object_lock_info( | |
library_name => 'TOYSTORE3', | |
object_name => 'SALES', | |
object_type => '*FILE', | |
iasp_number => 144) | |
); | |
stop; | |
-- | |
-- description: What locks are held by any of Tim's activity | |
-- Note: *USRPRF objects are always found in the QSYS library, which is in *SYSBAS, | |
-- which is why the iasp_number is set to zero | |
-- | |
select * | |
from table ( | |
qsys2.object_lock_info( | |
library_name => 'QSYS', | |
object_name => 'TIMMR', | |
object_type => '*USRPRF', | |
iasp_number => 0) | |
); | |
stop; | |
-- | |
-- description: Who are the *ALLOBJ users? (avoid IBM shipped profiles) | |
-- | |
declare global temporary table session.super_users(user_name, default_password, text) as ( | |
select authorization_name, user_default_password, trim(text_description) | |
from qsys2.user_info | |
where user_creator <> '*IBM' and | |
special_authorities like '%*ALLOBJ%' or | |
authorization_name in (select user_profile_name | |
from qsys2.group_profile_entries | |
where group_profile_name in (select authorization_name | |
from qsys2.user_info | |
where special_authorities like '%*ALLOBJ%')) | |
order by authorization_name) | |
with data with replace; | |
stop; | |
-- | |
-- description: What are the *ALLOBJ users doing right now? | |
-- | |
select user_name, job_name, default_password, text, | |
regexp_replace(j.v_sql_statement_text, ' ', '') as sql_stmt, | |
v_client_ip_address as client_ip, v_client_applname as client_app, v_cpu_used | |
from session.super_users s, lateral ( | |
select * | |
from table ( | |
qsys2.object_lock_info( | |
library_name => 'QSYS', object_name => user_name, object_type => '*USRPRF', | |
iasp_number => 0) | |
) | |
) o, lateral ( | |
select * | |
from table ( | |
qsys2.get_job_info(job_name) | |
) | |
) j; | |
stop; | |
-- | |
-- If you read this far into the script, you deserve a bonus! | |
-- | |
-- Here is a partial list of the undocumented UDTFs: | |
-- QSYS2.ASP_INFO | |
-- QSYS2.ASP_VARY_INFO | |
-- QSYS2.AUTHORITY_COLLECTION | |
-- QSYS2.COLUMN_STATISTICS | |
-- QSYS2.CONDENSE_ADVICE | |
-- QSYS2.DATABASE_MONITOR_INFORMATION | |
-- QSYS2.ENVIRONMENTAL_LIMITS | |
-- QSYS2.GETATLINFO | |
-- QSYS2.GETMLBST | |
-- QSYS2.GROUP_USERS | |
-- QSYS2.INDEX_PARTITION_STATISTICS | |
-- QSYS2.JOB_QUEUE_INFO | |
-- QSYS2.MESSAGE_QUEUE_INFO | |
-- QSYS2.MQT_PARTITION_STATISTICS | |
-- QSYS2.NS_GET4CJOB | |
-- QSYS2.GET4CSTS | |
-- QSYS2.NS_GET4ISTS | |
-- QSYS2.NS_GET4RINF | |
-- QSYS2.NS_GET6CJOB | |
-- QSYS2.NS_GET6CSTS | |
-- QSYS2.NS_GET6ISTS | |
-- QSYS2.NS_GET6RINF | |
-- QSYS2.OBJECT_LOCK_INFO | |
-- QSYS2.OBJECT_PRIVILEGES | |
-- QSYS2.OUTPUT_QUEUE_INFO | |
-- QSYS2.PACKAGE_STMT_STATISTICS | |
-- QSYS2.PARTITION_DISKS | |
-- QSYS2.PARTITION_INDEX_DISKS | |
-- QSYS2.PARTITION_STATISTICS | |
-- QSYS2.PRIVILEGES | |
-- QSYS2.PROGRAM_STATISTICS | |
-- QSYS2.PTF_INFO | |
-- QSYS2.QDBTS_TS_INDEX_INFO | |
-- QSYS2.QDBTS_TS_INDEX_NAME | |
-- QSYS2.QJORTVJI | |
-- QSYS2.QMPROFILES | |
-- QSYS2.QPM_QAPMCONF | |
-- QSYS2.QSQENVVAR | |
-- QSYS2.QSQJOBSCDE | |
-- QSYS2.QSQLCINF | |
-- QSYS2.QSQLIBL | |
-- QSYS2.QSQRPYLE | |
-- QSYS2.QSQSRVAUTE | |
-- QSYS2.QSQSYSCOL2 | |
-- QSYS2.QSQSYSVAL | |
-- QSYS2.QSYFINFO | |
-- QSYS2.QSYFUSAGE | |
-- QSYS2.QSYUSRINFO | |
-- QSYS2.RECORD_LOCK_INFO | |
-- QSYS2.SCHEMAS | |
-- QSYS2.SERVER_SBS_ROUTING | |
-- QSYS2.SYSTMPSTG | |
-- QSYS2.TCPIP_INFO | |
-- QSYS2.USERS | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks Scott! this is very useful!