Troubleshooting
Problem
This document discusses how to use SQL to extract information from IBM Query for i (WRKQRY, Query/400) QRYDFN object types.
Resolving The Problem
When looking to get information about existing QRYDFN objects, there is not an API as such; however, in V5R4 and beyond, there is an SQL stored procedure called QSYS2.Print_Query_Definition. It has the following three parameters:
1. Query_library - The name of the library of the query
2. Query_name - The name of the query
3. Language_option
o 0 - Print the results based on the library list
o 1 - Print the results in English
You can call it using SQL to print the query definition.
Next, you can use the power of SQL by creating other procedures. The following procedure uses the above procedure to print the definition for several queries. For example, the following would print all query definitions in library MJATST:
1. You should use the following CL command to get an outfile of what query definitions you are interested in. This is the first step of the process that follows:
DSPOBJD OBJ(lib/*ALL) OBJTYPE(*QRYDFN) OUTPUT(*OUTFILE) OUTFILE(QTEMP/a);
1. Query_library - The name of the library of the query
2. Query_name - The name of the query
3. Language_option
o 0 - Print the results based on the library list
o 1 - Print the results in English
You can call it using SQL to print the query definition.
Next, you can use the power of SQL by creating other procedures. The following procedure uses the above procedure to print the definition for several queries. For example, the following would print all query definitions in library MJATST:
1. You should use the following CL command to get an outfile of what query definitions you are interested in. This is the first step of the process that follows:
DSPOBJD OBJ(lib/*ALL) OBJTYPE(*QRYDFN) OUTPUT(*OUTFILE) OUTFILE(QTEMP/a);
Adjust the library "lib" as needed
2. Create a procedure that loops through the outfile:
CREATE PROCEDURE lib.print_qrys ()
language sql
begin
for a as x cursor for select ODLBNM, ODOBNM from qtemp.a do
call QSYS2.Print_Query_Definition (ODLBNM, ODOBNM,0);
end for;
end;
call lib.print_qrys;
Calling the above procedure will generate some spooled files that you would then still have to look through manually; therefore, it is less work, but still very labor intensive.
3. We can extend the above procedure a bit by issuing the CPYSPLF command.
For example:
create table qtemp.b (line char(132));
create table qtemp.c (line char(132));
CREATE PROCEDURE lib.print_qrys2 ()
language sql
begin
declare clstmt varchar(1000);
declare clstmtlen int;
for a as x cursor for select ODLBNM, ODOBNM from qtemp.a do
call QSYS2.Print_Query_Definition (ODLBNM, ODOBNM, 0);
set clstmt = 'CPYSPLF FILE(QPQUPRFIL) TOFILE(QTEMP/B) SPLNBR(*LAST)';
set clstmtlen = length(clstmt);
CALL qsys2.qcmdexc (clstmt , clstmtlen );
insert into qtemp.c select * from qtemp.b b where rrn(b) IN(1,2,3) or line
like '%Output%';
-- delete from qtemp.b;
end for;
end;
call lib.print_qrys2;
select * from qtemp.c;
This time, the first three lines and the "Output line" are all placed in qtemp.c so you can see the four lines for all the query definitions that were printed together.
Note: Because of how spooled files work, you will probably have to run this from STRSQL in a 5250 session for CPYSPLF to work. If you see error CPF3303 - File QPQUPRFIL not found in job xxxxxx/xxxxxxxxxx/xxxxxxxxxx, the spooled file was routed somewhere else.
You can further modify the procedure to substring out only the information of interest in those four lines.
2. Create a procedure that loops through the outfile:
CREATE PROCEDURE lib.print_qrys ()
language sql
begin
for a as x cursor for select ODLBNM, ODOBNM from qtemp.a do
call QSYS2.Print_Query_Definition (ODLBNM, ODOBNM,0);
end for;
end;
call lib.print_qrys;
Calling the above procedure will generate some spooled files that you would then still have to look through manually; therefore, it is less work, but still very labor intensive.
3. We can extend the above procedure a bit by issuing the CPYSPLF command.
For example:
create table qtemp.b (line char(132));
create table qtemp.c (line char(132));
CREATE PROCEDURE lib.print_qrys2 ()
language sql
begin
declare clstmt varchar(1000);
declare clstmtlen int;
for a as x cursor for select ODLBNM, ODOBNM from qtemp.a do
call QSYS2.Print_Query_Definition (ODLBNM, ODOBNM, 0);
set clstmt = 'CPYSPLF FILE(QPQUPRFIL) TOFILE(QTEMP/B) SPLNBR(*LAST)';
set clstmtlen = length(clstmt);
CALL qsys2.qcmdexc (clstmt , clstmtlen );
insert into qtemp.c select * from qtemp.b b where rrn(b) IN(1,2,3) or line
like '%Output%';
-- delete from qtemp.b;
end for;
end;
call lib.print_qrys2;
select * from qtemp.c;
This time, the first three lines and the "Output line" are all placed in qtemp.c so you can see the four lines for all the query definitions that were printed together.
Note: Because of how spooled files work, you will probably have to run this from STRSQL in a 5250 session for CPYSPLF to work. If you see error CPF3303 - File QPQUPRFIL not found in job xxxxxx/xxxxxxxxxx/xxxxxxxxxx, the spooled file was routed somewhere else.
You can further modify the procedure to substring out only the information of interest in those four lines.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CQbAAM","label":"IBM i Db2-\u003EQuery\/400"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
520924468
Was this topic helpful?
Document Information
Modified date:
29 November 2024
UID
nas8N1013014