Re: Single record access really required (was RE: Views and

[Simon Coulter <shc@xxxxxxxxxxxxxxxxx>]

On 28/07/2004, at 4:53 AM, Dave Odom wrote:

> Is DB2/400 like VM & MVS DB2, such that when you create tables inside
> of DB2 you can't read the data except with SQL and the security inside
> of theDB2 engine?

No. That's why our system is called "Integrated". There is no practical 
difference between SQL tables and DDS files. There are some specialised 
differences especially between views and logical files but for the most 
part they can be treated the same.

SQL can be used on DDS files and native I/O can be used on SQL tables.

> If that is true, then I prefer SQL for programming
> applications, at least from the security aspect.

Again, security is integrated on OS/400. The same data access rules 
apply to DDS files and SQL tables.

> And, if the DB2/400
> optimizer is anything like the VM/MVS DB2 optimizer, I'd say that 
> you'll
> get better performance with DB2 over the keyed-sequence 
> record-at-a-time
> access of the native 400 I/O but usually only IF you have large numbers
> of records.  The reason for this is because the DB2 Optimizer makes
> decisions on how to best get your data, and those decisions may change
> day-to-day, whereas the keyed sequence approach goes through keys and
> follows much the same path regardless of how circumstances have changed
> and may not be the best performer.

It depends. In many cases SQL will be faster but there are other cases 
where native I/O will be faster. Which is faster is usually a case of:
	o processing sets then use SQL
	o processing single records then use native I/O
however both methods can be used interchangeably, in the same program, 
on the same files and for the same purposes.

Regards,
Simon Coulter.
--------------------------------------------------------------------
   FlyByNight Software         AS/400 Technical Specialists

   http://www.flybynight.com.au/
   Phone: +61 3 9419 0175   Mobile: +61 0411 091 400        /"\
   Fax:   +61 3 9419 0175                                   \ /
                                                             X
                 ASCII Ribbon campaign against HTML E-Mail  / \
--------------------------------------------------------------------