User Defined Query File in DRS Stage

JKenklies · Post by **JKenklies** » Tue Dec 30, 2008 9:20 am

Hi,

using the seach, I found this thread:
viewtopic.php?t=86881

but since I'm not using an oracle stage and the thread above seems to be outdated, I apologize for this new thread.

Based on input Server Job parameters, I need to generate different (completely different) where-statements. Former I used statements like:

select * from MyTable WHERE FIELD1 = CASE WHEN #param1# <> 0 THEN #param1# ELSE FIELD1 END

But this clause has huge impact on the source system even if param1 equals zero. The where clause for Field1 is rarely needed so I want to generate the whole SQL to avoid using unneeded clauses.

I'm using a DRS stage connecting via ODBC to the database. (This is how we connect to the DB almost everywhere in the ETL project.)

Trying to use the sql file confuses me, I thought I had to put the path of the SQL-File into the filed where normally the statement itself is entered to. I switched the "Generate SQL" option to "Sqlfile", but the stage oviously tries to interpret the path itself as SQL statement.

This is the Path I'm using:
D:\Ascential\DataStage\Projects\EPM\SrcFiles\test\generated.sql
I tried it in several formats like with quotes, forward slashes, absolute an relative paths...

I'm lost.

Is there any trick, workaround or maybe another stage enabling me to use an SQL file to select data from a DB2 UDB 8.2 ?

Thank you for reading.

MarkB · Post by **MarkB** » Tue Dec 30, 2008 10:09 am

I'm not sure about the DRS stage - don't use it - but I use this with a DB2 stage, and under the SQL tab, I use Load SQL from a file at run time, and under that I specify {FILE}/pathnameofsqlfile. At least in this stage, you need to specify {FILE} before the path/filename of the SQL file in order for the stage to properly function.

chulett · Post by **chulett** » Tue Dec 30, 2008 10:11 am

Yes, that's how it is meant to be used - you put the path to the "SQL file" in the user-defined SQL area rather than the sql itself. However, I never had success with it in the DRS stage and there's no such option in the ODBC stage itself... only ever got it to work for the OCI stage.

MarkB · Post by **MarkB** » Wed Dec 31, 2008 8:29 am

chulett wrote:Yes, that's how it is meant to be used - you put the path to the "SQL file" in the user-defined SQL

This sounded interesting, so I gave it a shot using the Dynamic RDBMS stage and got it working.

Under Stage - General Tab I selected the DBMS Type (in my case, DB2)
I filled in the proper connection information (Connection Name, User ID and Password.

Under Output - > General Tab, I specify the table name, and the Query Type is set to User-defined SQL query file.

I have the correct columns loaded, and under SQL -> User-Defined I have entered the following: {FILE}/#SQLFILE#.

It ran fine. Interesting to note, and I'm not sure if this is a bug or expected behavior - my parameter SQLFILE contained the fulll UNIX path/filename to the SQL file, like this: /mypath/mysqlfile.sql . If I specify just {FILE}#SQLFILE#, the job cannot find the SQL file. You need to specify the slash between the {FILE} and the parameter for it to work.

Anyway, hope this helps.

chulett · Post by **chulett** » Wed Dec 31, 2008 8:57 am

Interesting... I'd never seen that syntax until you posted it but on digging back through all of the pdf documents, most of the database stages mention it:

If your entry begins with {FILE}, the remaining text is interpreted as a pathname, and the contents of the file supplies the text for the query.

All without mention of the "SQL File" option which should obviate the need for this prefix, meaning you can use it with the 'User defined SQL' option.

For what it's worth, the documentation for the ODBC and RDBMS stages do not mention this as a possibility. And as noted, the "SQL File" option in the OCI stages doesn't need this to function.

JKenklies · Post by **JKenklies** » Fri Jan 02, 2009 5:42 am

You guys rock!

Using the {FILE} Syntax seems to work. (I still have to correct my 40 columns set, but the error message shows me the SQL from the file.)

Mark mentioned that he had to specify a slash in front of the pathname variable. I tried some syntax and found out that it simply doesn't matter what character you take, the filename has to start just one character after {FILE}. I'm using a space now.

For me, on windows, it works using any file qualification that is allowed by the operating system:

{FILE} D:\Ascential\DataStage\Projects\EPM\SrcFiles\test\generated.sql
{FILE} \Ascential\DataStage\Projects\EPM\SrcFiles\test\generated.sql
{FILE} .\SrcFiles\test\generated.sql
{FILE} SrcFiles\test\generated.sql

Thank you for that amazing fast help!