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.
As an Amazon Associate we earn from qualifying purchases.