Difference between Physical files and Tables in DB2..

[hrishikesh]

Hi,

I just want to know the differences between the Physical files and the tables in DB2.

As we can query physical files, we can access a physical file with diffrent keys using logical files...i mean these are facilities provided by tables also in DB2.

How these tables in the DB2 has an edge over PF's?



Thanks,
Hrishikesh

[DeadManWalks]

Re: Difference between Physical files and Tables in DB2..

A PF is created using DDS.
A LF is created Using DDS.

A Table is created using SQL.
An Index is created using SQL.
A view is created using SQL.

You can create an index or a LF over a File or a table.

The advantage of a table is you can use some field defs that are not allowed in a PF.

Some shops insist on DDS so you can always see the source.

[B.Hauser]

Re: Difference between Physical files and Tables in DB2..

Hi,

first DDS described files physical files can be used with native I/O and SQL. SQL described tables can be used in the same way.

A DDS described logical file can be used with native I/O and can be specified in an SQL statement. Even though it is possible to specify a logical file in a SQL statement it is not recommended. SQL statements where logical files are specified must be executed with the old/classic query engine (CQE). The DDS described file must be analyzed and the SQL statement rewritten based on the physical file. The rerouting to the CQE can cause a 10-15% performance decrease.

An SQL view is a logical file that never has a key, but is much more powerful than a DDS described logical file, because it can contain everything that is possible with a SELECT statement, except an order by. An SQL view can be specified in an SQL statement, but also with native I/O
An SQL index is a keyed logical file. The query optimizer needs access paths (either in SQL indexes or keyed logical file) to retrieve the data as fast as possible.
Even though an SQL index cannot be specified in a SQL statement, it can be used like any DDS described keyed logical file with native I/O.

The main difference between DDS desrcibed files and SQL tables is when data validation occurs.
In a DDS described physical file data validation occurs as soon as a record is be read, but there is no validation when a row is written. That means with CPYF and *NOCHK it is possible to insert invalid data into a phyiscal file.
In a SQL defined table data validation occurs as soon as a record is written, but there is no checking when the record is read. That means with CPYF and *NOCHK it is not possible to insert invalid data into the table.
Because normally much more rows get read than written, using SQL tables will gain performance, because a read time no validation checking occurs.

There are much more differences, but it would be too much to explain them here.

Birgitta

[bryce4president]

Re: Difference between Physical files and Tables in DB2..

Hey Birgitta,

Are we talking about a noticeable difference? How significant can the performance gain be? 15% or more?

[B.Hauser]

Re: Difference between Physical files and Tables in DB2..

Hi,

according to IBM there may be a performance gain up to 40%.

For more information, just read the following article:
Modernizing Database Access
The Madness Behind the Methods

By Dan Cruikshank

Birgitta

[hrishikesh]

Re: Difference between Physical files and Tables in DB2..

Yes..I agree..Thanx for your help guys..

I think 'Data Validation' is the major diffrence between DDS described files and DB2 tables?

Is there any major difference other than this...

[bryce4president]

Re: Difference between Physical files and Tables in DB2..

hrishikesh,

do read the last link that Birgitta posted. That is a great article that outlines the differences in the two formats. And, yes, there is more to it than just the data validation.

no spoilers though

[hrishikesh]

Re: Difference between Physical files and Tables in DB2..

Bryce,

You are right. I just went through the link provided Birgitta it was really a good one. yes it has much more in it


Hrishikesh

[jsight]

Re: Difference between Physical files and Tables in DB2..

Wow, nice thread!

Can someone explain to me how these differences affect writes that are different from the creation type?

What happens if:

• I create a file with DDS, then write to it w/ SQL? Is the validation still done at write time? (presumably at read time as well)?
• Read from a SQL created file using old style techniques? Presumably read validation is still done?
• Read from a DDS created file using SQL? Presumably no read validation is done?

Anyway, maybe I'm just talking confused. At the very least, I found the linked articles enlightening.

[bryce4president]

Re: Difference between Physical files and Tables in DB2..

Correct me if I'm wrong Birgitta...

There isn't any read validation when you do an embedded SQL query in RPG. Only at write time.

But I think, trying to remember from previous posts, that unless you are really doing a lot of reads in an RPG program, like millions, that the native chains and reads are about as fast as embedded sql statements. I may be wrong on this...but something lingers in my mind about it from a few months back when I first started programming on this wonderful system. I wanted the fastest way, but i think in the end, its all about equal. This is unless they are all

SQL described files of course

end of the day, i might be off on this...