Regarding RCP Enabled Job

[manojbh31]

I have multi instance job which is RCP Enabled. Source query is parameterized as below
Select #Parameter#
From #Parameter A#,
#Parameter B#
where
A.column=b.column

Select has one column and is derived after joining 2 tables and the data of this column is stored in Dataset. I need to use this dataset to delete the data from table.

I read couple of posts where it says the column on which i need to delete should be as parameter and use modify stage to get.

Can it possible to use the data which is present in dataset to delete the data from table?

[ray.wurlod]

I must presume that your selection was of the form

Code: Select all

Select #Parameter#
From #Parameter# A,
#Parameter# B
Where
A.column=B.column 

rather than what you provided.

You can use the data from the Data Set, materialized via the Modify stage, to delete, provided that the column is identified as Key in DataStage metadata, since that is what drives putting the column into the WHERE clause of the generated DELETE statement.

[manojbh31]

Hi Ray,

The data of column in the select statement (which is passed as parameter) is stored in dataset. How can i use this column from dataset and delete the data from a particular table.

[Thomas.B]

If you have to extract the data you want to delete to a DataSet, you can simply use the database connector as output: on the usage menu, set the "Write mode" value to "Delete"
If the column you store in the DataSet is defined as a key, the data will be deleted.

[manojbh31]

I cannot use DB2 Connector as output in same job, because i am selecting the data from same table by joining with one table. So i need to store this data in dataset and in next job i need to use this dataset and delete the data. As the job is RCP Enabled i cannot make this column as key in dataset

[Thomas.B]

Did you try to delete the rows using a request like this?

DELETE FROM #Parameter A#
WHERE #Parameter# IN
(
SELECT #Parameter#
FROM #Parameter A#,#Parameter B#
WHERE A.column=B.column
)

Or if that's not too restrictive:

DELETE A FROM #Parameter A# A,#Parameter B# B
WHERE A.column=B.column

Using a Teradata database, the rows are deleted
If you have to execute that requests on a DataStage job, you can create a job with that design:

Row Generator ---> DB2 Connector

On the Row Generator, set the "number of records" value to "0", on the DB2 Connector set the "Write mode" value to "User-defined SQL" and use one of the 2 the requests above as a statement

[Amin]

In connector use Before SQL and do following;
CREATE TABLE T1
(
Column_Name Type PRIMARY KEY
)
;
INSERT INTO T1
SELECT Column
FROM Table_1 , Table_2
WHERE Table_1.column = Table_2.column;

Then extract data to Data Set