SQL Assist: Powerful Interactive SQL

Published: September 10, 2008

by Skip Marchesani

The Run SQL Scripts function, a.k.a. the SQL Script Center or Script Center, is System i Navigator's counterpart to Interactive SQL on the green screen side of the System i. The SQL Script Center allows the user to execute all or a subset of a script that contains one or more SQL statements and/or batch CL commands. It is part of the Navigator Database function and is an extremely powerful and flexible tool with lots of function that can have a very positive impact on application developer productivity.

Interactive SQL has a very good multilevel SQL prompter that supports a subset of the SQL statements available in DB2. While the SQL Script Center does not have this type of SQL prompting, it does provide SQL Assist, which is an easy-to-use, graphical approach to creating a SELECT, INSERT, UPDATE, or DELETE statement. These four SQL statements are used a very large percentage of the time. Prior to using SQL Assist, there are a couple of things you need to be aware of.

The libraries available for SQL Assist are those defined under Schemas on the left pane of the Navigator panel. Note that for purposes of this discussion, an SQL Schema can be considered the same as a native library. To add or remove libraries, right click on Schemas on the Navigator panel and click Select Schemas to Display to invoke the Select Schemas dialog.

SQL Assist will create the SELECT, INSERT, UPDATE, or DELETE statement using the SQL naming convention (library.object) as opposed to system naming convention (library/object) and will qualify all references in the SQL statement with library and file name. This means that you must choose SQL Naming in Run SQL Scripts in order for the statement created by SQL Assist to execute correctly. To do this, click on Connection in the toolbar of the Run SQL Scripts panel and then JDBC in the resulting drop-down menu to invoke the JDBC setup dialog. Next click on the Format tab, and then select SQL(*SQL) under naming convention at the top of the dialog display.

To invoke SQL Assist, press F4 with the cursor placed anywhere on the Run SQL Scripts Input panel, or click on Edit in the toolbar and select SQL Assist from the drop-down menu. Either option takes you to the primary SQL Assist dialog panel, which is divided into three parts.

The upper-left portion contains the Outline view, which shows a high level representation of the clauses that comprise SQL statement. Clicking on any clause or node in the outline view causes the detailed information for the clause or node to be displayed in the Details area in the upper-right portion of the panel.

Initially the Details area shows the SELECT, INSERT, UPDATE, and DELETE statements that can be generated, along with the connection information. The SELECT statement is the default selection. The Details area is used to graphically add elements to the SQL statement, and changes based on the clause or node you select in the Outline view in the upper left.

When you select a node in the Outline view and make changes in the Details area, SQL Assist generates code in the SQL Code View area in the lower portion of the panel. The code is syntax-highlighted. In some environments, you can edit the SQL code in the SQL code view.

Once the SQL statement is to your liking, click OK and the generated SQL statement will be inserted in the Run SQL Scripts window, and the SQL Assist dialog will close. The generated SQL statement can then be executed.

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.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot