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

Ah Matt - now you're talking a dangerous line - go to a different language? Bah, Hamberg!!

Just kidding - there is less and less that will go to CQE - national language used to be one of those, now it's not, IIRC. I think Dreamworks (er, developerworks) may have details - or NoKnowledgeCenter.

I guess I don't see the push to "native I/O is dead" - although IBM do seem to be pushing it hard. For me, this becomes a choice - going with only one approach isn't where I'm at, for now anyway.

The nice stuff you list, which we can get in a table or index only by using DDL, are fantastic - the new RCAC stuff is fantastic, I think. But we don't need to use SQL DML embedded in order to take advantage of these - RCAC is enforced, no matter the access method, whether native or SQL, as I think I've been told.

I hear you on the look of things - it seems better now in the freer format, to some extent. I'm also quite excited at the prospect of embedding an entire stored procedure in RPG - then most of the SQL code is really clean - only one /exec sql - and SQL code makes things pretty easy, compared to what we are used to with, as you say, so many /EXEC SQL bits.

I think you and I are not that far apart on the spectrum - slightly different choices and preferences - seems it's all good. And maybe as the immortal Tevye would say - "You're both right!"

Vern

On 10/24/2014 4:26 PM, Matt Olson wrote:

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 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.

--
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.