richard_crist
asked on
AS400 Obtain sql from query definition
On the AS400 you can issue the command "WRKQRY" and work with query definitions. I have a lot of existing query definitions. How can I quickly obtain or generate the equivalent sql for each query? That is, as far as selecting, grouping, sequencing records goes, how can I quickly get the sql statement that will produce the same output?
Thank you!
Thank you!
RTVQMQRY command. It looks wrong, but it does the job. Specify the QRYDFN object on the QMQRY parm, and specify ALWQRYDFN(*YES).
- Gary Patterson
- Gary Patterson
ASKER
Thank you, Dave, for your answer earlier today to my other question.
After searching some more I found the question below here at EE.
Gary, does the CL shown there look like what you are preposing? What do you think about it?
https://www.experts-exchange.com/questions/20487332/Query-Documentation.html?sfQueryTermInfo=1+10+sql+wrkqry
Thank you both for your help. :)
After searching some more I found the question below here at EE.
Gary, does the CL shown there look like what you are preposing? What do you think about it?
https://www.experts-exchange.com/questions/20487332/Query-Documentation.html?sfQueryTermInfo=1+10+sql+wrkqry
Thank you both for your help. :)
If you want to rip through a lib (or set of libs) and cranks out SQL for each *QRYDFN object, this looks like it'll do it. The only catch is if there is a *QMQRY object with the same name as the *QRYDFN in the same lib. If that happens, then you will get the SQL from the QMQRY and the QRYDFN will be ignored.
If that happens in your system, I'd just move or copy the *QRYDFNs to a different lib and run the process over them there.
- Gary Patterson
If that happens in your system, I'd just move or copy the *QRYDFNs to a different lib and run the process over them there.
- Gary Patterson
ASKER
Thank you! I will test the CL and respond with my results.
ASKER
I have built and run the CL below and it works great. I now have a set of text files representing the SQL for each query on my system. This allows me to search the queries as SQL text files and find ones that already do what I need instead of reinventing the wheel.
When you have time please check the CL below and let me know what you think.
Thank you!
/************************* ********** ********** ********** ********** ****/
/* BUILD QUERY REFERENCES FILE */
/* */
/* Generate SQL source text for queries */
/* */
/* REVISION HISTORY: */
/* 4/4/2009 Richard Crist - Created based on information found on */
/* internet */
/* */
/************************* ********** ********** ********** ********** ****/
PGM
DCLF FILE(QADSPOBJ)
/* Clear physical file that will receive list of all query defs */
CLRPFM FILE(RCRIST/QRYLIST) MBR(*ALL)
MONMSG MSGID(CPF0000)
/* Produce output file of all query definitions in all user */
/* libraries */
DSPOBJD OBJ(*all/*all) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(RCRIST/QRYLIST)
OVRDBF FILE(QADSPOBJ) TOFILE(RCRIST/QRYLIST)
/* For each query in list (outfile), retrieve definition as Query */
/* Mgmt query source */
LOOP1:
/* Receive one line of input from the file containing list of queries*/
RCVF
MONMSG MSGID(CPF0864) EXEC(DO)
GOTO ENDLOOP
ENDDO
/* create physical file for the current query library; monmsg so that*/
/* we keep going if it already exists */
CRTSRCPF FILE(RCRISTQRYS/&ODLBNM) RCDLEN(240)
MONMSG MSGID(CPF0000)
/* use Retrieve Query Manager Query to generate the sql for the */
/* current query */
RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) +
SRCFILE(RCRISTQRYS/&ODLBNM ) ALWQRYDFN(*YES)
/* get next line from input file */
GOTO LOOP1
ENDLOOP:
DLTOVR FILE(*ALL)
ENDPGM
When you have time please check the CL below and let me know what you think.
Thank you!
/*************************
/* BUILD QUERY REFERENCES FILE */
/* */
/* Generate SQL source text for queries */
/* */
/* REVISION HISTORY: */
/* 4/4/2009 Richard Crist - Created based on information found on */
/* internet */
/* */
/*************************
PGM
DCLF FILE(QADSPOBJ)
/* Clear physical file that will receive list of all query defs */
CLRPFM FILE(RCRIST/QRYLIST) MBR(*ALL)
MONMSG MSGID(CPF0000)
/* Produce output file of all query definitions in all user */
/* libraries */
DSPOBJD OBJ(*all/*all) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(RCRIST/QRYLIST)
OVRDBF FILE(QADSPOBJ) TOFILE(RCRIST/QRYLIST)
/* For each query in list (outfile), retrieve definition as Query */
/* Mgmt query source */
LOOP1:
/* Receive one line of input from the file containing list of queries*/
RCVF
MONMSG MSGID(CPF0864) EXEC(DO)
GOTO ENDLOOP
ENDDO
/* create physical file for the current query library; monmsg so that*/
/* we keep going if it already exists */
CRTSRCPF FILE(RCRISTQRYS/&ODLBNM) RCDLEN(240)
MONMSG MSGID(CPF0000)
/* use Retrieve Query Manager Query to generate the sql for the */
/* current query */
RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) +
SRCFILE(RCRISTQRYS/&ODLBNM
/* get next line from input file */
GOTO LOOP1
ENDLOOP:
DLTOVR FILE(*ALL)
ENDPGM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry about the late response. Thank you both for your help. I gave some points to tliotta for the suggestion about the IF/DO statement. Thank you, Gary, for your reminding me about RTVQMQRY. Also, I will use QTEMP for auto cleanup as you suggested.
If memory serves, you can load WrkQry objects into WrkQMQry and export the corresponding SQL.
Try doing "11=Create from source" in WrkQMQry.
I haven't done that in a long time, but I know it was possible. Try it out.
HTH, DaveSlash