Run database query in datastage with no inputs or outputs

Question

Relatively new to datastage, quite possibly a stupid question.

From datastage, I want to run a database query against a SQL Server database. The query is a delete query with a hardcoded WHERE clause (not my decision).

What I cannot figure out is how to run a query through the ODBC connector without inputs or outputs. If I don't link the connector to either an input or output, the 'Usage' section of the ODBC connector does not appear.

Is it possible to make the 'Usage' section appear without linking inputs/outputs? If not, is there another alternative in datastage to accomplishing what I have described?

score 3 · Accepted Answer · answered Oct 16 '14 at 13:08

DataStage only writes to a database when data comes from a link.

What you can do is :

Put a Row Generator Stage in your Job and configure it to generate one row (with one column)
Link that Stage to your ODBC database target
Put your static query on the link

You don't have to use data on the generated row, it's just necessary to trigger action on the target.

score 2 · Answer 2 · answered Jun 17 '16 at 20:32

I faced the same issue, and now achieved it by designing the job as below,

Row_generator -> Transformer -> copy_stage -> DB_connector

Row_generator- define a column to generate(need not map to output in transformer stage)

Transformer - Add an output column say "dummy" and hard code it as "1" and RCP should be unchecked.

copy_stage - Dont map anything to the output link, just keep RCP enabled.

DB_connector - in the query box , mention delete statement as " delete from #Schema#.#Table# where 1=orchestrate.dummy"

In the above query we are using the condition 1=orchestrate.dummy which is always TRUE. Let me know if it works !! Cheers.

score 1 · Answer 3 · edited Jun 25 '16 at 18:19

1

Connect the ODBC connector stage to a copy stage
Code a dummy select query ( Oracle , you can use "select 1 as dummy from dual")
Give the dummy col name in the columns with with a datatype
Plug in your Delete query in Before or After SQL of the ODBC connector stage

I have production jobs running with the same requirement . Should work as smooth as wine :)

edited Jun 25 '16 at 18:19

Bhargav Rao

50,140
28
121
140

answered Jun 25 '16 at 18:17

balaji karthik

51
1
6

score 0 · Answer 4 · edited Jun 17 '16 at 19:55

0

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Security No], Customer.DOB, Customer.IDNo, Customer.ContactNumber, Customer.cnu' at line 1

edited Jun 17 '16 at 19:55

Vojtech Ruzicka

16,384
15
63
66

answered Jun 17 '16 at 19:03

user3413996

11

Run database query in datastage with no inputs or outputs

4 Answers4