Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to run multiple Queries in a ksh Script?
Top Forums Shell Programming and Scripting How to run multiple Queries in a ksh Script? Post 302920651 by JolietJake on Friday 10th of October 2014 04:01:59 PM
Old 10-10-2014
Code:
SQL QUERY 1: Ceates - SYBASE_EXPIRED_LOGINS Table
use master
go
set nocount on
  declare @xerox_emp varchar 
  select @xerox_emp = "N" 
  declare @swexpire int
  declare @LoginExpInt int
  select @swexpire=value from master.dbo.sysconfigures
    where name = 'systemwide password expiration'
  print "Serverwide password expire: %1!" ,@swexpire
  select distinct @@servername AS "ServeName" , l.suid SybSuid, l.name SybLoginName , @xerox_emp AS "EMPID",
      case a.int_value when null then @swexpire else a.int_value end AS PasswordExpInt,
      l.pwdate AS "PwdLastChgDate", 
      DATEADD(DAY, case a.int_value when null then @swexpire else a.int_value end ,   l.pwdate) AS "PasswordExpDate",
      LoginLocked = case when l.lockreason >=0 then "YES" else "No" end,
      LoginExpired = case when l.status =4 then "YES" else "No" end
INTO tempdb.dbo.SYBASE_EXPIRED_LOGINS
      from master.dbo.syslogins l , master.dbo.sysattributes a
    where l.suid *= a.object
      and a.object_type='PS'
      and a.attribute=0
      and object_cinfo='login'
order by l.suid asc
go
ALTER TABLE tempdb.dbo.SYBASE_EXPIRED_LOGINS add NumDaysToExp INT DEFAULT (0);
GO 
Update tempdb.dbo.SYBASE_EXPIRED_LOGINS
Set  NumDaysToExp = Case when PasswordExpInt !=0 Then  DATEDIFF(DAY, getdate(), PasswordExpDate) Else 0 end
--Set  NumDaysToExp = Case when PasswordExpInt !=0 Then  DATEDIFF(DAY, PasswordExpDate, getdate()) Else 0 end

Query 1 Output Sybase Expired Logins
ServeName,SybSuid,SybLoginName,EMPID,PasswordExpInt,PwdLastChgDate,PasswordExpDate,LoginLocked,LoginExpired,NumDaysToExp
'OLTP_DEV',1,'sa','N',0,2010-03-11 18:44:51.713,2010-03-11 18:44:51.713,'YES','No',0
'OLTP_DEV',2,'probe','N',0,2009-12-05 14:32:47.686,2009-12-05 14:32:47.686,'No','No',0
'OLTP_DEV',38,'TPCS','N',0,2005-12-20 10:10:00.0,2005-12-20 10:10:00.0,'No','No',0
'OLTP_DEV',40,'alvarte','N',90,2013-04-18 16:05:53.33,2013-07-17 16:05:53.33,'No','No',-447
'OLTP_DEV',41,'brownra','N',90,2003-04-28 08:23:00.0,2003-07-27 08:23:00.0,'No','No',-4090
'OLTP_DEV',43,'carutji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124


Code:
 SQL Query 2 - Creates Table - Sybase_USER_ROLES
 
DECLARE @LastSuidCode INT,
        @LastRoleDesc VARCHAR(512)
 
  
select distinct sl.suid as slsuid ,sl.name as slname , CONVERT(VARCHAR(512), ssr.name) as ssrname
into tempdb.dbo.Sybase_USER_ROLES
from master.dbo.syslogins sl
  join master.dbo.sysloginroles slr
    on slr.suid = sl.suid
  join master.dbo.syssrvroles ssr
    on ssr.srid = slr.srid
Order by sl.suid asc

 
  SET @LastSuidCode = 0,
      @LastRoleDesc = ''
 
  UPDATE tempdb.dbo.Sybase_USER_ROLES
     SET ssrname = CASE WHEN slsuid = @LastSuidCode
                          THEN @LastRoleDesc + ssrname + ','
                          ELSE ssrname + ',' END,
         
         @LastRoleDesc = CASE WHEN slsuid  = @LastSuidCode
                          THEN @LastRoleDesc + ssrname + ','
                          ELSE ssrname + ',' END,    
         @LastSuidCode = slsuid

 
SELECT distinct slsuid, slname, ssrname
into tempdb.dbo.Sybase_SECURITY_USERS_AUD_ROLE
    FROM tempdb.dbo.Sybase_USER_ROLES
    GROUP BY slsuid HAVING ssrname=(MAX(ssrname))
 
Query 2 - Sybase_USER_ROLES
 
slsuid,slname,ssrname
1,'sa','oper_role,'
1,'sa','oper_role,replication_role,'
54,'prasaap','mon_role,'
54,'prasaap','mon_role,navigator_role,'
54,'prasaap','mon_role,navigator_role,oper_role,'
54,'prasaap','mon_role,navigator_role,oper_role,sa_role,'
56,'sburdett','dtm_tm_role,'
56,'sburdett','dtm_tm_role,ha_role,'
56,'sburdett','dtm_tm_role,ha_role,mon_role,'
56,'sburdett','dtm_tm_role,ha_role,mon_role,navigator_role,oper_role,'


Code:
 SQL Query 3 Creates Table 
 
 
select distinct sl.suid as slsuid ,sl.name as slname , CONVERT(VARCHAR(512), ssr.name) as ssrname
into tempdb.dbo.Sybase_USER_ROLES
from master.dbo.syslogins sl
  join master.dbo.sysloginroles slr
    on slr.suid = sl.suid
  join master.dbo.syssrvroles ssr
    on ssr.srid = slr.srid
Order by sl.suid asc

 
  SET @LastSuidCode = 0,
      @LastRoleDesc = ''
 
  UPDATE tempdb.dbo.Sybase_USER_ROLES
     SET ssrname = CASE WHEN slsuid = @LastSuidCode
                          THEN @LastRoleDesc + ssrname + ','
                          ELSE ssrname + ',' END,
         
         @LastRoleDesc = CASE WHEN slsuid  = @LastSuidCode
                          THEN @LastRoleDesc + ssrname + ','
                          ELSE ssrname + ',' END,    
         @LastSuidCode = slsuid

 
 
use tempdb
go
Select a.ServeName,a.SybLoginName,  a.EMPID,a.PasswordExpInt,a.PwdLastChgDate,a.PasswordExpDate,a.LoginLocked,a.LoginExpired,a.NumDaysToExp,b.ssrname
from tempdb.dbo.SYBASE_EXPIRED_LOGINS a, tempdb.dbo.Sybase_SECURITY_USERS_AUD_ROLE b
where a.SybSuid *= b.slsuid
order by a.SybSuid

 
QUERY 3 Output
 
ServeName,SybLoginName,EMPID,PasswordExpInt,PwdLastChgDate,PasswordExpDate,LoginLocked,LoginExpired,NumDaysToExp,ssrname
'OLTP_DEV','sa','N',0,2010-03-11 18:44:51.713,2010-03-11 18:44:51.713,'YES','No',0,'oper_role,replication_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','probe','N',0,2009-12-05 14:32:47.686,2009-12-05 14:32:47.686,'No','No',0,
'OLTP_DEV','TPCS','N',0,2005-12-20 10:10:00.0,2005-12-20 10:10:00.0,'No','No',0,
'OLTP_DEV','alvarte','N',90,2013-04-18 16:05:53.33,2013-07-17 16:05:53.33,'No','No',-447,
'OLTP_DEV','brownra','N',90,2003-04-28 08:23:00.0,2003-07-27 08:23:00.0,'No','No',-4090,
'OLTP_DEV','carutji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','cron_user','N',90,2004-06-18 09:25:00.0,2004-09-16 09:25:00.0,'YES','No',-3673,
'OLTP_DEV','davismi','N',90,2005-01-21 10:30:00.0,2005-04-21 10:30:00.0,'No','No',-3456,
'OLTP_DEV','dialja','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'YES','No',-5124,
'OLTP_DEV','dischro','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','gastoji','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','goelpoo','N',90,2014-08-12 14:44:42.28,2014-11-10 14:44:42.28,'No','No',34,
'OLTP_DEV','powersite','N',90,2000-06-28 12:19:00.0,2000-09-26 12:19:00.0,'No','No',-5124,
'OLTP_DEV','prasaap','N',90,2014-03-06 14:41:22.913,2014-06-04 14:41:22.913,'YES','No',-125,'mon_role,navigator_role,oper_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','sarabsa','N',90,2012-09-18 11:54:56.846,2012-12-17 11:54:56.846,'YES','No',-659,
'OLTP_DEV','sburdett','N',90,2014-03-26 09:54:35.366,2014-06-24 09:54:35.366,'No','No',-105,'dtm_tm_role,ha_role,mon_role,navigator_role,oper_role,replication_role,sa_role,sso_role,sybase_ts_role,'
'OLTP_DEV','seligri','N',90,2003-08-04 10:02:00.0,2003-11-02 10:02:00.0,'No','No',-3992,
'OLTP_DEV','hicksan1','N',90,2000-10-05 09:57:00.0,2001-01-03 09:57:00.0,'No','No',-5025,'dtm_tm_role,ha_role,mon_role,navigator_role,replication_role,'
'OLTP_DEV','mcdonka','N',90,2004-07-27 08:57:00.0,2004-10-25 08:57:00.0,'No','No',-3634,'mon_role,'
'OLTP_DEV','bohnech','N',90,2012-04-30 12:11:13.623,2012-07-29 12:11:13.623,'YES','No',-800,
'OLTP_DEV','sybase','N',0,2000-11-08 14:25:00.0,2000-11-08 14:25:00.0,'No','YES',0,
'OLTP_DEV','EAS1','N',90,2000-12-14 14:16:00.0,2001-03-14 14:16:00.0,'No','No',-4955,

 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Run ksh script from cgi

Hi, I'm developing a system which requires me to run a ksh script from within a cgi script. What sort of syntax will I need to do this, I'm sure it's simple but can't find out how anywhere! Thanks. (2 Replies)
Discussion started by: hodges
2 Replies

2. Shell Programming and Scripting

Multiple MySql queries in shell script?

Hi guys, i know how to run a single query using mysql embedded in a shell script as follows: `mysql -umyuser -pmypass --host myhost database<<SQL ${query}; quit SQL` However, how would i be able to run several queries within the same connection? The reason for this is i am creating... (3 Replies)
Discussion started by: muay_tb
3 Replies

3. Shell Programming and Scripting

KSH script to run other ksh scripts and output it to a file and/or email

Hi I am new to this Scripting process and would like to know How can i write a ksh script that will call other ksh scripts and write the output to a file and/or email. For example ------- Script ABC ------- a.ksh b.ksh c.ksh I need to call all three scripts execute them and... (2 Replies)
Discussion started by: pacifican
2 Replies

4. Shell Programming and Scripting

run script through crontab using ksh

hi i have a script called test.sh. the content is ls >> crontest.txt. if i run manually it's giving output.but if i scheduled in crontab it's not giving output. crontab entry: 02 * * * * /sms5/SMSHOME/eds_sh/test.sh >> /sms5/SMSHOME/eds_sh/testfile/logfile 2>&1 I am using ksh.is there... (2 Replies)
Discussion started by: muraliinfy04
2 Replies

5. Shell Programming and Scripting

How to store results of multiple sql queries in shell variables in ksh?

Hi, I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else. Thanks in advance.. Cheers (6 Replies)
Discussion started by: gonchusirsa
6 Replies

6. Shell Programming and Scripting

Run script in a backgroun - ksh

i ran the below in ksh... nohup <script> & it is runnign in background. now how do i see if the above command is success... i also need to bring the command to foreground and view the run details. pls advise how to do that... (1 Reply)
Discussion started by: billpeter3010
1 Replies

7. Shell Programming and Scripting

How to run multiple .py in ksh?

Hi programmers, say I have 4 files : file1.py,file2.py,file3.py,file4.py How do I, on a korn shell, create one file, run_all, that is one file that sequentially calls file1-file4, but only so if they complete w/o errors? Something like: #!/usr/bin/ksh file1.py /*......????*/ ... (7 Replies)
Discussion started by: sas
7 Replies

8. Linux

How to store count of multiple queries in variables in a shell script?

how to store the count of queries in variables inside a filein shell script my output : filename ------- variable1=result from 1st query variable2=result from 2nd query . . . . (3 Replies)
Discussion started by: sanvel
3 Replies

9. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

10. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused: (1 Reply)
Discussion started by: 24ajay
1 Replies
All times are GMT -4. The time now is 12:25 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy