DDS or DDL

[John Andersen]

I am curious if folks these days are embracing the use of DDL instead of good old DDS?

I can see some of the benefits to DDL but is that really driving innovative apps that just aren't possible when using DDS?

-JA

[B.Hauser]

Re: DDS or DDL

The main reasion to move from DDS to DDL is, DDS is STABILIZED, that means will not be enhanced anymore.
Since Release V4R5 all new development is solely done for SQL.

For more information about DDS and SQL read the following article:
Modernizing Database Access
The Madness Behind the Methods
By Dan Cruikshank

I haven't used any DDS for the last 5 years.

Birgitta

[tomholden]

Re: DDS or DDL

i've been using DDL only for a couple of years now and no issues. i forsee no reason to use DDS anymore (well other than DSPF and PRTF)

[Denny]

Re: DDS or DDL

There are a lot of pros and cons on that subject. Here are mine.

Con: Vendor software. They have been using DDS since V1 on S/38. They are not going to change the logic in thousands of programs just to embrace the "new" technology.
Con: DDL does not support a keyed view. I've been using keyed logicals with select/omit for nearly 30 years. Hard habit to break. Depending on the application, performance can require a logical.
Con: Dynamic SQL's do not show up in DSPPGMREF. No "level check" for SQL. My current client uses a Genexus Cobol package, with RPG, SQLRPG enhancemets/modifications, Sequel reports and other GUI SQL based add-ons. The next version of the package product will have significant data base changes including changes to field sizes and numeric code/control fields changing to alpha. Determining the impact of the DB changes to SQL applications will be a chore. Or perhaps I should look at it as job security.
Con: The OS does not consistently mark SQL objects as used.
Con: SQL refused to allow me to create a unique keyed table in QTEMP. There must be a way around this!
Con: Using a field reference file is a superior design technique. Very poorly supported by DDL. Column headings and field text are cumbersome in DDL. No support for edit codes.

[tomholden]

Re: DDS or DDL

in v6.1 you can create an index with select/omit logic for example:

CREATE INDEX TTLIB/WORKFLOWI3 ON TTLIB/WORKFLOW                    
 ( TICKET_NUMBER, LOCATION, CHECK_POINT_SEQUENCE, SCAN_TIMESTAMP )
  WHERE PROCESSED <> ' ' RCDFMT WORKFLOWR;

for QTEMP create an index over the table in QTEMP.
using a field reference file can be done using

CREATE TABLE AS (SELECT <whatever fields you want> FROM <field reference file> ) DEFINITION ONLY

as far as vendor code..yep you're stuck with it. but really there should be zero code changes to change a DDS file to a DDL built table (a simple recompile will suffice if the level check ID changes)

as to the "level check" for SQL if you use select * from a file in your programs and the file changes (add field, change field, etc) your programs will not work as expected. which is why it's recommended to specify the fields to select in your statement vs. select *. and again you'd have the exact same issue with DDS.

as far as dynamic SQL and DB changes...you'd have the same issue with DDS defined files so i'm not sure where that's a con since it affects both methods. so the only real cons you have listed is the object used date and the ability to have a primary keyed file in QTEMP and the last one has a workaround. i don't like the workaround but it does suffice for my needs.

[Denny]

Re: DDS or DDL

I haven't been on a system at V6.1 yet. Do you open the table with a Where xxx Order By yyy and just hope it used the right index?

When I use CA to convert a DDS file into DDL CREATE TABLE... it ignored the field reference file and generated hard coded DDL field definitions.

I don't think you can use DDL to create a non-unique keyed table. So you have to rename the file (table) and create a logical (index) with the original files name. I noticed a lot of vendor files are created this way even when the key is functionally unique. I believe that unique keys caused a S/38 performance issue.

My point on the level check is that I can do a DSPPGMREF and a DSPFD to outfiles and use a program to identify level checks. The some of the SQL access do not show up in the DSPPGMREF. This system has over 3500 physical files. Not sure yet how many files will be affected by the version upgrade.

Yea, there is always a work around but that gets tiresome. Like the 50th time I typed NOT NULL WITH DEFAULT.

[B.Hauser]

Re: DDS or DDL

I haven't been on a system at V6.1 yet. Do you open the table with a Where xxx Order By yyy and just hope it used the right index?

If you use SQL to access the data YES.

But you can use an SQL index like any keyed logical file with native I/O, i.e. specifing it in the F-Specs and using Chain/Read/Update ....
When defining your tables / views / indexes you are not forced to move from native I/O to embedded SQL.

Specifying logical files in SQL statements is not a good idea, because the DDS for the logical file must be analyzed and the SQL statement rewritten based on the physical files/tables by the query optimizer before optimization starts.

When I use CA to convert a DDS file into DDL CREATE TABLE... it ignored the field reference file and generated hard coded DDL field definitions.

The answer I got from Rochester some times ago concerning field reference files was: If your database/tables would be normalized, a field/column would only be defined in a single/table ... and in this way you will not need a field reference table.
Using Identity columns or other artificial unique keys, instead of (composed) keys will avoid specifiying the real key fields in other tables. To access those information you use joined views.

This concept will be difficult to realize with existing tables, but can be easily realized with new tables.

I don't think you can use DDL to create a non-unique keyed table. So you have to rename the file (table) and create a logical (index) with the original files name. I noticed a lot of vendor files are created this way even when the key is functionally unique. I believe that unique keys caused a S/38 performance issue.

It is not possible to create non-unique keyed tables with DDL. DDL tables can either have primary and/or unique key constraints or must be defined without key.

All of my (new) files have a primay key constraint (my identity colum) which makes it easy to access records/rows in files/tables without unique key (such as transaction tables). If there is a unique key on the table I add a unique key constraint to my table. For non unique keys I use an index.

Using the relative record no to access data within a table/file with SQL will result in either a tables scan or a table probe, both will not be performant for large files/tables. ... but when using the unique id to access the data an index scan or index probe can be used.

Like the 50th time I typed NOT NULL WITH DEFAULT.

Use iSeries Navigator to create your tables!
You only have to click and enter the table/column names and Column heading and text. You do not need to specify several SQL statements.
There is even a wizard that allows you to select (referenece) fields located in other tables (for example reference tables).

Birgitta

[tomholden]

Re: DDS or DDL

Use iSeries Navigator to create your tables!
You only have to click and enter the table/column names and Column heading and text. You do not need to specify several SQL statements.
There is even a wizard that allows you to select (referenece) fields located in other tables (for example reference tables).

Birgitta

one other tidbit...you can retrieve the DDL for the table after it's created using the QSQGNDDL API (i have a command available on my website that uses thishttp://tommyholden.com, also FROG uses this API as well.

[kitvb1]

Re: DDS or DDL

Tom, you can do this on the fly with Navigator.
Biggie, don't forget to save the source afterwards - for documentation purposes. If creating with navigator and saving to a source member, the format gets all messed up. Doesn't matter tho, because you can generate the DDL on the fly with Navigator.

[Denny]

Re: DDS or DDL

Kit, have you tried www.SQLinFORM.com? Paste you source into the browser page and it will reformat it. The freebee on line version is limited to 100 lines.

[kitvb1]

Re: DDS or DDL

Denny... the point I was trying to make is that although the formatting is lost... it doesn't matter, because I regenerate using Navigator every time. If you copy & paste from Navigator, it often brings in, for some obscure reason, control characters. Also, when you save the source in a source file member ... as opposed to an IFS file member ... you then get some characters blinking and so on. So I'm not talking about making it look pretty with indenting and so on.

If it weren't for auditors/regulators, would we actually need to save them? Why... when we re-generate?

[Denny]

Re: DDS or DDL

I have only run the OpsNav Generate a couple of times, just experimenting. In playing with it again (CA V5R3) I discovered:

1. My output is formatted. On the options tab of the generate panel there is Format statements for readability which is checked by default for me.

2. When I send the generated SQL to a DB file member I get the control character junk. THIS IS A BUG THAT IBM SHOULD FIX.

3. I had never tried to actually do anything with generated source starting at OpsNav. I have always opened a xxxx.sql file from windows explorer and gone directly into the SQL execution panel. From there you do an Edit/Generate. I see that the Generate panel there is slightly different from the one in OpsNav. You do not have the option to "save" the source. That entire tab is missing. I have not encountered any control character issues when I did copy/paste into SEU. Apparently it works differently depending on your starting point.

I usually work on my customer's machine, so later I don't have access to the file. If it is something I want to keep, I need a local copy of the source. If I develop something on my ole 170, then I take the source to the customer's machine. And I like my stuff formatted nice and neat.

I do agree with you. The source is mostly for documentation. If you really want to create a new file it is much safer to regenerate the source. Text and labels can be changed or fields added/removed.

[tomholden]

Re: DDS or DDL

FROG and the command i wrote does not have the issue with the control characters FWIW.

[littlepd]

Re: DDS or DDL

You also do this from the Data Perspective in WDSc/RDi/RDp.

[kitvb1]

Re: DDS or DDL

2. When I send the generated SQL to a DB file member I get the control character junk. THIS IS A BUG THAT IBM SHOULD FIX.

Yep, it's this crap that not only affects the save part, but can possibly affect the running of the SQL. The char is sometimes a ";" ... and the sql does not work, and one cannot see the ";". If one is lucky, the script part will turn reverse image blue.. then at least one knows that there's something fishy that IBM's doing (or can we also pin this one on windows? )

I have always opened a xxxx.sql file from windows explorer and gone directly into the SQL execution panel.

I have a quite couple on my desktop. Except for one, the other are there just for reference. But I went to my co-workers puter the other day, clicked on xxx.sql - and was greeted by a MS version ....accckkk!!