Running a SQL from dos command

sunil_acc · Post by **sunil_acc** » Thu Oct 04, 2007 3:10 pm

Here goes one more stuff.
We can write various sql's and run them in routine using "EXECUTE", but is there a way to run the same command (TCL) at dos prompt by any means?

I gave a thought to this and can think of following options:
1. Write a routine and execute it from DOS Prompt (cant find and way out yet).
2. Write a basic program and then compile it and use the exe (can we produce a exe here) to run the desired sql.

Like to have some suggestions (and if somebody has done anything like this, then his implementation technique) for this problem!

ray.wurlod · Post by **ray.wurlod** » Thu Oct 04, 2007 4:29 pm

1. No.
2. No.

Any SQL to be run from the DOS command line would have to be run from a BAT file - DataStage BASIC does not create an "executable" in the operating system sense - all DataStage BASIC code executes within dssh. You can regard dssh as a virtual machine if you like. However, routines can not be executed directly - just as in C they need a "main" program that invokes them. You do not have the tools/skills to construct one of these in the DataStage BASIC environment.

kduke · Post by **kduke** » Fri Oct 05, 2007 8:10 am

Do a search for Dwn_ExecTCL.exe.

sunil_acc · Post by **sunil_acc** » Mon Oct 08, 2007 6:09 pm

kduke wrote:Do a search for Dwn_ExecTCL.exe.

KIM,
Is this a utility coded by you or its a datastage product utility and comes with a standaed package?

thanks!

narasimha · Post by **narasimha** » Mon Oct 08, 2007 6:48 pm

sunil_acc wrote:
KIM,
Is this a utility coded by you or its a datastage product utility and comes with a standaed package?

thanks!

Does not come inbuilt with Datastage, It's one of Kim's creation.

kduke · Post by **kduke** » Mon Oct 08, 2007 8:51 pm

It is a little program written by me in VB. They may post source code for something like this in a members only area. If that would be something you might want then ask Dennis the Editor.

sunil_acc · Post by **sunil_acc** » Tue Oct 09, 2007 8:40 am

kduke wrote:It is a little program written by me in VB. They may post source code for something like this in a members only area. If that would be something you might want then ask Dennis the Editor.

Thanks for help KIM!
i need that code, to understand how that works!if you can provide a way out then it will be a great help!

thanks again!

kduke · Post by **kduke** » Tue Oct 09, 2007 8:50 am

I give a lot of code and jobs away now. Dennis wants to hold back on some of this as an incentive to become a member. Depends on the demand. We may give away all my tools if you become a member. Need to make this site at least break even.

CharlesNagy · Post by **CharlesNagy** » Fri Oct 12, 2007 6:54 am

Just curious as to why you need to run the sql query from the Dos prompt. If you can describe why you need to do it that way, there may be another solution.

sunil_acc wrote:
kduke wrote:It is a little program written by me in VB. They may post source code for something like this in a members only area. If that would be something you might want then ask Dennis the Editor.
Thanks for help KIM!
i need that code, to understand how that works!if you can provide a way out then it will be a great help!

thanks again!

sunil_acc · Post by **sunil_acc** » Mon Oct 15, 2007 1:46 pm

CharlesNagy wrote:Just curious as to why you need to run the sql query from the Dos prompt. If you can describe why you need to do it that way, there may be another solution.

sunil_acc wrote:
kduke wrote:It is a little program written by me in VB. They may post source code for something like this in a members only area. If that would be something you might want then ask Dennis the Editor.
Thanks for help KIM!
i need that code, to understand how that works!if you can provide a way out then it will be a great help!

thanks again!

I want to query DS_JOBS abd DS_ROUTINES to make a list of the jobs and routines in a particular category/project. We have a lot of ways to get the jobs name (dsjob, dssearch) but i am still not aware os any command that can list all the routines (besides querying DS_ROUTINES).

Hop i make my point clear

CharlesNagy · Post by **CharlesNagy** » Wed Oct 17, 2007 5:05 am

Yes, I see what you mean. Bit tricky that. Obtainable readily enough within the Datastage environment, but from outside...

sunil_acc wrote:
CharlesNagy wrote:Just curious as to why you need to run the sql query from the Dos prompt. If you can describe why you need to do it that way, there may be another solution.

sunil_acc wrote: Thanks for help KIM!
i need that code, to understand how that works!if you can provide a way out then it will be a great help!

thanks again!

I want to query DS_JOBS abd DS_ROUTINES to make a list of the jobs and routines in a particular category/project. We have a lot of ways to get the jobs name (dsjob, dssearch) but i am still not aware os any command that can list all the routines (besides querying DS_ROUTINES).

Hop i make my point clear

ray.wurlod · Post by **ray.wurlod** » Wed Oct 17, 2007 9:08 am

Attach to project directory and use dssh command to execute DataStage/SQL query (which must be surrounded by double quotes).

Code: Select all

CD C:\Ascential\DataStage\Projects\MyProject
C:\Ascential\DataStage\Engine\bin\dssh "SELECT JOBNO FROM DS_JOBS WHERE NAME = 'MyJob';"