Posted by: Serge Rielau
Motivation
Have you ever had the task to write a script for a database?
Often users write scripts to monitor the database in a host language such as PERL or bash.
But these host languages have some downsides:
- The language environment must be installed on the server.
This creates a dependency on a specific environment and one more moving part
- The environment may not be portable.
For example if you move the database from Windows to Linux a shell script is unlikely to work unchanged
- If a lot of data needs to be processed in the script performance may be bad
Shuffling data from the server to the client just to perform logic is very inefficient.
This is even true if the client is local to the server.
So clearly there is a desire to allow for server side, portable scripting.
Scripting using SQL procedures
Traditionally the preferred way to perform server side scripting has been through the use of SQL stored procedures.
A SQL stored procedure provides a several benefits:
- Minimal overhead in executing logic based on data returned from SQL within.
DB2 does not need to pass output variables or result set through DRDA to a client.
Instead DB2's Procedure Virtual Machine (PVM) can look at the data locally.
- Code is portable across all environments supported by DB2 for LUW and to a large extend other DB2 family members.
So there is no cost to port script. Further more copies of the same procedure can be used everywhere.
- A procedure can be executed from any client that supports an SQL API.
So logic can be pre-canned and driven by a tool, an application or a DB2 shell simply by invoking the procedure.
- A procedure encapsulates access rights.
It is possible for an administrator to write a set logic using elevated privileges, but still safely allow a wider audience to execute the logic.
Here is an example for a stored procedure which performs all the tasks of adding a generated column as described in my last post on generated columns.
CREATE OR REPLACE PROCEDURE add_gen_col(tabschema VARCHAR(128) DEFAULT (CURRENT SCHEMA),
tabname VARCHAR(128),
colname VARCHAR(128),
genexp CLOB(2M))
BEGIN
DECLARE alter CLOB(2M);
DECLARE integrity VARCHAR(1000);
SET alter = 'ALTER TABLE "' || tabschema || '"."' || tabname
|| '" ADD COLUMN "' || colname || '" GENERATED ALWAYS AS ('
|| genexp || ')';
SET integrity = 'SET INTEGRITY FOR "' || tabschema || '"."' || tabname || '" ';
EXECUTE IMMEDIATE integrity || 'OFF';
EXECUTE IMMEDIATE alter;
EXECUTE IMMEDIATE integrity || 'IMMEDIATE CHECKED FORCE GENERATED';
END;
/
I always have a hard time remembering the exact syntax of SET INTEGRITY. Now I do not need to remember anymore:'
CREATE TABLE hr.emp(name VARCHAR(10), salary INTEGER, bonus INTEGER);
INSERT INTO hr.emp VALUES ('John', 30000, 3000),
('Jack', 45000, 6000),
('Jill', 37000, 8000);
CALL add_gen_col('HR', 'EMP', 'COMP', 'SALARY + BONUS');
NAME SALARY BONUS COMP
---------- ----------- ----------- -----------
John 30000 3000 33000
Jack 45000 6000 51000
Jill 37000 8000 45000
Compiled compound statements (aka anonymous blocks)
Procedure do have one big disadvantage though:
In order to create a procedure we need to have sufficient privileges to execute DDL and a schema where we can place the procedure in.
Even if we have the authorization to execute DDL on principle the DBAs may not like it to see random tools deployed on a database.
This is even worse if the script is to be called by a tool.
That is when you install a tool on a client machine you do not expect that this tool requires procedures to be deployed on every database just to operate.
Since many years DB2 has supported the execution of scripts using dynamic compiled statements (BEGIN ATOMIC ... END).
But these blocks had to be written in inline SQL PL which has severe limitations.
For example dynamic SQL itself (as used in the above procedure) is not allowed within an dynamic compound.
In DB2 9.7 compiled compound statements have been introduced.
A compiled compound statement is a BEGIN.. END block that stands on its own without being part of a trigger or routine.
compiled compounds can be dynamically prepared and executed using PREPARE/EXECUTE or EXECUTE IMMEDIATE.
They can be used everywhere a CALL statement can be used.
Another name for compiled compound statement is anonymous block.
To show how they work we will redo the example above, this time using an anonymous block instead of a procedure.
DROP TABLE hr.emp;
CREATE TABLE hr.emp(name VARCHAR(10), salary INTEGER, bonus INTEGER);
INSERT INTO hr.emp VALUES ('John', 30000, 3000),
('Jack', 45000, 6000),
('Jill', 37000, 8000);
BEGIN
DECLARE tabschema VARCHAR(128) CONSTANT 'HR' ;
DECLARE tabname VARCHAR(128) CONSTANT 'EMP';
DECLARE colname VARCHAR(128) CONSTANT 'COMP';
DECLARE genexp CLOB(2M) CONSTANT 'SALARY + BONUS';
DECLARE alter CLOB(2M);
DECLARE integrity VARCHAR(1000);
SET alter = 'ALTER TABLE "' || tabschema || '"."' || tabname
|| '" ADD COLUMN "' || colname || '" GENERATED ALWAYS AS ('
|| genexp || ')';
SET integrity = 'SET INTEGRITY FOR "' || tabschema || '"."' || tabname || '" ';
EXECUTE IMMEDIATE integrity || 'OFF';
EXECUTE IMMEDIATE alter;
EXECUTE IMMEDIATE integrity || 'IMMEDIATE CHECKED FORCE GENERATED';
END;
/
Since anonymous blocks do not have a formal parameter list I have used local variables to gather up all the inputs in an easy to find place.
But just like with any other dynamic SQL statement extracting literals from the statement is good practice.
You can use regular parameter markers for variables here or named parameter markers.
CLPPlus works well with named parameter markers.
So let's create some CLPPlus variables and set them.
Ironically we must use an anonymous block for that.
But this also serves to show how you can pass output parameters:
VARIABLE tabschema VARCHAR(128);
VARIABLE tabname VARCHAR(128);
VARIABLE colname VARCHAR(128);
VARIABLE genexp VARCHAR(128);
BEGIN
SET :tabschema = 'HR',
:tabname = 'EMP',
:colname = 'COMP',
:genexp = 'SALARY + BONUS';
END;
/
When using named parameter markers it is important to avoid specifying the same variable twice.
DROP TABLE hr.emp;
CREATE TABLE hr.emp(name VARCHAR(10), salary INTEGER, bonus INTEGER);
INSERT INTO hr.emp VALUES ('John', 30000, 3000),
('Jack', 45000, 6000),
('Jill', 37000, 8000);
BEGIN
DECLARE alter CLOB(2M);
DECLARE integrity VARCHAR(1000);
SET alter = 'ALTER TABLE "' || :tabschema || '"."' || :tabname
|| '" ADD COLUMN "' || :colname || '" GENERATED ALWAYS AS ('
|| :genexp || ')';
SET integrity = 'SET INTEGRITY FOR "' || :tabschema || '"."' || :tabname || '" ';
EXECUTE IMMEDIATE integrity || 'OFF';
EXECUTE IMMEDIATE alter;
EXECUTE IMMEDIATE integrity || 'IMMEDIATE CHECKED FORCE GENERATED';
END;
/
ERROR near line 7:
SQL0270N Function not supported (Reason code = "116").
Generally, for best readability I recommend to copy parameter markers into local variables at the beginning of the compound statement.
That way, when looking at the text you can immediately see which parameters are expected.
BEGIN
DECLARE tabschema VARCHAR(128);
DECLARE tabname VARCHAR(128);
DECLARE colname VARCHAR(128);
DECLARE genexp VARCHAR(128);
DECLARE alter CLOB(2M);
DECLARE integrity VARCHAR(1000);
SET tabschema = :tabschema,
tabname = :tabname,
colname = :colname,
genexp = :genexp;
SET alter = 'ALTER TABLE "' || tabschema || '"."' || tabname
|| '" ADD COLUMN "' || colname || '" GENERATED ALWAYS AS ('
|| genexp || ')';
SET integrity = 'SET INTEGRITY FOR "' || tabschema || '"."' || tabname || '" ';
EXECUTE IMMEDIATE integrity || 'OFF';
EXECUTE IMMEDIATE alter;
EXECUTE IMMEDIATE integrity || 'IMMEDIATE CHECKED FORCE GENERATED';
END;
/
SELECT * FROM HR.EMP;
NAME SALARY BONUS COMP
---------- ----------- ----------- -----------
John 30000 3000 33000
Jack 45000 6000 51000
Jill 37000 8000 45000
returning result sets
I have demonstrated above how you can SET output parameter markers to pass back information to the client. In addition to returning values of simple base types you can also return more complex values such as ARRAY, ROW or REFCURSOR. This capability depends of course on the ability of the client to receive these types in general. The following example shows how to return a result set to CLPPlus using a REFCURSOR
VARIABLE cur REFCURSOR;
BEGIN
DECLARE cur CURSOR;
SET cur = CURSOR FOR SELECT * FROM HR.EMP;
OPEN cur;
SET :cur = cur;
END;
/
PRINT cur;
NAME SALARY BONUS COMP
---------- ----------- ----------- -----------
John 30000 3000 33000
Jack 45000 6000 51000
Jill 37000 8000 45000
Conclusion
Anonymous blocks provide a powerful tool for DBAs, application developers and tools.
Using anonymous blocks you can execute complex portable scripts on the server without requiring any server side DDL artifacts.
In addition to what i have above DB2 10 also introduces local types and local procedures which can be used in anonymous blocks.
I will blog about those in a future post.
#Db2