System iNetwork

Why doesn't IBM simply change the compile option of a DDS source file to allow us to choose to compile it as a regular DB2 file or as a SQL file??

The problem with SQL files is that you have to write "code" to generate the file. This means that the moment you make a change, the source is completely useless to allow you to re-create the file on another system or library.

I'm not about to start writing one time hard coded SQL statements every single time we need to build a table or one time hard coded SQL statements every time we want to alter that table.

Just give us a compile option that builds a SQL table from the DDS source and make it handle file changes under the covers (just like we've been able to do for over 20 years now).

I've been aware of DDL for a while but I don't understand what we gain by moving from DDS to DDL. "Industry standard" is a nice argument but lacks teeth as a motivator. We are awash in industry standards ... including the SOTM (Standard of the Month) coming from IBM.
Is the sky really going to fall if we continue to use DDS?

Both DDS and SQL DDL create PF objects. DDL automatically defaults to the most optimal settings, ie ReuseDlt (*YES). To emulate DDL simply change your CRTPF keyword defaults to use the same defaults as SQL. When you are ready to use advanced feature and function, such as Identity columns, rowid, large object support, etc then step up to DDL. Kind of like replacing your rotary phone with digital, once you're ready. My mother still uses her rotary phone and it works just fine, although she also uses a cordless touchtone with those fancy new big buttons and caller ID and such.

After doing this job since 1987, I too have to disagree with just doing it because it's new. As I see it, the rest of the world stunk until they got SQL while we had a killer system to work with all along. And now we have to switch? But the 2 things I note are: we loose field references, which keeps something like a customer number the same in all files, and in the notes about XCASE, it's going to generate nearly 200 more lines of code for the same file to be created (per the case outline in article). A pain on new files later on. And, you have a CHGPF command that deals very nicely with adding new fields, changing field size, etc, on existing DDS files. I often see SQLRPGLE programs with 2 or 3 times the line count as a pure rpg4 app as well, so all this seems to be is bigger everything just to be more like the rest of the systems out there. More chance for bugs, longer to understand someone else's work, etc. Using this in moderation is best, not wholesale changes, as the benefits don't add up to me. Just one opinion.

I totally agree with Mike . . . why doesn't IBM just make a DDS crtpf create the SQL statements "under the covers" and let us continue using DDS?

You just can't beat DDS for documentation on a file structure and OpsNav already knows how to create the SQL statement for the compile. I do realize there are some advantages to the SQL-created table (access path comes to mind), but the SQL statement to do it is just NOT very good documentation for a developer to work with. This whole "follow everyone else" that IBM is doing now doesn't make sense to me.

Chris! Or anybody! Will we ever be able to create views and or indexes using RUNSQLSTM that a green-screen programmer using RPG-ILE can really use?

Thank you,

--John

I tried using DDL in place of DDS and found there were no tools (like WDSC or SEU) that could work with it. When I create a file with DDS, I can use tools that prompt for parms, edit my syntax etc. There is nothing in DDL that does this. Similarly in creating the file or table, there is no "compile" option per se. You have to adapt runsqlstm to do the job. The iSeries Access feature/option is very poor - the DDL created often won't work, logical files are very messy as well. Creating tables in a test environment and moving them to production is another nightmare.

I like the SQL/DDL concept but, comparing it to physical files and DDS, the tools to work with it just aren't there at this time. For any IBMers, take a look at PMR 56784 from last spring. I finally gave up and went back to DDS.

There is more to using DDL to DDS than new capablities. DDL created files are faster. DDS created files do not check on writes, only on reads. DDL files check on writes and does not check on reads so assuming that you normally read data many times more than you write the data, DDL created files are faster.

I am assuming the reason that IBM created DDS the way they did was because of all the data being ported from System 34/36 that had decimal data errors in it.

Same applies to SQL index. They are much faster although you can use SQL indexes with DDS.

I like having both, and I hope that both stay around for a good long time. I agree with Ed Garrett about "industry standards" not being a reason to switch. The nicest thing about standards is that there are so many available to choose from.

I was most impressed with the statement that System i customers don't seem to be very well equipped to manage their databases. The primary reason is that they don't need to manage the System i databases. Most customers do very well with what IBM has provided via DDS. We've found in our shop that the SQL/DLL techniques don't provide anywhere near the consistency that we've had with good old DDS. Not that there isn't a place for new things, but changing just for the sake of changing when what you have works well, just doesn't make sense.

One of my frustrations with SQL Tables is that there isn't any capability for multiple members. An SQL table can't have them and if your using SQL to reference a DDS created file that does you have to go through the extra step of creating an alias.

Reading these comments I see a lot of frustration because of a lack of knowledge about what current releases can do for you.

But I totally agree that there could be more support on bulk conversion from IBM if they want us to transform to DDL.

IBM started some years and releases ago to move to a graphical interface, but for some reason, they stopped.
As a consequence, today the available tools are spread over several places.

I miss the concept behind it!

The simple fact is that it is not possible to create a relational database using DDS. DDS has no concept of foreign keys, cascading deletes or auto-incrementing numbers. It's the continuation of techniques like DDS that contribute to the AS/400's reputation as a legacy system.

On 22 July, Ed Garrett said:

"I've been aware of DDL for a while but I don't understand what we gain by moving from DDS to DDL. "Industry standard" is a nice argument but lacks teeth as a motivator. We are awash in industry standards ... including the SOTM (Standard of the Month) coming from IBM. Is the sky really going to fall if we continue to use DDS?"

There are implications to not using a "standard" that nearly everybody else uses, namely:

• Tools availability to work with DDS
  
• Skills availability to work with DDS
  
• DDL/SQL is the route for the support of the newer data object types in DB2 (IBM driving this)
  
• DDS uses the "old" Classic Query Engine [CQE], DDL/SQL uses the newer SQL Query Engine [SQE] ... and IBM are pouring all their development dollars into the SQE for optimised performance. CQE is "stabilised"
  
• Business rules are moved into the database which is easier for future development and SOA.

Apart from that, there are no good reasons . . . I'm sure you could survive for a few more years on DDS before you start feeling you're hitting a "hard stop" ... and we wonder why the "i" appears to be "stagnating"???

iSeries Navigator will generate the SQL to create a table from an existing physical file adapt* it will not create the indexes. With logical files it creates a view again with the indexes. Go to Databases-> Schemas -> library name/schema name -> Tables-> right click, select Generate SQL...

[*Note from Chris: I'm guessing Randal meant "although" rather than adapt. . . .]

Just to clarify one earlier point, DDS does not always prevent the usage of the SQL Query Engine. An SQL statement referencing a PF can be processed by SQE. DDS may prevent the usage of SQE (eg, referencing a Logical File on the FROM clause).

System i Navigator does offer a graphical wizard to "prompt" you thru the creation of an SQL table and then allows you to save the SQL statement into a source member.

I'd also like to understand how the "compile" feature of CRTPF is better than RUNSQLSTM? Also, please post the include the branch/dept & country code for PMR 56784.

If you want to use field reference files with the SQL CREATE TABLE statement, there is a technique available to do that.

SQL Views & Indexes can be used by green-screen programmers, need more details on why poster didn't think that was the case.

After reading these posts it is embarassing to me as an IBM'er that we have not done a good enough job in communicating the many enhancements that DDL brings to both the legacy DDS programmer and the experienced SQL user.

I do not know of a way to automatically direct a write to the proper "member" without first doing an OVRDBF command. With DB2 multisystem this is defined as part of the CREATE TABLE statement, no programmer intervention required. Nor do I know of a way to truncate a table without using the CLRPFM command, something that is automatic when using the DELETE FROM TABLE SQL statement. Nor do I know of a way to pre-fetch an index or table into memory without using the SETOBJACC command, something SQL does automatically. How about updating a join logical file? Not possible with record level access and DDS, yet it is available with SQL Instead of Triggers attached to an SQL view. Don't forget automatic ID generation via identity column and sequence objects support, not available in DDS. Nor can you join a DDS LF to another DDS LF , or for that matter, create a summary LF, although this is done all the time with SQL.

As far as WDSc goes, I use the Data perspective for creating my database objects. I use the templates and snippets to quickly produce both stored procedures and RPG programs with embedded SQL. And I can prompt my SQL and have it automatically formatted. I can go on and on. But unfortunately I am not preaching to the choir. Is it a lack of education, leadership or simply an unwillingness to change? Twenty years ago I deleted the programmers menu so my developers would be forced to use modern tools like PDM. What's it gonna take today?

Dan C. asks whether it is "a lack of education, leadership, or simply an unwillingness to change?" Personally I think it is a mix of all of it. Starting with the first point, - education, I do not remember seeing much in the way of articles (published online or on paper) that speak to managing databases using DDL instead of DDS. Could this be due to the fact that using one over the other has little impact on how a business application performs?

Regarding leadership, I personally think this falls back on IBM. If DDL is really a better tool, then they should be the ones promoting its virtues and make it easy for us to give up DDS (which btw, started on the S/38 and was not AFAIK a tool to help S/34 or S/36 shops). As a *cobbler's child* it is hard to justify a new pair of shoes when *selling* it from a business standpoint. ROI seems to be hard to qualify (yes, we have Oracle DBA's; no, we do not have DB2 DBA's - just another one of my many hats ^-^).

Which leads us to the final point: an unwillingness to change. I think you would find most i developers willing to adopt DDL (personally I miss not having auto ID generation with DDS). However, the reality is that most shops exist to support their business (which I doubt is software). That said, it is hard to justify change just for the sake of change. How long would it take for someone to get *up to speed* on DDL (having gone through Oracle DBA training I do not think I am in a position to fairly judge this one)? How many internal project management processes would need to change to accommodate the way file (sorry, table) management is done using DDL versus DDS?

It is easy to point the finger and say, do this. But it is quite another to make it happen. Dan C., you should feel fortunate you are in a position to make those kinds of business decisions. Maybe one day the rest of us can join you.

Our RPG ILE application only uses DDL created tables, indexes, and views. While the majority of our RPG ILE applications use embedded SQL, we have applications that use RPG op-codes of READ, WRITE, etc to these DDL files without any problem. We notice significant performance improvement using DDL vs DDS. Anyone wonder why users view the "i" as an obsolete system? Maybe because so many developers still want to use old programming techniques. Join the future or live in the past.

I believe Dan has hit the nail on the head here. IBM has not clearly enunciated the benefits of moving to the new database model. Automatic ID generation may be the greatest thing since peanut butter, but if I've never had the option before, how am I to know its value? Nor have they provided a clear migration (DDS to DDL dictionary?) such as CLRPFM = delete from table. It isn't that we as a group are intolerant of change. We are, however, unlikely to change without a clear understanding of the benefits. Actually, all this may (and probably is) in a redpaper or some such on IBM's web site, but I haven't found it yet.

Thank you Mike, for sponsoring this discussion. I have heard so many questions and comments in this area. The one Guru who addresses these issues well is Mike Otey.

I would also like an IBM Red-Book dealing with all these issues.

Thank you!
Good posts here. Many of them are my issues, too.

--John

I looked into the CREATE TABLE statement. It takes a subquery clause as an option in defining table: SELECT FIELD1, FIELD2, FIELD3… FROM FIELD_REFERENCE_FILE.

I do not agree with the statement that DELETE FROM TABLE is a valid replacement for CLRPFM. CLRPFM is a database function,which requires exclusive access and there are no deleted records in the file after the command has run, thus space is given back to the system. And it runs quickly. The SQL DELETE runs forever when there are a lot of records, does not require exclusive access and leaves deleted records behind. It's more like the Delete code in RPG, but without the need for a loop since you can specify a where clause.

Don't get me wrong, SQL Delete is a very powerful and useful function I'm using regulary but it's not CLRPFM.

I've gone through a conversion from DDS to DDL. It's been a challenge, but a good learning experience. I'm not one to say just change for changes sake, but this change has opened our shop up to many more possibilities than we could of had using DDS. (i.e. placing business rules into database which saves me coding!!)

There are many arguments to stay with DDS and there are probably many situations where it's the appropriate thing to do.

I have my issues with some of IBM's direction, but SQL/DDL is not one of them. We all say we are so in love with this platform, but we don't want to learn or educate ourselves with new ways of getting our job done on this platform.

Maybe if we learned these new technologies we wouldn't be replacing the i5 with other solutions. We could sell the i5 to our management as a "modern" solution.

I truly believe there is enough blame to go around in both the development community and IBM. We should be finding ways to make this work.

I'm off my soap box now.

API QDBRTVFD has the information needed to create DDL for DDS files. This should handle the majority of cases. It provides key information also.

Chris, have you noticed any performance issues or gains since the conversion from DDS to DDL? On average how long did it take for each table?

Try out IBM API QSQGNDDL to convert DDS to DDL.
The same API is used in OpsNav database functions. (generate SQL...).

Google for GENDDL or use that link: http://www.itjungle.com/mgo/mgo060502-story01.html.

This is a good example for green screen.

Note:
You can also create DDL for SQL indexes, used in native PF/LF objects.

I'm in a large shop using active database sizes of 800GB and 1.5TB and the problem I face is trying to use DDL over DDS is tools.

Each time our have our software support team use DDL and SQL over DDS it takes so much time to get it coded and tested that we miss our rollout dates.

We have to roll out software updates, patchs and database file changes using a number of tools to keep track of file/table field changes, program code changes and program source code changes so we only use DDL if we can't find another what to get the job done.

The three most needed items for our shop would be DDL tools, DDL tools, DDL tools.

Our i5 would go to DDL today if we had DDL tools.

My question is what is holding up good i5 DDL tools?

Andre