DB2 Tools

IBM calls fixed to its database product Authorized Program Analysis Reports (APARs). These fixes are distributed in FixPaks.

You can open a DB2 command window by issuing the db2cmd command. The DB2 command line processor (CLP) operates in interactive or non-interactive modes. In interactive mode, you can enter commands directly. In non-interactive mode, you must precede each command with "db2". You may also need to enclose commands in quotes when using the non-interactive mode of the CLP. The backslash character allows multiple line command, and these lines are terminated with the terminator character. The default terminator character is the exclamation point.

Developers can code functionality in User Defined Functions (UDFs). These functions can be written in C/C++, Java, or SQL PL languages. SQL PL is IBM's extension to the SQL standard.

Now for some miscellaneous information. The db2pd command invokes a statistics tool. A satellite is a DB2 server that is a member of a group of other servers with are similarly configured.

Sharing More Basics

Issuing the db2 command invokes the Command Line Processor (CLP) tool. In the world of DB2, the instance is the environment in which databases run. The DB2INSTANCE environment variable let's you know the current active instance. There is a Database Administration Server (DAS) which lets you administer your database with GUI tools.

DB2 settings are stored in a repository called the DB2 Profile Registry. Note that this has nothing to do with the Microsoft Windows registry. Global variables in the profile registry apply to all DB2 instances. You can issue the "db2set -all" command to enumerate all the DB2 registry variables.

There is a file called the Database Manager Configuration file that stores instance level variables. Database level variables are stored in the Database Configuration file. There are a number of default table spaces created in a DB2 database:

• SYSCATSPACE - holds the catalog (data dictionary)
• TEMPSPAE1 - system temporary space
• USERSPACE1 - default user table space

As mentioned in my first post sharing what I learned, the Enterprise Server Edition of DB2 support partitioning. This partitioning applies to the database level (as opposed to the instance).

Sharing What I Learned

Previously I had thought my next assignment was going to be a in DB2 shop. So I did a lot of reading, and recently started playing around with a DB2 installation on my machine. I thought I would share some of the basic information on DB2 I learned. It is broad in scope. But it is not too deep as I am still very much a beginner.

The current version of DB2 that I am running is 9.5. However DB2 version 8.2 was code named "Stinger". I installed DB2 on Microsoft Windows XP Professional. This is a version that shares a code base with their Linux and UNIX versions. This code base is known as DB2 UDB LUW (Linux UNIX Windows).

The targeted platform for the project I was going to join was the z/OS. The version of DB2 for that platform is a different code base than the DB2 UDB LUW. The version I installed was DB2 Personal Edition. However there is also an Enterprise Server Edition (ESE) that supports database partition feature (DPF).

Back to Oracle

I started trying to learn IBM DB2 because my next project was going to be one backed by a DB2 database. Through some unfortunate set of events, it looks like that project will not be picking me up. Therefore I am going back to the project I have worked on for the last 8 years or so. And that project is all Oracle.

Therefore I imagine I will not be posting as much on this blog any more. Maybe in my last 2 weeks of employment at my current company I shall play around with the sample database I installed. And if I find anything interesting, I will share it here. However I will not be getting to dig into the details of how things work in the world of DB2. It is a shame. Finally after installing DB2 for Windows 3 times, I got to the point where everything seemed to be working. Oh well. C'est la vie.

Perhaps I will write a couple posts on everything I learned from the book Understanding DB2. It was chock full of information for newbies such as me.

Third Time is a Charm

This may be hard to believe. But I uninstalled and reinstalled IBM DB2 on my machine for the 3rd time. And guess what? This time it is looking pretty good.

I chose DB2 Personal Edition. For the DB2 Administration Server, I selected my domain account. It seemed strange that I needed to enter my password for this account though. Is DB2 storing my password somewhere? What if my password changes? I certainly hope this does not break the DB2 database.

When the install was complete, it reminded me to use port number 50000 to connect to the DB2 instance. I don't think I will need to remember this information. I am running a copy of the database locally. Why would I need to know what port the database is listening on? I am already on the machine when I run programs which need to connect to the database. Strange...

Now here is where the beauty comes in. I chose to install the sample database after the installation was complete. To my complete surprise, the sample database actually installed without error. This is a first. I guess by now it does not take much to impress me. The installation of the sample database took over 1 minute. So I was a bit worried. However I never got that far in the past, so I figured something must have been going right.

Currently I am using the Command Line Processor to issue SQL commands on the data in the sample database. It helps to have the Control Center open to spy on the structure of the tables in the sample database. My background is in the Oracle database. However I am finding that my basic SQL skills are pretty much still valid in DB2. I believe the next step is to review the important parts of the book "Understanding DB2" that I recently finished. It is my hope that I now have all the access to execute the commands that gave me errors before.

Return of Problems

Having reinstalled DB2 on my machine, I was hoping to get some practice with the database. These hopes came crashing down pretty quickly. I chose to install the sample database as part of the post-installation "First Steps". This choice led to an SQL 1092N. Apparently my domain user does not have the authority to create the sample database. Clicking on the SAMPLE database overview link was not encouraging. It just took me to a web page that stated, "The topic that you have requested is not available." LOL.

I thought maybe there was something wrong specific to the sample database. So I tried to use the DB2 Control Center to create my own database. This led back to the same SQL 1092 N. My next theory was that maybe my domain account did not have the correct rights. So I checked and found that I am a member of administrators on my machine. I am also a member of the groups DB2ADMNS and DB2USERS. These groups should be defunct since I chose to not use Windows groups for DB2 security. Now I was completely confused.

As a last ditch effort, I logged into my local machine with the db2admin account I specified when installing DB2 this last time. I created directory C:\DB2 and chose it as Default Path for the database I was creating. This resulted in a new error stating that the "path does not exist". Well I went back one last time and chose directory C:\ as the Default Path. Finally a database got created. The command completed successfully. But I don't want to log onto my workstation locally every time I create a database. And I bet I cannot log in with my normal ID and do anything with this database. Is it time to reinstall again, choosing my domain account as the User Information provided for the DB2 Administration Server? Who knows?

Uninstall Time

I decided it was time to unintall IBM DB2 from my computer. There were too many problems, based in part due to choices made during installation. I was a bit worried that maybe I would not be able to reinstall due to my trial license being over. There was one warning message during uninstall - "DB2 databases will not be removed". That was fine. I later used Windows Explorer to manually delete the database files. It was surprisingly quick to uninstall the whole DB2 software. It took a couple minutes total with no reboot of my PC needed.

Luckily the reinstall worked pretty well. At first I was back to the same issues when I first did an install. But I reviewed my post on Installing DB2. That helped me decide to choose Personal Edition of DB2. And this time around I chose a Typical installation, and accepted almost all the defaults. For example I chose a local user account of db2admin. However I once again ran into problems choosing a password for this account. It kept giving me errors like I mentioned in DB2 Install Continued. Note to self - always choose a short password that is complex like Pass2008 (upper case, lower case, numbers).

This time around I skipped installation of the DB2 Tool Catalog. Apparently this is for the "Task Center" and "Scheduler". Not sure what exactly these 2 items do. But I won't need any backups for my test database. So I hope this was the right decision. I also decided not to mess around with the notifications option. The most important install decision was to disable operating system security. This got me into so much trouble after the last install.

As a last step bonus, I decided to install the IBM Database Add-Ins for Visual Studio 2005. This step took a long time. But I have recently installed Visual Studio 2005 on my machine. So I hope to take advantage of this feature.