run a batch script

[syang@collaborative.com]

Hi all,

I'm trying to run a job from DataStage using a Batch file. I am new to DataStage and new to Batch files.

I'm using this batch file as a starting point.

Code: Select all

@echo off
:: -----------------------------------------------------------------
:: Name: CommonProviderExtract.bat
:: -----------------------------------------------------------------
:: Description:
:: Run the DataStage job from command line using the parameters.
:: -----------------------------------------------------------------
:: Created by: Sreeram Makam, April 29,2010
:: -----------------------------------------------------------------
:: Todo:
::    Add more functionality like job reports.
:: -----------------------------------------------------------------
:: Required Components:
::     dsjob.exe (Windows Version)
:: -----------------------------------------------------------------
:: Command Line Parameters:
:: 1. Host
:: 2. User
:: 3. Password
:: 4. Project
:: -----------------------------------------------------------------
:: Ensure that everything that are set here are not permanent.
:: -----------------------------------------------------------------
    SETLOCAL
:: -----------------------------------------------------------------
:: Test for command line parameters.
:: -----------------------------------------------------------------
    IF "%1"=="" GOTO Syntax
    IF "%2"=="" GOTO Syntax
    IF "%3"=="" GOTO Syntax
    IF "%4"=="" GOTO Syntax
:: -----------------------------------------------------------------
:: Set paramters.
:: -----------------------------------------------------------------
    SET Host=%1
    SET User=%2
    SET Password=%3
    SET Project=%4
 SET TGTSSODSDSN=<DSN_Name>
 SET TGTSODSUserID=<UserName>
 SET TGTSODSPassword=<Password>
 SET EnterpriseInterfacesDSN=<Target DSN>
 SET EnterpriseInterfacesUID=<Username>
 SET EnterpriseInterfacesPWD=<Password>
 SET JobName=Common_ProviderExtract
:: -----------------------------------------------------------------
:: Hard-coded values.  Dependent on each computer.
:: -----------------------------------------------------------------
    SET Designer=C:\Progra~1\Ascential\DataStage\dsdesign.exe
    SET DsJob=C:\IBM\InformationServer\Clients\Classic\dsjob
    SET JobList=DsJobReportList.txt
    SET ProjectList=ProjectList.txt
    SET DSLog=DsJobReportLog
    SET BackupDir=C:\tmp\log
:: -----------------------------------------------------------------
:: Get the current Date
:: -----------------------------------------------------------------
    FOR /f "tokens=2-4 delims=/ " %%a in ('DATE/T') do SET DsxDate=%%c%%a%%b
:: -----------------------------------------------------------------
:: Get the current Time
:: -----------------------------------------------------------------
    FOR /f "tokens=1* delims=:" %%a in ('ECHO.^|TIME^|FINDSTR "[0-9]"') do (SET DsxTime=%%b)
:: -----------------------------------------------------------------
:: Set delimeters so that current time can be broken down into components
:: then execute FOR loop to parse the DsxTime variable into Hr/Min/Sec/Hun.
:: -----------------------------------------------------------------
    SET delim1=%DsxTime:~3,1%
    SET delim2=%DsxTime:~9,1%
    FOR /f "tokens=1-4 delims=%delim1%%delim2% " %%a in ('echo %DsxTime%') do (
        set DsxHr=%%a
        set DsxMin=%%b
        set DsxSec=%%c
        set DsxHun=%%d
    )
:: -----------------------------------------------------------------
:: If provided directory is missing an ending \, append it.
:: Validate %BackupDir%'s existance.
:: -----------------------------------------------------------------
    if exist %BackupDir%\ set BackupDir=%BackupDir%\
    if NOT exist %BackupDir% GOTO BadMain
:: -----------------------------------------------------------------
:: Set the log file name to improve readability of code.
    SET LogFileName=%BackupDir%%DSLog%-%DsxDate%-%DsxHr%_%DsxMin%_%DsxSec%.log
:: -----------------------------------------------------------------
:: Announce to log of this program's run.
:: -----------------------------------------------------------------
    ECHO. > %LogFileName%
    ECHO DsJobReport ran on %DsxDate% %DsxHr%:%DsxMin%:%DsxSec% with the following parameters >> %LogFileName%
    ECHO Host=%Host% >> %LogFileName%
    ECHO User=%User% >> %LogFileName%
    ECHO BackupDir=%BackupDir%%DsxDate%\ >> %LogFileName%
    ECHO Designer=%Designer% >> %LogFileName%
    ECHO DsJob=%DsJob% >> %LogFileName%
    ECHO ProjectList=%ProjectList% >> %LogFileName%
    ECHO JobList=%JobList% >> %LogFileName%
    ECHO DSLog=%DSLog% >> %LogFileName%
    ECHO. >> %LogFileName%
:: -----------------------------------------------------------------
:: Begin Job Execution
::
:: NOTE:  %ERRORLEVEL% does not work for some reason.
:: -----------------------------------------------------------------
        ECHO %DsJob% -server %Host% -user %User% -password %Password% -run -wait -jobstatus -param $TGTSSODSDSN=%TGTSSODSDSN% -param $TGTSODSUserID=%TGTSODSUserID% -param $TGTSODSPassword=%TGTSODSPassword% %Project% %JobName% >> %LogFileName%
    %DsJob% -server %Host% -user %User% -password %Password% -run -wait -jobstatus -param $TGTSSODSDSN=%TGTSSODSDSN% -param $TGTSODSUserID=%TGTSODSUserID% -param $TGTSODSPassword=%TGTSODSPassword% %Project% %JobName%
        IF NOT %ERRORLEVEL%==0 GOTO ProjFail
        ECHO. >> %LogFileName%
        ECHO *** Completed Job execution for Job: %%i on Host: %Host% Project: %Project% >> %LogFileName%
        ECHO     to File: %BackupDir%%DsxDate%\%%i.html >> %LogFileName%
        ECHO. >> %LogFileName%
    )
:: -----------------------------------------------------------------
::    ECHO *** Export completed successfully for projects:
::    type %TempFile%
:: -----------------------------------------------------------------
    GOTO EXITPT
:: -----------------------------------------------------------------
:: a job failed to be exported.
:: -----------------------------------------------------------------
:ProjFail
    ECHO.
    ECHO *** ERROR:  Errorlevel did not return a value 0: %%i on Host: %Host% on Project: %Project%
    ECHO.
    ECHO. >> %LogFileName%
    ECHO *** ERROR:  Errorlevel did not return a value 0: %%i on Host: %Host% on Project: %Project% >> %LogFileName%
    ECHO. >> %LogFileName%
GOTO EXITPT
:: -----------------------------------------------------------------
:: Report that paramters are not valid on screen and log file.
:: Note: Password are not reported for better security.
:: -----------------------------------------------------------------
:BadParam
    echo.
    echo Invalid parameters - Unable to access Server.
    echo.
    echo. >> %LogFileName%
    echo Invalid parameters - Unable to access Server. >> %LogFileName%
    echo. >> %LogFileName%
GOTO Syntax
:: -----------------------------------------------------------------
:: Report that directory is non-existant.
:: -----------------------------------------------------------------
:BadMain
    echo.
    echo Bad/Non-existing directory: %BackupDir%
    echo.
    echo Please ensure that you have permission to access/create directories
    echo and files.  Also ensure that directory listed exists.
    echo.
    echo. >> %LogFileName%
    echo Bad/Non-existing directory: %BackupDir% >> %LogFileName%
    echo. >> %LogFileName%
GOTO EXITPT
:: -----------------------------------------------------------------
:: Report that program does not have privs to create directory.
:: -----------------------------------------------------------------
:BadDir
    echo.
    echo Unable to create subdirectory: %BackupDir%%DsxDate%\
    echo.
    echo Please ensure that you have permission to access/create directories
    echo and files.
    echo.
    echo. >> %LogFileName%
    echo Unable to create subdirectory: %BackupDir%%DsxDate%\ >> %LogFileName%
    echo. >> %LogFileName%
GOTO EXITPT
:: -----------------------------------------------------------------
:: Report proper syntax usage.
:: -----------------------------------------------------------------
:Syntax
    echo.
    echo DataStage DsJobReport Routine
    echo.
    echo Based on design by Sreeram Makam
    echo.
    echo Usage: CommonProviderExtract Server User Password Project
    echo.
GOTO ENDPOINT
:: -----------------------------------------------------------------
:EXITPT
    ECHO. >> %LogFileName%
:: -----------------------------------------------------------------
:ENDPOINT
    ENDLOCAL

I'm wondering what I put down as the directory for the DS jobs folder.

[priyadarshikunal]

Frankly speaking, I did not understand your questions. If you want to modify this batch file to put location of dsjob executable then you should change the below line in the batch file.

Code: Select all

 SET DsJob=C:\IBM\InformationServer\Clients\Classic\dsjob 

[syang@collaborative.com]

Sorry for the confusion, I'm wondering where the DataStage jobs' folder is located, so I can access the job with the script.

Thanks.

[chulett]

You're not sure where the jobs themselves live? That would be in each of your Project directories but that knowledge isn't needed in order to run them, all you need is the name of the project.

You've picked quite a goofy example to start with. Usually a batch made to run jobs is generic and can run any job, this one only runs one particular one... and seems conflicted as to what it is doing. "BackupDir"? "Export successfully complete"? Seems to me there are far better starting points out there for someone to learn from, especially if all you are doing is running a job which is just one line out of all of those.

You should also be looking into leveraging the "authfile" option to retrieve credentials, passing all that in the clear on the command line is not recommended.

[priyadarshikunal]

You do not need the project directory to run as mentioned by Craig already. You just need to supply the credentials and project name as parameter, as mentioned in the code already. There are many unused variable there though, may be its WIP and few more code block is to be written. But if its a valid copy you may wanna get rid of them, so that its easy to understand.

Code: Select all

Command Line Parameters: 
:: 1. Host 
:: 2. User 
:: 3. Password 
:: 4. Project 

[syang@collaborative.com]

Thanks. I didn't know that I didn't have to specify a path to where the jobs live. Is there a more simple example somewhere I could use? I couldn't find any except for the example posted here.

I'm trying to accomplish running a single job when a "done" file appears in a folder. As soon as that file appears, this batch script launches and runs a DataStage job, then once the job finishes successfully, it deletes that "done" file and waits for the next "done" file to appear.

[kduke]

Nobody writes scripts like me. Tons of comments and the dashes out to 80 characters. Funny to see my stuff reformatted and somebody else has their name on it. I don't mind but even with all those comments can't seem to make it work. Sad.

[chulett]

Knew that was yours, Kim. Or at least started off life as yours.

Syang - all you need is a Sequence job for that, no script required. No need to overly complicate things.

[chulett]

On the subject of a Sequence job, at its most basic all we're talking about is a Wait For File stage and a Job Activity stage. Next question would be - how often during the day will this flag drop? It is quite simple to handle if it is once per day, if it can be multiple times during the day that complicates things... slightly.

[syang@collaborative.com]

Thanks Craig, but the job I need to run is a Sequence job(email test for now). The OS the client is running is Windows Server 2012, so job scheduling in DS Director doesn't work. We are migrating from MS SQL Server 2005 to Netezza, and need to do incremental loading for the database daily. The batch script will run a single sequence job that runs all incremental loading for the database, only when all updates on the MS SQL Server has been completed.

[syang@collaborative.com]

I will be only once a day, but it will vary everyday depending on when the MS SQL Server finishes its updates.

[chulett]

Don't really understand the "job scheduling in Director doesn't work" statement but I'll take it at face value. Regardless, still don't need a script.

On the subject of once a day, schedule (however) the Sequence to start running first thing in the morning. The WFF will cause it to sit running until the file shows up at which point the job will delete the file and run the Job Activity or whatever else is in there. At that point it is done for the day and you can wait to restart it until the next morning.

And a Sequence job can run another Sequence job.

[syang@collaborative.com]

MS SQL Server is not compatible with the built-in scheduler in DS Director.
http://www-01.ibm.com/support/docview.w ... wg21669154

The end goal is to grab a file when it is written in a folder, but as of right now, we have to get a script to run the sequence job.

The script is not accepting Netezza's DSN for the "SET TGTSSODSDSN=<DSN_Name> " parameter when I type it in the cmd. Now I'm totally lost.

[chulett]

What's going to schedule the script?

I'm assuming you already have the job working outside of this script, yes? So if that's true and you already know the values the parameters need to have to run the job, if you still have an issue then it is specific to the script itself.

[syang@collaborative.com]

The Windows Task Scheduler is going to run the job at a specific time for now. The Job runs correctly. The problem I'm having now is the script itself. I've narrowed it down by putting an echo statement after the "SET TGTSSODSDSN=<Target DSN>" line, and the echo doesn't print in the log. I don't know what's wrong. I run the script as followed:

c:\script_folder\script_job.bat host username password project DSN_Name UserName Password JobName

Nothing happens after I execute it.