sysbase scipt error

Hi All,

when is run this manually it work's

i am UNable to get the proper .sql so that i can use this in my input file. I need to do this way only bcz of requirment.

Sample script

isql -Sxxx -Usa -Pyyyy -D master -o "abc_script.sql" << EOF
echo USE master >> "abc_script.sql"
echo go >> "abc_script.sql"
echo dump database $1 to "\"/opt/sybase/shell_script/tradescope_test.dump"\" >> "abc_script.sql"
echo select "\"kill "\"+convert\(\varchar\(\5\)\,spid\)\ from master..sysprocesses where dbid= db_id\(\"$2"\)\ >> "abc_script.sql"
echo go >> "abc_script.sql"
EOF
isql -i "abc_script.sql" -o "abc_script.log" -Usa -Pyyyy -Sxxxx -Dmaster

[sybase@server05 shell_script]$ ./dump.sh abc test

Two place i am getting error

1. overall i am not getting the "abc_script.sql" file (0 bytes)
2. i am getting error on echo select "\"kill "\"...... line

Thanks
all

---------- Post updated at 12:00 PM ---------- Previous update was at 10:36 AM ----------

Hi All,

2. i am getting error on echo select "\"kill "\"...... line

The echo of this line is for $2 parameter variable is unable to assgin ,where i am going wrong

echo select "\"kill "\"+convert\(\varchar\(\5\)\,spid\)\ from master..sysprocesses where dbid= db_id\(\"\$2\"\) >> "abc_script.sql"

 
output of the echo is...
 
create database abc_test on data_db1 = 20
go
select "kill "+convert(varchar(5),spid) from master..sysprocesses where dbid= db_id("$2")
go

Do you really need to program it this way? That is awfully confusing.

First, why are you killing connections after the dump database?

Second, do you really need to program it that way?

Third, there is a bug here (I think):

tradescope_test.dump"\"

The slash should be before the first ".

tradescope_test.dump\""

Are you not dumping the transaction log?

I can think of several ways to make this better:

  1 #!/usr/bin/ksh
  2
  3 $SYBASE/$SYBASE_OCS/bin/isql -X -U$1 -P`cat "$2"` -S$3 << ! | grep -v -e-- | $SYBASE/$SYBASE_OCS/bin/isql -X -U$1 -P`cat "$2"` -S$3 | grep -v 'return status'
  4 set nocount on
  5 go
  6 use master
  7 go
  8
  9 print "set nocount on"
 10 print "go"
 11 print "use master"
 12 go
 13 print "go"
 14 go
 15
 16 print 'dump database $4 to "/tmp/$4.dump"'
 17 go
 18 print "go"
 19 go
 20
 21 --select char(10)+'kill '+spid+char(10)+'go' from master..sysprocesses where dbid=db_id($2)
 22 !
 23
 24 exit

I didn't run the kill, but you see how it could be better done.

Or make a script and run the script:

cat <<EOF >>$DUMP_COMMAND_FILE
use master
go
dump database $4
go
EOF

$ISQL_COMMAND -X -U$SQLUSER -P`cat "$PASSFILE"` -S$SERVER -w 255 -e -i $DUMP_COMMAND_FILE

This last slash also seems to be a bug:

$2"\)\ >>

Finally, here is code that we have used for years:

echo "use master"                                       > $DUMP_COMMAND_FILE
echo "go"                                               >>$DUMP_COMMAND_FILE
echo "dump database $DATABASE"                          >>$DUMP_COMMAND_FILE

# first stripe
if [[ $STRIPES -gt 1 ]]
then
   echo "       to \"${DUMPNAME}.1\""                   >>$DUMP_COMMAND_FILE
else
   echo "       to \"${DUMPNAME}\""                     >>$DUMP_COMMAND_FILE
fi

# for each stripe, loop
COUNT=2
while [[ $COUNT -le $STRIPES ]]
do
   echo "stripe on \"${DUMPNAME}.$COUNT\""              >>$DUMP_COMMAND_FILE
   let "COUNT += 1"
done

# final parameters
echo "with file=\"$FILENAME\",  retaindays = 8, init"   >>$DUMP_COMMAND_FILE
echo "go"                                               >>$DUMP_COMMAND_FILE

BEGIN_DUMP_TIME=`date +"%H:%M:%S"`

# now run the command file we just built
$ISQL_COMMAND -X -U$SQLUSER -P`cat "$PASSFILE"` -S$SERVER -w 255 -e -i $DUMP_COMMAND_FILE | tee $COMMAND_FILE_LOG
RETCODE1=$?; if [[ $RETCODE -eq 0 ]]; then RETCODE=$RETCODE1; fi

# check for errors
if [[ `cat $COMMAND_FILE_LOG | grep -c 'Msg.*Level.*State.*'` -gt 0 ]]
then
   RETCODE=-1

fi

If you need to kill the users on the system, try this:

131 $SYBASE/$SYBASE_OCS/bin/isql -X -U$SQLUSER -P$(cat "$PASSFILE") -S$SERVER << ! | grep -v -e-- | $SYBASE/$SYBASE_OCS/bin/isql -X -U$SQLUSER -P$(cat "$PASSFILE") -S$SERVER
132 set nocount on
133 go
134 /* build a list of processes */
135 select char(10)+'kill '+convert(varchar(10),SP.spid)+char(10)+'go'
136   from sysprocesses SP, sysdatabases SD, syslogins SL
137   where SP.dbid = SD.dbid
138   and (SD.name = '$DATABASE' or '$DATABASE' = 'all')
139   and spid not in (2,3,4,5,6,7,@@spid) and
140   SP.suid = SL.suid and
141   SL.fullname is NULL
142   order by spid
143 go
144 !