Guru: Content Assist Plus in Run SQL Scripts

November 29, 2021 Paul Tuohy

By now, you should be well aware that Run SQL Scripts provides content assist (promoting) for Select statements (see Guru: ACS 1.1.8.3 Content Assist Includes Prompt For SQL! ). But did you know that you can also use content assist for stored procedures, table functions and parameters? Let’s see how it works, using some of the Integrated File System (IFS) procedures and functions provided by IBM i Services.

Prompting a Stored Procedure

Open Run SQL Scripts, type in:

call qsys2.

Then press F4 (or Ctrl+Space). You will be presented with a list of all the stored procedures in the schema QSYS2.

This is a very big list, so let’s hone it down to the IFS procedures by typing in ifs. This will shorten the list to just those procedures starting with the letters IFS.

IFS_WRITE is the one we want, so simply press Enter and the script is updated with the selected procedure name. Now add opening and closing parentheses, place the cursor between the parentheses, and you are ready to prompt for parameters.

Prompting Stored Procedure Parameters

Before we look at prompting parameters, I would like to clarify something about SQL procedure and function parameters that a lot of people do not seem to be aware of. Although the syntax is different, SQL procedures and parameters work in the same way as system commands — they can be entered by position or they can be identified by a parameter keyword.

With the cursor placed between the two parentheses, press F4 and you are presented with the list of parameters for the stored procedure.

If you move the cursor down through the list of parameters, the pane on the right will provide the details of what you need to provide for the parameter (assuming the person who wrote the procedure commented it accordingly).

Pay careful attention to the information provided. The lack of a Default keyword (as with the PATH_NAME parameter) indicates a required parameter.

Select the required parameters and any other parameters you might want to change.

Press Enter and the script will be updated with the names of the parameters.

call qsys2.IFS_WRITE(PATH_NAME => , LINE => , FILE_CCSID => )

Now all you have to do is provide the values for the parameters and run the statement.

call qsys2.IFS_WRITE(PATH_NAME => '/home/TUOHYP/myFile.txt', 
                     LINE => 'This is some text', 
                     FILE_CCSID => 1208);

Prompting A Table Function

Prompting a table function is just as easy. Enter the following:

select * from table(qsys2.ifs

Press F4 to see a list of table functions, in schema QSYS2, that start with the characters IFS.

Select the required table function, place the cursor between the parentheses and press F4 to get the list of possible parameters.

Select the required parameters and any other parameters you might want to change. Press Enter and the script will be updated with the names of the parameters.

select * from table(qsys2.IFS_READ(PATH_NAME => ))

Now all you have to do is provide the values for the parameters and run the following statement.

select * from table(qsys2.IFS_READ(PATH_NAME => '/home/TUOHYP/myFile.txt'));

The ability to prompt SQL procedures/functions and, especially, their parameters is something I have found invaluable.

RELATED STORY

ACS 1.1.8.3 Content Assist Includes Prompt For SQL!