Native SQL Procedures

Stored procedures have been available in DB2 z/OS since v5, but were limited in that they needed to be written in a third generation language such as COBOL or C, and implemented as an external stored procedure running in a Workload Manager (WLM) controlled address space.

The SQL Procedures Language has been available since v5, in which DB2 z/OS stored procedures could be written in a native DB2/SQL language that is a subset of the ANSI SQL/PSM language standard. But to implement an SQL Procedure prior to DB2 v9, the SQL/PL stored procedure needed to be precompiled and translated into a C program, which would then in turn be compiled and implemented as an external stored procedure.

Since Phoenix was not licensed for a C compiler on our mainframe environment, the use of SQL Procedures on DB2 z/OS was not an option prior to DB2 v9.

The native SQL Procedures support introduced in DB2 v9 removes this restriction. Stored procedures now written in SQL/PL no longer need to be precompiled and converted to C programs. They can now be run natively directly within the DB2 engine.

Native XML Support

DB2 now allows for unstructured XML data to be stored natively alongside structured relational data in a DB2 table via DB2’s “pureXML” technology.

Among the capabilities provided by pureXML in DB2 are:

• A native XML data type
• SQL/XML language, which provides full XML functions within the SQL language to access XML structures
• Hybrid data access, in which both XML and relational data can be accessed together in a single SQL/XML statement
• Read and write access to XML documents and sub-documents
• XML indexes to provide efficient access paths to XML data stored within a DB2 table
• XML schema validation against an XML schema that is registered in the Schema Repository, including support for multiple versions of an XML schema

Collectively with these capabilities, DB2 becomes a repository for native storage of both relational and XML data, thus making DB2 a hybrid database server.

Prior to the introduction of pureXML technology in DB2, XML data could be stored in a DB2 database, but it would have to be stored either as an unformatted string in a CLOB data type column, or it would need to be “shredded” into individual elements that would then be stored in traditional relational columns.

If XML data was stored within a CLOB data type, then DB2 had no understanding of the structure of the XML document it was storing, and searching on components of an XML document meant that the entire document would need to be retrieved and parsed. This could be an expensive operation in terms of CPU resources. If the XML document was shredded into individual elements that were stored in traditional relational database columns, then all the inherent advantages and integrity of the original XML document would be lost.

With native XML storage, XML data can be searched on and retrieved efficiently without having to lose any of the integrity of the XML document itself.

The SQL/XML language that is introduced with pureXML allows for individual elements of an XML document to be inserted, updated and deleted. Stored XML documents can also be indexed for efficient searching and retrieval of data, in much the same way that relational indexes are used for traditional relational data in a DB2 table.

Multi-Row Fetch and Insert

DB2 is a relational database, which means that it works with “sets” of data at all times. DB2 tables are basically sets of zero to many rows of data, and SQL statements often process against sets of data (i.e. tables) and return sets of data as their results (i.e. cursors). However, because DB2 application programs are usually written in third generation programming languages such as COBOL, they can only process one row of data at a time. When working with a cursor that contains a result set, rows are fetched one at a time from within a loop in the program and processed as they are retrieved. If the result set cursor contains 100 rows, for example, then processing is bouncing back and forth between the application program and DB2 100 times, once for each fetch. This constant context switching between the application program and DB2 can generate a fair amount of CPU overhead, which is unavoidable because of the nature of 3GL “one row at a time” processing.

Multi-row fetch alleviates this problem. Cursor processing in an application program can now return multiple rows in a single fetch statement, up to 32,767 rows at one time. This is done via the use of host-variable arrays rather than single host-variable fields within the host COBOL program. The fetch statement is coded with the number of rows to be fetched with each call. The result is that with the use of multi-row fetch in COBOL programs, much of the CPU overhead of multiple fetches can be reduced or eliminated, thus improving overall performance and processing elapsed time.

Benchmark testing of multi-row fetch has shown that performance gains can start to be realized at around 10 rows per fetch, and optimal performance starts to be seen at around 100+ rows per fetch.

Similar performance gains can be seen with multi-row insert, which follows a similar philosophy. Rows to be inserted are staged in host-variable arrays, which can then be sent to DB2 in a single INSERT statement.

Multi-row fetch and multi-row inserts require a more sophisticated level of error checking. New diagnostic commands are available to help with error handling when using multi-row fetch/insert processing.

There is a moderate amount of programming changes that would need to be made to retrofit multi-row fetch/insert processing into an existing DB2 COBOL program, but the performance gains that can be attained may make it worth the effort if the typical processing workload of the program involves fetching or inserting large sets of data per transaction, and CPU consumption and elapsed runtime are chronic constraints for the application.

Materialized Query Tables

A materialized query table (MQT) is basically a pre-calculated and stored view. While an MQT can be used for any kind of view, it would more commonly be used for aggregation of data, or any kind of query that involves a fair amount of calculations to create a result set. Since an MQT is stored as a physical table, it can have indexes defined on it for efficient access.

The main advantage of an MQT is that if an MQT is defined as a particular query, the DB2 optimizer is intelligent enough to recognize a subsequent query that comes along with a similar or identical definition, and rather than process the new query to calculate the result set, it can just “reroute” the query to use the previously calculated and stored results in the MQT table. The end user or program that is executing the query does not even need to be aware of the existence of the MQT . . . this reroute is performed automatically by the DB2 optimizer. However, the MQT can also be accessed directly via SQL statements if the user or program wishes to do so.

The automatic reroute of queries to be able to access the MQT can be disabled if the MQT is defined with the “DISABLE QUERY OPTIMIZATION” option. In that case, the MQT can only be accessed directly via SQL.

One of the disadvantages of MQTs is that the data that is stored in them can and will become stale if the source tables from which the MQT data was originally derived from are updated. The MQT can be refreshed periodically via an explicitly invoked “REFRESH TABLE” SQL statement, which will re-execute the query that defines the MQT and store the new result set, overlaying the previous results.

There are two “flavors” of MQTs: “maintained by system”, or “maintained by user”. System maintained MQTs can only be populated or refreshed by a “REFRESH TABLE” statement. User maintained MQT’s can be populated or refreshed either by a “REFRESH TABLE” statement, by a LOAD utility, or by SQL DML (INSERT, UPDATE or DELETE) statements.

Label based Access Control Security

Label-Based Access Control (LBAC) allows for more granular control over who can access data in specific rows of an LBAC secured table. This is done via a multi-level security (MLS) hierarchy (i.e. a “security policy”) that can be set up within the security system.  For DB2 z/OS, the security system would be RACF.  For DB2 LUW, the components that make up the security policy will be defined within the DB2 database itself.

With LBAC security, a special column in defined in each table that will contain a security label. The security label will define what level of security is needed to access the row. Each protected table can be associated with only one security policy, though multiple security policies can exist for different tables. Each user will have one or more security labels associated with their ID (each label is associated with one security policy). When they attempt to access the data via SQL, their security label is compared to the security label associated with each row of the table. If their security label either matches or is at a higher level in the security policy than the security policy assigned to the row, they are allowed to access it. If not, then the row will be ignored as if it does not exist (they will not receive an error message).

Here is an example of a simple security policy hierarchy:

TOP SECRET
      SECRET
            UNCLASSIFIED

In a simple security policy like this, each row of data can be classified as either “TOP SECRET”, “SECRET”, or “UNCLASSIFIED”. Each user will be associated with one of these three labels, and will be able to see all rows of data that are classified with a security label that is equal to or lower in the security policy than his or her label.

For instance, say user JOE has a security label of “TOP SECRET”. He will be able to access all rows in the table, since “TOP SECRET” is the highest label in the hierarchy.

If user MARY has a security label of “SECRET”, she will be able to access all rows in the table that are labeled either “SECRET” or “UNCLASSIFIED”. She will not be able to see any rows that are labeled as “TOP SECRET”, since that label is higher in the hierarchy than her assigned security label.

If user BOB has a security label of “UNCLASSIFIED”, he will only be able to see rows labeled as “UNCLASSIFIED” since that is the lowest level in the security policy hierarchy. He will not be able to see any rows labeled “TOP SECRET” or “SECRET”.

One of the idiosyncrasies of LBAC security is that different users can execute the exact same SQL statement against the exact same table, and receive different results due to their security label.

Let’s say a particular table, named T1, contains 10 rows of data. One row of data has a security label of “TOP SECRET”. Three rows of data have a security label of “SECRET”. The other six rows of data have a security label of “UNCLASSIFIED”.

If users JOE, MARY and BOB all run the following SQL statement, they will receive different results:

SELECT COUNT(*)
    FROM T1;

JOE (“TOP SECRET” label) would get a result of 10, as he can see all rows in the table.

MARY (“SECRET” label) would get a result of 9, as she can see the three “SECRET” rows and the six “UNCLASSIFIED” rows.

BOB (“UNCLASSIFIED” label) would get a result of 6, as he can only see the six “UNCLASSIFIED” rows.

Data Encryption and Decryption

Data can be encrypted and decrypted at the column level using the ENCRYPT and DECRYPT SQL scalar functions. A password is needed to encrypt and decrypt the data. The password can either be supplied as part of the ENCRYPT or DECRYPT function, or it can be set within a DB2 special register via the SET ENCRYPTION PASSWORD statement (recommended). A password hint can also be stored with the encrypted data to help “remember” the password. The password hint can be retrieved by the GETHINT function.

Once the data has been stored after being encrypted via the ENCRYPT function, the same password must be used to correctly decrypt the data when retrieving at a later time. If a different password is used for decryption, the data returned will not match the original string. No error or warning message will be returned.

Example of using the ENCRYPT and DECRYPT functions:

Example 1 (no password hint):

SET ENCRYPTION PASSWORD = ‘TARZAN’;
INSERT INTO EMP (SSN) VALUES ENCRYPT (‘289-46-8832’);
SELECT DECRYPT_CHAR(SSN) FROM EMP;

Example 2 (with password hint):

INSERT INTO EMP (SSN) VALUES ENCRYPT (‘289-46-8832’, ‘TARZAN’, ‘? AND JANE’);
SELECT DECRYPT_CHAR(SSN, ‘TARZAN’) FROM EMP;

NOTE: In the LUW environment, data encryption and decryption is already included in the base product.  However, on the z/OS environmenrt, the Encryption Facility is a separately licensed product that requires specific hardware, and is not currently available in the Phoenix environment.

Common Table Expressions and Recursive SQL

A common table expression is basically a temporary table that exists only for the duration of a single SQL statement. Ideally, this can be used to replace a process in which data is gathered and stored in an interim table, which is then accessed by a subsequent SQL statement. This can now be done in a single SQL statement.

A common table expression can reference another common table expression that was defined earlier within the same SQL statement, thus allowing you to “stack” a number of intermediate processing results and process them all within a single execution of an SQL statement.

A common table expression can also reference itself, which allows for recursive processing within SQL. This is especially useful when processing tables that are self-referencing, as in a hierarchy. An example of this would be an employee table that references the employee’s manager, who in turn is also an employee with their own manager, etc.

Example of a recursive common table expression:

Given the following table:

CREATE TABLE PARTLIST
    (PART CHAR(2)
    ,SUBPART CHAR(2)
    ,QUANTITY INTEGER
    );

. . . that is populated as follows:

PART    SUBPART    QUANTITY

00      01          5
00      05          3
01      02          2
01      03          3
01      04          4
01      06          3
02      05          7
02      06          6
03      07          6
04      08         10
04      09         11
05      10         10
05      11         10
06      12         10
06      13         10
07      14          8
07      12          8

Say you have a need to answer the question “what is the total quantity of each part required to build part ‘01’?”

This can be done with the following recursive common table expression:

WITH RPL (PART, SUBPART, QUANTITY) AS
    (SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
         FROM PARTLIST ROOT
         WHERE ROOT.PART = '01'
     UNION ALL
     SELECT PARENT.PART, CHILD.SUBPART,
            PARENT.QUANTITY*CHILD.QUANTITY
         FROM RPL PARENT, PARTLIST CHILD
         WHERE PARENT.SUBPART = CHILD.PART
    )
    SELECT PART, SUBPART, SUM(QUANTITY) AS TOTAL_QTY_USED
        FROM RPL
        GROUP BY PART, SUBPART
        ORDER BY PART, SUBPART;

The result of the query is shown below:

PART    SUBPART    TOTAL_QTY_USED

01      02           2
01      03           3
01      04           4
01      05          14
01      06          15
01      07          18
01      08          40
01      09          44
01      10         140
01      11         140
01      12         294
01      13         150
01      14         144