(SECono DIET DB2 for the COBOL Programmer Curtis Garvin Anne Prince ih MIKE Muracu & ASSOCIATES, INC. 2560 Wess Shaw Lane, Suite 101 # Fresno, CA 93711-2765Authors: Curtis Garvin ‘Anne Prince Editor: Mike Murach Cover design: Zylka Design Design and production: Tom Murach Related Murach books: DB2 for the COBOL Programmer Part | Murach’s Structured COBOL Murach’s CICS for the COBOL Programmer The CICS Programmer's Desk Reference IMS for the COBOL Programmer, Parts I and 2 MVS TSO, Parts I and 2 ‘MVS JCL © 1999, Mike Murach & Associates, Inc. All ights reserved. Printed inthe United States of America. 1098765432 ISBN; 1-890774-03.0 Library of Congress Cataloging-in-Publication Data Garvin, Cunis, 1961- DB? for the COBOL programmer: version 4.1/Curtis Garvin, Steve Ecko - 2nd ed Pp. em ISBN 1-890774-02-2 (pbk.: v. 1) ISBN 1-890774-03-0 (pbk: v. 2) |. Database management. 2. IBM Database. 3, COBOL (Computer program language) 4. SQL (Computer program language) 1. Eckols, Steve, Il Title QA769.D3G385 1998 (005.75°65-de21 98-40935 cP.Contents Introduetion y Section 1 Advanced DB2 concepts Chapter 1 OS/390 and data sharing concepts Chapter 2 Advanced locking concepts Section 2 Advanced DB2 programming techniques Chapter 3. How to use dynamie SQL Chapter 4 How to work with distributed data 99 Chapter $ How to create and execute stored procedures 135 Section 3 DB2 in CICS programs .. Chapter 6 CICS concepts and terminology for DB2 programmers 16s Chapter 7 Basie CICS/DB2 programming techniques 179 Chapter 8 How o browse DB2 data in a CICS program 21 Section 4 Database administration .... Chapter 9 How to design a DB2 database Chapter 10 How to manage DB2 objects with SQL’s Data Definition Language 263 Chapter 11 How to use DB2’s commands and utilities 291 Chapter 12. How to extract information from DB2's catalog tables 319 Chapter 13 How to get performance analysis data using the EXPLAIN statement 343. Appendixes .. a Appendix A SQLCODE and SQLSTATE values Appendix B_ SQLCA and SQLDA fields IndexIntroduction DB2 is the primary database management system (DBMS) for IBM mainframe computers that run under the MVS operating system. Although you can use several different methods to work with DB2 databases, most DB2 work on a mainframe is done through COBOL programs. So Part 1 of this series covers the DB2 features that every COBOL programmer needs to know for just about every program. Then, this book expands on what is covered in Part J by presenting material that goes beyond the basics. When you complete this book, you'll have the skills you need to be a senior programmer or programmer/analyst 4 ways this book differs from other DB2 books This is the only DB2 book that's designed just for COBOL application programmers. In contrast, most other DB2 books focus on ad hoc processing or database adminis- tration that’s beyond what programmers need to know to the exclusion of many of the DB2 features for COBOL programming. + Most of the chapters in this book are self-contained, so you can learn the skills you need when you need them. If you want to leam about working with distributed data, for example, you can go directly to chapter 4 without reading the first three chapters. And if you want to learn about using DB2's commands and utilities, you can go directly to chapter 11, The only exceptions are that you should read chapter 1 (on data sharing) before you read chapter 2 (on locking), and you should read chapters 6, 7, and 8 (on CICS programming) in order. + To help you learn more easily, this book presents 7 complete COBOL programs run under the MVS operating system using DB2 version 4.1. These programs let you clearly see how the DB2 code you're learning fits in with your COBOL code. And once you've used these programs for training, they become time-saving models for the programs you develop on the job. + After you use this book to leam DB2 programming and database administration, it becomes the best reference guide you've ever used, Why? Because all the content is logically organized by function under headings that clearly identify the information chunks, so it’s easy and fast to find what you're looking for. And all of the essential information for each function is summarized in the illustrations, so you read less to get the information you need.What’s new in this edition The first edition of this book was based on DB2 version 2. With the release of DB2 versions 3, 4, and 5, though, some significant enhancements became available. As a result, the primary purpose of this second edition is to present the new language and features that became available with those versions. That means every chapter has been thoroughly revised and updated to reflect these versions In addition, two new chapters have been added to this book to cover the major new features that weren't available with version 2, Specifically, this book now includes a chapter on OS/390 and the data sharing features it provides, and it includes a chapter on stored procedures. In addition, we added a chapter on advanced locking concepts that expands on what was presented in Part 1 of this series. And we added a chapter that covers the most useful DB2 commands and utilities. With these new chapters, you'll have all the information you need to develop any COBOL program that accesses DB2 data and to manage the data you need to test those programs. What this book teaches This book assumes that you already have the skills presented in Part 1 of this series. Specifically, it assumes that you know how to develop COBOL programs that retrieve and modify DB? tables; how to code embedded SQL statements that use unions and joins, advanced selection conditions, column functions, data manipulation features, and subqueries; how to work with variable length data and nulls; how to use the DB2 features for error process- ing; how to use the COBOL constructs that affect locking; how to prepare and run programs; how to use SPUFI; and how to use QMF to update tables. If you don’t have some of these skills, you'll want to get Part 1 of this series before you proceed with Part 2. With that as background, this book raises you to the next professional level. To start, the two chapters in section 1 present advanced DB2 concepts that will increase your understanding of how DB2 works. In chapter 1, you'll learn about the various System/390 configurations and how these systems provide for sharing data. Then, chapter 2 expands on the locking concepts you learned in Part J. In particular, it explains how locking works in a data sharing environment. Although the information in this section isn’t mandatory for reading the other chapters in this book, it gives you a perspective on data sharing and locking that can help you write more efficient programs. That's why we recommend that you read these chapters at some point in your DB2 training. Section 2 of this book teaches you some advanced programming tech- niques that weren't presented in Part J. In chapter 3, you'll learn how to develop programs that issue SQL statements dynamically, In chapter 4, you'll learn how to write programs that access distributed data, And in chapter 5, you'll learn how to write programs that execute stored procedures and how todevelop stored procedures in COBOL. When you complete this section, you'll have all the skills you need to develop COBOL programs that access DB2 data in a batch environment or under TSO. Since most interactive programs run under CICS, though, you need to learn how to use CICS facilities. So that’s what you'll learn in section 3 of this book. In chapter 6, you'll lea basic CICS concepts and terminology. In chapter 7, you'll learn basic CICS programming techniques. And in chapter 8, you'll learn how to browse DB2 data in a CICS program. Although these chapters introduce you to the concepts and skills for developing programs that run under CICS, they don’t present everything you need to know about CICS For a complete course on CICS, we recommend our CICS books: CICS for the COBOL Programmer, Part I and Part 2 The last section of this book presents the least you need to know about database administration. In chapter 9, you'll lear how to design your own database. In chapter 10, you'll learn how to use the SQL DDL statements to create and manage DB2 objects. And in chapters 11, 12, and 13, you'll learn how to use the database administration features that let you set up a quality assurance environment for testing DB2 programs. Specifically, you'll learn how to use the most important DB2 commands and utilities in chapter 1; you'll learn how to look at the information in the DB2 catalog tables in chap- ter 12; and you'll learn how to use the EXPLAIN statement to get performance analysis data in chapter 13. How to download the 7 example programs and other files from our web site As we mentioned earlier, the 7 programs in this book not only teach you DB2 processing, they're also time-saving models for your own programs. That's why we make them available to you on our web site, along with some other useful files that you can use for program development tasks (there are JCL job streams, for example). By downloading these files to your system, you'll save hours of entry time. To download the files, go to the Downloads portion of our web site Gewwmurach.com/downloads). From there, you can download the zip file for this book to a default folder on your system. Then, from your Windows Explorer, you can double-click on the name of the downloaded file to expand it into its component files in this folder: C\Murach\DB2\Part2. Since all of the files are in ASCH (text) format, you can then open them with a word processor or a text editor.Please let us know how this book works for you If you have any comments about this book, we would enjoy hearing from you. That’s why there's a postage-paid comment form at the back of this book, ‘And if this book helps you learn what you wanted to know, we'd be especially delighted to hear about it. That’s what makes it all worthwhile. [dd tami Dana Pree Curtis Garvin Anne Prince Author AuthorSection 1 Advanced DB2 concepts The two chapters in this section present some advanced DB2 concepts that will take you to the next step in your understanding of how DB2 works, Specifically, chapter | describes the various System/390 system configurations and one of OS/390's integral features: the ability to share data, Then, chapter 2 presents some advanced locking concepts. If you read Part I of this series, the information in this chapter will expand on what you learned in chapter 11 of that book. Although it's not critical that you read the chapters in this section before you read the other chapters in this book, we recommend that you read them at some point. If you do, they'll give you a perspective and understanding of data sharing and locking that most application programmers don’t have. And that knowledge will not only help you write more efficient programs, but it may also help you get that new job or advancement you've been looking for. When you read the chapters in this section, be sure to read chapter I first. Because some of the locking concepts presented in chapter 2 have to do with you'll want to understand how data sharing works before you1 OS/390 and data sharing concepts DB2 version 4.1 made it possible for applications running on separate DB2 subsystems to share data, That means that these applications can read and update the data in the same data sets at the same time. Because the techniques you use to share data depend in large part on the operating system and system hardware you're using, this chapter starts by describing some of the common System/390 configurations. Then, it summa- rizes the various techniques for accessing shared data. Finally, it describes DB2 data sharing on one of the most advanced System/390 configurations, the parallel sysplex. The evolution of System/390 computers 4 How the first Systenv390 computers were configured 4 How a loosely coupled system is configured 6 How abase sysplex is configured 8 How a parallel sysplex is configured 10 How parallel processing i implemented on a parallel sysplex 2 How to share DB2 data Alteratives for accessing shared DB2 data 4 How DB2 data sharing groups are implemented 16 How DB2 uses the Coupling Facility to share data 18 PerspectiveSection | Advanced DB2 concepts The evolution of System/390 computers In the early 1990's, many mainframe shops began looking at the possibility of replacing their mainframes with PC-based networks. However, several factors prevented many of those companies from making this move. The most critical factor was the inability of PC-based networks to manage large volumes of data. Because it was clear that more and more companies were looking for networking capabilities, IBM developed a new architecture, called the System/ 390, or just $/390. This system provides many of the same features as a PC- based network, but can manage large volumes of data that those systems can’t Because of that, it has quickly become one of the most popular IBM systems ever built, How the configured ‘st System/390 computers were Figure 1-1 presents two of the first System/390 cor configuration, called a single system uniprocessor, consists of a single central processor (CP). This processor, along with the other system hardware such as channels and storage, make up what is known as a central processor complex, or CPC, The idea of a central processor complex is common to all System/390 configurations The second configuration shown in this figure is a tightly coupled multi- processor. This configuration is made up of two or more central processors that share the same storage. The main advantage of this system over the single system uniprocessor is its increased capacity. However, its capacity is still limited by the maximum number of CPs it can contain. In addition to the system hardware, each of these systems require an operating system that provides its basic functions. Today, the most popular operating system is MVS/ESA. In the two configurations in this figure, a single copy, or image, of MVS controls the system operations. In the case of the tightly coupled multiprocessor, one of the functions of the operating system is to distribute the work among the CPs, That way, the CPs can perform work simultaneously, providing a function called multiprocessing, Before I go on, you should know that in 1995, the MVS/ESA operating system was superseded by a new operating system called OS/390. OS/390 provides a complete package that includes the MVS/ESA operating system, along with other products that are typically required in today’s mainframe environments. Throughout this chapter, though, we'll refer to the MVS/ESA operating system since it’s still at the center of OS/390,Chaprer 1 OS/390 and data sharing concepts A single system uniprocessor mvs _ {| ! image ‘ | — Central storage [- Central processor MVS system console asp A tightly coupled multiprocessor mvs _ | \ image} ‘ 1] [conver J} ‘-f--F=P=E! [with four central MVS system processors console asp Concepts A System/390, ot S/390, computer consists of one or more central processors (CPs), The CPs, along with other system hardware such as channels and storage, make up a central processor complex (CPC). A single system uniprocessor consists of a single CP that’s managed by an MVSVESA operating system. Because the system is controlled by a single copy of the operating system, it has a single MVS image. A tightly coupled multiprocessor consists of two or more CPs that share central storage. The CPs are managed by a single MVS/ESA operating system. The operating system can distribute work among the CPs so that multiple program instructions can be processed simultaneously. When a system that’s managed by a single operating system fails, the entire system. fails, The entire system must also be taken down to implement software or hard- ware changes, However, these systems are easy to manage because they have a single image. Figure 1-1 The first System/390 configurationsSection I Advanced DB2 concepts a loosely coupled system is configured Figure 1-2 presents the next major step in the evolution of the System/390. In this loosely coupled configuration, two or more central processor complexes are connected using channel-to-channel communications. Then, components of the job entry subsystem, along with a feature of the MVS/ESA operating system called global resource serialization, manage the sharing of data, printers, and consoles among the CPCs. JES2 or JES3 provides for the sharing, of a work input queue across two or more CPCs and for distributing work via a shared job queue. And global resource serialization provides for the sharing of data at the data set level across those same CPCs. Although a loosely coupled configuration lets you coordinate some of the work of the CPCs, it does not provide a way for a product or application running on one CPC to communicate with a product or application running on another CPC. To do that, the product or application must provide its own communication mechanism. In addition, each CPC has its own MVS image that must be managed separately from the other images, often by a human operator who monitors product-specific messages on a set of consoles. Both of these shortcomings are addressed by the sysplex configuration,Chaprer 1 OS/390 and data sharing concepts A loosely coupled configuration : 1 dobenysubsystem i lon components t ‘ Cental |! if Centar oe miner }| [oem | fez ]|/-o eae | | cremratiocrannet | | i “eps yor! connection IeEe=f--]--4-! Ms system onesies Concepts + A loosely coupled configuration consists of two or more CPCs connected by channel-1o-channel communications. Usually, the CPCs are tightly coupled multi- processors, but they can also be single system uniprocessors. + The CPCs in a loosely coupled configuration can share data, printers, and consoles. ‘The components used to accomplish this sharing are JES2 or JES3 and global resource serialization. These components provide a way of sharing a work input queue across the MVS images. * Because a loosely coupled configuration includes multiple CPCs, it provides increased system capacity and system availability over single system uniprocessors and tightly coupled multiprocessors. However, the management of a loosely coupled configuration is more difficult because each CPC must be managed separately. + Although the job entry subsystem and global resource serialization provide a way of sharing data and a work input queue, they do not provide for communication between products and applications running on different CPCs. These products and applications must establish their own mechanism for communicating with each other. Figure 1-2 A loosely coupled configurationSection I How Advanced DB2 concepts a base sysplex is configured In September of 1990, IBM introduced the MVS systems complex, or sysplex. A sysplex is similar to a loosely coupled configuration in that it consists of two or more CPCs that can share data and work. Unlike a loosely coupled configuration, though, the programs running on separate CPCs in a sysplex ean communicate with each other to coordinate their work. ‘The product that provides this functionality is called the cross-system coupling facility, or XCF. XCF became available with version 4 of MVS/ESA, and it’s the facilitating component of a base sysplex system. This is illustrated in the configuration shown in figure 1-3. You should notice in this figure that although each CPC has its own copy of MVS and therefore its own MVS image, all of the CPCs in the complex can be viewed through a single system image. This makes managing a sysplex significantly easier than managing a loosely coupled system. Also notice that this configuration includes a Sysplex Timer. The Sysplex Timer ensures that the time-of-day clocks on all the CPCs in the sysplex are synchronized Another feature that was made available with MVS/ESA version 4 is the support of Enterprise Systems Connection (ESCON) 1/0 channels. These channels use fiber optic cables that carry light pulses rather than electrical signals like the bus and tag cables used with parallel channels. Because they use light, fiber optic cables aren’t susceptible to external disturbances such as electromagnetic and radio-frequency interference. They can also transmit data al much faster speeds than bus and tag cables. In addition, fiber optic cables are smaller than bus and tag cables and can be used over greater distances. ‘A recent improvement to ESCON channels are FICON (Fiber Connection) channels. These channels provide an increased data transfer rate and maximum throughput rate over ESCON channels. In addition, they can be used over even greater distances than ESCON channels.Chaprer 1 OS/390 and data sharing concepts A base sysplex configuration Single system [7 ‘mage — Ce MVS system console 5 ‘Cross-system |, s2upling fecity (XC) connection | Channel-to-channel ASO Concepts + Assystems complex, or sysplex, consists of a group of two or more CPCs that communicate and work cooperatively with each other. The CPCs in a sysplex are connected by channel-to-channel communications supported by a shared data set and can be viewed as a single logical entity from a system console. + Ina base sysplex, the cross-system coupling facility (XCF) provides the mechanism. for authorized programs (such as DB2) on the same system or on different systems to communicate with each other so they can coordinate their work. XCF is a component of MVS that became available with MVS/ESA version 4. + A Sysplex Timer is a hardware unit that is used to synchronize the time-of-day clocks for all of the CPCs in the sysplex. It ensures that the time stamping for transactions, logs, and messages are consistent across the entire sysplex. + The capacity and availability of a base sysplex are the same as for a loosely coupled system, However, XCF improves communication among the CPCs of the sysplex and makes system management easier. Figure 1-3 A base sysplex configuration10 Section Advanced DB2 concepts How a parallel sysplex is configured In 1994, IBM announced version 5 of MVS/ESA. This version supports a new System/390 configuration called a parallel sysplex that can currently support up to 32 CPCs, Figure 1-4 illustrates a parallel sysplex configuration. ‘The most significant new feature of a parallel sysplex is the Coupling Facility. This feature provides for the integrity and serialization of data that’s shared across the sysplex. For mission-critical applications, (wo or more Coupling Facilities may be employed. Then, if a failure occurs in the main Coupling Facility, a backup Coupling Facility can be employed to provide uninterrupted processing of data. Later in this chapter, you'll learn how DB2 uses the Coupling Facility to share data. When it was first introduced, the Coupling Facility had to be implemented on a separate hardware device as indicated in this figure. More recent System/390 computers, however, can support the Coupling Facility internally. This optional feature is called the Internal Coupling Facility. Another new feature of MVS/ESA version 5 is the Workload Manager (WLM). The main function of the Workload Manager is to balance the workload across the sysplex. In other words, the Workload Manager deter- imines which CPC work is assigned to. It performs that task based on. proc ing goals established by the system administrator. Those goals are defined in terms of business needs and objectives rather than system-related parameters as in the past. Before I go on, you should know that the CPCs in a parallel sysplex configuration are typically referred to as servers, and the workstations that are used {0 initiate programs that run on the servers are typically referred to as clients. In other words, this is the mainframe implementation of a client/server system. You'll see this terminology used in later chapters of this bookChapter 1 08/390 and data sharing concepts 14 A parallel sysplex configuration Enhanced single system Coupling Facility MVS system console Genial storage ‘Sysplex Timer oa asp Concepts + In aparalle! sysplex, the CPCs communicate through the Coupling Facility. The Coupling Facility is a combination of hardware, software, and licensed internal code that manages the data integrity and serialization needed for data sharing. The support for the Coupling Facility became available with MVS/ESA version 5. + The CPCs in a parallel sysplex communicate with the Coupling Facility over high- speed fiber-optic links called coupling links, MVS/ESA provides the services needed for authorized applications (including DB2) to use the Coupling Facility services. + A component of MVS/ESA version 5 called the Workload Manager (WLM) manages the workload of a parallel sysplex based on goals established by the system administrator. The WLM provides for balancing the workload across the CPCs of the sysplex. + Because a parallel sysplex can support more CPCs than a base sysplex, it provides additional capacity and increased availability. It also provides improved data sharing capabilities through the Coupling Facility, and it provides improved workload balancing through the Workload Manager. Figure 1-4 A parallel sysplex configuration12 Section | Advanced DB2 concepts How parallel processing is implemented on a parallel sysplex As its name implies, a parallel sysplex also provides for parallel process- ing. Parallel processing is similar to multiprocessing, but implies that addition to processing work simultaneously on two or more processors, the same type of work is being processed. For example, batch, query, and online transaction processing workloads can all be made to run in parallel. As you ean imagine, parallel processing can dramatically improve processing efficiency in a data sharing environment. In particular, parallel processing can increase the throughput for an online workload, and it can decrease the processing time for a long-running application. Figure 1-5 illustrates how this works ‘The first diagram in this figure illustrates how two or more CPCs can process online transactions simultaneously. To accomplish this, the Workload Manager works in conjunction with a transaction manager like CICS to distrib- ute the applications to the available CPCs. Note that the transactions that are processed in parallel can be the same transactions, or they can he different transactions. ‘The second diagram in this figure illustrates how a long-running applica- tion can be split into smaller units of work that can run in parallel. For ex- ample, an application that updates data based on a table of transactions and then prints a listing of the transactions could be divided into two programs: one that performs the update function and one that prints the listing. Then, these ‘wo programs could run in parallel. As an application programmer, you can take advantage of parallel processing by not combining related functions into a single program. In both of the diagrams in this figure, you'll notice that all the CPCs have access to the same data. IF they didn’t, they wouldn't be able to process the same applications or applications that used the same data, Parallel processing, then, depends in large part on the ability of the CPCs to share data. In the following topics, you'll learn more about how PB2 data is shared on a parallel sysplex,Chapter 1 08/390 and data sharing concepts. 18 How parallel processing increases throughout for an online workload i390 pcs Completed Transactions O transactions 999 “8 OQ O00 F 620 How parallel processing decreases processing time for long-running applications Units processed in parallel _ Application soptzaton Manta ~ [Jp soteton a O-@-3- 0 N [AKL D Description *+ Parallel processing refers to the processing of a specific type of work on two or more processors at the same time, Batch, query, and online transaction processing, (OLTP) workloads are the most common types of work to be run in parallel + If the number of online transactions exceeds the processing capabilities of the system, the processing of those transactions can be delayed causing response times to increase. To address that problem, additional CPCs can be added to the sysplex so that the transactions can run in parallel as illustrated in the first diagram above. + Ifa program is written so that it performs two or more related tasks, it can be rewritten so that each task is performed by a separate program. Then, those pro- grams can be run in parallel as illustrated in the second diagram above. This technique is particularly appropriate for programs that take a long time to execute. Figure 1-5 How parallel processing is implemented on a parallel sysplex14 Section 1 Advanced DB2 concepts How to share DB2 data The term data sharing refers to the sharing of data on a parallel sysplex using the Coupling Facility. However, you don’t necessarily need this confi ration to share DB2 data, Before I describe how data sharing is implemented on a parallel sysplex, then, I'll present some alternatives for sharing DB2 data. Alternatives for accessing shared DB2 data Figure 1-6 summarizes some of the altematives for sharing DB2 data across two or more DB2 subsystems. The first alternative is to use a single server that’s responsible for processing all requests for the data. Although this, technique eliminates data integrity problems, the communication between systems can be inefficient. In addition, a failure of the server makes the data inaccessible to all systems. Another alternative is to partition the data among two or more servers so that each server manages a portion of the data. Then, data requests must be sent to the appropriate server. This technique provides for sharing of the workload across {Wo or more servers, thereby increasing overall system efficiency. To keep the workload in balance, however, the servers and the data they control ‘must be managed continually. And, if a server fails, the data it manages is no longer accessible. ‘A third alternative is to distribute the data among two or more servers. This, technique is similar to partitioning the data, except that the servers are con- nected over a network. Because the routing of data over a network can be inefficient, it’s appropriate when data must be stored at remote locations. You'll learn more about working with distributed data in chapter 4. The final alternative presented in this figure is to implement data sharing. When you use data sharing, the workload can be spread out across the servers since each server can access the required data directly. You'll learn how data sharing is implemented in the next two topics.Chapter 1 08/390 and data sharing concepts — 15 Use a single server + Assingle server controls and manages the data. Other systems must send their data requests to that server, + Data integrity problems don’t occur since a single server updates the data + The communication between systems can reduce performance. + Data access relies on a single server. If that server goes down, data is no longer accessible, Partition the data among two or more servers + Each server controls a portion of the data, and requests for data must be sent to the server that controls it. + Data integrity problems don’t occur since a single server updates the data, + Data must be split across the servers and managed continually so that the workload is balanced. + Ifa system fails, the data it controls can't be accessed. Distribute the data among two or more servers + Data is distributed among two or more servers connected over a network. + To access distributed data, application programs must specify the location of the data (see chapter 4), Then, the request is routed to that location. + Routing data requests over a network can have a significant effect on response times. Implement data sharing + Each server has direct access to the data. + The workload can be spread out across the servers, + Ifa server fails, its workload can be assigned to another server. Figure 1-6 Alternatives for accessing shared DB2 data16 Section | Advanced DB2 concepts How DB2 data sharing groups are implemented Figure 1-7 illustrates the basic data sharing concepts. Here, the sysplex. consists of five CPCs and six DB2 subsystems that are divided into two data sharing groups. These groups, which are defined by the DBA, have access to specific data. Notice that each DB2 subsystem can be a member of one, and only one, group. One of the advantages of data sharing is that data can be accessed by any ‘member of a data sharing group. Because of that, the Workload Manager can balance the workload among the members of the group. In addition, if a DB2 subsystem or an entire CPC in the group goes down, its work can be routed to another subsystem or CPC. In other words, data access doesn’t depend on a single subsystem or server like it does when you use any of the other tech- niques for sharing data that were presented in figure 1-6, ‘To implement data sharing, the DB2 subsystems in a data sharing group communicate with each other through the Coupling Facility. You'll learn more about the Coupling Facility in the next topic. For now, just realize that it provides the services necessary for two or more DB2 subsystems to access the same data at the same time. Once data sharing groups are established, they are transparent (0 the application programs that use them. That means that no special coding is required to identify the location of the data or the data sharing group that has access to it. That also means that changes to the system configuration or the data sharing groups have no effect on existing applications. Even so, it makes sense for application programmers to have a basic understanding of how data sharing works.Chaprer 1 OS/390 and data sharing concepts DB2 data sharing groups Data sharing group 1 Pot Data sharing DB2A fA group 2 B28 ce T |_L el pe2e Be Be rt Poe catalog catalog and and CPCS = directory directory, DBF Pca = peed Description To implement data sharing on a parallel sysplex, DB2 subsystems are organized into data sharing groups. The members of each group can reside on the same CPC or on different CPCs, Each DB2 subsystem can be a member of a single data sharing group. ‘The DB2 subsystems in a data sharing group communicate with each other through the Coupling Facility. Although two or more groups can use the same Coupling Facility, a member of one group can’t communicate with members of another group. In addition to the actual data, the members of a data sharing group also share the DB? catalog and directory that store information about the plans and packages that access the data. If a DB2 subsystem or an entire CPC in a data sharing group shuts down, work can be routed to another subsystem or CPC in the group since all the members of the group can access the same data. Because the DB2 subsystems in a data sharing group appear to application pro- grams as a single subsystem, no special programming is required to access shared data, Figure 1-7 How DB2 data sharing groups are implemented 1718 Section 1 Advanced DB2 concepts How DB2 uses the Coupling Facility to share data An integral part of the data sharing environment is the Coupling Facility. The main function of the Coupling Facility is to maintain the integrity of the data that’s shared by the members of a data sharing group. To do that, it implements the structures shown in figure 1-8. To insure that two or more DB2 subsystems don’t update the same data at the same time, the Coupling Facility includes a lock structure. The lock structure provides for shared and exclusive locking capabilities like those that are provided by DB2. If you understand DB2 locking, then, you won't have any trouble understanding the function of the lock structure. In the next chap- ter, you can learn more about how the Coupling Facility manages locking. ‘The Coupling Facility also provides a list structure, also called the shared communications area (SCA). AS its name implies, the list structure contains information that’s stored in lists, That information can then be accessed by any of the members of the data sharing group. When an application reads data, the data is stored in the buffer pool of the DB2 subsystem that requests it, If that data is subject to sharing, itis also registered with the Coupling Facility. Then, if the data is updated, it is written 10 a group buffer poo! that’s part of the Coupling Facility’s cache structure. When that happens, any other member of the data sharing group that has accessed the same data is notified that the data has changed, and the copy of the data in that member's buffer pool is invalidated. That member can then retrieve the current data from the group buffer pool.Chapter 1 08/390 and data sharing concepts. — 19 The coupling facility and its structures Butfer pool n Coupling Facility DB2A E28 Lock structure Buffer pool List Buffer pool 0 structure Butfer pool 1 Cache structure Butfer pool 1 Group . butter pool 0. e 8 Group ° les the shared and exclusive locking capabilities that prevent more than one application from changing the same data at the same time, + The list structure is DB2’s shared communications area (SCA). It's used to store data such as work queues, directories, and status information that's organized in lists. Description + The lock structure pro The cache structure provides a buffer invalidation mechanism that tracks the data accessed by the members of a data sharing group and notifies those members when the data changes. The changed data is stored in the group buffer pools so it’s available to all the members of the group. Figure 1-8 How DB2 uses the Coupling Facility to share data20 Section 1 Advanced DB2 concepts Perspective Now that you've completed this chapter, you should be able to see why data sharing is critical to a System/390 parallel sysplex configuration, the ability to share data, each central processor complex would have to access its own data or copies of the same data, causing an administrative nightmar ‘That's why data sharing is considered one of the most significant improve- ‘ments ever made to DB2. When data sharing is implemented in a parallel sysplex environment, special locking capabilities are required to maintain the concurrency and. integrity of the shared data, Those capabilities are provided by the DB2 subsystems in a data sharing group and the lock and cache structures of the Coupling Facility that were introduced in this chapter. To learn more about how locks are managed in a data sharing environment, you can read the next chapter. out2 Advanced locking con- cepts Chapter 11 in Part 1 of this series presented the basic locking concepts that all COBOL programmers need to know to use locking efficiently. But there's a lot ‘more to locking than what's presented in that chapter. This chapter, then, expands on what you leamed in Part J. In case you didn’t read Part 1, though, this chapter starts by reviewing the basic locking concepts. A review of the basic locking concepts DB2 table spaces and lock sizes B2 lock modes and lock promotion Bind parameters that affect lock duration COBOL constructs that affect locking Expanding the basic Concepts «rn How locks are acquired for SELECT operations 30 ow locks are aequired for UPDATE and DELETE operations that use a cursor 32 How locks are acquired for INSERT, UPDATE, and DELETE operations that don't use a cursor How type I and type 2 indexes affect locking How locks are used in a data sharing environment. How logical locks are used An L-lock scenario How physical locks are used A Pelock see Perspective..22 Section 1 Advanced DB2 concepts A review of the basic locking concepts DB2 uses locking to prevent one program from accessing data that has been changed, but not committed, by another program. Because locking can affect the concurrency of a system (the number of programs and users that can access the data in shared tables at the same time), it's important for you to understand how locks work and how you can affect locking using bind param- eters and COBOL constructs. That's what you'll lear in the following topics. DB2 table spaces and lock sizes Each DB? table is stored in a table space. This space defines the physical structure of the VSAM data sets that are used for storing the tables. Figure 2-1 presents the three types of table spaces DB2 provides and describes the size of the locks it can acquire on each type. All three types of table spaces are divided into equal-sized units called ages, and each page contains rows of table data. In a segmented table space, the pages are grouped into units called segments where each segment contains data from a single table. That makes it possible to lock data at the table level as well as at the row, page, and table space level. For most applications, this is the most efficient type of table space because it maximizes concurrency. A partitioned table space can only contain data from a single table. In this type of table space, the pages that contain the rows of the table are grouped into Units called partitions. Prior to DB2 version 5, the data in a partitioned table space could be locked only at the row, page, or table space level. Now, with DB2 version 5, you can also lock data at the partition level. To do that, the table space must be defined with the LOCKPART option set to YES. Partitioned table spaces are most appropriate for large tables that contain one million or ‘more pages. A simple table space is divided into pages without any higher level struc- ture, which means that it can be locked only at the row, page, and table space levels. Like a segmented table space, a simple table space can contain data from more than one table. Unlike a segmented table space, though, each page in a simple table space can contain data from more than one table, Because this type of table space is inefficient, it’s typically not used for new applications, One of the factors that determine the lock size used in a table space is the LOCKSIZE option that’s set when the table space is defined, Although this option can be set to ROW, PAGE, TABLE, or TABLESPACE, the ANY option is used for most table spaces, Then, DB2 selects the optimum lock size for each, processing situation. In most cases, that size is a page, which means that less data is locked and more concurrency is possible. If too many locks are held at the page or row level, though, DB2 can escalate the locks to the next level. By increasing the lock size, DB2 reduces the overhead that's required for manag- ing the locks, but this also reduces the concurrency.Chapter 2 Advanced locking concepts 28 A segmented table space ‘Segmented table space ‘Segment t ‘Segment 2 ~ Paget Pat Pages Pages Table 1 Row? || || Tablet Row a Table? Row? || || Table2 Rowa Tabie 1 Row 2 || || Table 1 Rows Table 2 Row 2 Table 1 Row || || Table t Row6 Table 2 Rows A partitioned table space Partioned table space Pariont ————) Parion2 y Paget a Pages ——— - Page4 ——— Table 1 Row? || || Table 1 Row Table 1 Row? || || Tablet Row 10 [Table 1 Row2 || || Table 1 Rows Table 1 Rows || || Table 1 Row 11 Table 1 Row || || Table t Row6 Table 1 Row A simple table space 1 Simple tablespace Page 1 Page? Pages Page 4 Row? |) || Tablet Rows Tabie 1 Row || || Table 3 Row 2 Tablet Fow2 || || Tables Row 1 Table 2 Flows || || Tables Rows Table 2 Row || || Table2 Row 2 Table 2 Row 4 Description + A segmented table space is divided into equal-sized groups of pages called segments that can contain rows from only one table. DB2 can take locks at the row, page, table (segment), or table space level of a segmented table space. +A partitioned table space contains rows from a single table that are divided into components called partitions. DB2 can take locks at the row, page, partition, or table space level of partitioned table space. For a lock to be acquired at the partition level, the table space must be defined with the LOCKPART = YES option, + A simple table space is divided into pages that can contain rows from different tables. DB2 can take locks at the row, page, or table space level ofa simple table space. + A table space can be defined with a default lock size of ROW, PAGE, TABLE, TABLESPACE, ‘or ANY. IF ANY is specified, DB2 selects the optimum lock size, which is usually a page. + flocks are held at the page or row level and the number of locks that are held exceeds an instalation default, DB2 escalates the locks to the next highest level + Before a lock can be taken at the page or row level, a compatible lock must be taken at the table, partition, or table space level. And before a lock can be taken at the table level, a compat- ible lock must be taken atthe table space level, See figures 2-5, 2-6, and 2-7 for details Figure 2-1 DB2 tables spaces and lock sizes24 Section I DB2 Advanced DB2 concepts Before DB2 can take a lock at one level, it must take a lock at the higher levels. Before it can take a lock at the page level in a segmented table space, for example, it must take a lock at the table level. And before it can take a lock at the table level, it must take a lock at the table space level. Note that row and page locks are considered to be at the same level, so it’s not necessary to take a page lock before taking a row lock. Similarly, partition and table space locks in a partitioned table space are considered to be at the same level. You'll learn more about the locks that are acquired in various situations later in this chapter, lock modes and lock promotion Figure 2-2 presents the six modes of locks that DB2 can acquire. As you can see, DB2 can take a Share (S), Update (U), or Exclusive (X) lock on any lockable object. The Intent Share (IS), Intent Exclusive (IX), and Share with Intent Exclusive (SIX) locks can be taken only on tables, partitions, and table spaces. These are the locks that DB2 takes automatically before taking a lock at a lower level. ‘The mode of a lock determines what the program that owns the lock and concurrent programs can do with the locked data. If, for example, DB2 applies an IX lock to a table space, both the lock owner and concurrent programs can read and change the data in the tables in that space. This lasts until one of the programs acquires a U lock on a page or row, indicating that the data will be updated. But even then, the other programs can read, but not change, the data in that page or row. Before the data can be updated, though, the U lock must be promoted to an X lock. Then, the other programs can’t update the data until the X lock is released. In fact, they can’t even read the data unless they're bound with the UR isolation level as explained in the next figure. But that’s not common.Chapter? Advanced locking concepts The modes of table, partition, and table space locks (ee) eee riety eat eee eed 1S (Intent Share) Read but not change data Read and change data, X, or IX lock IX (Intent Exclusive) Read and change data Read ancl change data or SIX lock S (Share) Read but not change data Read but not change data_—_X lock U (Update) Read but not change data Acquire $ lock and read lock until the lock is promoted data, but can’t acquire a toan X lock U lock SIX (Share with Read and change data Read but not change data__X lock Intent Exclusive) X (Exclusive) Read and change data Read data, but only if the program uses UR isolation The modes of page and row locks ey eee era! ara Pee ee eed $ (Share) Read but not change data Acquire $ lock or U lock, X lock ‘or read data without acquiring a lock U (Update) Read but not change data Acquire $ lock and read X lock until the lock is promoted data, but can’t acquire a toan X lock U lock X (Exclusive) Read and change data Read data, but only if the program uses UR isolation Description * DB2 sets and promotes locks automatically based on the options that were used when the table space and table were defined, the options that were used when the program package and plan were bound, and the processing that’s being performed. + The mode and size of locks that are acquired determine what locks other programs ‘can acquire on the same data and what they can do with that data, * Before data that’s held for update can be updated, the U lock on that data must be promoted to an X lock. That can happen only after all other programs that have locks on the data release those locks. Note + A concurrent program that’s bound with cursor stability (CS) and CURRENT- DATA(NO) can also read data that's held with an X lock if DB2 can tell that the data has been committed. Figure 2-2 DB2 lock modes and lock promotion 2526 — Section 1 Advanced DB2 concepts Bind parameters that affect lock duration ‘The duration of a lock is the length of time that the lock is held, which is ‘measured from the time the lock is acquired to the time it's released. This is, affected by three parameters that are set when a program is bound into a plan or package. Figure 2-3 describes these parameters: ACQUIRE, RELEASE, and ISOLATION For a batch program, you normally use the ALLOCATE option for the ACQUIRE parameter and the DEALLOCATE option for the RELEASE parameter. Then, the table, partition, or table space lock is acquired when the bind plan is allocated, and the lock is released when the bind plan is termi- nated. These options will improve the run-time of most batch programs without degrading concurrency. In contrast, the USE and COMMIT options are better for interactive programs. With those options, a table, partition, or table space lock is acquired when it’s needed and released when a commit occurs. This maximizes concurrency with minimal effect on program performance. ‘The ISOLATION options affect the page and row locks that are acquired by a program, In general, the various levels offer more or less concurrency at the cost of more or less protection from other application programs. The one that should be used for most programs is CS, or Cursor Stability, which should ‘maximize concurrency at the same time that it insures data integrity. When this isolation level is used, read-only page locks are released as soon as another page is accessed.Chapter? Advanced locking concepts Bind parameters that affect table, partition, and table space locks prec eerie) or released ACQUIRE(ALLOCATE) When the plan is allocated ACQUIRE(USE) As they are needed. RELEASE(DEALLOCATE) ‘When the plan is terminated RELEASE(COMMIT) When a commit occurs eu al Use for batch processing, Use for online processing Use for batch processing Use for online processing Bind parameters that affect page and row locks uc Description RR (Repeatable Read) Ensures that your program doesn’t read a row that another program has changed until the other program releases the row, and that other pro- ‘grams don’t change a row that your program has read until your program ‘commits the change or the program ends. RS (Read Stability) Ensures that your program doesn’t read a row that another program has changed until the other program releases the row. Also ensures that other programs don’t change qualifying rows (those that satisfy the search condition specified in your program) until your program commits the changes or ends. However, other programs can insert new rows or update ‘non-qualifying rows. CS (Cursor Stability) Ensures that your program doesn’t read a row that another program has changed until the other program releases the row, but doesn’t prevent other programs from changing rows that your program reads before your program commits changes or ends. This option should be used for most packages and plans. UR (Uncommitted Read) Lets other programs change any row your application reads during the unit of work, and lets your program read any row that another program. has changed even if the change hasn't been committed. This option can be used only with read-only operations and should be used for working with tables that are changed infrequently. Note + The isolation levels are in sequence from the one offering the most isolation (and least concurrency) to the one offering the least isolation. Figure 2-3 Bind parameters that affect lock duration 2728 — Section 1 Advanced DB2 concepts COBOL constructs that affect locking In general, DB2 handles locking and concurrency automatically based on the options that are set when a table space is defined and when a program package or plan is bound, In addition, though, COBOL provides three con- structs that affect locking. These are the WITH clause, the WITH HOLD. clause, and the LOCK TABLE statement shown in figure 2-4. You use the WITH clause to override the isolation level that the plan or package was hound with, The SELECT statement shown at the top of this, figure, for example, specifies the UR isolation level. Note that the isolation level you specify is in effect only for the statement in which it appears. If you request RR or RS isolation, you ean also code the KEEP UPDATE LOCKS clause. This clause causes DB2 to acquire an X lock on the selected pages or rows instead of an $ or U lock. To use KEEP UPDATE LOCKS, you ‘must also code the FOR UPDATE OF clause. The second example in this figure is a DECLARE CURSOR statement that uses the WITH HOLD clause. When that clause is specified, the cursor position is maintained past a commit point, even if the plan was bound with ISOLATION(CS) or RELEASE(COMMIT). Then, you can access the next row in the cursor-controlied table by issuing a simple FETCH statement. Although this can simplify the coding in your COBOL program, it also increases lock duration, which can lead to suspensions and timeouts. A suspension occurs when a program requests a lock that is already held by another program and can’t be shared. And a timeout occurs if the program is suspended for longer than a preset time interval. If you use the WITH HOLD clause, then, you should monitor your program and the programs that run with it to determine whether response times are satisfactory. You can use the LOCK TABLE statement presented in this figure to override the initial lock that DB2 takes. Note that if you use this statement for a table in a simple table space, the lock is applied to the entire table space since individual tables can’t be locked. Also notice that you can lock a single parti- tion in a partitioned table space by including the PART clause, This clause became available with DB2 version 5 and can be used only if the table space is, defined with the LOCKPART = YES option. ‘The mode of the lock DB2 acquires when you use the LOCK TABLE statement depends on whether you inelude the SHARE MODE or EXCLU- SIVE MODE clause and whether on not a lock is already held on the tab partition, or table space. If you request an exclusive lock on a table in a sé ‘mented table space, for example, an X lock is taken on the table and an IX lock is taken on the table space. And if you request a share lock on a table in a segmented table space, an § lock is taken on the table and an IS lock is taken on the table space unless an IX lock is already held on the table. In that case, an SIX lock is taken on the table.