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.

Following video tutorial describe the installation process properly.

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.

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.

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.

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.

Then databases will be created in given subfolder.

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.

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

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.

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.

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.

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.

Insert Query .

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

Run above commands in SQL window.

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

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

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 .

DB2 installation using docker

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

Go to this directory by entering the following command:

Log into your Docker container:

Pull the Db2 Docker image from Docker Hub:

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

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.

// Following command can be used to run the db2

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

Starting and Stopping db2 containers

Now database should be created by a docker client.

Start the service

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

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.