Run SQL Scripts- Automating Your Analysis

Of course, by now you should realize that you don’t have to re-create your SQL. You can use the Save function in Run SQL Scripts to save your work. What I suggest you do is put all of the SQL that you need to run regularly into a separate Run SQL Script file; then run each statement one at a time. When the results appear, right-click on the results and choose the Save Results… option to generate your report as an .xlsx, .csv, or whatever format meets your requirements. (You must first enable the Save option. See chapter 1 for instructions.)

SQL in a CL Program

While running established SQL statements regularly may work for you, some may need or want to run the SQL from a scheduled job. Unfortunately, both the regular and advanced job scheduler take only CL commands, not SQL. So how do you schedule SQL? One way you can accomplish it is to put your SQL in a CL program. You have two options for running SQL from a CL program: Run SQL (RUNSQL) and Run SQL Statement (RUNSQLSTM). To use RUNSQLSTM, put your SQL into a source physical file or a file in the IFS and then use RUNSQLSTM in your CL program to run the SQL defined in that source file. Here’s an example: https://www.ibm.com/support/pages/sample-cl-program-run-sql-scripts-using-runsqlstm

However, most people use RUNSQL, and that’s the command I’ll use for my example. The tricky part of adding SQL to a CL program is that you can’t use the SELECT statement, so you have to do a bit of sleight of hand to get the SELECT accomplished.

A few notes regarding this program:

  • First, this program is provided as is: no warranties, use at your own risk, and whatever words I need to use to make sure I’m not liable in case it fails or doesn’t work for you!
  • Now for some info that will actually be helpful…
    • All literals have to be qualified with two single quotes. Notice my WHERE clause; the *YES is surrounded with two sets of single quotes (those are not double quotes). Note: This issue will bite you if you have just copied your SQL from Run SQL Scripts because that interface uses one single quote to qualify literals.
    • I use a naming convention of *SQL (NAMING(*SQL)) so I can use the period (.) rather than a slash (/) to name objects—for example, QSYS2.USER_INFO.
    • I specify COMMIT(*NONE) so I don’t have to worry about uncommitted transactions being rolled back.
    • While this specific example works without it, in other CL programs running SQL, I’ve had to specifically set my CCSID to the language on my system to have the report correctly display the information.

For what I was working on when I wrote this program, I needed to create a spooled file (thus my use of the RUNQRY command). But you may want to send the contents of the file to a stream file and then download it to Excel. Or maybe you generate the spooled file as I did and then PDF it and email it off the system. My intent in providing this example is not to provide you with a total solution but to get you thinking about how you might make this work in your environment, using the utilities you already have. In addition, many other examples of adding SQL to a CL program exist on the Internet; you can learn from those. If you want a more-thorough explanation of RUNSQL, you can find it here: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/sqlp/rbafyrunsql.htm

Leave a comment

Your email address will not be published. Required fields are marked *