New Manual Covers DB2 10 for z/OS Security

Every few months, I visit IBM's DB2 product library and refresh all of my PDF manuals.

IBM was busy during the latter part of 2011. The majority of manuals have been updated since September, meaning I refreshed almost my entire DB2 10 manual library. Even if you're still on DB2 9 or DB2 8, you should check your own list, because these manuals have been refreshed as well.

In the course of my library upkeeping, I also found a new manual that you should be aware of: "DB2 10 for z/OS: Managing Security."

Since I haven't yet read this manual, I'll give you the roughest of outlines by citing some chapter titles:

Chapter 2. Managing access through authorization IDs and roles

Chapter 3. Managing access though RACF

Chapter 4. Managing access through row permissions and column masks

Chapter 5. Managing access through trusted context

Chapter 6. Managing access through data definition control

Chapter 7. Managing access through exit routines

Chapter 8. Protecting data through encryption and RACF

Chapter 9. Auditing access to DB2

 With the obvious interest customers have security and compliance issues, and given the many security-related enhancements in DB2 10, it's easy to understand why IBM came out with this new document. Diving into this information is one of my top priorities for the early new year. I'm really looking forward to reading this manual and learning more about DB2 10 for z/OS security.

DB2 10 Reduced Catalog Contention

For years DBAs have had to schedule times to perform DB2 object management tasks--like using DDL statements such as CREATE and ALTER or making program changes requiring BIND and REBIND--because of contention on the DB2 catalog. This contention is usually caused by running a dynamic SQL statement which takes catalog locks. DB2 object changes using DDL cannot be made while these dynamic SQL statements are running.

DB2 10, however, offers some new function to reduce contention. First, catalog links are eliminated, and catalog tables are converted from page-level locking to row-level locking. In addition, each table is put into a separate partition-by-growth table space. The advantage of using partition-by-growth table space is better space map management and improved availability by avoiding out of space conditions. A new partition is added to the table space as the previous partition fills up.

You'll benefit from this redesign when executing any of these DDL statements:

• CREATE TABLESPACE, TABLE, and INDEX
• ALTER TABLESPACE, TABLE, INDEX
• DROP TABLESPACE, TABLE, and INDEX
• CREATE VIEW, SYNONYM, and ALIAS
• DROP VIEW and SYNONYM, and ALIAS
• COMMENT ON and LABEL ON
• GRANT and REVOKE of table privileges
• RENAME TABLE
• RENAME INDEX
• ALTER VIEW

I'd like to hear about your experiences with the catalog redesign. Have you seen reduced deadlock timeouts? Has it allowed you to modify the way you schedule and implement DB2 object changes?

DB2 10 New Catalog Tables

After I wrote about the changes with existing catalog tables, a reader asked me for a list of new catalog tables. Luckily for me, my colleauge Steen Ramuseen used RC/Compare to generate a list of new tables for me. Steen, in fact, will be presenting “The DB2 10 Catalog: A Revolution” at IDUG North America 2011. He'll provide many more details about the dropped, changed and new catalog tables.

Autonomic Procedures
•    SYSIBM.SYSAUTOALERTS: Recommendations from autonomic procedures.
•    SYSIBM.SYSAUTOALERTS_OUT: AUX table for 2M CLOB column.
•    SYSIBM.SYSAUTORUNS_HIST: History from every execution of autonomic procedures.
•    SYSIBM.SYSAUTOALERTS_HISTOU: AUX table for 2M CLOB column.
•    SYSIBM.SYSAUTOTIMEWINDOWS: Time periods for execution of autonomic procedures.
•    SYSIBM.SYSTABLES_PROFILES: Profile associated with SYSTABLES (used by RUNSTATS and ADMIN_UTL_MONITOR).
•    SYSIBM.SYSPROFILE_TEXT: AUX for 1M CLOB PROFILE_TEXT column.

Audit and Permission
•    SYSIBM.SYSAUDITPOLICIES: Contains one row for each audit policy. SECADM users have the privilege to select/insert/update/delete from this table. Users with SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, SYSCTRL or SYSADM authority can select from this catalog table.
•    SYSIBM.SYSCONTROLS: Row permissions and column masking.
•    SYSIBM.SYSCONTROLS_DESC: AUX table for 2M BLOB column.
•    SYSIBM.SYSCONTROLS_RTXT: AUX table for 2M CLOB column.

Object DDL related
•    SYSIBM.SYSINDEXES_RTSECT: AUX for 1GB BLOB RTSECTION column.
•    SYSIBM.SYSINDEXES_TREE: AUX for 1GB BLOB PARSETREE column.
•    SYSIBM.SYSTRIGGERS_STMT: AUX for 2M CLOB STATEMENT column.
•    SYSIBM.SYSVIEWS_STMT: AUX for 2M CLOB STATEMENT column.
•    SYSIBM.SYSVIEWS_TREE: AUX for 1G BLOB PARSETREE column.
•    SYSIBM.SYSROUTINES_TREE: AUX for 1G BLOB PARSETREE column.
•    SYSIBM.SYSXMLTYPMOD: XML type modifiers and XML columns.
•    SYSIBM.SYSXMLTYPMSCHEMA: XML schema information.
•    SYSIBM.SYSPENDINGDDL: Objects which have pending DDL changes (e.g., DSSIZE, SEGSIZE etc.)
•    SYSIBM.SYSPENDINGDDLTEXT: AUX for 2M CLOB STATEMENT_TEXT column.
•    SYSIBM.SYSPENDINGOBJECTS: Specific objects for SYSPENDINGDDL.
 
Package and Statement related
•    SYSIBM.SYSPACKCOPY: Copies of packages.
•    SYSIBM.SYSPACKSTMT_STMT: AUX for 2M CLOB STATEMENT column (SYSPACKSTMT).
•    SYSIBM.SYSPACKSTMT_STMB: AUX for 2M BLOB STMTBLOB column (SYSPACKSTMT).
•    SYSIBM.SYSQUERY: Holds QUERIES (dynamic PLANMGMT).
•    SYSIBM.SYSQUERYOPTS: Optimization parameters/attributes for dynamic queries in SYSQUERY.
•    SYSIBM.SYSQUERY_AUX: AUX for 2M CLOB STMTTEXT column (SYSQUERY).
•    SYSIBM.SYSQUERYPLAN: OPTHINT information for SYSQUERY – including versioning/historic OPTHINTs.
 
Thanks again to the reader who contacted me and of course to Steen for compiling this information. If you haven't seen Steen present, I highly recommend attending his presentation if you're going to IDUG NA. Steen is one of the best speakers and most knowledgeable DB2 experts I know.

DB2 Catalog Changes

In the early years dynamic SQL was never used in a production environment. There were several reasons for this, but the biggest was catalog contention. The locks held in any user's dynamic ad hoc SQL statement could bring down a new version of a program being moved into production.

As dynamic SQL's use has grown with Java's emergence, customers continue to struggle with deadlock and timeout issues on the catalog. The continued contention caused by having multiple tables in the same table space led IBM to split up the tables into separate table spaces. IBM moved the tables into partition by growth (PBG) universal table spaces. PBG table space can be managed like a segmented table space, but it also provides the capability extend very large tables into partitions.

 During the final phase of migration (new function mode) of an existing DB2 subsystem to DB2 10, all new available DB2 10 features are made available. During this phase of the migration, some of the DB2 catalog tables will be moved into the new PBG table space. Here's the list:

• DSNDB06.SYSOBJ
• DSNDB06.SYSDBASE
• DSNDB06.SYSVIEWS
• DSNDB06.SYSPLAN
• DSNDB06.SYSPKAGE
• DSNDB06.SYSDBAUT
• DSNDB06.SYSGROUP
• DSNDB06.SYSALTER
• DSNDB06.SYSSEQ2
• DSNDB01.DBD01
• DSNDB01.SPT01

IBM also reduced locking by removing internal links. Now each table space uses row-level locking, and all tables are in reordered-row format.

It's important to be aware of these table spaces, because this change could impact your current utility jobs like ImageCopy and reorgs.

In a future DB2utor blog entry, I will cover some of the other catalog changes as well.