Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Help needed for a query

    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());

    }

    }

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    UPDATE PRAGS.PERSON P SET SYNC='Y'
    WHERE EXISTS(SELECT 1 FROM PRAGS.CUSTOMER
                 WHERE FNAME = P.FNAME AND LNAME = P.LNAME)
    or
    Code:
    UPDATE PRAGS.PERSON SET SYNC='Y'
    WHERE (FNAME, LNAME) IN (SELECT FNAME, LNAME FROM PRAGS.CUSTOMER)
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Mar 2009
    Posts
    24
    Thanks for the reply, it worked.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dav1mo
    The index eats up some space, but the query performs very well.
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •