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
SQL Script executed in Unix from jobs Command line
- liketolearn
- Nouveau
- Posts: 17
- Joined: 07 Nov 2009 12:00
- nicolas_mulot
- Nouveau
- Posts: 149
- Joined: 07 Jan 2010 12:00
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
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
- liketolearn
- Nouveau
- Posts: 17
- Joined: 07 Nov 2009 12:00
SQL Script executed in Unix from jobs Command line
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)
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)
- liketolearn
- Nouveau
- Posts: 17
- Joined: 07 Nov 2009 12:00
SQL Script executed in Unix from jobs Command line
Oh I forgot the control m version I need the jobs for is 6.4.01
- nicolas_mulot
- Nouveau
- Posts: 149
- Joined: 07 Jan 2010 12:00
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
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
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
____________________________
market samurai ~ marketsamurai ~ marketsamurai.com