forstie
/ printf to the Joblog using SQL.sql
Last active
June 23, 2023 12:37
printf to the Joblog using SQL
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
| -- | |
| -- description: printf to the Joblog, using SQL | |
| -- | |
| cl:addlible qsysinc; | |
| cl:crtsrcpf qtemp/qcsrc; | |
| cl:addpfm file(qtemp/qcsrc) mbr(LPRINTF); | |
| insert into qtemp.qcsrc values | |
| (1,010101,'{'), | |
| (2,010101,'extern int Qp0zLprintf (char *format, ...);'), |
forstie
/ Optimize local journals
Created
October 12, 2019 21:29
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
| -- | |
| -- Search for journals that can be easily improved | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm | |
| -- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
| -- | |
| select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number, | |
| journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library, | |
| message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay, |
forstie
/ Optimize journaled database files
Created
October 12, 2019 22:24
Subtle options in how objects are journaled can have a big impact on performance.
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
| -- | |
| -- | |
| -- Find journaled database files that can be adjusted for improved performance | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm | |
| -- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
| -- | |
| select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner, | |
| objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled, |
forstie
/ Use ACS on your IBM i to build spreadsheets.sql
Created
January 17, 2020 17:02
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS.
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
| -- | |
| -- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the | |
| -- acsbundle.jar onto the IBM i. Just apply the latest ACS PTFs and reference the jar | |
| -- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar | |
| -- | |
| -- Initial PTFs of ACS | |
| -- V7R4M0 SI71900 | |
| -- V7R3M0 SI71934 | |
| -- |
forstie
/ Object ownership by user - total report.sql
Last active
October 1, 2022 18:22
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.
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
| create or replace function systools.objects_owned( | |
| user_name varchar(10) FOR SBCS DATA) | |
| RETURNS TABLE ( | |
| object DBCLOB(16M) CCSID 1200, | |
| object_type varchar(10) for sbcs data, | |
| size bigint, | |
| sizeform varchar(50) FOR SBCS DATA, | |
| created timestamp, | |
| changed timestamp, |
forstie
/ calculating an ALLOCATE value for a column.sql
Created
March 3, 2020 20:11
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…
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
| -- | |
| -- 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) |
EdgardoEhiyan
/ gist:da94c085296776a297636d240222d855
Created
July 1, 2020 13:52
Statment SQL QSYS2.DISPLAY_JOURNAL ( for Object /Date/ and recivers(start-end)
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
| SELECT ENTRY_TIMESTAMP as JE_TIME, CURRENT_USER as CURRENT_USR, | |
| SEQUENCE_NUMBER, JOURNAL_ENTRY_TYPE,JOB_NAME, | |
| JOB_USER, JOB_NUMBER,PROGRAM_NAME, | |
| OBJECT,OBJECT_TYPE,PATH_NAME, | |
| CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) as F1, | |
| CAST(cast(substring(entry_data, 33, 130) as char(130) for bit data) as CHAR(130) CCSID 1141) as F2, | |
| HEX(cast(substring(entry_data, 163, 258) as char(258) for bit data)) as F3, | |
| HEX(cast(substring(entry_data, 421, 6) as char(6) for bit data)) as N1, | |
| HEX(cast(substring(entry_data, 427, 11) as char(11) for bit data)) as N2, | |
| HEX(cast(substring(entry_data, 438, 4) as char(4) for bit data)) as N |
forstie
/ Largest MTIs in use today.sql
Created
October 27, 2020 17:59
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!
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
| -- | |
| -- Gist: Largest MTIs in use today | |
| -- | |
| -- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more... | |
| -- | |
| -- | |
| -- | |
| -- Find the tables with the 10 largest MTIs | |
| -- |
forstie
/ Library sizes and more.sql
Created
November 27, 2020 18:15
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 paramters to provide better performing queries for library specific questions.
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
| -- category: IBM i Services | |
| -- description: Librarian - Library Info | |
| -- minvrm: V7R3M0 | |
| -- | |
| create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default | |
| 'create or replace table coolstuff.library_sizes | |
| (library_name, schema_name, | |
| -- qsys2.library_info() columns |
forstie
/ Protect the IFS root for *PUBLIC
Created
February 8, 2021 19:48
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.
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
| -- | |
| -- 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 | |
| -- | |
| -- For help on this or related security topics, contact Robert and team... | |
| -- http://ibm.biz/IBMiSecurity | |
| -- Robert Andrews - robert.andrews@us.ibm.com | |
| -- | |
| stop; |
OlderNewer