User-Defined SQL File

[ramnishgupta]

I have an ETL job where i need to use a SQL file in the User Defined SQL section of a DRS Stage for a DB2 Database. In the DRS Stage Under the General Tab -> Query Type -> User-Defined SQL query file is selected and i give the SQL file path under User-Defined section in the SQL tab.

{File}/home/acrwrb2/test.sql

I have tried giving the path with and without {File} option but keep getting a message

=========================================
Executing SQL statement '{File}/home/acrwrb2/test.sql'

Error executing statement '{File}/home/acrwrb2/test.sql'. See following database message for details.

[IBM][CLI Driver] CLI0118E Invalid SQL syntax. SQLSTATE=37000

Error occurred during link open processing.
============================================

Looks like it is not executing the SQL in the file. I also tried giving the path in the table name section but get the same error.

Thanks

[chulett]

Put the full pathame to the file in the User-Defined SQL area without any {File} qualifier and you should be fine. Note that "file" SQL cannot have job parameter references in it, however.

[ramnishgupta]

Still does not work. Does the .SQL file have to be in a specific format ??

==========================================
Executing SQL statement '/home/acrwrb2/test.sql'

Error executing statement '/home/acrwrb2/test.sql'. See following database message for details.

[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "/" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT". SQLSTATE=42601

Error occurred during link open processing.

=========================================

[chulett]

It just needs to be valid sql. I haven't had any problems doing this in the OCI stage but don't have DB2. Are you sure your SQL is valid? Care to post it?

[ramnishgupta]

SELECT DISTINCT 'X' FROM sysibm.sysindexes;

for testing purpose i have a simple sql in the .sql file. This sql executes on the command prompt

[chulett]

Try it without the semicolon on the end.

[ramnishgupta]

Same Message. Looks like it is not substituting the query in the file under user defined section because it says Executing SQL statement '/home/acrwrb2/test.sql' and is not executing the actual SQL.

==========================================
Executing SQL statement '/home/acrwrb2/test.sql'

Error executing statement '/home/acrwrb2/test.sql'. See following database message for details.

[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "/" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT". SQLSTATE=42601

Error occurred during link open processing.

=========================================

[chulett]

Looks that way. Like I said, this works just fine in OCI and I don't have DB2 to test it. Best to open a case with your support provider if no-one else chimes in on this.