• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

Convert Unix Script for IQ from ASE

Hi guys

I have unix shell scipt created for ASE to get all login information of all server/databases . I need to convert it for IQ. Its created by someone else and i am preety new to sybase though not unix. I do got wht code is doing but it seems there are some sybase manipulations in it........


#!/bin/ksh
#set -x


USER=admin_user
PASSWD=`cat /cpt/sybase/dba/...`
SERVER=develop_dina_sina_sina01
DBASE=reporting
#SERVER=pr_databasemonitor01
#DBASE=databasemonitor

OUTFILE=/cpt/sybase/dbe/database_info/server_login.sql
LOG_FILE=/cpt/sybase/dbe/database_info/logs/server_login.log
MAILADD="dba@yahoo.com"
ERRFILE=/cpt/sybase/dbe/database_info/server_login.err

rm -f /cpt/sybase/dbe/database_info/login_info*
TEMPFILE=/cpt/sybase/dbe/database_info/login_info.err$$
SERVER_OUT=/tmp/servers.out
LOGIN_OUT=/tmp/logins.out

if [[  -a $ERRFILE ]]
then
  rm $ERRFILE
fi

echo /$LOG_FILE
echo "Population of server_login_info started: " + `date` > $LOG_FILE

$SYBASE/$SYBASE_OCS/bin/isql -U$USER -P$PASSWD -S$SERVER -D$DBASE -b <<  EOF1 >$SERVER_OUT
set nocount on
/*Select server_id and server_name */
select SERVERID=server_id, SERVERNAME=server_name from dbo.server_info
where server_type = "ASE"
and active_flag = 1
go
EOF1

while read SERVERID SERVERNAME
do

echo "Processing the Server: $SERVERNAME" >> $LOG_FILE

$SYBASE/$SYBASE_OCS/bin/isql -U$USER -P$PASSWD -S$SERVERNAME -b -w200 << EOF2 > $LOGIN_OUT
set nocount on  
use master
go
/*Select login information*/
Select SUID=l.suid,
       LOGIN_NAME=",'" + substring(isnull(l.name,''),1,35) + "'," ,
       FULLNAME= "'" + substring(isnull(l.fullname,''),1,50) +"',",
       LOCKED_FLAG =case l.status
          When 2 then 1
          else 0
       end,
       APPLICATION_USED=",'" +  substring(isnull(d.name,''),1,100)+ "',",
       DTE_CREATE="'" + convert(varchar(12),l.accdate)+ "'"
       
from master..syslogins l, master..sysdatabases d
         where l.suid *= d.suid
         and  d.dbid > 4 and d.dbid < 100
go
EOF2

SQLSTAT=$?

if [ "$SQLSTAT" -ne 0 ]    
then
   echo "\n\t Error: Login Incorrect.\n" >> $LOG_FILE
fi

#if [`wc -c $LOGIN_OUT | awk '{ print $1 }'` -eq 0]
# then
#    echo "\n\t\t Server <$SERVERNAME > no result set from syslogins.\n" >> $LOG_FILE
#fi

ERR=`egrep -i 'error' ${LOGIN_OUT} | wc -l`
if [ "$ERR" -gt 0 ]
then
  echo "Process the Server: $SERVERNAME" >> $ERRFILE
  egrep -i 'error' ${LOGIN_OUT} >> $ERRFILE
#  mail -s "Update of server_login_info failed" $MAILADD < $ERRFILE
fi

ERR=`egrep -i 'Msg' ${LOGIN_OUT} | wc -l `
if [ "$ERR" -gt 0 ]
then
  echo "Process the Server: $SERVERNAME" >> $ERRFILE
  egrep -i 'Msg' ${LOGIN_OUT} >> $ERRFILE
#  mail -s "Update of server_login_info failed" $MAILADD < $ERRFILE
fi

ERR=`egrep -i 'invalid' ${LOGIN_OUT} | wc -l `

if [ "$ERR" -gt 0 ]
then
  echo "Process the Server: $SERVERNAME" >> $ERRFILE
  egrep -i 'invalid' ${LOGIN_OUT} >> $ERRFILE
#  mail -s "Update server_login_info failed" $MAILADD < $ERRFILE
fi

ERR=`egrep -i 'Login' ${LOGIN_OUT} | wc -l `
if [ "$ERR" -gt 0 ]
then
  echo "Process the Server: $SERVERNAME" >> $ERRFILE
  egrep -i  'Login' ${LOGIN_OUT} >> $ERRFILE
#  mail -s "Update server_login_info  failed" $MAILADD < $ERRFILE
fi

cat ${LOGIN_OUT} | egrep -i -v 'error' |egrep -i -v 'Msg' | egrep -i -v 'Server' | egrep -i -v 'invalid' | egrep -i -v 'Login' | sort -u >  temp.sql

mv temp.sql  ${LOGIN_OUT}

while read SUID LOGIN_NAME FULLNAME LOCKED_FLAG APPLICATION_USED DTE_CREATE
do
set -x
       echo "p_iu_server_login_info_all $SERVERID,$SUID $LOGIN_NAME $FULLNAME $LOCKED_FLAG $APPLICATION_USED $DTE_CREATE \ngo\n" >> $OUTFILE

done < $LOGIN_OUT
done < $SERVER_OUT
#Execute all stored procedures
echo "***********Executing all stored procedures***************" >> $LOG_FILE

$SYBASE/$SYBASE_OCS/bin/isql -U$USER -P$PASSWD -S$SERVER -D$DBASE -i$OUTFILE  -b  >> $TEMPFILE

if [ "$SQLSTAT" -ne 0 ]
then
   
   echo "\n\t Error: Error in executing procedure p_iu_server_login_info. SQL Status: $SQLSTAT" >> $ERRFILE
fi

ERR=`egrep -i -v 'return status = 0' $TEMPFILE | wc -l `

if [ "$ERR" -gt 0 ]
then
  echo "\n\t Error: Error in executing procedure p_iu_server_login_info for server: $SERVERNAME"  >> $ERRFILE

   egrep -i -v 'return status = 0' ${TEMPFILE} >> $ERRFILE
fi

echo "Population of server_login_info ended: " + `date` >> $LOG_FILE
#remove the temporary files

rm $SERVER_OUT
rm $LOGIN_OUT
mv $OUTFILE $OUTFILE$$
rm $TEMPFILE
if [ -r ${ERRFILE} ]
then
if  [ `wc -c ${ERRFILE} | awk ' { print $1 } '`  -gt  0 ]
 then
 exit 1
fi


0
amahajan1981
Asked:
amahajan1981
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
The vast majority of this simply doesn't apply to IQ. It doesn't have these system tables.

It has some equivalents but I'm not sure these are even sensible questions to ask about IQ... it's totally different to ASE.

I'm currently at a site with 120+ ASEs and 4 IQs. There are very few scripts we can use in common. All IQ handling required different code.

Going through it one section at a time...

The first section checks a server_info table. This isn't a standard ASE system table, it's a custom table someone's created for your environment. You'd have to make one for IQ.

The second section gets login information. IQ has something similar in the system table SYS.SYSLOGIN... but it doesn't store any of the same information, really, and there's no easy way to get it. You'd have to poke around with system procedures like sa_conn_info.

Skipping the error handling (which you'd also have to rewrite as the errors are formatted differently), the third section calls "p_iu_server_login_info_all" - which is another custom procedure someone's written at your site. We can't tell what's in it, so it isn't possible to say whether it can be ported to IQ at all, or how much work it would be.

Strongly suggest you go back to requirements. What actually has to be done? It will be much less work to just figure that out and then write something to do that in IQ. Trying to port ASE code that heavily relies on system tables to IQ is about as much work as porting ASE to Oracle. System tables are product-specific. There's no "one size fits all".

Sorry I don't have a good answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now