Page 1 of 2
USER DEFINED SQL FILE WITH DRS STAGE
Posted: Mon Sep 18, 2006 2:38 pm
by anu123
Hi all:
I am trying to read source data from Oracle table using DRS.Iam unable to put my complete USER defined SQL in 'SQL' tab. I think there some limitations on size/no of lines that we can put over there.
The next option I know is..User Defined SQL file. Can some one help me on how to call a "User-Defined SQL file"?
thanks in advance.
Posted: Mon Sep 18, 2006 3:17 pm
by meena
Hi,
I think there is no such limitations.How many lines query it is? And is it showing any error when you are trying put User-defined SQL. If yes can you post the error.
Posted: Mon Sep 18, 2006 3:22 pm
by thumsup9
For using User Defined SQL file you will have to provide its path. Also, whats the complexity of your SQL and how many lines of code is it?
Posted: Mon Sep 18, 2006 3:39 pm
by kris007
There is a limit of 700 lines for (user-defined) sql in OCI stage and I think it applies to all other DB stages though not sure since never tried such a big user-defined sql. If your code exceeds that limit then running a sql script is another choice.
Posted: Mon Sep 18, 2006 3:39 pm
by anu123
thumsup9 wrote:For using User Defined SQL file you will have to provide its path. Also, whats the complexity of your SQL and how many lines of code is it?
thank you Meena & thumsup9 for your replies.
My sql is not very complex...but it has got pretty large no. of line.I am unable to paste complete/(all lines) SQL in OUTPUT --> SQL tab. for example consider below is my complete SQL.
"SELECT A,B,C,D,E FROM TABLE WHERE F=1 AND G=2"
When I copy & paste it.....it looks like the below one...
"SELECT A,B,C,D,E FROM TABLE WHERE F" which is wrong.
..I can run this comlete SQL in a toad session.
Posted: Mon Sep 18, 2006 3:42 pm
by kris007
kris007 wrote:There is a limit of 700 lines for (user-defined) sql in OCI stage and I think it applies to all other DB stages though not sure since never tried such a big user-defined sql. If your code exceeds that limit then running a sql script is another choice.
Posted: Mon Sep 18, 2006 3:43 pm
by anu123
kris007 wrote:There is a limit of 700 lines for user-defined sql in OCI stage and I think it applies to all other DB stages though not sure since never tried such a big user-defined sql. If your code exceeds that limit then running a sql script is another choice.
thanks for info,Kris.
Could you please explain a bit more on how to call user-defined scripts from DRS?
Posted: Mon Sep 18, 2006 3:50 pm
by kris007
You cannot do it from DRS stage. That is my understanding. Last time I had to execute a 3000line sql code, I had to write a routine to connect to oracle and execute the sqlscript. I can paste that routine here if you are interested in it.
Posted: Mon Sep 18, 2006 3:56 pm
by anu123
kris007 wrote:You cannot do it from DRS stage. That is my understanding. Last time I had to execute a 3000line sql code, I had to write a routine to connect to oracle and execute the sqlscript. I can paste that routine here if you are interested in it.
Could you please paste that routine here for me, Kris.
Thanks in advance.
Posted: Mon Sep 18, 2006 4:06 pm
by kris007
I'll have to dig it out from my notes. May be later in the evening today.
Posted: Mon Sep 18, 2006 4:12 pm
by kris007
Here you go. It is a copy of the routine when I started coding intially. It doesn't handle any errors with in the sql script. I shall paste the whole code later. This should give you a start.
Code: Select all
* Routine
*---------------------------------------------------------------------
* Description: To run SqlScript
*---------------------------------------------------------------------
* Written by:Kris
*---------------------------------------------------------------------
$INCLUDE DSINCLUDE JOBCONTROL.H
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Equate RoutineName To "Routine"
Arg1 = USERNAME
Arg2 = PASSWORD
Arg3 = INSTANCE
Arg4 = PATH
InputArg = "sqlplus":' ':USERNAME:'/':PASSWORD:'@':INSTANCE:' ':'@':PATH
*---------------------------------------------------------------------
* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).
*---------------------------------------------------------------------
If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to
execute.", "")
GoTo ErrorExit
End
*----------------------------------------------------------------------
* Else, call support routine that executes a UNIX command.
*----------------------------------------------------------------------
Call DSExecute("UNIX", InputArg, Output, SystemReturnCode)
*-----------------------------------------------------------------------
* Log any and all output as an Information type log message,
* unless system's return code indicated that an error occurred,
* when we log a slightly different Warning type message and set
* ErrorCode = 1 (job will abort if called as Before routine).
*------------------------------------------------------------------------
If Trim(Output) <> "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "<L>*** Output from
command was: ***<L>", ""):Output
End Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "<L>*** No output from
command ***", "")
End
If SystemReturnCode = 0 Then
Message = DSRMessage("DSTAGE_TRX_I_0039", "Command executed
successfully.", ""):Message
Call DSLogInfo(Message, RoutineName)
End Else
Message = DSRMessage("DSTAGE_TRX_I_0040", "Error when executing
command. ", ""):Message
GoTo ErrorExit
End
*-----------------------------------------------------------------------
* Exit with no error.
*-----------------------------------------------------------------------
ErrorCode = 0
GoTo NormalExit
*-----------------------------------------------------------------------
* Exit with error, logging warning message first.
*-----------------------------------------------------------------------
ErrorExit:
Call DSLogWarn(Message, RoutineName)
ErrorCode = 1
NormalExit:
Ans=ErrorCode
Posted: Mon Sep 18, 2006 4:12 pm
by anu123
kris007 wrote:I'll have to dig it out from my notes. May be later in the evening today.
thanks Kris.
Posted: Mon Sep 18, 2006 4:23 pm
by kris007
In case you just missed it, I have posted it before your post.
Posted: Mon Sep 18, 2006 4:35 pm
by anu123
kris007 wrote:In case you just missed it, I have posted it before your post.
I got it. Thank you very much.
Posted: Wed Jun 20, 2007 2:28 pm
by vnspn
Hi,
The Routine that Krish has posted above is for executing a SQL from an UNIX box. Could anyone please let us know what would be the change in command format for executing an SQL when the DataStage server in a Windows box.
The first argument for the DSExecute function needs to be replaced by "NT" instead of "UNIX". What would be the format in which the second argument needs to be passed?
Thanks.