Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010

    Unanswered: db2 update sql with like predicate

    Hi All,

    I have a update sequel with a like predicate that is updating 20 million records in prod. I have been asked to rewrite the sequel one update at a time removing the like predicate. Is there a more efficient way of doing it ?

    db2level= DB2 v9.5.0.8

    below is the sequel

    update solid set tint = 0 where serialnolike '%-ss27c3-%';
    update solidno set published = 0 where solid_ID in (select solid_id from solid where serialno like '%-ss27c3-%');

    Help will be much appreciated, this is going into production on 7th of this month



  2. #2
    Join Date
    Jul 2009
    You can use Locate(...) instead of Like.
    Can you explain why they don't want Like ?


  3. #3
    Join Date
    Nov 2011
    i have two questions:
    1)what is the difference between the table solid and table solidno?
    2) why the update columns is different ? ( sql1 is tint and sql2 is published )

    if solidno is the same as solid , then
    1) if solid_id is not unique , these two sql are not the same.
    for example:
    solid serialno
    1 A-ss27c3-B
    1 A-xxx-B
    sql1 will only update the first row
    and sql2 will update these two rows.
    2) if solid_id is unqiue, the optimizer will rewrite sql2 to sql1, so there is
    no diffrence between them.

  4. #4
    Join Date
    Apr 2006
    Provided Answers: 11
    the problem is the % in first position - the optimizer does not like this..
    as it is difficult to define the range...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

  5. #5
    Join Date
    Nov 2010
    thanks for all the help guys..I had them remove the first percentile and they ran it last night itself in production. It ran in less than 1 minute

Posting Permissions

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