Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DB2 – Create a Database

OK for my next DB2 trick and remaining off the path of the righteous for the time being we will look at creating a DB2 database. I assume that you have the DB2 RDBMS software installed, you can get a free 'community' copy of the express edition, follow this link for the download. Being a SQL DBA with an MSDN subscription I installed DB2 on Windows Server R2 2003 running on VM Workstation. It can also run on a variety of *nix OS too.

As with SQL Server there are many ways to create a database in DB2. In this example I am going to use the wizard, I'm new to this so I'll go with the simple approach. Assuming you have all the components installed, as I do, you need to fire up Control Manager.

Note: There are differences in the physical architecture of database files when comparing DB2 to SQL Server. As my understanding improves I may write a little about that topic but for now we'll focus on using wizard and creating a database.

Before we start let me give you a bit of background on this server I setup, I'm not yet all the knowledgeable on ideal performance layout for DB2 and as this was setup for me to learn a bit more about DB2 so I configured my server with a C:\ drive which houses the OS and the DB2 software and an E:\ drive which I intended to use for the data.

We'll now create a database using the wizard in Control Manager. So how do I open Control Manager, here's how I do it on my Windows Box:

Click

  • <Start>
  • <All Programs>
  • <IBM DB2>
  • <DBCOPY1(DEFAULT)>
  • <General Administration Tools>
  • <Control Centre>

ControlManager

When Control Manager opens you are asked prompted by the Control Center View box to specify the view that you want. "Advanced" was selected by default so I went with that and clicked OK. My DB2 instance and the databases installed as part of the install were already registered. These are Sample and Schedule:

CMopen

To create a database:

  • Right Click <All Databases>
  • Select <Create Database>
  • Select <Standard>

This starts up the Create Database Wizard:

Enter a name for your database in my case I called it GETEST and I set the default path for the database to be the e:\ drive and a folder called data.

I took all other default settings, Let DB2 manage my storage (automatic storage). I left the default bufferpool and table space page size at 4K:

 

CreateDB

I made no changes on the storage tab, because I specified a default path and let DB2 manage my storage on the name tab I have no changes to make here. Click <Next>

For simplicity I make no changes on the Region tab and I take the default settings. Click <Next>

on the summary tab it give you a "Show Command" button which gives you the underlying code that will execute

CREATE DATABASE GETEST
AUTOMATIC STORAGE YES ON 'E:\DATA'
DBPATH ON 'E:\DATA'

USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096;

So there we go as you can see that the syntax is very similar to its TSQL equivalent.

Click on <Finish>

This should cause DB2 to go away and create you a nice new database.

except in my case it doesn't create a database. I get the following error:

"SQL1052N  The database path "E:\DATA" does not exist.

Explanation :

The path specified in the "<path>" parameter of the command is not
valid. Either there is no path with that name, or a path has been
specified when the DB2_CREATE_DB_ON_PATH registry variable is disabled
(Windows only).

Well I the path is there so it must be the later in the explanation " a path has been specified when the DB2_CREATE_DB_ON_PATH registry variable is disabled (Windows only)"

Well it is windows, so I guess this is viable.

So how do I enable this registry variable. It is very simple.

Open a command prompt on the server and run the following:

db2set DB2_CREATE_DB_ON_PATH=YES

You won't get any message confirming it has been set or any errors telling you it hasn't worked, when I run it i just got another command prompt.

You then need to restart the DB2 Service, mine is listed in the services as:
DB2 - DB2COPY1 - DB2

Restart that service.

and re-run the wizard and you should get yourself a brand new DB2 database.


Comments

Posted by Steve Jones on 30 June 2010

Looks a lot like SQL Server

Posted by Gethyn Ellis on 1 July 2010

they do seem quite similar.

Leave a Comment

Please register or log in to leave a comment.