DB2 quick start in Windows

DB2 is a popular database for integration and IBM Cloud deployments. So We may see lot of use in db2 in enterprises.

Benefits of using DB2® Multisystem include improved query performance, decreased data replication, larger database capacity, and so on.

But non of the system is perfect. So its pros and cons as described “https://www.trustradius.com/reviews/db2-2015-12-08-12-27-24” website are given bellow.

Pros and Cons

• DB2 maintains itself very well. The Task Scheduler component of DB2 allows for statistics gathering and reorganization of indexes and tables without user interaction or without specific knowledge of cron or Windows Task Scheduler / Scheduled jobs.
• Its use of ASYNC, NEARSYNC, and SYNC HADR (High Availability Disaster Recovery ) models gives you a range of options for maintaining a very high uptime ratio. Failover from PRIMARY to SECONDARY becomes very easy with just a single command or windowed mouse click.
• Task Scheduler ( DB2 9.7 and earlier ) allows for jobs to be run within other jobs, and exit and error codes can define what other jobs are run. This allows for ease of maintenance without third party softwares.
• Tablespace usage and automatic storage help keep your data segmented while at rest, making partitioning easier.
• Ability to run commands via CLI (Command Line Interface) or via Control Center / Data Studio ( DB2 10.x+) makes administration a breeze.
• You cannot run multiple secondary nodes or cluster without additional software purchases; in some cases third party tools. This drastically increases your overall capital investment. The only way to accomplish a true HADR scenario is to set up NEARSYNC in one datacenter and do logshipping to another datacenter. Downside: You have to wait for the final log ship to complete before your DB is back up.
• Licensing is prohibitively expensive! If you are not grandfathered in, IBM licensing for a multi-datacenter, PRIMARY, SECONDARY, and Disaster Recovery (DR) setup can be in the multiple $100,000 range.
• Data Studio is built on IBM’s Rapid Application Development (RAD) tool, built on Eclipse. So the download is in the multi-GB range and it includes a ton of bloatware not needed for your standard database maintenance. Control Center is a simple, powerful tool at a quarter of the disk space.
• Support for DB2 is very hard to come by without paid IBM support. Even then, opening PMRs does not solve problems as the response time for any PMR is always more than two (2) hours, even for enterprise-level paying customers. They always want the most inane log files that have nothing to do with DB2 or its operation, or they want core dumps during the issue. This becomes useless when the issue is “our database just crashed and you can’t get those logs right now because I do not want to replicate the cause!”
• DB2’s SQL syntax, while ANSI in CRUD opertaions, is different than Oracle. PostgreSQL, MySQL, and even MS SQL. One must become accustomed to a different syntax for LIMITs, cursors, record counting, stored procedures, user-defined functions, and even table / index creation or altering.

Install Db2 in Windows.

Following video tutorial describe the installation process properly.

After installation

DB2 Express-C version 10 and later no longer include a full suite of administration tools as part of the installer (the “Administration Tools” you installed might only include the ones you’re looking at…).

Like MySQL and SQL Server, the administration tools are now available as a separate download.

The official tool you’re looking for is IBM Data Studio, which is available free of charge.

Older versions of DB2 Express-C used to include the legacy Control Center apps, but IBM has deprecated those and is really pushing the Eclipse-based Data Studio.

IBM Data Studio Installation.

Following url has the installation instructions and the download link.

https://www.ibm.com/support/pages/download-and-install-ibm-data-studio-version-41x#download_web_tab

As a example i will show how to install it in windows environment.

First download the latest IBM Data Studio client for windows extract it and run the launchpad.exe

Just click on Administrative Installation Or upgrade and installation process will begun by opening IBM installation Manager. First of all this process will install IBM Installation Manager and then IBM Installation Manager will install IBM Data Studio.

You may change the version hear. If you are ok with the latest version then click next and accept the policy agreement and then click next.

In next window you will be asked the file path to install. I am going with the default one.

Finally a summary of provided information will be displayed for confirmation.

Once you are start installation it will install IBM installation Manager and it will ask you to restart the installation manger to begin other installations.

Once Restart installation Manger it will be prompted following window which you can start installing Other IBM packages. Click on Install button.

Then you have to select the IBM Data Studio client and start installing it.

Then aggrement acceptance process.

Finally installation messages will be given.

Next come package group creation window.

Then select the language required for you.

Then features will be shown to be installed. By default SQL execution is not selected and if you want just check to add it to the list and start to install by clicking Next.

Finally summary page will be displayed and you can start the installation process.

Finally you will see the window you were waiting for. Successful completion and there is only one button to “Finish”.

How to use IBM Data Studio

Once you done you may find menu item to launch IBM Data Studio. Just click and start the party.

After workspace configuration you will see the welcome window.

Creating a new Database

First you have to check your current instance or if you want change the instance which you want to create the databae.

You may do it by DB2 Command Window Administratoty and use following command to view the current instances.

> db2ilist

If you want to change the instance to LDAPDB2 use the following command.

> set DB2INSTANCE=LDAPDB2

IBM has provided more informations about instance management in following url.

Listing DB2 instances and checking/setting the current DB2 instance on Windows

Then now you know your instance and now you can create a new database using IBM Data Studio. Its simple just click the New Database icon marked by read arrorw in the interface and first give instance information and then db information.

Once Instance is successfully created then you are able to create the database.

I tried to give the Database location as given in the following interface.

But When i click the Run button in the top of the window and error appeared as “Database Path Doesn’t Exists” in the Query running part in the Storage which instantly opened when we run.

The reason was this issue was given in one help page as follows.

In Windows you can use only C:,D:,E: drive letters for Database Paths
during create db command.
Please use
db2set DB2_CREATE_DB_ON_PATH=YES
Then you should be able to create DB on sub folders.

So hear you have 2 options you may give the path as “C:” etc. Or Enable db on path option from Administration console. Remember to restart the instance after the configuration to take it in to effect.

commands used.

> db2set DB2_CREATE_DB_ON_PATH=YES
>db2stop
> db2start

Then databases will be created in given subfolder.

Create a New User for a database.

It is required to create users grant privileges and user management for database. For this option first of all you have to connect to the specific database.

Right click on the database → Connect

Then Database should be connected. Now you database name in the Administration Explorer window can be expanded. Now Go to privilege management interface.

Right Click on the databae name → Manage Privilages.

In the bottom panel click on the diamond icon and create a new user with the privileges you want.

References
https://www.ibm.com/support/pages/how-create-brand-new-blank-db2-database-controller

Sample Java DB connection

Connection conndb2 = DriverManager.getConnection(“jdbc:db2://localhost:50000/samesb”, “db2esb1”,”db2esb2");

Exporting Data to CSV file from Table

Right click on the table then go to “Data” and “New “Select” Script and then execute the query by clicking on green arrow circle. Then click on the result set in bottom window. Right click on the record select “Export” then all result or any other your choice.

Create new Schema, Table and import previous Export csv file.

Create a new schema.

Right click on localhost DB2 Schemas and new Schema.

Give name as MIGRATION.

Click on save changes.

Then click on “Generate DDL”. It will commit the changes and if error found you have to correct it. Then refresh it see the changes.

Create the Table

Original table structure.

Click on Tables icon. Right click on it then click on Create Table.

Select the Schema you created early “MIGRATION”.

Then Give the name “DB_TABLE_CONFIG”.

Now you have to define columns. Original structure as follows.

So you have to create this schema in you local database also.

Then save the changes and and then click on “Generate DDL”.

Then you can execute this sql query and get the output as follows.

CREATE SCHEMA MIGRATION

CREATE TABLE MIGRATION.DB_TABLE_CONFIG ( TABLE_NAME VARCHAR(255 OCTETS), SOURCE_COLUMN VARCHAR(255 OCTETS), TARGET_COLUMN VARCHAR(255 OCTETS), TRANSFORMATION VARCHAR(255 OCTETS), DATA_TYPE VARCHAR(50 OCTETS), ORACLE_TABLE VARCHAR(255 OCTETS) )

Once done click on the Refresh button to see the changes.

Import CSV to existing table.

Now time to import csv file to local table.

Click on the table and right click on it. — → Load → With Import Utility

Give the file you imported data and click on Run button then you will see the status of the import task.

A Successful message should come as number of record it has imported.

Now you can view all records you have imported.

Click on “Tables”

Click on record “MIGRATION “ with table name “DB_TABLE_CONFIG” record.

Right click on it select “Data” then “New “Select” Script”

It will give following query and by executing it you may see records.

SELECT TABLE_NAME, SOURCE_COLUMN, TARGET_COLUMN, TRANSFORMATION,DATA_TYPE, ORACLE_TABLE
FROM MIGRATION.DB_TABLE_CONFIG

Insert Query .

INSERT INTO
table_name(column_list)
VALUES
(value_list_1),
(value_list_2),
(value_list_3),
...;

There will be a record added with column names. So you have to remove that record.

DELETE FROM MIGRATION.DB_TABLE_CONFIG
WHERE TABLE_NAME = ‘TABLE_NAME’;
# Delete which table name is null
DELETE FROM MIGRATION.DB_TABLE_CONFIG WHERE TABLE_NAME IS NULL;

Run above commands in SQL window.

Changing Tables

Db2 ADD COLUMN: Adding One or More Columns to a Table

ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;

Use DBeaver for DB2 Database Management.

For administration the DB2 database you may use DBeaver . You may download this tool from bellow url.

https://dbeaver.io/download/

Further Jdbc-db2 driver also required and it can be downloaded from the maven repository.

https://mvnrepository.com/artifact/com.ibm.db2.jcc/db2jcc4/10.1

After installation DBeaver then try to make a connection as follows.

New → DBeaver -> DB Connection

Give db2admin username and password pwd@123

Then select the driver also.

Then you can connect and view SAMPLE database and schemas can be viewed .

Create New Database using docker.

DB2 installation using docker

From the command line terminal, create a new directory for your Docker image:

mkdir Docker

Go to this directory by entering the following command:

cd Docker

Log into your Docker container:

docker login

Pull the Db2 Docker image from Docker Hub:

docker pull ibmcom/db2

From your Docker folder, create an environment variables file, .env_list, for your Db2 Community Edition image:

type nul > “.env_list”

Be sure to include the quotation symbols when creating the file.

In a text editor, open the .env_list file and paste the following:

Parameter list of this can be find in the above url.

// Sample Env file
DB2_4K_DEVICE_SUPPORTLICENSE=accept
DB2INSTANCE=db2inst1
DB2INST1_PASSWORD=db2inst1
DBNAME=testdb
BLU=false
ENABLE_ORACLE_COMPATIBILITY=false
UPDATEAVAIL=NO
TO_CREATE_SAMPLEDB=false
REPODB=false
IS_OSXFS=false
PERSISTENT_HOME=false
HADR_ENABLED=false
ETCD_ENDPOINT=
ETCD_USERNAME=
ETCD_PASSWORD=

// Following command can be used to run the db2

docker run -d -e LICENSE=accept –-privileged=true -h db2server -–name db2server -–restart=always –-detach -p 50000:50000 -p 55000:55000 –-env-file .env_list -v /Docker:/database ibmcom/db2

Once it is running it should be visible by : docker ps cammand.

docker ps

Starting and Stopping db2 containers

docker start <container id found in docker ps command"
// stop
docker stop <container id>

Now database should be created by a docker client.

sudo docker exec -i -t db2 /bin/bash
su -- db2inst1

Start the service

db2start

Then you have to create the database if it is not created by now.

https://www.tutorialspoint.com/db2/db2_databases.htm

db2 activate db <db_name>

// if it is created you may connect it now

db2 connect to testdb
db2 select * from org
db2 terminate
db2stop

Create a DB2 Database

Use the below commands to create the database

db2 create database test3

db2 connect to test3 user db2inst1 using db2inst1

Further following source discuss on DB2 installation in ubuntu.

Configure Database DB2 with Docker and use DB2 as the DB Type with WSO2 Identity Server