Search SQL Source Using System Catalogs

Routine Type	System Catalog	Column containing SQL source
Trigger	SYSTRIGGERS	ACTION_STATEMENT
Stored Procedure	SYSPROCS	ROUTINE_DEFINITION
User-Defined Function	SYSFUNCS	ROUTINE_DEFINITION

These catalogs are located in schema QSYS2. An additional catalog view called SYSROUTINES contains data for both procedures and functions. (In this tip, I use the term “routine” to also include SQL triggers.)

Keep in mind that for older versions of IBM i OS (a.k.a. OS/400), these views may be incomplete for very large routines. For example, in V5R3, the ROUTINE_DEFINITION column in SYSPROCS was defined as VARCHAR(24000). If the routine source was longer than 24K, then this column would contain a NULL. In V7R1 (i7.1) the data type of this column is DBCLOB(2M). Unless IBM has some source recovery magic that is run during an OS upgrade, I’m guessing a stored procedure with 26KB of source created on V5R3 would not be present in the system catalog after the system was upgraded to i6.1 or i7.1. If nothing else, this is a good reason to ALWAYS keep a separate copy of your SQL source code.

Finally, note that the SQL source columns only contain the code embedded in the routine’s body. The CREATE statement and other options are not recorded in the source column (although this info can be found elsewhere in the system catalogs.)

For example, for this simple SQL based user-defined function:

CREATE FUNCTION DEV.TEST1 ()
RETURNS INT
LANGUAGE SQL
BEGIN
    RETURN 1;
END

The SYSFUNCS catalog’s ROUTINE_DEFINITION column will only contain the following text:

BEGIN
    RETURN 1;
END

To recover the full text of an SQL object’s source, including the CREATE statement and other options, use System i Navigator or use a command line utility like GENDDL that uses the Generate Data Definition Language (QSQGNDDL) API. But I’m digressing, as this tip is about finding where certain text lies within your SQL source kingdom.

So if you want to find where the text RECEIPT ID is used in within your SQL routines, how do you search them when the source is incomplete or missing? Shown here is a starter query that can be used as a basis to search the three system catalogs to identify routines that contain a specified literal to find:

-- Remove the UCASE functions if the search should be
-- case sensitive
WITH FIND_TEXT AS (
-- Include one or more search strings here
SELECT UCASE(FIND_VALUE) AS FIND_VALUE
  FROM (VALUES('%RECEIPT ID%')) SINGLE_ROW (FIND_VALUE)  
)
SELECT SPECIFIC_SCHEMA,SPECIFIC_NAME,ROUTINE_NAME,
ROUTINE_DEFINITION,
       'Procedure' AS TYPE
  FROM FIND_TEXT, QSYS2.SYSPROCS
 WHERE UCASE(ROUTINE_DEFINITION) LIKE FIND_VALUE
UNION ALL
SELECT SPECIFIC_SCHEMA,SPECIFIC_NAME,ROUTINE_NAME,
ROUTINE_DEFINITION,
       CASE FUNCTION_TYPE 
       WHEN 'S' THEN 'Scalar Function' 
       WHEN 'T' THEN 'Table Function' 
       ELSE 'Function' END AS TYPE
  FROM FIND_TEXT, QSYS2.SYSFUNCS
 WHERE UCASE(ROUTINE_DEFINITION) LIKE FIND_VALUE
UNION ALL
SELECT TRIGGER_SCHEMA,TRIGGER_NAME,TRIGGER_NAME,
ACTION_STATEMENT,
       'TRIGGER' AS TYPE
  FROM FIND_TEXT, QSYS2.SYSTRIGGERS
 WHERE UCASE(ACTION_STATEMENT) LIKE FIND_VALUE

Pretty easy stuff. One drawback is that this query only returns the routine name that contains the text. It returns the entire SQL text as a large string in a single row, but a developer can’t easily spot where the requested text lies. Unlike PDM and RDi, it doesn’t show a specific line of text in the results, which is often useful, especially if the requested search text occurs multiple times in a single source member.

This situation can easily be remedied by writing a recursive table function to split the source-code based on a line break character into individual source lines. Or, writing an external user-defined table function in a language like RPG or Java that will parse text data will accomplish the same thing. When this “splitter” table function is applied to the catalog views, the source code will be broken up line by line so that each line of source will have its own row in the query results.

If you’re on IBM i7.1 with the latest DB2 Group PTF, you can implement a hack to let the new XMLTABLE table function do the dirty work of splitting the rows for you without writing a separate function. The following CTE query will XML-ify the SYSPROCS ROUTINE_DEFINITION column by inserting a <Source> root tag and multiple <Line> tags whenever there is a line feed character detected in the SQL source. Once the source is in this XML format, XMLTABLE can be used to split the source code so that each line of source gets its own row in the result set.

For simplicity, this query only examines SYSPROCS, but it can be expanded to record all three catalog views:

-- Assume an embedded LF as a line separator  (EBCDIC X'25')
WITH SOURCE_XML AS (
SELECT SPECIFIC_SCHEMA,SPECIFIC_NAME,ROUTINE_NAME,
XMLPARSE(DOCUMENT '<Source><Line>'||REPLACE
(ROUTINE_DEFINITION,X'25',
'</Line><Line>') ||'</Line></Source>') AS 
ROUTINE_DEFINITION,
       'Procedure' AS TYPE
  FROM QSYS2.SYSPROCS
WHERE SPECIFIC_SCHEMA='DEV' -- Your library goes here
),
SOURCE_LINES AS (
SELECT SPECIFIC_SCHEMA,SPECIFIC_NAME,ROUTINE_NAME,TYPE,
LINE_NO,SOURCE
FROM SOURCE_XML, XMLTABLE(
'$doc/Source/Line' 
PASSING SOURCE_XML.ROUTINE_DEFINITION AS "doc"
COLUMNS 
Line_No FOR ORDINALITY,
Source VARCHAR(32000)  PATH '.'
) AS SOURCE_LINES
)
SELECT *
  FROM SOURCE_LINES
 WHERE UCASE(SOURCE) LIKE '%RECEIPT ID%';

Nothing to it!

In addition to literals, tools like RDi can also search for regular expression patterns. It just so happens that SQL can be enhanced (using a language like Java or C) so that it can utilize regular expression functionality. With a regular expression capable user-defined function like re_Test, the catalog search query can also utilize the power of a regular expression pattern match just like RDi can do.

The catalog views provide a wealth of information about the databases you work with, including information about the code. Use these views to aid the many database administration tasks that developers and DBAs face.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

Protect Your Intellectual Property: Obfuscate DB2 For i Source Code

Retrieving and Storing SQL Source for DB2 Database Objects

Build SQL to Define Your Existing Files

DB2 For i XMLTABLE, Part 1: Convert XML to Tabular Data

Staggering SQL String Handling with Regular Expressions

Staggering SQL String Handling with Regular Expressions

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot