数据迁移

DECLARE MYCUR CURSOR FOR SELECT S.ID,S.DATA_MODEL,S.DATA_AREA,S.SRC_ENTITY,S.SRC_ATTR_LOG,S.SRC_ATTR_PHY,S.SRC_ATTR_DEF,S.SRC_DOMAIN,S.SRC_DATATYPE,S.TAR_ENTITY_LOG,S.TAR_ENTITY_PHY,S.TAR_ATTR_LOG,S.TAR_ATTR_PHY,S.TAR_ATTR_DEF,S.TAR_DOMAIN,S.TAR_DATATYPE,S.TRACE_COMMS,S.MAPPING_STS,S.COMMENTS,S.CHK_FLAG,S.VERSION_FLAG,S.BATCH_NO,S.PPN_TSTMP,S.SRC_ENTITY_BKP,S.SRC_ATTR_LOG_BKP,S.MAP_RULE_SET_ID,S.SRC_ENTITY_CODE,S.SRC_ATTR_CODE FROM DSA.N_ETL_M_INFO S
LOAD FROM MYCUR OF CURSOR REPLACE INTO DSA.ETL_M_INFO(ID,DATA_MODEL,DATA_AREA,SRC_ENTITY,SRC_ATTR_LOG,SRC_ATTR_PHY,SRC_ATTR_DEF,SRC_DOMAIN,SRC_DATATYPE,TAR_ENTITY_LOG,TAR_ENTITY_PHY,TAR_ATTR_LOG,TAR_ATTR_PHY,TAR_ATTR_DEF,TAR_DOMAIN,TAR_DATATYPE,TRACE_COMMS,MAPPING_STS,COMMENTS,CHK_FLAG,VERSION_FLAG,BATCH_NO,PPN_TSTMP,SRC_ENTITY_BKP,SRC_ATTR_LOG_BKP,MAP_RULE_SET_ID,SRC_ENTITY_CODE,SRC_ATTR_CODE)

 有自增序列的情况：

DECLARE MYCUR CURSOR FOR SELECT S.ID,S.JOB_TP,S.JOB_TP_DESC FROM ETL.JOB_TP S
LOAD FROM MYCUR OF CURSOR MODIFIED BY IDENTITYOVERRIDE REPLACE INTO ETL.JOB_TP(ID,JOB_TP,JOB_TP_DESC) NONRECOVERABLE

 

#!/bin/bash
#Usage:
#      ./transfer.sh DB User Passwd
#------------------------------------------------------
#Filename:              transfer.sh
#Revision:              1.0
#Date:                  2017/04/21
#Author:                
#Description:        Create load sql   
#Notes:              
#------------------------------------------------------

Usage="Usage: $0 DB User Passwd"
[ $# -ne 0 ] && echo "${Usage}" && exit -1

alias dt='date +%Y-%m-%d" "%H:%M:%S'
shopt -s expand_aliases

DBNAME=DWMM
USER=dainst
PASSWD=dainst

db2 connect to ${DBNAME} user ${USER} using ${PASSWD} > /dev/null 2>&1

[ $? -ne 0 ] && echo "`dt`:Connect to ${DBNAME} failed, Exit! " && exit -1

db2 -x "SELECT TABLE_SCHEMA,TABLE_NAME FROM SYSIBM.TABLES WHERE TABLE_SCHEMA IN ('SMY','ETL') AND TABLE_TYPE='BASE TABLE'" > ./tab.list
echo $?
[ $? -ne 0 ] && echo "`dt`:Get the table list failed, Exit " && exit -1

function generate()
{  
   schema=$1
   tabname=$2
   columns=
   columns_arry=
   length=
   src_string=
   des_string=
  # echo "${schema}.${tabname}"
   db2 connect to ${DBNAME} user ${USER} using ${PASSWD} > /dev/null 2>&1
   [ $? -ne 0 ] && echo "`dt`:Connect to ${DBNAME} failed " && exit -1 
   db2 -x "select name from SYSIBM.SYSCOLUMNS where TBCREATOR='${schema}' and tbname = '${tabname}'  order by COLNO"  > ./column.list
   [ $? -ne 0 ] && echo "`dt`:Get the table ${tabname} column failed" && exit -1   
   columns=`cat ./column.list`
   columns_arry=(${columns})
   length=`echo  ${#columns_arry[*]}`
   for ((i=0; i<${length}; i=i+1))
   do
    if [ ${i} == `expr ${length} - 1 ` ];then 
       src_string=${src_string}S.${columns_arry[${i}]}
       des_string=${des_string}${columns_arry[${i}]}
      # echo ${src_string}
      # echo ${des_string}
    else
       src_string=${src_string}S.${columns_arry[${i}]},
       des_string=${des_string}${columns_arry[${i}]},
    fi
   done
   isidentity=`db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE "IDENTITY"='Y' AND TABSCHEMA='${schema}' AND TABNAME='${tabname}' AND "GENERATED" = 'A'"`
   echo ${isidentity}
   mkdir -p  ./${DBNAME}
   > ./${DBNAME}/${schema}.${tabname}
   src_sql="DECLARE MYCUR CURSOR FOR SELECT "${src_string}" FROM ${schema}.${tabname} S"
   if [ ${isidentity} -eq 1 ];then
    des_sql="LOAD FROM MYCUR OF CURSOR MODIFIED BY IDENTITYOVERRIDE REPLACE INTO ${schema}.N_${tabname}("${des_string}") NONRECOVERABLE"
   else
    des_sql="LOAD FROM MYCUR OF CURSOR REPLACE INTO ${schema}.${tabname}("${des_string}")"
   #echo ${des_sql}
   fi
   > ./${DBNAME}/${schema}.${tabname} 
   echo ${src_sql} >> ./${DBNAME}/${schema}.${tabname}
   echo ${des_sql} >> ./${DBNAME}/${schema}.${tabname}
   #cat ./${DBNAME}/${schema}.${tabname}
}

cat -n  ./tab.list| sort | while read num  schema tabname  null 
do
   echo "${schema},${tabname}"
   #echo " ${num},${schema},${tabname}"
   generate ${schema} ${tabname}
done