Topic
  • No replies
MoatazElSayed
MoatazElSayed
2 Posts

Pinned topic Executing Queries Dynamically

‏2014-01-19T20:13:52Z |

The required logic is to generate a query by parsing XML file and execute it dynamically using DataStage. We used User-defined SQL statements for executing queries on the database (This feature is in DB2 connector) and following techniques are tested to achieve this logic.

1. Execute a query sent from a file (Job Name--> CopyOfWrite_From_File):

sequential file,

transformer,

db2 connector stage (ORCHESTRATE. column name),

peek stage for rejects 

Output:

 An unexpected token "?" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  SQLSTATE=42601

Trail Failed

2. Execute a query selected from another table (TRX_TABLE) (Job Name-->Write_From_DB2):

db2 connector stage,

copy,

d2 connector stage (ORCHESTRATE. column name)

peek for rejects

Note: Also tried writing the query with escaping special characters, still got rejected

 

SAME ISSUE AS THE FIRST INVESTIGATION

Output:

 An unexpected token "?" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  SQLSTATE=42601

Trail Failed

3. Execute a query sent as parameter (Job Name-->Write_Parameters):

row generator,

transformer,

filter,

db2 connector stage (#Parameter_name#),

peek stage for rejects

Output:

Trail Succeeded

The limitation of this technique is that we need to write the query and store it first (I/O operation) after that read it as parameter and pass it to another DataStage job.

 

 

Question: Is there a way to implement this logic in one job instead of having I/O operation in between (writing to a database and reading it as parameter and send this parameter to another job to execute it)?

 

Regards,

Moataz