November 29, 2021

Paul tuohy

By now you should know that Run SQL Scripts provides content assistance (promotion) 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.

Invite a stored procedure

Open Execute SQL Scripts, type:

call qsys2.

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

This is a very long list, so let’s refine it with IFS procedures by typing if. This will reduce the list to only procedures starting with the letters IFS.

IFS_WRITE is the one we want, so just hit enter and the script is updated with the name of the selected procedure. Now add opening and closing parentheses, place the cursor between the parentheses, and you are ready to request parameters.

Invite stored procedure parameters

Before looking at the prompt parameters, I would like to clarify something about SQL procedure and function parameters that a lot of people don’t seem to be familiar with. Although the syntax is different, SQL procedures and parameters work the same way as system commands: they can be entered by position or identified by a parameter keyword.

With the cursor placed between the two parentheses, press F4 and the list of stored procedure parameters is displayed.

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

Pay special attention to the information provided. The absence of a default keyword (as with the PATH_NAME parameter) indicates a required parameter.

Select the required settings and any other settings you 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 need to do is supply the parameter values ​​and execute the instruction.

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

Invite a table function

Just as simple is the prompt for a table function. Enter the following:

select * from table(qsys2.ifs

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

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

Select the required settings and any other settings you 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 => ))

All you have to do is provide the parameter values ​​and execute the next instruction.

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

The ability to invite SQL procedures / functions and, in particular, their parameters is something that I have found invaluable.

RELATED STORY

ACS 1.1.8.3 Content Assistant includes a prompt for SQL!