Prompting CL in Run SQL Scripts

December 9, 2009 Skip Marchesani

System i Navigator’s Run SQL Scripts lets you create scripts, from simple to complex, that can be comprised of one to many SQL statements and/or batch CL commands (no interactive or display commands allowed). SQL statements are entered without any prefix, and batch CL commands must be prefaced with CL.

The syntax for SQL statements and batch CL commands can get quite complex, and keying or creating each without some sort of help or assistance can be a challenge. RUN SQL Scripts does a good job of helping you create either.

Figure 1.

There is SQL Assist for SQL statements and CL Prompt for batch CL commands. Both are accessed the same way, and since the focus of this article is prompting CL commands, the CRTLIB command will be used as our example as shown in Figure 1.

Place the cursor on or within the batch CL command you wish to prompt. Next click Edit on the left side of the toolbar, and then SQL Assist/Prompt CL as shown in Figure 2, or just press F4 while the cursor is on or within the CRTLIB command.

Figure 2.

After clicking on SQL Assist/CL Prompt or pressing F4, you will see the Create Library panel shown in Figure 3. A quick examination and comparison will show that this Create Library panel is the graphical equivalent of the basic green screen prompt for the CRTLIB command.

Figure 3.

If you click on View on the left side of the toolbar for the Create Library panel as shown in Figure 4, you will also find that the same command key functions exist for this graphical CL prompting as for the Green Screen prompting seen in Figure 5.

Figure 4.

Figure 5.

The function key titles may differ–Advanced vs. Additional Parameters for F10, and Reset vs. Refresh for F5–but the function remains the same.

Figures 6 and 7 illustrate the results of pressing F10 to see advanced or additional parameters.

Figure 6.

Figure 7.

Figures 8 and 9 show the result of pressing F11 to see keywords.

Figure 8.

Figure 9.

To complete prompting of the command, key in the required and optional (if any) parameters and then click the OK button at the bottom of the Create Library panel as shown in Figure 10.

Figure 10.

The completed command string will then be returned in the Run SQL Scripts panel as shown in Figure 11.

Figure 11.

Take note that there is no support for menu based prompting to see a group of related commands, such as is available with the GO CMDxxx (GO CMDCRT) command.

As previously stated, only batch commands are allowed in scripts. Interactive commands such as DSPxxx (DSPLIB, for example) are not allowed.

To prompt a batch CL command, the cursor must be on or within the textual context of the command. If the cursor is not, Run SQL Scripts assumes you want to create an SQL statement and brings up SQL Assist.

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.