Guru: When Playing With SQL

March 18, 2019 Paul Tuohy

One of the questions I have been asked a lot at conferences is “How do you figure out x in SQL?” In this article, I will discuss four things I use a lot when playing with SQL in Run SQL Scripts: VALUES, SYSIBM.SYSDUMMY1, global variables, and the system catalog.

VALUES

When I am trying to figure out how an SQL function works, my first port of call is the VALUES statement. VALUES derives a result directly from an expression. For example, the following statement:

values upper('paul');

Would generate the following result set:

You can specify more than one value in the expression. Enclosing all of the expressions in parentheses results in a single row. This statement:

values  (upper('paul'), lower('PAUL'), trim('   Tuohy  '));

Would generate the result set:

If you omit the parentheses, you will get one row per expression. This statement:

values  upper('paul'), lower('PAUL'), trim('   Tuohy  ');

Would generate the result set:

You must be careful when you omit the parentheses and ensure that each of the expressions returns the same data type. You are performing a type of UNION. If you get it wrong, for instance a statement such as:

values upper('paul'), int(123.45);

Would return a result set of:

This is not what we want!

One of the issues with VALUES, when looking at multiple expressions in the result set, is the column headings. We cannot assign an AS name as we would in a SELECT clause. Which brings us to SYSIBM.SYSDUMMY1.

SYSIBM.SYSDUMMY1

As the name implies, SYSIBM.SYSDUMMY1 is a dummy table. It contains one row and one column, which is named IBMREQD and contains a value of ‘Y’. SYSIBM.SYSDUMMY1 is used extensively in SQL examples on the web and in the Knowledge Center. We are not interested in its content. We just need a table we can use in a SELECT statement:

select upper('paul') upper_, 
       lower('PAUL') lower_, 
       trim('   Tuohy  ') trim_ 
from sysibm.sysdummy1;

This statement would result in:

Global Variables

Most of the time it is just fine to use simple literals (as in the previous examples), but sometimes you will be dealing with a large literal value (XML or JSON) or values that need to be of a certain data type (XML or parameters for a call to a stored procedure, where a literal might have to be cast to a date type).

Global variables provide a means of having a variable whose value is unique to our job. The global variable can be used anywhere than a column name or literal would be used. This is an example of creating a global variable:

CREATE VARIABLE PTARTICLES.MY_LONG_NAME_VAR 
  FOR SYSTEM NAME MYVAR 
  VARCHAR(1000) 
  DEFAULT 'Some default text';

We can now reference the global variable anywhere we would reference a literal or a column:

values MY_LONG_NAME_VAR; 
values MYVAR;
select MYVAR from sysibm.sysdummy1;
set MYVAR = 'Different Text';

There are a few points worth noting about global variables:

• The value of a global variable is unique to the job. Changing the value of a global variable does not affect any other job that may also be using the global variable.
• If you Reconnect (in Run SQL Scripts), you will be dealing with a new version of the global variable – you have just started a new job.
• If you want to refer to the global variable without using a qualified name, then the schema (containing the global variable) must be included in the PATH directive. SET SCHEMA will not do it.
• Creating a global variable results in the creation of a service program, so the system name is important if you want a meaningful system name.

System Catalog

When using Run SQL scripts with a database that you are not familiar with, one of the challenges is know what all the columns are in table or view. This is where a decent prompt facility would be of benefit. (They are working on it as I type.) But all of the information we require is available in the system catalog.

The system catalog is a database which cross references all of the database definitions on the system. To get a list of columns in a table you could try something along the lines of:

select column_name, system_column_name,
       column_text, data_type, 
       length, numeric_scale 
from qsys2.syscolumns
where table_schema = 'SIDDBV2'
and table_name = 'ALUMNI'
order by ordinal_position;

If you take the option to Display Results in Separate Window you will now have a record layout you can reference as required and you can copy column names from the result window, shown here:

Play tools

These are my standard play tools with SQL – do you have any?

Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.