Re: RE: update field based on substring of numeric field

Charles,

The test LPAR(7.1) has better hardware compared to DB2 LUW development
server. The test LPAR has more memory compared to DB2 LUW server (6 GB vs 4
GB) and has more disks compared to DB2 LUW (6 hard disks vs 2 hard disks).
The OS for DB2 LUW is CentOS (Redhat clone), it is not a single user OS.
DB2 LUW on development server is express version (free). I am connecting
to CentOS server from my workstation just like connecting to test LPAR. I
was testing after business hours, so no one else is on the system on either
test LPAR or DB2 LUW server. They have same set of indexes. None of your
arguments are true for the test conditions.

You are missing the point I was making. Both DB2 LUW and Oracle were able
to rewrite the query 1 and choose efficient access path. Both DB2 LUW and
Oracle were using same access path for query 1 and query 2. "DB2 for i" has
chosen inefficient access path for query 1. It was not smart enough to
identify query 1 and query 2 are same. Both DB2 LUW and Oracle were able to
make that distinction. I am pointing to this inefficiency in "DB2 for i"
optimizer.

Query 1:(By Satya)

update tstmis/actfile a
set umnum = (select substring(digits(umnum),6,4)
from tstmis/shactfile b
where a.umact = b.umact and b.umnum > 0 and
a.umnum = 0 )
where exists
(select 1
from tstmis/shactfile b
where a.umact = b.umact and b.umnum > 0 and
a.umnum = 0 )

Query 2: (By CRPence)

update tstmis/actfile a
set umnum =
( select substring(digits(umnum), 6, 4)
from tstmis/shactfile b
where a.umact = b.umact
and b.umnum > 0
and a.umnum = 0
)
where a.umnum = 0 /* do not change non-zero values */
and exists
( select '1' /* select anything; one-char is least */
from tstmis/shactfile b
where a.umact = b.umact
and b.umnum > 0
and a.umnum = 0
)



Satya...