Datastage routine to run SQL queries directly

[TonyInFrance]

I need to implement the above requirement of calling SQL queries using a server routine. I'm aware of the routine ExecDOS and ExecSH which could call a batch file which in turn can call a script file but in this case the errors aren't detected.

Is there a routine that can execute an SQL statement taking in user name, password, DSN and SQL query as input? Ideally it should give as output 1 if the query successfully executes or 0 otherwise.

I vaguely remember coming across a routine called ExecSQL on the lines of ExecDOS but am not totally sure.

Regards

T

[priyadarshikunal]

I need to implement the above requirement of calling SQL queries using a server routine. I'm aware of the routine ExecDOS and ExecSH which could call a batch file which in turn can call a script file but in this case the errors aren't detected.

Is there a routine that can execute an SQL statement taking in user name, password, DSN and SQL query as input? Ideally it should give as output 1 if the query successfully executes or 0 otherwise.

I vaguely remember coming across a routine called ExecSQL on the lines of ExecDOS but am not totally sure.

Regards

T

the simplest way would be to capture the ScreenIO and parse it to detect any kind error pattern and if found decide accordingly.

[chulett]

You would have to write a batch file to call your command line query tool of choice and then use ExecDOS directly or DSExecute in a routine to execute it.

[TonyInFrance]

You would have to write a batch file to call your command line query tool of choice and then use ExecDOS directly or DSExecute in a routine to execute it. ...

That's what I've done now. However the batch file always returns a positive result since even if the SQL query fails as far as DOS is concerned the command executes without an error. Thus I cannot use this batch file in a datastage job sequence and branch out on other stages depending on its result.

[varaprasad]

Write a Shell script and do all the error handling in the script itself and call that from your Server Routine.

[TonyInFrance]

Did that. I'm getting a log that looks like the following:

TEST #1
*******

Arg1 = KZOIODEV
Arg2 = kzsoint
Arg3 = sointpwd
Arg4 = F:\Datas\Sell_Out\Scripts\SQL\SELECT_ID_TRAITEMENT_NEXTVAL.sql

Test completed.

DSLogInfo called from : ExecSQL
Message to be logged is...
> Executed command: SQLPLUS kzsoint/sointpwd@KZOIODEV @F:\Datas\Sell_Out\Scripts\SQL\SELECT_ID_TRAITEMENT_NEXTVAL.sql
> *** Output from command was: ***
>
> SQL*Plus: Release 10.2.0.3.0 - Production on Jeu. Avr. 30 16:55:19 2009
>
> Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
>
> NEXTVAL
> ----------
> 5
>
> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
> With the Partitioning, OLAP and Data Mining options
>

Result =
SQL*Plus: Release 10.2.0.3.0 - Production on Jeu. Avr. 30 16:55:19 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


NEXTVAL
----------
5

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Now the problem is in the datastage routine how do I extract just the 1 value 5 from this output and direct the same as the output for my routine?

[Sainath.Srinivasan]

Are you going to call the routine once per job or once per record ?

[TonyInFrance]

At the moment once per job. however i would like it to be dynamic so i can use this routine later to call any SQL query.

[varaprasad]

Call the DSExecute function instead of ExecDOS or whatever to call the Shell script and parse the output to read the value you need. Use some smart logic to read the value from output.

To keep your script dynamic, keep the SQL in a separate file, as you are doing currently, and pass the SQL script name as parameter to the shell script from DS Routine.

[chulett]

You would have to write a batch file to call your command line query tool of choice and then use ExecDOS directly or DSExecute in a routine to execute it. ...

That's what I've done now. However the batch file always returns a positive result since even if the SQL query fails as far as DOS is concerned the command executes without an error. Thus I cannot use this batch file in a datastage job sequence and branch out on other stages depending on its result.

You need a smarter script. Redirect the output of the sql session to a file and then grep the output for errors, otherwise you are right that all you'll catch are errors launching the query tool, not problems with the sql itself.

[ray.wurlod]

Search the forum for "BCI". You don't need any form of script - just a valid ODBC connection. On Windows there should be no licensing issues.

[sbass1]

You would have to write a batch file to call your command line query tool of choice and then use ExecDOS directly or DSExecute in a routine to execute it. ...

That's what I've done now. However the batch file always returns a positive result since even if the SQL query fails as far as DOS is concerned the command executes without an error. Thus I cannot use this batch file in a datastage job sequence and branch out on other stages depending on its result.

I can't test this since we have DS on Unix. However, AFAIK the Windows return code is ERRORLEVEL. So, what does DS do if you return 0 vs. non-zero ERRORLEVEL from your batch script? This would be easy to test - a simple batch file like:

Code: Select all

set ERRORLEVEL=%1%

then call from DS as ScriptName.bat 0 vs. ScriptName.bat 123.

If DS returns an error if your batch script returns a non-zero return code, then you just have to set ERRORLEVEL accordingly.

See viewtopic.php?t=126216&highlight= for related issues under Unix.

HTH...