• 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
    Midrange Dynamics North America

    Accelerate Change & Integration on IBM i

    Good change management unites IBM i and open systems development for productive collaboration. Developers work with their preferred tools and IDEs. Ultimate version control and traceability mean fast bug fixes and less stress. Rollback to a stable version in seconds.

    Change management gives managers, operations teams, and auditors the visibility they need, and developers can focus on what they like best: building great applications.

    Learn More

    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

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

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