Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Unix Script for IQ from ASE

Posted on 2006-05-01
1
Medium Priority
?
766 Views
Last Modified: 2008-03-04
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
Comment
Question by:amahajan1981
1 Comment
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 1500 total points
ID: 16612793
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
Use this step by step method when setting up QuickBooks Online. They will allow you to explore the various features of the advanced settings available to you.
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question