DB2 simple backup and restore test

Test Platform OS - RHEL5.3


First, backup the production data
force all the application
command - db2 force application all

try to stop and restart db2
command - db2stop
command - db2start

backup the database, using online backup -
command :db2 BACKUP DATABASE dbName USER db2inst1 USING dbPWD ONLINE TO /home/db2inst1/backup

OPTIONAL - DROP BACKUP DB
command - db2 drop database dbName

restore the database use db2 restore
command - db2 RESTORE DATABASE ${BACKUP_DB} FROM ${BACKUP_LOCAL_DIR} TAKEN AT ${TIMESTAMP} INTO ${RESTORE_DB} REPLACE EXISTING

For changing the database name
command -
vi xxx.cfg

Insert the following to xxx.cfg

DB_NAME=test3,test1
DB_PATH=/home/db2inst1
INSTANCE=db2inst1


After that run the relocate db name command - db2relocatedb -f xxx.cfg
*The new DB name have to be same as the backup_DB name*
roll data to end of log
command - db2 rollforward db DBName to end of logs

rollforward stop
command - db2 rollforward db DBName stop

information for MYSQL

SHOW CURRENLY DATABASE
mysql> select database();

SHOW COMMAND FOR CREATE DATABASE
mysql> show create database db_name

SHOW COMMAND FOR CREATE TABLE
mysql> show create table table_name

SHOW TABLE INDEX
mysql> show index from table_name

RUN BATCH

shell> mysql -u user_name -p < batch_name
OR
mysql> source batch_name;
OR
mysql> \. batch_name;

ADD/DROP VALUE IN TABLE

ADD NEW VALUE TO TABLE


either use insert into or load data


test.txt
xxxx YYY 1999-03-30 \N

mysql> LOAD DATA LOCAL INFILE '/path/test.txt' INTO TABLE pet;
mysql> INSERT INTO table_name VALUES ('xxxx','YYY','1999-03-30',NULL);


UPDATE VALUE IN TABLE


mysql> update table_name set value_name=value where value_name=value


REMOVE ALL VALUE TO TABLE


mysql> delete from pet;

REMOVE TABLE
mysql>  drop table table_name;

Mysql command(BASIC)

shell > mysql -h (hostname) -u (user) -p

-p = with password

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.5.23    | 2012-05-06   |
+-----------+--------------+
1 row in set (0.02 sec)

exit the mysql command shell - (ctrl+c / quit)
cancel the current command - (\c)

SHOW EXISTING DATABASE
mysql> show databases;

CHANGE CURRENT USING DATABASE
shell> mysql -u username -p db_name
mysql> user db_name;

CREATE A NEW DATABASE
mysql> create database db_name;

SHOW CURRENT DATABASE OWNED TABLE
mysql> show tables;

CREATE A NEW TABLE
mysql> create table table_name (column1 type, column2 type, column3 type);

TYPE-> VARCHAR(NO); CHAR(NO); INT();FLOAT();DATE; BOOLEAN

SHOW TABLE DESC
mysql> describe table_name;

IBM DB2 run sql script

db2 -tvf script_name.db2 -z script.log

-z -> show log
-f -> use script
-v -> show in detail

IBM DB2 set dbm parameter

List database parameter : db2set -all
set database parameter  : db2set PARA_NAME=XXX
set database parameter to empty : db2set PARA_NAME=

List database cfg parameter : db2 get db cfg
set database cfg parameter  : db2 update db cfg using CFG_NAME XXX

DB2 LOGIN METHOD

Easy Login

     db2 connect to db_name user userID using password
eg. db2 connect to newinst user db2inst1 using 1234

List all applications
     db2 list applications
stop all applications
     db2 force applications all
stop application
     db2 "force application (Appl.)"

select statment
   db2 select * from staff

Command List - IBM DB2 modify instance and database

Command List - IBM DB2 modify instance and database

Run the command in /opt/ibm/db2/V9.7/instance
Create a new instance        : ./db2icrt -u instance_name instance_name
Remove instance*              : ./db2idrop instance_name

Run the command in /opt/ibm/db2/V9.7/binCheck all existing instance* : db2ilist
Check current instance      : db2 get instance
Set current instance            : set db2instance=instance_name

Create new database       : db2 create db db_name
connect to database        : db2 connect to db_name
stop connecting database: db2 terminate
List all database              : db2 list db directory
remove database             : db2 drop db db_name
LIST all table                   :db2 list tables

Create table : db2 create table

check dbm cfg parameter : db2 get dbm cfg
update dbm cfg parameter: db2 update dbm cfg using PARAMETER OPTION

stopall application to stop connection
   db2 force application all
   db2 terminate

Run the command in /opt/ibm/db2/V9.7/admStartup current instance  : db2start
Stop current instance      : db2stop


* Which request user run the command by root