Database Administration
| < Day Day Up > |
| As with each new version, DB2 V8 offers new functionality that helps DBAs administer and manage their databases and subsystems. This release contains many enhancements to the DB2 objects that DBAs must manage including sequence objects, variable length index keys, expanded partitions, new types of partitioned indexes, new partition management, and materialized query tables (also known as automated summary tables). Also, index keys can comprise up to 2000 bytes ”so more data can be indexed using a single index. Each of these features delivers more functionality but also presents implementation and maintenance challenges. SequencesIdentity columns , added during the DB2 V6 refresh, can be useful, but there are numerous problems involved when trying to actually use them. The biggest problems come about when data must be loaded into these tables because you cannot control the identity values assigned. SEQUENCE objects resolve most of the problems with identity columns. A SEQUENCE object is a separate database object that generates sequential numbers . When a SEQUENCE object is created, it can be used by applications to "grab" a next sequential value for use in a table. Sequences are efficient and can be used by many users at the same time without causing performance problems. Multiple users can concurrently and efficiently access SEQUENCE objects because DB2 does not wait for a transaction to COMMIT before allowing the sequence to be incremented again by another transaction. Sample DDL for creating a SEQUENCE object follows : CREATE SEQUENCE ACTNO_SEQ AS SMALLINT START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; This SEQUENCE object can be used to generate sequential values in SQL statements, using sequence expressions. DB2 V8 supports two sequence expressions:
The following sample SQL uses a sequence expression to generate the next sequential value and uses that value in an INSERT statement: INSERT INTO DSN8810.ACT (ACTNO, ACTKWD, ACTDESC) VALUES (NEXT VALUE FOR ACTNO_SEQ, 'TEST', 'Test activity'); Sequence expressions are not limited to INSERT statements, but can be used in UPDATE and SELECT statements, too. PartitioningMajor changes to the way DB2 partitions data are introduced in V8. First of all, the partitioning limit keys now are defined in the table, instead of a partitioning index. In fact, no partitioning index is required. Furthermore, clustering and partitioning have been separated, so you can cluster on one group of columns and partition on another. Also, DB2 V8 expands partitioning support to allow for much-needed data growth. You can define up to 4,096 partitions per partitioned table space with DB2 V8. With online schema evolution, discussed later, making changes to partitioned table spaces is much easier. And a new type of partitioned index is introduced. Data Partitioned Secondary IndexesOne of the biggest problems DBAs face when they are managing large partitioned DB2 table spaces is contending with non-partitioned indexes. DB2 V8 introduces data partitioned secondary indexes, or DPSIs, to help resolve these problems. A DPSI is basically a partitioned NPI. Even though the index key for a DPSI is not the partitioning key, DB2 will manage the partitioning in the index such that entries are maintained in the same partition where the data is found in the partitioned table space. In other words, a DPSI will be partitioned by the same key ranges as the table, whereas an NPI is not partitioned at all. As of V8 another term for NPI is NPSI, or non-partitioned secondary index. So, with a DPSI the index will be partitioned based on the data rows. The number of parts in the index will be equal to the number of parts in the table space. This helps withpartition-based utility processing, because now DB2 utilities can process the DPSI partition at the same time it processes the table partition. But you will not want to change every NPI to a DPSI once you migrate to V8. Changing an NPI to a DPSI will likely cause queries to perform worse than before. This is so because each partition of a DPSI has its own index tree structure. So queries may have to examine multiple partitions of the DPSI as opposed to the single NPI it previously used. This can degrade performance. Online Schema EvolutionAnother useful administration feature of DB2 V8 is known as Schema Evolution. Today, there are many types of DB2 changes that require the DBA to DROP and then re- CREATE the object in order to implement the change. Schema evolution enables the DBA to make more types of changes to database objects using native DB2 features. For example, DBAs will be able to add and rotate partitions of partitioned table spaces and to expand the length of numeric and character columns using the ALTER statement. Basically, schema evolution provides more support for a variety of changes to be made directly using ALTER statements. An in-depth discussion of online schema evolution is provided in Chapter 7, "Database Change Management and Schema Evolution." Multi-Level SecurityThe new security features are interesting, too! DB2 V8 introduces multi-level security. With multilevel security (MLS) in DB2 V8 it becomes possible to support applications that need a more granular security scheme. For example, you might want to set up an authorization scenario such that employees can see their own data but no one else's. To complicate matters somewhat, you might also want each employee's immediate manager to be able to see his payroll information as well as all of his employee's data, and so on up through the org chart. Setting up such a security scheme is next to impossible with current DB2 versions, but it is straightforward using row level security in DB2 V8. DB2 V8 supports row-level security in conjunction with a security management product (like RACF). To activate this authorization mechanism, you will need to add a specially named column to act as the security label. The security label column is matched with the multilevel security hierarchy in the security manager. You can set up security hierarchies to be as simple, or as complex, as you need. To support MLS hierarchies, DB2 V8 requires several new RACF access control functions that are not available prior to V1R5 of z/OS. When row-level security is implemented, every user must be identified to RACF (or another security server with equivalent functionality) with a valid SECLABEL . Row-level security is then implemented by matching the SECLABEL of the data to the SECLABEL of the user. But, of course, there is additional detail that is needed to implement user row-level authorization properly in DB2. This detail can be found in Chapter 10, "DB2 Security and Authorization." Padded Variable Length IndexesPrior to Version 8, when indexing on a variable column, DB2 automatically pads the variable column out to its maximum size . So, for example, creating an index on a column defined as VARCHAR(50) will cause the index key to be padded out to the full 50 bytes. Padding very large variable columns can create a very large index with a lot of wasted space. DB2 V8 offers the capability to direct DB2 whether variable columns in an index should be padded or not using a new keyword in CREATE INDEX : PADDED or NOT PADDED . The specification is made at the index level ”so every variable column in the index will be either padded or not padded. When PADDED is specified, DB2 will create the index just as it did prior to V8 ”by padding all variable columns to their maximum size. When NOT PADDED is specified, DB2 will treat the columns as variable and you will be able to obtain index-only access because the length is stored in the index key. Support for Greater Log VolumeThe maximum number of archive log volumes recorded in the BSDS expands to 10,000 volumes per log copy from the previous limit of 1,000 volumes . The maximum number of active log data sets is also increased from 31 per log copy to 93. To obtain this increased number of log data sets you must re-size your BSDS. This is accomplished by running DSNJCNVB . DB2 V8 must be running in New Function Mode before you can modify the BSDS. NOTE Although BSDS conversion is optional, it is wise to convert it to take advantage of the expanded log volume support. Additionally, the maximum size of each log data set (both active and archive) can be up to 4 MB minus 1 CI. Although this increase is available in the base code for DB2 V8, it is also available to DB2 V6 and V7 via APAR PQ48126 . Additional V8 DBA ImprovementsThis section covered the highlights of this version's DBA improvements. But there are others. For example, you can create MQTs, or materialized query tables, to improve the performance of data warehousing queries. MQTs are essentially views where the data has been physically stored instead of virtually accessed. Other improvements include
|
| < Day Day Up > |
EAN: 2147483647
Pages: 388