一个转化db2 select 语句输出的脚本
最近在db2 下工作，对db2 select 语句的输出排列感到不能容忍，便写个这个小脚本，使select语句的输出象informix isql的form查询那样。#!/usr/bin/ksh
# This script was created by Valentine
# if you prefer informix isql's output format to db2's,
# you can enjoy this small tool!
# Usage: sel "SQL_STATEMENT"
# exp:sel "* from mytable where a>'1' fetch first 2 rows only"
#
# valentine 2003/09/05
#
DB=${DEFAULTDBS:-${LOGNAME}}
SCHEMA=${SCHEMA:-"schema"}
TMPDIR="/tmp"
TMPFILE1=$TMPDIR/sqldata.$$
TMPFILE2=$TMPDIR/export.$$
myexit()
{
echo "You canceled the operation"
rm -f $TMPFILE1 >/dev/null 2>&1
rm -f $TMPFILE2 >/dev/null 2>&1
}
[ $# -lt 1 ] && echo "Usage: `basename $0` SQL_SELECT_STATEMENT" && exit
trap "myexit" 2 3
fields=`echo "$*"|sed's/\(.*\)from.*/\1/'`
table=`echo "$*"|sed's/.*from *\([^ ]*\) *.*/\1/'|tr "" ""`
where=`echo "$*"|sed 's/.*from/from/'`
db2 "connect to $DB" >/dev/null 2>&1||{ echo "can't connect$BTPDBS";exit;}
db2 "set schema $SCHEMA">/dev/null2>&1
db2 "describe select $fields from $table"|sed -e '1,9d' -e '$d' -e 's/,./,/g'|aw
k '{printf("%s|",$4)}END{print}'>$TMPFILE1 2>/dev/null
db2 "export to $TMPFILE2 of del modified by coldel| select $fields $where" >/dev
/null 2>&1
cat $TMPFILE1 $TMPFILE2|tr -d '"' |sed 's/\([+,-]\)0*/\1/g'|awk -F'|' '{if(NR==1){split($0
,field)} else{for(i=1;i<=NF;i++){printf("%s [%s]\n",field
,$i);}print("")}}'
rm -f$TMPFILE1
rm -f$TMPFILE2
trap 2 3
页:
[1]