IBM continues to deliver enhancements that make the DB2 for i database easy to manage while at the same time meeting the security and scalability requirements that your business dictates.

The DB2 for i 7.2 release is chock full of enhancements that take its legendary ease of use and security to new levels. In this article, you'll learn how to simplify development with procedural SQL advancements, streamline management with new DB2 capabilities, and tighten data security with row and column access controls.

Protecting Data

Several high-profile data breaches have many companies pressing their IT organizations to limit and control access to sensitive data. As a result, there's been a greater emphasis on ensuring that security personnel don't have too much power. A separation of duties approach is often implemented to prevent a security administrator from having the ability to both control data access and perform data access. IBM has delivered a new DB2 security administration function usage, QIBM_DB_SECADM, so that a security administrator can be limited to just the duty of controlling data access. This control is implemented by using the CHGFCNUSG (Change Function Usage) command as shown in the following example to add users as a DB2 security administrator.

CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SECUSR) USAGE(*ALLOWED)

Once this command has been executed, the specified user profile (SECUSR) will be able to grant other users read access to DB2 tables but will not be able to read the content of any DB2 table. DB2 security administrators can grant data access only to others, not themselves.

This separation of duties enhancement helps with controlling access at the object level, but it isn't helpful for users wanting to grow data access at the row and column level. In prior releases, IBM i really only provided controls for an all-or-nothing approach; either a user had no access to the values in a row/column or had access to all of the values in a row/column. Some tried implementing these more granular controls with SQL views and application logic, but they found these user-defined solutions to be costly to maintain and easy to bypass.

Those limitations are a thing of the past with the new DB2 Row and Column Access Control (RCAC) support that's available with the no-charge IBM Advanced Data Security feature in the IBM i 7.2 release.

RCAC is an additional layer of security that complements table-level authorizations that are already in place in your database. This additional layer of security is implemented with row permission or column mask rules. Only those users who have been designated as a DB2 Security Administrator with the QIBM_DB_SECADM function usage are able to add row permissions and column masks to a table (or physical file).

Row permissions enable you to define a rule that will determine whether a user is authorized to access a row within a table. This rule is checked every time a row is accessed, regardless if the row is accessed with SQL or non-SQL interfaces. A row permission rule is applied only after the table-level authorization has been first verified.

The following SQL statements demonstrate how to create and activate a row permission. In this example, the permission is defined to ensure that a patient can access only their own patient data, physicians can view data only for their patients, and members of the accounting team can access data for all patients. If the user accessing rows in the patient table doesn't meet any of the three criteria, then the row is not returned for access.

CREATE PERMISSION access_to_row ON patient

FOR ROWS WHERE

 ( VERIFY_GROUP_FOR_USER(SESSION_USER, ’PATIENT’) = 1

    AND patient.userid = SESSION_USER )

OR

   ( VERIFY_GROUP_FOR_USER (SESSION_USER, ’PCP’) = 1

    AND patient.pcp_id = SESSION_USER )

OR

 ( VERIFY_GROUP_FOR_USER (SESSION_USER, ’ACCOUNTING’) = 1 )

ENFORCED FOR ALL ACCESS ENABLE;

ALTER TABLE patient ACTIVATE ROW ACCESS CONTROL;

This permission example demonstrates that column values (userid and pcp_id) and function calls can be utilized in the coding of a row permission. The VERIFY_GROUP_FOR_USER function is a built-in system function that can be used to determine if the current user (e.g., SESSION_USER) belongs to an IBM i group profile or matches a user profile value. The VERIFY_GROUP_FOR_USER function returns a value of 1 if the input user profile matches the specified group or user profile values.

The creation and activation of a column mask has a similar implementation as you can see in the following example. This mask definition returns only the actual patient ID value (pid) for users that belong to the Patient group profile. The ELSE leg of the CASE expression returns a masked version of the patient ID for all other users.

CREATE MASK pid_mask ON patient FOR

COLUMN pid RETURN

   CASE

     WHEN

       VERIFY_GROUP_FOR_USER(SESSION_USER,'PATIENT') = 1

         THEN pid

       ELSE

         'XXX XXX ' || SUBSTR(pid, 8, 3)

   END

ENABLE;

ALTER TABLE patient ACTIVATE COLUMN ACCESS CONTROL;

Simplified Development

The usage of stored procedures and user-defined functions on IBM i is on the rise, so IBM continues to enhance its procedural SQL support. One of those new DB2 7.2 capabilities allows you to define default values for function parameters as shown in the following function definition. This new capability for user-defined functions is a continuation of the parameter default support for stored procedures delivered in the 7.1 release.

CREATE FUNCTION Chk_Coverage(

   DrugID CHAR(8), Refils INT DEFAULT 0,

   OrderType CHAR(1) DEFAULT 'M')

LANGUAGE SQL

BEGIN

…

END            

The Chk_Coverage function definition defines default values for the second and third parameters. These default values enable the function to be called with the following invocations: Chk_Coverage('FLTC0001') and Chk_Coverage('FLTC0001',OrderType=>'S'). Default values would be used for the second and third parameters on the first invocation and only for the second parameter on the second invocation. Default parameter values make it simple to enhance the capabilities of a function with new parameters without requiring you to change all of the applications that use the function.

Obfuscation is another 7.1 enhancement that's been extended in the IBM i 7.2 release with new support for SQL triggers. Software vendors are most interested in obfuscation because it allows them to protect intellectual assets that they have created with procedural SQL. Without obfuscation, the source code for SQL procedures, functions, and triggers can be retrieved using DB2 catalog views or system commands. Obfuscation enables SQL procedural objects to be created in a way that the source code is masked (e.g., CREATE TRIGGER UPDATETRIG WRAPPED QSQ07020 aacxW8pT1…), making it quite difficult for users to access the original source code. The obfuscation of SQL triggers is accomplished by invoking either the WRAP function to generate an obfuscated CREATE TRIGGER SQL statement that can be executed as part of an installation script or the CREATE_WRAPPED stored procedure to create an obfuscated SQL trigger object.

AUTONOMOUS is a new stored procedure attribute that makes it easier to seamlessly add stored procedure calls into an existing application. Normally, COMMIT (and ROLLBACK) statements performed by a stored procedure will commit database changes made by the stored procedure as well as any outstanding database changes made by the stored procedure invoker. If a stored procedure didn't commit its database changes, any Rollback statement issued by the invoker can undo the changes made by the procedure. Creating an autonomous stored procedure enables the data changes to be committed independently of the program that invokes the stored procedure. In the autonomous update_agency procedure below, the UPDATE and INSERT statements performed by the procedure will automatically be committed by DB2 as long as no errors are encountered. Any commit or rollback operations performed by invokers of the update_agency procedure will not affect the update and insert operations done by this autonomous procedure. Changes made by an autonomous stored procedure are completely independent of the changes made by the invoking application.

CREATE PROCEDURE update_agency(IN agencyVID INTEGER,

IN agencyNUM INTEGER, IN agencyID INTEGER, IN agentNID)
LANGUAGE SQL

AUTONOMOUS

BEGIN

   UPDATE agency

   SET agency_vid=agencyVID, agency_num=agencyNUM,

       agent_NID=agentNID, updated_date=CURRENT_TIMESTAMP

   WHERE agency_ID = agencyID;

   INSERT INTO agency_log

     VALUES(USER, CURRENT_TIMESTAMP,

                   agencyVID, agencyNUM, agentNID);
END                                                

If you're developing SQL procedural objects, you'll also find debugging of these SQL objects easier on 7.2 with the ability to perform single-step execution on the SQL source debug view and simpler evaluation of SQL variable values.

The timestamp precision support is an enhancement that can be used to simplify your table definitions and potentially reduce storage usage. On previous releases, all timestamp values were stored with a precision down to the microsecond level (e.g., '2014-04-20 15:12:11.129897'). Some applications may not require this sub-second precision, so essentially the extra six digits of precision was wasting 3 bytes of storage in every row. The 7.2 release provides easy control of the timestamp precision value as demonstrated in the following CREATE TABLE statement.

CREATE TABLE tstab(orderTS TIMESTAMP(0), orderManufacturedTS TIMESTAMP(12))

As you can see in this example, not only can timestamp sub-second precision be eliminated, it can also be increased up to 12 digits to more accurately capture timestamp values on today's faster servers.

Streamlined SQL Management

With each release, IBM makes it easier to manage and administer your DB2 databases with SQL statements and SQL-based interfaces like System i Navigator, so it's no surprise to see that pattern continue with IBM i 7.2. A new SQL TRUNCATE statement now allows you to quickly delete rows from a DB2 table. While this is similar to the function provided by the Clear Physical File Member (CLRPFM) command, the TRUNCATE statement also includes advanced controls, such as the ability to control the behavior of identity columns and storage reuse. Here's an example of a TRUNCATE statement that deletes all of the rows from the shipments table and dictates that identity column values for the table should be restarted from the starting value as new shipments are added to the table after the truncate operation.

TRUNCATE shipments RESTART IDENTITY

The Navigator client has been expanded to track operating system limits such as IFS and spool file limits from the DB2 Health Center interface. In a similar fashion, the DB2 Catalog has been expanded to include a set of general system views and functions known as DB2 for i Services that make it quite simple to access operating system metadata and metrics with SQL. This simplicity is demonstrated in the examples in Figure 1. The first query returns a list of the user profiles that have been authorized to the new QIBM_DB_SECADM function usage ID. While the second query returns a list of the active jobs that are consuming the most temporary storage on the system, the SYSTMPSTG catalog view leverages the temporary storage management enhancements delivered in the IBM i 7.2 release.

Figure 1: DB2 for i Services Example

SQL performance analysis and tuning is one of the most common tasks performed with the System i Navigator client. These activities should receive a boost from the new metrics and controls that are provided by the SQL Plan Cache Properties interface shown in Figure 2.

Figure 2: SQL Plan Cache Properties Interface

Enhanced Performance and Capabilities

Speaking of performance, the DB2 for i 7.2 release provides help in this area as well. Many shops are looking for ways to improve application scalability as databases get bigger and the number of users increase. Larger databases will benefit from the DB2 7.2 enhancements that increase the maximum size of an index object to 1.7 terabytes, strengthen reuse-deleted-records processing with VLDB awareness, and enhance storage utilization for variable-length and LOB columns. This set of enhancements has also been made available for the IBM i 7.1 release through recent Technology Refreshes. IBM will be adding new DB2 capabilities after the GA of 7.2, so make sure that you check the DB2 Technology Update Wiki on a regular basis.

The scalability of SQL workloads will benefit from the SQL Query Engine's (SQE) enhanced I/O costing algorithms and IN predicate-processing advancements. The enhanced I/O costing will allow SQE to more efficiently exploit enhanced I/O devices such as SSDs. DB2's In-Memory database support is now SQL-friendly with the new KEEP IN MEMORY clause that can be specified on SQL table and index definitions. In the prior release, the Change Physical File (CHGPF) and Change Logical File (CHGLF) commands had to be used to exploit the in-memory database capabilities for SQL objects. That's no longer the case with the DB2 7.2 release, as this CREATE INDEX demonstrates:

   CREATE INDEX important_index

     ON important_table(columnX, columnY)

   KEEP IN MEMORY YES

Multiple Benefits

The DB2 for i 7.2 release provides enhancements that should simplify your everyday activities regardless of your role—developer, database engineer, system administrator, or security officer. IBM continues to deliver enhancements that make the DB2 for i database easy to manage while at the same time meeting the security and scalability requirements that your business dictates.