Guru: ACS 1.1.8.3 Content Assist Includes Prompt For SQL!

February 10, 2020 Birgitta Hauser

STRSQL, the green-screen facility for executing SQL Statements, has had its days. Nevertheless, many SQL users still prefer this legacy interface over the Access Client Solutions (ACS) Run SQL Scripts facility. Others use third-party tools to execute SQL statements. The common excuse has been that the Run SQL Script facility does not include a way to prompt SQL commands, tables, and columns. In ACS version 1.1.8.3, Content Assist includes prompting for SQL statements.

Before ACS Version 1.1.8.3, no prompting was available within the Run SQL Scripts Facility. To access tables, views or materialized query tables (MQTs), we needed to know the names of our database objects and columns. We also needed to know in which schemas (libraries) our database objects were located. Alternatively, we could run queries against the SYSTABLES and SYSCOLUMNS views to find database object names and/or column names. After finding the appropriate information, we had to copy or type the database object and column names by hand. You will agree, for complex queries copy and paste could get a little sophisticated. Not to mention the typos!

Fortunately, this pain ended with the introduction of Content Assist in the ACS Run SQL Scripts facility.

What Is Content Assist?

Content Assist is a prompter for SQL commands on different levels:

• Commands: You may prompt the most commonly-used standard SQL commands, such as SELECT, INSERT, and UPDATE.
• Objects: You may prompt tables, views, MQTs, aliases, physical and logical files, and aliases from within SQL statements.
• Columns: You may select columns from the specified tables, views, physical files, and aliases. It is even possible to select columns generated in common table expressions.

Where Is Content Assist And How Can It Be Used?

You can find Content Assist in the Run SQL Scripts interface in the Edit tab. You can also activate Content Assist by pressing the Control and Space keys.

Figure 1 displays the start menu of IBM i Client Access, from which you can open Run SQL Scripts. (There are also several other places from which you can activate Run SQL Scripts.) Figure 1 shows the location of the Content Assist option in Run SQL Scripts.

Figure 1: Content Assist in ACS – Run SQL Script.

Selecting Standard SQL Commands

If you activate Content Assist from an empty screen or after a semicolon-terminated SQL command, you’ll see a pop-up list of predefined SQL commands, as shown in Figure 2.

Figure 2: SQL Standard Commands to be selected.

To insert the desired SQL command, position to the command and double click. Afterward, you can modify and complete the inserted SQL command.

Figure 3 shows an inserted “SELECT with GROUPING” SQL command.

Figure 3: Inserted SQL Command: SELECT with GROUPING.

Selecting Tables, Views, MQTs, Aliases, And Physical Files

You can select tables, views, MQTs, and physical files within an SQL SELECT statement, but you can also select within other SQL commands, such as UPDATE and DELETE. To get a list of database objects, position within the SQL command where the table or view must be specified and then activate Content Assist, using either of the methods described above. For example, to get the database object list within a SELECT statement, you have to position within the FROM clause and then activate Control Assist. If the SELECT statement includes common table expressions (CTEs), the file selection works not only within the final SELECT, but also within the CTEs.

When working with the SQL naming convention, all database objects within the default/current schema are listed without a schema specification. If the default schema is not explicitly set in the connection or by executing the SET CURRENT SCHEMA SQL command, all database objects in the schema with the same name as the current user profile are displayed.

When working with the system naming convention, all database objects located within the schemas in the library list are displayed. Database objects that are unique within the library list are not qualified, while all database objects that can be found in multiple schemas are qualified with the appropriate schema.

You may use the F11 key to switch between the SQL (long) names and the system (short) names. To insert, double-click on the desired database objects.

Content Assist displays the following additional information about the database object in a second window.

• Table: If the SQL names are displayed within the left window, the system names are displayed within the right one, and vice versa.
• Type: Type of the database object, i.e. table, view, materialized query table (MQT), alias, physical or logical file
• Schema: Library/schema where the database object is located.

This information is especially important when the system naming convention is used and the database objects are listed without schema specifications.

Figure 4 shows all database objects that can be found in the library list, since the system naming convention is used.

Figure 4: Content Assist – Display all database objects within the library list.

Listing all database objects within the library list might be nice, but quite often hundreds or even thousands of database objects are stored within a single schema or within the library list. Scrolling through all of these database objects until the desired one is found can become very annoying, so a partial listing would be very helpful.

When you’re working with the system naming convention and you know the schema in which the database object is located, you can specify the schema followed by a period and then activate Content Assist. Now the list contains only the database objects within the specified library. You may also specify a schema followed by a period when working with the SQL naming convention,

The specified schema does not have to be located in the library list (system naming convention) or the default schema (SQL naming convention). You can use this method to find database objects in any schema.

In Figure 5, only the database objects in the COMSQLQRY schema are displayed.

Sometimes even restricting the list to a specific schema returns too many database objects. For a more detailed selection, you can specify the first few characters of the name of the database object. Qualifying the specified characters with the library list is not mandatory. Depending on the naming convention, either all database objects beginning with the specified characters located in either the library list or the default schema are displayed. If the first few characters of the database object name are qualified with the schema, only the database objects beginning with the same characters in the specified schema are displayed.

Figure 6 shows a list of all database objects beginning with SAL located in the current library list. Database objects that can be found in multiple schemas are qualified with the appropriate schema.

Figure 6: Content Assist – Listing all database objects beginning with SAL within the library list.

Selecting Columns And Fields

You can activate Content Assist to select columns or fields anywhere a column name can be specified. For example, in a SELECT statement, you can use Content Assist in the SELECT list, within the FROM clause, within the WHERE conditions, within the GROUP BY clause, within the HAVING clause and also within the ORDER BY clause. It is also possible to activate Content Assist in common table expressions and nested sub-selects at the appropriate positions.

You can select a single column, or you can highlight multiple columns and insert all of them, separated by commas, in a single step. There is also an option for selecting all columns of the current listing. This might be especially helpful for tables or views with a lot of columns, when all or most columns must be explicitly listed within the SELECT Statement.

Use the F11 key to switch between the SQL names and the system names. If the SQL column names are displayed, Content Assist inserts the SQL names of the columns, otherwise it inserts the system names. You can insert all columns of the database objects specified in the FROM clause, as well as those generated or selected from the related common table expressions.

The following more detailed information about the columns is displayed within a separate window.

• Field: If the SQL names of the columns are displayed in the left window, the system names are displayed in the right one, and vice versa.
• Type: Data type of the column, and depending on the data type, the length and number of decimal positions.
• CCSID: Character-Set-ID is displayed for all character data types, both single- and double-byte.
• Nullable: Whether the column can contain nulls or not.
• Table: Table, view, MQT, alias, physical or logical file where the column can be found.
• Schema: Schema of the database object.
• Text: Column description (equivalent of the TEXT keyword in DDS).
• Comment: More detailed information about the database object (if defined).

Figure 7 shows all columns of the SALESCUST view. The right window contains the detailed information about the highlighted columns. If you position on All Columns and double click, Content Assist inserts all column names, separated by commas.

If multiple files are joined, all column names of all joined files are displayed. Column names that are available in multiple files are qualified with the file name. If files have correlation names, the columns are qualified with the correlation names rather than with the file names. In Figure 8, the SALES table has the correlation name S and the ADDRESSX table to the name A in the FROM clause. All column names are qualified with the correlation name in the column list.

Figure 8: Content Assist – Column list with correlation names.

It is possible to display only a subset of the column names. You can follow the database object name or the correlation name can with a period. In this case, all columns within this database object or common table expression are displayed.

It is also possible to specify the beginning of the appropriate column name before activating Content Assist. Only the column names that start with the specified characters are displayed. In Figure 9, all columns starting with CUST and located in one of the joined tables are displayed. The CUSTNO column is located within the SALES table but also within the ADDRESSX table. Since none of the tables has a correlation name, the CUSTNO column is qualified with the related table name.

Figure 9: Content Assist – Select a subset of all Column Names.

Finally, A Few Comments

Content Assist is not complete, but IBM is working on it! I expect a lot of enhancements in the next version of IBM i Access Client Solutions, some that we may wish we already had. Nevertheless, Content Assist in ACS Version 1.1.8.3 is a great tool that enormously facilitates coding SQL statements.

Content Assist provides much more information and is easier to handle as the prompt in the green screen STRSQL facility. There is no longer any excuse to stay with the outdated green-screen facility or to use a third-party tool. If you are not yet working with IBM i Access Client Solutions or if you are not yet on ACS version 1.1.8.3, you can download and install it from the following link:

https://www.ibm.com/support/pages/ibm-i-access-client-solutions

And now have fun with Content Assist!