DB2 11 System Administrator for z/OS: Certification Study Guide: Exam 317 (DB2 DBA Certification) - Softcover

Judy Nall

Softcover

ISBN 10: 1583478566 ISBN 13: 9781583478561

Publisher: MC Press (US), 2016

Synopsis

This book provides an in-depth review of the skills required to pass IBM's DB2 11 for z/OS System Administration exam 317 certification exam. The book addresses the changes in this new release of the DB2 11 for z/OS System Administration certification test, specifically for the latest version of DB2 for z/OS, version 11. Each section of the exam addresses a detailed set of skills that a DB2 system administrator on DB2 11 for z/OS should have. The book takes those sections, chapter by chapter, and provides the information a DB2 for z/OS system administrator will need to know to pass the certification exam. Practice questions and answers follow each chapter. A new practice final exam has been added to the book. The reader will come away being fully prepared to pass exam 317 and acquire certification, an important milestone showing significant academic and/or professional achievement.

About this title

About the Author

Judy H. Nall is an internationally recognized DB2 speaker writer, consultant, and educator. She has worked with DB2 since the first release. Her company, Computer Business International, Inc., for 21 years has provided DB2 classes, consulting, and mentoring on DB2 for z/OS. Judy is an IBM DB2 Gold Consultant, IBM Champion for Analytics 2009-2016, IDUG RUG Planning Committee Member, President of the Alabama DB2 Users Group, past IDUG conference chair, IBM DB2 for z/OS Gold Consultant Advisory Council member, and author of DB2 9 System Administration for z/OS: Certification Guide. She holds IBM certifications in DB2 9, 10, and 11 for z/OS Database Administration; DB2 9, 10, and 11 for z/OS System Administration; and DB2 11 for z/OS Application Programming.

Excerpt. © Reprinted by permission. All rights reserved.

DB2 11 System Administrator for z/OS

Certification Study Guide

By Judy H. Nall

MC Press

Copyright © 2016 Judy H. Nall
All rights reserved.

ISBN: 978-1-58347-856-1

Contents

1: Introduction to DB2 11 for z/OS, 
2: DB2 for z/OS Overview, 
3: Installation and Migration, 
4: Security and Auditing, 
5: System Backup and Recovery, 
6: Performance and Tuning, 
7: System Operations and Troubleshooting, 
Appendix A: DB2 Message Numbers, 
Appendix B: References and Useful Websites, 
Appendix C: Practice Exam — #317, 
Index, 

CHAPTER 1

Introduction to DB2 11 for z/OS

This chapter will address the job responsibilities of the DB2 system administrator, what to expect on the IBM DB2 11 System Administrator for z/OS certification exam (exam 317), and the basic prerequisites required for the reader of this book.

Purpose of the Book

This certification study guide for the IBM DB2 11 System Administrator for z/OS certification test (exam 317) will lead you through the responsibilities, tasks, and methods used by a DB2 11 for z/OS system administrator. It will cover all the objectives of the exam, as well as provide sample exam questions and comprehensive answers to test your knowledge.

We will define a broad understanding of a DB2 z/OS system administrator's responsibilities, give you examples of daily work requirements, show you techniques to assist you in your work, and provide you with details on how to develop your skill sets.

Certification Exam 317 — DB2 11 System Administrator for z/OS

The IBM exam certifies that the candidate has the skills, knowledge, and abilities to administer the processes and can describe the architecture required to plan, install, manage, and tune secure DB2 for z/OS environments.

To obtain this certification, you must pass two exams:

1. Test 610 — DB2 10.1 Fundamentals, or Test 320 — DB2 11 Fundamentals for z/OS

2. Test 317 — DB2 11 System Administrator for z/OS

Test 317 is 90 minutes long and has 58 questions. The passing score is 65 percent. The test is divided into six sections; each section contains questions and some scenarios to evaluate.

Here are the six objectives of this test.

• Section 1, Installation and Migration, comprises 20 percent of the test.

• Section 2, Security and Auditing, covers protecting the DB2 resources; this is 12 percent of the test.

• Section 3, System Operation and Maintenance, represents 17 percent of the test, and covers the management of DB2 components, commands, monitoring, and threads.

• Section 4, Backup and Recovery, comprises 17 percent of the exam. This section tests your knowledge of the procedures for system-level backup, recovery from system failures, and disaster recovery processes.

• Section 5, Performance and Tuning, is 22 percent of the exam. You will be given scenarios and asked to analyze the performance. You will be tested on your knowledge of buffer pools, DSNZPARMs, statistics, traces, and tools, as well as how to efficiently use memory and workload manager settings for DB2.

• Section 6, Troubleshooting, comprises 13 percent of the exam; it tests your ability to solve problems (i.e., troubleshooting). This section tests your knowledge of operator commands and traces, identifying contention problems, and diagnostics in dealing with utilities and resolving and identifying data-sharing problems.

The IBM Certification website provides a list of resources to help you prepare for the exam, a sample assessment test, and information about the testing centers worldwide. Take the time to visit the website (www03.ibm.com/certify/), and become familiar with the resources.

The sample assessment test for exam 317 is a good place to start. This is a way to practice taking the exam — but remember, passing the sample test does not result in credentials. It does, however, give you information about how well you scored on each section of the test, which you can use to evaluate your strengths and weaknesses.

The Sample/Assessment tab for exam 317, at www03.ibm.com/certify/tests/eduC2090-317.shtml, provides instructions on how to access the Web-based assessment test.

DB2 System Administration

Requirements

In working as a DB2 11 for z/OS system administrator, you need a strong background in DB2 for z/OS. As an example, if you are presently a database administrator with a couple of years of experience, or if you have several years as a DB2 advanced programming lead, you would have the baseline to start working on your system administration skill sets and your certification.

Basics You Should Know

Knowledge of DB2

Your understanding and background should include the DB2 architecture:

• DB2 catalog tables, DB2 directory, and bootstrap data sets (BSDS)

• DB2 active and archive logging, and DB2 virtual buffer pools (buffer pools)

• Structured Query Language (SQL) query writing, EXPLAIN, and troubleshooting

• Data Definition Language (DDL) and physical structures in DB2

• DB2 data sharing system operation and maintenance

• IBM DB2 Administration Tools

• IBM or other vendor DB2 utilities — COPY, LOAD, RUNSTATS, and others

System z Architecture

Your z/OS skill sets should include how to do the following:

• Write Job Control Language (JCL) to execute a job in z/OS.

• Work with Interactive System Productivity Facility/Program Development Facility (ISPF/PDF) utilities to edit data sets.

• Display data set lists or define a data set as an example.

• Use System Display and Search Facility (SDSF) and/or Interactive Output Facility (IOF) to find job execution information.

• Work with System Modification Program Extended (SMP/E) and its basic functions.

• Use Workload Manager (WLM) and its basic functions.

Familiarity with other software products — such as CICS, WebSphere, Data Studio, and non-IBM vendor products — is also helpful. Throughout the book, we will build on DB2, z/OS, and product skills.

The IBM website for DB2 11 for z/OS at www01.ibm.com/software/data/db2/zos/family/db211/ provides downloads, product documentation, and product support. Take time to become familiar with the website.

Review Questions

1. What two types of logs does DB2 use, and what is the purpose of each?

2. What are virtual buffer pools, and what are they used for?

3. Describe the SQL commands used to establish objects in DB2.

Answers

1. DB2 uses an active log and archive log. Events are recorded in the logs to reflect the activity against the data. The active logs are automatically archived when they fill. Archives can be disk or tape, but active logs must be disk. Active logs and archive logs are defined as dual copies to provide fallback, and we have multiple sets of active logs. Active logs sets are a wraparound process, so when we fill a log set, we move to the next set, and so on. When the last set of logs is filled in the active log set, we wrap back to the first set, provided that first set of logs has been properly archived. If this is not the case, DB2 will stop.

2. Data that is read, modified, or must be held is maintained in virtual buffer pools. These buffer pools' names and sizes are defined by systems or database administrators based on the workload in DB2. The names of the buffer pools are explicit in DB2 for z/OS (e.g., BP0, BP1, BP32k).

3. To establish objects in DB2, use DDL statements containing CREATE, ALTER, or DROP commands.

CHAPTER 2

DB2 for z/OS Overview

This chapter presents the tasks, services, structures, architecture, and components of DB2 11 for z/OS, which is required knowledge for a DB2 system administrator.

CSECTs in DB2

This short overview shows how the DB2 code is structured internally. A subcomponent is a group of related CSECTs.

• In DB2, each object module contains a single Control Section (CSECT).

• Typically, a CSECT will perform a single function, and the object module and CSECT have the same name.

• CSECT names and message identifiers begin with DSN in DB2.

• You can find the readable data set associated with a CSECT for DB2 in member DSNWMODS in library SDSNSAMP.

• A subcomponent is identified by the fourth character of a DB2 CSECT name.

• There are three groups of subcomponents:

* Distributed Data Facility (DDF) Services

* Database Services (DBAS)

* System Service Address Space (SSAS)

Distributed Data Facility Services

Running as an additional address space in DB2, DDF consists of one subcomponent called the distributed data facility. DDF controls connecting distributed applications to DB2 for z/OS. The naming convention for the subsystem is xxxxDIST.

Following are the resource managers associated with DDF. These subcomponents execute in the DDF services address space:

• Data communications resource manager (DCRM)

• Distributed transaction manager (DTM)

• Distributed relational data system manager (DRDS)

• Distributed data interchange services (DDIS)

DB2 Distributed Relational Data Architecture (DRDA) subsystems and other relational databases can communicate with DDF by using TCP/IP or VTAM on the same network. DDF supports two network protocols — SNA and TCP/IP — and the DRDA database communication protocol.

DRDA is set of protocols for databases that describe the architecture, allowing connection and access to distributed relational data in multiple database systems. DRDA defines what must be exchanged and how it must be exchanged, and then it coordinates the communication between systems. The three components in DRDA are the application requestor, the application server, and the database server.

Database Services

The DBAS uses SSAS and z/OS to handle the actual database structures. The goal of the DBAS is to manage the physical structures and data, execute SQL, and control the buffers. Within the DBAS, you have three main components: the relational data system (RDS), the data manager (DM), and the buffer manager (BM). Even though each is an independent component, they work together to make a proper subsystem of z/OS. The DBAS address space is also referred to as the Advanced Database Management Facility (ADMF) address space.

Subcomponents of interest that execute in the database services address space are:

• Data manager

• LOB manager (LOBM)

• Service controller

• Stored procedures manager

• Data space manager

• Relational data system (RDS)

• Utilities (work with associated code in an allied address space)

• Buffer manager

IRLM

DB2 requires the address space subsystem services of the internal resource lock manager (IRLM), which resides in its own address space. This is a separate IRLM from the IMS IRLM. IRLM works with DB2 to serialize access to your data. DB2 requests locks from IRLM to ensure data integrity when applications, utilities, and commands all attempt to access the same data.

You must still specify PC and MAXCSA DSNZPARM parameters, but their values are no longer used. We keep the parameters for compatibility reasons in DB2 11. Specific system-site specifications determine the amount of available storage for IRLM private control blocks, including locks. All IRLM locks are in the IRLM private address space; locks are not placed in the extended common service area (ECSA) any longer.

IRLM control block structures are estimated at 540 bytes per lock and are above the 2 GB bar.

System Services Address Space

SSAS manages logs, agent services, and more by executing subcomponents in the system services address space. This address space is also called the data system control facility (DSCF) space. A few of the subcomponents that execute in the SSAS are as follows:

• System parameter manager

• Recovery manager

• Recovery log manager

• Group manager

• Distributed transaction manager

• Storage manager

• Agent services manager

• Message generator

• Initialization procedures

• Instrumentation facilities

• General command processor

• Subsystem support

Allied Address Spaces

DB2 communicates with other address spaces in your z/OS environment, and we refer to these as allied address spaces. DB2 communicates with the following "allied agents" to facilitate requests:

• TSO attachment facility

• Standalone utilities

• Subsystem support

• Message generator (this is standalone only) DSN1SDMP

• IMS attachment facility

• Call attachment facility

• CICS attachment facility

• Resource Recovery Services attachment facility (RRSAF)

• Utilities

DB2 controls connections or threads to these allied agents through a DSNZPARM value of CTHREAD (defaults to 200, maximum of 20,000, updatable online). This value defines the number of concurrently allocated threads for our local connections. If we find that we are waiting for a connection to access the DB2 subsystem, we may need to increase the number of allied connections through CTHREAD. This, along with MAXDBAT, protects the virtual storage allocation. Remember to not over commit your virtual storage resources. You might need to increase MAXDBAT if the number of remote threads is queued with work waiting. The sum of the values of CTHREAD and MAXDBAT is limited to a maximum of 20,000.

Because some utilities use parallelism, we will have one thread for each utility and an additional thread for each subtask. So a single utility may be using many threads. You must specify a value for CTHREAD that will accommodate utility parallelism.

Non-Allied Address Spaces

Remember that DB2 does not communicate with the DB2 precompiler (PRE). However, the precompiler may require an allied address space, depending on the precompiler options you have selected. The full message generator for DB2 resides in the SSAS. You can also run this standalone in allied or non-allied address spaces.

DB2 Subcomponents

The software that comprises the resource managers is usually responsible for managing a specific resource. The resource being managed can be physical or logical. There is usually one subcomponent for one resource manager. However, some exceptions apply; for example, the precompiler is not a resource manager, and the instrumentation facilities subcomponent contains two resource managers.

A resource manager identifier (RMID) identifies a resource manager. This number identifies the source of diagnostic output in your dumps. Appendix A has a list of subcomponents and identifiers for your reference.

Work Requests in DB2

Our DB2 tasks and agents are subcomponents that run in an allied address space. A DB2 work request is represented by an agent. Classes of agents include system agents, allied agents, and database access agents. DB2 tracks the agent (the work) through an agent control element (ACE). Each ACE is associated with one or more execution blocks (EBs).

A z/OS execution unit (TCB or SRB) and EB have a one-to-one relationship. An EB describes each unique unit of dispatch work that can be either in task control block (TCB) or in service request block (SRB) mode. The user's home address TCB is pointed to by all allied agents' primary EB with which it is related.

An execution unit describes each unique unit of dispatch. This unit of dispatch can be in either a TCB or SRB mode in z/OS.

In the DB2 address space, execution units that are created in TCB mode are called service tasks. Resource managers in DB2 can dynamically delete and create service tasks. When DB2 is initialized, service tasks are created and usually exist until DB2 stops. These service tasks remain idle until their services are needed in DB2.

Examples of permanent service tasks are as follows:

• System service tasks

* Log manager

* Recovery manager

• Database services tasks

* Buffer manager

* Data manager

• DDF tasks

* Distributed transaction manager

DB2 11 System Structure Basics

DB2 uses several types of private address spaces, and each type requires storage.

• DB2 DDF address space (DSN1DIST)

• IRLM address space (IRLMPROC)

• DB2 SSAS (DSN1MSTR)

• DB2 database services address space (DSN1DBM1)

• DB2 allied agent address spaces

• DB2 stored procedures address spaces (WLM)

• DB2 administrative scheduler address space

When we start our DB2 subsystems, there is a recommended dispatching priority for these address spaces in z/OS. IRLM is started first; without locking to protect our resources, we cannot begin. Then, we start our DB2 performance monitors, followed by DBM1, and then the MSTR address space. Figure 2.1 provides an overview of the subsystem.

Attachment

An attachment facility provides the interface between DB2 and another environment, like Time Sharing Option (TSO). In TSO and your batch environments, you can use the TSO, call attachment facility (CAF), and Resource Recovery Services (RRS) attachment facilities to access DB2, as Figure 2.2 shows. Other attachment facilities are DB2 subcomponents that run in the user's address space, which include CICS and IMS.

Call Attachment Facility

For TSO and batch applications needing tight control over their session environment, DB2 does provide the CAF as a connection option. Programs can explicitly control the state of their connections to DB2 by using connection functions that CAF supplies.

First, make available the load module CAF or DSNALI. When the language interface is available, your program can use CAF to connect to DB2 by including SQL statements or instrumentation facility interface (IFI) calls in your program. You can also access CAF by explicitly writing CALL DSNALI statements.

Resource Recovery Services

DB2 supports RRS, which is a newer implementation of attachment capability. This z/OS system feature will coordinate the two-phase commit processing of recoverable resources.

RRS runs in its own address space and can be started and stopped independently of DB2. Starting z/OS RRS allows you to run an RRSAF application. RRS needs to be equal or higher priority than the dispatching priority of DB2.

(Continues...)

Excerpted from DB2 11 System Administrator for z/OS by Judy H. Nall. Copyright © 2016 Judy H. Nall. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Edition details

• Publisher: MC Press (US)
• Publication date: 2016
• Language: English
• ISBN 10: 1583478566
• ISBN 13: 9781583478561
• Binding: Paperback
• Number of pages: 424