If you have read my previous blogs, you probably know that I recommend we all stop guessing file sizes and switch to SIZE(*NOMAX) for all physical files!
Now for something a little more radical, STOP USING DDS and start defining your database objects with SQL instead!
Upgrading from legacy DDS to SQL for defining tables, has many advantages: SQL creates tables with SIZE(*NOMAX) by default. It’s faster under the covers (I have noticed a small performance improvement simply by converting my existing DDS Files to SQL Tables)
I couldn’t say it better than an old friend of mine:
As data gets larger and more complex, not to mention integrated with multiple platforms and technologies, you must go SQL. Primary Keys, foreign keys, and GUIDs. No more alpha keys. It’s future proof and opens everything up for superior applications.
Mike Moegling
Why You Should Ditch DDS and Start Using SQL DDL on IBM i
Here is exactly why SQL DDL beats the socks off DDS when you are building tables on IBM i:
- Data validation happens at write time rather than read time. The database catches bad data the instant you INSERT or WRITE so you avoid junk sneaking in via CPYF *NOCHK. Your reads run faster because all that checking is already done.
- Built-in data integrity constraints. You define primary keys foreign keys unique constraints and check constraints right in the CREATE TABLE statement. The database enforces them no matter how the data arrives.
- Modern data types and features. Think BIGINT identity columns CLOB BLOB row change timestamps generated columns partitioned tables and more. These are either missing or clunky in DDS.
- Longer descriptive column names. Up to 128 characters instead of the old 10-character system name limit. Your code becomes far more readable and self-documenting.
- Better performance and indexing. SQL tables work hand-in-glove with the SQL query engine and let you create advanced indexes that DDS logical files cannot touch.
- Future-proofing and easier skills transfer. IBM pours almost all new DB2 for i enhancements into SQL DDL. Modern tools languages and new developers already know SQL so onboarding is quicker.
- Full compatibility with native I/O. Your RPG programs keep using CHAIN READ WRITE and every other record-level access opcode exactly as before.
Embrace it : SQL is the industry standard!
Every modern database from Oracle to PostgreSQL to SQL Server speaks SQL. Your developers learn one language that works everywhere. DDS is a proprietary IBM i thing. New hires or contractors from outside the platform always have to climb that extra learning curve. With SQL you stay in the mainstream.
IBM-i SQL DDL gives you features that DDS cannot touch. You get identity columns without any extra work. You can add LOB data types such as CLOB, BLOB, or DBCLOB for documents and images. You gain row change timestamps, generated columns, and proper check constraints right in the table definition. Referential integrity becomes rock solid because the constraints live inside the table instead of scattered across logical files.
Maintenance gets a whole lot easier. Need to add a column? ALTER TABLE does the job in seconds without a full file re-create and recompile of every program. Need to change a data type or drop a field? Same story. DDS forces you through those painful CRTPF and recompile cycles far too often. SQL keeps your changes quick and clean.
Performance usually improves straight away. SQL tables default to a 64 KB page size instead of the 8 KB you get with DDS logical files and views. Data validation happens at write time rather than read time, which is a big win for any application that reads far more records than it writes. Deleted record space is reused automatically with REUSEDLT(*YES) built in. And because IBM i now optimizes almost everything through the SQL Query Engine, your queries run faster and scale better.
IBM puts all its new database development effort into SQL. New data types, advanced indexing, temporal tables, and performance enhancements arrive first (and sometimes only) in SQL DDL. DDS has not seen any meaningful updates since V5R3. If you stay on DDS you are locking yourself out of future improvements.
Finally, remember that SIZE(*NOMAX) we talked about last time? SQL tables use it by default. No more guessing, no more worrying about runaway programs hitting arbitrary limits.
Making the switch does take some planning. You will want to test the conversion on a few files first, especially if your programs use native I/O. Tools such as ACS (Access Client Solutions) can generate the SQL CREATE TABLE statements from your existing DDS with a single click. Once the table is converted the record format level usually stays the same, so you often do not even need to recompile your RPG programs right away.
The long-term payoff is huge. Your database becomes easier to document, easier to model with modern tools, easier to maintain, and ready for whatever comes next.
Have you already started converting any of your physical files to SQL tables? Drop a comment below and let me know what your experience has been. I would love to hear the good, the bad, and the ugly from the trenches.
