Preparing To Install IBM’s RUNSQL Command
May 30, 2012 Rob Berendt
|
I’ve often needed to run a SQL statement from within a CL program. I knew I could by calling a RPG program with SQL embedded in it, or by executing RUNSQLSTM, but I really didn’t want to go to that much trouble to run one simple SQL statement. IBM has given you a way to run an SQL command within a CL procedure if you’re running IBM i 6.1 or 7.1. It’s a new CL command called RUNSQL. For 7.1, you must order level 14 or higher of DB2 PTF Group SF99701. For 6.1, order level 25 or higher of DB2 PTF Group SF99601. Here’s an example of a simple RUNSQL command:
RUNSQL SQL('DELETE FROM myfile WHERE itsrecid=''D''')
RUNSQL has numerous other keywords to help you with things like commitment control and naming convention. See the RUNSQL documentation on IBM’s website. Perhaps RUNSQL sounds familiar to you. It was very familiar to us, because we already had a command called RUNSQL. We had downloaded it years ago as code from a magazine article. That RUNSQL had a parameter called REQUEST, not SQL. Our first concern was what would happen to all the code with our RUNSQL command buried in it if we loaded this PTF. It would cancel, of course. So we studied options. One option we quickly threw out was to never apply a PTF again. That was not going to happen in my shop. We considered renaming IBM’s command, but that could cause no end of problems. I didn’t like that idea because it would make it rough for some new hire or consultant who might know IBM’s new command. And any IBM software that tried to call QSYS/RUNSQL would cancel because that command would not exist. We considered the common practice of putting our RUNSQL in a new library and adding that library to the top of the system library list. However, that would be a problem because unqualified RUNSQL commands that were supposed to run IBM’s command would find our command instead. In the end we decided to bite the bullet and prepare for the arrival of the new command by renaming our command to EXESQL and changing every program that used it. Here are the steps we took in order to make the process successful. First we used Create Duplicate Object (CRTDUPOBJ) to create the new EXESQL command as a duplicate of our RUNSQL command. CRTDUPOBJ OBJ(RUNSQL) FROMLIB(ROUTINES) OBJTYPE(*CMD) + TOLIB(ROUTINES)NEWOBJ(EXESQL) Next, we changed RUNSQL so it would no longer run interactively from the command line. This would forcibly wean people over to the new command. We did this by running Change Command (CHGCMD) and removing the value *INTERACT from the ALLOW parameter. *IPGM and *BPGM still allowed it to run from where we had it in programs: interactive or otherwise. Third, we used utilities to scan our source code for RUNSQL, and changed every instance of RUNSQL to EXESQL. So far, so good. But I wanted confirmation that we hadn’t missed any usage of RUNSQL. That’s when we hit upon the idea of auditing the usage of the command. To begin auditing, we created a journal receiver and the journal QSYS/QAUDJRN. CRTJRNRCV JRNRCV(QUSRSYS/QAUDJR0001) CRTJRN JRN(QSYS/QAUDJRN) JRNRCV(QUSRSYS/QAUDJR0001) We changed system value QAUDCTL to *AUDLVL, *OBJAUD, and *NOQTEMP.
CHGSYSVAL SYSVAL(QAUDCTL) VALUE('*AUDLVL *OBJAUD *NOQTEMP')
Then I ran the Change Object Auditing (CHGOBJAUD) command to start auditing RUNSQL usage. CHGOBJAUD OBJ(ROUTINES/RUNSQL) OBJTYPE(*CMD) OBJAUD(*ALL) When it came time to see what the system had logged, I used the Copy Audit Journal Entries (CPYAUDJRNE) command to read the journaled data. CPYAUDJRNE ENTTYP(CD) JRNRCV(*CURCHAIN) FROMTIME(date time) I used SQL to query the database file that held the journal entries. SELECT * FROM QTEMP/QAUDITCD WHERE CDCMDS like '%RUNSQL%' The report, shown below, told us which jobs used the RUNSQL command, which programs had used the command, and even which SQL statements they used! Journaling command usage even found the places where RUNSQL was buried in an FTP script, as shown in the second line of the report.
Job User Job Program Program
name name number name library
ROBS1 ROB 850,303 DELETEME ROB
QTFTP52226 QTCP 687,062 QTMFSRVR QTCP
Command
string
RUNSQL REQUEST('SELECT * FROM ERPLXF/RCO')
ROUTINES/RUNSQL REQUEST('select * from erplxf/rco')
I hope that you find RUNSQL a welcome addition to your toolbox. But if you already have it in your toolbox, I hope that this article will help you prepare for IBM’s new addition. Rob Berendt is an IBM Certified System Administrator for IBM i. He is a Systems Analyst for Group Dekko, and during his 25-plus years there, he has been involved in several areas including programming, security, Domino, EDI, Mimix, and more. He has installed and upgraded numerous systems throughout the years from 1.2 all the way to 7.1. Currently he administers 10 lpars of i spread across three Power 6 servers and maintains the Domino servers served on i. He also provides other technical advice as needed.
|
