iSeries Navigators Database component includes a very useful SQL scripting tool that enables you to interact with the DB2 database on your i5. Figure 25.25 demonstrates how to launch the scripting tool. Open the iSeries Navigator and right click Database beneath the server you wish to work with. This opens a list of actions to perform against that server. Click Run SQL Scripts… to launch the GUI SQL scripting tool (Figure 25.26).
Figure 25.25: Launching the Run SQL Scripts tool.
Figure 25.26: Run SQL Scripts.
The primary feature of the scripting tool is a large text box. By default, this text box contains the comment /* Enter one or more SQL statements separated by semicolons */. The comment does not have to be deleted, but I find that it often confuses students and interferes with writing code. So, for the sake of avoiding future confusion, delete that comment. The text box should now be completely empty and ready for you to enter your first SQL statement. But before running an SQL statement, take a minute to configure the environment.
Click the Options pull-down menu and select those options shown in Figure 25.27. Five options are selected:
Stop on Error. Controls the behavior of the scripting tool when multiple SQL statements are being processed in order. If any SQL statement has an error, the processing of the remaining SQL statements is aborted. If this option is not selected, each SQL statement is evaluated independently, and all valid statements are processed.
Smart Statement Selection. When this option is activated, each time an SQL statement is executed, the entire statement is executed, rather than just the selected portion of the statement.
Display Results in Separate Window. Each time an SQL statement runs and displays a result table, that result table is displayed as a separate window on the desktop. If this option is not activated, the results are displayed in a separate tab at the bottom of this window.
Include Debug Messages in Job Log. Selecting this option causes any diagnostic errors that occur to display in the job log for this session. The job log can be reviewed by selecting Job Log& within the View pull-down menu.
Run Statement on Double-Click. This option allows the execution of SQL statements simply by double clicking on them. Semicolons must be used to mark the end of each SQL statement.
Figure 25.27: Options pull-down menu.
After the options have been set, you are ready to execute SQL statements. For a list of example statements that show the basic syntax of a large number of SQL statements, open the Examples drop-down box. To insert one of the example statements into the text box, click to select it, and then click the Insert button. The selected example inserts into the text box at the current cursor location.
If you already know which SQL statement you wish to run, simply type it in. For example, type the following statement and run it by double clicking:
SELECT * FROM KPFSQL/CUST;
The following error should be displayed at the bottom of the window:
> select * from KPFSQL/CUSTMAST; [SQL5016] Qualified object name CUSTMAST not valid. Cause . . . . . : One of the following has occurred: The syn- tax used for the qualified object name is not valid for the naming option specified. With system naming, the qualified form of an object name is collection- name/object-name. With SQL naming the qualified form of an object name is authorization-name.object-name. The syntax used for the qualified object name is not allowed User-defined types cannot be qualified with the library in the system naming convention on parameters and SQL variables of an SQL procedure or function. Recovery . . . : Do one of the following and try the request again: If you want to use the SQL naming convention, verify the SQL naming option in the appropriate SQL command and qualify the object names in the form authorization-id.object- name. If you want to use the system naming convention, specify the system naming option in the appropriate SQL command and qualify the object names in the form collec- tion-name/object-name. With the system naming conven- tion, ensure the user-defined types specified for parameters and variables in an SQL routine can be found in the current path. Processing ended because the highlighted statement did not complete successfully
This error occurred because "/" is not a valid character for use in qualifying a file name. To identify which library a file resides in, use the period (.) instead. This SQL standard is followed on most platforms; the i5 may be the only system that uses the "/" (back-slash) character. Change the SQL statement as below and double-click it again:
SELECT * FROM KPFSQL.CUSTMAST;
Figure 25.28 shows the results displayed in a separate window. If the results are larger than the space provided in the window, the window can be resized; scroll bars are provided to display different portions of the result table.
Figure 25.28: Result window.
Notice that the error message from the first failed SQL statement is still listed at the bottom of the SQL Script window. To erase the old messages, click Clear Run History in the Edit pull-down menu. (The Edit pull-down menu also contains the option Clear Results. This option is not used in this chapter. If we had not selected the option to display results in a separate window, they would be displayed at the bottom of the text box, similarly to the error messages. Select Clear Results to erase previous result sets from the bottom of the text box.)
Now that the previous errors have been cleaned up, let's look at sorting the data. To sort the data, we'll add an ORDER BY clause to the SQL statement. Enter the statement as shown below and double-click it:
SELECT * FROM KPFSQL.CUSTMAST ORDER BY CUSTST;
The customers appear in alphabetical order, sorted by their states, in a result window as shown in Figure 25.29.
Figure 25.29: Results sorted by state.
Some SQL statements may require the SQL engine to create temporary access paths to perform certain sorting and selecting logic. In some cases, performance is improved if a permanent access path is built. To determine if the SQL engine is building a temporary access path for this SQL statement, click Job Log… in the View pull-down menu. The job log shown in Figure 25.30 is displayed.
Figure 25.30: Viewing the job log.
Any SQL statements that generates the message "Access path built for file…". is a candidate for this performance improvement task. Before building the index, consider how often the SQL statement is executed and how much overhead the access path will add to the database. Sometimes it is better to let the system generate a temporary index for infrequently run SQL statements rather than create an index that will require constant maintenance by the database engine.
The SQL scripting tool supports the ability to run multiple SQL statements consecutively. Type the following SQL statements and click the Run All icon:
SELECT CUSTNAM, CUSTST FROM KPFSQL.CUSTMAST WHERE CUSTST <> 'OH'; SELECT CUSTNAM, CUSTCTY from KPFSQL.CUSTMAST WHERE CUSTST = 'OH';
Clicking the Run All icon causes the execution of all SQL statements in the text box. Figure 25.31 shows the results of each SELECT displayed in separate windows.
Figure 25.31: Results from Run All.
By default, the windows are displayed directly on top of one another. To view them simultaneously, they must be moved and possibly resized.
As you continue to create and execute SQL statements, at some point you may wish to run many of the SQL statements in the script, but not all. IBM provides the ability to begin execution at a specified point, ignoring all SQL statements above that point in the SQL script. For example, write the following code, then click the cursor on the second SQL statement; then click the Run from Selected icon to execute all SQL statements from that point on:
SELECT * FROM KPFSQL.CUSTMAST; SELECT CUSTNAM FROM KPFSQL.CUSTMAST WHERE CUSTST = 'OH'; SELECT CUSTNAM FROM KPFSQL.CUSTMAST WHERE CUSTST <> 'OH' AND CUSTCTY = 'MONROE';
Clicking the Run from Selected icon causes the execution of all SQL statements in the text box starting with the one on which the cursor is located. Figure 25.32 shows the results of each SELECT displayed in separate windows.
Figure 25.32: Results from Run from Selected.
To force only SQL statement to execute, either click the Run Selcted icon after placing the cursor on the statement to be executed or double-click the statement to execute (if the Run on double-click option is activated).
After coding a number of SQL statements, or perhaps coding some particularly complex SQL statements, you may want to save them for use at a later time. Unlike the STRSQL tool in the native environment, this STRSQL tool does not automatically remember your previous SQL statements. You must save them manually. To save the three SQL statements written above, click the Save As option within the File pull-down menu. The Save As dialog box shown in Figure 25.33 is displayed.
Figure 25.33: Save dialog box.
Select the desired folder and file name. The file suffix defaults to .sql and should not be changed unless absolutely necessary. If the SQL script files are saved into a network folder or a folder on the i5 Integrated File System (IFS), it can be shared with other programmers or users on the i5. If it is stored locally on your PC, only a user at your workstation can use it.
The history log maintained by the STRSQL tool is easy to use because it is automatic. It's harder to search, however, unless various important SQL statements are saved into well-named files. By well-named, I mean that the names need to intuitively reflect the SQL statements stored within them. SQL files named TEST1.SQL, TEST2.SQL, and TEST3.SQL, don't identify their contents. Had they been named PartCost.SQL, TotalSales.SQL, and ProductionSchedule.SQL, the nature of their contents would be clearer, and users would spend less time hunting for the right SQL statement.
Once the script file has been saved, it can be recalled by selecting the Open… option within the File pull-down menu. The Open dialog box shown in Figure 25.34 is displayed.
Figure 25.34: Open dialog box.
Select the desired folder and file, then click Open. All SQL statements saved within the selected SQL script file are loaded into the text box. From there, they can be executed as a group or individually as needed.
The Run SQL Scripts tool is a great example of the new client-based tools provided by IBM to help you manage your i5 and iSeries servers. Essentially, the tool provides the same capabilities as the green screen STRSQL command reviewed earlier in this chapter, plus even more. For more information on the Run SQL scripts tool, see the IBM manual "Advanced Functions and Administration on DB2 Database for iSeries".