Modernize AS400 iSeries Queries – Convert to IBM i SQL

This week I be mainly…. working on a Casino System upgrade, dragging decades old code into the twenty first century. Old RPG3, RPG400, CLP programs and techniques using outdated OPNQRYF and QUERIES are fighting back tooth and nail. I love this schnizzle. 🙂

One of the techniques for upgrading/converting older QUERY400 reports to modern SQL format is IBM i’s very cool RTVQMQRY command.

I had completely forgotten about this technique (possibly because my 32k system 38 brain has a memory leak) but it was so simple and quick I had to write it down before I had a mental buffer overload.

IBM i QUERY

A while ago I made a QUERY400 for a Software Tester who wanted to see a hotel check-in date from an Agilysys file (GIP) which stores dates in the 5 digit HYD (hundred year format). But, of course, our tester wanted to check the dates in human friendly USA standard date format (M/D/YY)

So I made a quick and dirty QUERY400 to basically list the dates, create a new mapped date showing it in *USA format:

and then run the query and see a nice simple data screen like this:

From QUERY to SQL

Now if we want to modernize our approach and do this with a SQL statement it’s as easy as pie!

We simply use the RTVQMQRY command to suck that QUERY400 definition and spit out a source file with a lovely SQL statement in it that looks like this:

RTVQMQRY QMQRY(LITTENN/GIPCHECK) SRCFILE(LITTENN/QSQLSRC) ALWQRYDFN(*YES)

and this generates this lovely piece of source code:

which as  you can see, has this line of SQL in it:

SELECT ALL CIDTGI, CITMGI, DATE(CIDTGI+DAYS('12/31/1899')) AS
CHECKINDAT, CIAGGI, FNAMGI, KYDTGI 
FROM LMDTA15/GIP T01 
WHERE CIDTGI > 0

and we can run that in SQL to see the exact same result.

So, next time a grey haired AS400 developer tells you that it’s a QUERY and it can’t be changed… you can raise an eyebrow and mutter about IBM i RTVQMQRY.  🙂