It is currently 18 Oct 2017 4:16


Post new topic  Reply to topic  [ 6 posts ] 
Author Message
PostPosted: 27 Jul 2010 5:59 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Nov 2009 12:00
Posts: 17
Hi all,

I have a couple scripts I use in Unix, but I need to convert the script to be used in a Windows env. Can someone help me with syntax?
This job does a general scan of all tables and jobs.
What do I replace for windows? Help appreciated Thanks

#!/bin/ksh
#
Date=`date`
Host=`hostname`
report=/tmp/scan_mem.$$.$1
#
echo "MEMBER REPORT - UNIX CONTROL-M - $Host ON: $Date">$report
echo "==================================================\
======================">>$report
echo "">>$report
#
SQL <<EOF>> $report
select SCHEDTAB, MEMNAME, NODEID, DESCRIPT from CMS_JOBDEF
where MEMNAME like '$2'
order by NODEID, SCHEDTAB, MEMNAME
/
EOF
#
cat $report
mailx -s "XXXXXXXXX" XXXXXXX.com < /tmp/scan_mem.$$.$1


Top
   
 Post subject:
PostPosted: 27 Jul 2010 6:32 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Jan 2010 12:00
Posts: 149
Simple: just build up your request by echo-ing your commands into a txt file, which you take as input to SQL
You do not mention who hides behind the SQL command, neither whether you change your control-m DB (from Oracle to MS/SQL for example)

For MSSQL, the MSDOS syntax would be:

set CTMU=control-m dbo
set CTMP=controlm dbo passwd
set CTMS=MS/SQL instance/server name

set ARG=%~2

echo select SCHEDTAB, > %MYREP%\select_from_JOBDEF.tmp
echo MEMNAME, >> %MYREP%\select_from_JOBDEF.tmp
echo NODEID, >> %MYREP%\select_from_JOBDEF.tmp
echo DESCRIPT >> %MYREP%\select_from_JOBDEF.tmp
echo from CMS_JOBDEF >> %MYREP%\select_from_JOBDEF.tmp
echo where MEMNAME like '%ARG%' >> %MYREP%\select_from_JOBDEF.tmp
echo order by NODEID, SCHEDTAB, MEMNAME >> %MYREP%\select_from_JOBDEF.tmp

isql -U%CTMU% -P%CTMP% -S%CTMS% -n -w800 -i%MYREP%\select_from_JOBDEF.tmp -o%MYREP%\select_from_JOBDEF.out

The -n suppresses the echo of the SQL commands in the result. The -w stands for the Oracle LINESIZE. The -o can be replaced by a greater than sign.

Use isql or osql depending on the MSSQL version (if MSSQL)
If you keep on using Oracle with a oracle client for windows, then the tmp file should include as well the closing semicolon and a "QUIT", or
your job will be stuck in Oracle.

I dont know what your $2 stands for, but according to the script, it is not changed and might includes a % sign.
The above syntax works but, in some cases, you might need to double the percent sign so DOS reduces it to a single %.

Cheers


Top
   
PostPosted: 27 Jul 2010 8:27 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Nov 2009 12:00
Posts: 17
Thank you so much Nicolas!

This job runs on Unix Solaris with an Oracle db. My new system is Windows with SQLserver 2005 db. I can't read the script very well myself. I just know what the out put is :)
What do you mean "who hides behind the SQL command"? I will work on it and if it is alright I will send it to you once I finished. Ok? Here is the other script I need to convert..please take a look.
They are similar.
#!/bin/ksh
#
Date=`date`
Host=`hostname`
report=$1
#
echo "STEPS & POSTPROC REPORT - CONTROL-M/Server $Host ON: $Date">$report
echo "=======================================================\
===========================">>$report
echo "">>$report
#
sqlplus -S dbo/dbopswd@EM613 <<EOF>> $report
set head off
select a.SCHEDTAB, a.JOBNAME, b.LOGIC_DEST StepsDoMail, c.MESSAGE PostProcMsg
from CMS_JOBDEF a, CMS_MAIL b, CMS_SHOUT c
where a.JOBNO=b.JOBNO and b.JOBNO=c.JOBNO and c.JOBNO=a.JOBNO
order by a.SCHEDTAB, a.JOBNAME;
EOF
mailx -s "STEPS & POSTPROC REPORT - CTM/Server sched1" $2 < $1
#cat $report

(the EM613 is the Oracle SID)


Top
   
PostPosted: 27 Jul 2010 8:30 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Nov 2009 12:00
Posts: 17
Oh I forgot the control m version I need the jobs for is 6.4.01 :)


Top
   
 Post subject:
PostPosted: 27 Jul 2010 9:21 
Offline
Nouveau
Nouveau
User avatar

Joined: 07 Jan 2010 12:00
Posts: 149
Your first script include a "SQL" command, which is supplied as part of the controlm distibution, and which hides either a sqlplus
or isql command, depending on whether you use sybase or oracle on Unix.
Actually, I just checked under a windows DOS prompt, and SQL command is also available. It actually replaces the complete sequence
osql -U -P -S, which means you do not need to hardcode the dbo and its password as part of your DOS script, which is even better, and which I just realize after quite a long experience.

The structure of the SQL tables of the controlm db is stricly identical in windows and in Unix. As a consequence, the logic of your select
statement is stricly identical in both environments.
Just keep on echo-ing your SQL lines to a tmp text file, remove the semicolon, and you can replace the osql -U blabla command by the following:

SQL -n -w800 -i%MYREP%\select_bla_bla.tmp

While converting other PL/SQL to MS/SQL sequnces, you might have to face differences in the script syntax. For example, string concatenation in Oracle is "||" while it is a "+" in MS/SQL, ocnverting numeric is "TOCHAR(nn)" under Oracle while it is "convert(char(n),nn)" iunder MS/SQL etc..

I guess you will have both Oracle and MS/SQL script experts to assist you.

I operate a ctm 640 with mssql2005 too, and I ran your tests

Cheers


Top
   
 Post subject:
PostPosted: 15 Feb 2011 11:52 
Offline
Nouveau
Nouveau
User avatar

Joined: 09 Feb 2011 12:00
Posts: 2
Does anybody know of other bands like Dos Gringos? I'm becoming a big fan of the "almost" famous fighter pilot band called Dos Gringos. Does anybody know of somebody who does aviaton related music like theirs?
____________________________
market samurai ~ marketsamurai ~ marketsamurai.com


Top
   
Display posts from previous:  Sort by  
Post new topic  Reply to topic  [ 6 posts ] 

All times are UTC+02:00


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Limited
The site created by Franck YOT. Images, logo, content and trademarks about Control-M products are property of BMC Software.
All the comments are property of their posters. Images, logo, content and design are © copyright by Scheduler-Usage.com. All Rights Reserved