Parameterized RUNSQLSTM

June 21, 2002 Timothy Prickett Morgan

Hey, Ted:

Can I pass parameters to an SQL statement in a source member before executing the Run SQL Statement (RUNSQLSTM) command? I’m sure there must be a simple way, but I’ve never seen any examples of this.

For example, today I might like to have the SQL statement select company 005. Tomorrow I might want it to select company 007.

— Darrell

What you want is the ability to put placeholders in the SQL source and specify replacement arguments on the RUNSQLSTM command, as you can do with the Start Query Management Query (STRQMQRY) command.

It may be worth the effort to write an RPG, COBOL, or C program that will build or update a source member for you.

Another option is to use Qshell’s stream editor, sed. Most source-code editors I’ve used are for interactive editing, but batch editors do exist, although they’re rare. Sed is such a batch editor.

As an exercise to prove the concept, I created a source member called UPDTCUSPAT (PAT to indicate pattern) with an SQL command in it. I put CCC as a placeholder for the company number:

update customer         
   set creditlim = 0 
   where company = CCC  

I copied source member UPDTCUSPAT to source member UPDTCUS in order to create a source member to hold the SQL statement that will be executed.

I developed the necessary sed command in an interactive Qshell session:

sed "s%CCC%002%g" 
   /qsys.lib/tholts.lib/sqlsrc.file/updtcuspat.mbr 
   >/qsys.lib/tholts.lib/sqlsrc.file/updtcus.mbr  

This command tells Qshell to read updtcuspat, replacing every occurrence of CCC with 002, and to put the output into updtcus. That is, the stream editor doesn’t change the updtcuspat source member, but makes an updated copy of it.

Then I wrote a CL program that takes a company number as a parameter, builds the sed command, executes the sed command to build UPDTCUS, and finally runs RUNSQLSTM:

pgm &company                                               
                                                               
dcl &lib        *char  10 value('tholts')                  
dcl &file       *char  10 value('sqlsrc')                  
dcl &patternmbr *char  10 value('updtcuspat')              
dcl &sqlcmdmbr  *char  10 value('updtcus')                 
dcl &company    *char   3                                  
dcl &shellcmd   *char 256                                  
                                                               
chgvar var(&shellcmd) +                                    
  value('sed "s%CCC%' *cat &company *cat '%g"' *bcat +
           '/qsys.lib/' *cat +                           
           &lib *tcat '.lib/' *cat +                     
           &file *tcat '.file/' *cat +                   
           &patternmbr *tcat '.mbr' *bcat +              
           '>/qsys.lib/' *cat +                          
           &lib *tcat '.lib/' *cat +                     
           &file *tcat '.file/' *cat +                   
           &sqlcmdmbr *tcat '.mbr')                      
QSH       CMD(&SHELLCMD)                           
RUNSQLSTM  SRCFILE(&LIB/&FILE) SRCMBR(&SQLCMDMBR) +
                          COMMIT(*NONE)                       

It works, but I haven’t tried it in a production environment.

— Ted

Sponsored By TRAMENCO

Introducing a New Source for Training and Mentoring. Follow this link to a vital new source for how-to technical information: www.tramenco.com. Unlike companies that offer training as an afterthought, The Training and Mentoring Company (Tramenco) is dedicated to just one thing: Advancing your career by giving you the skills you need to solve real-world business problems. You get the best information from the world’s leading experts–Howard Arner, Kelly Conklin, Don Denoncourt, Susan Gantner, Skip Marchesani, Glen Marchesani, Shannon O’Donnell, Craig Pelke, and Richard Shaler. Choose from a menu of training options to fit your needs: onsite seminars, public seminars, mentoring, consulting, books, CBTs, and Web-based training. And make plans to attend the 2002 iSeries Connection Conference, the multi-day, multi-track conference that was the only sold-out iSeries training event this year, co-sponsored by the Education Connection and Tramenco. For more information about Tramenco’s career enhancing opportunities, call (800) 421-8031 or go to www.tramenco.com.