Retrieve the SQL statements from multiple QM queries

The germ for this post came from a message I received:

Is there any way to retrieve sources for all QMQRY objects available inside a library in one go? My objective is, there are 100's of QMQRY objects(SQL type) inside a library but they don't have predefined source members. I need search for particular string inside all those SQL queries used inside the QMQRY objects.

I decided to work out a way I could do this.

I did not create "100's" of Query Management, QM, queries as what will work for two will work for many more too.

It is possible to retrieve the SQL statement from a QM query object by use of the Retrieve Query Management Query command, RTVQMQRYM. That command copies the retrieved statement into a source member.

Rather that have to search a multitude of members, it would be easier to search for strings in a DDL Table that contained all of the source statements retrieved from the QM queries.

I needed to breakdown this process into several steps:

1. Make a "list" of all QM query objects in a library
2. Use the RTVQMQRY command to retrieve the SQL statement into a source member
3. Copy the contents of the source member into, what I am calling, the search file

All of this is possible if I use SQL, and I can embed it into RPG source. The program is too long to include in one piece; therefore, I am going to show a piece at a time as I explain what it does. The is the first part of the program:

01 **free 02 ctl-opt main(Main) dftactgrp(*no) ; 03 dcl-ds Data qualified dim(*auto : 9999) ; 04 Library char(10) ; 05 Object char(10) ; 06 end-ds ; 07 dcl-proc Main ; 08 dcl-ds Single likeds(Data) ; 09 dcl-s Command varchar(100) ; 10 MakeFiles() ; 11 GetData() ;

Line 2: It is optimal in modern RPG to use a Main procedure. As this program contains subprocedures it cannot run in the default activation group.

Lines 3 – 6: The only "global" variable, available to all of the procedures, is an auto extending data structure array that can contain up to 9,999 elements. It contains subfields for the library and object names.

Line 7: Start of the Main procedure.

Line 8: I need a data structure to can contain each element of the data structure array. To make sure they have the same structure I use the LIKEDS.

Line 9: Defining a variable that will be used to contain a CL command.

Line 10: Call the subprocedure to create all the files this program uses.

Line 11: Call the procedure that loads the list of QM queries into the Data data structure array.

Rather continue with the Main procedure, it is easier to understand what this program does if I explain what these two subprocedures do. I am going to start with MakeFiles:

24 dcl-proc MakeFiles ; 25 exec sql CALL QSYS2.QCMDEXC('DLTF QTEMP/QMQRYSRC') ; 26 exec sql CALL QSYS2.QCMDEXC('CRTSRCPF FILE(QTEMP/QMQRYSRC) RCDLEN(112)') ; 27 exec sql CALL QSYS2.QCMDEXC('ADDPFM FILE(QTEMP/QMQRYSRC) MBR(FIRST)') ; 28 exec sql DROP TABLE IF EXISTS MYLIB.SEARCH_FILE ; 29 exec sql CREATE TABLE MYLIB.SEARCH_FILE 30 FOR SYSTEM NAME "SEARCHF" 31 (LIBRARY CHAR(10), 32 OBJECT CHAR(10), 33 CODE VARCHAR(100)) ; 34 exec sql DROP TABLE IF EXISTS QTEMP.OBJECTS ; 35 exec sql CREATE TABLE QTEMP.OBJECTS AS 36 (SELECT OBJLIB,OBJNAME 37 FROM TABLE(QSYS2.OBJECT_STATISTICS 38 (OBJECT_SCHEMA => 'MYLIB', 39 OBJTYPELIST => '*QMQRY', 40 OBJECT_NAME => '*ALL'))) 41 WITH DATA ; 42 on-exit ; 43 end-proc ;

Lines 25 – 26: I am using the SQL QCMDEXC procedure to delete and create a source file, QMQRYSRC in QTEMP, and add one member to it, FIRST.

Lines 28 – 33: Use the SQL Drop to delete the DDL table, and then create the search file, SQL Table name SEARCH_FILE in my library. I have given SEARCH_FILE a short system name, SEARCHF, which can be used with certain IBM i tools. The table contains three columns:

1. LIBRARY:  The library the object is in
2. OBJECT:  The object name
3. CODE:  The SQL statement was is retrieved from the QM query. This will be several rows of data for each QM query

Lines 34 – 41: I am creating DDL Table, OBJECTS in QTEMP, that uses the OBJECT_STATISTICS Table function return a list of all the QM queries in MYLIB.

I use the data in the OBJECTS Table in the next procedure, GetData, to load the data structure array.

44 dcl-proc GetData ; 45 dcl-s Elements uns(10) inz(%elem(Data : *max)) ; 46 exec sql DECLARE C0 CURSOR FOR 47 SELECT * FROM OBJECTS FOR READ ONLY ; 48 exec sql OPEN C0 ; 49 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 50 exec sql CLOSE C0 ; 51 on-exit ; 52 end-proc ;

Line 45: The value I initialize this variable with the maximum number of array elements the data structure array can have.

Lines 46 and 47: Define my SQL cursor.

Line 48: Open the cursor.

Line 49: Fetch the first 9,999 rows of the results into the data structure array.

Line 50: Close the cursor.

I am going to return to the Main procedure, just after GetData was called, to continue explaining what this program does.

12 for-each Single in Data ; 13 exec sql CALL QSYS2.QCMDEXC('CLRPFM FILE(QTEMP/QMQRYSRC) MBR(FIRST)') ; 14 Command = 'RTVQMQRY QMQRY(' + %trimr(Single.Library) + '/' + 15 %trimr(Single.Object) + ') + 16 SRCFILE(QTEMP/QMQRYSRC) SRCMBR(FIRST)' ; 17 exec sql CALL QSYS2.QCMDEXC(:Command) ; 18 exec sql INSERT INTO SEARCH_FILE 19 SELECT :Single.Library,:Single.Object,SRCDTA 20 FROM QTEMP.QMQRYSRC ; 21 endfor ; 22 on-exit ; 23 end-proc ;

Line 12: The FOR-EACH is used to "read" the data in Data the data structure array, into the Single data structure.

Line 13: Before I retrieve the SQL statement from the QM query I need to clear the source member the command will be putting the data into.

Lines 14 – 16: I am building the CL command that will use the RTVQMQRY command to retrieve the SQL statements into the source member FIRST.

Line 17: The QCMDEXC executes the RTVQMQRY, and the retrieved statements are placed into the source member FIRST.

Lines 18 – 20: I am using an SQL Insert statement to take the data from the source member, along with the QM query library and name, inserting the data into the search file.

Line 19: I am selecting the SRCDTA column from the source member, and the library and object names from the data structure.

This why I only need one source member, I keep reusing it for every QM query.

After compiling the program, I call it. When it has finished I can query the search file:

01 SELECT * FROM MYLIB.SEARCH_FILE

Which returns:

LIBRARY OBJECT CODE ------- ---------- -------------------------------------------- MYLIB TESTQMQRY1 H QM4 05 Q 01 E V W E R 01 03 DD/DD/DD TT:TT MYLIB TESTQMQRY1 V 1001 050 MYLIB TESTQMQRY1 SELECT * FROM TESTFILE ; MYLIB TESTQMQRY2 H QM4 05 Q 01 E V W E R 01 03 DD/DD/DD TT:TT MYLIB TESTQMQRY2 V 1001 050 MYLIB TESTQMQRY2 DELETE FROM TESTFILE WHERE FIELD1 = 'X' ;

I could delete the extra lines, those that start with the 'H' and 'V', if I wanted:

01 DELETE FROM MYLIB.SEARCH_FILE 02 WHERE SUBSTR(CODE,1,2) IN ('H ','V ')

Line 2: I want to delete any row that starts with 'H ' or 'V '. I can use the substring, SUBSTR, to compare the first two positions of the CODE column to the list of values in the IN. If there is a match the row is deleted.

Which would give me:

LIBRARY OBJECT CODE ------- ---------- -------------------------------------------- MYLIB TESTQMQRY1 SELECT * FROM TESTFILE ; MYLIB TESTQMQRY2 DELETE FROM TESTFILE WHERE FIELD1 = 'X' ;

Whether or not I delete those extra lines, I can now search the CODE column for the desired string. I would use SQL to do so. For those not as familiar with SQL could use Query/400 instead. When the desired string is found I can look in the LIBRARY and OBJECT columns to see which QM query has it.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.