DB2数据库导出文件命令 列分割符0x03 默认封闭符号为双引号
db2 “export to ./tables.del of del modified by COLDEL0x03 select * from table"
达梦数据库导出文件命令 导出模式 兼容db2模式
dmfldr username/passwd@DMDSC control='\table.ctl\' mode==\'OUT\' DB2_MODE=TRUE PRIORITY_ENCLOSE=TRUE escape=\'\"\' data=\'table.del\' sql="select * from table"
达梦数据库需要添加控制文件才能导出 table.ctl
LOAD DATA
INFILE * STR X '0A' REPLACE
INFO TABLE TABLENAME
FIELDS X '03'
(
column1,
column2
)
如果需要导出大量表的情况需要对控制文件及导出命令编写 费时费力,而且数据库中有些类型DB2中默认不需要添加封闭符(INTEGER NUMRIC DATA DECIMAL SMALLINT等),为此编写此脚本用来方便开发
export LC_ALL="en_US"
export LANG="en_US"
##schname需要修改成默认的schema
genCtlFile() {
schtablename=$1
schname="默认schema"
##SYS.SYSCLOUMNS schema.tablename
if [[ "$schtablename" == *"."* ]]
then
schname=`echo "$schtablename" | awk -F. '{print $1}'`
tabname=`echo "$schtablename" | awk -F. '{print $2}'`
else
tablename=$schtablename
fi
array=`disql -S SYSDBA/SYSDBA@DMDSC -C "set pages 0 long 50000" -E sql=" select replace(WM_CONCAT(con),',',' ') from (select (col.NAME||'|'||TYPE$||'|'||col.colid+1) as con from all_objects tab inner join SYSCOLUMNS col on tab.OBJECT_ID=col.ID where OBJECT_TYPE='TABLE' AND OWNER='$schname' and object_name='$tabname' order by col.colid)"`
if [ "$array" != "NULL" ] && [ "$array" != "" ]
then
filename="$schname"_$tabname.ctl
##创建并清空文件
touch $filename
> $filename
read -r arr <<< "$array"
size=`echo ${#array[@]}`
echo "LOAD DATA
INFILE * STR X 'OA' REPLACE
INTO TABLE $tabname
FIELDS X '03'
(" >> $filename
SQLSTR=""
for line in ${arr[@]}
do
colname=`echo "$line" | awk -F'|' '{print $1}'`
coltype=`echo "$line" | awk -F'|' '{print $2}'`
ide=`echo "$line" | awk -F'|' '{print $3}'`
case $coltype in
"DECIMAL"|"NUMERIC"|"SMALLINT"|"DATE"|INTEGER)
if [ "$ide" -ge "$size" ]
then
echo "$colname" >> $filename
SQLSTR=$SQLSTR"$colname"
else
echo "$colname," >> $filename
SQLSTR=$SQLSTR"$colname,"
fi
;;
*)
if [ "$ide" -ge "$size" ]
then
echo "$colname OPTIONALLY ENCLOSE BY X '22'" >> $filename
SQLSTR=$SQLSTR"$colname"
else
echo "$colname OPTIONALLY ENCLOSE BY X '22'," >> $filename
SQLSTR=$SQLSTR"$colname,"
fi
;;
esac
done
echo ")" >> $filename
fmfrStr="dmfldr $schname/\$""$schname""PASSWD@DMDSC control=\'$filename\' mode='\OUT\' DB2_MODE=TRUE PRIORITY_ENCLOSE=TRUE escape=\'\\\"""""\' data=\' /""$schname""_$tabname.del\' sql=\"SELECT $SQLSTR FROM $schname.$tabname\""
echo $fmfrStr >> $filename
echo $schtablename"已生成"
else
echo $schtablename"不存在"
fi
}
##ctl 控制文件不需要添加封闭符的类型
##脚本传入参数个数
size1=$#
while [ "0" -eq "0" ]
do
echo "请选择模式 1参数模式 2文本模式 3exit"
read mode
case $mode in
1)
if [ "$size1" -gt "0" ]
then
for line in $@
do
genCtlFile $line
done
else
echo "请输入参数 例: SYS.COLUMNS 或 重新运行脚本 sh Generate_DB2_DM_Ctl.sh SYS.COLUMNS SYS.TABLE1"
read tbname
genCtlFile $tbname
fi
;;
2)
if [ -f ./tables.txt ]
then
while read -r line
do
genCtlFile $line
done < ./tables.txt
else
echo "请在当前目录创建tables.txt,并在文本中添加表名字 :例如
SCHEMAS.TABLE1
SCHEMAS2.TABLE2
"
fi
;;
3)
exit 0
;;
*)
echo "输入有误 请重新选择"
;;
esac
done