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

    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

    Thanks

    Smriti

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

    Lenny

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    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
    Location
    Belgium
    Posts
    2,514
    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 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Nov 2010
    Posts
    40
    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
  •