Many people have a misconception that while running the SQL queries DB2 uses the catalog tables to get the information about the tables involved in these SQL queries, but DB2 uses an internal DB2 structure called Database Descriptor (DBD). 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, the EDMDBDC cache in V8. The DBD is made up of one more of the following internal identifiers DBID, OBID, PSID & ISOBID. Let us discuss in detail about each one of these identifiers.
DBID is the short name 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 is a two byte hex value stored in the column called DBID in SYSIBM.SYSDATABASE, a table in the DB2 catalog. We should be able to create a maximum of 65,535 (or x'FFFF") databases. DB2 has reserved the first 254 database identifiers for its own use. Hence the maximum number of databases that we can create in a single DB2 subsystem is 65,271. Once the Database is created its associated Database Descriptor (DBD) is also created.
After the Database and its associated DBD is created, the first object that needs to be created is the Tablespace, with the creation of a table space comes two 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 tablespaces, 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 useless object.
Now, if we create a table in any one of the above two tablespaces, irrespective of where the table is created it will get the next available sequential identifier and the table gets only one identifier called OBID and it is given the number 5 in our example. If we create an index on this table then again it gets two identifiers called OBID and ISOBID (Index Space Object Identifier), it gets an OBID of 6 and ISOBID of 7.
The Database and Table which are the logical entities have only one internal identifier whereas the Tablespace and Indexspace which exist physically have two identifiers. One for the file or fan set and one for the page set and a table and relationship. As the number of objects in the database increases the size of the DBD also increases and more space is required for it to be loaded into the DBD Cache. Also there is a restriction to the maximum size of a DBD, a single DBD cannot exceed 25% of the size of the EDM pool. The size of the DBD can be determined by issuing –DISPLAY DATABASE command. The DSNT362I message will contain the database name, status, and size.
I think we have discussed about all the DB2 internal Identifiers in short, now let’s examine a problem with the OBIDs on which I had worked recently.
The problem is: What if I encounter THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED error for any database even if the number of objects existing in the database is not too many
A couple of days back another DBA in our company asked for my help in resolving one problem for which he has encountered SQLCODE -497. The explanation for this SQLCODE is as follows
-497 THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE database-name
Explanation: The SQL statement cannot be executed because an internal identifier limit has been exceeded for the database. The cause of this error is due to one of the following:
1. On a CREATE DATABASE statement, the limit of 65279 DBIDs has been exceeded.
2. For all other statements, the limit of 32767 OBIDs has been exceeded for that database.
The DBA has encountered the error while trying to create a table. He told me that there are only 1400 tables in that database and he is not sure how the OBID number has reached the maximum limit.
Remember from our discussion the OBID number is not only for the tables it is there for the Tablespaces and Indexes also, the total OBIDs of all these objects in a Database should never exceed 32767. I told him the same and he said there aren’t so many indexes and Tablespaces also. I told him that there is one more factor also which makes this OBID number reach the maximum even with the number of objects existing in the database is no where near that maximum limit but asked him to run the following queries to verify the max OBID number in his Database.
1. select DBID from SYSIBM.SYSDATABASE WHERE NAME=DATABASE NAME; 2. SELECT MAX(OBID) FROM SYSIBM.SYSTABLES WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY 3. SELECT MAX(OBID) FROM SYSIBM.SYSTABLESPACE WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY 4. SELECT MAX(OBID) FROM SYSIBM.SYSINDEXES WHERE DBID= THE DBID WHICH YOU GOT FROM ABOVE sysdatabase QUERY
Then I asked him whether any of the last three queries showed you an OBID close to 32700 and he said yes and he got it for SYSTABLES.
Now I will talk about the other factor which could cause this problem. Let us consider that I have created a database VIKRAM and a Tablespace TBSP1 in it and 15 tables TBL01, TBL02 and so on until TBL15. Also after creation of each table I took an image copy of TBSP1. Now as per our OBID discussion the tables get the OBIDs as shown in the following table.
Now I have dropped the table TBL10 and recreated it but this time it took an OBID of 18 not 12. Then I have dropped TBL10 and did a REORG of TBSP1 and ran a MODIFY RECOVERY DELETE DATE (*) on TBSP1 which deleted all the image copies of TBSP1 and created TBL10, now it took the OBID 12. The point to observe here is as long as there is an entry in SYSCOPY for this TABLESPACE we can’t reclaim the unused OBIDs.
Now coming back to the DBA’s problem, in his system they use a QMF proc DROP and CREATE the same table quite frequently on a daily basis. The proc DROP the table, run a query from another table, reCREATE the dropped table, then insert the query output into the newly created table. This causes the OBID increasing very quickly.
I asked him to run the REORG and delete the image copies by running MODIFY RECOVERY but he told me that in his system it is an audit requirement to keep SYSCOPY info for 3 months.
Also it has been suggested to him to do a MASS DELETE and then INSERT instead of doing a DROP and RECREATE to avoid this kind of issue in future and also suggested him to consider creating only one table per Tablespace.
Also he has been suggested to run a REPAIR DBD DIAGNOSE followed by a REPAIR DBD REBUILD, but his system is not current with some of the PTFs and his REPAIR DBD DIAGNOSE encountered an error for the field OBDRELTH, which is a known issue and he has to apply the PTF .
