Plan Program Authorization

A DB2 plan is used to set runtime parameters and security when executing applications on a local DB2 system. A plan specifies run-time options as well as one or more collections of program “packages” that the plan can execute.

The list of packages that a plan can use is defined in the package list parameter at the time the plan is bound. To avoid having to rebind the plan every time it needs to be authorized to execute a package, a collection name is used with a wild card package name to provide dynamic access to any package in the collection. For example you may allow employees to view payroll information but restrict them from updating it. They would use the READPAY PLAN that uses the collection (READONLY_PAYINFO with a program package called PAY001. The syntax for the BIND is:

            BIND PLAN (READPAY)

               PKLIST(READONLY_PAYINFO.PAY001)

 

To bind this plan so program PAY002 can be added to the READONLY_PAYINFO collection without the need to rebind it with the new package name, use this syntax:

 

            BIND PLAN (READPAY)

               PKLIST(READONLY_PAYINFO.*)

 

Now, as packages are added to the collection READONLY_PAYINFO, they are available to the plan READPAY.

Of course, if a package is inadvertently added to a collection, any authorized user could then execute the plan, which could lead to unauthorized uses of the application package. To protect against these types of exposures, a new bind and rebind plan option was introduced with DB2 11. This option serves essentially as a double check that's designed to ensure, as IBM puts it, "a valid program is authorized to execute the plan."

As is often the case when new features are introduced, plan program authorization isn't tightly integrated with DB2; therefore, some manual steps must be implemented. First, you must create the DB2 table, SYSIBM.DSNPROGAUTH. The DB2 sample library SDSNSAMP(DSNTIJSG) contains both DDL to create table SYSIBM.DSNPROGAUTH and sample DML to INSERT a row into the table.

The row inserted into SYSIBM.DSNPROGAUTH contains information to authorize a PLAN to be used for a given program package. To activate plan program authorization, you must BIND or REBIND a PLAN using the new parameter, PROGAUTH. Once enabled (the system default is DISABLE), DB2 will examine table SYSIBM.DSNPROGAUTH to find a row with combination of PLAN NAME and PACKAGE NAME.

Several new reason codes are available to help determine why program authorization may fail. Here's an example:

00F3003C The program authorization is enabled by specifying PROGAUTH option when the plan was bound. The MDC value for the program does not match the program authorized to use the plan. The request to allocate a plan to the program name is denied.

If the program name exists in SYSIBM.DSNPROGAUTH table, then correct the ROGMDCVAL and PROGMDCPAD values for the program. Otherwise, the program name must be added to the SYSIBM.DSNPROGAUTH table.

To find a list of plans that have enabled program authorization:

 

      SELECT  NAME, VALID, OPERATIVE,

                      CAST(QUALIFIER AS CHAR(10)) AS QUALIFIER,

                      RELBOUND, PROGAUTH, PLENTRIES

      FROM SYSIBM.SYSPLAN

      WHERE PROGAUTH = ‘E’

      WITH UR

 

Do you ever need to lock down individual packages so they can only be used by specific plans? Personally, I've never required this level of security, and I'd worry that it could be a maintenance nightmare. If DB2 customers are really demanding this feature, I'd expect IBM to soon add the capability to insert into the DSNPROGAUTH table through a grant statement or some form of external security package. What are your thoughts on this?

Static SQL Support a Key Feature of DB2 Accelerators V4.1

Last fall IBM announced DB2 Analytics Accelerator (DB2 Accelerators) for z/OS Version 4.1. In contrast to previous releases, which supported a limited SQL syntax (and only for dynamic SQL), V4.1 enables a wide array of queries with static SQL support. I've written frequently about DB2 Accelerators, and from what I've seen on recent customer visits, I believe that the enhancements in V4.1 are contributing to increased adoption of this product.

Here's a quick list of other new features:

·         Support for EBCDIC and Unicode in the same DB2 system and accelerator.

·         Support for multi-row FETCH

·         Improved scalability workload balancing through automated routing, based on workload balancing information, that evenly distributes the workload across the attached accelerators.

·         Improved high availability through automated workload balancing failover support. If, for any reason, one accelerator is unavailable, queries are automatically rerouted to available accelerators.

·         Increased number of replication sources per accelerator: It's now possible to replicate from up to 10 different subsystems into a shared accelerator.

·         Better performance for incremental update through Instrumentation Facility Interface (IFI) filtering, which is designed to lower MIPS utilization and improve performance because fewer log records need to be processed by the capture component.

·         Richer monitoring: The accelerator now provides expanded information for monitoring applications with the IFI. The IFI makes this data available to software vendors who can leverage this broader information to better inform customers about the usage, execution and concurrency of queries as they are processed in the accelerator.

·         Significant enhancements to the High Performance Storage Saver (HPSS) solution:

o    Support to update historical read-only record through a stored procedure;

o    Support to restore individual partitions without requiring any outages;

o    The capability to enforce read-only state on a partition used by HPSS; and,

o    Backup image maintenance can be simplified by marking HPSS partitions as "undeletable."

·         Simplified maintenance and lower cost of operation. V4.1 now offers an automated NZKit Install that facilitates the fully automatic installation and activation of Netezza kits, with no firmware or operating system updates required.

·         Support for DB2 for z/OS version 11.

If you’re using this technology, please share some of your experiences, pros and cons, in comments.

Using Arrays

Last week I discussed the new user-defined ARRAY data type in DB2 11. This is the first DB2 for z/OS release to support arrays, although DB2 for Linux, UNIX and Windows has supported arrays since DB2 V9.5 (October 2007).

The DB2 11 for z/OS SQL Reference Guide lists and defines the SQL built-in functions that can be used to assign values as well as retrieve first, last and next values. It also includes numerous examples that show how to assign values to an array. I'll cite two here:

 

            Example 1: Suppose that the array variable RECENT_CALLS has the array type

            PHONENUMBERS. Assign an array of fixed numbers to RECENT_CALLS.

 

            SET RECENT_CALLS = ARRAY[9055553907, 4165554213, 4085553678];

 

            Example 2: Suppose that the array variable DEPT_PHONES has the array type             PHONENUMBERS. Assign array phone numbers that are retrieved from the  DEPARTMENT_INFO table to DEPT_PHONES.

            SET DEPT_PHONES =

              ARRAY[SELECT DECIMAL(AREA_CODE CONCAT ’555’ CONCAT EXTENSION,16)

                            FROM DEPARTMENT_INFO

                            WHERE DEPTID = 624];

 

Java developers should review the DB2 11 for z/OS Application Programming and Reference for Java manual. As you'd expect from the title, this manual provides examples usage in Java. The DB2 11 z/OS Application Programming and SQL Guide covers other languages like C, COBOL and REXX.

Another resource on arrays is the IBM Redbook, "DB2 11 for z/OS Technical Overview." Included in this publication is the following example that illustrates how to use the ARRAY_AGG function to return an array of employee phone numbers:

 

                CREATE TYPE PHONELIST AS CHAR(4) ARRAY[];

                CREATE FUNCTION PHONELIST_UDF (LOWSAL DECIMAL(9,2))

                    RETURNS PHONELIST

                    LANGUAGE SQL

                    CONTAINS SQL

                    NO EXTERNAL ACTION

                    RETURN

                    (SELECT ARRAY_AGG(PHONENO ORDER BY SALARY)
                     FROM DSN81110.EMP WHERE SALARY > LOWSAL)

 

Do you have a creative use for arrays? Please share your ideas in comments.

Array Support in DB2 11

DB2 11 brings many new SQL processing enhancements that are designed to simplify application coding. One such enhancement is support for arrays. An array is a user-defined data type that consists of an ordered set of elements of a single built-in data type. Elements can be accessed and modified by their index position. An array type is created with an SQL CREATE TYPE (array) statement. An array type can be used as a parameter of a procedure and as a variable in an SQL procedure.

Here's the syntax for creating a user-defined array:

        >>-CREATE--TYPE--array-type-name--AS--| built-in-type |--------->

 

                     .-2147483647-------.      

        >--ARRAY--[--+------------------+--]---------------------------><

                     +-integer-constant-+      

              '-| data-type2 |---'  

There are two array types: ordinary or associative. An ordinary array type is defined using the ARRAY(integer-constant) syntax. The maximum cardinality is based on the value integer-constant. The value must be an integer between 0 and positive 2147483647. The maximum value is the default size (2147483647).

Here's an example of an ordinary array user-defined type. The company-name element can be up to 100 characters in length, while the company-name array can contain up to 75 elements.

            CREATE TYPE company-name AS VARCHAR(100) ARRAY(75)

ARRAY(data-type2) is an associative array that's indexed by data-type2. The data type must be either INTEGER or VARCHAR, and the value assigned to the index must be of the same data type. Each index is unique, and as a new index is assigned, the cardinality is incremented by 1. The maximum cardinality of an array is limited by the total amount of memory available to DB2 applications.

For more information on ARRAY TYPE, see the DB2 11 for z/OS SQL Reference Guide.

Next week I'll explain how user-defined data types (ARRAY TYPE) can be used.