Archive for the ‘DBMS’ Category

DB2 advanced concepts

December 20, 2011

Now lets look at some advanced level terms of DB2 product. First comes Buffer Pool which is an extremely useful object for tuning DB2 performance. In fact Buffer Pool is just a region of memory where database keeps indexes and data. Initially pools are small. So it’s a rule of thumb to always tune them during database installation.

Next one is Transaction Logs. Transaction Logs are of big importance to any database. They keep track of transactions happening in database and in case of failure allow to “roll-forward” to particular point in time. In other words if you have full database backup made at T0 and transaction logs from that point and something bad happens at T1 then you can always recover your database to any point in time between T0 and T1 by recovering from full backup and then roll-forwarding.

Large Objects or LOBs are objects like video, audio, scanned documents, etc. It’s a type which is available during table creation. One special thing about LOBs is that they are logically created as a column of table which is stored in particular tablespace but are physically located in separate so called large object tablespace.

Structure of DB2 database

December 16, 2011

Now lets talk about databases which is a more interesting topic for developers. If you show up here then you apparently know that databases consist of tables. But there is another slice between them called schema. Schema is just a convenient aggregator of database objects. If your database is bigger than 10 tables then logically separating them into different schemas would probably be beneficial for you.

Next term is view. Using views you can look at your tables from different angle, so to speak. So if you have your table in place and don’t want to change it but it’s more convenient sometimes to use it with different structure from SQL-query then use views.

And a last but not least and probably a most interesting feature is index. Indexes help database to increase performance when searching for data programmer requests. Instead of comparing all fields you indicate in query to find necessary data, database generates indexes for each row in advance, then generate index from query when you run it and compare indexes to find requested data. Basic idea here is to to compare two numbers instead of strings and/or several numbers. It’s just faster.

General view of IBM DB2 architecture

December 16, 2011

No one would argue that DB2 is one of the leading (R)DBMS products in the market. As any other powerful software product it has complex architecture. Here I’d like to introduce some basic terminology of it in series of posts because sometimes this terminology can become somewhat vague.

Lets kick off by explaining some general view of DB2. Each IBM DB2 installation has following basic levels of architecture:

  • Instance can be understood as a completely independent environment with it’s own security configuration, resource allocation and contains databases and partitions isolated from all other instances. Each instance has its own system processes which manage data. Instance may contain several databases.
  • Database is a most familiar term. It’s a logical unit which holds your data. It has complicated structure which we will explain later on. Database can reside in one or more partitions.
  • Partitions (or nodes) is a way of creating DB2 database cluster for the sake of higher performance. You can split your database onto several servers where each of them will have their own chunk of data. Since more servers means more CPU cores, memory and disk I/O it’s a natural way of scaling DB2. Particular storage resource is called Container.
  • Database is then mapped onto several Tablespaces. Tablespace allows you to manage how database tables are held on your storage resources. For example you can hold your data in files as well as on raw hard drives (which is faster). In addition it’s possible to put frequently used data on SAS hard drives and rarely used on SATA by means of creating two different tablespaces. On top of that you can set up a different page size for each of your tablespaces.

Here is the basic idea of what DB2 represents from the view of systems administrator. To better understand a folding of different levels please refer to this well-known among dbtwoers picture: link.