Jephe Wu - http://linuxtechres.blogspot.com
Environment: RHEL4 32bit with "DB2 v8.1.2.88", "s050422", "MI00117", and FixPak "9". We need to add an additional column for a table, but we encountered error message below:
The row length of the table exceeded a limit of "4005" bytes. (Table space "xxxxx".)
Objective: to create a 8k tablespace and migrate this table over to new tablespace
Steps:
1. backup the table jephe by using the following commands:(assuming database and schema name are wu, talbe name is jephe)
# more backup.sql
export to "jephe.txt" of del messages jephe.msg select * from wu.jephe;
CONNECT RESET;
db2 connect to wu user wu
db2 -tvf backup.sql -l backup.sql.log -s
2. use db2look to extract the whole database ddl statements and grep the necessary table creation statements
db2look -d wu -e -z wu -o db2look.sql
vi db2look.sql to search string JEPHE to copy out the table create and alter statements for 'JEPHE' as well as alter statements for other tables which has foreign keys on table 'JEPHE'
e.g.
# more createtable.sql
CREATE TABLE "WU "."JEPHE" (
"LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
balabala
balabala
....
"CONTACT_NUMBER" VARCHAR(200) )
IN "TB_JEPHE" ;
ALTER TABLE "WU "."JEPHE"
ADD PRIMARY KEY
("LIFE_INSURANCE_OID");
ALTER TABLE "WU "."JEPHE"
ADD CONSTRAINT "SQL050615160106790" FOREIGN KEY
("BENEFIT_FILE_OID")
REFERENCES "WU "."BINARY_FILE_REPOSITORY"
("BINARY_FILE_OID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
# following is for another table 'TABLE1' which has foreign key for WU.JEPHE
ALTER TABLE "WU "."TABLE1"
ADD CONSTRAINT "SQL050615160109840" FOREIGN KEY
("JEPHE_OID")
REFERENCES "WU "."JEPHE"
("JEPHE_OID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
Note: once you drop table 'WU.JEPHE', the foreign key for WU.TABLE1 will also be gone. After recreating table WU.JEPHE in 8k tablespace, you have to create this foreign key again.
You also need to extract view creation statements which depends on the table 'WU.JEPHE'. Because you need to create view again after dropping and creating again WU.JEPHE in 8k tablespace.
# more view.sql
create view emp_benefits_life_ins_v as select balabala.
3. create a 8k buiffer pool tablespace and a 8k system temporary tablespace for 'order by' operation on new table wu.jephe, specify container path.
If you don't create a 8k temporary tablespace, you will encouter the following error message when doing 'order by' operation
db2 "select * from wu.jephe order by status"
SQL1585N A system temporary table space with sufficient page size does not
exist. SQLSTATE=54048
creation statement is as follows for system temporary tablespace:
CONNECT TO WU;
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 8 K MANAGED BY SYSTEM USING ('/db2/db2inst1/db/WU/tb_temp2' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL IBM8KBP ;
COMMENT ON TABLESPACE TEMPSPACE2 IS '8k temporary tablespace';
CONNECT RESET;
4 drop table
login as db2inst1
db2 connect to wu
db2 "drop table wu.jephe"
5 recreate table in 8k tablespace
db2 connect to wu
db2 set schema = jephe
modify createtable.sql to change
CREATE TABLE "WU "."JEPHE" (
"LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
balabala
balabala
....
"CONTACT_NUMBER" VARCHAR(200) )
IN "TB_JEPHE" ;
to
CREATE TABLE "WU "."JEPHE" (
"LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
balabala
balabala
....
"CONTACT_NUMBER" VARCHAR(200) )
IN "TB_JEPHE2";
db2 -tvf createtable.sql -l createtable.sql.log -s
6. restore data into table wu.jephe again from backup
#more restore.sql
import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe;
connect reset;
db2 connect to wu
db2 set schema = jephe
db2 -tvf restore.sql -l restore.sql.log -s
import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe
Number of rows read = 77
Number of rows skipped = 0
Number of rows inserted = 77
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 77
7. run command to add additional comumns for new table wu.jephe
# more command.sql
alter table wu.jephe add column contact_detail varchar(250);
db2 connect to wu
db2 set schema = jephe
db2 -tvf command.sql -l command.sql.log -s
8. grant permission for table WU.JEPHE and related views from db2 control center GUI
How to add additional columns for a table in db2 where default 4k tablespace is not enough
How to restore db2 database to specified date and restore the deleted records from certain table
Jephe Wu - http://linuxtechres.blogspot.com
Scenario: single DB2 instance and database db1, database transaction log is enabled, partial data of some tables in schema 'jephe' were deleted accidently. As this is a production database used by many different clients/schemas, we cannot restore back as the client only realized this accident one week later after deletion.
Solution: restore monthly database full backup plus the transaction logs just before the time which deletion happened on another server(DR?). Then use db2 export and db2 import to import back those deleted data.
Environment: RHEL5, IBM DB2 UDB 9.1 fixpack 3.
Steps:
1. restore the monthly full online database backup to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"
note: /data is the directory where the database full backup image exists.
2. get all the transaction log files after that full backup and before the deletion time
3. copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2011-04-19-11.30.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"
PLease refer to my last time blog at http://linuxtechres.blogspot.com/2010/07/how-to-onlineoffline-backup-and-restore.html
4. backup those tables on production database first in case they are destroyed during import process
# more backup.sh
cd /db2/db2inst1/scripts/20110420_restore/backup
db2 connect to db1
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * from table1"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * from table2"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * from table3"
db2 terminate
5. extract those deleted data first from restored DR database server
# more extract.sh
cd /home/db2inst1
db2 connect to db1dr
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * FROM table1 WHERE balabala-same statement used during deletion"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * FROM table2 WHERE balabala-same statement used during deletion"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * FROM table3 where balabala-same statement used during deletion"
6. import back to the production database (the sequence for importing might be different from the original deletion sequence as it might depends on foreign key or something)
# more import.sh
cd /db2/db2inst1/scripts/20110420_restore/
db2 connect to db1
db2 set schema = jephe
db2 "import from \"./table3\" of del messages \"./table1.imp\" insert into table1"
db2 "import from \"./table2\" of del messages \"./table2.imp\" insert into table2"
db2 "import from \"./table1\" of del messages \"./table3.imp\" insert into table3"
db2 terminate
Some DB2 database FAQs
Jephe Wu - http://linuxtechres.blogspot.com
1. Install IBM DB2 Client Version 9 on Windows 7 Professional 32bit
Problem: after configuring remote database profiles, it works in control center, but not in command editor.
Also, when you issue command 'db2' under db2 command prompt, it doesn't show anything.
Solution: go to Control Panel, User Account and Family Safety, User Accounts, Change user account control settings, put as 'Never notify'.
Note: If you enabled db2 operating system security, which means db2 installation created db2admins and db2users groups, you have to put the Windows logon user name into the corresponding groups before using db2 client.
2. db2advis usage
db2advis -d dbname -q schema_name -n schema_name -i input_file
Reference: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002452.htm
3. SQL0575N - View or materialized query table name cannot be used because it has been marked inoperative.
If name is a view, recreate the view by issuing a CREATE VIEW statement using the same view definition as the inoperative view. (see http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.msg.doc/doc/sql0575.htm )
Note: how to check if there are any other inoperative views in database schema name starting with NC.
db2 "select viewschema,viewname,valid from syscat.views where viewschema like 'NC%' and valid <> 'N'";
db2 "describe table syscat.views"
db2 "select viewschema,viewname,valid,text from syscat.views where viewname='NAMEOFVIEW' and viewschema = 'JEPHE'";
4. reason code 7
you need to reorg that table first before and after altering table.
5. DB2 version 9 comparison
http://www.slideshare.net/deepblue5479/a-comparison-reviewofdb29releases
Duplicate a db2 schema to another for a new client
Jephe Wu - http://linuxtechres.blogspot.com
Objective: duplicate a db2 schema to another for a new client
Environment: RHEL 5 server, IBM db2 V9, duplicate from existing client user1 to user2
# db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09013"
with level identifier "01040107".
Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack
"3".
Product is installed at "/opt/ibm/db2/V9.1".
Concept:
create OS user and use db2look to duplicate all table structures to another schema
Steps:
1. Create OS user user2
login as root, run commands below:
useradd -c 'DB2 account for user2' -m user2
passwd user2
chage user2 (to change maximum password expiry to 99999 if you have defined default expiry days)
su - db2inst1
cd /db2/db2inst1/db/DB1 (go to the place where all the tablespace directory resides if any)
mkdir tb_user2
2. use db2 control center or command line to create tablespace for user2
preparing the following content for file create_tablespace.sql
# more create_tablespace.sql
--please login as root to create user and assign password first before creating tablespace
CONNECT TO DB1;
CREATE REGULAR TABLESPACE TB_USER2 PAGESIZE 16 K MANAGED BY SYSTEM USING ('/db2/db2inst1/db/DB1/tb_user2' ) EXTENTSIZE 8 OVERHEAD 10.67 PREFETCHSIZE 8 TRANSFERRATE 0.04 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
GRANT CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE TO USER user2;
GRANT USE OF TABLESPACE TB_USER2 TO USER user2;
CONNECT RESET;
su - db2inst1
db2 -tvf create_tablespace.sql -l create_tablespace.sql.log -s
3. use db2look to duplicate schema objects structures
db2look -d db1 -e -z user1 > user1.sql
vi user1.sql
%s#USER1#USER2#g
cat user1.sql | grep -i tb_ | grep -v tb_user1 (to check any other tablespace the user1 objects is residing in)
vi user1.sql
search 'DDL Statements for foreign keys' to separate file into 2 files as user1-1.sql and user1-2.sql so that you can import data into all tables without constrains, that will be faster if you can make sure all the data is in line with constrain already.
4. user db2 control center to give permission for tables and views for user2
open db2 control center - all databases, locate the db1, go to 'User and Group Objects' - 'DB Users', right click on user 'user2' - change, go to 'table' column to add all tables for user2 schema then give 'yes' to all privileges except for 'control'. As well as views etc
How to online/offline backup and restore DB2 database
Jephe Wu - http://linuxtechres.blogspot.com
Environment: Linux server and IBM db2 database, backup database on one server, then restore it to another server
Objective: backup and restore db2 database in both online and offline mode
1. online backup script by cronjob
[db2inst1@db1 ~]$ more /db2log/db2inst1/scripts/monthly_backup.sh
#!/bin/sh
export PATH=/sbin:/usr/sbin:/bin:/usr/bin
. /db2/db2inst1/sqllib/db2profile
db2 "BACKUP DATABASE DB1 ONLINE TO \"/db2log/db2inst1/DB1/backups\" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING" > /tmp/db2monthlybackup.log
sync;sync;sync;sleep 10
echo "running ls -lart /db2log/db2inst1/DB1/backups/*.001" >> /tmp/db2monthlybackup.log
ls -lart /db2log/db2inst1/DB1/backups/*.001 >> /tmp/db2monthlybackup.log
mutt -s "Monthly db2 online backup with logs for DB1 database" jephe1@domain.com jephe2@domain.com < /tmp/db2monthlybackup.log
2. restore online full database backup file with logs from another server
assuming we are going to restore from db1 to db1dr
a. restore to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"
b. restore log files to specified logtarget folder from backup image itself
nice -n 19 "restore db DB1 logs from /db2/db2inst1/backup into DB1DR logtarget /db2/db2inst1/db/DB1/logs"
c. restore to the end of the logs under /db2/db2inst1/db/DB1/logs and complete
db2 "rollforward database db1dr to end of logs and complete overflow log path (\"/data/db2log/DB1/logs\")"
d. restore to the end of the logs under /db2/db2inst1/db/DB1/logs but don't complete
db2 "rollforward database db1dr to end of logs overflow log path (\"/data/db2log/DB1/logs\")"
note: if the the number of files is big, you can use the following command to check how many files left waiting for process:
watch "ls -lut | grep -v 'May 29'| wc -l"
if the today' date is May 29.
e. if you need to restore to the specified date
you need to copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2008-09-25-11.00.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"
f. query database last transaction log date and pending status
db2 "rollforward database db1dr query status"
g. other commands such as
db2 rollforward database dbname complete
h. backup db2 configuration information
db2 get dbm cfg
db2 get db cfg for db1
Part II - offline backup and restore
1. backup (stop db2 server first)
db2 backup database db1 to .
note: offline backup database db1 to the current folder
If it's online backup, use 'db2 backup database db1 online to .'
2. restore
db2 "drop database db1"
db2 "restore database db1 from /home/db2inst1/db1_offline_backup/"
note: you need to put the backup file to /home/db2inst1/db1_offline_backup first
useradd user1;passwd user1
2. assign permission to user1
db2 connect to db1dr
db2 "grant createtab,connect,implicit_schema on database to user user1"
3. test connection for user1
db2 connect to db1dr user user1
How to install a db2 database server and apply patches
Jephe Wu - http://linuxtechres.blogspot.com
Objective: install IBM db2 version 8 on CentOS 4.4 server and apply patches
Environment: CentOS 4, IBM db2
Total 5 parts
Part I - GUI Installation method
Steps:
1. get the db2 installation file and patch file
2. extract these 2 tar file under /root/db2install directory
3. ssh -X serverip (from a Linux machine X windows )
4. run command under /root/db2install
./db2setup
5. in preparing db2 tools catalog part, choose 'use a local database'
in 'set up the administration contact list', you might want to disable notification
in 'specify a contact for health monitor notification' choose 'defer this task until after installation is complete'
6. apply patches
run command './installfixpak -y' to apply patches
7. set parameters
$ db2set (to display current settings)
$ db2set DB2AUTOSTART=YES
$ db2set DB2COMM=TCPIP
$ db2 update database manager configuration using svcename 50000
$ db2 update database manager configuration using diaglevel 4
8. Install license
login as root
cd /opt/IBM/db2/V8.1/adm
./db2licm -a 331_WSUE_LNX26_32_NLV/db2/license/db2wsue.lic
9. start up db2 database
su - db2inst1 ; db2start
Part II - CLI Installation method
Steps:
1. Install original DB2 package
# cd /usr/local/src
# cd db2srcfiles/
# cd 331_WSUE_LNX26_32_NLV/
# ./db2_install
if encounter some error regarding “libstdc++.so.5” not found, download compat-libstdc++-8-3.3.4.2.i386.rpm and install.
# db2level
2. Add groups and users and set passwords
#groupadd -g 101 dasadm1
#groupadd -g 102 db2grp1
#groupadd -g 103 db2fgrp1
# useradd -c "DB2 Admin" -d /db2/dasusr1 -g dasadm1 -m dasusr1
# useradd -c "DB2 Instance User" -d /db2/db2inst1 -g db2grp1 -m db2inst1
# useradd -c "DB2 Fenced User" -d /db2/db2fenc1 -g db2fgrp1 -m db2fenc1
# passwd db2inst1
# passwd db2fenc1
# passwd dasusr1
3. Post-install configuration
# cd /opt/IBM/db2/V8.1/instance/
# ./dascrt -u dasusr1 (create db2 administrator server)
# cd ../adm
# ./db2licm -a /usr/local/src/db2srcfiles/331_WSUE_LNX26_32_NLV/db2/license/db2wsue.lic
# cd ..
# cd instance/
# ./db2icrt -a SERVER_ENCRYPT -u db2fenc1 db2inst1 (create instance)
# su - db2inst1
# mkdir -p /db2log/db2inst1/logs
# chown db2inst1:db2grp1 -R /db2log/db2inst1/
# cd /db2log
# su – db2inst1
$ db2set
$ db2set DB2AUTOSTART=YES
$ db2set DB2COMM=TCPIP
$ db2 update database manager configuration using svcename 50000
$ db2 update database manager configuration using diaglevel 4
$ db2 terminate
$ db2start
4. create database
xterm
xhost +
export DISPLAY=:0
su – db2inst1
cd /db2/db2inst1/
mkdir –p db/db1
db2cc &
5. post-configuration of creating database
* add the following to /db2/db2inst1/.bashrc
db2set DB2LINUXAIO=TRUE
db2set DB2_SCATTERED_IO=ON
db2set DB2COMM=tcpip
db2set DB2AUTOSTART=YES
db2set DB2_LGPAGE_BP=YES (dangerous, can cause ‘shared memory cannot be allocated, referring to http://tldp.org/HOWTO/DB2-HOWTO/kernels.html for how to enable it, it requires some additional steps, not just enable it)
*configuring database logging (right click database name, choose ‘configuring database logging’)
change circular logging to archive logging, and choose ‘manual archive log file handling’
change log patch to /db2log/db2inst1/db1/logs
*check logretention
right click database, configure parameter, make sure logretention is set to recovery ( by default should be )
*configuring db2inst1 cronjob to archive log every hour as follows:
0 * * * * /db2log/db2inst1/scripts/archive.sh
archive.sh
#!/bin/sh
. /db2/db2inst1/sqllib/db2profile
for DB in db1
do
db2 archive log for db $DB
done
*right click the database in control center, configure parameters
change LOGSECOND(-1) and userexit(yes) to make the transaction log infinite
change LOGFILSIZ LOGPRIMARY APP_CTL_HEAP_SZ APPLHEAPSZ BUFFPAGE DBHEAP LOGBUFZ if necessary
Part III - Uninstalling DB2 database
Note: steps 1 and 2 might cause to delete the whole /db2/db2inst1 directory which might not be your intention. So, be careful to do them.
1.Remove the DAS by issuing the /opt/IBM/db2/V8.1/instance/dasdrop command as root.
2.Remove the instance by issuing the /opt/IBM/db2/V8.1/instance/db2idrop db2inst1 command
3. Run the db2_deinstall command as root.
4.Remove the users that were created.
userdel -r db2inst1
userdel -r dasusr1
userdel -r db2fenc1
groupdel dasadm1
groupdel db2grp1
groupdel db2fgrp1
rm –fr /var/db2
rm –f /tmp/db2* (and other files related to DB2)
vi /etc/inittab (remove the last line)
5. (If you created different user names during installation, substitute as necessary.)
6. Optionally, you can remove IBM's version of the Java 2 package that db2 installed.
rpm -e IBMJava2-SDK
Part IV - Installing db2 fix pack for live database
Su – root
Su - instancename
. $HOME/sqllib/db2profile
db2 force applications all
db2 terminate
db2stop
db2licd –end (run at each physical node)
exit
su – dasusr1
db2admin stop
exit
login as root
cd /opt/IBM/db2/V8.1/bin
./db2fmcu –d
su – dasusr1
/db2/dasusr1/das/bin/db2fm –i dasusr1 –D
su – db2inst1
/db2/db2inst1/sqllib/bin/ipclean
./installFixPack –y
login as root still
/opt/IBM/db2/V8.1/bin/instance/db2iupdt db2inst1
/opt/IBM/db2/V8.1/bin/instance/dasupdt dasusr1
su – db2inst1
db2start
Part V - Appendix
a. some commands
# list db directory
# list node directory
# catalog tcpip node db2 remote 10.0.0.1server 59000
# catalog database db1 as db1alias at node db2
IBM db2 lock wait analysis
Objective: When db2 lock wait happens anytime, get the name of locked tables and save the statements
Environment: CentOS 5 and DB2 V9.1
Scripts and cronjobs:
# check lock wait process
*/5 * * * * /db2log/scripts/checklockwait.sh > /dev/null 2>&1
# more /db2log/scripts/checklockwait.sh
#!/bin/sh
DATE=`date +%Y-%m-%d`
export PATH=/db2/db2inst1/sqllib/bin:/usr/bin:/bin
. /db2/db2inst1/sqllib/db2profile
rm -f /tmp/lock-wait /tmp/lockid /tmp/lockdetails /tmp/lockemail /tmp/statements.txt /tmp/statements.zip
sync
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
sleep 15
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
note: if the lock wait remains after 15 seconds, consider it as real lock wait as sometimes the application will hold on tables for a while.
if [ -s /tmp/lock-wait ];then
cat /tmp/lock-wait | awk '{print $3}' > /tmp/lockid
while read line
do
db2 get snapshot for locks for application agentid $line >> /tmp/lockdetails
DBNAME=`db2 list application | awk -v APPHL="$line" '$3==APPHL {print $5}'`
db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications >> /tmp/lockdetails
db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications -dynamic -repeat 20 1 > /tmp/statements.txt
done < /tmp/lockid
cat /tmp/lock-wait /tmp/lockdetails > /tmp/lockemail
zip /tmp/statements.zip /tmp/statements.txt
mutt -s "Lock-wait found on database server,please take action" -a /tmp/statements.zip jephe.wu@domain.com < /tmp/lockemail
fi
Notes:
1. found out while tablespace and tables according to their IDs
SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID = 2 AND TABLEID = 6
Reference:
1. Analyzing lockwait situations in DB2 for Linux, UNIX, and Windows -
http://www.ibm.com/developerworks/data/library/techarticle/dm-0707fechner/index.html
note: please refer to above URL to get the exact statements when lock wait happens. Which statement is blocking.
How to setup a tomcat server with JDK and DB2 runtime client
Jephe Wu - http://linuxtechres.blogspot.com
Objective: Preparing a Linux db2 client environment with JDK and Tomcat
Environment: CentOS 5.4, IBM DB2 V8.1 JDK 1.4.2 and Tomcat 4.1
Steps:
1. Preparing JDK environment:
cd /usr/local
./j2sdk-1.4.2xxx.bin
ln -sf j2sdk1.4.2xxx jdk
Put the following to /etc/profile.d/java.sh
export JAVA_HOME=/usr/local/jdk
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=$JAVA_HOME/lib
then run Chmod +x java.sh
2. Preparing Tomcat environment:
# cd /usr/local/
# tar xvpfz /usr/local/src/jakarta-tomcat-4.1.31.tar.gz
# ln –sf jakarta-tomcat-4.1.31 tomcat
Add tomcat user and group
# groupadd tomcat
# useradd –g tomcat –c “Tomcat User” –d /usr/local/tomcat tomcat
# chown –R tomcat:tomcat Jakarta-tomcat-4.1.31
# chown tomcat:tomcat tomcat
Put the following to /usr/local/tomcat/.bash_profile and give it 755 permission
CATALINA_HOME=/usr/local/tomcat
LD_LIBRARY_PATH=/home/db2inst1/sqllib/lib
LIBPATH=/home/db2inst1/sqlib/lib
DB2INSTANCE=db2inst1
export CATALINA_HOME DB2INSTANCE LD_LIBRARY_PATH LIBPATH
Modify catalina.sh, to add this:
JAVA_OPTS='-Xmx1024m -XX:+AggressiveHeap' at the top of the file
use cronolog to auto rotate log daily
[jephe@app tomcat]$ grep -A 3 -B 3 cronolog bin/catalina.sh
-Dcatalina.base="$CATALINA_BASE" \
-Dcatalina.home="$CATALINA_HOME" \
-Djava.io.tmpdir="$CATALINA_TMPDIR" \
org.apache.catalina.startup.Bootstrap "$@" start | /usr/local/sbin/cronolog "$CATALINA_BASE"/logs/%Y-%m-%d.catalina.out \
>> /dev/null 2>&1 &
if [ ! -z "$CATALINA_PID" ]; then
echo $! > $CATALINA_PID
fi
fi
note: you can vi bin/catalina.sh , then search for 'stop' string, before the following line , you can add above cronolog filter
elif [ "$1" = "stop" ] ; then
3. Preparing DB2 Client
download IBM DB2 runtime client from www.ibm.com, login as root to install runtime client software
# cd /root
# tar xvf FP8_M00099.tar
# cd rtcl
#./db2_install (db2setup needs GUI , so use db2_install instead), it will install all rpms to /opt/IBM/ directory.
create instance (CLI)
# cd /opt/ibm/db2/V8.1/instance
# groupadd db2grp1
# useradd –c ‘DB2 Instance User’ –g db2grp1 –m db2inst1
# ./db2icrt –s client db2inst1 (important, even installation of rtcl sometimes created /home/db2inst1/sqllibxx for you, rename that, run this command as root)
# su - db2inst1
# db2 catalog tcpip node db1 remote 10.0.3.2 server 50000
note: node name cannot use -, _ is allowed
# db2 catalog database DB1 [ as DB1ALIAS ] at node db1
# db2 list db directory
# db2 list node directory
# db2 connect to DB1ALIAS user jephe using password
# db2 uncatalog node db1
# db2 uncatalog db DB1
Set environment for db2inst1 user
Append the following into /home/db2inst1/.bash_profile
. /home/db2inst1/sqllib/db2profile
note: testing db2 runtime client first before using tomcat application:
db2 connect to db1 user schemanmame
db2 list tables for all
if you encounter errors like SQL0805N package "NULLID.SQLxxxxxxxxx" was not found. try to run 'db2 ? SQL0805N' to follow the suggestion below to bind.
db2 bind @db2ubind.1st blocking all grant public 4. Setup tomcat again after finishing db2 client setup
Copy connector over
# cd /opt/IBM/db2/V8.1/java
# cp –i db2java.zip /usr/local/tomcat/common/lib/db2java.jar
note: you have to copy db2java.zip from db2 runtime client to above tomcat folder which is from tomcat server itself
# chown tomcat:tomcat /usr/local/tomcat/common/lib/db2java.jar
5. Preparing Firewall
Allow port 8080, 8443 and 8009 in /etc/sysconfig/iptables
-A RH-Firewall-1-INPUT –m state –state NEW –m tcp –p tcp –dport 8080 –j ACCEPT
-A RH-Firewall-1-INPUT –m state –state NEW –m tcp –p tcp –dport 8443 –j ACCEPT
-A RH-Firewall-1-INPUT –m state –state NEW –m tcp –p tcp –dport 8009–j ACCEPT
# service iptables restart
6. Make tomcat listening on port 80
This section is referred from http://www.klawitter.de/tomcat80.html (How to run Tomcat on Port 80)
There are a few ways to make tomcat to be listening on port 80, you can run tomcat as root which is not recommended, anther way is to remain tomcat to listen on port 8080, and use iptables to forward port 80 request to tomcat:
steps:
iptables -t nat -A PREROUTING -d your hostname -p tcp --dport 80 -j REDIRECT --to-ports 8080iptables-save > /etc/sysconfig/iptables
chkconfig iptables on
Under some circumstances, the HttpConenctor class reports the original port back to the client. Further requests will continue with that port (which is not the desired effect and might even be blocked by your firewall).
Besides switching to a more contemporary Connector like CoyoteConenctor (recommended), you can circumvent that problem by adding a proxyPort to the HttpConnector declaration:
<Connector className="org.apache.catalina.connector.http.HttpConnector" port="8080" proxyPort="80" >
7. FAQ
a. if testing db2 connection got error like 'SQL0805N Package dc2j.NULLID.SQLC2D01.4141414141350 not found.', you can solve it by binding:
login as db2inst1 on db2 client / tomcat server, run:
db2 connect to db1 user db2inst1 (must login as db2inst1, not user)
cd sqllib/bnd
db2 bind @db2cli.lst
db2 bind @db2bind.lst (may not have this filename, then just ignore this line)
How to use db2move and db2look to duplicate IBM DB2 database
Objective: use db2 control center to create another testing database and tablespace, then use db2look and db2move to export then import the production database data into this testing database.
Environment: RHEL5, db2 Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack "3". Database name is DB1, the os and schema user name is user1
Steps:
1. Install same version of IBM db2 database on testing server
note: Setup database runtime client side parameters, such as db2 node directory and db directory
db2 ? catalog node
db2 ? catalog db
db2 catalog tcpip node db1 remote 10.0.0.10 server 50000
db2 catalog db db1 at node db1
db2 list node directory
db2 list db directory
2. Create a new database called DB1 also then create tablespace tb_user1 and OS user user1, please refer to the article http://linuxtechres.blogspot.com/2010/01/how-to-setup-new-schema-in-db2-database.html
3. Use db2look to generate DDL statements for later use
db2look -d db1 -e -z user1 -o user1.sql
Note: for db2look, you might need to separate the file into 2 files called db2look-1.sql and db2look-2.sql. db2look-2.sql contains those ‘adding constrain’ statements.
Also take note the schema name and tablespace name might need to be changed also.
4. Preparing table and view granting script
cat db2look.sql | grep 'CREATE TABLE' | awk -F\" '{print $4}' > tablelist
cat db2look.sql | grep 'CREATE VIEW' | awk '{print $3}' | sed -e 's#(.*##g' > viewlist
after that, generate the 2 files which content such as this:
grant select,insert,update,delete,alter,index,references on table STUDENT to user1 ;
and this:
grant select,insert,update,delete on to user1 ;
5. use db2move to export the schema
Run the following db2move command on production database server:
db2move db1 export -sn user1
6. Run the first part of db2look-1.sql to create tables and views first
7. Use command db2move to import/load the content:
db2move db1 import
note:
1. You can modify db2move.lst file to exclude certain unnecessary big tables.
2. Actually db2move is a wrapper of db2 “import xxxx” or db2 “load xxxx”, you just don’t have to type so many times for importing/loading each table, use db2move instead.
When you use db2move to import db2 version 9 Linux db2move dump to solaris db2 version 8, you might encounter codepage error like this:
Code page option is incompatible with the lobsinfile option
This is because you use db2move ‘import’ option, if you use db2move ‘load’ option, then the problem will be solved.
Search: db2move load import lobsinfile incompatible
Problem encountered during using db2move load to load tables
Search: db2move load –lo replace ,insert
Due to tablespace has no free pages left. During db2move load operation period, it failed with the following errors:
You can use the following command to verify that:
# db2 “list tablespaces show detail”
LOAD: table " ".""
*** ERROR -289. Check message file tab365.msg!
*** SQLCODE: -289 - SQLSTATE: 57011
*** SQL0289N Unable to allocate new pages in table space "". SQLSTATE=57011
Note: this error might also happen during db2look period also:
After failed to load, then you cannot load again, you have to clear it using the following command:
Otherwise, the error message for accessing those fail-loaded tables are :
SQL0668N operation not allowed for reason code “3” on table “xxxx.yyyy”. SQLSTATE=57016
# db2 “load from /dev/null of ixf replace into .tablename [nonrecoverable]”
Regarding how to recover from a failed LOAD operation in DB2, you can refer to article
Recovering from a failed LOAD operation in DB2 for Linux, UNIX and Windows
At http://www.ibm.com/developerworks/data/library/techarticle/0202kline.html
8. After importing data, import the second db2look-2.sql script to enable constrain
9. Test. You can test the connection from client to server from runtime client application server.
db2 connect to db1 user user1
How to setup a new schema in a DB2 database
Objective: Create a new client schema on the existing DB2 database
Environment: RHEL5 and DB2 V9
Steps:
1. create operating system user
[root@db1 ~]# useradd -c 'DB2 account for jephe' -m jephe
[root@db1 ~]# passwd jephe
Changing password for user jephe
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@db1 ~]# chage jephe
Changing the aging information for jephe
Enter the new value, or press ENTER for the default
Minimum Password Age [7]:
Maximum Password Age [90]: 99999
Last Password Change (YYYY-MM-DD) [2010-01-11]:
Password Expiration Warning [7]:
Password Inactive [-1]:
Account Expiration Date (YYYY-MM-DD) [1969-12-31]:
2. create tablespace directory
su - db2inst1
cd /db2/db2inst1/db/DB1 (assuming db2 database directory is /db2/db2inst1/db)
mkdir tb_jephe
3. create database and tablespace using db2 control center
Create database by using control center, you'd better to choose 16k tablespace page size instead of 4k, and choose UTF-8 as codeset
CREATE DATABASE JEPHE AUTOMATIC STORAGE NO ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 16384;
CONNECT TO DB1;
CREATE REGULAR TABLESPACE TB_JEPHE PAGESIZE 16 K MANAGED BY SYSTEM USING ('/db2/db2inst1/db/DB1/tb_jephe' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
CONNECT RESET;
4. add user jephe to tablespace tb_jephe
CONNECT TO DB1;
GRANT CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE TO USER JEPHE;
GRANT USE OF TABLESPACE TB_JEPHE TO USER JEPHE;
CONNECT RESET;
5. use db2look to duplicate schema from the existing ones
db2look -d db1 -e -z existingschemaname -o existingschema.sql
then use vi to batch change the existingschemaname to jephe
6. use db2 control center to assign table and view privileges for the new user
# TABLES
CONNECT TO DB1;
GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES ON TABLE JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET
#VIEWS
CONNECT TO DB1;
GRANT SELECT,INSERT,UPDATE,DELETE ON VIEW JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET
7. login as new client jephe and test
db2 connect to db1 user jephe
db2 "select * from certaintablename"
Run db2 script at the specified time automatically and send log file out
Objective: run db2 script at the specified time automatically and send log file out.
Environment: RHEL5 with db2 V9.0
Script:
- write script as below
#!/bin/sh
export PATH=/db2/db2inst1/sqllib/bin:/usr/bin:/bin
. /db2/db2inst1/sqllib/db2profile
cd /db2/db2inst1/scripts/20091210
FILE=open.sql
db2 connect to db1
db2 set schema = schema1
db2 -tvf $FILE -l ${FILE}.log
db2 terminate
sleep 5
scp ${FILE}.log backup@mon1:/tmp
sleep 5
#copy log file to email server and run email command there
ssh -t backup@mon1 "email -b -s "script result" jephe@domain.com < /tmp/${FILE}.log"
2. make it run as cronjob
#chmod +x /db2/db2inst1/scripts/20091210/open.sh
then crontab -e to add the following to run it at 9am 5 Jan
0 9 5 1 * /db2/db2inst1/scripts/20091210/open.sh > /dev/null 2>&1
note:
1. make sure all your command used in the script are in /usr/bin or /bin, otherwise, put the necessary paths inside the script export line.
2. put open.sql under /db2/db2inst1/scripts/20091210/
3. some tips - open and close code
update something set code=concat(code,'_DISABLE') where something;
update something set code=replace(code,'_DISABLE','') where something;
Posts
