Meet the DBD family: DBID, OBID, PSID, etc…

What a family! Misunderstood, avoided, misquoted, and sometimes even feared. Everyone has some opinion about them yet few people really take the time to understand them. So, I decided it was time to give them the opportunity to tell their side of the story in their own words. You will find out the truth about all of the "ID's" in this in-depth, personal interview with the entire DBD family. Now, as I am sure you are aware, having a conversation with someone in the DBID family can be difficult to say the least. They belong to a very exclusive group called the DBDs and are quite different from any other ids you might come across in DB2 and they tend to be a challenge to manage sometimes. In addition, there can be all kinds of language barriers. I mention this because what I report here may have to be my interpretation of this conversation with these object identifiers.

Who are the members of the DBD family and why do we even care about them? There certainly seem to be a lot of them. Most folk have heard of or may even be familiar with the two or three most popular identifies: DBID, PSID, and OBID so let's start with them.

The senior member of the family, DBID, is always the first to arrive when a new DBD is being created. The other ids cannot even exist until it's present. DBID is an acronym for database object identifier and is associated directly with the creation of a database. When the database object is created, it is assigned a DBID. A DBID, like all the object identifiers we will be discussing, is a two byte hex value stored in the column called DBID in SYSIBM.SYSDATABASE, a table in the DB2 catalog. Theoretically, you should be able to create a maximum of 65,535 (or x'FFFF") databases. However, DB2 has reserved the first 264 254 database identifiers for its own use. This still gives you a maximum that you will probably never come close to using: 65,271 databases (DBIDs) in a single DB2 subsystem. But what abut this DBD thing.

Once the database is created, an internal DB2 structure called the DBD (database descriptor) is also created. Although we can query the catalog tables that make up the table space DSNDB06.SYSDBASE to find out what objects we have, DB2 navigates its objects using the DBD in the directory, DSNDB01.DBD01, not the catalog tables. When an SQL statement is executed, the DBD, or DBDs if there are multiple tables referenced in the same SQL statement, containing any tables' referenced in that SQL statement are loaded into the EDM pool (or rather the EDMDBDC cache in V8). In case you might have forgotten, the EDM is the environmental descriptor manager and the EDM pool is the caching mechanism the EDM uses when processing its objects.

Once the user database and its corresponding DBD have been created, other objects can be created using that database (or within that database). The first object that needs to be created, either implicitly or explicitly, is the table space. With the creation of a table space comes two (not just one) additional identifiers. A table space has one id used to indentify the page set, the PSID or page set identifier, and a second id identifies the file, the OBID or object identifier. The very first table space created within a database will have an OBID equal to 1 and a PSID equal to 2. If a second table space was immediately created, before any other objects are created in this database, the second table space would have an OBID of 3 and a PSID of 4. A single user database could, again in theory, contain up to 32,767 table spaces, or ½ the hex value x'FFFF". Of course, to reach this value you could not create any tables, indexes, or relationships in this database making it a fairly useless object.

Let's keep creating objects in our newly formed user database. Currently we have two table spaces. Next, let's create a table. It really doesn't matter to the ids in which table space the table is created. The next object created in our user database will be assigned the next sequential identifier available regardless of the table space in which it is created. If we create a table, a type record, is only assigned on an OBID. Therefore the table in our example would be assigned an OBID of 5, the next available identifier in this example. If we then went on to create an index on that table, then we are back to assigning multiple identifiers again. Indexes, like table spaces, also have two identifiers. The first identifier assigned is for the fan set and is another OBID. The second identifier is for the index space page set, the ISOBID or index space object identifier. This is this VSAM portion of the index. This newly created index would have 6 assigned to the OBID and 7 assigned to the ISOBID.

BTW, we may run around calling objects table spaces and indexes, but internally these objects are referred to as page sets. Each page set, regardless of the type, is just a collection of VSAM LDS data sets.

I have been mentioning the theoretical maximum of objects that can be defined in a database as 65,535. This is the total number of all object identifiers that could potentially be assigned within a single database. Remember that each table space and each index account for two identifiers, one for the file or fan set and one for the page set and a table and relationship each count for one. If that total number seems like a lot of objects, it is. It would be difficult, to say the least, to create a DBD that has that many identifiers in a single database. One of the primary reasons would be the size of the DBD being created. The database has that internal twin called a DBD that contains the internal information that DB2 needs to satisfy the access path requirements for an SQL statement. All of these different ids previously discussed take the form of OBDs or object descriptors. Every object created within a database causes the DBD to increase in size. The larger the DBD, the more prohibitive it becomes to load it into the EDM pool. Some of this problem may be taken care of in DB2 V8 because in V8 DBDs will no longer share the pool of the other internal objects (SKCT, SKPT, CT, PT, and cached dynamic statements). In V8, DBDs are stored in the EDMDBDC cache, while only SKCT, SKPT, CT, and PT will only be stored in the EDMPOOL cache. And for completeness, the dynamic statement cache now uses EDMSTMTC. However, let's get back to our DBD discussion.

There is a restriction to size of a DBD. A single DBD cannot exceed 25% of the size of the EDM pool. If you need to determine the size of a DBD, the simplest way is to issue a DB2 -DISPLAY DATABASE command. The DSNT362I message will contain the database name, status, and size.

All of theses object identifiers creep into your DB2 life constantly. For example, let's say you want to use DSN1COPY to copy data into a different subsystem. You would use the OBIDXLAT option of the DSN1COPY utility to move the data and translate the identifiers. You specify the identifiers on the SYSXLAT DD statement in pairs. The source and target DBID is first, followed by the source and target PSID for the table space, and finally a list of all source and target table OBIDs. For existing objects, this information is fairly easy to locate. You just need to run a few queries against the DB2 catalog.

To obtain the DBID and PSID, run:

SELECT DBNAME, DBID, NAME, PSID
   FROM SYSIBM.SYSTABLESPACE
      WHERE DBNAME = "database-name"
        AND NAME   = "table_space-name"

To obtain the table OBIDs, run:

SELECT NAME, OBID
   FROM SYSIBM.SYSTABLES
      WHERE TSNAME  = " table_space-name"
        AND CREATOR = " creator-name"

These ids will pop up all over DB2. Another example of DB2's use of them is mapping objects to their real time statistics. You will find them in your log records, IFCIDs, error messages, and imbedded throughout your data, as noted earlier. And if you want to have a lot of fun dealing with DBDs and all the ids that are contained in them, get the DBD out of sync with the database definition in the catalog. Then you get to play with the utilities DSN1PRNT, REPAIR DBD TEST, REPAIR DBD DIAGNOSE, and REPAIR DBD REBUILD. However, tread very cautiously and bring lots of friends If you ever have to fix a DBD. It is not a task for the faint of heart.

Now you know why it is important to have the correct identifiers in the page set, what those identifiers mean, and where to find them. Go forth and have fun identifying.

Since this has somehow turned into a technical discussion about DBDs anyway, I have just a few last comments about it. Every time you CREATE, ALTER, or DROP an object you are updating a DBD. That means you are getting an exclusive (X) lock over the entire DBD. That means you have an X lock over every table space, table and index created within the database represented by that DBD. No one else will have any access until you release you X lock. Imagine if you were creating a new index over a 100 million row table, as an example. The index create does not complete until every key/RID combination for every row in the table has been created. All of that time you, because of your lock, no one can do anything that uses that DBD. No one can run SQL against a table in the DBD. No one can run a utility or do any type of bind, all because you have a lock. If you do a lot of creates and alters, you might consider spreading your object across more databases to minimize the contention.

This entire discussion was supposed to take place in a less formal environment next to a pool (smile). However, we had an unexpected turn of event. A DBD, part of the DBID family, suddenly grew and attempted to take over more than one quarter of the pool. This action failed causing a great deal of commotion and we were asked to leave, never having the opportunity to even start our interview. However, off camera we were told by a lesser object identifier that the size issue was just an excuse to cancel the interview. It seems the DBD was still upset over an incident dating back to 1989 when a couple of guys names Mike and Pat made up a song about DBD that he thought was a bit unflattering. What a shame. Perhaps someday, when feelings are mended and the DBD reduces its size, we will try again to complete our interview.

I would be remiss if I did mention where you can find out more detail on DBDs and all of the identifiers discuss here. Give you copy of the DB2 Diagnosis Guide and Reference, LY37-3201.

Corrected April 14, 2009

Thanks for stopping by…

 

---

Well, that's it for now.

Don't forget to check out my Grandson Anthony's blog

And as always, thank you for stopping by and thanks for your continued support
Willie

** Do you have a suggestion for a future blog post, then Click Here

P.S. Check out the index I'm building of all of my past blog post broken down by categories, or at least the way I think of categories. Index last updated July 24, 2007 and goes through July 1, 2007. Just click here to view the "under construction" index and be sure to let me know if you think it will be of any use once it is completed.

My usual disclaimer: "These comments are my own personal opinions only and do not necessarily reflect the positions or opinions of my employer (IBM) or their affiliates. All comments are based upon my current knowledge and my own personal experiences. You should conduct independent tests to verify the validity of any statements made in this blog before basing any decisions upon those statements. In addition, any views or opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine."

IBM, DB2, pureXML, System z, and z/OS are trademarks or registered trademarks of International Business Machines Corporation. For a list of additional IBM trademarks, please see www.ibm.com/legal/copytrade.shtml. All other company, product, or service names may be trademarks or registered trademarks of others.