How to run multiple Queries in a ksh Script?


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
How to run multiple Queries in a ksh Script?

How to run multiple Queries in a ksh Script
I have a KSH script that has one SQL Query and generates and emails output of the query in HTML format. I want to change the script so that it has three SQL queries and the last query generates and emails the HTML output page of just that query.
So far I have:

Query 1 - Creates a table by performing a select into into
a table in tempdb

Query 2 - Creates a table by performing a select into into a
table in tempdb

Query 3 - Performs a join between table 1 and table 2 and creates table 3 Then table 3 is queried and and the result is placed in a HTML file and emailed

I know the code below works great In a script I have but this is only one query. I want to use 3 Queries and the result of the third query
is used to generate the html report.




Code:
while IFS=" " read server uid pwd
do
###############    Backup Report Connection and Query    #######################         
`isql  -S$server  -U$uid -P$pwd -D master -s',' -n  -b  -w250 <<EOF >> $txtfile
go
Set nocount on
go
use master
go
select  @@servername,
        ltrim(rtrim(b.DBNAME)),
        ltrim(rtrim(convert(char(3),b.BackupInProgress))), 
        ltrim(rtrim(convert(char(3),b.LastBackupFailed))),
        ltrim(rtrim(b.BackupStartTime)),  
        ltrim(rtrim(b.LastTranLogDumpTime))
from monopendatabases b
go
exit
EOF`

# 2  
Show the input you have, and the output you want.
# 3  
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,

# 4  
PS: Consider using derived tables and one query:
Code:
SELECT whatever from (query 1) a join (query 2) b on b.key=a.key . . . .

Sybase will handle the temdb tables underneath silently.
# 5  
I have this script that does what I want. This reports on uses one
SQL query. What I need to know is how to how to have 3 queries
in the script were queries 1 and 2 create tables that are used
by query 3. Query three performs a query that produces the data the generates the report.


I hope this clarifies things.
# 6  
The trick to SQL is thinking in sets and joins, not procedurally. This is a big leap for many procedural coders. I have seen too many loops that should be joins. For instance:
Code:
This could just be a table & column in the following join:
 
declare @swexpire int
select @swexpire=value from master.dbo.sysconfigures
    where name = 'systemwide password expiration
 
This is just a constant:
 
@xerox_emp = "N"
 
This is unused:
 
declare @LoginExpInt int
 
This should just be a calculated column in the target list:
 
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
 
becomes the new end of the target list:
 
   , Case when PasswordExpInt !=0 Then DATEDIFF(DAY, getdate(), PasswordExpDate) Else 0 end AS NumDaysToExp 
 
Now the first query can just be a single select in a derived table.

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #385
Difficulty: Easy
The Linux uptime command show how long the hard drive has been powered down.
True or False?

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

Featured Tech Videos