Adding a new column to replicated table in DB2 Q Replication

This tutorial will help you understand on how to add a new column to table which is already replicated, This tutorial applies for Q Replication with bidirectional setup ( active - active solution ) that i was using in my earlier posts. Description of table is below to which we shall add one new column, name it as ADDRESS VARCHAR(10)

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  

  2 record(s) selected.


Basic steps are like this

1) Check the subscription of the table on source and target and see if it active before we add a new column
2) Add the column with normal alter table add column command to the source table
3) Pass the signal to replication so it can understand that we have added a new column to table
4) Check on the target side if the column is added or not

1)

db2inst1@murali:~$ db2 connect to testa

   Database Connection Information

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

db2inst1@murali:~$ db2 "select subname,state from asnta.ibmqrep_subs where source_name='DBA'"

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

  1 record(s) selected.

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@murali:~$ db2 "select subname,state from asntb.ibmqrep_subs where source_name='DBA'"

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

  1 record(s) selected.


2)

db2inst1@murali:~$ db2 "alter table dba add column address varchar(10)"
DB20000I  The SQL command completed successfully.

3)

db2inst1@murali:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL(SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN,SIGNAL_STATE) values ( CURRENT TIMESTAMP,'CMD','ADDCOL','DBA0001;ADDRESS','P' )"
DB20000I  The SQL command completed successfully.

4)

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  
ADDRESS                         SYSIBM    VARCHAR                     10     0 Yes

  3 record(s) selected.

All the commands used in this post are highlighted in ITALIC

Learning DB2 Basics

Its always good to learn basics so let me share you some best books and tutorials.

1) Learning db2 visually with examples 2nd edition 2008 - this is my favorite book for DB2 learners . I got a soft copy luckily somewhere in internet, Often i read it on GS3 when i get bored ..

Link to download : https://www.mediafire.com/?cuy2zr07roj3et1  ( please don't share any alternative links as i want to see the original downloads count )

2) Want to learn db2 in video tutorials, luckily we have one for that as well. We can even download offline copy to watch it. You have to signup using Google or some... Its has two parts of tutorials. Once you read everything you can also take test to evaluate your learning's.

1) DB2 Essential Training I  (DB101EN)

2) DB2 Essential Training II (DB102EN)

Link for video tutorials : http://bigdatauniversity.com/courses/course/view.php?id=81&justenroled=1#

Automatically reroute your client requests in DB2 HADR in the event of failure

You can use automatic client reroute (ACR) with high availability disaster recovery (HADR) to transfer client application requests from a failed database server to a standby database server.

Today i will show how to implement it practically and see how it works. I'm using the same setup that i have did in my earlier post on how to setup DB2 HADR, so refer to that and setup in your environment.


Basic steps are like these.

1) Setup HADR pair and see that they are in peer mode.
2) UPDATE ALTERNATIVE SERVER configuration on the primary and standby database.
3) Catalog the primary database at the standby node/instance
4) takeover the hadr on standby database
5) run the query to test it and see if we are connected to standby from client


1) Setup HADR and check if HADR pair is in peer state or not

db2pd -db char -hadr

2)
on primary:
db2 update alternate server for database char using hostname sairam port 65000

on standby:
db2 update alternate server for database char using hostname sairam port 64000

3)
on standby:

db2 catalog tcpip node primeno remote sairam server 64000
db2 catalog database char as apple at node primeno

from standby instance connect to primary database which is cataloged as local database

db2inst2@sairam:~$ db2 connect to apple user db2inst1
Enter current password for db2inst1:

   Database Connection Information

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

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

Note : Dont close this session as we need to check the same query from same session as described in step 5

4)

on standy: use a different session to give this command

Issue a takeover on the standby database to switch the HADR role

db2inst2@sairam:~$ db2 takeover hadr on database char
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

5)

from the same session from step 3 - Issue the same select query again, It will display us the warning message as connection to old database has been lost and trying to connect to new primary database. As show below If you give select query with out ur isolation level it throws error because we are connected to standby database and we need to maintain UR isolation level.

db2inst2@sairam:~$ db2 "select * from  tab1"
SQL30108N  A connection failed but has been re-established. Special register
settings might have been replayed. Host name or IP address of the new
connection: "sairam". Service name or port number of the new connection:
"64000".  Reason code: "1".  SQLSTATE=08506

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
SQL1773N  The statement or command requires functionality that is not
supported on a read-enabled HADR standby database. Reason code = "1".

db2inst2@sairam:~$ db2 "select * from  tab1 with ur"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

All commands that i used in this post are highlighted in ITALIC 

8 steps to setup DB2 HADR solution

High Availability disaster Recovery solution HADR in DB2 is one of robust ways of maintaining two db2 databases in SYNC for high availability solutions.

We can use primary database for read,write operations and standby database for read operations or completely for disaster solution in case if primary fails.
Setting up HADR in DB2 is one of the easiest one that i ever worked, it took me only half hour to setup and test the solution.

Basic step are like these.
create a database on one machine/instance
set HADR DB parameters on primary database
backup database on primary
restore the image in standby machine/instance
set HADR DB parameters on standby database
start hadr on standby first then on primary database
Validate the HADR setup

Here is how my setup look like

1) db2 create database samsung

2) db2 update db cfg for samsung using logarchmeth1 DISK:/home/db2inst1/logs

3) Setting up HADR cfg parameters on primary database

update db cfg for samsung using HADR_LOCAL_HOST sairam
update db cfg for samsung using HADR_LOCAL_SVC 64000
update db cfg for samsung using HADR_REMOTE_HOST sairam
update db cfg for samsung using HADR_REMOTE_SVC 65000
update db cfg for samsung using HADR_REMOTE_INST db2inst2
update db cfg for samsung using LOGINDEXBUILD ON

4) Take an offline backup to be used for setting HADR, move the backup image (from the primary machine/instance) to the standby machine/instance

5) restore the database on standby instance

db2 restore db samsung taken at 20140509113342 on /home/db2inst2/ into apple

6) Setting up HADR cfg parameters on standby database

update db cfg for apple using HADR_LOCAL_HOST sairam
update db cfg for apple using HADR_LOCAL_SVC 65000
update db cfg for apple using HADR_REMOTE_HOST sairam
update db cfg for apple using HADR_REMOTE_SVC 64000
update db cfg for apple using HADR_REMOTE_INST db2inst1

7) Starting up HADR on the standby server

db2 start hadr on database apple as standby

8) Starting up HADR on the primary server

db2 start hadr on database samsung as primary

Finally validating the HADR setup is important.

create a table and populate some 5 rows at primary site, switch the between the roles and check if the table available in standby database.

Installing DB2 fixpack

Install DB2 Fix packs – Below steps are intended for Linux, Unix operating systems.

1) Perform the necessary tasks before installing a fix pack.
2) Choose a fix pack installation method and install the fix pack.
3) Perform the necessary tasks after installing the fix pack.
4) Apply the appropriate DB2 database product license.

1) Perform the necessary tasks before installing a fix pack

a) If you already have DB2 database product installed, the space required to install the fix pack is slightly greater than the space consumed by the existing DB2 database products. This space is only required temporarily during the fix pack installation process.

To determine the space used by the existing DB2 database products, perform the command:

du -h -s DB2DIR

where DB2DIR represents the location where the DB2 copy is installed.

b) Download the fix pack

c) Uncompress the fixpacks, use the below command

gunzip -c filename.tar.gz | tar -xvf - 

d) Before installing a fix pack, if there are DB2 database products installed in the selected installation path, you must stop all of the DB2 processes. If you have multiple DB2 copies, stop only the DB2 processes that are associated with the copy that you are updating.
Determine which instances are associated with the DB2 copy.

Issue the command: DB2DIR/instance/db2ilist

where DB2DIR represents the location where the DB2 copy is installed.

su - iname
db2 force application all
db2 terminate
db2stop
exit

If the DB2 Administration Server (DAS) belongs to the DB2 copy that you are updating, stop the DAS:
su - aname
db2admin stop
exit

where aname represents the DAS owner name.

e) Optional: On AIX, run slibclean to unload unused shared libraries from memory before installation:

/usr/sbin/slibclean

f) Disable the fault monitor processes. To stop the Fault Monitor Daemon, issue the command:

DB2DIR/bin/db2fm -i iname -D

where DB2DIR is the location where the DB2 copy is installed and iname represents the instance owner name. The command must be performed once for each instance in the DB2 copy.

g) Ensure all DB2 interprocess communications are cleaned for the instance to be updated. As the instance owner, run the following command at each physical partition:

$HOME/sqllib/bin/ipclean

2) Choose a fix pack installation method and install the fix pack.

There are 2 ways that you use the fixpack and installation methods that match the purpose.

1) Follow these instructions to install new DB2 database products at a particular fix pack level. The db2setup command is used to perform the installation.

Change to the directory that contains the fix pack image.

Launch the installation by issuing the command: ./db2setup

2) Follow these instructions if a DB2 database product is already installed and you want to apply a new fix pack level. The installFixPack command is used to install the fix pack.

You have two choices when it comes to fix pack installation; using a universal fix pack (all products) or a product-specific fix pack.

Why would I use the universal fix pack?
If I want to upgrade multiple products in place.
If I do not remember what I have installed to be able to install the fix pack in a new location without losing my current set of features or products.

Why would I use the product-specific fix pack?
If I have a single product and want to save the time to download the fix pack.
If system downtime is costly.
If I need to test the new fix pack without impacting my production databases.
If I need the ability to go back to the previous version quickly (system downtime in case of failure).
If I already have a fix pack installed and need to add new features or products to the fix pack.
Note: Many of these advantages can be achieved by installing from your original media to a new location and using the universal fix pack to upgrade features or products, however, there is a cost of extra installation time.

Launch the installation by issuing the installFixPack command. For example,

./installFixPack -b DB2DIR

where DB2DIR is the location of the DB2 database products that you want to update.

Note: I had seen below error in Linux environment.

Stopping DB2 Fault Monitor :.......Success
ERROR: The installFixPack command detected some DB2 libraries are still loaded
in memory and some applications might still be running in the current
installation copy.  All applications must be stopped. See the fix pack readme
for pre-installation instructions, and re-run the installFixPack command.
Alternatively, to override automatic checking, you can re-issue the
installFixPack command with the '-f db2lib' parameter. Note: If you re-issue
the installFixPack command with the '-f db2lib' parameter, after the DB2
instances are updated, some applications might not work properly and might need
to be restarted to function properly against the updated DB2 instance.

Preparing the system :.......Failure
Change to the directory that contains the fix pack image.

If you get error as above use the below command, and complete the install.

./installFixPack -b DB2DIR –f db2lib

3) Perform the necessary tasks after installing the fix pack.

a) For each instance, issue the command:

DB2DIR/instance/db2iupdt iname

where iname represents the instance name and DB2DIR represents the location where the DB2 copy is installed.

b) If the DB2 Administration Server (DAS) belongs to the DB2 copy where you installed the fix pack, issue the command:

DB2DIR/instance/dasupdt

where DB2DIR is the location where the DB2 copy is installed. If this DB2 copy is now running at a more recent fix pack level than all of the other DB2 copies, consider updating the DAS to belong to this DB2 copy.

c) Update the system catalog objects in your databases to support the fix pack.

db2updv97 -d dbname
where dbname represents the name of the database.

Note: Backup your database before running db2updv97. Some system objects might become unusable after moving back to an earlier fix pack, and your database will need to be restored.

d) Restart the instance , DAS admin

su - iname
db2start

su - dasuser
db2admin start

e) As part of a fix pack installation on the server, binding of the database utilities (IMPORT, EXPORT, REORG, the Command Line Processor) and the CLI bind files occurs automatically. However, if you install a fix pack on the client or an error occurs, you can manually bind the database utilities and the CLI bind files.

If you installed the fix pack on DB2 database products that have existing databases, perform the following commands once for each database:

db2 terminate
db2 CONNECT TO dbname user USERID using PASSWORD
db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE 
db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD 
db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD 
db2 terminate

If you installed the fix pack on DB2 database products that have existing databases, rebind the packages by running the REBIND or db2rbind command.

db2rbind dbname -l  fullpath/logfile all 
fullpath refers to a valid path given with a name to log the rebind output

4) Apply the appropriate DB2 database product license.

INSTHOME/sqllib/adm/db2licm -a filename

Where INSTHOME represents the home directory of the instance owner and filename is the full path name and file name for the license file
Check the license information once after applying the license.

db2licm -l

All the commands used in the post are highlighted as Italic