My JUNE favourite DB2 9.7 articels

Finally, today i completed my DB2 9.7 Database administrator certification. DB2 9.7 have a good bunch of new feature that simplifies every DB2 DBAs job. Oracle Compatibility is very good feature that IBM brought into the product.

WLM which was launched as part of DB2 9.5 is enhanced in DB2 9.7 and is really one of the powerful feature in DB2 9.7. Along with it couple of new monitoring views, event monitors are released that help in identifying how data server is performing at a given point in time. New DB2 HADR feature provide read only standby.
Some of the other interesting feature that i rated are
1) CGTT
2) Index compression
3) New Monitoring views
4) New Event monitors
5) WLM enhancement
6) Online table move

and many more.....

I am not very interested in XML technology in DB2, but IBM had extended the XML capability in DPF environment which was not possible in previous releases.

DB2 9.7 and all the future version will bring really interesting extensions to DB2.

I am awaiting for DB2 9.8 pureScale for Linux. DB2 9.8 is really gone shake the market for OLTP application. It has brilliant technology running under its bonnet which is borrowed from its successful implementation in Mainframe.
 
Some of the good dw articles i find interesting for the month of June 2010 are

1) DB2 pureScale - Group Crash Recovery
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1006wucx/index.html

2) IBM InfoSphere Federation Server Best Practice
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1006lihf/index.html

3) Unleash the power of table partitioning in your DB2 9.7 warehouse
http://www.ibm.com/developerworks/data/library/techarticle/dm-1006tablepartitioning/index.html

4) IBM DB2 e-kit for Database Professional
http://www.ibm.com/developerworks/data/kits/dataprokit/index.html

5) Whats new with DB2 Express-C 9.7.2
http://www.ibm.com/developerworks/data/library/techarticle/dm-1006db2expressc972/index.html

6) Build skills for DB2 for Linux, Unix and Windows
http://www.ibm.com/developerworks/data/db2skills/?ca=dnw-1123&ca=dth-i

DB2 Client Server Connectivity and Configuration

In this post i will discuss different scenario of DB2 client connectivity to DB2 server. In each new release of DB2, the DB2 client is re-branded with different name. The table below describe the DB2 9.1, DB2 9.5 and DB2 9.7 client name.

DB2 V9.1	DB2 V9.5	DB2 V9.7
DB2 Client	IBM Data Server Client	IBM Data Server Client
DB2 Runtime Client	IBM Data Server Runtime Client	IBM Data Server Runtime Client
IBM DB2 Driver for JDBC and SQLJ	IBM Data Server Driver Package	IBM Data Server Driver Package
IBM DB2 Driver for ODBC and CLI	IBM Data Server Driver Package	IBM Data Server Driver Package

Below are link to DB2 Information that gives the details of DB2 Client Type

DB2 Version	InfoCenter Link
V9.1	http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.rn.doc/doc/c0023028.htm
V9.5	http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html
V9.7	http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

In order to connect to DB2 Server, one of this DB2 client needs to be installed. This DB2 client binary then allows the application to connect to the DB2 server. We will present two Case of DB2 client connectivity to DB2 Server.

Requirement for setup:

1)  You need to set service port on the DB2 Server through which the DB2 Client communicate with DB2 Server. On Windows, the entry is recorded in “c:\windows\System32\drivers\etc\services” file. On Linux, the entry is recorded in “/etc/services” file. The service port entry in this file is of the form  

•   service_name           port_number          /tcp
•   For Example:-    DB2_test     7777   /tcp

2) Update the DB2 Database Manager configuration SVCENAME with the service port number. The following command is executed to set the SVCENAME

• update dbm cfg using svcename DB2_test

3) DB2COMM registry variable needs to be set to value “TCPIP”. The DB2 instance needs to be bounced in order reflect the changes made to the DB2COMM registry variable. The following set of command needs to be executed in sequence to make this changes

• db2set DB2COMM=TCPIP
• db2stop force
• db2start

4) On the client, DB2 Client instance must be created if it didnt already exist. This is needed to catalog the remote DB2 database. The client is created using the following command

• db2icrt –s client {instance_name}

5) On client, the CATALOG DATABASE and CATALOG NODE command must be executed as the DB2 Client Instance owner.

Case 1:  DB2 Client is located on the same server where the DB2 Server is executing

a)  In this DB2 Client binary is NOT needed to be installed since all the files needed to connect to DB2 Server is installed as part of DB2 Server Installation. 

b) If we need to access database with different name then we need to catalog the database with different name. Following command is executed in sequence

• CATALOG DATABASE {source_db_name} AS {alias_db_name} ON {drive | path}

c) Then connect to the alias using the following command

• CONNECT TO {alias_db_name}

Case 2: DB2 Client is Located on the Remote Machine which in turn connect to DB2 Server running on different machine.

a) In this the DB2 Client need to be installed on the client machine. 

b) We need to catalog the remote instance. This step is needed to access the remote database created under that instance. Following command are executed

• CATALOG TCPIP NODE {nodename} REMOTE {hostname OR IP address} SERVER {service_port_set_on_DB2_Server}

c) We then need to catalog the database in order for client to connect to the DB2 server. Following command is executed

• CATALOG DATABASE {source_db_name} AS {alias_db_name} AT NODE {node_name_from_step_2}

d) Then connect to the alias using the following command

• CONNECT TO {alias_db_name}

Case 3: Remote application is located on the Remote Machine which want to connect to DB2 Server running on different machine using the JDBC connection.

a) Download the IBM Data Server Driver Packages client from the following website
 http://www-01.ibm.com/support/docview.wss?uid=swg24025865

Download Steps:

   1) Select "Windows 32-bit X86"

   2) Select "IBM Data Server Driver Packages"

   3) Select "fix pack: DSClients-nt32-dsdriver-9.5.500.784-FP005"

   4) Select "Download using your browser (HTTP)"

   5) Select "v9.5fp5_nt32_dsdriver_EN.exe (24.95 MB)"

b) More details on the driver can be read at below link
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

c) Driver Installation details can be read at below link
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html


d) Set the CLASSPATH environment variable to {driver_install_path}\java\db2jcc.jar 


e) Connect to DB2 Server using the JDBC URL as described by JAVA documentation.

DB2 Database Directories

When the database is created and/or catalogued in an instance, it record its details in three directory. They are System Database directory, Local database directory and Node directory

The System database directory contain all the database information. The database may be a local database or it can also show the remote database details. Local database is represented with keyword “INDIRECT” and remote database is represented by “REMOTE”.

When the connection to the DB2 Database is made, DB2 first check the details in the System Database directory. If the connection is to the “INDIRECT” database then it check the local database directory. If the connection is to the “REMOTE” database then it check the Node directory.

System Database directory is a binary file with a name SQLDBDIR. On Windows, the file can be located in DB2INSTPROF/SQLDBDIR/SQLDBDIR. On Linux, the file is located in INSTHOME/sqllib/sqldbdir/sqldbdir. The content of this directory is displayed by using the command “list db directory”. The purpose of the System database directory is to record all the database (local or remote) that are accessible.

DB2INSTPROF = DB2 Registry variable. If not set then the default value is the DB2 Installed directory path.

INSTHOME = Is the user profile variable which is set to the Instance home directory.

Local Database Directory is a binary file with the name SQLDBDIR. However, this file is different from the System Database directory binary file and also reside in different storage path. On Windows and Linux, the file can be located in DFTDBPATH/NODEXXXX/sqldbdir/sqldbdir which will list all database created in the default directory. If we need to check database in non-default directory then it can be located in /NODEXXXX/sqldbdir/sqldbdir.The content of this directory is displayed by using the command “list db directory on ”. The purpose of the local database directory is to record all the database (local only) created in specific directory. This directory also give the SQLXXXXX directory name of the database.

DFTDBPATH = DB2 Database Manager variable which show the default path for creating a new database.

Node Directory is a binary file with the name SQLNODIR. On Windows, the file can be located in DB2INSTPROF/SQLNODIR/SQLNODIR. On Linux, the file is located in INSTHOME/sqllib/sqlnodir/sqlnodir. The content of this directory is displayed by using the command “list node directory”. The purpose of the Node Directory is it record how to communicate with the remote DB2 instance.





Database Manager Configuration File (DBM CFG)

The DBM CFG files store all the Database Manager (Instance) parameter values. Each Instance has its own DBM CFG file. This is a binary file. The file is called as "db2systm". On Linux the file is located at 
INSTHOME/sqllib/db2systm





Database Configuration File (DB CFG)


The DB CFG files stores all the Database parameter values. Each database has it unique DB CFG file. This is a binary file. The file is called as "SQLDBCONF" starting from v8.2 and later.
To locate the file on Linux following step are performed
1) Get the database path
db2 list active databases



2) Change to "Database Path" directory returned from the output of previous command. The file will be located under that path





Useful Reference



1) DB2 directory Structure on Linux

2) Database directories and files

IBM Balanced Warehouse D5100 Upgrade Guide available

Finally the IBM Balanced Warehouse D5100 Upgrade Guide for Linux is available. It can be downloaded from

http://www-01.ibm.com/support/docview.wss?rs=3354&uid=swg21295194

The upgrade guide explain the complete Balanced Warehouse D5100 stack upgrade.

While reading the document i skimmed through some of the component that are upgraded.


1) OS from SLES10 SP1 to SLES10 SP2

2) InfoSphere Warehouse 9.5.2 (9.5.1 refresh) to InfoSphere Warehouse 9.7.1

3) DB2 is upgraded from DB2 9.5.3b to DB2 9.7.1

4) NFS v3 to V4


It is really great that IBM has released document for Linux first as apposed to AIX.