Idea Details

DB 2 on IBM i DDL support

Last activity 19 days ago
Anon Anon's profile image
04-01-2014 05:33 AM

As a Plex developer I want the database to be described with DDL instead of DDS, so that I can make use of the performance gain and benefit from future enhancements that will not be supported by DDS.

I want to simply switch the language from DDS to DDL for Tables and Views. Plex should take care of the Views and also create the necessary Indexes.  The existing RPG IV functions should run unchanged.

There should be option to create an Index from a View, without actually creating the view itself. These Indexes are used for performance optimization.


12-10-2015 09:26 AM

Apparently, I can see this idea on the feed, but am not authorized to view it...

12-10-2015 02:53 AM

Hi Jorge, you will be waiting a very long time if not indefinitely in my opinion for CA New CA Plex Product Owner - No CA Plex Product Architect . In the meantime you will lose the IBMi platform.


But thank you for the praise as it does make all the sacrifices worth while, with out it, it becomes a monumental waste of time.

12-10-2015 02:47 AM

sorry, George

Indeed you work is excellent...the one to be pinpointed and followed for all of us working on db2 + IBM i. But the case is that I was expecting a presententation from CA at that moment: it seemed a great step from CA, putting hands on work, and facing solutions on Plex modernization for IBM i. At least it was the way I understood the webcast announcement. What a great announce! it would be applied for 7.2? for 8.0?...But the crude reality was it was a non-CA approach, absolutely good, but not a CA Plex decision on future design. No doubt, it is the way you and other people are pushing design, but I continue waiting for thinks as simple as support for varychar fields, or full suport for ILE.

07-07-2015 09:08 AM

VW type SYS

Defining triple which specifies whether or not CA Plex generates a CREATE VIEW statement for a logical view.

This verb is variant-dependent since not all SQL databases support views. Takes one of the following system values:


Real The view contains a CREATE VIEW statement within its generated file and must be implemented in the application as an SQL view object.


Table_access The view does not contain a CREATE VIEW statement and generates DML that operates directly over the underlying table.



This allows me to create a Index object in the plex model represented as a view but no View or logical view generated for it!!!! Works nice

06-20-2014 08:31 AM


so take a LF with a key instead of a non-keyed physical file. I think ORM products don't realize a difference between.

06-20-2014 08:28 AM

Well Lorenz,

you are right - as often (or mostly). A big YES for DDL described databases!

But I won't say "DDL instead of DDS", better say DDL and DDS described databases.

As Long as there are application Systems out there, using the DDS-stuff (LF's, multi-members, etc.) there is no need to press them into new world.

But any new designed or modernized application should (better must) be able to use DLL described databases.

04-10-2014 07:50 AM

Absolutely! DDL would also mean Varchar support, wouldn't it? Thus, this would mean that data transmisstion to client would get slimmer and faster.

04-04-2014 01:38 AM

Big yes from me!

We have a huge demand for keeping indexes maintained by Plex model rather than manually updating DDL-scripts and forgetting something essential in next publish of our applications. We have a situation where we are accessing DDS-based tables via ORM product and ORM products dont quite like it if tables are missing primary keys etc

04-03-2014 01:31 PM

Indexes are required for RPG Record Level Access. Views are unkeyed. The current DDS implementation of an LF in Plex is most likened to an Index. Note that Join LF's, are not supported in an Index, so you have to be able to co-exists TABLE, LF, INDEX, VIEW, maybe even PF for those that are using multi member PF's (not supported natively from Plex, but still implementable in a Plex environment).