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. The 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)

An X-Window server needs to be installed on your Windows client if running db2setup on remote UNIX/Linux system. Examples of  X-Windows servers available:

(B) Creating user ids that require access to the database

DB2 uses operating system to manage the users and groups. Thus if the additional user ids that require access to DB2 server already exist in the operating system, nothing needs to be done. Otherwise add them to where DB2 server is installed

Windows:

Highest authority SYSADM is automatically granted to users which are part of local Administrators group.

Windows Administrative Tools->Computer Management

Notes:

  • If DB2 installed using default options, Windows External Security is enabled. Database administrator userid should be part of Windows local group DB2ADMNS. Application users should be part of DB2USRS. No fenced user (db2fenc1) is needed on Windows.

Linux:

# useradd -m db2inst1  /* This will be used to manage the DB2 instance. To be used only by Database Administrator */

# useradd -m db2fenc1 /* Needed to run non-SQL routines.  */

# useradd -m appusr /* Optional: additional users that require access to DB2 */

AIX:

# mkuser db2inst1 /* This will be used to manage the DB2 instance. To be used only by Database Administrator */

# mkuser db2fenc1 /* Needed to run non-SQL routines. */

# mkuser appusr /* Optional: additional users that require access to DB2 */

For other operating systems refer to the documentation included with O/S.

Notes:

  • UNIX/Linux. 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 can contain spaces for primary group.
  • For client installations, db2inst1 and db2fenc1 are not needed. Only the application user id is needed.

(B.1) 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 that DB2 was installed to /opt/ibm/db2
  • For client installations we assume the user id is db2inst1, replace with your own id
  • The db2icrt command creates symbolic link from /home/db2inst1/sqllib to DB2 installation directory /opt/ibm/db2

(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 <license file>". 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 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 whether the license is a trial license, open the license file with 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

  Note on Windows: Issue command from "DB2 Command Window - Administrator" shortcut, otherwise the error "LIC1435E "This license could not be added because on an I/O error" will be returned.

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) Optional: Creating database on DB2 server

 

Login as db2inst1

$HOME/sqllib/bin/db2sampl /*Creates sample employee database */

db2 "create db sample" /*Create database in $HOME/$USER/NODE0000 */

or

db2 "create db sample on /mynewpath" /* Create database in a new path outside of $HOME */

The creator of the database is granted DBADM (database administration authorities) which allows the creator to access contents of all objects inside the database. This is separate from SYSADM (db2fenc1 or member of Windows local Administrator). SYSADM will not have access to contents of database unless it was also the creator of the database. All applications ids (non-DBADM) will be able to connect to database, but not be able to access objects which were created by another user.

To grant more ids the same authority use:

db2 "connect to sample"

db2 "grant dbadm on to user newuser"

(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 Connect 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, 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 may contain a summary of the bind operation with 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"

From Administrative Tools->Data Sources (ODBC), the DB2 database aliases should appear in the System DSN tab. In this example, 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 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

Test JDBC connection can be made from the driver outside of the application.

java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://test.ibm.com:50000/SAMPLE -user db2inst1 -password 123456 -tracing
Where:
database name= server.ibm.com
database port=50000
database name = sample
userid = db2inst1
password = 123456
The -tracing parameter can be removed to make the output more legible.
(F.3) Optional: Setting up SSL/TLS
Refer to docs:

(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, 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.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5;11.1;11.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
05 July 2023

UID

swg21985636