Run SQL statements automatically with RUNSQLSTM

STRSQL is a powerful and useful interactive tool (in the right hands) but it can be cumbersome to retrieve previous SQL statements, modify them and then run them again all the while hoping there are no typos. If you find yourself frequently entering the same SQL commands why not save them to a text file and automate the process?

I like to save the SQL statements in a source file named qtxtsrc. Once saved the SQL can be executed from a CL containing the RUNSQLSTM command.

In this example two inventory file field definitions were reversed during an upgrade and the data had to be swapped (mapped correctly) during the process. This can be accomplished with one SQL statement. Note that there is no need for messy work fields that might otherwise be used when coding the same solution in RPG.

Step 1. Create the text type source member.

Contents of the source member sqlfswap (saved as a text file):
update datalib/invenpf set field1 = field2, field2 = field1

Step 2. Create CL member sqlfswapcl:

ADDLIBLE mylib
MONMSG msgid(cpf0000)
RUNSQLSTM srcfile(mylib/qtxtsrc) srcmbr(sqlfswap)
MONMSG msgid(cpf0000)
endpgm

Step 3. Run the CL and check your results.

Of course the CL can be set up on the job scheduler (wrkjobscde) if your application needs to be run automatically at specified intervals and multiple sql statements can be added into the same text source member.