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?
Regarding RCP Enabled Job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I must presume that your selection was of the form
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.
Code: Select all
Select #Parameter#
From #Parameter# A,
#Parameter# B
Where
A.column=B.column
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Re: Regarding RCP Enabled Job
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
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