|
Yes DDS is a language and SQE is the optimizer/process as you indicate.
I incorrectly stated access path sizes, I meant to say page sizes.
I believe there are still cases where SQL querys against DDS files will revert to the "CQE" (Classic query engine) rather than the SQE optimizer. I just don't know what all those cases are, the only one I've read about is when doing querys against keyed logical's where you do a restriction that isn't part of the key, it will then revert to the CQE rather than use the more heavily optimized (aka faster) SQE engine.
Yet another article here: http://www.ibmsystemsmag.com/ibmi/developer/modernization/dbmodernization_part2/
Re-affirms the previous two postings. Basically I read all of those articles as "native I/O is dead, please use SQL". And if that's the case, you may as well go ahead and do DDL as well and get the larger page sizes, row change timestamps, identity columns, decfloat, NCHAR, NVARCHAR, NCLOB, XMLTABLE, create masks, field procedures, and others that you can't do in DDS.
My only problem with this, is if you go the SQL route within your RPG programs the code is atrocious. To many cursors and /EXEC SQL commands.
I feel like if your going to modernize your database, then you may as well pick a new language for your programs that can speak SQL better than the myriad of cursors and /EXEC SQL commands in an RPG program can do.
-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
Sent: Friday, October 24, 2014 3:54 PM
To: Midrange Systems Technical Discussion
Subject: 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_Per
formance_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 SQLI am unsure what is alluded. Can a link be provided?
defined table gives you?
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 saysAnd I suppose the future of /QDLS similarly shows support for *DOC
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 *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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.