- IBM Optim Database Administrator for DB2 Linux, UNIX and Windows Version ODA 2.2.2
- DB2 Enterprise Server Edition 9.7
- Windows XP Professional SP 3
- Intel(R) Pentium(R) 4 CPU 2.80 GHz
- 2 GB RAM
Create a new database
- Open the Optim Database Administrator and choose any directory that is desired for your workspace.
- Go to your Data Source Explorer, and click on the New Database icon. You can refer to the image below.
- Next, a pop out window will ask you to specify the database vendor. Select DB2 for Linux, Unix and Windows and click Next.
- You are required to enter your database instance parameters. If you are new to DB2, you can follow these points to retrieve your parameter value.
InstanceRun the command db2 get instance in your DB2 CLP.
PortRun the command db2 get dbm cfg in your DB2 CLP. Look for a the parameter SVCENAME. If the value is not numeric, you will need to open the services file in the directory C:\WINDOWS\system32\drivers\etc. Look for the value from SCVENAME in the file to retrieve the port number.
HostnameIt will be localhost since our Database is in the local.
Username & PasswordThe username is your instance owner name. For Windows, you can use you Windows username and password.
Important Notes: Always use capital letters to create database object name in Optim Database Administration like database,schema, table, column etc. This is a good habit because Optim will try to insert lower case with double quotes into DB2. The double quotes will cause the DB2 object created to be almost impossible to retrieve.
Design database tables.We are going to create and design new tables using the Optim Database Administrator. You can easily drag and drop your tables and modify it using this software. This is very useful if you want to have an overview of your database design and to view the relationships between tables.
Create new Data Design Project
- Click on File --> New --> Data Design Project
- In the screen New Data Design Project you can fill in the project name as MyDataDesign and click Finish.
- Click Yes when you are asked whether do you want to open the Data perspective.
Create a new schemaWe will need to create a new schema so that we can store our tables into the schema later.
- Go to Data Source Explorer. Right click on the SALESDB connection and click connect.
- Expand the Schemas under the SALESDB database. Right click on the Schemas and select Create --> Schema.
- Select Data Object Editior when you are asked which editor to use and click OK.
- Name you schema as MYSCHEMA and click on Run DDL.
- Close the Data Object Editor.
Create diagrams for a schema
- Go to Data Project Explorer. Right click on the Data Diagram under the MyDataDesign project and go to New --> Physical Data Model.
- Enter your information in the next screen like the below image and click Next.
- Next, select Database as your source and click Next.
- In the next screen, select SALESDB as your connection and click Next.
- Now you will have to select the schema that will be use in your diagram. Select the MYSCHEMA which you have created earlier and click Next.
- The following screen will ask you what database element you wish to select for you model. We will leave it to the default selection. Click Finish.
- Go to your Data Project Explorer. Expand MyDataDesign-->Data Models--> Database Model.dbm --> SalesDB --> MYSCHEMA, right click on Diagrams and select New Overview Diagram.
Tips: If you could not see any thing under Database Model.dbm, double click it and there should be the sub directories.
Create new tables in a diagramLet's try to create some tables in the diagram.
- On the blank diagram created, right click and select Add Data Object --> Table.
- Right click on the table and go to Filters --> Show/Hide Compartment --> All Compartments. This will show all the related object to the table.
- Now we are going to change the information and add new columns to the table. Select the table and go to the Properties view under the diagram.
- Under the Properties, go to General and change the name from the default Table 1 to PLAYERS
- Go to Columns and click on the diamond shape icon to create new column.
Image 4: Create a new column
- Rename the Column1 name by selecting it and replace it with PLAYERID.
- We want the PLAYERID to be the primary key for our Table. Thus, select Primary Key box.
- Next, select INTEGER as your Data Type.
- Make sure the Not Null box is ticked.
- Create another column using the following information
- Column Name: PLAYERNAME
- Data Type: Varchar
- Length: 32
- Not Null
- Your columns for your the table Players will look like the below image.
- Go to File -->Save all to save your progress.
- Create a new table called Tournament with the 3 columns which are TOURNAMENTNAME, TOURNAMENTID and Champion.
Column Name TOURNAMENTID Primary Key Ticked Data Type Integer Not Null Ticked
Listing 1: Details for TOURNAMENTID column
Column Name TOURNAMENTNAME Data Type Varchar, length 32 Not Null Ticked
Listing 2: Details for TOURNAMENTNAME column
- You diagram should look like the image below.
Image 6: Tables in MYSCHEMA diagram
- Go to File --> Save all to save your progress.
Create a relationship between tablesNext, we are going to see how we can create a relationship between two tables with foreign key.
- Make sure you are in Database Administrative Perspective. If you are not in your Database Administration Perspective, go to Windows-->Open Perspective-->Database Administration.
- Go to your Palette which is on the right and click on the Data folder.
- Select the Non-Identifying Mandatory connection. Drag the connection from Players table to Tournament. If you are prompted to a windows called Key Migration you can just click Ok. It does not matter what you have selected. This is because we will change the foreign key later.
- If you see a new column created called PlayerID [FK] in the Tournament table. This will be your foreign key in the table.
Compare the MYSCHEMA data model with Original SourceSo far we have been creating two tables in the Data Model only. This does not mean that the database SALESDB has the two tables. In order for the database to have the new tables we have to do a compare between the MYSCHEMA data model and the database.
- Right click on MYSCHEMA in Data Project Explorer under MyDataDesign-->DataModels-->MYSCHEMA. Select Compare With--> Original source .
- Next, you will see a Compare tab . On the left is the database model while on the right is the database.
- Click on the icon Copy from Left to Right, this will copy the table from the database model to the database.
- Next, click on Generate Right Delta DDL.
- In the Generate DDL window select the Run DDL on server only and click Next.
- After that, you will need to select you database connection. Select SALESDB and click next.
- In the summary window click Finish.
You can now check in your Data Source Explorer and see if the tables exist.
ConclusionIn this article, we have covered the following using Optim Database Administration:-
- Create a new database
- Design database tables and created a new tables
IBM Virtual Innovation Center