IBM Support

HOWTO: Getting started with DB2

Question/Answer


Question

Tutorial for installing and basic setup of DB2 Data Server Client, DB2 Connect and DB2 LUW Servers including licensing and ODBC application connectivity.

Answer

Before you begin:


(A) Installation

DB2 installation media can be obtained from Passport Advantage or the Fixpack site. It will contain information regarding the different types of clients and fix packs i.e. UNIVERSAL vs. SERVER fix pack, IBM Data Server Client vs. Data Server Driver Package, etc... DB2 Client or SERVER fix packs contain full installation image for DB2 so it can be used to install DB2 on to system with no base installation of DB2.

Windows: Run setup.exe.

UNIX/Linux: Run db2_install non-GUI script or db2setup (GUI, requires X-Windows)

A X-Window server needs to be installed on your Windows client if running db2setup located on remote UNIX/Linux system. Below are some X-Windows servers available:

Xming (free)

Cygwin (free)

Opentext Exceed (not free)

Starnet X-Win32 (not free)

(B) Post-Installation DB2 instance creation

If db2setup was run on UNIX/Linux/Windows please skip this step since the GUI install automatically creates the needed DB2 instances. Proceed only if the UNIX/Linux script db2_install was run.

UNIX/Linux DB2 Client:

# cd /opt/ibm/db2/instance # db2icrt -s client db2inst1
 

UNIX/Linux DB2 Server:

# cd /opt/ibm/db2/instance # db2icrt -u db2fenc1 db2inst1
 

Notes:

  • Example assumes DB2 was installed to /opt/ibm/db2
  • The db2icrt command creates symbolic link from /home/db2inst1/sqllib to DB2 installation directory /opt/ibm/db2
  • The system admin will need root privileges to create a new user id, the default account is usually db2inst1. For DB2 servers, an additional fenced user id is needed to run non-SQL stored procedures and user defined functions. This is usually called db2fenc1 but can be any existing user id on the system.
  • Account for DB2 DAS (DB2 Administration Server) not needed since Control Center GUI tools were removed in v10.1
  • Primary group of user should be the same as the user id and should not contain spaces. In some instances UNIX/Linux accounts located in remote Microsoft Active Directory which may contain spaces for primary group.
 

(C) Apply DB2 Server or DB2 Connect license

If connecting to DB2 Linux/Unix/Windows, skip this step no license needed.

If connecting to DB2 zOS, i-Series(AS400), VSE or VM apply the DB2 Connect license via db2licm -a <license file> Alternatively, db2connectactivate command can be used to install server side licensing so each DB2 client does not need to run "db2licm -a ....". If there are hundreds of clients, db2connectactivate saves time because the license does not have to be applied across hundreds of clients that need access to DB2 z/AS400, etc...

If this is a DB2 server installation, install the server license.

Download your DB2 license from the link below:

IBM How do I download my DB2 license from Passport Advantage

The license file does not have db2*_t.lic or db2*_b.lic since these are just base/trial licenses. DB2 License file names found here. DB2 licenses included for use with another IBM product would typically have the db2*_r.lic or db2*_o.lic (restricted) extension, check the bundling product's documentation regarding where the license file is located.

To check if the license is a trial license is to open up the license file using a text editor. The ProductVersion should match the release installed and the LicenseEndDate should be 2037 for permanent licenses.

[LicenseCertificate]
...
ProductName=DB2 Enterprise Server Edition
ProductID=1413
ProductVersion=10.5
...
LicenseStartDate=12/28/2012
LicenseDuration=9135
LicenseEndDate=12/31/2037

Apply the license:

db2licm -a license file

 

Check license has been successfully installed, you should see Expiry date=Permanent. Ignore the additional features like BLU Acceleration if these features were not purchased.

db2licm -l
Product name: "DB2 Enterprise Server Edition"
License type: "CPU Option"
Expiry date: "Permanent"
Product identifier: "db2ese"
Version information: "10.5"
Enforcement policy: "Soft Stop"

Features: IBM DB2 BLU Acceleration In-Memory Offering: "Not licensed"
IBM DB2 Performance Management Offering: "Not licensed"
IBM DB2 Encryption Offering: "Not licensed"
IBM DB2 Business Application Continuity Offering: "Not licensed"



Installing server side DB2 Connect licensing

db2connectactivate script is included with IBM DB2 Connect Unlimited Edition for System z - Quick Start and Activation from Passport Advantage and newer release of

db2connectactivate -host test.ibm.com -port 446 -database sample -user db2inst1 -password 123456

hostname of remote database = test.ibm.com
port of remote database = 446
database name = sample
For DB2 z, database is the LOCATION name.


For Java applications connecting to non-LUW
1) Obtain db2jcc_license_cisuz.jar included with DB2 Connect
2) Ensure application's CLASSPATH environment variable points to db2jcc_license_cisuz.jar

(D.1) Setting up connectivity to remote database

Note: Connectivity to DB2 databases outside of Linux/Unix/Windows is not free. A separate product called DB2 Connnect must be purchased.

test.ibm.com = Remote hostname of the DB2 database

50000 = port number of remote database

test = this is arbitrary node name you create

sample = name of the database

db2inst1 = any user id which exists on the remote system

db2 "catalog tcpip node test remote test.ibm.com server 50000"
db2 "catalog db sample at node test"

Test Connectivity:

db2 "connect to sample user db2inst1"
db2 "select count(*) from sysibm.systables"

1
-----------
751

1 record(s) selected.

db2 "connect reset"

Optional: DCS directory used only for connecting to non-LUW servers like DB2 for z/OS, i-Series (AS400) when you need to specify additional DCS directory values or when the remote database name is longer than 8 characters. In the example below the database name on DB2 for z/OS is SAMPLEZDB which exceeds the 8 char limit in DB2 LUW. In DB2 LUW we catalog the database as ZDB, but need the DCS directory to map it to SAMPLZDB on DB2 for z/OS.

db2 "catalog tcpip node test remote zosdb.ibm.com server 447"
db2 "catalog db zdb at node test"
db2 "catalog dcs db zdb as samplezdb"

(D.2) Optional: Setting up TCP/IP listener to accept connections from remote clients

This step does not apply to DB2 clients, only DB2 database or DB2 Connect servers. This example sets up listener at default port 50000. With a DB2 Connect Server product, this will allow for 3-tier connection: DB2 client-->DB2 Connect Gateway-->DB2 for z/OS database

db2set db2comm=tcpip
db2 "update dbm cfg using svcename 50000"
db2stop
db2start

DB2 Server only: Create test database called sample

db2 "create db sample"

(E) Bind files needed for application connectivity


Connectivity to remote DB2 Linux/Unix/Windows:
cd ..\bnd (Windows) or cd ~/sqllib/bnd (UNIX/Linux)
db2 "connect to sample user db2inst1"
db2 "bind @db2ubind.lst action replace blocking all sqlerror continue messages ubind.msg grant public"
db2 "bind @db2cli.lst action replace blocking all sqlerror continue messages cli.msg grant public"


Connectivity to remote DB2 for z/OS, i-Series (AS400) and other non-LUW:
cd ..\bnd (Windows) or cd ~/sqllib/bnd (UNIX/Linux)
db2 "connect to sample user db2inst1"


For OS/390(R) or z/OS(TM) or z-Series:
db2 "bind @db2ubind.lst blocking all sqlerror continue messages ubind.log grant public"
db2 "bind @ddcsmvs.lst blocking all sqlerror continue messages ddcsmvs.log grant public"



For OS/400(R) or i-Series:
db2 "bind @ddcs400.lst blocking all sqlerror continue messages ddcs400.log grant public"


For VSE:
db2 "bind @ddcsvse.lst blocking all sqlerror continue messages ddcsvse.log grant public"


For VM:
db2 "bind @ddcsvm.lst blocking all sqlerror continue messages ddcsvm.log grant public"


Reference: Binding applications and utilities (DB2 Connect server)

Notes:

  1. The id used to connect to the remote database will require BIND authority to bind packages.
  2. The bind log files *.log will contain a summary of the bind operation containing a summary of Warnings and Errors. Bind WARNINGS can be safely ignored. Bind ERRORS should be investigated.
 

(F.1) Optional: Establishing ODBC application connectivity

Windows:

db2 "catalog system odbc all data sources"

After running the command above going to Administrative Tools->Data Sources (ODBC), the DB2 database aliases should appear in the System DSN tab. In example below, there is one DSN called v105s.


Note: For 64-bit Windows systems that have applications which require use of 32-bit drivers refer to How to point to 32 bit IBM DB2 ODBC driver on 64-bit Microsoft system?

UNIX/Linux:

Instructions for UNIX would be similar to the tech note below for Linux.


See Related URL section at end of technote: HOWTO: Setup ODBC application connectivity on Linux

(F.2) Optional: Establishing JDBC application connectivity

If application is using DB2 instance user id then the CLASSPATH environment variable is already pointing to db2jcc.jar (JDBC 3.0 compliant) or db2jcc4.jar (JDBC 4.0 compliant) and sqlj.zip (SQLJ support, used by few customers). If connectivity to non-LUW like z/OS or AS400 add the path to db2jcc_license_cisuz.jar to CLASSPATH.

If application is using a different user id, modify the CLASSPATH so it points to all the needed files.

Windows:

Control Panel->System->Advanced system settings

Advanced tab->Environment variables

Modify CLASSPATH for User/System variables

UNIX/Linux:

Assuming files are placed in /home/myuser, modify startup scripts like .profile, .kshrc, .bashrc, etc... to append the JDBC drivers to the existing CLASSPATH environment variable.

export CLASSPATH=$CLASSPATH:/home/myuser/db2jcc.jar:/home/myuser/db2jcc_license_cisuz.jar:/home/myuser/sqlj.zip

(G) Optional: Windows only. Changing Windows Service to authenticate domain ids

1) Run As and enter "services.msc"

2) Ensure Log On As is set to a domain id for "DB2 - DB2COPYxx - DB2" Windows service. If it is set to local user id, DB2 will not be able to authenticate Windows Domain ids which may result in SQL0551, SQL1092 authorization errors. In screen shot below, Log On As is the local id "db2admin". It will not be able to authenticate domain users like MYDOMAIN\db2inst1

(H) Optional: Export/Import DB2 profile

After configuring DB2, the settings like DB2 registry variable, DBM CFG or connectivity information (database & node directory) can be saved to a file to be imported into another installation. This is commonly used when installing DB2 on multiple systems via automated silent install.

1) Save the existing profile from current DB2 installation

db2cfexp db2.cfg template

Note: There are different options for this command. Use template when exporting/importing the connectivity information.

2) Copy the file db2.cfg to the target system where another copy of DB2 is installed

3) Import the profile

db2cfimp db2.cfg

(I) Reference

For additional DB2 commands please see Quick Reference in Related URL section along with DB2 CLP and SQL statements.


Related information

Quick Reference: Common DB2 CLP Commands
HOWTO: Setup ODBC application connectivity on Linux
DB2 CLP Commands
SQL Statements

Document information

More support for: DB2 for Linux, UNIX and Windows

Component: OTHER - Uncategorised

Software version: 9.5, 9.7, 10.1, 10.5, 11.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1985636

Modified date: 06 June 2019