sql ddl vs dds - IBM AS400
This is a discussion on sql ddl vs dds - IBM AS400 ; Has anyone gone thru converting their dds to sql ddl? Is DDS a thing
of the past?...
sql ddl vs dds
Has anyone gone thru converting their dds to sql ddl? Is DDS a thing
of the past?
Re: sql ddl vs dds
On May 2, 3:35*pm, eknight wrote:
> Has anyone gone thru converting their dds to sql ddl? Is DDS a thing
> of the past?
I currently have a mix. My PF are nearly all DDS, most new LF are SQL
views. I have no plan to convert DDS to DDL at this time.
DDS is not dead yet, but ....
IBM has stated it will no longer enhance DDS, only do bug fixes. But
just as there are things you can do in DDL that you can't do in DDS,
it also true the other way around. For example DDL doesn't support
multi-member files, edit codes, or keyed logicals with select/omit.
Based on my experience, I think DDL will increase in use, but I expect
DDS to be around for quite a while. There is a lot of applications
out there that are still using it (maybe most?), so support for it
will probably be around for years (decades?).
Re: sql ddl vs dds
all further development in data definition language will be done in
SQL. The last enhancements in DDS were in release V5R3, to allow 63
digit numeric fields.
All other enhancements where done with SQL. Here a few examples what
SQL DDL provides while DDS not:
1. Identity Columns in SQL tables
2. New Datatypes such as all kinds of LOBs (Large Objects), DATALINKS,
DECFLOAT (in V6R1) and even distinct data types
3. For DDS described physical files data validation occurs as soon as
a record gets read, but there is no validation at write time. In this
way it is possible to copy invalid data, for example with CPYF and
*NOCHK into DDS described physical files. In SQL described tables data
validation occurs as soon as a row will be written, but there is no
data validation if a row is read. In this way it is not possible to
insert invalid data into SQL tables, even with CPYF and *NOCHK.
Because validation happens at write time using SQL tables instead of
DDS described physical files may result in a (huge) performance gain.
4. SQL views are much more powerful than DDS described logical files
are. In SQL views everything that can be done in SELECT statements,
including grouping, (recursive Common Table Expressions CTE) and the
use of scalar functions is possibe, except Order By. It is even
possible to built a view over an other view.
5. Instead of triggers, that allow updating joined tables can be built
over SQL views but not over DDS described logical files.
6. An SQL index can be used with native I/O like any DDS described
keyed logical file.
7. With release V6R1 SQL indexes are enhanced to allow columns that
are not in the base table (for example if a scalar function must be
used to split a date into 3 fields and built an index over these
fields). Also Where conditions the equivalent for select/omit clauses
can be added to SQL indexes. It is even possible to select fields in
indexes and specify a differen format name. In this way only joind DDS
described files with keys must be defined as DDS files. All other can
be covered by SQL.