Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AS400 Obtain sql from query definition

Posted on 2009-05-04
9
Medium Priority
?
3,282 Views
Last Modified: 2013-12-06
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!
0
Comment
Question by:richard_crist
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24297618

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
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24297664
RTVQMQRY command.  It looks wrong, but it does the job.  Specify the QRYDFN object on the QMQRY parm, and specify ALWQRYDFN(*YES).

- Gary Patterson
0
 
LVL 3

Author Comment

by:richard_crist
ID: 24297757
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?

http://www.experts-exchange.com/Programming/System/AS_-_400/Q_20487332.html?sfQueryTermInfo=1+10+sql+wrkqry

Thank you both for your help.   :)

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24297861
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
0
 
LVL 3

Author Comment

by:richard_crist
ID: 24297867
Thank you!  I will test the CL and respond with my results.

0
 
LVL 3

Author Comment

by:richard_crist
ID: 24299376
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


0
 
LVL 35

Accepted Solution

by:
Gary Patterson earned 1600 total points
ID: 24299757
Sure looks good to to me.  Of course, my word isn't nearly as good as thorough testing.  One note:  As a standard, I create temp files like this in QTEMP so they get cleaned up automatically when the job ends.

- Gary Patterson
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 400 total points
ID: 24300558
At first glance, the CL looks fine.

The only change I'd make (after Gary's) would be to wrap the CRTSRCPF/MONMSG pair in an IF/DO-statement so that they're only executed when &ODLBNM actually changes. But that shouldn't change actual results -- it'll merely reduce the number of error messages that will otherwise clutter the joblog.

As Gary noted, the real test will be in the 'testing'.

Tom
0
 
LVL 3

Author Closing Comment

by:richard_crist
ID: 31580508
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question