My DB2 experiments: June 2014

Monday, June 30, 2014

Installation methods of DB2 on Linux/Unix

Installation methods of DB2 on Linux/Unix - There are 4 methods, i will take this in a detailed with practical examples.

1) db2setup

The DB2 Setup wizard is a GUI installer available on Linux, UNIX, and Windows operating systems. The DB2 Setup wizard provides an easy-to-use interface for installing DB2 products and for performing initial setup and configuration tasks.
note: On Linux and UNIX platforms, an X server is required to display the DB2 Setup wizard.
a) uncompress the v9.7fp9_linuxx64_server.tar file
b) cd server
c) sudo ./db2prereqcheck
d) sudo ./db2setup - a GUI installer will be opened and you just need to run through the steps and complete, its pretty straightforward and easy process.

2) response file installation

a) A response file is a text file that contains setup and configuration values. The file is read by the DB2 setup program and the installation is performed according to the values that have been specified, this method of installation is also referred as silent installation as you dont need to interact during the installation process
using db2setup wizard we can generate a response file, benefit of this installation method is that you can create a response file without performing an installation and later change the options based on your requirement.
a) uncompress the v9.7fp9_linuxx64_server.tar file
b) cd server
c) sudo ./db2prereqcheck
d) sudo ./db2setup - a GUI installer will be opened and follow the steps.. at step number 4 we will have an "save my installation settings in a response file" ( No software will be installed ) - follow through rest of steps based on your requirement and you will finally get response file generated by the end of all steps.

e) sudo ./db2setup -r responsefile

b) Customizing the sample response files that are provided for each DB2 product , sample file will be found in below location you can see after untar and unzipping the file
server/db2/platform/samples , where < platform >is the platform that you are installing on
a) uncompress the v9.7fp9_linuxx64_server.tar file
b) cd server/db2/platform/samples ( here platform refers to your OS ) , here you can see sample response file
c) copy the sample to your custom location and make changes according to your requirements and the specific components that you want to install, use that edited file to install db2 product
d) sudo ./db2setup -r db2aese.rsp_filename

3) db2_install command

The db2_install command installs all components for the DB2 product, but i doesnt perform user and group creation, instance creation .
a) uncompress the v9.7fp9_linuxx64_server.tar file
b) sudo ./db2prereqcheck
c) sudo ./db2_install ( follow the response and complete the installation )

4) payload file deployment

This method is an advanced installation method that is not recommended for most users. It requires the user to physically install payload files. A payload file is a compressed tarball that contains all of the files and metadata for an installable component.
a) uncompress the v9.7fp9_linuxx64_server.tar file
b) cd server/db2/linuxamd64/FILES
c) now we need to untar needed components to the location /opt/ibm/db2/V9.7
d) now we will setup the fault monitor by running command DB2DIR/bin/db2fmcu -u -p /etc/inittab ( DB2DIR is the installation location your prouduct)
e) check which level of db2 installed using command db2greg -dump
f) create instance and setup profile file ..

First 3 ways of installation of db2 product is recommended for all users and 4th method is for most advanced users and is also not a recommend method of db2 product installation by IBM.

Let me know your comments and issues that you faced during DB2 installation on Unix and Linux. And 4th method is a very interesting one, let me know if any you faced any particular issues using 4th method.

Friday, June 27, 2014

Error SQL10013N SQLSTATE=42724 create wrapper for federation support

When we need to setup federation we need to first create a wrapper which will identify data source of the federated server.
We get this error SQL10013N as shown below, When we don't have Informix data source support installed in our server.

db2inst1@test-machine:~$ db2 "create wrapper drda"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL10013N The specified library "libdb2drda.so" could not be loaded.
SQLSTATE=42724

Below commands list the features that are installed on the server, we can seen there is no Informix Data Source Support Installed on the server

db2inst1@test-machine:~$ db2ls -q -b /opt/ibm/db2/V9.7

Install Path : /opt/ibm/db2/V9.7

Feature Response File ID Level Fix Pack Feature Description
---------------------------------------------------------------------------------------------------------------------
BASE_CLIENT 9.7.0.6 6 Base client support
JAVA_SUPPORT 9.7.0.6 6 Java support
SQL_PROCEDURES 9.7.0.6 6 SQL procedures
BASE_DB2_ENGINE 9.7.0.6 6 Base server support
JDK 9.7.0.6 6 IBM Software Development Kit (SDK) for Java(TM)
COMMUNICATION_SUPPORT_TCPIP 9.7.0.6 6 Communication support - TCP/IP
REPL_CLIENT 9.7.0.6 6 Replication tools
DB2_DATA_SOURCE_SUPPORT 9.7.0.6 6 DB2 data source support
LDAP_EXPLOITATION 9.7.0.6 6 DB2 LDAP support
INSTANCE_SETUP_SUPPORT 9.7.0.6 6 DB2 Instance Setup wizard
SPATIAL_EXTENDER_CLIENT_SUPPORT 9.7.0.6 6 Spatial Extender client
APPLICATION_DEVELOPMENT_TOOLS 9.7.0.6 6 Base application development tools

As we identified the problem now we shall install the Informix Data source support, i'm using response file installation for this as its a single component installation. Just copy below things and change the parameter "FILE" according to your already installed location of db2 server

PROD = ADVANCED_ENTERPRISE_SERVER_EDITION
FILE = /opt/ibm/db2/V9.7_01
LIC_AGREEMENT = ACCEPT
INSTALL_TYPE = CUSTOM
COMP = INFORMIX_DATA_SOURCE_SUPPORT

I will save above 5 lines in /home/db2inst1/resp

Now i will install the single component informix_data_source_support using below command

db2inst1@test-machine:~/server$ sudo ./db2setup -r /home/db2inst1/resp
WARNING:
DBT3534W The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.
WARNING:
The 32-bit library file libpam.so is not found on the system.
DBI1191I db2setup is installing and configuring DB2 according to the
response file provided. Please wait.

A minor error occurred while installing "DB2 Advanced Enterprise Server
Edition " on this computer. Some features may not function correctly.
For more information see the DB2 installation log at "/tmp/db2setup.log".

Once done i have connected to database and was able to create wrapper successfully

db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ db2 connect to attempt

Database Connection Information

Database server = DB2/LINUXX8664 9.7.6
SQL authorization ID = DB2INST1
Local database alias = ATTEMPT

db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ db2 create wrapper drda
DB20000I The SQL command completed successfully.

Sunday, June 8, 2014

Changing the data type of a column in replicated table in DB2 Q Replication

db2inst1@sairam:~$ db2 describe table task

Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TASK_ID SYSIBM INTEGER 4 0 No
TASK_INFO SYSIBM VARCHAR 10 0 Yes
SALARY SYSIBM VARCHAR 10 0 Yes

3 record(s) selected.

We want to change SALARY column datatype from varchar(10) to char(20)

Steps to change the data type:

1) Stop the subscription by passing capstop signal
2) stop the data capture changes to none on both databases
3) alter the column with the new data type on both sides
4) start the data capture changes to yes on both databaes
5) Start the subscription by passing capstart signal

1) db2inst1@sairam:~$ db2 connect to testb

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB

db2inst1@sairam:~$ db2 "insert into ASNTB.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTOP','TASK0002')"
DB20000I The SQL command completed successfully.

2) db2inst1@sairam:~$ db2 alter table task data capture none - ( on both databases )
DB20000I The SQL command completed successfully.

3) db2inst1@sairam:~$ db2 "alter table task alter column task_info set data type char(20)" - ( on both databases )
DB20000I The SQL command completed successfully.

4) db2inst1@sairam:~$ db2 alter table task data capture changes
DB20000I The SQL command completed successfully.

5) db2inst1@sairam:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTART','TASK0001')" - ( on one side )
DB20000I The SQL command completed successfully.

Resolving conflicts due to particular sqlstate in DB2 Q Replication

I had a situation when i tried to insert a row in source which was detected as conflict at target so Q Apply program stopped processing, so now if we want that transaction to be ignored we need to tell q apply program to ignore it .

2014-06-08-12.47.57.547884 ASN0552E "Q Apply" : "ASNTA" : "BR00000AG012" : The program encountered an SQL error. The server name is "". The SQL request is "EXEC". The table name is "run SQL". The SQLCODE is "-668". The SQLSTATE is "57016". The SQLERRMC is "". The SQLERRP is "SQLDTBLR".
2014-06-08-12.52.03.789464 <brwzMain> ASN7524E "Q Apply" : "ASNTA" : "BR00000" : The Q Apply program encountered an error or conflict for Q subscription "STAFF0002" (receive queue "ASNTB.TESTB_TO_ASNTA.TESTA.DATA", replication queue map "TESTB.ASNTB_TO_TESTA.ASNTA"). It stopped reading from the receive queue. The error occurred while applying a row from the transaction corresponding to the LSN "0000:0000:0000:04d6:82d3".

Below are steps the pass to ibmqrep_targets to ignore that transaction for particular sql state.

db2inst1@sairam:~$ db2 connect to testa

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTA

db2inst1@sairam:~$ db2 "update asnta.ibmqrep_targets set oksqlstates='57016' where subname='STAFF0002'"
DB20000I The SQL command completed successfully.

Then it had successfully ignored that transaction alone and q apply started processing the Receive queue.

Resetting Q subscriptions that are in inconsistent states in DB2 Q Replication

Q subscriptions are considered to have inconsistent states when the state (active, inactive, new) is different between the source and target servers. In such cases we can't activate the subscriptions without bringing them into same state(inactive, new). I was able to propagate a situation where there is a table "STAFF" for which the state between source and targets was different.

Below are the subnames and their state from both databases we can observe the state as G for subname STAFF0001 in TESTA database, where as state as I for subname STAFF0002 in TESTB database

db2inst1@sairam:~$ db2 connect to testa

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTA

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

SUBNAME STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001 A
STAFF0001 G

2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testb

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

SUBNAME STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002 A
STAFF0002 I

2 record(s) selected.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Steps to reset the incosistent states in Q Replication

1) Stop capture and apply processes this is important before you update the state of subs and targets

2) UPDATE ASNTA.IBMQREP_SUBS SET STATE='I', STATE_TRANSITION=NULL WHERE SUBNAME='STAFF0001';
UPDATE ASNTA.IBMQREP_TARGETS SET STATE='I' WHERE SUBNAME='STAFF0002';

3) Start the capture and apply process now. We have state as INACTIVE in both sides so lets activate the subscription in the next step

4) INSERT INTO ASNTA.IBMQREP_SIGNAL (SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN) VALUES ('CMD','CAPSTART','STAFF0001');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

db2inst1@sairam:~$ db2 connect to testb

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

SUBNAME STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002 A
STAFF0002 A

2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testa

Database Connection Information

Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTA

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

SUBNAME STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001 A
STAFF0001 A

2 record(s) selected.

My DB2 experiments