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>
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:
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:
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.
