Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to run multiple Queries in a ksh Script?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to run multiple Queries in a ksh Script?
# 1  
Old 10-08-2014
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  
Old 10-08-2014
Show the input you have, and the output you want.
# 3  
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,

# 4  
Old 10-10-2014
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  
Old 10-10-2014
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  
Old 10-10-2014
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 to Ask a Question

Previous Thread | Next Thread

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
Login or Register to Ask a Question