Writing SQL with SQL

by | Apr 23, 2020 | Blog, Db2

Writing SQL with SQL

The Db2 RDBMS offers many great tools for getting things done. There are, however, times when tools are unavailable. This isn’t a problem when you’re performing a simple and discrete task. When the task has many iterations then like dropping triggers or granting privileges then running the commands manually can be tedious and time-consuming. For these occasions you can use SQL to write out the commands for you. Ember Crooks describes the basic methodology in her blog article Basic scripting tricks for DB2(LUW). My intention here is to provide those basics, explaining various functions, and offer additional insight for using this method. The process outlined here is intended for use with any version of Db2 on Linux/Unix OS’s.

The Basics of Writing SQL with SQL

The process for using SQL to write SQL involves running a select statement with an expression that combines text with data results from tables and redirects that output to a file for execution. The basics of this are:

  • Use a basic select statement to create other SQL statements
  • Do not include ‘db2’ at the start of the expression since this is unnecessary
  • Use the ‘-x’ CLP option to eliminate the column header and footer from the results
    (details for Db2 CLP options can be found at this knowledge center page)
  • Wrap strings in single quotes, concatenating them with the table data using || (the CONCAT function)
  • Keep the SQL statement output clean using RTRIM
  • Be sure to include a terminating character

When creating statements with this method, care should be taken to properly handle special characters. The single quotation mark, or apostrophe, is one such case that bears specific consideration. If the apostrophe is part of the string, it will need to be escaped or else Db2 will interpret it as a delimiter for the string. To escape this character preface it with another apostrophe. The range of special characters for Db2 can be found in their knowledge center characters page.

Example

This example uses SQL to write the necessary statements for dropping triggers. Here we target all triggers but a where clause could also be added to restrict the triggers targeted for dropping.

db2 -x "select 'drop trigger ' || trigname || ';' from syscat.triggers with ur" > trig_drops.sql

Which will produce output similar to this:

drop trigger STAG0001;
drop trigger STAG0002;
drop trigger STAG0003;
drop trigger STAG0007;
drop trigger STAG0008;

The output from this statement can then be executed using the following command (be sure to include the ‘v’ option or otherwise direct the output to a file so that you can see if any of the statements fail):

db2 -tvf trig_drops.sql

Working with Distinct

This is great and works for the majority of use cases, but occasionally you need to do something more complex, or we need to keep our results restricted to unique values, like using the sysinstallobjects procedure for upgrading explain tables. Upgrading these tables after upgrading your Db2 version is important to maintain their functionality. You could search for all of the explain tables and then write and call the procedure for each schema manually, or you can use SQL to write the statements for you. Your first attempt might look something like this:

db2 -x "select 'call sysinstallobjects(''EXPLAIN'',''M'',''NULL'',''' || rtrim(tabschema) || ''');' from syscat.tables where tabname like 'EXPLAIN' and type='T' with ur" > explain.sql

This statement will produce viable statements, but there will be a lot of redundancy as shown below:

call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','SYSIBM');
call sysinstallobjects('EXPLAIN','M','NULL','SYSIBM');
call sysinstallobjects('EXPLAIN','M','NULL','SYSIBM');
call sysinstallobjects('EXPLAIN','M','NULL','SYSTOOLS');
call sysinstallobjects('EXPLAIN','M','NULL','SYSTOOLS');


Your next attempt will be to write the statement leveraging the power of distinct to eliminate the duplicate entries:

db2 -x "select 'call sysinstallobjects(''EXPLAIN'',''M'',''NULL'',''' || distinct(rtrim(tabschema)) || ''');' from syscat.tables where tabname like 'EXPLAIN%' and type='T' with ur" > explain.sql

But this command will fail with the following error:

SQL0440N No authorized routine named "DISTINCT" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

There are two options for making distinct work to produce the desired results. Both methods will work, however, the first is likely to be a better choice for most circumstances.

METHOD 1:

This first method would be more desirable for its simplicity and ease of use. To do so, simply place the distinct clause out in front of your selected criteria, wrapping the expression in parenthesis like so:

db2 -x "select distinct('call sysinstallobjects(''EXPLAIN'',''M'',''NULL'',''' || rtrim(tabschema) || ''');') from syscat.tables where tabname like 'EXPLAIN' and type='T' with ur" > explain.sql

this will produce the desired results like so:

call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','SYSIBM');
call sysinstallobjects('EXPLAIN','M','NULL','SYSTOOLS');

METHOD 2:

This second method is more complex, and certainly wouldn’t be necessary for our sample case, but in some circumstances the concept may be useful so I include it here. We can also get distinct to work using a ‘with’ statement.

db2 -x "with temptable (schema) as (select distinct(tabschema) from syscat.tables where tabname like 'EXPLAIN' and type='T' with ur) select 'call sysinstallobjects(''EXPLAIN'',''M'',''NULL'',''' || rtrim(schema) || ''');' from temptable" > explain.sql

The output will be the same as method 1:

call sysinstallobjects('EXPLAIN','M','NULL','DB2INST1');
call sysinstallobjects('EXPLAIN','M','NULL','SYSIBM');
call sysinstallobjects('EXPLAIN','M','NULL','SYSTOOLS');

Piping the Statements

In our process thus far we’ve chosen to redirect the output to a file for later execution. For small result sets the intermediary file can be eliminated in lieu of piping the results for execution immediately. This is achieved by piping the select statement back into the Db2 CLP. Using our trigger example the complete statement would look like this:

db2 -x "select 'drop trigger ' || trigname || ';' from syscat.triggers with ur" | db2 -tv

The initial statement is exactly the same as before, but instead of redirecting the output to a file we pipe it back through Db2 for immediate execution. The drawback to this method is the system’s pipe buffer size. If the results are too large for the buffer, the command will hang. The amount of space available to the pipe buffer is different from system to system and there is no standard way for checking the size.

When using this method it is important to include the ‘x’ option at the beginning. Excluding the ‘x’ will cause the output header to be included with the first command which will cause it to fail.

Conclusion

Using SQL to create statements helps DBA’s perform tedious tasks easily. This method can be utilized for a variety of situations and circumstances beyond those suggested here. For instance, you can use this method to build a single SQL statement where the table queried has a huge number of columns or to write grant statements for setting up a group or role.