Tuesday, August 20, 2013

Getting the SQL statement out of Query/400

I first learned about embedding SQL into RPG programs at a user group meeting many years ago. I could see it as a tool that would increase my productivity, but all I had was a paper copy of the slideshow that had been presented. The company I was working with had SQL loaded on their AS400 (IBM i), but none of their programmers used it. This was the time before Google, and I struggled to find resources about SQL especially about the different types of joins, etc.

Fortunately I stumbled across a way to retrieve the SQL statement from a Query/400 into a source member. Now I could build a Query close to what I want, then view the SQL to get an idea of how to make my own statement.

The RTVQMQRY command extracts the SQL statement from a Query Management, QM, query, and places it in a source member. Fortunately for the me it is also possible to do the same for a Query/400.

                 Retrieve Query Mgmt Query (RTVQMQRY)

 Type choices, press Enter.

 Query management query . . . . .   test      
   Library  . . . . . . . . . . .     mylib     
 Source file  . . . . . . . . . .   mysrc
   Library  . . . . . . . . . . .     mylib     
 Source member  . . . . . . . . .   *QMQRY    
 Allow information from QRYDFN  .   *yes 

In the example, above, I want to see the SQL for the Query TEST, which is in the MYLIB library. I want the source member to be created in the source file MYSRC, which is in the library MYLIB. I have left the 'Source member' parameter to be the default, *QMQRY, as I want the member to have the same name as the Query.

The important parameter is the last one, 'Allow information from QRYDFN'. Query/400 generates objects with the type QRYDFN, just as programs are PGM and files are FILE. Change this to '*YES' as object we want the SQL from is a Query/400. Press Enter and a member, with the name entered, is added to the source file.

H QM4 05 Q 01 E V W E R 01 03 13/08/28 05:00
V 1001 050
V 5001 004 *HEX
SELECT
  ALL       T01.ORDNBR, T02.ORDSEQ, T02.STATUS, T01.JOBNBR, 
            T01.CUSTNO, T02.WRKCTR, T02.STRDTE
  FROM      PRODLIB/ORDHDRP T01 LEFT OUTER JOIN
            PRODLIB/ORDDTLP T02
  ON        T01.ORDNO = T02.ORDNO
  ORDER BY  T01.ORDNO ASC, T02.OPSEQ ASC

The source, above, was extracted from a simple Query I created for this post. The first three lines contain the Query/400 specific formatting, and should be ignored. The remaining seven lines are the SQL statement. These can be taken and used elsewhere.

You can learn more about the RTVQMQRY command on the IBM website here»

 

This article was written for IBM i 7.1, and it should work with earlier releases too.

20 comments:

  1. It's a very good article and very handy option to get the SQL behind the WRKQRY.

    ReplyDelete
  2. This is useful information, thank you.

    ReplyDelete
  3. Looks like a great way of learning SQL for end uses who are fully proficient in Query

    ReplyDelete
  4. I think you're better off reading SQL manuals. WRKQRY will show all the joins as done via WHERE clauses instead of JOIN. And when you 'retrieve' an exception join it goes ill since that's doable with a WHERE clause.
    That being said, I've written a similar magazine article on RTVQMQRY about a decade or so ago so, yes, I have done it both ways.
    And I use sql instead of wrkqry even on quick and dirty's because there are so many times that I want to step outside of the comfort zone of wrkqry and I don't want the slightest temptation to not use this function or that function just because it's not available in WRKQRY and I've already invested this time in WRKQRY so is it worth stepping out into sql? Nope, start out in sql and avoid that temptation.
    Rob Berendt

    ReplyDelete
  5. THANKS!! This was an awesome tip! Like many shops, I have a myriad of legacy Queries that I have to maintain, but haven’t the time to convert to RPG. After reading this article, I immediately created a new source file for my Query source and new user-defined option in SEU specifying the RTVQMQRY command. In a matter of five minutes I ran the SEU option on all object type QRYDFN in my production library, and now have a searchable source file for all 955 queries in my environment. This will undoubtedly save me a ton of time when I’m looking for something in our queries.

    ReplyDelete
  6. I know you can find things using the searchable source from RTVQMQRY, because it will give you the columns and tables used, but queries should be translated one by one,with testing,before actually using the source files for a revised query. The SQL translation turns summaries into detail queries and messes up the left and exception joins. Note that there is also a RTVQMFORM.

    However, I did use RTVQMQRY to get started in SQL, and it does help. I would start with WRKQRY and then retrieve the SQL. It is training wheels; you don't keep working that way; but it is good to start with what you know.

    ReplyDelete
    Replies
    1. This is good information. Never knew.

      Delete
  7. I had no idea you could do this! Very cool!

    ReplyDelete
  8. This is great, thanks for sharing.

    ReplyDelete
  9. Good article. I wish I knew more about embedding SQL in RPG. I haven't had time to research it on the web.

    ReplyDelete
  10. Very interesting material - appreciate your efforts!

    ReplyDelete
  11. Maybe someone can answer this question for me. I have a dependent query that accepts variables from the CL. When using STRQMQRY with this query, it retrieves the data correctly...When I extract the SQL source, (and modify the cross joins into an inner join and change the ON statements) it errors on the WHERE clause WHERE T01.field1 = &parm1 with the error "Position 26 Token & was not valid.".
    Will this process not work with dependent queries?

    ReplyDelete
    Replies
    1. Ron, how are you re-running the SQL stmt after you've modified it? Some 3rd party tools will allow you to put the Query400 substitution variables but some won't Look for the SETVAR parameter to compatibility. You could also Google "SQL Query SETVAR DB2" to look for solutions.

      Delete
  12. Hi This is nice but there is a catch it doesn't give you the output file name, i.e the output file name in the work query. How to get that, I need that too. Please help.

    ReplyDelete
    Replies
    1. You are correct it does not.
      WRKQRY uses the internal Select statement to gather the result set.
      then it is whatever you put into the WRKQRY that decides the type of output you desire.
      You would need to look at the Query's definition to find that information.

      Delete
  13. This been a blessing to find. Converting AS400 queries to living data via PHP and ODBC has been challenging in some cases.

    ReplyDelete
  14. I've noticed that it that feature doesn't convert summary and break information when created the SQL statement.Am I wrong?

    ReplyDelete
    Replies
    1. You are not wrong. The summary and break information are part of the tool, not the SQL contained within.
      I have written several other posts how you can provide summaries, etc., with just SQL.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.