Run SQL Scripts: Use Temporary JDBC Settings

January 20, 2010 Skip Marchesani

I consider myself to be a power user of Run SQL Scripts and use them for a variety of purposes, including ad hoc queries to do data analysis. Run SQL Scripts can be configured for your personal preferences (SQL default schema or current library, schema or library list, SQL or system naming convention, and other parameters) by clicking on Connection in the toolbar and then JDBC properties. Once you select your personal preferences and click the Connect button on the bottom of the JDBC Setup panel, your preferences are saved and become the default each time you invoke Run SQL Scripts.

Often when I run ad hoc queries, these preferences need to be changed to suit the query, and one of my frustrations with Run SQL Scripts is the inability to temporarily change the JDBC settings. Changing the settings was not the issue. The problem was that the change was permanent and more times than I like to remember, I would forget to make the change back to my default settings and then wonder why the execution of my SQL script would fail the next time I used Run SQL Scripts.

IBM solved the problem by adding the capability to use temporary settings in V6R1 of Navigator and Run SQL Scripts. A temporary change can be made by clicking on Connections in the toolbar and then Use Temporary JDBC Settings in the dropdown menu. (Also note that in V6R1 you can view the current JDBC settings by clicking on Show JDBC Properties in the dropdown menu; however, I find the format of the resulting display difficult to read and I don’t use it.)

To see how Use Temporary JDBC Settings works, let’s run the ad hoc query against the table called CUSTTEST as shown in Figure 1.

Figure 1.

Note that the attempt to run the query failed, returning an SQL State code of 42704, which says that the table CUSTTEST could not be found in the schemas or libraries that were specified in my default JDBC settings for Run SQL Scripts.

To see the SQL default schema (current library) and the schema list (library list), click on Connection and then JDBC Settings in the resulting drop-down menu. The JDBC Setup panel is displayed as shown in Figure 2. We can see that the SQL default schema is SKIPSQL with no schema list specified, so the current library is SKIPSQL and default user library list for the job is in effect.

Figure 2.

Suddenly the light bulb goes on in my head because I remember that the table called CUSTTEST is in the schema or library called SQLWRK and not SKIPSQL. Rather than change the JDBC Setup permanently, let’s make a temporary change that will easily revert back to the default settings.

To make temporary changes to the default JDBC settings, click on Connection in the toolbar and then Use Temporary JDBC Settings in the resulting drop-down menu as shown in Figure 1. The temporary JDBC Setup panel will then be displayed as shown in Figure 3.

Figure 3.

Change the name of the SQL default schema to SQLWRK and click the Connect button on the panel. RUN SQL Scripts will reconnect to the system with SQLWRK as the temporary SQL default schema, and the Run SQL Scripts Panel will be redisplayed.

Now when the query is run against CUSTTEST a second time, it executes successfully as shown in Figure 4.

Figure 4.

When you exit Run SQL Scripts you will be prompted to either discard the temporary JDBC settings and revert to the saved default settings, or save the temporary settings as the new default for Run SQL Scripts as shown in Figure 5.

Figure 5.

The ability to set temporary JDBC settings in V6R1 of Navigator and Run SQL Scripts give Run SQL Scripts more flexibility and makes it easier to use. And, it eliminates an issue that I found very frustrating.

Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.