.st0{fill:#FFFFFF;}

Modernize AS400 iSeries Queries – Convert to IBM i SQL 

 May 23, 2018

By  NickLitten

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 LMS 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:

Modernize AS400 iSeries Queries - Convert to IBM i SQL 1
Modernize AS400 iSeries Queries - Convert to IBM i SQL 2
Modernize AS400 iSeries Queries - Convert to IBM i SQL 3
Modernize AS400 iSeries Queries - Convert to IBM i SQL 4
Modernize AS400 iSeries Queries - Convert to IBM i SQL 5

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

Modernize AS400 iSeries Queries - Convert to IBM i SQL 6

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:

Modernize AS400 iSeries Queries - Convert to IBM i SQL 7

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.  šŸ™‚

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

Iā€™m always looking for awesome input, feedback and critique!

>