Re: Replacing Traditional I/O with SQL

On 1/25/11 6:16 AM, Jeff Buening wrote:

For people that have replaced traditional I/O including Read and
Writes with SQL, was there a significant hit on performance if the
underlying file structure was still DDS? Just wondering if not even
worth doing the embedded SQL, unless we switch the file to DDL.

The design intention of the integrated database was to allow someone to switch to use the SQL DML first, and then only after the database files for the application(s) might require some changes, would a move generally be made to use the SQL DDL. Along those lines for example, constraints were allowed to be added to a DDS PF and a DDS means to effect [even more than] SQL ALTER by CHGPF SRCFILE(); neither was made available only to the SQL. Some published and often overstated benefits from using DDL, stated with almost no mention of the [actual and potential] consequences for having done so, had in my experience long caused the reverse order of a switch; i.e. moving to DDL, and later to DML... and it seemed to me, much to the dismay of the architect given all the work made to allow someone to continue without ever replacing their underlying files which can be a very costly endeavor.

The switch from the CQE to the SQE by the database for the SQL however, eventually added more weight to the argument for using the SQL DDL, at least for defining access paths; but the QAQQINI derived index option to ignore the unsupported DDS LFs enables creating extra SQL INDEX while leaving existing DDS LF for a mixed use of both the RLA and the SQL DML.

I have written some new programs that only do some selects and write
to a print file and have been fine; still DDS structure. My concern
is if I start replacing traditional I/O Reads and Writes and I am
going to see this huge impact unless we switch to DDL first. Or this
is my impression I have gotten online. Does my concern seem valid or
has anyone done this and the impact not as bad as I think?

The biggest issue, both for performance and functionally, is almost always with the change from RLA to SQL DML; due mostly to mindset for retrieval and for what access path definitions will be beneficial to the SQL versus for use by the RLA. Specifically, the typical keyed DDS LF created for RLA is generally undesirable for implementing queries, both for needs of SQL selection [generally and possibly more for the SQE, given support level for derived indexes] and for the page size [as alluded by others]. Note however there is a newer capability to achieve the same page size from DDS CRTLF [with the same costs and consequences as with the SQL CREATE INDEX].

I expect little concern with leaving the PF as DDS and adding any necessary SQL INDEX to provide any [advised] indexes, but also for having eliminated any keyed LF that will no longer be utilized so as to avoid any excess access path maintenance.

Regards, Chuck