Keep it Quick: DB2 data copy scripts

The scripts attached can synchronize the content of similar tables hosted in different databases e.g. synchronize content between a production and staging databases. The scripts are not performing complete database backup and restore but work on supplied tables list only and internally it uses DB2 export and load commands.

Deployment:
The scripts can be started from a user with proper db2 profile settings e.g. db2inst1. For example, they can be deployed under /home/db2inst1/scripts.

To call the scripts you need to run the following command:
$/home/db2inst1/scripts/dataCopy.sh /home/db2inst1/scripts

Editing is required in one file only which is loadSettings.sh to define source db, target db, list of tables and many other options explained in detail in supplied file.

To automate the script using crontab, you can add do the following as a sample:
1- $crontab -e
2-Add the following lines
0 10 * * 0 /home/db2inst1/scripts/dataCopy.sh /home/db2inst1/scripts | mail myemail@ibm.com -s "Data replication status"
3-Save

This will run the script every Sunday at 10 AM. It takes few minutes to complete even with millions of records.

Important:

File names are case sensitive so save them on your UNIX as is without changing names
Save all files in the same folder
DB2 data replication can be used to do the same job in a more elegant fashion and with much more options. The scripts below are intended as a quick alternative that requires minimum skills (and much less features of course).

loadSettings.sh
#!/usr/bin/ksh
#Author Ahmed Fadel (fadela@eg.ibm.com)
#version 1.1
#1.0 initial release
#1.1 Define variable for DB2 home directory to load environment propely while executing from a cron job & define more error messages

#DB2 home directory
export DB2HOME=/home/ccsp

#Data format ixf or wsf or del
export EXPORT_LOAD_FORMAT=ixf

#Load mode setting
#Possible values are REPLACE or TERMINATE or INSERT or RESTART
export LOAD_MODE=REPLACE

#Load options. The default options supplied with terminate any connection on table.
export LOAD_OPTIONS="INDEXING MODE REBUILD SET INTEGRITY PENDING CASCADE IMMEDIATE LOCK WITH FORCE"

#The default cycle is to backup target tables, export data from source tables ans then load the new data data to target tabls. The three steps can be turned or and off from parameters below.
export DATA_BACKUP_ENABLED=yes
export DATA_EXPORT_ENABLED=yes
export DATA_LOAD_ENABLED=yes

#Source database connection information
export SOURCE_DB_NAME=sample1
export SOURCE_DB_USERNAME=db2inst1
export SOURCE_DB_PASSWORD=mypass

#Target database connection information
export TARGET_DB_NAME=sample2
export TARGET_DB_USERNAME=db2inst1
export TARGET_DB_PASSWORD=mypass

#Number of tables to be defined and upon which process will apply
export TABLES_NUMBER=4

#For each table to be uploaded you need to define the following:
#SOURCE table name
#SOURCE table where condition when you need to extract data based on certain condition. Must be surrounded in double quotes.
#Target table name which is useful when table names are different.
#Target table where condition which controls the backup process and will only backup data based on supplied where condition
#Target table set integrity which is either yes or no. If this table had got any forgien key relation with other tables then this value must be set to yes. If not set to Yes, the target table will be inactive after data load. One ther thingfor tables with relations, the parent table needs to be listed before child tables so that set integrity works in right order.

#The list below is a dummy sample

export SOURCE_TABLE_1=client
export SOURCE_TABLE_WHERE_CONDITION_1=
export TARGET_TABLE_1=client
export TARGET_TABLE_WHERE_CONDITION_1=
export TARGET_TABLE_SET_INTEGRITY_1=yes

export SOURCE_TABLE_2=customer_number
export SOURCE_TABLE_WHERE_CONDITION_2=
export TARGET_TABLE_2=customer_number
export TARGET_TABLE_WHERE_CONDITION_2=
export TARGET_TABLE_SET_INTEGRITY_2=yes

export SOURCE_TABLE_3=client_hierarchy
export SOURCE_TABLE_WHERE_CONDITION_3=
export TARGET_TABLE_3=client_hierarchy
export TARGET_TABLE_WHERE_CONDITION_3=
export TARGET_TABLE_SET_INTEGRITY_3=no

export SOURCE_TABLE_4=tasks
export SOURCE_TABLE_WHERE_CONDITION_4="WHERE TASK_NAME LIKE ('CLIENT_HIERARCHY%') OR TASK_NAME = 'CLIENT_BUILDER'"
export TARGET_TABLE_4=ccsp_tasks
export TARGET_TABLE_WHERE_CONDITION_4=
export TARGET_TABLE_SET_INTEGRITY_4=no

export EXIT_ERROR="Script terminated with error"
export EXIT_SUCCESS="Script completed successfully"
export ENVLOAD_SUCCESS="Script settings loaded successfully"
export ENVLOAD_ERROR="Script settings wasn't found"
export DB2LOAD_SUCCESS="DB2 profile loaded successfully"
export DB2LOAD_ERROR="DB2 profile wasn't found"
export UNEXPECTED="Unexpected error in script"
export DBCONNECT="Connecting to database"
export LOAD_DATA="Data loading for target tables in progress"
export BACKUP_DATA="Data backup for target tables in progress"
export EXPORT_DATA="Data export from source tables in progress"
export BACKUP_NOT_ENABLED="Backup process will be skipped"
export EXPORT_NOT_ENABLED="Export process will be skipped. The source files must be already available"
export LOAD_NOT_ENABLED="Load process will be skipped"
export BACKUP_SUCCESS="Backup completed successfully"
export LOAD_SUCCESS="Load complete successfully"
export BACKUP_ERROR="Backup terminated with error"
export LOAD_ERROR="Load terminated with error"

dataExport.sh

#!/usr/bin/ksh
#Author Ahmed Fadel (fadela@eg.ibm.com)
#Version 1.0

if [ "$1" = "EXPORT" ]
then
    DB=$SOURCE_DB_NAME
    USER=$SOURCE_DB_USERNAME
    PASSWORD=$SOURCE_DB_PASSWORD
    OPERATION_MESSAGE=$EXPORT_DATA
    OPERATION=Export

    TABLE_PARAM=SOURCE_TABLE
    ext=
elif [ "$1" = "BACKUP" ]
then
        DB=$TARGET_DB_NAME
        USER=$TARGET_DB_USERNAME
        PASSWORD=$TARGET_DB_PASSWORD
    OPERATION_MESSAGE=$BACKUP_DATA
    OPERATION=Backup

    TABLE_PARAM=TARGET_TABLE
    ext=_backup
else
    echo $UNEXPECTED
    exit 1
fi

echo "$DBCONNECT $DB"
db2 connect to $DB user $USER using $PASSWORD

echo $OPERATION_MESSAGE

count=1
limit=`expr $TABLES_NUMBER + 1`
type=`echo $EXPORT_LOAD_FORMAT | tr "[:lower:]" "[:upper:]" `

while [ $count -lt $limit ]
do
    table=`eval echo '$'$TABLE_PARAM'_'$count `
    condition=`eval echo '$'$TABLE_PARAM'_WHERE_CONDITION_'$count `

    echo "$OPERATION table "$table to ${table}$ext.$type
    db2 "export to $WORK/${table}$ext.$type of $type select * from $table $condition WITH UR "

    if [ $? != 0 ]
    then
        db2 connect reset
              exit 2
    fi
count=`expr $count + 1`
done

db2 connect reset

exit 0

dataCopy.sh

#!/usr/bin/ksh
#Author Ahmed Fadel (fadela@eg.ibm.com)
#version 1.1
#1.0 initial release
#1.1 Load DB2 profile for proper DB2 commands execution & check if an error occured in DB2 and general environment settings loading

export WORK="$1/"

. "$WORK/loadSettings.sh"
    if [ "$?" = "0" ]
    then
           echo $ENVLOAD_SUCCESS
    else
           echo $ENVLOAD_ERROR
           echo $EXIT_ERROR
           exit 1
    fi

. "$DB2HOME/sqllib/db2profile"
    if [ "$?" = "0" ]
    then
           echo $DB2LOAD_SUCCESS
    else
           echo $DB2LOAD_ERROR
           echo $EXIT_ERROR
           exit 1
    fi

backup=`echo ${DATA_BACKUP_ENABLED} | tr "[:lower:]" "[:upper:]" `
export=`echo ${DATA_EXPORT_ENABLED} | tr "[:lower:]" "[:upper:]" `
load=`echo ${DATA_LOAD_ENABLED} | tr "[:lower:]" "[:upper:]" `

if [ "$backup" = "YES" ]
then
    "$WORK/dataExport.sh" BACKUP
    if [ "$?" = "0" ]
    then
       echo $BACKUP_SUCCESS
    else
       echo $BACKUP_ERROR
       echo $EXIT_ERROR
       exit 2
    fi
else
    echo $BACKUP_NOT_ENABLED
fi

if [ "$export" = "YES" ]
then
    "$WORK/dataExport.sh" EXPORT
    if [ "$?" = "0" ]
    then
           echo $EXPORT_SUCCESS
    else
           echo $EXPORT_ERROR
           echo $EXIT_ERROR
           exit 3
    fi
else
    echo $EXPORT_NOT_ENABLED
fi

if [ "$load" = "YES" ]
then
    "$WORK/dataLoad.sh"
    if [ "$?" = "0" ]
    then
           echo $LOAD_SUCCESS
    else
           echo $LOAD_ERROR
              echo $EXIT_ERROR
           exit 4
    fi
else
    echo $LOAD_NOT_ENABLED
fi

echo $EXIT_SUCCESS
exit 0

dataLoad.sh

#!/usr/bin/ksh
#Author Ahmed Fadel (fadela@eg.ibm.com)
#Version 1.0

DB=$TARGET_DB_NAME
USER=$TARGET_DB_USERNAME
PASSWORD=$TARGET_DB_PASSWORD
OPERATION_MESSAGE=$LOAD_DATA
OPERATION=Load

echo "$DBCONNECT "$DB
db2 connect to $DB user $USER using $PASSWORD

echo $OPERATION_MESSAGE

count=1
limit=`expr $TABLES_NUMBER + 1`
type=`echo $EXPORT_LOAD_FORMAT | tr "[:lower:]" "[:upper:]" `

while [ $count -lt $limit ]
do
        table=`eval echo '$TARGET_TABLE_'$count `
    filename=`eval echo '$SOURCE_TABLE_'$count `
        echo "$OPERATION table "$table from ${table}$ext.$type
    db2 "load from $WORK/${filename}.$type of $type $LOAD_MODE into $table $LOAD_OPTIONS "
        if [ "$?" != "0" ]
        then
                db2 connect reset
                exit 2
        fi
count=`expr $count + 1`
done

count=1
OPERATION="Set integrity"
while [ $count -lt $limit ]
do
        table=`eval echo '$TARGET_TABLE_'$count `
    setIntegrity=`eval echo '$TARGET_TABLE_SET_INTEGRITY_'$count | tr "[:lower:]" "[:upper:]" `
    if [ "$setIntegrity" = "YES" ]
    then
           echo "$OPERATION for table "$table
          db2 "set integrity for $table immediate checked"
       if [ "$?" != "0" ]
           then
                   db2 connect reset
           exit 2
           fi
    fi
count=`expr $count + 1`
done

db2 connect reset

exit 0

Keep it Quick

Wednesday, 8 June 2011

DB2 data copy scripts

No comments:

Post a Comment