UPDATE SCHEMA and CATMAINT [DB2 9 for z/OS]

Welcome back to my blog as I continue our examination of the new features of DB2 9 for z/OS. Today we will look at the new UPDATE SCHEMA capability of the CATMAINT utility.

Have you ever wanted to make a global change to a schema, owner, creator, or VCAT name for your DB2 objects? Well, you can do that with CATMAINT in DB2 9 for z/OS using new UPDATE SCHEMA options.

There are three (3) new options added to CATMAINT, namely:

• SCHEMA: Owner, creator and schema names can be changed using this option.
• VCAT: Indexes, table spaces and storage groups can be altered to use a different ICF or VCATNAME using this option.
• OWNER: Ownership of objects can be changed to a role using this option.

To use any of these options you must be in DB2 9 NFM and have Install SYSADM authority.

How does it work? Well, let’s take a look at a few examples, starting with the SCHEMA option. To rename the owner, creator, and schema of database objects, plan, and packages, we will run CATMAINT specifying the SCHEMA SWITCH option. This process updates every owner, creator or schema name in the catalog and directory that matches the specified schema_name value. Importantly, all GRANTs that were made by or received by the original owner are changed to the new owner. Ownership of objects is not changed if the owner is a role.

So if we want to change OLDNAME to NEWNAME we can code the following CATMAINT job:

CATMAINT UPDATE
SCHEMA SWITCH(OLDNAME, NEWNAME)

You can change multiple names by repeating the SWITCH keyword, but you are not allowed to code the same name more than once.

Be aware though, when the schema name of an object is changed, any plans or packages that are dependent on the object are invalidated. If you do not REBIND those plans and packages an automatic REBIND will occur the next time you execute any of those programs.

Here is another example, this time for the VCAT option. To change the VCAT name that is used by storage groups or by index spaces and table spaces, we can run CATMAINT specifying the VCAT SWITCH option. This option is similar to using the ALTER TABLESPACE USING VCAT statement for changing the VCAT name. You need to move the data for the affected indexes or table spaces to the data set on the new catalog in a separate step.

So if we want to change OLDVCAT to NEWVCAT we can code the following CATMAINT job:

CATMAINT UPDATE
VCAT SWITCH(OLDVCAT, NEWVCAT)

You can change multiple VCAT names by repeating the SWITCH keyword, but you cannot specify the same name more than once. There are several restrictions to this option that you should research in the IBM manuals before attempting to switch VCAT names.

The final option is the OWNER option. It is used for changing the ownership of objects from a user to a role. Roles are new in DB2 9 and are associated with a TRUSTED CONTEXT. This will be the subject of a future blog posting here on the DB2portal blog – so keep an eye out for that one soon.

For example, if we want to switch ownership of objects for OWNER1, OWNER2 and OWNER3 to a role, we can run CATMAINT as follows:

CATMAINT UPDATE
OWNER FROM(OWNER1, OWNER2, OWNER3) TO ROLE

You must be running under a trusted context with a role to run this utility. The current role will become the owner. Privileges held on the object will be transferred from the original owner to the role.

A final caveat: be sure to create backups of your DB2 Catalog and DB2 Directory before running this CATMAINT to switch SCHEMA, VCAT, or OWNER.

Free Database Archiving Webinar

Attend a free webinar on database archiving on November 28, 2007 at 10:30 am CST.

The webinar, which I will be co-hosting with Bill Baker, will tackle the issue of skyrocketing data volumes in conjunction with increasing governmental regulations that impact the duration of data retention. These dual trends (among several others) conspire to make it difficult to ensure that your organization is in compliance regarding its operational data. As required data retention periods increase it becomes imperative that organizations develop a solid practice for archiving and managing business data from their online operational databases.

Now you may have read about database archiving either in my blogs (Long-Term Data Retention Drives Database Archiving, Data Management Today blog) or elsewhere, but here is a chance to attend a free presentation on the topic to learn all about the trends, troubles, and nuances involved in successfully archiving database data.

I hope you can find the time to attend!

BACKUP and RESTORE SYSTEM [DB2 9 for z/OS]

I am posting today’s blog entry from Athens, Greece as I participate in the European IDUG conference. Good thing I know how to use the Blogger site because when I log in over here in Greece the text on their site is all converted into Greek - and as I'm sure comes as no surprise to anyone, I don't understand Greek!

Anyway, today's post will be about the improvements IBM has made to the BACKUP SYSTEM and RESTORE SYSTEM utilities in DB2 9 for z/OS. And this will be the final entry in this series on Version 9 features discussing utility improvements… it will not be the last in the series on V9 improvements though, just the last one on the utilities.

Also, please keep in mind that these blog posts are meant to deliver a flavor of the new functionality in DB2 9 for z/OS. They will not cover every nuance and detail of what V9 has to offer. With that said, let’s dive into the enhancements to the BACKUP and RESTORE SYSTEM utilities.

Overview

As most of you surely know, BACKUP SYSTEM and RESTORE SYSTEM were are relatively new utilities, added to DB2 as of Version 8. They use disk volume FlashCopy backups and copypool z/OS DFSMShsm V1R5 constructs to copy and restore large volumes of DB2 data. In DB2 V9 these utilities are enhanced to use new functions available with z/OS V1R8 DFSMShsm.

Recovery of Individual Database Objects

In V9, backups produced by BACKUP SYSTEM (aka system level backups) can be used to recover individual table spaces or index spaces. This is helpful because previously you had to recover the entire system, and that is not always what is necessary.

When you wish to recover a subset of a system level backup you will use the RECOVER utility instead of RESTORE SYSTEM. Before your RECOVER jobs can use system level backups you must first set the SYSTEM_LEVEL_BACKUPS DSNZPARM option to YES. This can be set from the DSNTIP6 install panel. If you specify YES then your system-level backups will be considered in object level recoveries (along with your other image copy backups).

If you wish to use your system level backups for individual database object recoveries then you need to make sure that you are copying your indexes (specifying COPY YES).

Why would you want to use your system level backups in this way? Well, doing so should enable you to reduce the frequency with which you are taking conventional image copies. If you take a daily system level backup, then the database objects that you were also backing up on a daily basis may not be required. Of course, you cannot completely forgo all individual image copies because the system level backup timing may not conform to the timing needed for each object based on application requirements, and of course, image copies will still be needed after running utilities like LOAD REPLACE and REORG LOG NO to resolve copy pending situations.

Tape Support for BACKUP SYSTEM

DB2 V9 also delivers the ability for the BACKUP SYSTEM utility to copy the data directly to tape. The new parameters allowing this capability are the DUMP and DUMPONLY options.

The output of the DUMP or DUMPONLY is directed to a DFSMShsm dump class, which specifies the unit type the data will be directed to. Although IBM implemented this change to enable tape support, an SMS dump class is not restricted to tape.

Keep in mind that directing data to tape will have an impact on the speed of your restore. Restoring from tape will not be as fast as restoring from a FlashCopy made to disk. Of course, having your data on tape can help in terms of storage management, disaster recovery and off-site data storage, and long-term data retention. So be aware of these trade-offs before creating system level backups on tape.

Additionally, recognizing that copying data to tape can be time-consuming IBM has added a new keyword, FORCE, to enable a new backup to be started even if a previous DUMP has not yet completed. Of course, FORCE should not be used all the time - - only be used when it is very critical that a new backup be started.

Incremental FlashCopy

And finally, support for incremental copying has been added to FlashCopy. So now you can take a system level backup and then subsequent incremental system level backups. An incremental FlashCopy will copy only the tracks that have changed on the source volume since the last copy was taken. But unlike a typical incremental image copy, the previous content on the volume(s) will be replaced by the new content. That means there is no merging of incrementals required; essentially, the merge is part of the incremental FlashCopy.

I won’t go into all of the gory details here but this new functionality can greatly minimize I/O activity for system level backups.

Summary

So, to sum things up, the ability to work with system level backups becomes easier in V9 because you can recover individual table spaces and indexes from a system level backup without having to restore the entire backup, you can make system level backups directly to tape, and we get the ability to do incremental system level backups. All in all, some nice new features for BACKUP SYSTEM and RESTORE SYSTEM, wouldn't you say?