Home > AS/400 Tips > iSeries programmer tips > SQL vs. DDS -- The good, the bad and the downright ugly

	iSeries 400 Tips:	EMAIL THIS

	TIPS & NEWSLETTERS TOPICS

	ISERIES PROGRAMMER TIPS SQL vs. DDS -- The good, the bad and the downright ugly Paul Tuohy 11.03.2004 Rating: -4.19- (out of 5) Digg This!     StumbleUpon     Del.icio.us    Author Additions To This Tip In case you haven't heard, DDS is no longer the development language of choice when it comes to defining and maintaining your database. It has been usurped by SQL's Data Definition Language (DDL). Why the change? DDL is the standard definition language used for most (if not all) other databases. It is reasonable for IBM to adopt the same standard for OS/400 (i5/OS). [TABLE]			[IMAGE]

But what does DDL offer over DDS and vice versa? Let's take a look.

The Good

Here, in no particular order of importance, are some of the good points of DDL:

The Bad

Here are the bad points of DDL. Some are problems because they are the opposite of the way you do things with DDS, and others are problems because they do not offer some of the functionality of DDS. Again, the list is in no particular order of importance.

The Downright Ugly

Whereas each of the bad points listed above are manageable, there is one major problem with DDL.

SQL treats views and indexes as two separate entities. An index is a keyed logical, and a view is a non-keyed logical and never the twain shall meet. Or, to put it another way, you cannot define a key for a view.

That means all of those wonderful features that are available when defining views are of little or no benefit in high-level language programs. A view is useful only if you are not interested in a key sequence or in accessing records by key (and how often does that happen?) or if you are using embedded SQL. <

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT RPG iSeries programming Migrating from RPG to EGL on IBM i Allow access to data from a stored procedure result set using COBOL or RPG EGL Rich UI on IBM i: Do you Dojo? Programming for the Web on the IBM i, what is possible A taste of COMMON: ILE, IBM releases, Web applications and new products Documenting nested program structures on the AS/400 How to: Sort arrays using RPGIV How to: Create an RPGLE array How to use an embedded SQL statement and display the result in a subfile Eight steps for creating program documentation using AS/400 utilities iSeries programmer tips Groovy programming on IBM i EGL Rich UI on IBM i: Do you Dojo? Running PHP open source applications: NOBODY needs authority Programming for the Web on the IBM i, what is possible Using geocoding on AS/400 to enhance your Web presence The best technologies and tools for System i programmers in 2009 Seven IBM i project lessons learned in 2008 Documenting nested program structures on the AS/400 What is an integrated database? An automated CL method of moving a query from AS/400 to Excel iSeries SQL commands and statements When is the YES option for 'reuse deleted files' function the best choice? Convert a numeric physical file to a character in SQL without leading zeros Inserting data from a CTE into a file in SQL Implement variables in SQL when creating an alias Control and fix date format problems on AS/400 SQL Disable SQL query optimizer tool that removes trailing blanks Database drivers on the i: MySQL vs. IBM Toolbox How to: Output SQL script to a text file from an AS/400 IBM DB2 Connect allows SQL data sharing Size limit of DB2 table in V6R1 on IBM i iSeries SQL commands and statements Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary Report Program Generator  (Search400.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

/p>

Which to use?

Should you start using DDL or should you stick with DDS? The answer is to use both. Over the past couple of years I am inclined to use DDL to define any database, but I still resort to DDS when I need to. DDL offers functionality that removes code from programs and ensures the enforcement of rules through the database as opposed to the application.

It is almost certain (at least I hope it is almost certain) that IBM will provide some means of defining a combined view/index with DDL, especially since it can be easily defined with DDS. I am willing to make do with the bad as long as this one gets fixed.

Of course, I am also more then willing to have any of the bad features turn good.

---------------------------
About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company. He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"

FROM THE AUTHOR

My thanks to Dave Anderson for pointing out a couple of performance related omissions from this programming tip -- one good and one bad.

• Indexes have a page size of 64K as opposed to an index page size of 4K or 8K for logical files. This can lead to an obvious increase in processing speed.
• Logical files will share an access path if the keys are a subset of an existing access path (e.g., an existing access path is keyed on the fields A, B and C and you are defining a logical file with a key of the fields A and B: the access path will be shared). On the other hand, indexes will only share an access path if all the key fields match.



Rate this Tip To rate tips, you must be a member of Search400.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.