Delete when there are unmatched rows

[srilaxmi]

Hi,

I have a datastage job(job has one source UDB stage and one target UDB stage)which is used to delete records from a table when a condtion matches.(delete from table1 where <condition>)

When i run the job, i am getting FATAL error for the records in target table which doesnot match the delete condition.

Note: There are some records which matches the delete condition and they are deleted correctly.

Example:
source table count: 10
target table count:50

Among 10 records from source, 5 records satisfies the delete condition and other 5 records did not.

Now if i run the job its executed successfully, by giving below FATAL error for the 5 records which are not satisfied the delete condtion.

Code: Select all

Error in row #<row number>
Primary Error:
sqlcode=100, state=02000, message=[IBM][CLI Driver][DB2/AIX64] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000

Datastage version: 7.5.1
Database : DB2

Can anyone faced the similar issue? Please reply me on how to over come this FATAL error msg.

Let me know if you need more info.

TIA,
Sri

[nikhilanshuman]

Hi
The delete fails because the key column value from source is not found in the target.So,we have to make sure that only those values of source are passed to the target which exist in the target also.

Try following 2 solutions :

<b>Solution 1 : </b> if the source and target tables are in same database,do a join between these two tables in source query itself to get only new records.

Eg : Source table is say Tab1
target table is say Tab2

The delete key is say key1

The select query will be :

Select T1.col1,T1.col2,T1.col3 from Tab1 T1,Tab2 T2 where T1.key1=T2.key1

You will get only existing records and your delete statement will never fail.

<b>Solution 2: </b> If the source and target tables lie in different database,use join stage between the source and target.

The join stage will perform inner join between the two tables tab1 and tab2.in the output of join,put your target UDB.The UDB stage will never fail.