Datastage routine to run SQL queries directly
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
Datastage routine to run SQL queries directly
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
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
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: Datastage routine to run SQL queries directly
the simplest way would be to capture the ScreenIO and parse it to detect any kind error pattern and if found decide accordingly.BASU wrote: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
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
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.chulett wrote: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. ...
-
- Premium Member
- Posts: 34
- Joined: Fri May 16, 2008 6:24 am
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
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?
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?
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 288
- Joined: Tue May 27, 2008 3:42 am
- Location: Luxembourg
-
- Premium Member
- Posts: 34
- Joined: Fri May 16, 2008 6:24 am
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.
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.
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.BASU wrote: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.chulett wrote: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. ...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:BASU wrote: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.chulett wrote: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. ...
Code: Select all
set ERRORLEVEL=%1%
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...
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues