Thanks for the fast reply!! This helped greatly.
I'll try it
"Phil Sherman" <psherman@ameritech.net>
???????:OtVRf.6475$%m4.3047@newssvr33.news.prodigy .com...
An ascending index allowing reverse scans (see ShengTC's reply) or a
descending index on column A will give the best performance. You should
use the singleton select:
select * from B into :host-vrecord
where col A = (select max(col a) from b)
This will minimize the number of communications between your program and
DB2, decreasing CPU costs. This will work only if column a is unique. If
it isn't, you will have to use a cursor to fetch the rows.
If you have only an ascending index on column A then:
1. "Select * ... order by ... desc" will sort the entire table into
descending sequence then start passing you the data rows.
2. Obtaining the MAX value should scan the index. Using the value for a
single row retrieval will avoid any sorting. The SQL statement for a
descending index (above) should work well.
If there's no index, then you will have to either sort the entire table
or make two scans of the table - one to get the MAX value, one to
retrieve the data. Again, the SQL statement for a descending index
(above) should work well. The optimizer may be smart enough to recognize
that this could be done in one scan and do it that way.
Don't forget to use the EXPALIN facility to determine how the optimizer
will attempt to do each of the retrievals.
Philip Sherman
Peggy Wu wrote:[color=blue]
> dear all,
> I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390.
> As below, for the performance issue, Does anyone know which one is
> better
> ??
>
> To get a record that field A has a maximum value,
> either I could declare a cursor to select the qualified records order
> by
> field A descending,
> then fetch the 1st record.
> (exec-sql
> declare cur01
> select * from table B
> order by field A desc
> exec-end
> .................
> exec-sql
> fetch cur01 -------> fetch only 1 time
> into :host-vrecord
> exec-end)
> or get the max(A) from this table and select the record that
> field
> a equal maximum value
> (exec-sql
> select * from table B
> where fieldA = ( select max(field A) from table B)
> exec-end)
>
> thank you for your kindly help,
>
> Sincerely yours,
> Peggy Wu
> mailto:
rtomato@ms40.url.com.tw
>
>
>
>
>
>[/color]
