You don't need to write a program to run common SQL statements.

Do you have common SQL statements that you run on a regular basis but haven't found the time to put into a program? Do you try to remember the SQL statement that you ran last time, or do you keep notes to manually recall those statements interactively every time you need them?

When you do run the statement, do you have to think about it more than you would prefer because you have to do the syntax-checking and recheck yourself three or four times to make sure you don't execute a statement that will do more than you expect it to?

Maybe you're doing this because the statements change slightly each time you run them, or maybe they're cleanup processes that you run on a regular basis and you can't justify the time dedicated to creating and debugging a full-fledged RPG program with embedded SQL.

No, I'm sure you don't do any of this. I don't either. That's because I use the RUNSQLSTM command to execute SQL commands directly from a source member. When you put the SQL statements into a source member, you can easily modify the statements and not have to recompile anything. You simply modify the text and run the command.

You could initially test your SQL statements interactively, as discussed in my previous tipTechTip on interactive SQL. Once you have tested and tweaked the SQL statement to get the desired results, you can put the statement into a source member to be reused in the future.

Let's say you have an application that sends new database records to a location outside of your IBM i. The incremental updates to the outside application are indicated by the date that is recorded on the records that are sent. So, when the next incremental update is executed, it will look for records that do not have the date recorded. When the next set of records is sent, the date will be recorded and the process will repeat throughout the day.

Now suppose the destination site tends to halt during processing, so only part of the records that you sent are received. When this problem is identified, you would most likely want to resend all of the data that was missed. This can be accomplished by clearing the date information and resending the data. This is a perfect scenario for a quick SQL statement to clear the date off of the existing records to force a data refresh!

Suppose we had a simple data file with the following DDS (Figure 1):

Figure 1: You need to transfer this simple data file.

The users of the data are requesting that you resend all of the data for the month of June for every Sunday when the moon cycle is full.

You can call STRSQL from the command line and run the following SQL statement interactively to get the job done:

UPDATE DATAFILE SET DATE = 00000000 WHERE DATE > 20090601

AND DAY = 'SUN' AND MOONCYCLE = 'FULL'

Now, you could write this down or email it to yourself for future use. You could also write a program that would prompt you for the parameters, but that would take away from development time on other fun projects. Instead, you can put the SQL statement into a source file member.

-----------------------------------------------------------------------

-- COMMENTS ARE INDICATED IN THE TEXT FILE WITH DOUBLE DASHES.

-- USE THE RUNSQLSTM COMMAND TO EXECUTE THESE COMMANDS.

-- REMEMBER, UPDATES MUST BE FOLLOWED WITH A SEMICOLON!

-----------------------------------------------------------------------

UPDATE DATAFILE SET DATE = 00000000 WHERE DATE > 20090601

AND DAY = 'SUN' AND MOONCYCLE = 'FULL';

-----------------------------------------------------------------------

Notes:

• You can put multiple SQL statements in the source member, and they will be executed sequentially.
• You need to end the statement with a semicolon.

Now you can run that statement using the RUNSQLSTM command.

RUNSQLSTM SRCFILE(MYLIB/MYSRC) SRCMBR(SQLMCPRESS) COMMIT(*NONE)

You know that the next time you need to resend the data, the users may request that you filter on any days that the barometric pressure is greater than 1013.25 millibars (mb). So you would need to modify the query.

No problem, you just add the new BAROMETRIC criteria to your SQL statement and save the file. You're ready to run your new query.

-----------------------------------------------------------------------

-- COMMENTS ARE INDICATED IN THE TEXT FILE WITH DOUBLE DASHES.

-- USE THE RUNSQLSTM COMMAND TO EXECUTE THESE COMMANDS.

-- REMEMBER, UPDATES MUST BE FOLLOWED WITH A SEMICOLON!

-----------------------------------------------------------------------

UPDATE DATAFILE SET DATE = 00000000 WHERE DATE > 20090605

AND DAY = 'SUN' AND MOONCYCLE = 'FULL'

AND BAROMETRIC > 1013.25;

-----------------------------------------------------------------------

Now you can see the benefits of doing this. You have your query saved from the last time you ran it, and you know that it provided the desired results, so you run a lower risk of causing problems. And the method used to execute the query is highly versatile because you do not even have to compile it. You'll get quick results without having to remember anything, and if you do want to put some reminder notes in the file, you can just add them into the comments.

RUNSQLSTM Spool File

Executing the RUNSQLSTM command will generate a spool file that provides the results of your query. It will display any errors that were encountered as well as the number of records that were affected by your query.

Commitment Control

If you are using journaling on your files, you can take advantage of the commitment control option that is available on the RUNSQLSTM command. In this example, I was not using journaling, so I specified COMMIT(*NONE). If you are not using journaling and you try to use commitment control, you will receive the following message in the spool file that is generated by RUNSQLSTM:

More Information

For more exciting information on the RUNSQLSTM command, you can check out the IBM Web Site.

Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.

Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.

Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.

Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.

---

MC Press books written by Thomas Snyder available now on the MC Press Bookstore.

		Advanced, Integrated RPG See how to take advantage of the latest technologies from within existing RPG applications. List Price $79.95 Now On Sale
		Extract, Transform, and Load with SQL Server Integration Services Learn how to implement Microsoft’s SQL Server Integration Services for business applications. List Price $79.95 Now On Sale

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL