I am just posting a simple scenario of my problem which is typical then this actually.

there are 2 tables PERSON & CUSTOMER

PERSON

FNAME LNAME SYNC
A B N
A C N
D E N
F G N
H I N
J K N

CUSTOMER

FNAME LNAME
A B
D E
H I
Now i have to update SYNC field in PERSON as Y for the records which exists in table customer, for eg these rescords exist in person table also
FNAME LNAME
A B
D E
H I

i want to make SYNC field as Y for these records in PERSON table.

I tried this way i wrote this query :

SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON)

this will tell us if a particular FNAME exist in customer table or not, now to update the SYNC field in PERSON i tried using update query as

UPDATE PRAGS.PERSON SET SYNC='Y' WHERE EXISTS(SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON))

this updates all the SYNC as Y because as asoon as SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN (SELECT FNAME FROM PRAGS.PERSON) evalutaes to be truew it updates all sync as Y. i also tried and statemnet but it didn't work. please suggest me a way to work it out

Also this is just a small example of what i want to do. Actual person table and customer table will contain more than 25 lac records. so how to do it optimally.

i want to do it using SQL query.

i have also done this using a java program below, this solved my program but i dont know how to do it optimally because recordset can not hold 25 lac records , thsi is not viable.

String str = "SELECT FNAME FROM PRAGS.PERSON";

rs = stmt.executeQuery(str);

while(rs.next()){
String cfName="";
String fName = rs.getString(1);
//String lName = rs.getString(2);

//rs1 = stmt.executeQuery("SELECT FNAME, LNAME FROM PRAGS.CUSTOMER WHERE (FNAME, LNAME) IN ("+fName+","+lName+")");
rs1 = stmt1.executeQuery("SELECT FNAME FROM PRAGS.CUSTOMER WHERE (FNAME) IN ('"+fName+"')");
while(rs1.next()){
cfName = rs1.getString(1);
}

if(!cfName.equals("")){
stmt1.executeUpdate("UPDATE PRAGS.PERSON SET SYNC = 'Y' WHERE FNAME ='"+fName+"'");
}

try {
if(rs1!=null){
rs1.close();
}
} catch (SQLException e) {
System.out.println("Exception is "+e.getMessage());

}

}

or
Depending on the size of the tables, the existing indexes, and the DB2 version and platform, one of these might be (much) more performant than the other; use EXPLAIN to find out before running the query.

Thanks for the reply, it worked.

Also, to make it run a bit faster you could add and index that contains the columns sync, lname,fname. and then add the "AND SYNC = 'N'" to the where clause of your update statement. Since you are keeping this in sync on some interval, you shouldn't have that many N's as the sync value, which would help with the speed of the query. We have a similar flag, that is almost always >99% Y and we only want to process when the column contains N, on a table with a few hundred million rows. The index eats up some space, but the query performs very well.

Dave

Be aware that the index also gets updated on every single INSERT, UPDATE, or DELETE in the base table (PERSON in your case). If this is substantial, those database accesses will be negatively impacted by that index. As always, there's a trade-off to make or a price to pay!

Actually, there *is* an other option here which improves your UPDATE query (when you include the "AND SYNC = 'N'") without the need of creating an index: make the table partitioned, with two partitions, where SYNC is the partitioning column.
(Depending on your DB2 version and platform, this possibility could not yet be available.)