USER DEFINED SQL FILE WITH DRS STAGE

anu123 · Post by **anu123** » Mon Sep 18, 2006 2:38 pm

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.

meena · Post by **meena** » Mon Sep 18, 2006 3:17 pm

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.

thumsup9 · Post by **thumsup9** » Mon Sep 18, 2006 3:22 pm

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?

kris007 · Post by **kris007** » Mon Sep 18, 2006 3:39 pm

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.

anu123 · Post by **anu123** » Mon Sep 18, 2006 3:39 pm

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.

kris007 · Post by **kris007** » Mon Sep 18, 2006 3:42 pm

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.

anu123 · Post by **anu123** » Mon Sep 18, 2006 3:43 pm

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?

kris007 · Post by **kris007** » Mon Sep 18, 2006 3:50 pm

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.

anu123 · Post by **anu123** » Mon Sep 18, 2006 3:56 pm

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.

kris007 · Post by **kris007** » Mon Sep 18, 2006 4:06 pm

I'll have to dig it out from my notes. May be later in the evening today.

kris007 · Post by **kris007** » Mon Sep 18, 2006 4:12 pm

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

anu123 · Post by **anu123** » Mon Sep 18, 2006 4:12 pm

kris007 wrote:I'll have to dig it out from my notes. May be later in the evening today.

thanks Kris.

kris007 · Post by **kris007** » Mon Sep 18, 2006 4:23 pm

In case you just missed it, I have posted it before your post.

anu123 · Post by **anu123** » Mon Sep 18, 2006 4:35 pm

kris007 wrote:In case you just missed it, I have posted it before your post.

I got it. Thank you very much.

vnspn · Post by **vnspn** » Wed Jun 20, 2007 2:28 pm

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.