DB2 Catalogue Extraction

Recently migrated DB2 database from Aix to Linux platform: need to tidy up all the server catalogs, the server has AIX and Linux, manual collation is certainly slower cut error, the following script to solve.

Strategy:

1, script to extract the server catalog scripts, cataloging information to save to a file.

2, Ansible distributed to each Linux server, AIX does not have SSH authentication need to be completed manually.

3, ansible batch execution script, extract information to file.

4, ansible pull each server catalog information file to this machine.

5, native Script batch processing, generate CSV format.

The key processing scripts are given below:

Get catalog information to file:

#!/bin/bash#linux platform db_host= ' hostname ' db_ip=$ (ifconfig-a | grep "inet addr" | grep-v "127.0.0.1" | awk ' {print $} ' | awk  -F ': ' {print $} ' who_am= ' WhoAmI ' DB2 list db directory > ${DB_IP}_${WHO_AM}_${DB_HOST}.DBDB2 list node directory > ${db_ip}_${who_am}_${db_host}.node#!/usr/bin/ksh#aix platform db_host= ' hostname ' db_ip= ' ifconfig-a | Sed-n "5,5p" | awk ' {print $} ' who_am= ' WhoAmI ' DB2 list db directory > ${DB_IP}_${WHO_AM}_${DB_HOST}.DBDB2 list node directory > ${ Db_ip}_${who_am}_${db_host}.node

Batch each server Catalog:

#!/bin/bash#bash $ catadir Linux|aix db|node# script runs with three parameters: #1, directory where the catalog information file resides, #2, platform Linux or aix#3, type of catalog to generate, node or dbif [$#-ne 3 ]; Then echo "bash $ catadir linux|aix db|node" Exit 1ficata_dir= "$" os_sys= "$" db_node= "$" os_linux= "Linux" os_aix= "Aix" db= "DB" node= "node" catalog_node= "Catalog_node_ ' date +%f '" catalog_db= "catalog_db_ ' date +%f '" For file in $ (Find ${cata_ DIR}-name "*.${db_node}" | Awk-f '/' {print $ {} ') do #echo filename: $file ip_user= "${file%_*}" db_host= "${ip_user%_*}" db_user= "${ip_us Er#*_} "#linux node if [[" ${os_sys} "x =" ${os_linux} "x &&" ${db_node} "x =" ${node} "x]]; Then Cat ${cata_dir}/${file} | Egrep ' Node name|entry type| protocol| hostname| Service Name ' | awk ' {print $NF} ' | awk ' {printf $ ', '} nr%5==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= ' $db _user ' >> ${catalog_node} f I #aix node if [["${os_sys}" x = "${os_aix}" x && "${db_node}" x = "${node}" x]]; Then Cat ${cata_dir}/${file} | GREP-E "Node name"-E"Directory Entry Type"-E "protocol"-E "host name"-E "service name" | awk ' {print $NF} ' | awk ' {printf $ ', '} nr%5==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= ' $db _user ' >> ${catalog_node} f I #linux db if [["${os_sys}" x = "${os_linux}" x && "${db_node}" x = "${db}" x]; Then #间接db cat ${cata_dir}/${file} | Egrep ' alias| Database name|entry type|partition ' | awk ' {print $NF} ' | Grep-a 1-b 2 "Indirect" | Grep-v "\-\-" | awk ' {printf $ ', '} nr%2==0 && nr%4!=0 {printf ', '} nr%4==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= "$db _user" >> ${catalog_db} #远程db cat ${cata_dir}/${file} | Egrep ' alias| Database name| Node name|entry type|partition ' | Grep-a 1-b 3 "Remote" | Grep-v "\-\-" | awk ' {print $NF} ' |awk ' {printf $ ', '} nr%5==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= ' $db _user ' >> $ {catalog_db} fi #aix db if [["${os_sys}" x = "${os_aix}" x && "${db_node}" x = "${db}" x]]; Then #间接db cat ${caTa_dir}/${file} | Egrep ' Database alias | database name | directory entry type | directory database partition number ' | awk ' {print $NF} ' | Grep-a 1-b 2 "indirect" | Grep-v "\-\-" | awk ' {printf $ ', '} nr%2==0 && nr%4!=0 {printf ', '} nr%4==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= "$db _user" >> ${catalog_db} #远程db cat ${cata_dir}/${file} | Egrep ' Database aliases | database names | node name | directory Entry type | directory database partition number ' | GREP-A 1-b 3 "remote" | Grep-v "\-\-" | awk ' {print $NF} ' |awk ' {printf $ ', '} nr%5==0 {print db_host ', ' Db_user} ' db_host= ' $db _host ' db_user= ' $db _user ' >> $ {catalog_db} fidoneexit $?

　　

DB2 Catalogue Extraction