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)