IBMi

DB2 queries and other tools for IBMi

DTASRCSP.sql - Get the list of Tables/Programs referenced by a Stored Procedure in Order as it appears in the Library List of the job

Uses output from DSPPGMREF to determine the objects referred by the Program Object and then use a Stored Procedure to retreive the list of Tables/Programs referenced by a Stored Procedure in the same order as it appears in the Library List. This helps determine which LIBRARY.TABLE is being referenced by your Stored Procedure versus what LIBRARY.TABLE is available to it as per the Job Library List.

Program Type: Stored Procedure (Link)

SEARCH.sh - Find source physical file members with multiple keywords

Shell script to search inside source physical file for members that has multiple keywords (both inclusive). This helps to look for a specific source that contains two (or more) terms. Find string function in IBMi limits you to a single string that can be searched.

Progrm Type: Shell Script (Link)

STR2ARR.SQLRPGLE - Parse a string and populate an array with the elements

Program to parse a string, delimited by a specific character into an array. Uses the SYSTOOLS.SPLIT function, to parse the input string instead of going with a looping approach.

Program Type: SQLRPGLE (Link)

TABLESINSP.sql - Get the list of Tables referenced by a Stored Procedure in the Order as it appears in the Library List of the job

Stored Procedures with Static SQL - Uses QSYS2.SYSROUTINEDEP to determine which tables are used in the Stored Procedure
Stored Procedures with Dynamic SQL - Reads the source of the Stored Procedure and determines the tables by mapping it to SYSIBM.TABLES

Once the tables are determined, it is mapped against the library list of the job to determine what table is available in the Libraries as indicated by the Library List for the Stored Procedure. This helps determine which LIBRARY.TABLE is being referenced by your Stored Procedure versus what LIBRARY.TABLE is available to it as per the Job Library List.

Note: Check DTASRCSP.sql that uses a different approach on this same requirement

Program Type: SQL (Link)

WHRISOBJ.sql - Find which version of an Object is seen by the Job in accordance to the Library List set up

While executing IBMi commands/programs from an external source, it gets difficult to determine if an object is present in the Library List of the Job or which version of the Object is seen by the Job in accordance with the Library List set up of the job.
The idea behind this script is to get the Library Listing of the Job and use it to determine which version of the Object is seen by the job.

Program Type: SQL (Link)

DeepSearch.sql - Get the list of Source Physical file Members with a specific keyword

The idea behind this script is to find that one source in the system, which you know exists but you just don't know the source physical file which houses that source. This SQL does the same function as a Find String with a difference that this can be used to search into All Source Physical Files in a Libarary or into All Source Physical Files in the system.

Note: Check SEARCH.sh that does a multi-layered search using a shell script.

NB: This script alters the Find String command parameters to print the results. Flip the parameters back as required

Program Type: SQL (Link)

FindString.sql - Do the equivalent of Find String from the Run SQL

Do the equivalent of Find String from the Run SQL window. This Stored Procedure also lets you search for multiple SRCPFs for a keyword in a single go.

Program Type: SQL (Link)

TextCleanUp.sql - Clean up characters in a string by replacing from a list of characters

Clean up non-printable or other characters in a string by replacing each character from a substitution list.

Program Type: SQL (Link)

AbrahamReuben/IBMi