January 30

0 comments

Grabbing a library description in RPG with SQL

By NickLitten

January 30, 2020

#SQLRPGLE, #library

I was updating a little RPG utility this morning to add some more detail to a spreadsheet it generated. Adding the Library Description will help the non-propeller heads who read this stuff. This is super easy to do with a single line of SQL added to your RPG program.

What is this spreadsheet you are waffling about? PS: Skip this paragraph if you don’t care about why I decided to add the description and just want to see how 🙂 This utility runs nightly and compares various libraries across the DEV, TST, QA and PROD environments looking for differences and generating an email with all the library names and a hash value for each library. If the hash values don’t match then we have a mismatched library on one of our machines. Just looking at a list of library names is fine for us propeller heads because we KNOW what the library names are and what application they are attached to. I was in a meeting where an executive asked “interesting spreadsheet, but how do we know what these library names are?” and the answer (with a straight face) was “ahhh, we know because of technical tribal knowledge”. I winced. Technical peeps often hide behind their desks, talking technobabble while forgetting that normal folks aren’t necessarily aware of the tribal language. But I digress…

Me. waffling. I blame the double espresso.

SQLRPGLE Code Snippet – get Library Description

In this example, the code is reading a file with two fields HSHLIB (the library name) and populating the HSHLIBDESC (the 50 character library description).

read filename;

dow not %eof(filename);

  // now get the library description from the system catalog
  exec sql
    select SCHEMA_TEXT
      into :hshlibdesc
      from SYSSCHEMAS
      where SCHEMA_NAME = :hshlib;

  // now we have the library description in field "hshlibdesc'
  // so we can update the file or do whatever we want with it
  update filename;

  read filename;

enddo;

Simple right?

In the old AS400 and iSERIES days we would have written a CL program just to do a DSPOBJD to an outfile and reading it, or maybe have called an IBM *API to get the info, but nowadays we can use the new IBM-i SQL facilities to grab the same info easily.

Simple code snippet – but hopefully will help any BM i novices out there

#cheers

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>

Snug CBD

 20% Discount

I have partnered with SNUG CBD givING you Organic CBD
20% discount code "NL20"