how read the flat files from shell scripting

shijoe · #1 (**permalink**) 03-31-09, 07:56

We are using sybase data base and we have the data base name called MasterDB. The data baase MasterDB contains 100's of tables like sample1, sample2, sample3...etc...sample100
To take the count of every table we have to execute the following commands

use MasterDB //DB name
go //execute command in sybase
select count(*) from sample1 // command to find the count of table
go //execute the command

This is the way to take count. Likewise manually we have to take 100 tables count...we have to automates this though shell scripts.

What I did here is, I write all table names in the flat file and trying to read the flat files from the scripts

#!/bin/ksh

PATH=temp/flat_file
srv=sybaseserver
uid=operator
log=$LOGS/rowcount.log
pwd=`cat $SYBASE/.operatorpwd`
CP_CONN="-U$uid -S$srv -C3433 -b100000"

echo "count of table Started at: `date`" > $log
echo >> $log

echo "-------------------------------------------------------" >> $log
echo 'Setting table count..' >> $log
isql -U$uid -S$srv << EOF >> $log
$pwd
use customers
go
print 'taking count from tables..'
go
while read line
do
select count(*) from $customer_table \n
go
done < outfile.dat

i know its incorrect logic..pls help me onthis

pdreyer · #2 (**permalink**) 03-31-09, 10:28

Code:

#!/bin/ksh
srv=sybaseserver
uid=operator
log=$LOGS/rowcount.log
pwd=`cat $SYBASE/.operatorpwd`
gensel() {
isql -U$uid -S$srv -b -Dcustomers <<EOF
set nocount on
go
select 'select count(*) as '+name+' from '+name+char(10)+'go ' from sysobjects where type='U' and name like 'sample%'
go
EOF
}
isql -U$uid -S$srv -Dcustomers <<EOF >>$log
$(gensel)
EOF

LKBrwn_DBA · #3 (**permalink**) 03-31-09, 10:34

You are not allowed to MIX sql statements and shell commands.
Try this approach:

Code:

#!/bin/ksh

FILE_PATH=temp/flat_file
srv=sybaseserver
uid=operator
log=$LOGS/rowcount.log
pwd=`cat $SYBASE/.operatorpwd`
CP_CONN="-U$uid -S$srv -C3433 -b100000"
{
echo "$pwd"
echo "use customers"
echo "go"
} > $FILE_PATH/cmdfile.sql
while read customer_table
do
  echo "select '$customer_table count:' as table_name, count(*) from $customer_table"
  echo "go"
done < $FILE_PATH/outfile.dat >>$FILE_PATH/cmdfile.sql
echo "exit" >> $FILE_PATH/cmdfile.sql
## ---
echo "count of table Started at: `date`\n" > $log
echo "-------------------------------------------------------" >> $log
echo 'Setting table count..' >> $log
isql $CP_CONN <$FILE_PATH/cmdfile.sql >> $log

mike_bike_kite · #4 (**permalink**) 03-31-09, 12:17

Quote:

Originally Posted by shijoe

We are using sybase data base and we have the data base name called MasterDB. The data baase MasterDB contains 100's of tables like sample1, sample2, sample3...etc...sample100

Just a comment - I don't think it's a good idea having a production database named almost the same as the master database in Sybase. At some point in the future someone's going to get the two confused and you'll have a disaster on your hands.

Internet Services

Web Development

Digital Marketing

Consumer Tech