IBMDB2 - Basics

IBM Db2 Developer Community Edition

IBM Db2 Developer Community Edition for Windows 10

IBM_Db2_Developer_Community_Edition_Setup-1.1.3.exe

IBM Db2 database 90 Day Trial

DB2 11.1 data server trial for Windows

v11.1_ntx64_server_t.zip

IBM Data Server Driver Package (DS Driver)

IBM Data Server Driver Package (Windows 64-bit AMD64 and Intel EM64T)

Version 11.1

ibm_data_server_driver_package_win64_v11.1.exe

Note on Credentials

IBM DB2 authenticates using Windows Users. All throughout testing I never had to setup logins and passwords within the database server, I didn’t even need to assign Windows Users to database logins. Simply if a user was setup on the machine the server is on then it worked. If connecting from a remote machine with a Windows user with the same password in a workgroup, then it worked.

WRONG: All connects fine but when it comes to a SELECT it didn’t let me from Administrator

TODO: Can you use database logins with username and password in connection string?

Installing the Server

Don’t create a DATABSE (like Oracle) it means an entire instance ??? I think ???

Did again used typical need to add sample afterwards. Did not create windows groups used db2admin account was put into Standard Windows Administrators group.

DO NOT SELECT Custom, always Typical, it won’t work even if you leave all the defaults. Administrator or db2admin will not have privileges to do anything!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Let’s run through installing the no-charge community edition of IBM DB2 and installing the management software:

· Download the installer for DB2 Express-C for Windows 64-bit. You can use direct HTTP or a pointless download manager

· Extract and run “C:\v11.1_win64_expc\EXPC\image\setup.exe”

· Click “Install a Product” on the left then click the button “Install New”

· Select “Custom” install, this will default to Typical but you can enable other things

· Select Install on this Computer, you can create the response file if you like.

· Leave features as is and click Next

· Leave language English, add others if you need them

· Leave all SSH settings as is you want it to start automatically

· Leave the copy name “DB2COPY1” and program data directory as is

· Leave the information Centre on the IBM website

· Set up the user “db2admin” note down the password !!!!!!!!!!!!!!!!!!!!For a test system I recommend using “LocalSystem” for the Administration Server account and use the same account for other services !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

· You can click the “Configure” button now to change the port number if you want, I’d recommend leaving it on the default 50000

· On a test system I wouldn’t bother setting up SMTP notifications, but you can if a SMTP server is available

· Enter the notification email address if you’re using it

· Leave operating system security enabled. !!! BUG HERE !!! Tick then untick otherwise it won’t do it !!!!!!

· Click “Finish” and wait for it

· You can run "C:\Program Files\IBM\SQLLIB\bin\db2val.exe" to validate the install

· As this is the free edition you don’t need to worry about activating the installation

· Click the final “Finish” and you will be presented with the “First Steps” screen

· The sample database should already be installed from previous steps you can click “Create Sample Database” just to make sure, you will get an error saying it’s already there.

· Click the link to “Download IBM Data Studio”

· Choose “Data Studio Client” and tick the “I already have an IBM id” if you do

· Sign in or create you IBM Id

· Start the download using direct HTTP or a pointless download manager

· Extract and run “C:\ibm_ds4120_win\launchpad.exe”

· Click “Install or Update Product” then “Administrative Install”

· Click through to install the “Installation Manager”

· When finished click “Restart Installation Manager”

· Click Install and select “IBM Data Studio Client”

· Click through everything with the defaults and click the “Install” button and wait

· When done click “Finish” and exit the Installation Manager

· You may want to restart now just to prove everything starts back up fine

Shortcuts to all the server tools can be found in:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\IBM DB2 DB2COPY1 (Default)

Shortcut to the Data Studio in:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\IBM Data Studio

If Data Studio won’t start then delete the folder here (Or whatever your username is, it is “Administrator” here):

C:\Users\Administrator\IBM\rationalsdp\workspace\.metadata\.plugins\org.eclipse.e4.workbench

I’ve also seen Data Studio not start and then work when trying it for the second time.

Connect to the “SAMPLE” database and create a diagram

· Start Data Studio

· Make sure in right view: Window Menu->Show View->Data Source Explorer

· Right click on “Database Connections” and select “New…”

· The “New Connection” dialogue box is shown

· It should already be setup for connecting to “SAMPLE” on localhost. You just need to enter a username and password. Use a Windows account username and password, the current user will do like “Administrator” and the password you use to log in. You can click save password if you like.

· Click the “Test Connection” button

· Click “Finish” don’t need all the stuff in the “Next” pages

· You will now see the “SAMPLE” connection profile and within that the “SAMPLE” database

· Within the database open up “Schemas” then open up the Schema named after your user name for example “ADMINISTRATOR”

· Within the Schema there is a “Tables” folder that contains the actual tables

· Select the first table “ACT” then SHIFT select the last table “SUPPLIERS” so they are all selected.

· Right click on one of the selected tables and choose “Add to Overview diagram” from the menu.

· The “Overview Diagram Selection” dialogue appears all the actual tables should be selected just click the “OK” button

· Give it a moment and you should be looking at a diagram, that only shows table names and the relations

· To get the columns in the diagram, make sure the properties window is displayed, Window Menu->Show View->Properties

· Click on a blank area of the diagram and the properties window will be showing the diagram properties

· Within “Filters” -> ”Compartment Display Options” you can switch on “Show Key”, “Show non key”, and “Show Index”. Within “Column Display Options” you can switch on “Show data types”. Should now be showing all the columns with datatypes and indexes on each table.

· Right click on empty space and select “Arrange All”

· Bingo

· You can then print out to PDF, for some reason it took about 4 minutes to print on my test box

Setting up the client

The software component you want to download from IBM is called the “IBM Data Server Driver Package (DS Driver)”.

IBM describe it as “… is a lightweight solution and the best practice package for end user code deployment”. To me lightweight would mean a 70K DLL that I can just compile in and start using, to them it’s a 70Mb installer that installs an entire stack of software. There is no quick and easy way to connect from .net to a DB2 server you need to install full driver package. Do the following:

· Locate and download “IBM Data Server Driver Package (DS Driver)” from IBM

· Run the installer “ibm_data_server_driver_package_win64_v11.1.exe”

· Leave everything at default