DSXchange

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

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.

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.

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

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?

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

Try it without the semicolon on the end.

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.

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

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.