一个转化db2 select 语句输出的脚本

shift_1

最近在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 "[a-z]" "[A-Z]"`


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/null  2>&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