• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL May Be Catching Up with DDS

    June 18, 2008 Ted Holt

    As enthusiastic as I am about SQL, I am not ready to abandon the native DB2-for-i interfaces. There are a few things that DDS and native I/O op codes do that SQL doesn’t handle as well. However, in V6R1 IBM has eliminated one of the DDS advantages.

    A logical file can do three tasks: it can define a record format to be a set of fields, it can define a data set to be a set of records, and it can create an access path over the data. SQL needs two commands. CREATE VIEW handles the first two tasks, and CREATE INDEX handles the last one. Until V6R1, that is.

    Prior to V6R1, CREATE INDEX creates a logical file that contains all fields from the underlying physical file, similar to a logical file in which no field list is given, like the following:

    A          R CUSTREC                   PFILE(COW00)
    A          K COMPANY                               
    A          K CUSTNBR                               
    

    As of V6R1, you can create the index to have one of three field lists: all fields, the key fields only, or key fields plus other fields of your choosing. Specify your choice after giving a name to the record format.

    The following SQL command creates an access path over physical file MyTable, using field SomeField as the key. All fields (columns) are included in the record format, which is named MyRec.

    create index on MyTable (SomeField) 
       rcdfmt MyRec
       add all columns
    

    Here is the equivalent DDS.

    A          R MYREC                      PFILE(MYTABLE)
    A          K SOMEFIELD
    

    This SQL command includes only the key fields in the record format.

    create index on MyTable (SomeField) 
       rcdfmt MyRec
       add keys only
    

    Here is the equivalent DDS.

    A          R MYREC                      PFILE(MYTABLE)
    A            SOMEFIELD
    A          K SOMEFIELD
    

    And this create command includes the key fields and also fields OneField, TwoField, RedField, and BlueField.

    create index on MyTable (SomeField) 
       rcdfmt MyRec
       add OneField, TwoField, RedField, BlueField
    

    Here is the equivalent DDS.

    A          R MYREC                      PFILE(MYTABLE)
    A            SOMEFIELD
    A            ONEFIELD
    A            TWOFIELD
    A            REDFIELD
    A            BLUEFIELD
    A          K SOMEFIELD
    

    This new feature will not affect SQL applications. At present, you can’t reference an index name in the FROM clause of a SELECT statement, and I doubt that’s ever going to change. But native applications will be able to use a subset of fields, a logical-file feature that I have used to advantage many times.

    As much as I like SQL, I don’t think it’s perfect, and I still see plenty of room for improvement. (For further thought, visit http://en.wikipedia.org/wiki/SQL. See Criticisms of SQL and Alternatives to SQL.) If only IBM would give me a way to add an access path to a view. Then I could CHAIN to (read randomly by key) an SQL view from an RPG program. I could use that feature today.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    OCEAN:  Technical conference, June 30, 2008, Irvine, CA
    RJS Software Systems:  Spring Sale! Savings on WebDocs and Value Bundles
    COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    LANSA Touts Eight RAMP Successes Hospital Supplier Installs VAI’s WebSphere-Based Portal

    Leave a Reply Cancel reply

Volume 8, Number 23 -- June 18, 2008
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
Twin Data

Table of Contents

  • Creating an RPG-based Web Service Using WDSC, Part 1
  • SQL May Be Catching Up with DDS
  • Admin Alert: Redundancy is Good, Redundancy is Good, Re…
  • Keeping 5250 Alive
  • Seeking Advice on REXX
  • Admin Alert: All About the System i Attention Light

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle