Posts Tagged ‘db2inst1’

TBSM 6.1.1 database installation gotchas – real disk space requirements

Wednesday, May 1st, 2013

Have you ever tried to install TBSM 6.1.x on non-single partition disk space? It’s easy, you usually go for it on your demo VMware image, and it can be a production case too. It usually looks like this after all the installations (in this case we have logical volume to extend at any time by adding some more disk space):

tbsmdbdisk

Picture 1. Regular disk partitions layout on Unix-alike system, easy and boring.

 

Piece of cake. Let’s try something harder. Typically AIX systems storage is extensively divided on numerous disks in farm or Power System storage. Nevertheless a similar scenario can be easily emulated on your VMware box, assuming you create number of independent disks during your virtual OS installation, like in the example below:

Picture 2. Creating non-single-disk-based disk space in virtual Linux.

Picture 2. Creating non-single-disk-based disk space in virtual Linux.

The goal is to achieve the following setup. I have home directory on one disk (sda5), opt directory (historically for “optional” packages, it’s default directory for most of Tivoli packages on Linux platform) on another disk (sda2), tmp directory on sda6 and root directory on sda1. See this:

tbsmdbdisk03

Picture 3. 6 successfully installed virtual disks on Linux

Now let’s follow the available disk space degradation as we install next components of TBSM. We start from DB2 9.7 installation.

Picture 4. Disk space after installation of DB2 database manager code.

Picture 4. Disk space after installation of DB2 database manager code.

It looks quite obvious, DB2 default installation directory seats in /opt directory, which mounts sda2 disk. You can check for exact space value taken by DB2 database manager, by running this command:

Du –m /opt/ibm/db2/V9.7

871

If you compare to disk space consumed by DB2 instance itself, you’ll see consumption in /home directory, this is because I installed default DB2 instance on Linux platform, called db2inst1 which uses db2inst1 user to run and this user’s home directory to store the data files. Here’s how fresh DB2 instance impacts the available disk space on my Linux vm:

tbsmdisk05

Picture 5. Disk space after configuration one default DB2 database instance.

 

So the disk space taken by DB2 instance files (and don’t forget you have to create fenced user for running stored procedures and at least one administrative user for managing all instances, so db2fenc1 and dasuser1) is about 80 MB.

I’m sorry for taking you so far to this place to tell you this. It’s not going to work with your default TBSM installation. Why? It’s too little space for DB2 instance data files. If you continue with TBSM database installation, you’ll see these sizing options:

Picture 6. TBSM database installation size options.

Picture 6. TBSM database installation size options.

According to the documentation for TBSM 6.1, available here, You need to secure the following disk space:

  • Small – 3G of disk space
  • Medium – 6G of disk space
  • Large – 10G of disk space

Well, it’s maximum limits, someone may say. If you plan for running just a simple demo, it’s not going to break anything. This is true, however would you assume that you needed to monitor disk space consumption especially because of that? If yes, good for you, skip it. If not, consider this. Why? Let me show you what’s going to happen and why you don’t want to use your default settings during TBSM installation, related to data paths and log paths

Let’s assume we simply continue the installation with defaults. Database configuration, especially transaction log files size for TBSM database won’t accept your disk space offer. This is how your installation is going to finish, if you continue with it like presented above, it will simply fail:

Picture 7. Example of failure message during TBSM database installation if disk space is too low.

Picture 7. Example of failure message during TBSM database installation if disk space is too low.

 

The same for TBSM Metric Marker tables and demo tables. If you go to  /opt/IBM/tivoli/tbsmdb/logs/db2_stdout.log log file, you’ll read:

CREATE TABLESPACE TBSMSCR16KTS PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 100 M BUFFERPOOL TBSMSCR16KBP

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0968C  The file system is full.  SQLSTATE=57011

 

Off the record, after that entry in the log you can see that the installer tries to continue executing the DDL script without validation of available disk space based on the first failure occurrence, what starts all series of unfortunate events in consequence and doesn’t allow your installation succeeding at the end.

So it looks bad. Let’s see the disk space now:

Picture 8. Disk space after failing installation.

Picture 8. Disk space after failing installation.

Well, it doesn’t look that bad now after it failed, we still have some space available, it means that creating the tablespace must have used up all the space first, then failed and then rolled-back and so it released some of taken disk space after all. But what happened? Well, after TBSM database migrated to DB2 from Postgres in version 6.1.0 (previous TBSM 4.2.1 – there is no TBSM 5.x – was using Postgres 8.x) it became exposed to all those challenges and gains coming from the fact. In order to understand it step by step we need to get closer to few DB2 design assumptions. Here’s couple of settings which should draw your attention:

Picture 9. Default buffer pool settings for TBSM database.

Picture 9. Default buffer pool settings for TBSM database.

So, this is buffer pool setting first. Each row in each table is being cached in memory in objects called pages (number of rows) every time when DB2 has to read that data from external memory, means disk. Also, if data is to be written to disk from buffer pool to table space, you can calculate the maximum amount of data in 16K buffer pool will be 48MB and 32MB in 32K buffer pool. This is not something dangerous to your installation yet. It tells you about potential RAM memory consumption in future when you launch TBSM into production mode. But the screen tells you also about table spaces being created for TBSM database, let’s take a closer look on them.

Note, if you actively use TBSM Metric Marker and Metric History databases, they have their separate settings and disk and memory consumption rates.

From this command:

CREATE TABLESPACE TBSMSCR16KTS PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 100 M BUFFERPOOL TBSMSCR16KBP

You know that TBSM database has automated storage management which means that database manager creates new data containers if only needed. It is not System Managed Space (SMS) type of table space or database managed space (DMS) type. It means maximum capacity data can reach is defined by storage size while creating TBSM database, determined by the path. In the screenshot below you can see that TBSM database path is <default>.

Picture 10. Sample configuration of TBSM database, it shows default database path.

Picture 10. Sample configuration of TBSM database, it shows default database path.

What <default> is? Well, go to your DB2 command line, and run as DB2 instance owner the following command:

db2 get dbm cfg | grep DFTDBPATH

By default it is instance user’s home directory, in my case: /home/db2inst1.

It means your database will grow as long as it hits the storage limits, it means endlessly until some free disk space remains. It’s something good to remember, if didn’t realize or didn’t have clear answer. It means you don’t want to select <default> during your TBSM database installation, you rather want to check on other disks space and allocate TBSM data files there.

Next, lets see the transaction logs, like below. These steps lets you define how many and how big logs can be created for you TBSM database. Again – for TBSM Metric Marker and history databases it’s totally a separate story. So by default you define 10 primary logs and 2 secondary logs, 16000 4K pages big each, means up to 12*16000 = 192000 4K pages, means 768 MB of data max. Note, on Unix and Windows platform, the default log file size, both for primary and secondary is 1000*4K and range 4 – 1 048 572 (always *4K page size). This space gets allocated when only your database activates. It means you need to have this space available on your hard disk immediately when you start your database manager. Again, logs, similarly to data files, by default utilize the default log path name, see the screenshot below. By default it means it all goes to /home/db2inst1 directory again:

Picture 9. Default buffer pool settings for TBSM database.

Picture 11. Transaction log for TBSM database configuration snippet.

What to do then? Again, if you miss space in your /home directory mount, select another value for Log path name. Be knowledgeable,  know what it all means to your installation. Take the installation hardware requirements seriously and monitor the usage.

Last but not least, TBSM DB directory which is used to store TBSM DB DDL files, executables to recreate TBSM DB and jars etc. takes it piece of cake too, it’s 160 MB declared by the installer, and you can see it by Summary step:

Picture 12. Summary view

Picture 12. Summary view

 

Don’t forget about temporary disk space the installer takes and returns, but it must be available for installation time. The installer is flexible and will look for 200 MB in /tmp or /home directory of the user who runs it, in our case db2inst1 home directory (I have only 170 MB available in /tmp):

Picture 13. /tmp disk space is too low

Picture 13. /tmp disk space is too low

Conclusions.

I’ll need more than 1 GB disk space that I assigned to /home directory to install TBSM database successfully and the Install Guide is not specific about that.

This is because by default my data logs and transaction logs go to /home/db2inst1 directory and this is because I didn’t simply create one single disk space for all directories, which can be a real case in production environments too. Additionally all the temporary files of the installer will be copied there for TBSM DB installation time.

So what is the real hard disk space requirement for TBSM DB installation?

/home

a) at least 1x768MB for transaction logs (10 primary, 2 secondary, 16000 4K pages each, all in one TBSM database – means you don’t create separate TBSMHIST db)

b) 80 MB for fresh db2 instance installation

c) 200 MB for temporary files in case you don’t have space in /tmp

d) suggested 3 GB for up to 5000 service instances

Total: 4048 MB minimum in /home

 

/opt

a) at least 160 MB for tbsmdb installation in /opt/IBM/tivoli/tbsmdb

b) 871 MB for DB2 database manager in /opt/ibm/db2/V9.7

Total: 1031 MB minimum in /opt

 

Keep in mind this is still before installing TBSM dataserver, dashboard server, JazzSM, XMLtoolkit and ITMAgent for TBSM or embedded Netcool OMNIbus with EIF probe.

So, if you’re lucky and have one single / root partition only for all your files in your Linux or Unix box, prepare minimum 5,1 GB disk space for TBSM database preparation total. If you only get tempted to create TBSMHIST table, it will be next 768 MB for separate transaction logs. You can lower number of logs or decrease single log file size to accommodate it though. You’ll be 200 MB ahead if you secure enough space in /tmp directory (I put it 500-1000 MB to be safe usually).

That’s all for now, thanks and see you next time.

 

 

TBSM database – part 1

Tuesday, February 12th, 2013

I just figured out how to speed up my process of preparing DB2 for TBSM database installation and wanted to share with you guys. Today I wanted to share something rather universal, not just for TBSM use, so people from other areas may enjoy too. It’s command line DB2 instance creation process with saving time on not having to take few other actions more by running the following commands in right order and with right parameters.
This is Linux ready, with some modifications will be fine for other UNIXes.
You do all as root.

1. Create groups – you need them first, standard names:
groupadd db2iadm1
groupadd db2fadm1
groupadd dasadm1

2. Create home dirs – let’s assume we run home at /home:
mkdir /home/db2inst1
mkdir /home/db2fenc1
mkdir /home/dasusr1

3. Add users (standard names, bash assumed existing, remove -s flag and value if not available at your box)
mkgroup db2iadm1
useradd -g db2iadm1 -s /bin/bash -d /home/db2inst1 db2inst1
mkgroup db2fadm1
useradd -g db2fadm1 -s /bin/bash -d /home/db2fadm1 db2fenc1
mkgroup dasadm1
useradd -g dasadm1 -s /bin/bash -d /home/dasusr1 dasusr1

4. Setting passwords:
passwd db2inst1
db2inst1’s New password:
Re-enter db2inst1’s new password:
passwd db2fenc1
Changing password for “db2fenc1”
db2fenc1’s New password:
Re-enter db2fenc1’s new password:
passwd dasusr1

5. Reconfirm all the passwords, different security policy for new password may apply for different distribution installations, example:
su – db2inst1
db2inst1’s Password:
[compat]: You are required to change your password. Please choose a new one.

db2inst1’s New password:
Re-enter db2inst1’s new password:

6. Ensure home directories are owned by right users, it will help with setting up environment variables automatically for DB2 instance by the installer:
chown db2inst1:db2iadm1 /home/db2inst1/
chown db2fenc1:db2fadm1 /home/db2fenc1
chown dasusr1:dasadm1 /home/dasusr1

7. Configuring the DB2 instance for TBSM DB – remember of passing port number, it will save you time later by configuring TCPIP and service name automatically now:
[root@yourmachine]:\ # cd /opt/IBM/db2/V9.7/instance
[root@yourmachine]:\ # ./db2icrt -a SERVER -p 50000 -s ese -u db2fenc1 db2inst1
DBI1070I Program db2icrt completed successfully.

[root@yourmachine]:\ # ./db2iauto -on db2inst1
[root@yourmachine]:\ # su – db2inst1

Few postinstall steps, do all as DB2 instance administrative user this time:

8. Check on DB2 variables. DB2COMM is one you need to be set up for reaching the instance from TBSM later on:
[db2inst1@yourmachine]: # db2set
DB2COMM=tcpip
DB2AUTOSTART=YES

9. Check on the license expiration date, make sure it’s Permanent before you roll out your TBSM to production:
[db2inst1@yourmachine]: # db2licm -l
Product name: “DB2 Enterprise Server Edition”
License type: “Trial”
Expiry date: “10/01/2012”
Product identifier: “db2ese”
Version information: “9.7”

Product name: “DB2 Connect Server”
License type: “Trial”
Expiry date: “10/01/2012”
Product identifier: “db2consv”
Version information: “9.7”

10. Check on the code level if any fixpacks need to be installed before TBSM (in TBSM 6.1 minimum is 9.5 FP3 or 9.7 FP3)
[db2inst1@yourmachine]: # db2level
DB21085I Instance “db2inst1” uses “64” bits and DB2 code release “SQL09076”
with level identifier “08070107”.
Informational tokens are “DB2 v9.7.0.6”, “s120516”, “IP23321”, and Fix Pack
“6”.
Product is installed at “/opt/IBM/db2/V9.7”.

11. Check on whether your DB2 instance is up and running:
[db2inst1@yourmachine]: # db2start
SQL1026N The database manager is already active.

12. You can make sure whether any databases were installed successfully at any time by running this command:
[db2inst1@yourmachine]: # db2 list db directory
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

13. Create sample db manually, connect to it, disconnect and drop – the simplest test ever, in order to make sure all went ok

14. You’re ready to install TBSM

There’s bunch of disk requirements for TBSM database installation in DB2 specifically, but I will describe it next time.
Thank you.