In this article, enhancements related to storage management for DB2 for z/OS are discussed. Industry wide, many database products have been incorporating storage administration capabilities to manage storage efficiently and achieve the desired database performance. Engineered systems with database servers and dedicated storage suggest how critical data placement is to the overall database performance. This article will discuss the storage opportunities available to you when migrating to DB2 10 for z/OS from DB2 8 or DB2 9.
DB2 10 for z/OS Storage Management Subsystem (SMS)
This section provides some basic SMS concepts. The SMS component of DB2 10 for z/OS Data Facility Storage Management Subsystem (DFSMS) lets you manage storage based on certain policies that affect the availability, performance, manageability, and security of the data. Some of the key benefits of SMS are listed in the sidebar for you to review. Storage administrators transform these storage policies into Automatic Class Selection (ACS) routines.
Storage management of data sets is handled with the help of following SMS classes.
- Data class: Defines the data set definition attributes that you can specify on the JCL DD statement.
- Storage class: Defines the availability and performance characteristics for a data set.
- Management class: Defines the managing requirements like backup, migration, and retention of a data set.
When a data set is assigned with a storage class, the data set becomes an SMS-managed data set.
Storage administrators group volumes into storage groups, and unlike DB2 storage group objects, no two SMS storage groups can contain the same volume. SMS constructs can either be specified explicitly while creating the data set or left to be assigned by ACS routines. The only SMS construct that you cannot specify explicitly while creating a data set is an SMS storage group. ACS routines assign data sets with one class of each type, and possibly one or more volume storage groups. You can group storage groups to form Copy pools, which is used to perform DB2 system-level backups.
Another component of SMS is Interactive Storage Management Facility (ISMF), which provides the ISMF dialogs for the storage administration, and general display of SMS configurations.
Exploiting SMS in DB2 10 for z/OS
Historically, DB2 database administrators carefully managed the data and the database performance by assigning individual table spaces and indexes to volumes hand-picked according to their business needs. Database administrators had to be aware of the storage hardware in order to achieve the desired availability or performance. DB2-managed table spaces offered several benefits, including how DB2 automatically creates and deletes the underlying data sets during the execution of DDLs as well as utilities.
User-managed data sets were required only in cases where additional options were to be specified for the LDS data sets. As the data grew, database administrators started leveraging DB2 10 for z/OS SMS to manage their data effectively and efficiently. Assigning SMS classes from ACS routines offered database administrators some relief in placing and managing DB2 data sets, but it offered little freedom while doing so.
Since the SMS classes can be specified in the
DEFINE CLUSTER command of the IDCAMS utility,
database administrators used to prefer user-managed table spaces when they
would like to control the storage management. Starting with DB2 9, SMS
classes could be specified on
statements, which simplified storage allocation and administration.
For DB2-managed data sets before DB2 9, ACS routines had to assign the SMS classes for DB2 data sets, and DB2 storage groups were created by specifying ‘*’ in the VOLUMES clause. Both techniques are shown in Listing 1.
Listing 1. Creating storage group when data sets are system-managed
-- Pre version 9 syntax, letting ACS routines to assign the SMS classes CREATE STOGROUP SMSSG VOLUMES("*") VCAT TESTCAT ; -- For DB2 9 or above, specify SMS classes on DB2 DDL CREATE STOGROUP V9SG VOLUMES("*") DATACLASS(DB2VSAM) MGMTCLASS(TESTDB2) STORCLAS(TESTDB2)
If you are upgrading to DB2 10 for z/OS from Version 8, you must be in DB2 10 new-function mode to use DB2 9 features such as the one outlined previously in Listing 1.
It should be noted that even though you specify VOLSERs on the VOLUMES parameter of the DB2 storage groups, ACS routines can override it and create the underlying SMS-managed data sets. In situations where you want to place the data specifically on one or more of the SMS-managed volumes, enable the Guaranteed Space attribute in the respective storage class. In general, storage administrators do not prefer to enable Guaranteed Space, as it takes away the benefits of SMS.
There are also situations when SMS-managed DB2 data sets are mandatory in DB2. For example, in order to create data sets larger than 4GB, they should be SMS-managed and assigned a data class that is enabled for Extended Addressability (EA). Listing 2 shows a typical error that will occur when you try to create a table space partition larger than 4 GB without enabling EA.
Listing 2. Error when creating larger data set without EA
CREATE TABLESPACE BIGTS NUMPARTS 2 DSSIZE 8 GB IN BALATEST ; ---------+---------+---------+---------+---------+---------+---------+---------+ DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00D70008, TYPE OF RESOURCE 00000220, AND RESOURCE NAME DDS1923.DSNDBC.BALATEST.BIGTS.I0001.A001
Extended Addressability requires Extended Format data sets on SMS-managed volumes. Extended Format data sets also let you strip data across eligible volumes when the Sustained Data Rate (SDR) attribute in the Storage Class is set to a non-zero value. Data striping helps improve the performance of Sequential I/O, and hence, you should consider striping active log data sets. Starting with DB2 9, archive logs are written using the Basic Sequential Access Method (BSAM), instead of Basic Direct Access Method (BDAM) used in previous versions, to exploit DFSMS striping.
SMS-managed data sets also let you go past the maximum of 255 extents, which is something you could adopt for the sake of availability. Having too many extents causes performance degradation. Instead, it is recommended to adopt the sliding scale algorithm for secondary extents, introduced in DB2 8.
SMS requirement for DB2 10 installation
If you are already on DB2 9, you are probably familiar with the SMS-related enhancements highlighted in the previous section. DB2 10 for z/OS requires an SMS environment for its DB2 catalog and directory table spaces. The Data class that you define for the DB2 system data sets must be defined with Extended Format and Extended Addressability (EA) enabled.
You can optionally set up ACS routines to assign the SMS classes to these data sets. Based on your storage estimation, as detailed in chapter 2 of the DB2 for z/OS Installation Guide, consider adding as many DASD volumes to the storage group that will be assigned by the storage class. Since DB2 system table spaces are very critical for the functioning of DB2, consider assigning dedicated volumes in the storage group. DB2 9 moved the Real Time Statistics tables to catalog. Hence, if you are migrating from DB2 8, you should take that into account as well. Figure 1 shows the portion of the DSNTIPA2 panel where you can enter the SMS classes during the execution of the DB2 10 installation CLIST.
Figure 1. DSNTIPA2 panel to enter SMS classes
The installation CLIST saves the values you enter on the panel in an output member, and then generates migration jobs at the end of CLIST execution. Hence, the SMS environment should be set up before you actually submit the DSNTIJTC job that executes the CATMAINT utility to upgrade the catalog to DB2 10 format.
The DSNTIJTC job will fail if the SMS environment was not properly configured. The DSN6SPRM macro in the DSNTIJUZ job contains the following six parameters: CATDDACL, CATDMGCL, CATDSTCL, CATXDACL, CATXMGCL, and CATXSTCL. They represent the SMS classes you entered on the panel DSNTIPA2. Alternatively, you can leave these parameters blank and control SMS class allocation from the ACS routines.
IBM delivers an installation job called DSNTIJSS in the hlq.SDSNSAMP DB2 library to set up the required SMS environment. Since it is very unlikely for DB2 database administrators to configure SMS classes, this will be a task that you must request your storage administrators to perform ahead of your upgrade.
For a new installation of DB2, all of the catalog and directory table spaces will get created on the SMS-managed volumes according to the way you configure the SMS environment.
If you are migrating your system to DB2 10, the SMS environment will be used for the new catalog and directory table spaces created by the DSNTIJEN job. After the upgrade to DB2 10, the existing catalog and directory objects will continue to remain on the same non-SMS volumes that they were on before the upgrade. They will be converted into SMS-managed during the next reorganization. This article also will help you to perform the reorganization of catalog and directory objects.
Migrating DB2 data sets from non-SMS to SMS volumes
There is more than one method available for you to convert the non-SMS managed data sets to SMS managed ones. The DFSMSdss CONVERTV function offers the fastest way to convert the non-SMS managed volume to be SMS-managed. If you do not want the old DASD volumes to be included in SMS storage group for any reason, you can use the DFSMSdss COPY function to copy either individual data sets or the entire volume to a SMS-managed volume.
If you prefer to stick to DB2-aware methods, the DB2 REORG utility can be deployed to do the task. With REORG SHRLEVEL REFERENCE or CHANGE, you can actually complete the data sets migration without affecting availability for most of the activity duration. Prior to DB2 10, you could not have performed online reorganization on some DSNDB06 table spaces.
You can run the REORG utility on the catalog table spaces and directory table spaces except for DSNDB01.SYSUTILX. Since REORG on DSNDB01.SYSUTILX is not allowed, you can use DFSMSdss functions like COPY, CONVERTV, or DUMP/RESTORE to move the data sets to an SMS-managed volume. Listing 3 shows a JCL step that migrates the SYSUTILX data sets using the DFSMSdss COPY command.
Listing 3. DFSMSdss COPY for moving data sets
//COPY01 EXEC PGM=ADRDSSU //SYSPRINT DD SYSOUT=* //SYSIN DD * COPY DATASET(INCLUDE(DSNTCAT.DSNDBC.DSNDB01.DSNLLX01.I0001.A001,- DSNTCAT.DSNDBC.DSNDB01.DSNLLX02.I0001.A001 , - DSNTCAT.DSNDBC.DSNDB01.SYSLGRNX.I0001.A001 )) LOGINDYNAM(DSNTV1) - STORCLAS(DSNTS1) MGMTCLAS(DSNTS1) BYPASSACS SPHERE DELETE CATALOG /*
The example in Listing 3 assigns SMS classes explicitly and bypasses the execution of ACS routines. Alternatively, you can skip mentioning SMS classes on the COPY command and leave the assignment of SMS classes to the ACS routines. For more details on using DFSMSdss methods, you can refer to the DFSMSdss Storage Administration Guide that is applicable to your z/OS release.
Reorganizing DB2 catalog and directory
In this section, you will learn how to perform the reorganization of catalog and directory table spaces, which can help you to migrate the DB2 data sets from the non-SMS managed to SMS-managed DASD volumes.
IBM has restructured the DB2 catalog extensively to avoid lock contention. There are 95 catalog table spaces in DB2 10 new-function mode as compared to 28 in DB2 9. For example, DSNDB06.SYSDBASE in DB2 9 has been replaced with 13 individual table spaces of partition by growth type.
While enabling DB2 10 new-function mode, the DSNTIJEN job takes care of this catalog conversion. To completely convert the catalog and directory to be SMS-managed, you need to reorganize only the old table spaces that are not replaced in DB2 10 new-function mode. Under the Download section, you can find the list of such table spaces, and by reorganizing these table spaces you will have your catalog and directory completely converted to DB2 SMS managed.
If you wish, you can use the DFSMSdss or DFSMShsm techniques for all the DB2 catalog and directory data sets. On the other hand, the reorganization of catalog and directory table spaces can also be carried out periodically to improve the performance of queries against the catalog tables, and reclaim the unused space. It is also a highly recommended activity before the catalog migration, or as in this case, to modify the storage attributes of DB2-managed table spaces. You can exploit the INDREFLIMIT or OFFPOSLIMIT keywords on the REORG utility to conditionally execute the reorganization, just the way you would do for user table spaces.
Listing 4 shows an example of executing REORG to check if there is a need for reorganization. Since the verdict is dependent on the statistics, it is advisable to collect the statistics ahead of the reorganization.
Listing 4. Checking for a need for REORG
-- Job step to execute a REORG with INDREFLIMIT //STEP01 EXEC PGM=DSNUTILB,REGION=0M,TIME=1440, // PARM='ssid,XXXX.REORGDBASE' //STEPLIB DD DSN=DB2.V10.SDSNLOAD,DISP=SHR //SYSIN DD * RUNSTATS TABLESPACE DSNDB06.SYSTSDBA SHRLEVEL CHANGE REORG TABLESPACE DSNDB06.SYSTSDBA SHRLEVEL NONE LOG NO INDREFLIMIT 20 REPORTONLY //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* -- Portion of the SYSOUT containing the result of REORG DSNU050I 003 06:25:30.63 DSNUGUTC - REORG TABLESPACE DSNDB06.SYSTSDBA SHRLEVEL NONE LOG NO INDREFLIMIT 20 DSNU287I - 003 06:13:24.65 DSNURLIM - REORG TABLESPACE DSNDB06.SYSTSDBA INDREFLIMIT SYSTABLEPART ROWS DBNAME .TSNAME PART CARD FARINDREF NEARINDREF STATSTIME DSNDB06 .SYSTSDBA 1 57 0 0 2013-01-03-06.13.24 DSNU289I - 003 06:13:24.65 DSNURLIM - NO REORG LIMITS HAVE BEEN MET
Copying DSNDB01 and DSNDB06
You should take image copies of the catalog and directory table spaces ahead of the reorganization. Should you face any problem while reorganizing, you can recover one or more table spaces using the image copies you had taken. IBM supplies a job called DSNTIJIC in hlq.SDSNSAMP DB2 library, which you can use to perform the catalog and directory image copies.
If you are building your own job to perform the image copy, consider the following points.
- DSNDB01.SYSUTILX, DSNDB01.SYSLGRNX, DSNDB06.SYSCOPY table spaces have to be mentioned in separate COPY statements.
- DSNDB06.SYSCOPY should be the last table space copied, otherwise SYSCOPY will not have the image copy records of other table spaces.
- DSNDB01.SYSLGRNX should be the last but one to be copied.
- If using LIST, table spaces of DSNDB01 and DSNDB06 should be explicitly included.
Executing the reorganization
You can perform either offline or online reorganization depending upon availability requirements. If you do an offline reorganization, make sure you do not include the REUSE keyword to migrate the data sets to SMS-managed volumes. As there are 95 table spaces in the DB2 10 new-function mode, you can consider using TEMPLATE and the LISTDEF statements instead of coding as many JCL steps to perform the reorganization of these table spaces. You can generate a list containing the table spaces of DSNDB01 and DSNDB06 databases, omitting the DSNDB01.SYSUTILX.
Listing 5 shows an SQL to generate the LISTDEF statement, including the table spaces of DSNDB06 and DSNDB01 databases for a reorganization.
Listing 5. LISTDEF statement generation
SELECT 'LISTDEF CATLDIR ' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB06.'||NAME FROM SYSIBM.SYSTABLESPACE WHERE DBNAME ='DSNDB06' UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.DBD01' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SPT01' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SCT02' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SYSLGRNX' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SYSDBDXA' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SYSSPUXA' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ' INCLUDE TABLESPACE DSNDB01.SYSSPUXB' FROM SYSIBM.SYSDUMMY1 ;
Listing 6 shows the REORG JCL step. The DD statement SYSLISTD contains the LISTDEF statement generated by running the SQL shown previously in Listing 5, and removing extraneous lines other than the SQL output.
Listing 6. REORG job step
//* //STEP01 EXEC PGM=DSNUTILB,REGION=0M,TIME=1440, // PARM='ssid,XXXXX.REORGCAT' //STEPLIB DD DSN=DB2.V10.SDSNLOAD,DISP=SHR //SYSLISTD DD DISP=SHR,DSN=hlq.CATDIR.REORG.LIST //SYSIN DD * TEMPLATE SREC DSN(hlq.DSN2.&DB..&TS..SYSREC) UNIT SYSDA DISP(NEW,DELETE,CATLG) SPACE (50,50) CYL TEMPLATE COPY DSN(hlq.DSN2.&DB..&TS..REORGED.ICOPY) UNIT SYSDA DISP(NEW,CATLG,CATLG) SPACE (50,50) CYL REORG TABLESPACE LIST CATLDIR SHRLEVEL REFERENCE UNLDDN(SREC) COPYDDN(COPY) /* //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //*
For maximum availability while reorganizing, create a mapping table and
update the REORG statement in the JCL shown in Listing
REORG TABLESPACE LIST CATLDIR SHRLEVEL CHANGE MAPPINGTABLE
DBA.MAPPINGTABLE UNLDDN(SREC) COPYDDN(COPY).
DB2 10 has a new option called FORCE on the REORG utility to force the
blocking claimers, a feature that you can use if required.
You should consider collecting statistics after the reorganization of catalog table spaces in order to reflect the reorganized catalog data. Since statistics on LOB table spaces are not relevant for access path selection, you can leave out catalog LOB table spaces while collecting statistics. However, you can collect LOB statistics to determine whether a reorganization will be required.
- DB2 10 catalog and directories can remain on non-SMS managed volumes even after migrating to new-function mode. That means you do not have to force yourself to perform the SMS migration during the new-function mode migration, or before you stabilize your upgraded DB2 environment.
- SHRLEVEL REFERENCE is a convenient method that allows the restart at any phase.
- When the SMS environment is set up, the user IDs of the DB2 address spaces (started tasks) should be granted access to use the SMS classes.
- When you run the online REORG on the table spaces from previous DB2 versions, create the shadow data sets using the IDCAMS DEFINE command. Sample IDCAMS statements are available in the hlq.SDSNSAMP(DSNTIJIN).
- Fast switching is not allowed on DSNDB01 and DSNDB06 table spaces. Hence there will be no change in the underlying data set name even if you perform SHRLEVEL REFERENCE or CHANGE reorganization.
- You should be familiar with the recovery process of catalog and directory table spaces. After the image of the catalog and directory table spaces is copied, create a consistency point using the QUIESCE utility. During or after reorganizing, if you encounter a problem with a set of catalog table spaces, you do not need to recover all of the catalog table spaces. Similar to copying the catalog table spaces, you must take care to recover table spaces in a particular order. Image copies created with LIST of the table spaces will have different start RBA. Online image copies (SHRLEVEL CHANGE) of table spaces taken even using single LIST utility statement will have different start RBAs or LRSNs.
- If you have a periodic backup job to create image copies of DB2 catalog and directory table spaces, it must be updated to remove dropped table spaces and include new DB2 10 table spaces.
- Referential Integrity replaces links in the catalog table, and hence, any DSN1CHKR on the catalog table spaces is not required once you migrate to DB2 10 NFM.
This article provided an overview of the benefits of DB2 10 for z/OS DFSMS, and how to perform reorganization on the DB2 system table spaces. By migrating to DB2 SMS-managed data sets, you can simplify management of DB2 data sets and the storage control for them. The online reorganization of the table spaces, or the growth of data in a table, does not require manual allocation of VSAM LDS data sets.
I would like to thank the time and efforts of my colleagues Gary K Alburl and Willie Favero for reviewing the article and providing their inputs.
|List of table spaces from DB2 V9||ts.zip||---|
- Advance your database administration skills on DB2 for z/OS by reading the "Recommended reading list for DB2 z/OS DBA" developerWorks article.
- Learn more about DB2 for z/OS and other DB2 tools from the IBM Information Center .
- Learn more about DB2 for z/OS and Storage Management for DB2 in this Redbook .
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Follow developerWorks on Twitter.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
- Participate in the discussion forum.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.