Is there a way to use User Activity Variables to store SQL in Datastage

Question

I am considering using RCP to run a generic datastage job, but the initial SQL changes each time it's called. Is there a process in which I can use a User Activity Variable to inject SQL from a text file or something so I can use the same datastage?

I know this Routine can read a file to look up parameters:

Routine = ‘ReadFile’
vFileName = Arg1
vArray = ”
vCounter = 0
OPENSEQ vFileName to vFileHandle
Else Call DSLogFatal(“Error opening file list: “:vFileName,Routine)
Loop
While READSEQ vLine FROM vFileHandle
vCounter = vCounter + 1
vArray = Fields(vLine,’,’,1)
vArray = Fields(vLine,’,’,2)
vArray = Fields(vLine,’,’,3)
Repeat
CLOSESEQ vFileHandle
Ans = vArray
Return Ans

But does that mean I just store the SQL in one Single line, even if it's long? Thanks.

Answer 1

Why not just have the SQL within the routine itself and propagate parameters?

I have multiple queries within a single routine that does just that (one for source and one for AfterSQL statement)

This is an example and apologies I'm answering this on my mobile!

InputCol=Trim(pTableName)

If InputCol='Table1' then column='Day'
If InputCol='Table2' then column='Quarter, Day'

    SQLCode = ' Select Year, Month, '
    SQLCode := column:", Time, "
    SQLCode := " to_date(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'), "
    SQLCode := \ "This is example text as output" \
    SQLCode := "From DATE_TABLE"

    crt SQLCode

I've used the multiple encapsulations in the example above, when passing out to a parameter make sure you check the ', " have either been escaped or are displaying correctly

Again, apologies for the quality but I hope it gives you some ideas!

Answer 2

You can give this a try

1. As you mentioned ,maintain the SQL in a file ( again , if the SQL keeps changing , you need to build a logic to automate populating the new SQL)

2. In the Datastage Sequencer , use a Execute Command Activity to open the SQL file eg : cat /home/bk/query.sql

3. In the job activity which calls your generic job . you should map the command output of your EC activity to a job parameter

so if EC activity name is exec_query , then the job parameter will be

            exec_query.$CommandOuput

4. When you run the sequence , your query will flow from

   SQL file --> EC activity-->Parameter in Job activity-->DB stage( query parameterised)

Answer 3

Has you thinked to invoke a shellscript who connect to database and execute the SQL script from the sequential job? You could use sqlplus to connect in the shellscript and read the file with the SQL and use it. To execute the shellscript from the sequential job use a ExecCommand Stage (sh, ./, ...), it depends from the interpreter.

Other way to solve this, depends of the modification degree of your SQL; you could invoke a routine base who handle the parameters and invokes your parallel job.

The principal problem that I think you could have, is the limit of the long of the variable where you could store the parameter.

Tell me what option you choose and I could help you more.