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 v220.127.116.11
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
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.
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.
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