DB2 product editions available for z/OS and Linux, UNIX, Windows (LUW) are so different that DB2 database administrators must often possess different skills to deliver on different platforms. This article will help you get started with DB2 that is available on the other platform. It assumes that you would supplement it with required knowledge of respective hardware and operating systems. This article aims to introduce different techniques in handling database administration duties across platforms and should not be used to compare the two products for their features or capabilities.
DB2 LUW is also available in various editions, including DB2 Express-C (no-cost), DB2 Express Edition, DB2 Personal Edition, DB2 Workgroup Server Edition, DB2 Enterprise Server Edition, and DB2 Advanced Enterprise Server Edition. The ideal mix of features for your business would depend upon the features that come with the base product, size of the business, number of users, and other licensing terms. Throughout this article, DB2 z/OS refers to DB2 10 for z/OS; and DB2 LUW refers to DB2 9.7 for Linux, UNIX and Windows. It should also be noted that Linux on System Z servers would run DB2 LUW and not DB2 z/OS.
About subsystems, instances, and databases
You will find differences in the way DB2 server is referred to and accessed on these two platforms. DB2 server is called a subsystem in z/OS and an instance in LUW. DB2 runs its tasks under a set of address spaces in z/OS system (discussed in Memory structures), as a process in Linux and UNIX operating systems, and as a service (unless you started with /D option) in Windows. Each DB2 z/OS server is identified by a four character subsystem identifier (SSID), and each DB2 LUW server is identified by an instance name that takes effect through the DB2INSTANCE environment variable.
What does the term database mean in each platform? A database in DB2 LUW has its own memory region, processes, and recovery logs, while a database in DB2 z/OS is a logical collection of several table spaces and index spaces with very few parameters defined as default for all objects created under it. While each database in DB2 LUW holds its exclusive catalog tables, it is every DB2 subsystem that holds catalog tables.
What about the system objects
- A DSNDB06 database in DB2 z/OS subsystem, and a SYSCATSPACE table space in DB2 LUW will hold all the DB2 catalog tables.
- In non-data sharing DB2 z/OS, DSNDB07 is the work-file database. Table spaces that you create in DSNDB07 will be used as storage for processing SQL joins and sorts as well as for temporary tables. In DB2 LUW, a default system temporary table space called TEMPSPACE1 will be available for such DB2 DBM requirements as sorts or joins. You should create one or more USER temporary table spaces for storing data of temporary tables.
- When you do not specify the database name while creating a table space or table in DB2 z/OS, DSNDB04 is the default user database for the created object. If you create a table without specifying a table space, DB2 will create an implicit table space for you. In DB2 LUW, assuming you have not created any user table spaces yet, USERSPACE1 will hold the tables you create without specifying the table space explicitly.
- DB2 z/OS uses several information required for normal operation in a special database, DSNDB01. Boot Strap Dataset (BSDS) is a z/OS VSAM dataset that will contain some critical information regarding DB2 recovery log dataset names, DB2 checkpoints and DB2 location details. On the other hand, DB2 LUW database stores several information required for normal operation across several operating system files under local database directory.
How will you make your first remote connection
To access a remote DB2 LUW server, you should catalog the node by specifying DB2 instance and then catalog the target database under that instance at this cataloged node. Listing 1 shows how you can find DB2 server related details on an AIX host. On the other hand, you must catalog a remote DB2 z/OS using its location. Also, you could store host DB2-related information in Database Connection Services (DCS) Directory through
CATALOG DCS DATABASE command, as in Listing 2. The location of DB2 z/OS is defined when the DB2 subsystem is installed, which you can find by executing the
-DIS DDF command on the DB2 z/OS server, as shown in Listing 3.
The DB2 z/OS subsystem contains information for establishing connections with remote subsystems in a set of catalog tables called Communication Database (CDB). Also, an additional IBM DB2 Connect license is required to remotely connect to DB2 databases located on host systems including z/OS. You can refer to links in the Resources section for more information about IBM DB2 Connect.
Listing 1. The following commands will give you host-name and port/service of remote DB2 LUW server
$ db2set ‑all [i] DB2COMM=TCPIP [g] DB2SYSTEM=host.ibm.com $ db2 get DB2INSTANCE The current database manager instance is: db2inst1 $ db2 "get dbm cfg " | grep ‑i '(svcename)' TCP/IP Service name (SVCENAME) = DB2_db2inst1 $ cat /etc/services | grep ‑i DB2_db2inst1 DB2_db2inst1 60000/tcp
Listing 2. Run the following commands on DB2 Client for cataloging the above servers
$ db2 "CATALOG TCPIP NODE aixnode REMOTE host.ibm.com SERVER 60000 > REMOTE_INSTANCE db2inst1" $ db2 "CATALOG DATABASE sampledb AS sampledb AT NODE aixnode" $ db2 "CONNECT TO sampledb USER userid USING passwd" $ db2 "CATALOG TCPIP NODE zosnode REMOTE zserver.ibm.com SERVER 446" $ db2 "CATALOG DATABASE nyc AS zosdb2 AT NODE zosnode" $ db2 "CATALOG DCS DATABASE nyc AS newyork" $ db2 "CONNECT TO zosdb2 USER zosuser USING passwd"
Listing 3. The following is a sample -DIS DDF command output on DB2 z/OS
‑DIS DDF DSNL080I @ DSNLTDDF DISPLAY DDF REPORT FOLLOWS: DSNL081I STATUS=STARTD DSNL082I LOCATION LUNAME GENERICLU DSNL083I NEWYORK A.B ‑NONE DSNL084I TCPPORT=446 SECPORT=0 RESPORT=4463 IPNAME=‑NONE DSNL085I IPADDR=::ip‑addr DSNL086I SQL DOMAIN=zserver.ibm.com DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
You can issue
LIST DB DIRECTORY and
LIST NODE DIRECTORY commands in DB2 LUW to enquire about cataloged databases and nodes respectively.
DB2 memory structures
In DB2 LUW, memory consumed by DB2 server is broadly sub-divided into instance-level database manager shared memory and database-level database shared memory. The following list summarizes DB2 z/OS address spaces and their memory components.
- System Services Address Space (ssidMSTR) controls the entire system services including log management and command processing.
- Database Manager Address Space (DBM1) hosts all the buffer pools, Environment Descriptor Manager Pool (EDM), Sort pool, and Record Identifier Pool (RID). The largest of the DB2 address spaces, DBM1 is comparable to database shared memory of DB2 LUW. EDM pool includes cache blocks for catalog accesses, dynamic SQL, plans/packages, and cursors.
- Distributed Data Facility Address space (ssidDDF) is responsible for distributed data access from remote clients as well as for accessing remote data from the DB2 subsystem.
- Internal Resource Lock Manager (IRLM) takes care of lock and other serialization-related activities. In IRLM, you define the storage for locks that is comparable to the locklist parameter in DB2 LUW.
- z/OS Workload Managed (WLM) DB2 address spaces run stored procedures and routines. You could also come across DB2 Stored Procedure Address Space (SPAS) running routines that were created before DB2 v8.
- Agent Allied Address spaces run for each address space connecting to DB2. These are similar to private agent memory in DB2 LUW.
- DB2 utilities run under their batch address spaces and uses memory from other DB2 address spaces as required.
While DB2 z/OS will not allow you to create buffer pools by any name, DB2 LUW will allow you to name the buffer pool to anything you like through the
CREATE BUFFERPOOL SQL statement. In DB2 z/OS, there are 50 pre-defined buffer pools of various page sizes. You must choose a buffer pool name according to the page size you want and activate it through
-ALTER BUFFERPOOL command. Also, when you create a table space in DB2 z/OS, you will define its page size indirectly through a buffer pool of the required page size. Listing 4 and Listing 5 demonstrate and contrast how you can query details about buffer pools in DB2 z/OS and DB2 LUW respectively.
Listing 4. Buffer pools in z/OS
‑DIS BPOOL(BP0) DSNB401I DB1S BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 357 DSNB402I DB1S BUFFER POOL SIZE = 12500 BUFFERS AUTOSIZE = NO ALLOCATED = 12500 TO BE DELETED = 0 IN‑USE/UPDATED = 284 BUFFERS ACTIVE = 12500 DSNB406I DB1S PGFIX ATTRIBUTE ‑ CURRENT = NO PENDING = NO PAGE STEALING METHOD = LRU DSNB404I DB1S THRESHOLDS ‑ VP SEQUENTIAL = 80 DEFERRED WRITE = 30 VERTICAL DEFERRED WRT = 5, 0 PARALLEL SEQUENTIAL =50 ASSISTING PARALLEL SEQT= 0 DSN9022I DB1S DSNB1CMD '‑DIS BPOOL' NORMAL COMPLETION
Listing 5. Buffer pools in LUW
$ db2 "SELECT SUBSTR(BPNAME,1,15) BPOOL, BUFFERPOOLID, NPAGES, PAGESIZE, > NUMBLOCKPAGES, BLOCKSIZE FROM SYSIBM.SYSBUFFERPOOLS" BPOOL BUFFERPOOLID NPAGES PAGESIZE NUMBLOCKPAGES BLOCKSIZE ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ IBMDEFAULTBP 1 2000 4096 0 0 1 record(s) selected.
As a DBA on a new platform, you would need to know the current DB2 server configurations and the method to set or update them when required. DB2 z/OS subsystem parameters are assembled and linked into an executable module through a MVS Job DSNTIJUZ shipped by IBM. These subsystem parameters are referred to as DSNZPARM (or ZPARM in short). To update any of these DB2 subsystem parameters, as a DB2 system administrator, you should execute the DSNTIJUZ followed by the
-SET SYSPARM RELOAD command to refresh DSNZPARM module dynamically. A similar task would require executing the
UPDATE DBM CFG or
UPDATE DB CFG command on LUW platform, depending upon whether it is database manager (DBM) configuration or database (DB) configuration that you would like to update. The DB2 LUW environment is also controlled by different types of registries. You can view and set the variables under these registries using the
db2set system command.
DB2 LUW provides you with an easy way to manage various memory structures through self-tuning memory management (STMM). By enabling STMM through the DB CFG parameter self_tuning_mem, you can let DB2 tune memory structures depending upon fluctuations in workload.
Also, DB2 LUW offers a very useful command called
AUTOCONFIGURE that you can use to let DB2 set DBM and DB parameters according to a certain type of workload (OLTP or DSS types). DB2 z/OS offers an option to let DB2 automatically adjust the size of buffer pool(s) according to the workload. You can use the
ALTER BUFFERPOOL command with AUTORESIZE(YES) operand to achieve it. DB2 will fine-tune the buffer pool size with the aid of z/OS WLM.
Table spaces and storage layout
Table spaces group physical containers to hold data of various tables and their indexes. Because they map database data to underlying physical storage (raw disks, file systems, volumes), understanding them in a new platform is critical for DBAs. DB2 z/OS stores table data on Linear Virtual Storage Access Method (VSAM, type LDS) datasets. DB2 LUW also stores table data on files, directories and raw devices depending upon the type of table space.
Table spaces that hold indexes in DB2 z/OS are called index spaces, and when you create an index, DB2 automatically creates an index space for the new index. Unlike in DB2 LUW, you can not place more than one index on an index space. When you create a range-partitioned table in DB2 z/OS, the underlying table space is also partitioned and such a partitioned table space can not hold more than one table. In DB2 for LUW, you can place individual partitions of such table on one or more table spaces.
While typically, several tables are grouped under an individual table space in DB2 LUW, most of the time you will end up creating one table per table space in DB2 z/OS. This is because, in DB2 z/OS, most of the data management utilities run at table space level (or partition level if table is partitioned), and you get more freedom in choosing storage attributes or table maintenance window. Yet, you may create more than one table per table space when the table size is small. You should also know that creating multiple tables under a table space will affect the entire table space while running several DB2 utilities.
How can you define DB2 storage
In DB2 LUW, you have the following two methods to define DB2 storage. Listing 6 shows an example of how to use these methods.
In the Database Managed Space (DMS) method, you would explicitly specify a set of files or raw devices for each table space and let DB2 database manage them. Placing DB2 data on raw devices is discouraged now as DB2 creates DMS table spaces without file system caching by default. In this method, you are required to specify the initial amount of space and it should be available to have your table space successfully created. For DMS table spaces, you can add or remove containers as required. You can also specify whether containers should be allowed to grow, and for such containers, you can also define the size by which they should extend and the maximum size for individual containers. You can simplify DB2 storage management by enabling DB2 Automatic Storage for entire database. You can enable Automatic Storage (default for new databases) at database level either through
ALTER DATABASEcommands. This feature requires you to specify, at database level, one or more storage paths where you want DB2 to create containers for every table space.
In the System Managed Space (SMS) method, you would specify the locations and directories, and let OS file manager handle the space. Space will be allocated on-demand. You will be able to add space to your table space only by adding space to underlying file-system. Also, you cannot convert a SMS table space into DMS table space directly.
Listing 6. Different storage options in DB2 LUW
-- Create a database with automatic storage disabled; Each table space -- will use a default type depending upon its type. db2 "CREATE DATABASE testdb AUTOMATIC STORAGE NO" -- Enable Automatic Storage on automatic storage disabled database db2 "ALTER DATABASE testdb ADD STORAGE ON '/home/dbstor1'" -- Create a Tablespace managed by automatic storage db2 "CREATE TABLESPACE autotbsp MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES > INITIALSIZE 1G INCREASESIZE 10M MAXSIZE 2G" -- Create a DMS Tablespace with 100 4KB pages. DB2 will fix increment size -- and max size. db2 "CREATE TABLESPACE dmstbsp PAGESIZE 4096 MANAGED BY DATABASE USING > (FILE '/home/dbstor1/dbfile01.dbf' 100)" -- Add additional container to DMS table space db2 "ALTER TABLESPACE dmstbsp ADD (FILE '/home/dbstor1/dbfile02.dbf' 100)" -- Alter the DMS table space to use Automatic Storage db2 "ALTER TABLESPACE dmstbsp MANAGED BY AUTOMATIC STORAGE" -- Spread data to newly added storage paths db2 "ALTER TABLESPACE dmstbsp REBALANCE" -- Create SMS table space db2 "CREATE TABLESPACE smstbsp MANAGED BY SYSTEM USING ('/home/db2inst1/smsdir')" db2 "CREATE TABLE T1(C1 CHAR(1)) IN smstbsp INDEX IN smstbsp" db2 "CREATE INDEX I1 ON T1(C1)" -- At least one file would be created for every object created in smstbsp -- Operating system files for the SMS table space just created: $ ls -l /home/db2inst1/smsdir total 40 -rw------- 1 db2inst1 db2adm1 4096 Jul 27 17:12 SQL00002.DAT -rw------- 1 db2inst1 db2adm1 12288 Jul 27 17:12 SQL00002.INX -rw------- 1 db2inst1 db2adm1 512 Jul 27 17:06 SQLTAG.NAM
When creating table spaces and indexes in DB2 z/OS, you can specify whether you want the underlying storage be managed by user (DBA) or DB2. When you create user-managed table spaces, you must have already created underlying z/OS LDS VSAM datasets using IDCAMS Program. This method would let you plan DB2 storage and carefully place DB2 datasets on selected direct access volumes. For DB2 managed table spaces, DB2 z/OS provides a database object called storage group (STOGROUP). You can assign one or more direct access volumes to individual storage group and use the storage group name while creating objects. DB2 will manage space within the listed volumes and place data in available space among listed volumes. On the other hand, to manage storage, DB2 z/OS can work synergistically with another z/OS sub-system called Storage Management System (SMS). In this technique, entire DB2 storage will be handled by rules written by z/OS Storage administrators. This method is very common as it gives Storage administrators complete control of the storage consumed by different consumers. DB2 also has capabilities to let DB2 administrators specify individual SMS constructs while creating DB2 Storage groups. Listing 7 shows you an example of defining storage in DB2 z/OS.
Listing 7. Typical storage definition in DB2 z/OS
‑‑ Create an user managed table space in database TESTDB CREATE TABLESPACE USERTS USING VCAT DB2X IN TESTDB; ‑‑ Create Non‑SMS managed table space CREATE STOGROUP dbasg VOLUMES(VOLSER1,VOLSER2,VOLSER3) VCAT DB2T; CREATE TABLESPACE NONSMSTS USING STOGROUP dbasg PRIQTY 1440 SECQTY 720; ‑‑ Create SMS managed table space through VOLUMES ('') operand CREATE STOGROUP smssg VOLUMES('') VCAT DB2T; CREATE TABLESPACE SMSTS1 USING STOGROUP smssg; ‑‑ Create storage group with SMS clauses CREATE STOGROUP newsmssg VCAT DB2T MGMTCLAS mgmt1 STORCLAS storclas1 DATACLAS dc1 ;
With DB2 z/OS, no matter what method you create table space, the underlying LDS VSAM datasets will have a standard naming convention. When you create an user-managed object, DB2 forces you to follow the same naming convention. The maximum size of such LDS VSAM datasets would depend indirectly upon the type of table space you create and the parameters you specify on the CREATE statement.
Data movement techniques
If you are a DBA, you could already have performed some cross-platform table data movement involving z/OS servers. DB2 z/OS supports unloading and loading of data in delimited format for easy movement across heterogeneous products and platforms. In DB2 z/OS, some core utilities come with the base DB2 product, while several utilities are packaged separately under a different function called Utilities Suite.
On the other hand, DB2 LUW utilities come with the base DB2 product. You will typically run DB2 z/OS Utilities by executing the DSNUTILB program in batch mode. The batch jobs in z/OS are coded in Job Control Language (JCL), as shown in Listing 8. Hence you may want to learn a bit or more about JCLs before trying your hands on DB2 z/OS utilities. DB2 LUW offers EXPORT and IMPORT, in addition to the LOAD utility that is also available in DB2 z/OS. The IMPORT utility can perform a sequence of inserts into the table, thereby firing any triggers defined on the table, which does not happen when you move data into a table using LOAD utility. In z/OS, you can also perform bulk data movement, for example, like moving entire DB2 subsystem or a group of large table spaces using DFSMSdss utilities with the aid of DB2 stand-alone utilities. For such large-scale data movement in LUW, you can use BACKUP and RESTORE commands with redirected restore methods or db2move tool.
Listing 8. Typical z/OS JCL to execute DB2 utilities
//Job Statement line‑1 //Job Statement line‑2 // It will run against DB2 sub‑system DB2T //STEP01 EXEC PGM=DSNUTILB,PARM='DB2T' //STEPLIB DD DISP=SHR,DSN=DB2.V9R1.DB2T.SDSNEXIT // DD DISP=SHR,DSN=DB2.V9R1.SDSNLOAD //SYSPRINT DD SYSOUT= //SYSOUT DD SYSOUT= //SYSREC DD DISP=SHR,DSN=DDS1923.TABLE.DATA //SYSIN DD LOAD DATA RESUME NO LOG NO INTO TABLE DEPT /*
Know your environment before running LOAD
Though the Load utility is available on both DB2 z/OS and DB2 LUW, you will find significant differences between them. The DB2 z/OS LOAD utility, when run with the REPLACE function, would empty the entire table space. So before you run one like that, you may want to check if the underlying table space has more than one table in it. DB2 LUW does not support loading one or more partitions while maintaining the availability of other partitions. On the other hand, in DB2 z/OS, you can run the load utility to affect a set of partitions, and DB2 will load the data into those partitions if the records fall within the range of partitions. DB2 LUW load does not enforce relational integrity (RI) for the data affected. Instead, it will put affected tables in INTEGRITY PENDING state. This restricted status is equivalent of CHECK PENDING status you come across in DB2 z/OS.
DB2 z/OS enforces RI by default and, optionally, lets you bypass RI checks, only in this case DB2 puts your table space in check-pending. If your table has one or more check constraints, DB2 z/OS load will always check such constraints and reject rows that violate them. DB2 LUW load will not check constraints. Instead, it loads all rows into the table and places the table in INTEGRITY PENDING state.
You can use the UNLOAD utility while working with DB2 z/OS. IBM also ships a sample unload program called DSNTIAUL. This sample program can give you additional SQL capabilities (like data obtained by joining tables) that are not supported with the UNLOAD utility. But, the UNLOAD utility can let you get data from previously taken backup copies, a feature which will help you to get historical data. While doing an UNLOAD, you can also generate corresponding LOAD control statement and use it for loading data to the same or a similar table in any DB2 subsystem.
How to check data integrity
Listing 9 shows how you can query status of a table space to know about CHECK PENDING status. To check integrity of table data, you can index keys to table rows, and table rows to associated LOBs. While working with DB2 z/OS, you can run CHECK utility; and while working with DB2 LUW, you can run SET INTEGRITY statement and/or INSPECT command, as shown in Listing 10 and 11.
Listing 9. CHECK pending reported on a Display table space command in DB2 z/OS
-DIS DB(TESTDB) SPACENAM(TESTTS) DSNT360I DB1S *********************************** DSNT361I DB1S * DISPLAY DATABASE SUMMARY * GLOBAL DSNT360I DB1S *********************************** DSNT362I DB1S DATABASE = TESTDB<!-- DW-CHECK-V8 Warning: HTML strong tags were removed from the line above in the code listing. Please ensure there are no reference to these tags in the body text. --> STATUS = RW DBD LENGTH = 16142 DSNT397I DB1S NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE -------- ---- ----- ----------------- -------- -------- -------- ----- TESTTS<!-- DW-CHECK-V8 Warning: HTML strong tags were removed from the line above in the code listing. Please ensure there are no reference to these tags in the body text. --> TS 0001 RW,CHKP<!-- DW-CHECK-V8 Warning: HTML strong tags were removed from the line above in the code listing. Please ensure there are no reference to these tags in the body text. --> ******* DISPLAY OF DATABASE TESTDB ENDED **********************
Listing 10. CHECK utility samples for DB2 z/OS
‑‑ Check violations of check constraints and RI CHECK DATA dbname.tbspname SCOPE REFONLY ‑‑ Check violations and if any found, move them to exception tables ‑‑ and delete from original table CHECK DATA dbname.tbspname FOR EXCEPTION in tabowner.tabname USE tabowner.excep_tabname DELETE YES ‑‑ Check all dependant table spaces (containing all kinds of dependant tables) ‑‑ for violations of RI, Check constraints, LOB values and XML values CHECK DATA dbname.tbspname SCOPE ALL ‑‑ Check invalid LOB values and LOB references CHECK LOB TABLESPACE dbname.lobtbsp ‑‑ Check index to table data integrity with concurrent read/write access CHECK INDEX tabowner.indexname SHRLEVEL CHANGE ‑‑ Check all the indexes on all the tables under table space 'tbspname' ‑‑ of the database 'dbname' CHECK INDEX (ALL) TABLESPACE dbname.tbspname ‑‑ Finally, reset the Check‑pending w/o running the integrity check REPAIR SET TABLESPACE dbname.tbspname NOCHECKPEND
Listing 11. Checking for integrity in DB2 LUW
‑‑ A Sample query to query integrity status and access modes of individual tables ‑‑ 'U' and 'Y' denotes integrity checked by user or system respectively ‑‑ 'N' denotes particular constraint not checked yet $ db2 "select substr(creator,1,12) creator, substr(name,1,30) name, > case when access_mode='R' then 'Read Only' > when access_mode='F' then 'Full access' > when access_mode='N' then 'No Access' > when access_mode='D' then 'No Data Movement' > end as "Access mode", > substr(const_checked,1,1) FK, substr(const_checked,2,1) CC > from sysibm.systables order by 4,5" CREATOR NAME Access mode FK CC ‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑ ‑‑ DB2INST1 EMPPROJACT No Access N Y DB2INST1 ADEFUSR No Access U U DB2INST1 EMPLOYEE No Access N N DB2INST1 DEPARTMENT No Access N Y DB2INST1 CL_SCHED Full access Y Y DB2INST1 DEPT Full access Y Y ‑‑ SET INTEGRITY will not run if the table is not in INTEGRITY PENDING, ‑‑ So, you can set INTEGRITY PENDING on a particular table alone by: $ db2 "SET INTEGRITY FOR db2inst1.employee OFF CASCADE DEFERRED" ‑‑ Put table in INTEGRITY PENDING with read access to the data $ db2 "SET INTEGRITY FOR db2inst1.customer OFF READ ACCESS CASCADE DEFERRED" ‑‑ Check violations of check constraints and referential integrity $ db2 "SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED" ‑‑ Check violations and move violating rows off to exception tables $ db2 "SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED > FOR EXCEPTION in db2inst1.employee USE db2inst1.emp_exception" ‑‑ Reset Integrity pending for RI violations without actually checking $ db2 "SET INTEGRITY FOR db2inst1.employee FOREIGN KEY IMMEDIATE UNCHECKED"
Backup and recovery methods
The RECOVER Utility/command is available in both DB2 LUW and DB2 z/OS. RECOVER will do the RESTORE of the most recent backup, followed by a log apply process to bring the table space or database to a specified point in time. You should query the DB2 Catalog table SYSIBM.SYSCOPY to know about back-up details (location, RBA, time of the backup), similar to how you would use
LIST HISTORY command in DB2 LUW to read recovery related information stored in DB2RHIST.ASC file.
In DB2 LUW, you could also use
ROLLFORWARD DATABASE command, typically after the restore of an online copy (using the
RESTORE DATABASE command), to apply data changes from recovery logs. The RECOVER Utility in DB2 z/OS with LOGONLY performs something similar to ROLLFORWARD.
While working with DB2 z/OS, you can utilize the following two utilities to backup data at different levels.
- BACKUP SYSTEM utility:
- Entire DB2 sub system with or without logs using DFSMShsm component of z/OS SMS
- COPY utility:
- Complete table space (Non-partitioned or all partitions of a partitioned table)
- A set of partitions of a partitioned table
- Index space that is enabled for COPY
- A set of partitions of partitioned index
- Individual dataset of a non-partitioned table space
- Individual piece of a non-partitioned index
For DB2 LUW, what you can backup depends on the logging mode of the database. If the database is in a circular logging mode, only offline backup of the database is allowed. Backing up individual table spaces is not supported while the database is in circular logging mode. You can enable archive logging mode on the database and start taking online backups of the database as well as backups of a set of table spaces in the database. Listing 12 demonstrates online database backup and recovery methods in DB2 LUW.
Listing 12. Using recovery-related commands in DB2 LUW
-- Have archiving enabled and perform an online backup. $ db2 "backup db sample online" Backup successful. The timestamp for this backup image is : 20110728153122 -- Perform a recovery; DB2 will automatically pickup -- most recent backup it should restore to. $ db2 "recover db sample to 2011-07-28-15.33.00.000000" Rollforward Status Input database alias = sample Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000024.LOG - S0000025.LOG Last committed transaction = 2011-07-28-15.31.32.000000 Local DB20000I The RECOVER DATABASE command completed successfully. -- Also you can do a RESTORE from a backup copy followed by ROLLFORWARD $ db2 "restore db sample taken at 20110728153122 without prompting" SQL2540W Restore is successful, however a warning "2539" was encountered during Database Restore while processing in No Interrupt mode. $ db2 "restore db sample logs taken at 20110728153122 logtarget /home/db2inst1/logs' $ ls -l /home/db2inst1/logs total 104 -rw------- 1 db2inst1 db2adm1 53248 Jul 28 15:53 S0000024.LOG $ db2 "rollforward db sample query status using local time" Rollforward Status Input database alias = sample Number of nodes have returned status = 1 Node number = 0 Rollforward status = DB pending Next log file to be read = S0000024.LOG Log files processed = - Last committed transaction = 2011-07-28-15.31.32.000000 Local $ db2 "rollforward db sample to 2011-07-28-15.33.00.000000 using local time > and complete overflow log path (/home/db2inst1/logs)" Rollforward Status Input database alias = sample Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000024.LOG - S0000025.LOG Last committed transaction = 2011-07-28-15.31.32.000000 Local DB20000I The ROLLFORWARD command completed successfully.
While working with DB2 z/OS, you can also run the
REPORT RECOVERY utility to get prepared ahead of a recovery situation. When you recover a table space in DB2 z/OS, the indexes dependant on affected tables would not be built automatically. You should plan for and build such indexes using the
REBUILD INDEXES utility.
Cleaning up backup history
As a DBA, business rules will usually dictate how long you should keep backup copies. In DB2 z/OS, you should execute
MODIFY RECOVERY to delete obsolete entries for a table space from the SYSIBM.SYSCOPY catalog table. This utility by no means would physically delete the backup datasets.
MODIFY RECOVERY dbname.tsname DELETE DATE(111201)– To clean up entries older than 01-Dec-2011.
MODIFY RECOVERY dbname.tsname DELETE AGE(60)– To clean up entries older than 60 days.
MODIFY RECOVERY dbname.tsname RETAIN LAST(30)– To clean up entries but retaining recent 30 entries.
On DB2 LUW, for example,
PRUNE HISTORY 201105 will delete entries older than May 2011. DB2 will substitute values for month, date and other fields of timestamp if you do not specify a complete timestamp. By appending
AND DELETE to the previous command, you can also physically delete the backup copies.
A word about Quiesce
Even though Quiesce is available in both DB2 z/OS and DB2 LUW, typical use of this command/utility differs. In DB2 z/OS, you will find need to establish a log point of consistency to enable you to perform Point In Time (PIT) recovery. When you run
QUIESCE TABLESPACE dbname.tbspname, DB2 will write all pages from the buffers into the disk, and will also record the log sequence number (Relative Byte Address/RBA or Log Range Sequence Number/LRSN) in the SYSIBM.SYSCOPY table. You would want to do this on situations where frequent backups are not possible, or to force changed pages off buffer pools before performing copy activities outside DB2. You can use the recorded RBA in the
RECOVER TABLESPACE command to perform Point In Time (PIT) recovery.
On the other hand, in DB2 LUW, Quiesce will be in persistent lock-mode that can be applied on instance, database or table space to restrict normal users while you perform maintenance tasks. Once your maintenance activity is over, you should issue
UNQUIESCE DB sampledb or
QUIESCE TABLESPACES FOR TABLE myschema.tabname
RESET to allow normal users access to the data.
To maintain data in good shape, across both platforms, you will be using REORG and RUNSTATS. Real Time Statistics (RTS) is also available in both DB2 z/OS and DB2 LUW. When it comes to maintaining indexes, DB2 z/OS has the following two different utilities: REORG INDEX and REBUILD INDEX. In DB2 LUW, the REORG INDEX utility can perform both functions. Since DB2 z/OS utilities run at either table space level or partition level for partitioned-table space, they would affect all tables present in the table space. Hence, while working in DB2 z/OS, you could reorganize more than one table, or rebuild indexes of more than one tables at one shot. Table 1 lists and compares data maintenance methods available in these platforms.
Table 1. Different needs and ways to run REORG
|Aim||In DB2 z/OS||In DB2 LUW|
|Reorg a non-partitioned index||REORG INDEX creator.ixname||REORG INDEX schema.npi_index CLEANUP ONLY|
|Rebuild a non-partitioned index||REBUILD INDEX (creator.ixname)||REORG INDEX schema.npi_index|
|Rebuild all indexes of single-table table space||REBUILD INDEX (ALL) TABLESPACE dbname.tbspace||REORG INDEXES ALL FOR TABLE myschema.tabname|
|Reorg a partition of partitioned-index||REORG INDEX creator.ixname PART 10||Not supported|
|Reorg a single-table table space offline||REORG TABLESPACE creator.ixname SHRLEVEL NONE||REORG TABLE ALLOW NO ACCESS|
|Reorg a single-table table space online||Specify SHRLEVEL CHANGE and use MAPPINGTABLE||Specify INPLACE ALLOW WRITE ACCESS|
|Reorg 2 or more contiguous partitions of a table||REORG TABLESPACE dbname.tsname PART m:n||Not supported|
|Online Reorg a partitioned table that has at least one non-partitioning index||Supported||Not supported|
|Checking for (need of)Reorg||Run RUNSTATS utility and Query Catalog tables or run REORG TABLESPACE with OFFPOSLIMIT m INDREFLIMIT n and REPORTONLY keywords; Run REORG INDEX with LEAFDIST n and REPORTONLY keywords||REORGCHK ON TABLE myschema.tabname|
While working with DB2 LUW, if you want to monitor DB2 at any object level, you must enable Database Monitor (DBM) switches ahead of any activity. You can run the
GET DBM M0NITOR SWITCHES command to understand the level of monitoring at your server.
Similarly, in DB2 z/OS, there are different types of traces that will run at sub-system level to collect information that you normally require for monitoring and performance tuning. You can either start and stop trace when you set on a monitoring exercise, or you can define in the subsystem DSNZPARM to have DB2 run them automatically.
Listing 13. Getting to know active traces and their destinations in DB2 z/OS
-DIS TRACE(*) DSNW127I DB1S CURRENT TRACE ACTIVITY IS - TNO TYPE CLASS DEST QUAL IFCID 01 STAT 01,03,04,05, SMF NO 01 06,08 02 AUDIT 01 SMF NO 03 ACCTG 01,02,03,07, SMF NO 03 08 04 MON 01 OP1 NO 05 AUDIT 03 OP1 NO 06 AUDIT 01,02,04,05, OP1 NO 090,091
System Management Facility (SMF), seen as a destination (DEST) in the previous listing, is a z/OS aid that is used by various z/OS sub-systems to write different kinds of trace records. To read SMF records, you can use custom written routines (either home-grown or purchased from vendor) to produce several batch reports on a particular event or DB2 object. Also, you can use monitoring tools like IBM Tivoli OMEGAMON XE (has to be purchased separately) to provide you a snapshot view of the DB2 sub-system or its applications/threads.
In DB2 LUW, you can perform monitoring through an Event Monitor and Snapshot Monitor. You can define the Event Monitor for a particular type of event, and depending upon the type of event, you can tell DB2 to write the captured information into a DB2 table, file, pipe, or Unformatted Event Table (new in DB2 9.7).
You can also use some aiding tools like the
db2evtbl command, or
db2evmon while working with table or file type event monitors respectively. While in DB2 LUW, you can also use various administrative views under schema SYSIBMADM while monitoring DB2 instance or database. These dynamic views will present for you the snapshot of the system generated by snapshot monitor. You can also collect snapshot information through the
GET SNAPSHOT command, or through various snapshot functions. Listing 14 shows an example of creating and administering an Event Monitor in DB2 LUW.
Listing 14. Running a simple event monitor in DB2 LUW
‑‑ Create a simple file event monitor for every statement $ db2 "CREATE EVENT MONITOR file_event_stmt FOR STATEMENTS > WRITE TO FILE 'stmtevent' MANUALSTART" ‑‑ Event is not started yet because of keyword MANUALSTART $ db2 "SELECT SUBSTR(EVMONNAME,1,18) EVMONNAME, CASE WHEN > EVENT_MON_STATE(EVMONNAME)=0 THEN 'STOPPED' > WHEN EVENT_MON_STATE(EVMONNAME)=1 THEN 'STARTED' END AS STATUS > FROM SYSCAT.EVENTMONITORS" EVMONNAME STATUS ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑ DB2DETAILDEADLOCK STARTED FILE_EVENT_STMT STOPPED 2 record(s) selected. ‑‑ Make sure target directory is created and start the event $ db2 "SET EVENT MONITOR FILE_EVENT_STMT STATE=1" ‑‑ Format the DB2 event output $ db2evmon ‑path /home/db2inst1/db2inst1/NODE0000/SQL00002/db2event/stmtevent > event_out
Availability and scalability
IBM has been adding features with every DB2 upgrade in order to improve the availability of data and data servers. One such solution for high availability is DB2 Data Sharing for z/OS which is based on both software and hardware components of z/OS.
Data sharing is achieved by forming two or more DB2 subsystems that typically run on different physical machines in z/OS Parallel sysplex. Individual DB2 subsystems, called as members, present a single view to data requesters and share data on the disk. When you connect to such a data sharing group, DB2 will run your work on the member that is available and has capacity in its workload. One or more z/OS machines would act as a Coupling Facility upon which you will create DB2 components such as group buffer pools, a lock structure, or a shared communication area for efficiently managing data access. This solution also provides an easier way to perform migration and maintenance of individual DB2 subsystems. Scaling DB2 servers involves just adding one or more members to existing data sharing group.
The DB2 pureScale Feature that is available for Linux and UNIX is comparable to DB2 Data Sharing feature of z/OS. See the Resources section for more information on how these two features are compared. Note that this feature is only available as a separately-priced option on Enterprise Server Edition and Advanced Enterprise Server Edition.
Another way of scaling database servers, called Database Partitioning Feature (DPF) is available for you to spread data across several partitions (nodes). DB2 would distribute the entire table data among database partitions that could be located on different physical machines, based on a distribution map. As of DB2 LUW 9.7, DPF is no longer part of DB2 editions but is available with all IBM InfoSphere Warehouse editions.
DB2 LUW also offers the following high availability features:
- Automatic client reroute. You can register an alternate DB2 LUW server at the primary server using the
UPDATE ALTERNATE SERVER FOR DATABASEcommand. Clients connected to the primary DB2 server via TCP/IP will be able to connect to the defined alternate server if they have a loss of communication with primary.
- High Availability Disaster Recovery (HADR). This solution involves using an additional DB2 server as a stand-by that can take over operations when the primary has to go down. Once you configured the primary and standby databases through various database configuration parameters and restored the primary database at the standby location, you could start HADR function. The DB2 server would automatically relay log changes from primary to its stand-by server. You could also mingle automatic client reroute technique with the HADR databases.
In this article, we discussed methods, tools, and commands typically used in performing the most common database administration tasks on both DB2 for z/OS and DB2 for LUW. Because DB2 is used across both of these platforms and has a high percentage of SQL portability, similarity in catalog tables, and comparable features and data management commands, this article helps you to deliver more responsibilities by leveraging existing skills.
The author would like to thank his colleagues Ken Taylor, Camalla Haley Baruwa and Ramesh Chejarla for their valuable suggestions on content of this article.