Re: Can I use DDS to create an SQL table name

Matt

Just to clarify - DDS and SQE are not the same "kind" of thing - so comparing those isn't germane, IMO. DDS is a language, SQE is an optimizer/processor. The things that make SQE better are the same whether running DDL-based or DDS-based objects - yes, DDS-based object have attributes that can give better performance. Things like statistics, however, are at the DB component level and apply to both files and tables, e.g.

As to the access path sizes, those are defaults - they can be set in both CRTPF and CRTLF commands now. But how many of us go away from defaults?

Chuck addressed the access path size in an earlier reply on this thread - the traditional IBM "it depends" answer - where he said "bigger is not always better". I believe that I know he IS in a position to have an informed basis for his comments there.

At the same time, the presentations you cite are well-worth looking at. The performance charts show better performance with DDL-based objects. I'm not sure that they represent perception - there is always key-wait time. The chart from a customer showed a 10% improvement - is that worth the work to get there? Maybe.

I again say, SQL vs DDS is not the question - in other words, all the things we can do with SQL are fantastic, and I will use them. They apply almost equally to objects created with DDS, so use SQL, don't necessarily change existing objects to DDL-base unless there is something you now need = like ONCHANGE kinds of things for timestamps.

Regards
Vern

On 10/24/2014 3:01 PM, Matt Olson wrote:

Your statement about indexes is not correct. There is a difference between DDS and DDL indexes. See this link: http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf

Specifically read about the 64k access path vs DDS's 8K access path.

Also remember, there haven't been improvements in DDS for years. All IBM's investments are happening with SQE.

This is one of the best presentations I had on the subject years ago: http://www.gateway400.org/documents/Gateway400/Handouts/DDS%20vs%20DDL%20WhyModernize.pdf

Check out slide #64 and on. Another awesome slide is #73 that lists of a ton of features you can't do with DDS.

Remember you only get these goodies with SQL. Also, 99% of the planet is SQL based from my experience, not native I/O based :-)

-----Original Message-----
From: CRPence [mailto:CRPbottle@xxxxxxxxx]
Sent: Friday, October 24, 2014 2:46 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Can I use DDS to create an SQL table name

On 24-Oct-2014 12:18 -0500, Mike Cunningham wrote:

Chuck, what about access to the improved key indexing that an SQL
defined table gives you?

I am unsure what is alluded. Can a link be provided?

Given a SQL TABLE is a non-keyed database *FILE, and a keyed SQL TABLE is a database *FILE with a PRIMARY KEY CONSTRAINT or UNIQUE KEY CONSTRAINT, then the equivalent DDS PF is achieved with Add Physical File Constraint (ADDPFCST). Effectively there is *no difference* between the two files with regard to the keyed access path (ACCPTH); the underlying support for /indexing/ [and enforcement] is identical, as provided by the identical LIC DB methods betwixt, irrespective the choice of those two means used to define the files and their keys.

An INDEX can be created on [a single-member] PF, and like the AccPth of a constraint index, the keyed access path of the SQL INDEX is created and treated identically [by the LIC database] no matter whether the underlying physical data comes from either of a DDS PF or a SQL TABLE.

And then there is always the future to consider. My crystal ball says
at some point IBM will pull the plug completely on DDS for database
definitions just like it has pulled the plug on SEU. To their credit,
they have not gone the path of some other companies who have said
change your ways or don't upgrade, they have given us many years to
make the switch. So I don't see the demise of DDS anytime soon but 5
years from now I'm not so sure.

And I suppose the future of /QDLS similarly shows support for *DOC and *FLR going away.? Even if not, merely per having portended the demise of the Data Description Specification support, I would expect that crystal ball is due for maintenance or an upgrade to a newer model :-)

Until the system is reincarnated [much like the s/38 and s/36 were reincarnated as the AS/400, and the respective legacy "environments"
provided], I very much doubt the DDS would be pulled; even if the implication is only for the specifications capability for defining database Logical File (LF) and Physical File (PF) [as if the compiler might continue to enable processing DDS for DSPF and PRTF?]. The DDS is effectively dead already however, with regard to database files, because there was\is no intention to support [with DDS keywords\etc.] any new SQL data-types and features. Even so, the DDS compiler continues to function to create DBFs, and I would expect that compiler to continue to function in perpetuity just like I would expect with the run-time of OV/400, Query/400, DFU, and many other features. If the DDS compiler had been an optionally-installed LPP, only then would I expect any possible consideration for the removal of that compiler; too much code [including some OS code] have dependencies on that OS feature.

Most who know me understand that I would never offer a guarantee, and that I would be unlikely to bet, except on that which is all but guaranteed. But on that specific matter, I must admit that I would be quite willing to place a bet. I am confident the demise of the OS would precede the demise of the ability to Create Physical File (CRTPF) or Create Logical File (CRTLF) from DDS, and just as certain that the demise of the OS would transpire as a direct result given an inability to create database files from DDS; i.e. I expect few customers would be willing or able to accept that effect, as a loss considered to be a /bridge too far/, and I doubt the loss would be an impetus for a slew of new customers to take their place.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.