Table Spaces And Locking Levels, Part 1
by Bonnie Baker, Bonnie Baker Corp.

This article was originally printed in DB2 Magazine.

Yes, programmers, you do acquire table space and table locks.

While I was talking with a programmer about locking contention and overhead one day, I mentioned table and table space locking. She looked at me and in all innocence said, "Oh, we don't ever use table space or table locking here. We only use page locking."

Buried in that response was the seed of a column. Why? Everyone who uses DB2 for z/OS (MVS or OS/390) acquires table space and table-level locks. And partition-level locks. And, well, lots of levels of locks. I set the idea for the column aside.

But, in one of my prior columns, "More Joys of Commitment," I made a comment about partition-level locking and the use of the LOCKPART parameter in CREATE TABLESPACE DDL. I received one particularly insightful email about that comment. And that made me take the time to finally write this column.

We must start at the beginning. To fully understand the issues surrounding locking levels, we must first understand the difference between a database and a table space. We'll then look at the three basic types of table spaces.

Next time, in Part 2, we'll examine the types and levels of locks possible with each. Finally, in Part 3, we'll learn about timeouts, deadlocks, lock escalation, and lock upgrades, as well as many of the program logic and performance problems affected by these functions within DB2.

Level 1: DATABASE

The term database is often misused. For example, I hear folks saying database when what they really mean is table. In DB2, a database is more of a grouping concept than a physical entity. Just as the word neighborhood is a grouping concept for a bunch of houses, a database is a grouping concept for a bunch of table spaces, tables, index spaces, and indexes.

If a real estate entrepreneur decides to buy some land and build some houses, she must first decide on a name for the new development. The name can then be incorporated, the land purchased, architectural drawings commissioned, permits pulled, and houses built. All under the official name "Happy Acres." Happy Acres is the database in DB2 that lets us incorporate, establish an official DBD (database descriptor), purchase space (table spaces and index spaces), issue permits (grant authorization), and so on. To mix metaphors, the database name is the umbrella under which all of these things stand.

When a database is created, a numeric identifier is assigned. This identifier, the DBID, is used to begin a chain of information, called the database description chain (or DBD), about all of the objects that will be created in the database. As each object (table space, table, index space, and so on) is created within the database, the new object is also assigned an identifier: an OBID if the object is a tablespace or a table and an ISOBID if the object is an index space designed to hold its index. The DBD chain lengthens as objects are created and their identifiers and object information are stored in it. The DBD chain is stored in a database in DB2 called the Directory (DSNDB01).

The DBD chain must be resident inside DB2 whenever any object in the DBD is "in use." If it's not already resident, the chain is retrieved from DSNDB01 and brought into DB2. In version 7, the chain is stored, while resident, in the EDMPOOL, the same space used to hold package and plan segments.

In v.8, the resident chain has its own area of memory, called the EDM DBD CACHE. To ensure that the objects are not modified or dropped while in use, the DBD is locked. This database lock most often affects the DBAs who are unsuccessfully trying to issue ALTERs or DROPs of objects in the chain. Putting too many objects in a single database can essentially put a DBA out of the business of object manipulation. Just as our entrepreneur would not build a million houses in Happy Acres, neither should a DBA create all objects in a single database. Conversely, the developer probably wouldn't put just one house in Happy Acres either.

Level 2: TABLE SPACE

Okay. Now we're ready to purchase some land. Once a database is created and the DBD is established, the next step is to create a table space. Just as we purchase a piece of property with the knowledge of what we plan to build on the land, we create a table space with the idea of what we're going to put into it. Table spaces are created to hold tables. We must know the size of the tables, the lock size needed, and so on. The creation of the table space causes a file allocation (unless the build is deferred — but that's another topic).

Generally, when we create a table space, we plan to create a single table in it. But in some applications, the DBA chooses to create many tables in a single table space. Instead of a single house on our land, we end up with condominiums! There are some negative ramifications of this practice. Utilities, such as LOAD, RECOVER, and REORG, are run at the table-space (or partition) level, not the table level. And there are many parameters that apply to the entire table space and all tables in it. The granularity of these parameters (pctfree, lock size, and so on.) is lost when too many tables are put into a single table space. However, by putting tables with similar behavior patterns in the same table space, file allocations, opens, and closes are reduced.

There are three types of table spaces:

• Simple. In the early days of DB2 on the mainframe, there were only two types of table spaces; simple was one of them. You could put multiple tables into the simple table space, but there was no way to automatically segregate the rows of one table from the rows of another. Therefore, rows of two (or more) tables could be interleaved on a single page. When DB2 used a table-space scan to access the rows of one table, it actually had to read all of the rows of every table in the space. Maintaining the cluster order of the tables was difficult, if not impossible. Even if there was only one table in the table space, the performance wasn't as good as it could be for inserts or mass deletes. IBM recommends that you no longer use simple table spaces.
• Segmented. DB2 V2R1 introduced a smarter type of table space. Today, a new parameter (SEGSIZE) allows the DBA to allocate a second type of table space, one with pages grouped by segment. And one segment can only belong to one table. A table-space scan can now identify, at the segment level, which segments to scan; a table-space scan is actually a table scan. Oddly enough, even if there's only one table in the table space, the segmented table space is better because the information needed for insert logic is twice as robust. Also, rows can be mass deleted by merely maintaining entries in a space-map page rather than accessing, locking, and logging the maintenance of each individual page and row. And there are other benefits of the segmented table space; for example, the image copy utility recognizes which pages are active and which are not, and only backs up the active ones.
• Partitioned. The other type of table space that has been around from the beginning is the partitioned table space. Because DB2 datasets are really VSAM files and because the maximum size of a VSAM file allocation back in those days was 4GB, there needed to be a style of table space that could control more than one allocation gracefully. The partitioned table space allows the DBA to control the fragmentation of data when the table size exceeds 4GB.

In our analogy, the three table-space types represent the architectural concepts of the types of houses we are going to build in Happy Acres: basic ranch-style houses, three-story colonials, and condominiums with multiple towers. In other words, we have three basic architectural drawings and, with minor modifications to each, we can build many unique structures in our new subdivision.

One-Third of a Conclusion

How is locking handled in these three different types of table spaces? That question will be answered in the next two issues of the Pipeline Newsletter.

Bonnie Baker is a consultant and educator specializing in application performance issues on the DB2 OS/390 and z/OS platforms. She is an IBM DB2 Gold Consultant, a five-time winner of the IDUG Best Speaker award, and a member of the IDUG Speakers' Hall of Fame. She is best known for her series of seminars entitled "Things I Wish They'd Told Me Eight Years Ago" and the Programmers Only column in DB2 Magazine. You can reach her through Bonnie Baker Corp. at 813-876-0124 or bkbaker@bonniebaker.com.

RESOURCES

"More Joys of Commitment," DB2 Magazine, Quarter 1, 2003