 |
| All System objects that DB2 works with in Z/Os architecture. |
This is one of the DB2 system objects which is being used during normal operation (like to gather information ,etc ) and are totally internal to DB2.We cannot access the Directory tables. There are system databases like DSNDB01, DSNDB06, DSNDB07 on which all these system objects (as shown above)resides on.
DB2 Directory resides in DSNDB01 database and this database contains 5 main Table spaces as below:
SCT02
SPT01
DBD01
SYSUTILX
SYSLGRNGX .
SCT02: contains the skeleton cursor table for plans.When we do a DB2 bind for the SQL statements onto a PLAN, a db2 structure is in SCT02 structure.FREE PLAN removes the removes the table structure.
SPT01: Similarly when we execute a package Bind, db2 causes a structure to be created in SPT01 Table space..When we issue a FREE PACKAGE command, the SKT is removed from the SPT01.
DBD01 (DBD):Contains information about all the DB2 objects defined in the database.DB2 creates and updates DBDs whenever their corresponding databases are created or updated
SYSUTILX (Active Utilities): We run online DB2 utilites as part of normal day work. As each step progresses, its status gets updated and stored in
When the utility finishes normally or is terminated, all information about that utility is purged from SYSUTILX.
SYSLGRNGX (Logs):Tracks the opening and closing of table spaces, indexes, or partitions. This tracking information is associated with RBA.When a DB2 recovery is requested,the required logs are located and used for quick recovery.
Just For information:
DB2 Catalog resides in the DSNDB06 database.
DB2 Directory resides in the DSNDB01 database.
Work files reside in the DSNDB07 database.
2. What is DB2 CATALOG:
When we hear the term catalog,the thing that comes to our mind is 'information being stored/registered' in the z/os system. Exactly the same fact goes for DB2 catalog.
DB2 catalog is nothing but set of system tables like SYSIBM.SYSTABLES, SYSIBM.SYSTABAUTH,SYSIBM.SYSTABAUTH....and lot more of other tables whose main function is to keep information of the DB2 objects that we create like Tables,columns,indexes, primary key,foreign keysViews,Indexes.Lets check an example and have an idea about :
How the Catalog and system tables used in DB2?
Whenever a DB2 object is created, dropped or altered by any DDL statement or whenever control over the object is changed by any DCL statement of sql, an entry is made in the appropriate catalog table.
We can query the CATALOG TABLES to get information, but can not modify these CATALOG tables.
Example: When we trigger a SQL query, db2 checks in catalog tables if the table exists or not. Then it will look for SYSCOLUMNS to check if the column exists or not. Further more it checks the SYSTABAUTH to check the authority of the person firing the SQL statement.
Similarly when we create a table into one table space, every details about the table, ie the Table name, Table space name, name of the database it is residing on, name of the owner is captured and a new row gets inserted into the corresponding tables.
3. What is a DB2 LOG:
As the name suggests, log is something which keeps track of all the changes done to the database.So every transaction (insert, update, delete) performed on the data is logged in the log files.These information comes useful in crash recoveries. A log file has large number of log records in it.A log sequence number identifies the log record in the log file. DB2 writes each log record on to a file and is known as Active log. Once the space is full, these logs are migrated to tape and becomes Archive Log ( so that they can be referred later when needed).
4. What is a Boot strap data set(BSDS):
Contains information which are critical to DB2 , such as name of the logs.DB2 uses these information in doing system restarts.
5. What is a Buffer pool:
We all know about the concept of cache memory.A buffer pool is nothing but cached database page in memory.We can view it as a a virtual storage space for table space or indexes,but this is temporary.When DB2 access the rows of a table,that data page containing that requested row is written to the buffer pool.So next time when the data page is accessed, DB2 eliminates the need to go back to the disk to fetch the row, there by reducing the physical input/output access to the disk.
" For information:::The maximum size of a buffer pool is 1 TB "
6. What is a Resource Limit Facility:
The name is self explanatory."Resource Limit Facility" which means DB2 has some kind of governing system which helps to limit/control the use of DB2 resource. RLF limits the resource usage like bind operations in certain times of the day,to avoid contention; Set warning or error threshold when processing limit might be exceeded for particular dynamic SQL statement and inform the user
7. Work Database:
Simply these are the work files storage, used by DB2 to process SQL statements.( like we see SORTWK1...in dfsort /syncsort). The database for the workfiles is DSNDB07.
Now lets see brief about user defined Table spaces and other DB2 objects!!!
---------------------------------------------------------------------------------------------------
The data structure in db2 consists of Storage group, database, tablespace, tables, index, and index spaces. Among these only the storage group , Table space and index have the physical existence. Rest all are logical concept. Storage group is a physical DASD (direct Access storage device). A table space or an index is associated with one or more of these physical datasets.
Table and database have no actual physical representation.We can imagine a database to be big unit which contain tablespace and indexes. The tablespace in turn contains one or multiple table.
Since a Tablespace has physical existence ,we need to specify the PRIQTY and SECQTY while creating the table space because they actually allocate primary and secondary space in the DASD devices.
What are Object Identifiers(OBID),PSID,DBID in DB2?
When we create a object in DB2 database, those objects are assigned with a identifier and stored in DB2 catalog.These object identifiers are called OBID. Going further a table space or index space are assigned with Page Set Id know as PSID. A database is assigned with Database identifier known as DBID.
DBD thus stores information about database whereas OBD is used to store information about the DB2 objets like TS,Table,Index etc....
What is a page in Db2: Db2 internally continues read/write operation in terms of equal size units known as pages.
The size can vary from 4k,8k,16l,32k. The default page size is 4K.
How many types of table spaces are there in DB2?
Fundamentally the table space is the storage unit for recovery and re-organization purposes; i.e. it is the unit that can be recovered via the recover utility . If the table space is very large , however , recovery and re-organization could take a very long time.
There can be Simple, Segmented, partioned, universal, XML, Large Object Table spaces in DB2.
Simple Table Space are obsolete now a day. It can contain rows of one or more tables. When many tables are defined on a simple table space, one single page contains all the rows from the tables.A simple TS can be created by issuing the CREATE TABLESPACE statement without specifying either the SEGSIZE or NUMPARTS clause.
One reason for having more than one is that stored records for different stored tables can be clustered together in such a way as to improve access times to logically related data.In particular , certain join queries would then be handled more efficiently since the I/O operations would be reduced
Disadvantage of a simple table space is that it unnecessarily locks rows of other tables which are there in that page even when operation is being performed on some other table.
Command to Create a Tablespace
CREATE DATABASE "DBTEST1"
STOGROUP STTEST1
BUFFERPOOL BT0;
COMMIT;
CREATE TABLESPACE "REOT001" IN "DBTEST1"
USING STOGROUP STTEST1
PRIQTY 144000
SECQTY 144000
ERASE NO
BUFFERPOOL BP0
LOCKSIZE TABLESPACE
CLOSE NO
COMMIT;
Segmented Table space: Preferred way to build a table space.A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table. Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets. The Basic Difference between simple table space and segmented Table spaces is ,segmented table spaces do not support any kind of cross table clustering- that is they do not allow records for different stored tables to be interleaved on a single page. Instead , they keep the tables physically separated.
A segmented table space can be built using the SEGSIZE clause. The value indicates the pagesize
in each segment.
CREATE TABLESPACE "REOT001" IN "DBTEST1"
USING STOGROUP STTEST1
PRIQTY 144000
SECQTY 144000
ERASE NO
BUFFERPOOL BP0
LOCKSIZE TABLESPACE
CLOSE NO
SEGSIZE 24;
COMMIT;
We can keep some free pages in segment using the FREEPAGE clause with a value less than that of
SEGSIZE.Defining small tables in a table space with a large segment size could result in wasted DASD space.
If we specify the FREEPAGE to be greater than SEGSIZE, the FREEPAGE will be calculated as
SEGSIZE -1.
Partitioned tablespace: A table space that is partitioned stores a single table.
Partitioned table spaces are intended for stored tables that are sufficiently large. A partitioned table space thus contains exactly one stored table, partitioned in accordance with value ranges of a partitioning columns or column combination. Individual partitions of a partitioned table space are independent of each other in the sense that they can be independently recovered or reorganized.
They can also be associated with different storage groups i.e. it is possible to store different partitions on different devices and thereby spread the table space I/O load.
CREATE LARGE TABLESPACE test01TS IN TestDB
USING STOGROUP TestEX4
PRIQTY 504000 SECQTY 504000
FREEPAGE 0 PCTFREE 0
GBPCACHE CHANGED
TRACKMOD YES
NUMPARTS 40
BUFFERPOOL BPX
LOCKSIZE ANY
CCSID EBCDIC
MAXROWS 255
use of keyword NUMPARTS indicates the partitioned tablespace.
INDEX SPACE : index space is to an index what a table space is to a stored table. However, since the correspondence between indexes and index spaces is always one to one, there are no data definition statements for index spaces; instead, the necessary index space parameters
are specified on the corresponding index definition statements. Thus for e.g. there is no create
index space; instead the index space is created automatically when the corresponding index is created. Like Table spaces ,index spaces can be reorganized and recovered independently.
