tag:blogger.com,1999:blog-32664466658170709902024-03-13T06:26:15.309+05:30My DB2 experimentsThis blog has all my experiments and working truth with DB2 murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-3266446665817070990.post-35378924153048006432014-10-26T18:50:00.001+05:302014-10-26T18:52:01.253+05:30SQL1220N The database manager shared memory set cannot be allocated<div dir="ltr" style="text-align: left;" trbidi="on">
I faced this error while using db2 on Ubuntu 12.04 desktop, When tried to start instance it failed as below.<br />
<br />
SQL1220N<br />
<br />
The database manager shared memory set cannot be allocated.<br />
<br />
The term shared memory is a term used to describe a type of memory management in the Unix kernel. It is a memory region that can shared between different processes. Using a shared memory model, this avoids creating duplicate copies of memory. So programs can eaily share data.<br />
SHMMAX is just the maximum size of a single shared memory segment. It’s size is represented in bytes”. <br />
<br />
By default SHMMAX is 32 MB , Here db2 database manager wasn't able to start the instance with existing SHMMAX value use cat /proc/sys/kernel/shmmax to find out current value, so What we do is increase the value<br />
<br />
Place an entry in /etc/sysctl.conf like kernel.shmmax=500000000, which is around 500MB just restart the machine and we are good to go. If not working increase it to 1 gb or more.<br />
<br />
db2inst1@ubuntu:~$ db2start<br />
10/26/2014 18:50:07 0 0 SQL1063N DB2START processing was successful.<br />
SQL1063N DB2START processing was successful.</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com2tag:blogger.com,1999:blog-3266446665817070990.post-30185018117910823732014-09-17T12:12:00.001+05:302014-09-17T12:17:31.617+05:30db2_install or db2icrt or db2setup fails with memory corruption error on Ubuntu OS<div dir="ltr" style="text-align: left;" trbidi="on">
While i started installation of DB2 data server trail v 10.1 FP0 i got struck with memory corruption error and wasn't able to move forward in installation. So solution to this problem is to install RPM package or else need to move to next fix pack level 1 FP1.<br />
<br />
Problem :<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-4vnwUrqXb_Aeq3OjW7O5a7bwilYOyobJ3hzg6YcNGh2ceIzQ4qBEA8O-h2lxP2zD9Tbn_y4zdEr1-QseN6V1Dn8B0Z8pog__2R6ax4taoenlyUb0NzujFwTmoXudHOSEpJbkAHrjRW8/s1600/error.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-4vnwUrqXb_Aeq3OjW7O5a7bwilYOyobJ3hzg6YcNGh2ceIzQ4qBEA8O-h2lxP2zD9Tbn_y4zdEr1-QseN6V1Dn8B0Z8pog__2R6ax4taoenlyUb0NzujFwTmoXudHOSEpJbkAHrjRW8/s1600/error.JPG" height="224" width="640" /></a></div>
<br />
<br />
<span style="color: #3d85c6;"><span style="color: black;">db2inst1@ubuntu:~/server$ <b><i>sudo ./db2setup </i></b></span></span><span style="background-color: black;"><span style="color: #4c1130;"><span style="background-color: white;"> </span></span></span><br />
<br />
<span style="background-color: black;"><span style="color: #4c1130;"><span style="background-color: white;">DBI1190I db2setup is preparing the DB2 Setup wizard which will guide<br /> you through the program setup process. Please wait.<br /><br /><br />*** glibc detected *** /tmp/db2.tmp.2521/db2/linuxamd64/install/../java/jre/bin/java: malloc(): memory corruption: 0x00007f35880c0ab0 ***<br />======= Backtrace: =========<br />/lib/x86_64-linux-gnu/libc.so.6(+0x7ec66)[0x7f358f61dc66]<br />/lib/x86_64-linux-gnu/libc.so.6(+0x80e2b)[0x7f358f61fe2b]<br />/lib/x86_64-linux-gnu/libc.so.6(__libc_malloc+0x75)[0x7f358f6220b5]<br />/usr/lib/x86_64-linux-gnu/libstdc++.so.6(_Znwm+0x1d)[0x7f356f98dded]</span></span></span><br />
<br />
<span style="background-color: black;"><span style="color: #4c1130;"><span style="background-color: white;"></span></span></span><br />
<br />
<span style="color: #0b5394;"><span style="background-color: white;"> <span style="color: black;">Solution : <i><b>sudo apt-get install rpm</b></i></span></span></span><br />
<span style="background-color: black;"><span style="color: #4c1130;"><span style="background-color: white;"> </span></span></span><br />
<span style="background-color: black;"><span style="color: #4c1130;"><span style="background-color: white;">Here is the problem document from IBM link :</span></span></span><br />
<br />
<a href="http://www-01.ibm.com/support/docview.wss?uid=swg21611103" target="_blank">http://www-01.ibm.com/support/docview.wss?uid=swg21611103 </a></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-9693495106647901142014-07-28T17:49:00.002+05:302014-07-28T17:52:15.063+05:30Stop Q Replication in a Right Way<div dir="ltr" style="text-align: left;" trbidi="on">
Everyone must understand the importance of proper way to stop Q replication.<br />
I bet you that your setup will not work properly if you don't do that right way, after many tests personally i prefer the below way to my stop my replication safely.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqXtcbL6JJP4tfityWBuJr8xru2dOXKzBNA0iXbMpCIKT0fETTKo3rf9q_p5gUgbtZ8QnEOD866N8Yg6Bo8mY7NMc7VBbgHc0eoeUZA9rr3h-b59QaWKPES_QD4MTo-Qqd7U8mEUcDWbQ/s1600/moral.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqXtcbL6JJP4tfityWBuJr8xru2dOXKzBNA0iXbMpCIKT0fETTKo3rf9q_p5gUgbtZ8QnEOD866N8Yg6Bo8mY7NMc7VBbgHc0eoeUZA9rr3h-b59QaWKPES_QD4MTo-Qqd7U8mEUcDWbQ/s1600/moral.jpg" height="211" width="320" /></a></div>
<br />
<br />
<b><u>steps:</u></b><br />
<b><br /></b>
<br />
<h3 style="text-align: left;">
<b>A) stop the capture and apply programs that are running</b></h3>
<br />
<i>asnqccmd capture_server=TESTA capture_schema=ASNUA</i><br />
<i>asnqacmd apply_server=TESTB apply_schema=ASNUB</i><br />
<br />
<h3 style="text-align: left;">
<b>B) stop the listener and channels</b></h3>
<br />
We need to login into queue manager to stop the listener and channels<br />
<br />
db2inst1@sairam:~/setup$ <b>runmqsc QMUA</b><br />
5724-H72 (C) Copyright IBM Corp. 1994, 2011. ALL RIGHTS RESERVED.<br />
Starting MQSC for queue manager QMUA.<br />
<i>stop listener(REPL_LSTR)</i><br />
1 : stop listener(REPL_LSTR)<br />
AMQ8706: Request to stop WebSphere MQ Listener accepted.<br />
<i>stop channel(QMUA_TO_QMUB)</i><br />
2 : stop channel(QMUA_TO_QMUB)<br />
AMQ8019: Stop WebSphere MQ channel accepted.<br />
<i>stop channel(QMUB_TO_QMUA)</i><br />
3 : stop channel(QMUB_TO_QMUA)<br />
AMQ8019: Stop WebSphere MQ channel accepted.<br />
<i>end</i><br />
4 : end<br />
<b>4 MQSC commands read.</b><br />
<b>No commands have a syntax error.</b><br />
<b>All valid MQSC commands were processed.</b><br />
<br />
db2inst1@sairam:~/setup$ <i>runmqsc QMUB</i><br />
5724-H72 (C) Copyright IBM Corp. 1994, 2011. ALL RIGHTS RESERVED.<br />
Starting MQSC for queue manager QMUB.<br />
<i>stop listener(REPL_LSTR)</i><br />
1 : stop listener(REPL_LSTR)<br />
AMQ8706: Request to stop WebSphere MQ Listener accepted.<br />
<i>stop channel(QMUA_TO_QMUB)</i><br />
2 : stop channel(QMUA_TO_QMUB)<br />
AMQ9533: Channel 'QMUA_TO_QMUB' is not currently active.<br />
<i>stop channel(QMUB_TO_QMUA)</i><br />
3 : stop channel(QMUB_TO_QMUA)<br />
AMQ8019: Stop WebSphere MQ channel accepted.<br />
end<br />
4 : <i>end</i><br />
<b>4 MQSC commands read.</b><br />
<b>No commands have a syntax error.</b><br />
<b>All valid MQSC commands were processed.</b><br />
<br />
<h3 style="text-align: left;">
<b>C) stop the queue managers</b></h3>
<br />
db2inst1@sairam:~/setup$ <i>endmqm QMUA</i><br />
Quiesce request accepted. The queue manager will stop when all outstanding work<br />
is complete.<br />
db2inst1@sairam:~/setup$ <i>endmqm QMUB</i><br />
Quiesce request accepted. The queue manager will stop when all outstanding work<br />
is complete.<br />
<br />
Thinking complicated ? I do this every time and everyday but i prepared a shell script to do all these. Same way i have a script to start the replication... feeling easy :)<br />
<br />
########## START ###############<br />
#<b>input file(inputs) for the stop script(stop_qm)</b><br />
<i>stop listener(REPL_LSTR)</i><br />
<i>stop channel(QMUA_TO_QMUB)</i><br />
<i>stop channel(QMUB_TO_QMUA)</i><br />
######### END #################<br />
<br />
########## START #############################<br />
#<b>Put below lines a script and execute it from normal command line as a shell script</b><br />
#<b>to stop capture and apply programs</b><br />
<i>asnqccmd capture_server=TESTA capture_schema=ASNUA</i><br />
<i>asnqacmd apply_server=TESTB apply_schema=ASNUB</i><br />
#<b>allow capture and apply programs to go down properly and execute next command i will SLEEP FOR 10 secs</b><br />
<i>sleep 10</i><br />
#<b>to stop listener and channels use a input file(inputs) to the queue manager</b><br />
<i>runmqsc QMUA < $1</i><br />
<i>runmqsc QMUB < $1</i><br />
#<b>stop queue managers</b><br />
<i>endmqm QMUA</i><br />
<i>endmqm QMUB</i><br />
########## END ###############################<br />
<br />
<br />
Now let us execute the script<br />
<br />
<i>sh stop_qm inputs</i></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-18646449962776348852014-07-28T16:54:00.003+05:302014-07-28T16:54:55.712+05:30Quick setup of Unidirectional Q Replication between two DB2 databases<div dir="ltr" style="text-align: left;" trbidi="on">
Its been many days that i blogged, and today I have enough time to write on DB2 Unidirectional Q Replication between two DB2 databases.<div>
<br /></div>
<div>
Q Replication is high availability concept in DB2 , used to replicate data from one database to another database.Today i will show a practical demonstration on how to setup DB2 Unidirectional replication between 2 DB2 databases. Unidirectional replication is nothing but replicating data only in ONE direction. High level picture of my setup is as below.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz9LVU55RFIQ92hqMUcwSUVll7camUTUeelRjUnJzuwpaGQMcw1x6AwRQESwlANXxgsJbJnAYX8eq4zYm06o5a4mWaxHGUieIc7SRB6qRXLZD3tmRhSlyVVyMlRGUL1cVep_Sol8Lf7FY/s1600/hih.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz9LVU55RFIQ92hqMUcwSUVll7camUTUeelRjUnJzuwpaGQMcw1x6AwRQESwlANXxgsJbJnAYX8eq4zYm06o5a4mWaxHGUieIc7SRB6qRXLZD3tmRhSlyVVyMlRGUL1cVep_Sol8Lf7FY/s1600/hih.JPG" height="324" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /><b>Required:</b></div>
<div>
<br /></div>
<div>
Test box installed with DB2 V9.7 and Websphere MQ V7.1 and 2 databases created TESTA, TESTB</div>
<div>
<br /></div>
<div>
<b>High level steps:</b></div>
<div>
<br /></div>
<div>
1) Create necessary Websphere MQ objects to setup unidirectional replication</div>
<div>
2) Create control tables needed for capture and apply servers</div>
<div>
3) Create Replication Queue map</div>
<div>
4) Create a subscription and start capture and apply programs and populate the data to observe replication setup</div>
<div>
<br /></div>
<div>
Create Websphere MQ objects : We need certain objects to enable us to setup replication in unidirectional between two databases, check the script below and make necessary changes according the machine details</div>
<div>
<br /></div>
<div>
<div>
<i>ASNCLP SESSION SET TO Q REPLICATION;</i></div>
<div>
<i>CREATE MQ SCRIPT CONFIG TYPE <b>U</b></i></div>
<div>
<i>MQSERVER 1 NAME <b>TESTA</b> MQHOST "<b>sairam</b>" MQPORT <b>2510</b> QMANAGER <b>QMUA</b> QNAME_QUAL <b>ASNUA</b>,</i></div>
<div>
<i>MQSERVER 2 NAME <b>TESTB</b> MQHOST "<b>sairam</b>" MQPORT <b>2511</b> QMANAGER <b>QMUB</b> QNAME_QUAL <b>ASNUB</b>;</i></div>
</div>
<div>
<br /></div>
<div>
Put in a file and run it with asnclp command line as below it will create 2 batch files and 2 script files we shall use scripts as were are in linux environment, grant execute permission for the scripts and execute them</div>
<div>
<i><br /></i></div>
<div>
<i>asnclp -f create_mqobjects</i></div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>ls -lrt</i></div>
<div>
-rw-rw-r-- 1 db2inst1 db2inst1 3931 Jul 28 13:17 qrepl.testa.mq_windows.bat</div>
<div>
-rw-rw-r-- 1 db2inst1 db2inst1 3922 Jul 28 13:17 qrepl.testa.mq_aixlinux.sh</div>
<div>
-rw-rw-r-- 1 db2inst1 db2inst1 3976 Jul 28 13:17 qrepl.testb.mq_windows.bat</div>
<div>
-rw-rw-r-- 1 db2inst1 db2inst1 3967 Jul 28 13:17 qrepl.testb.mq_aixlinux.sh</div>
</div>
<div>
<br /></div>
<div>
<i>chmod 755 qrepl.testa.mq_aixlinux.sh qrepl.testb.mq_aixlinux.sh</i></div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>./qrepl.testa.mq_aixlinux.sh </i></div>
<div>
There are 19 days left in the trial period for this copy of WebSphere MQ.</div>
<div>
WebSphere MQ queue manager created.</div>
<div>
Directory '/var/mqm/qmgrs/QMUA' created.</div>
<div>
The queue manager is associated with installation 'Installation1'.</div>
<div>
Creating or replacing default objects for queue manager 'QMUA'.</div>
<div>
Default objects statistics : 71 created. 0 replaced. 0 failed.</div>
<div>
Completing setup.</div>
<div>
Setup completed.</div>
</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>./qrepl.testb.mq_aixlinux.sh</i> </div>
<div>
There are 19 days left in the trial period for this copy of WebSphere MQ.</div>
<div>
WebSphere MQ queue manager created.</div>
<div>
Directory '/var/mqm/qmgrs/QMUB' created.</div>
<div>
The queue manager is associated with installation 'Installation1'.</div>
<div>
Creating or replacing default objects for queue manager 'QMUB'.</div>
<div>
Default objects statistics : 71 created. 0 replaced. 0 failed.</div>
<div>
Completing setup.</div>
<div>
Setup completed.</div>
</div>
<div>
<br /></div>
<div>
By the end of this you will see two queue manager up and running, check using command</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>dspmq</i></div>
<div>
QMNAME(<b>QMUA</b>) STATUS(<b>Running</b>)</div>
<div>
QMNAME(<b>QMUB</b>) STATUS(<b>Running</b>)</div>
</div>
<div>
<br /></div>
<div>
<b>Create control tables for capture and apply servers and Replication Queue Map </b>: The control tables at the Q Capture server contain information about data sources, options for Q subscriptions or publications, operating parameters for the Q Capture program, Q Capture performance statistics, and other metadata. The control tables at the Q Apply server contain Q Apply operating parameters, Q subscription definitions, performance statistics, and other metadata. Replication Queue Map, which identifies the WebSphere MQ queues on both servers that are used to communicate between the servers.</div>
<div>
<br /></div>
<div>
I will add 2 and 3 steps in same script as show below to create control tables for capture and apply and replication queue map</div>
<div>
<br /></div>
<div>
<div>
<i>asnclp session set to q replication;</i></div>
<div>
<i>set server capture to dbalias <b>TESTA</b> dbname <b>TESTA</b> id "<b>db2inst1</b>" password "<b>db2inst1</b>";</i></div>
<div>
<i>set server target to dbalias <b>TESTB</b> dbname <b>TESTB</b> id "<b>db2inst1</b>" password "<b>db2inst1</b>";</i></div>
<div>
<i><br /></i></div>
<div>
<i>set capture schema source <b>ASNUA</b>;</i></div>
<div>
<i>set apply schema <b>ASNUB</b>;</i></div>
<div>
<i>set qmanager <b>QMUA</b> for capture schema;</i></div>
<div>
<i>set qmanager <b>QMUB</b> for apply schema;</i></div>
<div>
<i>set run script now stop on sql error on;</i></div>
<div>
<i><br /></i></div>
<div>
<i>create control tables for capture server using mqdefaults;</i></div>
<div>
<i>create control tables for apply server ;</i></div>
<div>
<i>create replqmap <b>ASNUA_TO_ASNUB</b> using mqdefaults;</i></div>
</div>
<div>
<br /></div>
<div>
Put the commands in a file and run it with asnclp command line, please observe that all objects are created successfully, you should see a line after execute successful execution of command when you execute script</div>
<div>
<br /></div>
<div>
<i>asnclp -f crt_rest</i></div>
<div>
<br /></div>
<div>
<b>ASN1514I The replication action ended at "Monday, 28 July, 2014 1:23:10 PM IST" with "1" successes, "0" errors, and "0" warnings.</b></div>
<div>
<b><ClpInfo2Log:: The SQL command completed successfully.></b></div>
<div>
<br /></div>
<div>
<b><br /></b></div>
<div>
<b>Create a subscription and start capture and apply programs and populate the data to observe replication setup:</b></div>
<div>
<b><br /></b></div>
<div>
create a table customer in db2inst1 schema in both database before hand and Use the below script that creates a unidirectional subscription for table <b>DB2INST1.CUSTOMER</b></div>
<div>
<br /></div>
<div>
<div>
ASNCLP SESSION SET TO Q REPLICATION;</div>
<div>
set server capture to dbalias TESTA dbname TESTA id "db2inst1" password "db2inst1";</div>
<div>
set server target to dbalias TESTB dbname TESTB id "db2inst1" password "db2inst1";</div>
<div>
<br /></div>
<div>
set capture schema source ASNUA;</div>
<div>
set apply schema ASNUB;</div>
<div>
<br /></div>
<div>
set qmanager QMUA for capture schema;</div>
<div>
set qmanager QMUB for apply schema;</div>
<div>
set run script later;</div>
<div>
set output capture script newsubcap;</div>
<div>
set output target script newsubapp;</div>
<div>
create qsub subtype <b>U</b> using replqmap ASNUA_TO_ASNUB ( SUBNAME CUSTOMER-UNI DB2INST1.CUSTOMER EXIST TARGET NAME DB2INST1.CUSTOMER); </div>
</div>
<div>
<br /></div>
<div>
Put above lines into a files and run it with asnclp command line to create one subscription</div>
<div>
<br /></div>
<div>
<i>asnclp -f sub_customer</i></div>
<div>
<br /></div>
<div>
Now lets start the capture program and apply program:</div>
<div>
<br /></div>
<div>
<i>nohup asnqcap capture_server=TESTA capture_schema=ASNUA &</i></div>
<div>
<i>nohup asnqapp apply_server=TESTB apply_schema=ASNUB &</i></div>
<div>
<br /></div>
<div>
Connect to source database TESTA and populate one row and check it on target database TESTB</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 connect to testb</i></div>
<div>
<br /></div>
<div>
Database Connection Information</div>
<div>
<br /></div>
<div>
Database server = DB2/LINUXX8664 9.7.9</div>
<div>
SQL authorization ID = DB2INST1</div>
<div>
Local database alias = TESTB</div>
</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "insert into customer values(1,'Murali','bhatiagardens')"</i></div>
<div>
DB20000I The SQL command completed successfully.</div>
</div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "select * from customer"</i></div>
<div>
<br /></div>
<div>
ID CUSTNAME CUSTADD </div>
<div>
---------- -------------------- --------------------</div>
<div>
1 Murali bhatiagardens </div>
<div>
<br /></div>
<div>
1 record(s) selected.</div>
</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 connect to testb</i></div>
<div>
<br /></div>
<div>
Database Connection Information</div>
<div>
<br /></div>
<div>
Database server = DB2/LINUXX8664 9.7.9</div>
<div>
SQL authorization ID = DB2INST1</div>
<div>
Local database alias = TESTB</div>
<div>
<br /></div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "select * from customer"</i></div>
<div>
<br /></div>
<div>
ID CUSTNAME CUSTADD </div>
<div>
---------- -------------------- --------------------</div>
<div>
1 Murali bhatiagardens </div>
<div>
<br /></div>
<div>
1 record(s) selected.</div>
</div>
<div>
<br /></div>
<div>
<b>We shall do a reverse test that is populate a row on target side and observe it don't replicate to source database. :p</b></div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 connect to testb</i></div>
<div>
<br /></div>
<div>
Database Connection Information</div>
<div>
<br /></div>
<div>
Database server = DB2/LINUXX8664 9.7.9</div>
<div>
SQL authorization ID = DB2INST1</div>
<div>
Local database alias = TESTB</div>
</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "insert into customer values(2,'Rupesh','hyderabad')"</i></div>
<div>
DB20000I The SQL command completed successfully.</div>
<div>
<br /></div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "select * from customer"</i></div>
<div>
<br /></div>
<div>
ID CUSTNAME CUSTADD </div>
<div>
---------- -------------------- --------------------</div>
<div>
1 Murali bhatiagardens </div>
<div>
2 Rupesh hyderabad </div>
<div>
<br /></div>
<div>
<b>2 record(s) selected.</b></div>
</div>
<div>
<br /></div>
<div>
<div>
db2inst1@sairam:~/setup$ <i>db2 connect to testa</i></div>
<div>
<br /></div>
<div>
Database Connection Information</div>
<div>
<br /></div>
<div>
Database server = DB2/LINUXX8664 9.7.9</div>
<div>
SQL authorization ID = DB2INST1</div>
<div>
Local database alias = TESTA</div>
<div>
<br /></div>
<div>
db2inst1@sairam:~/setup$ <i>db2 "select * from customer"</i></div>
<div>
<br /></div>
<div>
ID CUSTNAME CUSTADD </div>
<div>
---------- -------------------- --------------------</div>
<div>
1 Murali bhatiagardens </div>
<div>
<br /></div>
<div>
<b>1 record(s) selected.</b></div>
</div>
<div>
<br /></div>
<div>
<b>So that's it for the day and by the end of the post we have clearly defined source and target and created a setup and populated the data to show its working as expected. Readers please post your comments and doubts and any suggestions. I'm Happy to help you anytime. Good BYE</b></div>
<div>
<b><br /></b></div>
<div>
<b><span style="color: #274e13;">Please feel to write to me <i>saimurali619@gmail.com for any queries and help.</i></span></b></div>
<div>
<br /></div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-45262004398497780032014-07-01T18:43:00.001+05:302014-07-01T18:43:08.275+05:30Migration of db2 v9.7 to v10.1 / Upgrade of DB2 v9.7 to v10.1<div dir="ltr" style="text-align: left;" trbidi="on">
Migration of db v9.7 to v10.1<br />
<br />
<b>1) First run db2ckupgrade to find out if we are allowed to migrate the instance to 10.1 level</b><br />
<b>2) After db2ckupgrade is successful, stop the instance and install the db2 v10.1 binaries</b><br />
<b>3) We now upgrade the instance code level to 10.1 using command db2iupgrade</b><br />
<b>4) once instance level has been upgraded we need to upgrade the databases under that instance</b><br />
<br />
<h3 style="text-align: left;">
<b>STEP 1</b></h3>
db2inst1@sairam:~/software/server$ <i>./db2ckupgrade -e -l /home/db2inst1/logfile -u db2inst1 -p db2inst1</i><br />
DBT5508I The db2ckupgrade utility completed successfully. The database or databases can be upgraded.<br />
<br />
db2inst1@sairam:~/software/server$ <i>more ~/logfile </i><br />
Version of DB2CKUPGRADE being run: VERSION "10.1"<br />
<br />
Database: "SAMPLE"<br />
<br />
DBT5537I The db2ckupgrade utility has completed processing for database "SAMPLE".<br />
<br />
DBT5508I The db2ckupgrade utility completed successfully. The database or databases can be upgraded.<br />
<br />
db2inst1@sairam:~/software/server$ <i>db2ls</i><br />
<br />
Install Path Level Fix Pack Special Install Number Install Date Installer UID<br />
---------------------------------------------------------------------------------------------------------------------<br />
/opt/ibm/db2/V9.7 9.7.0.9 9 Tue Jul 1 16:48:02 2014 IST 0<br />
db2inst1@sairam:~/software/server$<br />
db2inst1@sairam:~/software/server$<br />
<br />
db2inst1@sairam:~/software/server$ <i>db2stop</i><br />
01/07/2014 18:03:05 0 0 SQL1064N DB2STOP processing was successful.<br />
SQL1064N DB2STOP processing was successful.<br />
<br />
db2inst1@sairam:~/software/server$ ls -lrt<br />
total 64<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5154 May 15 16:55 db2prereqcheck<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5154 May 15 16:55 db2setup<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5172 May 15 16:55 db2_install<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5136 May 15 16:55 db2ls<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5302 May 15 16:55 db2_deinstall<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5190 May 15 16:56 installFixPack<br />
-r-xr-xr-x 1 db2inst1 db2inst1 5349 May 15 16:56 db2ckupgrade<br />
drwxr-xr-x 28 db2inst1 db2inst1 4096 May 15 16:56 doc<br />
drwxr-xr-x 6 db2inst1 db2inst1 4096 May 15 16:56 db2<br />
<br />
<h3 style="text-align: left;">
<b>STEP 2</b></h3>
db2inst1@sairam:~/software/server$ <i>sudo ./db2_install </i><br />
............................................<br />
......................................<br />
.......................<br />
The execution completed successfully.<br />
<br />
For more information see the DB2 installation log at<br />
"/tmp/db2_install.log.52793".<br />
<br />
db2inst1@sairam:~/software/server$ <i>db2ls</i><br />
<br />
Install Path Level Fix Pack Special Install Number Install Date Installer UID<br />
---------------------------------------------------------------------------------------------------------------------<br />
<span style="background-color: orange;">/opt/ibm/db2/V9.7 9.7.0.9 9 Tue Jul 1 16:48:02 2014 IST 0 </span><br />
<span style="background-color: orange;">/opt/ibm/db2/V10.1 10.1.0.4 4 Tue Jul 1 18:08:00 2014 IST 0 </span><br />
<br />
<h3 style="text-align: left;">
<b>STEP 3</b></h3>
db2inst1@sairam:/opt/ibm/db2/V10.1/instance$ <i>sudo ./db2iupgrade db2inst1</i><br />
DBI1446I The db2iupgrade command is running.<br />
<br />
DB2 installation is being initialized.<br />
<br />
Total number of tasks to be performed: 4<br />
Total estimated time for all tasks to be performed: 309 second(s)<br />
<br />
Task #1 start<br />
Description: Setting default global profile registry variables<br />
Estimated time 1 second(s)<br />
Task #1 end<br />
<br />
Task #2 start<br />
Description: Initializing instance list<br />
Estimated time 5 second(s)<br />
Task #2 end<br />
<br />
Task #3 start<br />
Description: Configuring DB2 instances<br />
Estimated time 300 second(s)<br />
Task #3 end<br />
<br />
Task #4 start<br />
Description: Updating global profile registry<br />
Estimated time 3 second(s)<br />
Task #4 end<br />
<br />
The execution completed successfully.<br />
<br />
For more information see the DB2 installation log at<br />
"/tmp/db2iupgrade.log.23052".<br />
DBI1070I Program db2iupgrade completed successfully.<br />
<br />
db2inst1@sairam:~$ <i>db2level</i><br />
DB21085I This instance or install (instance name, where applicable:<br />
"db2inst1") uses "64" bits and DB2 code release "SQL10014" with level<br />
identifier "0205010E".<br />
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack<br />
"4".<br />
Product is installed at "/opt/ibm/db2/V10.1".<br />
<br />
db2inst1@sairam:~$ <i>db2 list db directory</i><br />
<br />
System Database Directory<br />
<br />
Number of entries in the directory = 1<br />
<br />
Database 1 entry:<br />
<br />
Database alias = SAMPLE<br />
Database name = SAMPLE<br />
Local database directory = /home/db2inst1<br />
Database release level = d.00<br />
Comment =<br />
Directory entry type = Indirect<br />
Catalog database partition number = 0<br />
Alternate server hostname =<br />
Alternate server port number =<br />
<br />
db2inst1@sairam:~$ <i>db2level</i><br />
DB21085I This instance or install (instance name, where applicable:<br />
"db2inst1") uses "64" bits and DB2 code release "SQL10014" with level<br />
identifier "0205010E".<br />
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack<br />
"4".<br />
Product is installed at "/opt/ibm/db2/V10.1".<br />
<br />
db2inst1@sairam:~$ <i>db2start</i><br />
07/01/2014 18:14:31 0 0 SQL1063N DB2START processing was successful.<br />
SQL1063N DB2START processing was successful.<br />
<br />
<span style="color: red;">db2inst1@sairam:~$ db2 connect to sample</span><br />
<span style="color: red;">SQL5035N The database must be upgraded to the current release. </span><br />
<span style="color: red;">SQLSTATE=55001</span><br />
<b><br /></b>
<b>STEP 4</b><br />
<br />
db2inst1@sairam:~$ <i>db2 upgrade db sample</i><br />
<br />
DB20000I The UPGRADE DATABASE command completed successfully.<br />
<br />
db2inst1@sairam:~$ <i>db2 connect to sample</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 10.1.4<br />
SQL authorization ID = DB2INST1<br />
Local database alias = SAMPLE<br />
<br />
db2inst1@sairam:~$ </div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com4tag:blogger.com,1999:blog-3266446665817070990.post-89550032117129511042014-06-30T22:20:00.001+05:302014-06-30T22:22:53.444+05:30Installation methods of DB2 on Linux/Unix<div dir="ltr" style="text-align: left;" trbidi="on">
Installation methods of DB2 on Linux/Unix - There are 4 methods, i will take this in a detailed with practical examples.<br />
<br />
<h3 style="text-align: left;">
1) <b>db2setup</b></h3>
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.<br />
note: On Linux and UNIX platforms, an X server is required to display the DB2 Setup wizard.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>a) uncompress the v9.7fp9_linuxx64_server.tar file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>b) cd server<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>c) sudo ./db2prereqcheck<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5HFQn7Q5Ki8vom4o6sWzSB9h2Hdfdis98itBbM8GRVDFEEXSttu_V-8lM5iTgDY_p-SxjH_IlbX-3mtpYcMuu2nWB4Vi6S_VlakrdehvPrcYkv8HX3unrOG89F0gfe5YWSf-1Jyz9RNY/s1600/1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5HFQn7Q5Ki8vom4o6sWzSB9h2Hdfdis98itBbM8GRVDFEEXSttu_V-8lM5iTgDY_p-SxjH_IlbX-3mtpYcMuu2nWB4Vi6S_VlakrdehvPrcYkv8HX3unrOG89F0gfe5YWSf-1Jyz9RNY/s1600/1.JPG" height="417" width="640" /></a></div>
<br />
<br />
<h3 style="text-align: left;">
<b>2) response file installation</b></h3>
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<br />
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.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>a) uncompress the v9.7fp9_linuxx64_server.tar file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>b) cd server<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>c) sudo ./db2prereqcheck<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgRrvnV71j1uytTRKwFZ2RL3G-Jtqfbe_p4bRY3l5greQ3SSbWnCmbUITiN3u92cxCidcewz8XX3swAvsLPhXnhMQvEXgTAklqkfrVx7O7nRxX6HdgWDa5nEMfPjzwZ-lAeo4X_qWysqk/s1600/response+file+generator.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgRrvnV71j1uytTRKwFZ2RL3G-Jtqfbe_p4bRY3l5greQ3SSbWnCmbUITiN3u92cxCidcewz8XX3swAvsLPhXnhMQvEXgTAklqkfrVx7O7nRxX6HdgWDa5nEMfPjzwZ-lAeo4X_qWysqk/s1600/response+file+generator.JPG" height="465" width="640" /></a></div>
<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>e) sudo ./db2setup -r responsefile<br />
<br />
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<br />
server/db2/platform/samples <span class="Apple-tab-span" style="white-space: pre;"> </span>, where < platform >is the platform that you are installing on<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>a) uncompress the v9.7fp9_linuxx64_server.tar file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>b) cd server/db2/platform/samples ( here platform refers to your OS ) , here you can see sample response file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>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<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>d) sudo ./db2setup -r db2aese.rsp_filename<br />
<br />
<h3 style="text-align: left;">
<b>3) db2_install command</b></h3>
The db2_install command installs all components for the DB2 product, but i doesnt perform user and group creation, instance creation .<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>a) uncompress the v9.7fp9_linuxx64_server.tar file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>b) sudo ./db2prereqcheck<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>c) sudo ./db2_install ( follow the response and complete the installation )<br />
<br />
<h3 style="text-align: left;">
<b>4) payload file deployment</b></h3>
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.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>a) uncompress the v9.7fp9_linuxx64_server.tar file<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>b) cd server/db2/linuxamd64/FILES<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>c) now we need to untar needed components to the location /opt/ibm/db2/V9.7<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>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)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>e) check which level of db2 installed using command db2greg -dump<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>f) create instance and setup profile file ..<br />
<br />
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.<br />
<br />
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.</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-38487710864824466352014-06-27T11:29:00.000+05:302014-06-27T11:31:43.864+05:30Error SQL10013N SQLSTATE=42724 create wrapper for federation support<div dir="ltr" style="text-align: left;" trbidi="on">
When we need to setup federation we need to first create a wrapper which will identify data source of the federated server.<br />
We get this error SQL10013N as shown below, When we don't have Informix data source support installed in our server.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVbhIsVaKAy5YrMyo5wzAP_KLwnf-fvXJ3V2ZUFkMjKuZvFrOFZllqDt0_EJpNZmN6DjTpmmA1L060VbLGtI3RTbv8h86iI24_sLi5ZLDTjhY5j3dBhANxennkDSpEXLmSJqhenMuk_bc/s1600/figure1.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVbhIsVaKAy5YrMyo5wzAP_KLwnf-fvXJ3V2ZUFkMjKuZvFrOFZllqDt0_EJpNZmN6DjTpmmA1L060VbLGtI3RTbv8h86iI24_sLi5ZLDTjhY5j3dBhANxennkDSpEXLmSJqhenMuk_bc/s1600/figure1.gif" height="198" width="400" /></a></div>
<br />
<br />
db2inst1@test-machine:~$ <i>db2 "create wrapper drda"</i><br />
<span style="color: red;">DB21034E The command was processed as an SQL statement because it was not a </span><br />
<span style="color: red;">valid Command Line Processor command. During SQL processing it returned:</span><br />
<span style="color: red;">SQL10013N The specified library </span><span style="color: blue;">"libdb2drda.so"</span><span style="color: red;"> could not be loaded. </span><br />
<span style="color: red;">SQLSTATE=42724</span><br />
<br />
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<br />
<br />
db2inst1@test-machine:~$ <i>db2ls -q -b /opt/ibm/db2/V9.7</i><br />
<br />
Install Path : /opt/ibm/db2/V9.7<br />
<br />
Feature Response File ID Level Fix Pack Feature Description <br />
---------------------------------------------------------------------------------------------------------------------<br />
BASE_CLIENT 9.7.0.6 6 Base client support<br />
JAVA_SUPPORT 9.7.0.6 6 Java support<br />
SQL_PROCEDURES 9.7.0.6 6 SQL procedures<br />
BASE_DB2_ENGINE 9.7.0.6 6 Base server support<br />
JDK 9.7.0.6 6 IBM Software Development Kit (SDK) for Java(TM)<br />
COMMUNICATION_SUPPORT_TCPIP 9.7.0.6 6 Communication support - TCP/IP<br />
REPL_CLIENT 9.7.0.6 6 Replication tools<br />
DB2_DATA_SOURCE_SUPPORT 9.7.0.6 6 DB2 data source support<br />
LDAP_EXPLOITATION 9.7.0.6 6 DB2 LDAP support<br />
INSTANCE_SETUP_SUPPORT 9.7.0.6 6 DB2 Instance Setup wizard<br />
SPATIAL_EXTENDER_CLIENT_SUPPORT 9.7.0.6 6 Spatial Extender client<br />
APPLICATION_DEVELOPMENT_TOOLS 9.7.0.6 6 Base application development tools<br />
<br />
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<br />
<br />
PROD = ADVANCED_ENTERPRISE_SERVER_EDITION<br />
FILE = /opt/ibm/db2/V9.7_01<br />
LIC_AGREEMENT = ACCEPT<br />
INSTALL_TYPE = CUSTOM <br />
COMP = INFORMIX_DATA_SOURCE_SUPPORT<br />
<br />
I will save above 5 lines in /home/db2inst1/resp<br />
<br />
Now i will install the single component informix_data_source_support using below command<br />
<br />
db2inst1@test-machine:~/server$ <i>sudo ./db2setup -r /home/db2inst1/resp </i><br />
WARNING:<br />
DBT3534W The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.<br />
WARNING:<br />
The 32-bit library file libpam.so is not found on the system.<br />
DBI1191I db2setup is installing and configuring DB2 according to the<br />
response file provided. Please wait.<br />
<br />
A minor error occurred while installing "DB2 Advanced Enterprise Server<br />
Edition " on this computer. Some features may not function correctly.<br />
For more information see the DB2 installation log at "/tmp/db2setup.log".<br />
<br />
Once done i have connected to database and was able to create wrapper successfully<br />
<br />
db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ <i>db2 connect to attempt</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.6<br />
SQL authorization ID = DB2INST1<br />
Local database alias = ATTEMPT<br />
<br />
db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ <i>db2 create wrapper drda</i><br />
DB20000I The SQL command completed successfully.<br />
<div>
<br /></div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com1tag:blogger.com,1999:blog-3266446665817070990.post-70596204427480736132014-06-08T15:39:00.007+05:302014-06-08T15:39:55.494+05:30Changing the data type of a column in replicated table in DB2 Q Replication<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: inherit;">db2inst1@sairam:~$ <i>db2 describe table task</i></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"> Data type Column</span><br />
<span style="font-family: inherit;">Column name schema Data type name Length Scale Nulls</span><br />
<span style="font-family: inherit;">------------------------------- --------- ------------------- ---------- ----- ------</span><br />
<span style="font-family: inherit;">TASK_ID SYSIBM INTEGER 4 0 No </span><br />
<span style="font-family: inherit;">TASK_INFO SYSIBM VARCHAR 10 0 Yes </span><br />
<span style="font-family: inherit;">SALARY SYSIBM VARCHAR 10 0 Yes </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"> 3 record(s) selected.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">We want to change SALARY column datatype from varchar(10) to char(20) </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Steps to change the data type:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">1) Stop the subscription by passing capstop signal</span><br />
<span style="font-family: inherit;">2) stop the data capture changes to none on both databases</span><br />
<span style="font-family: inherit;">3) alter the column with the new data type on both sides</span><br />
<span style="font-family: inherit;">4) start the data capture changes to yes on both databaes</span><br />
<span style="font-family: inherit;">5) Start the subscription by passing capstart signal</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">1) db2inst1@sairam:~$ <i>db2 connect to testb</i></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"> Database Connection Information</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"> Database server = DB2/LINUXX8664 9.7.9</span><br />
<span style="font-family: inherit;"> SQL authorization ID = DB2INST1</span><br />
<span style="font-family: inherit;"> Local database alias = TESTB</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">db2inst1@sairam:~$ <i>db2 "insert into ASNTB.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTOP','TASK0002')"</i></span><br />
<span style="font-family: inherit;">DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">2) db2inst1@sairam:~$ <i>db2 alter table task data capture none</i> - ( on both databases )</span><br />
<span style="font-family: inherit;">DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">3) db2inst1@sairam:~$ <i>db2 "alter table task alter column task_info set data type char(20)"</i> - ( on both databases )</span><br />
<span style="font-family: inherit;">DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">4) db2inst1@sairam:~$ <i>db2 alter table task data capture changes</i></span><br />
<span style="font-family: inherit;">DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">5) db2inst1@sairam:~$ <i>db2 "insert into ASNTA.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTART','TASK0001')"</i> - ( on one side )</span><br />
<span style="font-family: inherit;">DB20000I The SQL command completed successfully.</span></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-34482473814993105432014-06-08T14:42:00.000+05:302014-06-08T14:42:37.755+05:30Resolving conflicts due to particular sqlstate in DB2 Q Replication<div dir="ltr" style="text-align: left;" trbidi="on">
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 . <br />
<br />
<i>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 "<b style="background-color: orange;">57016</b>". The SQLERRMC is "". The SQLERRP is "SQLDTBLR".</i><br />
<i>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".</i><br />
<br />
Below are steps the pass to ibmqrep_targets to ignore that transaction for particular sql state.<br />
<br />
db2inst1@sairam:~$ db2 connect to testa<br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTA<br />
<br />
db2inst1@sairam:~$ db2 "update asnta.ibmqrep_targets set oksqlstates=<span style="background-color: orange;">'57016'</span> where subname='STAFF0002'"<br />
DB20000I The SQL command completed successfully.<br />
<br />
Then it had successfully ignored that transaction alone and q apply started processing the Receive queue.</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-56500152726786377102014-06-08T13:26:00.003+05:302014-06-08T13:28:35.161+05:30Resetting Q subscriptions that are in inconsistent states in DB2 Q Replication<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: inherit;"><span style="background-color: white;">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. </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">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 </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">db2inst1@sairam:~$ db2 connect to testa</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> Database Connection Information</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> Database server = DB2/LINUXX8664 9.7.9</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"> SQL authorization ID = DB2INST1</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"> Local database alias = TESTA</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">SUBNAME STATE</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">------------------------------------------------------------------------------------------------------------------------------------ -----</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">DBA0001 A </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">STAFF0001 G </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> 2 record(s) selected.</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">db2inst1@sairam:~$ db2 connect to testb</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> Database Connection Information</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> Database server = DB2/LINUXX8664 9.7.9</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"> SQL authorization ID = DB2INST1</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"> Local database alias = TESTB</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">SUBNAME STATE</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">------------------------------------------------------------------------------------------------------------------------------------ -----</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">DBA0002 A </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">STAFF0002 I </span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;"> 2 record(s) selected.</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white;"><i>Steps to reset the incosistent states in Q Replication</i></span></span><br />
<i><span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">1) <span class="Apple-tab-span" style="white-space: pre;"> </span>Stop capture and apply processes this is important before you update the state of subs and targets</span></span></i><br />
<i><span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">2)<span class="Apple-tab-span" style="white-space: pre;"> </span>UPDATE ASNTA.IBMQREP_SUBS SET STATE='I', STATE_TRANSITION=NULL WHERE SUBNAME='STAFF0001';</span></span></i><br />
<span style="font-family: inherit;"><span style="background-color: white;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>UPDATE ASNTA.IBMQREP_TARGETS SET STATE='I' WHERE SUBNAME='STAFF0002';</i></span></span><br />
<i><span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">3)<span class="Apple-tab-span" style="white-space: pre;"> </span>Start the capture and apply process now. We have state as INACTIVE in both sides so lets activate the subscription in the next step</span></span></i><br />
<i><span style="font-family: inherit;"><span style="background-color: white;"><br /></span></span>
<span style="font-family: inherit;"><span style="background-color: white;">4) <span class="Apple-tab-span" style="white-space: pre;"> </span>INSERT INTO ASNTA.IBMQREP_SIGNAL (SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN) VALUES ('CMD','CAPSTART','STAFF0001');</span></span></i><br />
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
<br />
db2inst1@sairam:~$ db2 connect to testb<br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTB<br />
<br />
db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"<br />
<br />
SUBNAME STATE<br />
------------------------------------------------------------------------------------------------------------------------------------ -----<br />
DBA0002 A <br />
STAFF0002 A <br />
<br />
2 record(s) selected.<br />
<br />
db2inst1@sairam:~$ db2 connect to testa<br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTA<br />
<br />
db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"<br />
<br />
SUBNAME STATE<br />
------------------------------------------------------------------------------------------------------------------------------------ -----<br />
DBA0001 A <br />
STAFF0001 A <br />
<br />
2 record(s) selected.<br />
<div>
<br /></div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-15870993969000215372014-05-17T17:59:00.001+05:302014-05-17T23:54:17.916+05:30Using ASNTDIFF and ASNTREP utilities to sync the data between source and target tables in DB2 Q replication<div dir="ltr" style="text-align: left;" trbidi="on">
When you observe that there are some data differences between source and target tables in DB2 Q replication, we can use these 2 utilities( asntdiff, asntrep ) which can compare the source and target and repair them based on our requirement to synchronize the data.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcVh_4jvgTnCUhQmPRareBwTODgJ8FaGytYEWq94-Y5XiKwBMU06BdCt5N9oTDKtiVyNYEDZ8O2gP6xWSlQ8x43tSZ7fBvFZC-k8_wrUCzu_rTUNTsbyEsa9DPACRFEGdLm19ZZnWUt7o/s1600/asntdiffFileDiffTable.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcVh_4jvgTnCUhQmPRareBwTODgJ8FaGytYEWq94-Y5XiKwBMU06BdCt5N9oTDKtiVyNYEDZ8O2gP6xWSlQ8x43tSZ7fBvFZC-k8_wrUCzu_rTUNTsbyEsa9DPACRFEGdLm19ZZnWUt7o/s1600/asntdiffFileDiffTable.jpg" height="120" width="320" /></a></div>
<br />
<br />
<b>ASNTDIFF : asntdiff utility which can compare two relational tables and generate a list of differences between the two.</b><br />
<b><br /></b>
<b>ASNTREP : asntrep utility will syncronize a source and target table by repairing differences between the two tables.</b><br />
<br />
Important note : Its always good to synchronize the tables when there is less amount of activity or no activity going at source so that we can have perfectly synchronized table. <br />
<br />
Below tutorial demonstrates the use of asntdiff to compare the source and target and asntrep to repair the target table based on differences generated by asntdiff. Below is the data populated on source and target without replication enabled. Then i have created the subscription for the table AUCTION with target having no load option so that it wont try to reload the data using source as we are in process syncing the tables<br />
<br />
1) In this first step i have created a table with same definition in source and target and loaded data with some differences in both of them<br />
<br />
db2inst1@murali:~$ <i>db2 connect to testa</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTA<br />
<br />
db2inst1@murali:~$ <i>db2 "select * from auction"</i><br />
<br />
ITEMID CATEGORY PRICE <br />
---------- ---------- -----------<br />
<span style="color: blue;">A0001 HOME 54</span><br />
<span style="color: blue;">A0002 GENERAL 17</span><br />
<span style="color: blue;">A0003 SPARES 120</span><br />
<span style="color: blue;">A0004 GENERAL 32</span><br />
<span style="color: blue;">A0006 GENERAL 40</span><br />
<br />
<span style="color: blue;">5 record(s) selected.</span><br />
<br />
db2inst1@murali:~$ <i>db2 connect to testb</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTB<br />
<br />
db2inst1@murali:~$ <i>db2 "select * from auction"</i><br />
<br />
ITEMID CATEGORY PRICE <br />
---------- ---------- -----------<br />
<span style="color: red;">A0001 HOME 15</span><br />
<span style="color: red;">A0002 HOME 12</span><br />
<span style="color: red;">A0003 HOME 35</span><br />
<span style="color: red;">A0006 HOME 50</span><br />
<br />
<span style="color: red;">4 record(s) selected.</span><br />
<br />
2) I'm here considering TESTA as source database/correct data and TESTB is having wrong data which i want to synchronize. Let us run the asntdiff utility to find the differences between the source and target tables. As showing below in the ending line we can see that it has given us the number of differences found between the source and target table: <span style="color: blue;">"5"</span>. These details can be found in database "TESTA", and it has stored the diff values in the difference table DIFFSCHEMA1.DIFF_AUCTION<br />
<br />
db2inst1@murali:~$ <i>asntdiff DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION where="SUBNAME = 'AUCTION0001'"</i><br />
<i><br /></i>
2014-05-17-17.12.21.904722 ASN0600I "AsnTDiff" : "" : "Initial" : Program "asntdiff 9.7.9" is starting.<br />
2014-05-17-17.12.22.005751 ASN4012I "AsnTDiff" : "ASNTA" : "Initial" : The program is comparing tables using the list of parameters following this message.<br />
TDIFF TABLE = "DIFFSCHEMA1"."DIFF_AUCTION" ;<br />
MAXIMUM COUNT = 100000 ;<br />
<br />
CONNECT TO TESTA ;<br />
<br />
SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;<br />
<br />
CONNECT TO TESTB ;<br />
<br />
SELECT "CATEGORY", "PRICE", "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;<br />
<br />
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION" (<br />
"DIFF " CHAR(4),<br />
"ITEMID" VARCHAR(10)<br />
) ;<br />
<br />
2014-05-17-17.12.22.416439 ASN4006I "AsnTDiff" : "ASNTA" : "Initial" : Between the source table and the target table, there are "0" common rows, "5" rows that are unique to the source table, and "4" rows that are unique to the target table.<br />
2014-05-17-17.12.22.438578 <span style="color: blue;">ASN4010I "AsnTDiff" : "ASNTA" : "Initial" : Number of differences found between the source and target table: "5". The details can be found in database "TESTA", difference table ""DIFFSCHEMA1"."DIFF_AUCTION"".</span><br />
----------------------------------------------------------------------<br />
<br />
3) Let us see what differences it has identified between source and target and have stored in the difference table. As show below we can see it contains <b>4 UPDATES and 1 INSERT</b><br />
<br />
db2inst1@murali:~$ <i>db2 "select * from "DIFFSCHEMA1"."DIFF_AUCTION""</i><br />
<br />
DIFF ITEMID <br />
----- ----------<br />
<b><span style="color: blue;">U</span></b> 2 A0001 <br />
<b><span style="color: blue;">U</span></b> 2 A0002 <br />
<b><span style="color: blue;">U</span></b> 2 A0003 <br />
<b><span style="color: blue;">U</span></b> 2 A0006 <br />
<b><span style="color: blue;">I </span></b> 2 A0004 <br />
<br />
5 record(s) selected.<br />
<br />
4) Now to repair the table at target we are running asntrep using the same difference table, that was created in earlier step. So it should perform 4 updates and 1 insert in target side. As shown in the ending line we can see The program applied the rows from the difference table to the target table as follows: <span style="color: blue;">"1" rows were inserted, "4" rows were updated, and "0" rows were deleted.</span><br />
<br />
db2inst1@murali:~$ <i>asntrep DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION WHERE="SUBNAME = 'AUCTION0001'"</i><br />
<br />
2014-05-17-17.13.49.984558 ASN0600I "AsnTRep" : "" : "Initial" : Program "asntrep 9.7.9" is starting.<br />
<br />
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION-" (<br />
"DIFF " CHAR(4),<br />
"ITEMID" VARCHAR(10)<br />
) ;<br />
<br />
DELETE<br />
FROM "DB2INST1"."AUCTION" T<br />
WHERE EXISTS (<br />
<br />
SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION-" D<br />
<br />
WHERE ( T."ITEMID" = D."ITEMID" OR<br />
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )<br />
AND D."DIFF " LIKE 'D%' ) ;<br />
<br />
<br />
<br />
INSERT INTO "DB2INST1"."AUCTION" (<br />
"CATEGORY",<br />
"PRICE",<br />
"ITEMID" ) VALUES( ?, ?, ? ) ;<br />
<br />
<br />
SELECT T.*<br />
FROM ( SELECT "CATEGORY" AS "CATEGORY",<br />
"PRICE" AS "PRICE",<br />
"ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"<br />
) AS T<br />
<br />
WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D<br />
WHERE ( T."ITEMID" = D."ITEMID" OR<br />
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )<br />
AND D."DIFF " LIKE 'I%' ) FOR READ ONLY ;<br />
<br />
UPDATE "DB2INST1"."AUCTION"<br />
SET<br />
"CATEGORY" = ?,<br />
"PRICE" = ?<br />
WHERE<br />
"ITEMID" = ?<br />
<br />
SELECT T.* FROM ( SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ) AS T WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D WHERE ( T."ITEMID" = D."ITEMID" OR T."ITEMID" IS NULL AND D."ITEMID" IS NULL ) AND D."DIFF " LIKE 'U%' ) FOR READ ONLY ;<br />
<br />
2014-05-17-17.13.50.321421 <span style="color: blue;">ASN4019I "AsnTRep" : "ASNTA" : "Initial" : The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.</span><br />
----------------------------------------------------------------------<br />
<br />
5) Now lets check the data in target to see if it really worked.<br />
<br />
db2inst1@murali:~$ <i>db2 connect to testb</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTB<br />
<br />
db2inst1@murali:~$ <i>db2 "select * from auction"</i><br />
<br />
ITEMID CATEGORY PRICE <br />
---------- ---------- -----------<br />
<span style="color: blue;">A0001 HOME 54</span><br />
<span style="color: blue;">A0002 GENERAL 17</span><br />
<span style="color: blue;">A0003 SPARES 120</span><br />
<span style="color: blue;">A0006 GENERAL 40</span><br />
<span style="color: blue;">A0004 GENERAL 32</span><br />
<br />
<span style="color: blue;">5 record(s) selected.</span><br />
<br />
<b>NOTE:</b><br />
<br />
<b>From my experience when we need to synchronize big tables we need to specify the conditions on which asntdiff and asntrep should run, else it might take a lot of time to find the differences between source and target, also sometimes it also fails. Its always important to understand the business logic and use these utilities with utmost care to make sure we moving in right direction.</b></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-74981740397832669842014-05-17T16:30:00.003+05:302014-05-17T23:49:52.126+05:30Adding a new column to replicated table in DB2 Q Replication<div dir="ltr" style="text-align: left;" trbidi="on">
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)<br />
<br />
db2inst1@murali:~$ <i>db2 describe table dba</i><br />
<br />
Data type Column<br />
Column name schema Data type name Length Scale Nulls<br />
------------------------------- --------- ------------------- ---------- ----- ------<br />
NAME SYSIBM VARCHAR 10 0 Yes <br />
ID SYSIBM INTEGER 4 0 No <br />
<br />
2 record(s) selected.<br />
<br />
<br />
<b>Basic steps are like this</b><br />
<br />
<b>1) Check the subscription of the table on source and target and see if it active before we add a new column</b><br />
<b>2) Add the column with normal alter table add column command to the source table</b><br />
<b>3) Pass the signal to replication so it can understand that we have added a new column to table</b><br />
<b>4) Check on the target side if the column is added or not</b><br />
<br />
1)<br />
<br />
db2inst1@murali:~$ <i>db2 connect to testa</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTA<br />
<br />
db2inst1@murali:~$ <i>db2 "select subname,state from asnta.ibmqrep_subs where source_name='DBA'"</i><br />
<br />
SUBNAME STATE<br />
------------------------------------------------------------------------------------------------------------------------------------ -----<br />
DBA0001 A <br />
<br />
1 record(s) selected.<br />
<br />
db2inst1@murali:~$ <i>db2 connect to testb</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTB<br />
<br />
db2inst1@murali:~$ <i>db2 "select subname,state from asntb.ibmqrep_subs where source_name='DBA'"</i><br />
<br />
SUBNAME STATE<br />
------------------------------------------------------------------------------------------------------------------------------------ -----<br />
DBA0002 A <br />
<br />
1 record(s) selected.<br />
<br />
<br />
2)<br />
<br />
db2inst1@murali:~$ <i>db2 "alter table dba add column address varchar(10)"</i><br />
DB20000I The SQL command completed successfully.<br />
<br />
3)<br />
<br />
db2inst1@murali:~$ <i>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' )"</i><br />
DB20000I The SQL command completed successfully.<br />
<br />
4)<br />
<br />
db2inst1@murali:~$ <i>db2 connect to testb</i><br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = TESTB<br />
<br />
db2inst1@murali:~$ <i>db2 describe table dba</i><br />
<br />
Data type Column<br />
Column name schema Data type name Length Scale Nulls<br />
------------------------------- --------- ------------------- ---------- ----- ------<br />
NAME SYSIBM VARCHAR 10 0 Yes <br />
ID SYSIBM INTEGER 4 0 No <br />
ADDRESS SYSIBM VARCHAR 10 0 Yes <br />
<br />
3 record(s) selected.<br />
<br />
All the commands used in this post are highlighted in <i>ITALIC</i></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-24425886225118155822014-05-14T22:00:00.003+05:302014-05-14T22:00:56.132+05:30Learning DB2 Basics<div dir="ltr" style="text-align: left;" trbidi="on">
Its always good to learn basics so let me share you some best books and tutorials.<div>
<br /></div>
<div>
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 ..</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://ecx.images-amazon.com/images/I/515srmkGBSL._BO2,204,203,200_PIsitb-sticker-arrow-click,TopRight,35,-76_AA300_SH20_OU01_.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://ecx.images-amazon.com/images/I/515srmkGBSL._BO2,204,203,200_PIsitb-sticker-arrow-click,TopRight,35,-76_AA300_SH20_OU01_.jpg" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>Link to download :</b> <a href="https://www.mediafire.com/?cuy2zr07roj3et1">https://www.mediafire.com/?cuy2zr07roj3et1</a> ( please don't share any alternative links as i want to see the original downloads count )</div>
<div>
<br /></div>
<div>
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.</div>
<div>
<br /></div>
<div>
<div>
1) DB2 Essential Training I (DB101EN)</div>
<div>
2) DB2 Essential Training II (DB102EN)</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://data-informed.com/wp-content/uploads/2012/10/Big-Data-University-logo.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://data-informed.com/wp-content/uploads/2012/10/Big-Data-University-logo.jpg" height="191" width="320" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>Link for video tutorials : </b><a href="http://bigdatauniversity.com/courses/course/view.php?id=81&justenroled=1#">http://bigdatauniversity.com/courses/course/view.php?id=81&justenroled=1#</a></div>
<div>
<br /></div>
<div>
<br /></div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-31297563753538322472014-05-13T12:11:00.003+05:302014-05-13T12:27:20.131+05:30Automatically reroute your client requests in DB2 HADR in the event of failure<div dir="ltr" style="text-align: left;" trbidi="on">
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.<br />
<br />
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 <a href="http://db2luw2.blogspot.in/2014/05/8-steps-to-setup-db2-hadr.html" target="_blank">how to setup DB2 HADR</a>, so refer to that and setup in your environment.<br />
<br />
<br />
Basic steps are like these.<br />
<br />
1) Setup HADR pair and see that they are in peer mode.<br />
2) UPDATE ALTERNATIVE SERVER configuration on the primary and standby database.<br />
3) Catalog the primary database at the standby node/instance<br />
4) takeover the hadr on standby database<br />
5) run the query to test it and see if we are connected to standby from client<br />
<br />
<br />
1) Setup HADR and check if HADR pair is in peer state or not<br />
<br />
<i>db2pd -db char -hadr</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3ukgWyh__-XX-OoDPYjS2O_faGppuDTn-LeJbfXMDfeBkN_6IxRxU5uOwt2j0XGhbniQVkUSK013RXWMwpL5SGOcoOmx5b86LTRteZU5MTVPuQwG0OmpaJUvTioqggVKPG14VGc5ROJ0/s1600/peerstate.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3ukgWyh__-XX-OoDPYjS2O_faGppuDTn-LeJbfXMDfeBkN_6IxRxU5uOwt2j0XGhbniQVkUSK013RXWMwpL5SGOcoOmx5b86LTRteZU5MTVPuQwG0OmpaJUvTioqggVKPG14VGc5ROJ0/s1600/peerstate.JPG" height="254" width="640" /></a></div>
<br />
2)<br />
on primary:<br />
<i>db2 update alternate server for database char using hostname sairam port 65000</i><br />
<br />
on standby:<br />
<i>db2 update alternate server for database char using hostname sairam port 64000</i><br />
<br />
3)<br />
on standby:<br />
<br />
<i>db2 catalog tcpip node primeno remote sairam server 64000</i><br />
<i>db2 catalog database char as apple at node primeno</i><br />
<br />
from standby instance connect to primary database which is cataloged as local database<br />
<br />
db2inst2@sairam:~$ <i>db2 connect to apple user db2inst1</i><br />
Enter current password for db2inst1:<br />
<br />
Database Connection Information<br />
<br />
Database server = DB2/LINUXX8664 9.7.9<br />
SQL authorization ID = DB2INST1<br />
Local database alias = APPLE<br />
<br />
db2inst2@sairam:~$ <i>db2 "select * from tab1"</i><br />
<br />
COL1 <br />
-----------<br />
1<br />
2<br />
3<br />
<br />
3 record(s) selected.<br />
<br />
Note : Dont close this session as we need to check the same query from same session as described in step 5<br />
<br />
4)<br />
<br />
on standy: use a different session to give this command<br />
<br />
Issue a takeover on the standby database to switch the HADR role<br />
<br />
db2inst2@sairam:~$ <i>db2 takeover hadr on database char</i><br />
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.<br />
<br />
5)<br />
<br />
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.<br />
<br />
db2inst2@sairam:~$ <i>db2 "select * from tab1"</i><br />
<span style="color: red;">SQL30108N A connection failed but has been re-established. Special register</span><br />
<span style="color: red;">settings might have been replayed. Host name or IP address of the new</span><br />
<span style="color: red;">connection: "sairam". Service name or port number of the new connection:</span><br />
<span style="color: red;">"64000". Reason code: "1". SQLSTATE=08506</span><br />
<br />
db2inst2@sairam:~$ <i>db2 "select * from tab1"</i><br />
<br />
COL1 <br />
-----------<br />
<span style="color: orange;">SQL1773N The statement or command requires functionality that is not</span><br />
<span style="color: orange;">supported on a read-enabled HADR standby database. Reason code = "1".</span><br />
<br />
db2inst2@sairam:~$ <i>db2 "select * from tab1 with ur"</i><br />
<br />
COL1 <br />
-----------<br />
1<br />
2<br />
3<br />
<br />
3 record(s) selected.<br />
<br />
All commands that i used in this post are highlighted in <i>ITALIC </i></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-27120832852106426802014-05-09T19:38:00.002+05:302014-05-09T19:39:03.697+05:308 steps to setup DB2 HADR solution<div dir="ltr" style="text-align: left;" trbidi="on">
High Availability disaster Recovery solution HADR in DB2 is one of robust ways of maintaining two db2 databases in SYNC for high availability solutions.<br />
<br />
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.<br />
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.<br />
<br />
<u>Basic step are like these.</u><br />
create a database on one machine/instance<br />
set HADR DB parameters on primary database<br />
backup database on primary<br />
restore the image in standby machine/instance<br />
set HADR DB parameters on standby database<br />
start hadr on standby first then on primary database<br />
Validate the HADR setup<br />
<br />
Here is how my setup look like<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA0OnIY3UDTdZpOg-6S8sGvXpe6jqg4I_SV2jm-bB8-8wrnRTSOX9kCzIW8ZznKt-jlcq4pbjHw3Ew1KtuFxgzSPRLSWFOm1wAB702akpv57gUiZ_HRtCWHma_QVJivyREwJEDPaU-KWE/s1600/hadr.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA0OnIY3UDTdZpOg-6S8sGvXpe6jqg4I_SV2jm-bB8-8wrnRTSOX9kCzIW8ZznKt-jlcq4pbjHw3Ew1KtuFxgzSPRLSWFOm1wAB702akpv57gUiZ_HRtCWHma_QVJivyREwJEDPaU-KWE/s1600/hadr.JPG" height="272" width="640" /></a></div>
<br />
<br />
1) db2 create database samsung<br />
<br />
2) db2 update db cfg for samsung using logarchmeth1 DISK:/home/db2inst1/logs<br />
<br />
3) Setting up HADR cfg parameters on primary database<br />
<br />
update db cfg for samsung using HADR_LOCAL_HOST sairam<br />
update db cfg for samsung using HADR_LOCAL_SVC 64000<br />
update db cfg for samsung using HADR_REMOTE_HOST sairam<br />
update db cfg for samsung using HADR_REMOTE_SVC 65000<br />
update db cfg for samsung using HADR_REMOTE_INST db2inst2<br />
update db cfg for samsung using LOGINDEXBUILD ON<br />
<br />
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<br />
<br />
5) restore the database on standby instance<br />
<br />
db2 restore db samsung taken at 20140509113342 on /home/db2inst2/ into apple<br />
<br />
6) Setting up HADR cfg parameters on standby database<br />
<br />
update db cfg for apple using HADR_LOCAL_HOST sairam<br />
update db cfg for apple using HADR_LOCAL_SVC 65000<br />
update db cfg for apple using HADR_REMOTE_HOST sairam<br />
update db cfg for apple using HADR_REMOTE_SVC 64000<br />
update db cfg for apple using HADR_REMOTE_INST db2inst1<br />
<br />
7) Starting up HADR on the standby server<br />
<br />
db2 start hadr on database apple as standby<br />
<br />
8) Starting up HADR on the primary server<br />
<br />
db2 start hadr on database samsung as primary<br />
<br />
Finally validating the HADR setup is important.<br />
<br />
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.</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com3tag:blogger.com,1999:blog-3266446665817070990.post-78324109840348110402014-04-28T12:00:00.001+05:302014-04-28T12:34:08.061+05:30Installing DB2 fixpack<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
<span style="font-weight: normal;">Install DB2 Fix packs – Below steps are intended for Linux, Unix operating systems.</span></h3>
<br />
1)<span class="Apple-tab-span" style="white-space: pre;"> </span>Perform the necessary tasks before installing a fix pack.<br />
2)<span class="Apple-tab-span" style="white-space: pre;"> </span>Choose a fix pack installation method and install the fix pack.<br />
3)<span class="Apple-tab-span" style="white-space: pre;"> </span>Perform the necessary tasks after installing the fix pack.<br />
4)<span class="Apple-tab-span" style="white-space: pre;"> </span>Apply the appropriate DB2 database product license.<br />
<br />
1)<span class="Apple-tab-span" style="white-space: pre;"> </span>Perform the necessary tasks before installing a fix pack<br />
<br />
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.<br />
<br />
To determine the space used by the existing DB2 database products, perform the command:<br />
<br />
<i>du -h -s DB2DIR</i><br />
<br />
where DB2DIR represents the location where the DB2 copy is installed.<br />
<br />
b) Download the fix pack<br />
<br />
c) Uncompress the fixpacks, use the below command<br />
<br />
<i>gunzip -c filename.tar.gz | tar -xvf - </i><br />
<br />
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.<br />
Determine which instances are associated with the DB2 copy.<br />
<br />
Issue the command: <i>DB2DIR/instance/db2ilist</i><br />
<i><br /></i>
where DB2DIR represents the location where the DB2 copy is installed.<br />
<br />
<i>su - iname</i><br />
<i>db2 force application all</i><br />
<i>db2 terminate</i><br />
<i>db2stop</i><br />
<i>exit</i><br />
<br />
If the DB2 Administration Server (DAS) belongs to the DB2 copy that you are updating, stop the DAS:<br />
<i>su - aname</i><br />
<i>db2admin stop</i><br />
<i>exit</i><br />
<br />
where aname represents the DAS owner name.<br />
<br />
e) Optional: On AIX, run slibclean to unload unused shared libraries from memory before installation:<br />
<br />
<i>/usr/sbin/slibclean</i><br />
<br />
f) Disable the fault monitor processes. To stop the Fault Monitor Daemon, issue the command:<br />
<br />
<i>DB2DIR/bin/db2fm -i iname -D</i><br />
<i><br /></i>
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.<br />
<br />
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:<br />
<br />
<i>$HOME/sqllib/bin/ipclean</i><br />
<br />
2)<span class="Apple-tab-span" style="white-space: pre;"> </span>Choose a fix pack installation method and install the fix pack.<br />
<br />
There are 2 ways that you use the fixpack and installation methods that match the purpose.<br />
<br />
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.<br />
<br />
Change to the directory that contains the fix pack image.<br />
<br />
Launch the installation by issuing the command: <i>./db2setup</i><br />
<br />
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.<br />
<br />
You have two choices when it comes to fix pack installation; using a universal fix pack (all products) or a product-specific fix pack.<br />
<br />
Why would I use the universal fix pack?<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If I want to upgrade multiple products in place.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>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.<br />
<br />
Why would I use the product-specific fix pack?<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If I have a single product and want to save the time to download the fix pack.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If system downtime is costly.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If I need to test the new fix pack without impacting my production databases.<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If I need the ability to go back to the previous version quickly (system downtime in case of failure).<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>If I already have a fix pack installed and need to add new features or products to the fix pack.<br />
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.<br />
<br />
Launch the installation by issuing the installFixPack command. For example,<br />
<br />
<i>./installFixPack -b DB2DIR</i><br />
<br />
where DB2DIR is the location of the DB2 database products that you want to update.<br />
<br />
Note: I had seen below error in Linux environment.<br />
<br />
Stopping DB2 Fault Monitor :.......Success<br />
ERROR: The installFixPack command detected some DB2 libraries are still loaded<br />
in memory and some applications might still be running in the current<br />
installation copy. All applications must be stopped. See the fix pack readme<br />
for pre-installation instructions, and re-run the installFixPack command.<br />
Alternatively, to override automatic checking, you can re-issue the<br />
installFixPack command with the '-f db2lib' parameter. Note: If you re-issue<br />
the installFixPack command with the '-f db2lib' parameter, after the DB2<br />
instances are updated, some applications might not work properly and might need<br />
to be restarted to function properly against the updated DB2 instance.<br />
<br />
Preparing the system :.......Failure<br />
Change to the directory that contains the fix pack image.<br />
<br />
If you get error as above use the below command, and complete the install.<br />
<br />
<i>./installFixPack -b DB2DIR –f db2lib</i><br />
<br />
3)<span class="Apple-tab-span" style="white-space: pre;"> </span>Perform the necessary tasks after installing the fix pack.<br />
<br />
a) For each instance, issue the command:<br />
<br />
<i>DB2DIR/instance/db2iupdt iname</i><br />
<br />
where iname represents the instance name and DB2DIR represents the location where the DB2 copy is installed.<br />
<br />
b) If the DB2 Administration Server (DAS) belongs to the DB2 copy where you installed the fix pack, issue the command:<br />
<br />
<i>DB2DIR/instance/dasupdt</i><br />
<br />
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.<br />
<br />
c) Update the system catalog objects in your databases to support the fix pack.<br />
<br />
<i>db2updv97 -d dbname</i><br />
where dbname represents the name of the database.<br />
<br />
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.<br />
<br />
d) Restart the instance , DAS admin<br />
<br />
<i>su - iname</i><br />
<i>db2start</i><br />
<br />
<i>su - dasuser</i><br />
<i>db2admin start</i><br />
<br />
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.<br />
<br />
If you installed the fix pack on DB2 database products that have existing databases, perform the following commands once for each database:<br />
<br />
<i>db2 terminate</i><br />
<i>db2 CONNECT TO dbname user USERID using PASSWORD</i><br />
<i>db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE </i><br />
<i>db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD </i><br />
<i>db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD </i><br />
<i>db2 terminate</i><br />
<br />
If you installed the fix pack on DB2 database products that have existing databases, rebind the packages by running the REBIND or db2rbind command.<br />
<br />
<i>db2rbind dbname -l fullpath/logfile all </i><br />
fullpath refers to a valid path given with a name to log the rebind output<br />
<br />
4)<span class="Apple-tab-span" style="white-space: pre;"> </span>Apply the appropriate DB2 database product license.<br />
<br />
<i>INSTHOME/sqllib/adm/db2licm -a filename</i><br />
<br />
Where INSTHOME represents the home directory of the instance owner and filename is the full path name and file name for the license file<br />
Check the license information once after applying the license.<br />
<i><br /></i>
<i>db2licm -l</i><br />
<i><br /></i>
<i>All the commands used in the post are highlighted as Italic</i></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-4420805129940851812014-04-17T14:37:00.004+05:302014-04-17T14:44:18.436+05:30Q capture program stops giving Websphere MQ error 2058 ASN0575E<div dir="ltr" style="text-align: left;" trbidi="on">
Problem statement: Websphere MQ error 2058 ASN0575E while issuing the WebSphere MQ command "MQCONN" on object "*"<br />
<br />
Solution: The above error is very common error for most of the users when a Queue manager tries to access a queue or when it tries to issue MQCONN call on the queues which are not belonging to it then it gives us an error.<br />
<br />
<i>ASNCLP SESSION SET TO Q REPLICATION;</i><br />
<i>CREATE MQ SCRIPT CONFIG TYPE B</i><br />
<i>MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, </i><br />
<i>MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB;</i><br />
=======================================================================<br />
In the above setup following are the configurations.<br />
<br />
Database 1 : TESTA<br />
Database 2 : TESTB<br />
QManager name for TESTA database is: QMTA<br />
QManager name for TESTB database is: QMTB<br />
Schema that we are using for TESTA database is ASNTA under which control tables, replication queue maps and all replication related objects are created in this schema.<br />
Schema that we are using for TESTB database is ASNTB under which control tables, replication queue maps and all replication related objects are created in this schema.<br />
<br />
========================================================================<br />
<i>ASNCLP SESSION SET TO Q REPLICATION;</i><br />
<i>SET OUTPUT MULTIDIR;</i><br />
<i>SET RUN SCRIPT NOW STOP ON SQL ERROR ON;</i><br />
<i>SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;</i><br />
<i>SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;</i><br />
<i>CREATE CONTROL TABLES FOR NODE 1;</i><br />
<i>CREATE CONTROL TABLES FOR NODE 2;</i><br />
<i>CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);</i><br />
<i>CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);</i><br />
<br />
Above script defines control tables for NODE1 and NODE2 and replication queue maps for NODE1 and NODE2<br />
========================================================================<br />
<br />
1) When i run the first set of commands using asnclp it will give scripts for BIDIRECTIONAL replication between two databases TESTA and TESTB and setup Queue managers QMTA for TESTA, QMTB for TESTB and sets replication schema ASNTA for TESTA and ASNTB for TESTB<br />
<br />
2) When i run the second set of commands using asnclp it will give script for setting up control tables and replication queue maps between both the database viceversa<br />
<br />
Everything ran successfully Queue managers QMTA and QMTB are up and running and all necessary objects are created. But when i start the Q capture program and when it tries to issue MQCONN call it wasnt able to identify the queues and gave me MQ error 2058, because in the above script i didnt specify queue manager name for NODE1 and NODE2.<br />
<br />
SET QMANAGER QMTA FOR NODE 1;<br />
SET QMANAGER QMTB FOR NODE 2;<br />
<br />
When i added above two lines to second script that is setting the which qmanager to which node, then all the objects are created with the right Queue managers. Before running this script again i have dropped the schema ASNTA and ASNTB from both sides.<br />
<br />
<i>ASNCLP SESSION SET TO Q REPLICATION;</i><br />
<i>SET OUTPUT MULTIDIR;</i><br />
<i>SET RUN SCRIPT NOW STOP ON SQL ERROR ON;</i><br />
<i>SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;</i><br />
<i>SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;</i><br />
<i style="background-color: lime;">SET QMANAGER QMTA FOR NODE 1;</i><br />
<i style="background-color: lime;">SET QMANAGER QMTB FOR NODE 2;</i><br />
<i>CREATE CONTROL TABLES FOR NODE 1;</i><br />
<i>CREATE CONTROL TABLES FOR NODE 2;</i><br />
<i>CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);</i><br />
<i>CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);</i><br />
<br />
Then i was able to start the capture program and apply program and was able to successfully start the replication. Below link has helped me diagnosing the error<br />
<a href="http://www-01.ibm.com/support/docview.wss?uid=swg21166938" style="background-color: lime;">http://www-01.ibm.com/support/docview.wss?uid=swg21166938</a></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-8594470886688592572014-04-13T00:37:00.000+05:302014-10-25T21:39:24.005+05:30DB2 Q Replication setup for an active active solution PART 2<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: inherit;">I'm back after my 5 day continuous night shifts.. Its </span>Saturday<span style="font-family: inherit;"> today fresh day to start with PART 2 to quick setup of DB2 Q Replication for active active solution</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br />
</span></div>
<span style="font-family: inherit;">We have installed DB2 on Fedora box in PART 1 , now let us install Websphere MQ on top of it.</span><br />
<span style="font-family: inherit;"><br /><b><u>Installation of Websphere MQ 7.1 :</u></b></span><br />
<span style="font-family: inherit;"><b><u><br /></u></b>1) Untar the installation files<br /><i>tar -xvf WMQ_7.1.0.3_TRIAL_LNX_X86_64_ML.tar.gz</i></span><br />
<span style="font-family: inherit;"><i><br /></i>2) we need to accept the license before proceeding for installation of MQ<br /><i>sudo ./mqlicense.sh -accept</i></span><br />
<span style="font-family: inherit;"><i><br /></i>3) Now lets install Websphere MQ using below command<br /><i>rpm -ivh MQSeries*</i></span><br />
<span style="font-family: inherit;"><i><br /></i>4) After MQ installed we need to set the current installation as primary for that we need to run setmqinst command from installation path like below.<br /><i>sudo ./mqsetinst -i -p /opt/mqm</i><br />After it executes we should see this messages like below.</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox bin]$ <i>sudo ./setmqinst -i -p /opt/mqm/</i></span><br />
<span style="font-family: inherit;">114 of 114 tasks have been completed successfuly.</span><br />
<br />
<span style="font-family: inherit;">'Installation1' (/opt/mqm) set as the Primary Installation.</span><br />
<div>
<span style="font-family: inherit;"><br /></span></div>
<span style="font-family: inherit;">5) Run command <i>dspmq</i> to see installed version of MQ</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox bin]$ <i>dspmqver</i></span><br />
<br />
<span style="font-family: inherit;">Name: WebSphere MQ</span><br />
<span style="font-family: inherit;">Version: 7.1.0.3</span><br />
<span style="font-family: inherit;">Level: p710-003-130724</span><br />
<span style="font-family: inherit;">BuildType: IKAP - (Production)</span><br />
<span style="font-family: inherit;">Platform: WebSphere MQ for Linux (x86-64 platform)</span><br />
<span style="font-family: inherit;">Mode: 64-bit</span><br />
<span style="font-family: inherit;">O/S: Linux 3.6.10-4.fc18.x86_64</span><br />
<span style="font-family: inherit;">InstName: Installation1</span><br />
<span style="font-family: inherit;">InstDesc: </span><br />
<span style="font-family: inherit;">InstPath: /opt/mqm</span><br />
<span style="font-family: inherit;">DataPath: /var/mqm</span><br />
<span style="font-family: inherit;">Primary: Yes</span><br />
<span style="font-family: inherit;">MaxCmdLevel: 711</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b>If this MQ installation is not first time on your machine, you need to run crtmqpkg command to create unique set of packages to install in your system. In order to do this we need to install pax before running crtmqpkg command</b></span><br />
<div style="text-align: left;">
</div>
<br />
<i style="font-family: inherit;">sudo apt-get install pax</i><br />
<span style="font-family: inherit;"><i>sudo ./crtmqpkg PROD</i><br />after the packages are created a path is displayed where those are created we need to go to that directory and install the available RPM's for MQ .<br /><i>cd /var/tmp/mq_rpms/PROD/</i></span><br />
<div style="text-align: left;">
</div>
<i style="font-family: inherit;">sudo crtmqpkg test</i><span style="font-family: inherit;"> </span><br />
<i>sudo rpm -ivh MQSeries*</i><br />
<br />
<br />
<div>
<span style="font-family: inherit;">================= End of </span>installation <span style="font-family: inherit;">Websphere MQ ===========================</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">A prerequisite for using Q Replication is that databases should be enabled for archive logging. use below script that creates a database , setup archive logging, and take an offline backup . see that you create the needed directories that used for archive log location, Put it everything in a file and run it.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<i>create database testa;</i></div>
<div>
<i>create database testb;</i></div>
<div>
<i>update db cfg for testa using LOGARCHMETH1 DISK:/home/db2inst1/archivelogs/testa;</i></div>
<div>
<div>
<i>backup database testa to /dev/null;</i></div>
<div>
<i>update db cfg for testb using LOGARCHMETH2 DISK:/home/db2inst1/archivelogs/testb;</i></div>
<div>
<i>backup database testb to /dev/null;</i></div>
</div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<i>db2 -stvf create.sql -z create.out</i></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">Now lets create Queue Manager and it objects. Use below script</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div>
<i>ASNCLP SESSION SET TO Q REPLICATION;</i></div>
<div>
<i>CREATE MQ SCRIPT CONFIG TYPE B</i></div>
<div>
<i>MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, </i></div>
<div>
<i>MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB; </i></div>
</div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">Change the "fedorabox" hostname to your machine name. </span></div>
<div>
<span style="font-family: inherit;"><br /></span>
<b><u><span style="font-family: inherit;">Important points to understand here:</span></u></b><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">CREATE MQ SCRIPT command will generate scripts for creating all of the WebSphere MQ objects that are needed for Q Replication. </span>Lets run the script using asnclp command line, Once done we can see 4 files created 2 files ending with .sh is for unix/linux OS , 2 files ending with .bat is for windows OS.<br />
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">[db2inst1@fedorabox scripts]$ <i>asnclp -f create_mq_objects.asn</i></span></div>
<div>
<span style="font-family: inherit;"><br /></span>
<br />
<div>
<span style="font-family: inherit;">[db2inst1@fedorabox scripts]$ <i>ls -lrt</i></span></div>
<span style="font-family: inherit;">total 40</span><span style="font-family: inherit;"></span><br />
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 240 Apr 12 19:49 create_mq_objects.asn</span></div>
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 5329 Apr 12 19:50 qrepl.testa.mq_windows.bat</span></div>
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 5330 Apr 12 19:50 qrepl.testa.mq_aixlinux.sh</span></div>
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 4671 Apr 12 19:50 qrepl.testb.mq_windows.bat</span></div>
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 4666 Apr 12 19:50 qrepl.testb.mq_aixlinux.sh</span></div>
<div>
<span style="font-family: inherit;">-rw-rw-r--. 1 db2inst1 db2inst1 412 Apr 12 19:50 qreplmsg.log</span></div>
<div>
<span style="font-family: inherit;"><br /></span>
Now switch to mq user and run the 2 files one for testa other for testb.<br />
<br />
<i>sh qrepl.testa.mq_aixlinux.sh</i><br />
<i>sh qrepl.testb.mq_aixlinux.sh</i><br />
<i><br /></i></div>
<div>
<span style="font-family: inherit;">After running that we should be able to see there are 2 Queue managers that should up and running as show below.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">[db2inst1@fedorabox mqm]$ dspmq</span><br />
<span style="font-family: inherit;">QMNAME(QMTA) STATUS(Running)</span><br />
<span style="font-family: inherit;">QMNAME(QMTB) STATUS(Running)</span><br />
<span style="font-family: inherit;"><br /></span></div>
</div>
<span style="font-family: inherit;">Lets create a table named "dba" with columns as below and run on both the databases TESTA and TESTB. </span><br />
<div style="text-align: left;">
</div>
<span style="font-family: inherit;"><br />[db2inst1@fedorabox scripts]$ <i>db2 connect to testa</i><br /> Database Connection Information<br /> Database server = DB2/LINUXX8664 9.7.6<br /> SQL authorization ID = DB2INST1<br /> Local database alias = TESTA<br />[db2inst1@fedorabox scripts]$ <i>db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"</i><br />DB20000I The SQL command completed successfully.<br />[db2inst1@fedorabox scripts]$ <i>db2 "alter table dba add primary key(id)"</i><br />DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox scripts]$ <i>db2 connect to testb</i><br /> Database Connection Information<br /> Database server = DB2/LINUXX8664 9.7.6<br /> SQL authorization ID = DB2INST1<br /> Local database alias = TESTB</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox scripts]$ <i>db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"</i><br />DB20000I The SQL command completed successfully.<br />[db2inst1@fedorabox scripts]$ <i>db2 "alter table dba add primary key(id)"</i><br />DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br />Now we use below script to create control tables,replication queue maps and a sample subscription for table DBA.</span><br />
<span style="font-family: inherit;"><br /><b><u>Important points to understand here:</u></b></span><br />
<span style="font-family: inherit;"><b><br /></b>create control tables statement will create control tables which store information about Q replication and its sources and targets.<br />create replqmap statement will create replication queue maps which are needed to identify websphere mq queues on both the servers that is source and target. One replication queue map defined from source to target and one for target to source.<br />create qsub statement will create a queue subscription that maps source table to target table.</span><br />
<span style="font-family: inherit;"><br /><i>ASNCLP SESSION SET TO Q REPLICATION;<br />SET OUTPUT MULTIDIR;<br />SET RUN SCRIPT NOW STOP ON SQL ERROR ON;<br />SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;<br />SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;<br />SET QMANAGER QMTA FOR NODE 1;<br />SET QMANAGER QMTB FOR NODE 2;<br />CREATE CONTROL TABLES FOR NODE 1;<br />CREATE CONTROL TABLES FOR NODE 2;<br />CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);<br />CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);<br />SET TABLES(NODE 1 "DBA");<br />CREATE QSUB SUBTYPE B FROM NODE TESTA.ASNTA SOURCE HAS LOAD PHASE I START AUTOMATICALLY YES TARGET LOAD TYPE 0;</i><br /><br />Now run the script using asnclp command line </span><br />
<i>asnclp -f replication_setup.asn</i><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">This will create control tables, replication queue map and subscription for table DBA. Once done with above script now lets start the capture and apply programs on both sides.</span><br />
<br />
<span style="font-family: inherit;"><b>Issue this commands using db2inst1 ID</b></span><br />
<span style="font-family: inherit;"><br /><i>nohup asnqcap capture_server=TESTA capture_schema=ASNTA &<br />nohup asnqcap capture_server=TESTB capture_schema=ASNTB &<br />nohup asnqapp apply_server=TESTA apply_schema=ASNTA &<br />nohup asnqapp apply_server=TESTB apply_schema=ASNTB &</i></span><br />
<span style="font-family: inherit;"><br />Lets replicate the data now, Insert data from TESTA and look if its replicated to TESTB database.</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox mqm]$ <i>db2 connect to TESTA</i><br /> Database Connection Information<br /> Database server = DB2/LINUXX8664 9.7.6<br /> SQL authorization ID = DB2INST1<br /> Local database alias = TESTA</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox mqm]$ <i>db2 "insert into dba values(1,'Murali','db2')"</i><br />DB20000I The SQL command completed successfully.</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox mqm]$ <i>db2 "select * from dba"</i><br />ID NAME TOPIC <br />------- ---------- ----------<br /> 1. Murali db2 <br /> 1 record(s) selected.</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox mqm]$ <i>db2 connect to testb</i><br /> Database Connection Information<br /> Database server = DB2/LINUXX8664 9.7.6<br /> SQL authorization ID = DB2INST1<br /> Local database alias = TESTB</span><br />
<span style="font-family: inherit;"><br />[db2inst1@fedorabox mqm]$ <i>db2 "select * from dba"</i><br />ID NAME TOPIC <br />------- ---------- ----------<br /> 1. Murali db2 <br /> 1 record(s) selected.</span><br />
<span style="font-family: inherit;"><br />So the data is replicating and we just did it.... :) that's all with this tutorial on DB2 Q Replication.</span><br />
<br />
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">In my next post i will post the common issues that i have encountered when did my first setup of Q Replication. People post your comments here ..... and lets start exploring more on Q Replication.</span></div>
<div style="text-align: left;">
<div>
<div style="font-family: inherit;">
<br /></div>
</div>
</div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com4tag:blogger.com,1999:blog-3266446665817070990.post-21177324448738194162014-04-07T15:36:00.002+05:302014-04-15T15:58:17.232+05:30DB2 Q Replication setup for an active active solution PART 1<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<u><span style="color: red;">How to setup DB2 Q Replication</span></u></div>
<br />
We are about a setup DB2 Q Replication that demonstrates active active solution. Active - Active simply means changes happening at source should go to target and at same time changes happening at target should go to source.<br />
<br />
<u><b>High level steps:</b></u><br />
<br />
1) Install DB2 9.7 on Fedora 18 OS - I'm Fedora OS as its free edition supported by REDHAT community.<br />
2) Install Websphere MQ 7.1 for messaging system between 2 DB2 databases.<br />
3) Create MQ objects needed for active active Q replication.<br />
4) setup a replication for a sample table to demonstrate active active replication.<br />
<br />
Below is the setup of two databases named TESTA and TESTB for demonstration of active - active Q replication between two db2 databases. Below is how the setup look like.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj24nAO2vSK-VFWXdZWcXqRwH61hph8g_I2yKnnGB_IXmwpQvNrP2sZcrbunFYFBp4I04yhDw_eWKWTOSBoll4DsvE892cWefog1Q13A3tUi33sULZsCetdo9h27bjYMZgnpyNohHyTytw/s1600/3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj24nAO2vSK-VFWXdZWcXqRwH61hph8g_I2yKnnGB_IXmwpQvNrP2sZcrbunFYFBp4I04yhDw_eWKWTOSBoll4DsvE892cWefog1Q13A3tUi33sULZsCetdo9h27bjYMZgnpyNohHyTytw/s1600/3.JPG" height="334" width="640" /></a></div>
<br />
<br />
<u><b>Links to download the needed software's</b></u><br />
<br />
1) Fedora 18 or above ( Free copy ) from here :- <a href="http://fedoraproject.org/en_GB/get-fedora-all">http://fedoraproject.org/en_GB/get-fedora-all</a> <br />
<br />
2) DB2 9.7 AESE ( trail 90 days ) from here <a href="https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_us&source=swg-dm-db297trial&S_CMP=web_dw_rt_swd">https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_us&source=swg-dm-db297trial&S_CMP=web_dw_rt_swd</a><br />
<br />
3) Websphere 7.1 (trail 90 days ) from here :- <a href="http://www.ibm.com/webspheremq/downloads">www.ibm.com/webspheremq/downloads</a><br />
<br />
<b><u>Installation procedure of DB2 9.7 on Fedora OS</u></b><br />
<br />
After installing fedora and Vmware tools you will be able to copy the downloaded trails files to the host machine to proceed with installation. An advice here when you try to copy to host machine, Use the command with sudo access ( <i>systemctl mask tmp.mount</i> ), This will turn off any filesystem from being mounted on /tmp, forcing it to be on / instead. To turn it on again, run ( <i>systemctl unmask tmp.mount</i> )<br />
<br />
1) Its always a best practice to do a prerequisite check before installing DB2<br />
<br />
[db2inst1@fedorabox server]$ <i>cd server/</i><br />
[db2inst1@fedorabox server]$ <i>sudo sh db2prereqcheck</i><br />
[sudo] password for db2inst1:<br />
WARNING:<br />
DBT3534W The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.<br />
WARNING:<br />
The 32-bit library file libpam.so is not found on the system.<br />
<div>
<br /></div>
<div>
Above you can see that there is a warning that libpam.so is missing. Now lets install it from internet. run <i>sudo yum install pam-devel.i686</i> in your terminal</div>
<div>
<br /></div>
<div>
Ok lets run the prereqcheck again and see.</div>
<div>
<br /></div>
<div>
<div>
[db2inst1@fedorabox server]$ <i>sudo sh db2prereqcheck</i></div>
<div>
[sudo] password for db2inst1: </div>
<div>
WARNING:</div>
<div>
DBT3534W The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.</div>
</div>
<div>
<b>above warning can be ignored.</b></div>
<div>
<br /></div>
<div>
2) now lets install the db2 using db2setup</div>
<div>
<br /></div>
<div>
[db2inst1@fedorabox server]$ <i>sudo sh db2setup</i></div>
<div>
<br /></div>
<div>
we get a GUI prompt like ( shown below ) which will allow us to use various options for installation, Just go ahead proceed with all steps and at the end when you click finished , it will do the actual work of installing DB2 with the selected options </div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYlAAeu68RaaurfAYJTwbz8HFsLkXuw8XrBplB2ooAhbrjVG_QeAZGhenH-S2VIuBt_QvAvyGP5_eiT51Ea_Ijt0tgnetFO2MUY6S4P9ooxzPVR5mIToDvsiJN1JEob324rbJIB6_71sk/s1600/db2setup.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYlAAeu68RaaurfAYJTwbz8HFsLkXuw8XrBplB2ooAhbrjVG_QeAZGhenH-S2VIuBt_QvAvyGP5_eiT51Ea_Ijt0tgnetFO2MUY6S4P9ooxzPVR5mIToDvsiJN1JEob324rbJIB6_71sk/s1600/db2setup.JPG" height="337" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
When all the steps are completed lets set the db2profile in user home directory add below 3 lines to profile file of db2inst1 ( run <i>vi .bash_profile</i> )</div>
<div>
<br /></div>
<div>
<i>if [ -f /home/db2inst1/sqllib/db2profile ]; then</i></div>
<div>
<i> sh /home/db2inst1/sqllib/db2profile</i></div>
<div>
<i>fi</i></div>
<div>
<br /></div>
<div>
execute the profile file to take changes into effect </div>
<div>
run <i>. .bash_profile</i> </div>
<div>
<br /></div>
<div>
3) Now lets start the instance.</div>
<div>
<br /></div>
<div>
[db2inst1@fedorabox ~]$ <i>db2start</i></div>
<div>
04/07/2014 15:17:17 0 0 SQL1063N DB2START processing was successful.</div>
<div>
SQL1063N DB2START processing was successful.<br />
<br /></div>
<div>
</div>
<div>
That's it with DB2 Installation on Fedora OS and we can proceed to step 2 which will be preceded in the next post as PART 2</div>
<br /></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-16591543461296330352014-04-04T23:59:00.003+05:302014-04-15T16:04:12.494+05:30DB2 Q Replication it made me stuck for more than a month<div dir="ltr" style="text-align: left;" trbidi="on">
I'm back with fresh concepts and ideas in my mind, those were the things that made me stuck with issues for at least more than a month and was not able to blog anything until i learnt something practical out of it myself. So what is that interesting topic in DB2?<br />
<br />
<b>DB2 Q Replication and Event Publishing.</b><br />
<br />
Since the time i started working on replication it made me so interested that i wanted to implement and see how it works.<br />
<br />
DB2 Q Replication captures the changed data from source and send committed transactions as messages using a messaging system called IBM WebSphere Message Queues to the target.<br />
<br />
Changes that happen at source are captured by Capture program by reading DB2 recovery logs and put them as messages on the WebSphere message queues where the apply program resides at target reads messages from queues and write back changes to respective targets. below figure explains it.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQav8T-wV6qT6uFE2GGtYrBcSIxtNdw0j1PNkNk-QEBkqooTvN9L6PsD54ZuLSG9ylJrlaXbRZkKpYv70e2N7AWm6jXkgv8qxDIaueJGLKTfMh6fATWo5GyWYHyOif_r4yJyerUDWbKUw/s1600/intro.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQav8T-wV6qT6uFE2GGtYrBcSIxtNdw0j1PNkNk-QEBkqooTvN9L6PsD54ZuLSG9ylJrlaXbRZkKpYv70e2N7AWm6jXkgv8qxDIaueJGLKTfMh6fATWo5GyWYHyOif_r4yJyerUDWbKUw/s1600/intro.gif" height="162" width="400" /></a></div>
<br />
<br />
I'm my next post i will give the implementation of DB2 Q replication between two DB2 databases.</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-89841300518427868132014-02-19T18:16:00.000+05:302014-02-19T18:19:43.746+05:30How to create federation between two DB2 databases<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Trebuchet MS, sans-serif;">The other day developer were asking if they access production table in their test databases. Here when we suggested them that we have Federation support across multiple RDMS vendors.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">So lets get started quickly on how to create federation between two DB2 databases</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">It involves 5 steps Enable database to support federation, </span><span style="font-family: 'Trebuchet MS', sans-serif;">Create wrapper, server, usermapping, nicknames . F</span><span style="font-family: 'Trebuchet MS', sans-serif;">irst we need to check whether Federation support is switched on for our database, If you see that FEDERATION is set to off go head and update to YES and recycle the instance</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ <b>db2 get dbm cfg | grep -i FEDERATED</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> Federated Database System Support <b>(FEDERATED) = NO</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ <b>db2 update dbm cfg using FEDERATED YES</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed </span><br />
<span style="font-family: Trebuchet MS, sans-serif;">successfully.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ <b>db2stop</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">19/02/2014 17:41:06 0 0 SQL1064N DB2STOP processing was successful.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">SQL1064N DB2STOP processing was successful.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ <b>db2start</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">02/19/2014 17:41:10 0 0 SQL1063N DB2START processing was successful.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"></span><br />
<span style="font-family: Trebuchet MS, sans-serif;">SQL1063N DB2START processing was successful.</span><br />
<div>
<br /></div>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ <b>db2 get dbm cfg | grep -i FEDERATED</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> Federated Database System Support <b>(FEDERATED) = YES</b></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span>
<span style="font-family: 'Trebuchet MS', sans-serif;">Wrapper</span><span class="Apple-tab-span" style="font-family: 'Trebuchet MS', sans-serif; white-space: pre;"> </span><span style="font-family: 'Trebuchet MS', sans-serif;">: The CREATE WRAPPER statement registers a wrapper with a federated server. A wrapper is a mechanism by which a federated server can interact with certain types of data sources,The default wrapper name for the DB2 family data sources is DRDA.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax<span class="Apple-tab-span" style="white-space: pre;"> </span>: <b>CREATE WRAPPER <wrappername></b> </span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Server<span class="Apple-tab-span" style="white-space: pre;"> </span>: The CREATE SERVER statement defines a data source to a federated database.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax<span class="Apple-tab-span" style="white-space: pre;"> </span>: <b>CREATE SERVER <servername> TYPE <datasourcename> VERSION <serverversion> WRAPPER <wrappername> AUTHORIZATION "<remoteauthid>" PASSWORD "<password>" OPTIONS (DBNAME 'dbname')</b> </span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Usermapping<span class="Apple-tab-span" style="white-space: pre;"> </span>: The CREATE USER MAPPING statement defines a mapping between an authorization ID that uses a federated database and the authorization ID and password to use at a specified data source.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax<span class="Apple-tab-span" style="white-space: pre;"> </span>: <b>CREATE USER MAPPING for <authname> SERVER <servername> OPTIONS (REMOTE_AUTHID 'username',REMOTE_PASSWORD 'password')</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Nicknames<span class="Apple-tab-span" style="white-space: pre;"> </span>: The CREATE NICKNAME statement defines a nickname for a data source object. we use this nick name to fetch records from the federated database.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax:<span class="Apple-tab-span" style="white-space: pre;"> </span>: <b>CREATE NICKNAME <nickname> for <servername>.<schenaname.tablename></b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Now let me take a scenario and explain it using two db2 databases. My aim is to access table in TESTA database from the database MURALI </span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">murali@test-machine:~$ db2 connect to murali</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Database Connection Information</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Database server = DB2/LINUXX8664 9.7.6</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> SQL authorization ID = MURALI</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> Local database alias = MURALI</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">create wrapper drda</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">create server fedserver TYPE DB2/UDB VERSION 9.7 WRAPPER drda AUTHORIZATION "murali" password "murali" OPTIONS(DBNAME 'TESTA')</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">create user mapping for murali SERVER fedserver OPTIONS(REMOTE_AUTHID 'db2inst1',REMOTE_PASSWORD 'db2inst1')</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">create nickname mytable for fedserver.db2inst1.tab1</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">murali@test-machine:~$ db2 connect to murali</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Database Connection Information</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Database server = DB2/LINUXX8664 9.7.6</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> SQL authorization ID = MURALI</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> Local database alias = MURALI</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">murali@test-machine:~$ db2 "select * from mytable"</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;">ID NAME </span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;">------- ----------</span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> 1. murali </span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> 2. sharath </span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> 3. balaji </span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"> 4. praks </span></i><br />
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i>
<i><span style="font-family: Trebuchet MS, sans-serif;"> 4 record(s) selected.</span></i><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Post your comments and doubts about this topic here.</span></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-24252261225759770862014-02-18T10:25:00.001+05:302014-02-18T10:25:45.916+05:30How to Drop schema including its contents in DB2<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
<span style="font-family: Trebuchet MS, sans-serif;">One day i was doing a schema refresh activity that's when this point came in, I first have to drop the schemas including content and then do a copy.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">DB2 has provided us, with a admin procedure which drop's a specific schema and also all the objects contained in it. </span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<b><span style="font-family: Trebuchet MS, sans-serif;">ADMIN_DROP_SCHEMA procedure</span></b></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax: <b>ADMIN_DROP_SCHEMA(<schema>,<dropmode>,<errortabschema>,<errortab>)</b></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><schemname><span class="Apple-tab-span" style="white-space: pre;"> </span>- Mention the name of the schema to be dropped in CAPTITAL LETTER</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><dropmode><span class="Apple-tab-span" style="white-space: pre;"> </span>- Reserved for future use and should be set to NULL</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><errortabschema><span class="Apple-tab-span" style="white-space: pre;"> </span>- specify the schema name of a table containing error information for objects that could not be dropped </span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><errortab><span class="Apple-tab-span" style="white-space: pre;"> </span>- specify the name of a table containing error information for objects that could not be dropped.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">Example: db2 "call ADMIN_DROP_SCHEMA('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ db2 "call admin_drop_schema('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Value of output parameters</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> --------------------------</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Parameter Name : ERRORTABSCHEMA</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Parameter Value : -</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Parameter Name : ERRORTAB</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Parameter Value : -</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> Return Status = 0</span></i></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">Return status=0 so it means success. Just query the schemaname in database and could see it created ERRORSCHEMA and as it didn't have any issues no errortable is created. If a schema doesn't contain any objects it easy to drop a schema using simple command</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2 "drop schema <schemaname> RESTRICT"</span></i></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">Let me use it on the empty schema which is just created when we tried to drop SAMPLE schema using ADMIN_SCHEMA_DROP procedure, Below is the example for it.</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ db2 "list tables for schema errorschema"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">Table/View Schema Type Creation time </span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">------------------------------- --------------- ----- --------------------------</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"> 0 record(s) selected.</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ db2 "drop schema errorschema restrict"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">I dropped the schema using admin_drop_schema and it has given me return status 0, still i could able to see it in catalog. After some research i understood that there are some dependants which are dropped so i was unable to drop it from catalog. I then took</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">DB2LOOK for the specific schema and then was able to the USER DEFINED DATA TYPES which are there so i was unable to drop the schema from catalog. I then dropped the user defined dataypes and executed the command db2 "drop schema schemaname restrict" this time</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">i was able to drop it from catalog. Before dropping the user defined data types i saved them in some file so if needed i can create them again. Also to make sure if your left with any more take a DB2LOOK and check again, Below is the example for the same.</span></div>
<div>
</div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2 "drop type userdefined1"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2 "drop type userdefined2"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">db2 "drop schema schemaname restrict"</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;">DB20000I The SQL command completed successfully.</span></i></div>
<div>
<i><span style="font-family: Trebuchet MS, sans-serif;"><br /></span></i></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;">References from IBM : ADMIN_DROP_SCHEMA procedure V9.7</span></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<a href="http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0022036.html"><span style="font-family: Trebuchet MS, sans-serif;">http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0022036.html</span></a></div>
<div>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span></div>
<div>
<br /></div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-11460439397981743212014-02-16T10:38:00.001+05:302014-02-16T10:55:31.746+05:30Important points to be noted when doing DB2 Database Refresh to UAT or DEV environments<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Trebuchet MS, sans-serif;">Hey all, today i would discuss on the important points that needs to be kept in mind when refreshing from PROD to UAT or DEV environments. Below are the issues that i recently faced when doing a refresh activity from PROD to UAT.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b>1) Bufferpools unavailability, only hidden bufferpools started.</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>2) Server hangup during rollforward phase, even doesn't allow other users to login and kill it.</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>3) Rollforward fails saying no enough memory to allocate.</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>4) Rollforward fails due to archive log location is not valid.</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Overall why are these incidents happened ? Answer for this is we have different physical configurations and different settings in PROD when noted to UAT or DEV. So before doing a refresh take a backup of all the DB CFG parameters for all the databases also DBM cfg of the instance for which you intend the refresh.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b>1) INSTANCE_MEMORY parameter</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>2) DATABASE_MEMORY parameter</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>3) Bufferpools size</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>4) LOGARCHMETH1 path</b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">My Scenario.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">We have 120 GB phsyical memory available in prod so we used 110 GB for INSTANCE_MEMORY parameter. Also for one of the db we have 50 GB set to DATABASE_MEMORY parameter so what happened when did a backup and restore in UAT .</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Restore was successful, then started rollforward of db then suddenly our prompt got hanged, not responding . When even tried to login from different session its not allowing to login. So we contacted system admin, and he told that total memory being used by DB2 and due to which its not allowing the users to ssh also. He then hard reboot the machine and then I checked and could see only 62 GB of physical RAM available on the server, but our DATABASE_MEMORY was set to 110 GB automatic so we understood that this is the main reason for server hangup. We then updated the DATABASE_MEMORY to 10 GB with automatic settings. A new issue started when trying to activate the db, as bufferpools memory settings in PROD are very high so only DB started only with hidden bufferpools which we didn't observe and started to rollforward again, which resulted in failure. So for time being i used this method, I made the bufferpools started with only 3000 pages initially, Here is the environment variable to set the BP intital pages to some number of pages, use command <b>DB2SET DB2_OVERRIDE_BPF=3000</b> , after setting it check if reflected or not <b>DB2SET -ALL</b> , this command show all the environment variables that are set. Then tried rollforward again and this time it again resulted in failure as we are having LOGARCHMETH1 in prod location not matching in the UAT server which resulted in failure. We then updated the LOGARCHMETH1 parameter to valid path and then tried rollforward again and this time its SUCCESS. Finally we have activated and connected to database and made valid changes to bufferpool size based on database_memory and restarted the DB.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Readers anybody has any doubts or any other errors that you faced please post your comments here and we shall discuss on them and update here.</span></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-11818829022983786062014-02-13T12:35:00.001+05:302014-02-16T11:02:40.555+05:30Catalog a DB2 database<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: Trebuchet MS, sans-serif;">Aim : Need to access a remote database as local database on the server that i currently reside on. For this we need to catalog the database</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Works on : Machine having db2 server or client sotware installed on it</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Its a two step process First catalog node then catalog database.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Catalog Node <span class="Apple-tab-span" style="white-space: pre;"> </span>- This puts an entry into node directory.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">Catalog Database <span class="Apple-tab-span" style="white-space: pre;"> </span>- This puts an entry into local database directory.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Syntax</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><b>db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT></b></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">use can use any name for <nodename>, this is just for indetification purpose.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">use the ip address or hostname of the target machine for <remote> parameter.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">use port number on which the db2 server is listening to <PORT> for allowing incoming connections.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">To get the ip address of machine, use command - ifconfig -a</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">To get the hostname or machine name, use command - hostname</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">To get the port number to which db2 server is listening to, use command - db2 get dbm cfg | grep SVCE </span><br />
<span style="font-family: Trebuchet MS, sans-serif;">SVCENAME is the service name of db2, which we need to match this services files in /etc/services to get the respective port number for the service name(SVCENAME) </span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">this is example of matching SVCENAME to respective portnumber in /etc/services file</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ db2 get dbm cfg | grep SVCE</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> TCP/IP Service name (SVCENAME) = db2c_db2inst1</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> SSL service name (SSL_SVCENAME) = </span><br />
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ </span><br />
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ cat /etc/services | grep db2c_db2inst1</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">db2c_db2inst1<span class="Apple-tab-span" style="white-space: pre;"> </span>50001/tcp</span><br />
<br />
<span style="font-family: Trebuchet MS, sans-serif;"><b>db2 catalog database <dbname> as <aliasname> at node <nodename></b></span><br />
<br />
<span style="font-family: Trebuchet MS, sans-serif;">use database name for <dbname> you can also put an alias name for that database using <aliasname> if you dont want any such just ignore it.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;">user node name that is defined in the first command <nodename></span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">Always make sure to check the connectivity to the database, that how its shown below.</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;">db2inst1@test-machine:~$ db2 connect to test1 user murali using murali</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"> Database Connection Information</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"><br /></span>
<span style="font-family: Trebuchet MS, sans-serif;"> Database server = DB2/LINUXX8664 9.7.6</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> SQL authorization ID = MURALI</span><br />
<span style="font-family: Trebuchet MS, sans-serif;"> Local database alias = TEST1</span><br />
<div>
<br /></div>
</div>
</div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0tag:blogger.com,1999:blog-3266446665817070990.post-14466633664730037772014-02-11T11:34:00.002+05:302014-02-16T11:03:07.014+05:30Installation of DB2 V9.7 on UBUNTU 12.04 - PART 2<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
PART 2</h3>
<br />
Now drag and drop the downloaded copy of IBM DB2 Data server trail copy from your Windows machine to Ubuntu. Follow below instructions.<br />
<br />
<h4 style="text-align: left;">
Always remember we are doing root installation, so remember to use sudo command / else login into root account and setup the db2</h4>
<br />
1) To extract zipped files to a directory use command <br />
<b>tar -xvf v9.7_linuxx64_server.tar.gz</b> -<br />
2) change to extracted directory use, list the files and you can now see 7 script files and 2 directories. Now we need to validate if all the prerequisites are met to continue installing DB2 in the machine.<br />
<br />
use this command <b>sh db2prereqcheck</b> it will show you errors/warnings like below.<br />
<br />
WARNING:<br />
The 32-bit library file libpam.so is not found on the system<br />
ERROR:<br />
The required library file libstdc++.so.5 is not found on the system.<br />
ERROR:<br />
The required library file libaio.so.1 is not found on the system.<br />
<br />
To fix this errors, we need to download and install below packages. Use below commands<br />
<br />
<b>1) sudo apt-get install libpam0g:i386 </b><br />
<b>2) sudo apt-get install libstdc++5</b><br />
<b>3) sudo apt-get install libaio-dev</b><br />
<br />
<br />
libpam.so.0 (32-bit) is required for DB2 database servers to run 32-bit non-SQL routines, use the command, libaio.so.1 is required for DB2 database servers using asynchronous I/O.<br />
libstdc++.so.5 is required for DB2 database servers and clients.<br />
<div>
<br /></div>
<div>
After install all 3 packages above run the prereqcheck again and see if we are good. Following all the instructions above shall give you no errors and warnings during prereqcheck</div>
<div>
<br /></div>
<div>
Now run <b>sh db2setup</b> to start the actual installation, DB2 Launch pad will open.</div>
<div>
<br /></div>
<div>
<div>
From the DB2 Setup launchpad, you can view installation prerequisites and the release notes, or you can proceed directly to the installation. </div>
<div>
<br /></div>
<div>
Click Install a Product and the Install a Product window displays the products available for installation.</div>
<div>
If you have no existing DB2 database products installed on your computer, launch the installation by clicking Install New. Proceed through the installation following the DB2 Setup wizard prompts.</div>
<div>
<br /></div>
<div>
If you have at least one existing DB2 database product installed on your computer, you can:</div>
<div>
1) Click Install New to create a new DB2 copy.</div>
<div>
2) Click Work with Existing to update an existing DB2 copy, to add function to an existing DB2 copy, upgrade an existing DB2 Version 9.5 and Version 9.7 copy, or to install an add-on product.</div>
</div>
<div>
<br /></div>
<div>
After the installation completes, login into the instance user and enter db2 to enter into interactive mode. Now your are good to fly into db2 world....</div>
<div>
<i><br /></i></div>
<div>
<div>
<i>db2inst1@test-machine:~$ db2</i></div>
<div>
<i>(c) Copyright IBM Corporation 1993,2007</i></div>
<div>
<i>Command Line Processor for DB2 Client 9.7.6</i></div>
</div>
<div>
<i>.....................................................................</i></div>
<div>
<i>.....................................................................</i></div>
<div>
<i><br /></i></div>
<div>
<div>
<i>For more detailed help, refer to the Online Reference Manual.</i></div>
<div>
<i><br /></i></div>
<div>
<i>db2 => </i></div>
</div>
<div>
<br /></div>
<b><br /></b>
<br />
<br /></div>
murali chavahttp://www.blogger.com/profile/12417090685903610903noreply@blogger.com0