Guru: Fooling Around With SQL And RPG

April 15, 2024 Gregory Simmons

Editor’s Note: This was originally scheduled to be published on April 1. No joke. And for a lot of complex reasons, that could not happen. But, it’s still fun, so enjoy.

I started out one morning, purely interested in having a bit of fun. Honest. In RSS within ACS, I often like to run this SQL:

 select *
 from json_table(
      QSYS2.HTTP_GET('https://icanhazdadjoke.com/',
              '{"header": "Accept,application/json", "sslTolerate":"true"}'),
              'lax $' columns ("joke" varchar(200) CCSID 1208)
      )

Okay, that was fun. This is harmless, good fun. But then I thought, what if I put this into a simple RPG program? Then I could call this anytime I wanted to and email all of my co-workers a dad joke! Now, because I don’t want to be accused of spreading mayhem to every RPG shop, I’m going to just write an entry to my joblog with the new enhancement to RPG in V7R4 TR6 or V7R5; the operation code snd-msg. Here is what I came up with:

**Free
Ctl-Opt Main(DAD_Joke_Generator);
Ctl-Opt Debug Option(*SrcStmt:*NoDebugIO);
Ctl-Opt ActGrp(*Caller);

Dcl-Proc DAD_Joke_Generator;

Dcl-Pi DAD_Joke_Generator;
End-Pi;

Dcl-s dad_joke VarChar(200);

Exec SQL
  Select *
  Into :dad_joke
  From json_table(QSYS2.HTTP_GET('https://icanhazdadjoke.com/',
 '{"header": "Accept,application/json",                          "sslTolerate":"true"}'),
                  'lax $'
                  columns ("joke" varchar(200) CCSID 1208)
                 );

snd-msg dad_joke;

// Or if you're feeling devious, email all your coworkers a dad joke...

Return;

On-Exit;
End-Proc DAD_Joke_Generator;
 

As you can see, I’ve written a simple linear-main RPG program to run my previous SQL statement to retrieve a dad joke from https://icanhazdadjoke.com/ . Then I use the new snd-msg operation code to put an entry into my joblog. Running it yields this:

Now, again, I am not condoning or suggesting that you do this, which is why I left out the emailing logic from my demo program. Perhaps you wanted to run this program and let’s say, just email yourself a joke every Monday morning, we can use the IBM i job scheduler. Adding an entry is simple with the ADDJOBSCDE command:

ADDJOBSCDE JOB(APRILFOOLS)          
                           CMD(CALL PGM(APRILFOOLS))
                           FRQ(*WEEKLY)            
                           SCDDATE(*NONE)          
                           SCDDAY(*MON)            
                           SCDTIME('06:00:00')     

In V7R2M0, the DB2 for i team gave us the SCHEDULED_JOB_INFO view, which gives us a nice way, for instance, have a look at all of the jobs that are scheduled to run on Monday mornings at 6:00:00 am!

SELECT SCDJOBNAME,STATUS,SBMTIMSTMP,SCHEDULED_DATE_VALUE,
   		  SCHEDULED_TIME,SCHEDULED_DAYS, COMMAND_STRING
FROM QSYS2.SCHEDULED_JOB_INFO
Where STATUS = 'SCHEDULED' and
      	  SCHEDULED_DAYS = '*MON' and
      	  SCHEDULED_TIME = '06.00.00'
ORDER BY SCDJOBNAME

Other commands are available for working with your job scheduled entries:

And if you did go the extra devious step and setup the DADJOKES program to email your co-workers, you’ll probably quickly want to remove the scheduled job entry with the RMVJOBSCDE command. For example:

RMVJOBSCDE JOB(APRILFOOLS)

I hope you find a chuckle in the SQL statement to retrieve a dad joke – for yourself. And I hope you find the snd-msg operation code useful as well as working with job scheduled entries.

Until next time, happy coding.

Gregory Simmons is a software engineer with PC Richard & Son. He started on the IBM i platform in 1994, graduated with a degree in Computer Information Systems in 1997 and has been working on the OS/400 and IBM i platform ever since. He has been a registered instructor with the IBM Academic Initiative since 2007, and holds a COMMON Application Developer certification. When he’s not trying to figure out how to speed up legacy programs, he enjoys speaking at technical conferences, running, backpacking, hunting, and fishing.