forstie
/ Bringing order to the IFS.sql
Last active Jun 1, 2020
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.
View Bringing order to the IFS.sql
| -- | |
| -- Reference material: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfifsobjstat.htm | |
| -- | |
| -- | |
| -- How much space is used by stream files from /tmp (and subdirs) that haven't been used in 6 months | |
| -- | |
| select varchar_format(sum(data_size),'999G999G999G999G999G999G999') tmp_size | |
| from table ( | |
| qsys2.ifs_object_statistics(start_path_name => '/tmp', |
forstie
/ ALLOBJ users coming in over non SSL network interfaces .sql
Created May 8, 2020
This is a probe of current network activity, showing what non-SSL interfaces are being used by users who have *ALLOBJ special authority. The idea here is to raise awareness to sensitive data being accessed over unencrypted interfaces.
View ALLOBJ users coming in over non SSL network interfaces .sql
| -- | |
| -- Reference: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajr/rzajrservicesandports.htm | |
| -- | |
| -- description: Which users with *ALLOBJ (either directly or indirectly via group profile membership) are accessing the IBM i via a non-SSL interface right now? | |
| -- | |
| select authorization_name as user_name, j.* | |
| from qsys2.netstat_job_info j | |
| where local_port in (23, 446, 449, 2001, 4402, 5544, 5555, 8470, 8471, 8472, 8473, 8474, 8475, 8476) | |
| and j.authorization_name in (select authorization_name text_description |
forstie
/ Extract SQL DML insight from the Plan Cache.sql
Last active Apr 18, 2020
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)
View Extract SQL DML insight from the Plan Cache.sql
| -- | |
| -- Title: Capture detail where SQL DML is being used | |
| -- Author: Scott Forstie | |
| -- Date: April 15, 2020 | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqprocdumppc.htm | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqprocextractpcstatements.htm | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/t1000.htm | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfparsestatement.htm |
forstie
/ read only views.sql
Last active Apr 9, 2020
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 read only views.sql
| -- Setup a sample database | |
| call qsys.create_sql_sample('HIS_STORE'); | |
| set schema his_store; | |
| -- Create a view over sales. The view qualifies as an updateable and deletable view. | |
| -- | |
| -- Background: | |
| -- Deletable views: A view is deletable if an INSTEAD OF trigger for the delete operation has been defined for the view, or if all of the following are true: | |
| -- ================ | |
| -- The outer fullselect identifies only one base table or deletable view that is not a catalog table or view. |
forstie
/ job queue closing in on max active jobs.sql
Created Apr 7, 2020
How do you manage your job queues? Do you have caps on the maximum active jobs? Here's a technique for studying this topic....
View job queue closing in on max active jobs.sql
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfactivejobinfo.htm | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqviewjobqueueinfo.htm | |
| -- Review job queues, from a percent of capacity consumed perspective | |
| select maximum_active_jobs, | |
| active_jobs, | |
| dec(dec(active_jobs, 19, 2) / dec(maximum_active_jobs, 19, 2), 19, 2) as jobq_percent_consumed, | |
| job_queue_name, job_queue_library, job_queue_status, number_of_jobs, | |
| subsystem_name, subsystem_library_name, sequence_number, held_jobs, released_jobs, scheduled_jobs, text_description, |
forstie
/ flexible view over a mmpf.sql
Created Mar 10, 2020
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 …
View flexible view over a mmpf.sql
| -- ==================================================================== | |
| -- description: Flexible view that uses a multiple member physical file | |
| -- author: Scott Forstie | |
| -- date: March 10, 2020 | |
| -- | |
| -- Create a global variable that will be used to create the select statement | |
| -- | |
| create or replace variable coolstuff.member_query_statement_text clob(1M) for sbcs data; | |
| -- |
forstie
/ calculating an ALLOCATE value for a column.sql
Created Mar 3, 2020
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…
View calculating an ALLOCATE value for a column.sql
| -- | |
| -- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule | |
| -- (80% of the time, the length of the column data would be less than or equal to n) | |
| -- minvrm: V7R3M0 | |
| -- | |
| create or replace function systools.compute_allocate ( | |
| p_schema_name varchar(128) for sbcs data, | |
| p_table_name varchar(128) for sbcs data, | |
| p_column_name varchar(128) for sbcs data, | |
| allocate_percentage decimal(3,2) |
forstie
/ parse_statement udtf.sql
Last active Mar 3, 2020
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.
View parse_statement udtf.sql
| -- ============================================================= | |
| -- Author: Scott Forstie | |
| -- Date : March 1, 2020 | |
| -- Description: ODBC user activity comes in over QZDASxINIT jobs. | |
| -- You can easily utilize a ZDA exit program to | |
| -- identify specific SQL activity, when you | |
| -- code your exit program to use QSYS2.PARSE_STATEMENT(). | |
| -- | |
| -- Here's the thing: | |
| -- With the QSYS2.PARSE_STATEMENT() UDTF, you do not need to invent your own parser! |
forstie
/ TopN user storage report.sql
Created Feb 9, 2020
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. This solution includes simple controls to allow the caller to specify t…
View TopN user storage report.sql
| -- #SQLcandoit #IBMiServices #Db2fori | |
| -- ====================================================================== | |
| -- | |
| -- Establish a UDTF that finds the largest N objects for a specific user | |
| -- | |
| -- ====================================================================== | |
| create or replace function systools.N_largest_objects_owned ( | |
| user_name varchar(10) for sbcs data, limit_size bigint default 10 | |
| ) |
forstie
/ Object ownership by user - total report.sql
Created Feb 2, 2020
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.
View Object ownership by user - total report.sql
| create or replace function systools.objects_owned( | |
| user_name varchar(10) FOR SBCS DATA) | |
| RETURNS TABLE ( | |
| object DBCLOB(16M) CCSID 1200, | |
| size varchar(50) FOR SBCS DATA, | |
| created timestamp, | |
| changed timestamp, | |
| used timestamp) | |
| SPECIFIC SYSTOOLS.objects_owned | |
| NOT DETERMINISTIC |
NewerOlder