SQL in iSeries Navigator I - cdn.ttgtmedia.com

1 SQL in iSeries NavigatorIn V4R4, IBM added an SQL scripting tool to the standard features included within iSeries Navigator and has continued enhancing it insubsequent releases. Because standard features of iSeries Navigator are avail-able to customers at no charge, this new feature marks a new era in SQLdevelopment on the iSeries . For the first time, users can get a mainstream toolto perform interactive SQLs without having to buy additional products. TheSQL engine has long been included within the DB2 database that ships withevery iSeries , but, the ability to send instructions to it required the installationof additional products. The GUI scripting tool shown in Figure can befound within the database component of iSeries Navigator . In addition toproviding a free alternative to the STRSQL tool outlined in Chapter 1, this toolprovides a much more comfortable environment for developers new to theiSeries. Avoiding the native 5250 interface and having the ability to performcommonplace actions such as cut-and-paste goes a long way towards puttingnew developers at the Database Component of iSeriesNavigatorThis component can be added, if it s not already installed.

2 To install theDatabase component and the SQL scripting tool, open iSeries Navigator and click on Selective Setup within the Filepull-down menu. This launchesthe Selective setup wizard (Figure ) that controls which features of iSeries Navigator are installed on each PC. Before the setup tool itself islaunched, this small prompt window is displayed. Select which iSeries serverto use. If more than one is present in your network, all servers will be listedin the drop-down box. Generally, I suggest you use the server that is at thehighest release level of OS/400. Some compatibility issues may exist ifCHAPTER2: SQL in iSeries Navigator24 Figure : Launching the Run SQL Scripts iSeries servers on your network are running at different levels ofOS/400. Some features of iSeries Navigator may work with one server and not others. To eliminate these compatibility problems, however, IBMoften provides PTFs for older versions of OS/400. After selecting theappropriate server, you will be prompted to log into the server.

3 The installprogram then runs: It determines which components are already installed onyour PC and which other components are available for installation ( ). If the Database component of iSeries Navigator (also referred to asAS/400 Operations Navigator ), is not selected, click the check box to select itand click Next. The wizard then completes the installation process. Beforeusing the new component, be sure to apply any available patches. IBMprovides fixes to the iSeries Navigator application on a regular basis and freeof charge. If you want to download patches to iSeries Navigator , see the website that the database is installed and the latest patches have been applied, theSQL scripting tool can be put to the Database Component of iSeries Navigator25 Figure : Selective Setup Started with the SQL Scripting ToolOnce the iSeries Navigator sDatabase component and itsscripting tool have been installed onyour PC, you can easily use SQL tointeract with the DB2 database onyour iSeries .

4 Figure demonstrateshow to launch the scripting the iSeries Navigator and rightclick on Database beneath the serveryou wish to work with. This opens alist of actions to perform against thatserver. Click on Run SQL launch the GUI SQL scripting tool(Figure ).CHAPTER2: SQL in iSeries Navigator26 Figure : Launching the Run SQL Scripts : Launching the Run SQL primary feature of the scripting tool is a large text box. By default, this textbox contains the comment /* Enter one or more SQL statements separated bysemicolons */. The comment does not have to be deleted, but I find that it oftenconfuses students and interferes with writing code. So, for the sake of avoidingfuture confusion, delete that comment. The text box should now be completelyempty and ready for you to enter your first SQL statement. But before runningan SQL statement, take a minute to configure the the SQL Script EnvironmentClick on the Optionspull-down menu and select those options shown in Figure Five options are on Error Controls the behavior of the scripting tool when multipleSQL statements are being processed in order.

5 If any SQL statement has anerror, the processing of the remaining SQL statements is aborted. If thisoption is not selected, each SQL statement is evaluated independently,and all valid statements are Statement Selection When this option is activated, each time anSQL statement is executed, the entire statement is executed, rather thanjust the selected portion of the Results in Separate Window Each time an SQL statementruns and displays a result table, that result table is displayed as a separatewindow on the desktop. If this option is not activated, the results are dis-played in a separate tab at the bottom of this window. Debug Messages in Job Log Selecting this option causes anydiagnostic errors that occur to display in the job log for this session. TheGetting Started with the SQL Scripting Tool27 Figure : Options pull-down log can be reviewed by selecting Job within the Viewpull-down Statement on Double-Click This option allows the execution ofSQL statements simply by double clicking on them.

6 Semicolons must beused to mark the end of each SQL the options have been set, you are ready to execute SQL statements. For alist of example statements that show the basic syntax of a large number of SQLstatements, open the Examples drop-down box. To insert one of the examplestatements into the text box, click to select it, and then click the Insert selected example inserts into the textbox at the current cursor 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 > select * from KPFSQL/CUSTMAST;[SQL5016] Qualified object name CUSTMAST not valid. Cause .. : One of the following has occurred: The syntaxused for the qualified object name is not valid for the namingoption specified. With system naming, the qualified form of anobject name is collection-name/object-name. With SQL naming thequalified form of an object name is The syntax used for the qualified object name isnot allowed.

7 User-defined types cannot be qualified with thelibrary in the system naming convention on parameters and SQLvariables of an SQL procedure or function. Recovery .. : Doone of the following and try the request again: If you want touse the SQL naming convention, verify the SQL naming option inthe appropriate SQL command and qualify the object names in theform If you want to use the systemnaming convention, specify the system naming option in the appropriate SQL command and qualify the object names in the formcollection-name/object-name. With the system naming convention,ensure the user-defined types specified for parameters and variables in an SQL routine can be found in the current ended because the highlighted statement did not complete successfullyCHAPTER2: SQL in iSeries Navigator28 This error occurred because / is not a valid character for use in qualifying afile name. To identify which library a file resides in, use the period (.) SQL standard is followed on most platforms; the iSeries may be the onlysystem that uses the / (back-slash) character.

8 Change the SQL statement asbelow and double click on it again:SELECT * FROM ;Figure shows the results displayed in a separate window. If the results arelarger than the space provided in the window, the window can be resized; scrollbars are provided to display different portions of the result that the error message from the first failed SQL statement is still listed atthe bottom of the SQL Script window. To erase the old messages, click on ClearGetting Started with the SQL Scripting Tool29 Figure : Result Historyin the Edit pull-down menu. (The Editpull-down menu also con-tains the option Clear Results. This option is not used in this book. If we hadnot selected the option to display results in a separate window, they would bedisplayed at the bottom of the text box, similarly to the error messages. SelectClear Resultsto 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 sort the data, we ll add an ORDER BY clause to the SQL statement.

9 Enterthe statement as shown below and double click on it:SELECT * FROM ORDER BY CUSTST;The customers are displayed in alphabetical order by their states in a resultwindow as shown in Figure : SQL in iSeries Navigator30 Figure : Results sorted by SQL statements may require the SQL engine to create temporary accesspaths to perform certain sorting and selecting logic. In some cases, performanceis improved if a permanent access path is built. To determine if the SQL engineis building a temporary access path for this SQL statement click on Job the Viewpull-down menu. The job log shown in Figure is SQL statements that generates the message Access path built for isa candidate for this performance improvement task. Before building the index,consider how often the SQL statement is executed and how much overhead theaccess path will add to the database. Sometimes it is better to let the systemgenerate a temporary index for infrequently run SQL statements rather than cre-ate an index that will require constant maintenance by the database engine.

10 Thistopic is reviewed in more detail in Chapter Multiple SQL ScriptsThe SQL scripting tool supports the ability to run multiple SQL statements con-secutively. Type the following SQL statements and click on the Run All icon:Getting Started with the SQL Scripting Tool31 Figure : Viewing the job CUSTNAM, CUSTST FROM WHERE CUSTST <> OH ;SELECT CUSTNAM, CUSTCTY from WHERE CUSTST = OH ;Clicking on the Run Allicon causes the execution of all SQL statements inthe text box. Figure shows the results of each SELECT displayed in default, the windows are displayed directly on top of one another. To viewthem simultaneously, they must be moved and possibly you continue to create and execute SQL statements, at some point you maywish to run many of the SQL statements in the script, but not all. IBM providesthe ability to begin execution at a specified point, ignoring all SQL statementsabove that point in the SQL script. For example, write the following code, thenclick the cursor on the second SQL statement; then click the Run from Selectedicon to execute all SQL statements from that point on:SELECT * FROM ;SELECT CUSTNAM FROM WHERE CUSTST = OH ;SELECT CUSTNAM FROM WHERE CUSTST <> OH ANDCUSTCTY = MONROE ; Clicking on the Run from Selectedicon causes the execution of all SQLstatements in the text box starting with the one on which the cursor is shows the results of each SELECT displayed in separate : SQL in iSeries Navigator32 Figure : Results from Run force only one SQL statement to execute, either click the Run placing the cursor on the statement to be executed or double-click on the statement to execute (if the Run on double-click option isactivated).