SQL Script executed in Unix from jobs Command line

Some examples running with Control-M and Control-EM database.
Post Reply
User avatar
liketolearn
Nouveau
Nouveau
Posts: 17
Joined: 07 Nov 2009 12:00

SQL Script executed in Unix from jobs Command line

Post by liketolearn » 27 Jul 2010 5:59

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

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 27 Jul 2010 6:32

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

User avatar
liketolearn
Nouveau
Nouveau
Posts: 17
Joined: 07 Nov 2009 12:00

SQL Script executed in Unix from jobs Command line

Post by liketolearn » 27 Jul 2010 8:27

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)

User avatar
liketolearn
Nouveau
Nouveau
Posts: 17
Joined: 07 Nov 2009 12:00

SQL Script executed in Unix from jobs Command line

Post by liketolearn » 27 Jul 2010 8:30

Oh I forgot the control m version I need the jobs for is 6.4.01 :)

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 27 Jul 2010 9:21

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

User avatar
amoniha
Nouveau
Nouveau
Posts: 2
Joined: 09 Feb 2011 12:00

Post by amoniha » 15 Feb 2011 11:52

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

Post Reply