Search SQL Source Using System Catalogs
May 15, 2013 Michael Sansoterra
The green screen PDM and the GUI Rational Developer for i (RDi) tools provide developers an easy way to search through source code to find a specified text literal. But what if these tools don’t have access to all your code, in particular the SQL code? Many developers may not be keeping an up-to-date source copy of their SQL persisted stored modules (functions, procedures and triggers) and further, may not be keeping a copy of the source at all. If this is the case in your shop, never fear, for DB2 for i maintains a record of the source statements in each SQL-based object that it creates. This information is stored in the DB2 catalog views and can be easily queried.
If you’ve never used them, the DB2 catalog tables and views contain the metadata about your databases. Within them you can find all table names, column names and data types, stored procedure names, registered Java JARs, etc. If you’ve ever wondered where an ODBC- or JDBC-based database tool gets its information about the tables and columns in your database, the answer is from these catalog views.
As long as the SQL source hasn’t been obfuscated, the SQL source code for an SQL object is stored in one of the following catalogs. (Remember, these catalogs don’t include the source for external routines written in C, COBOL, RPG, or Java.)
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.