• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Wow! I Could Have Had Long Column Names!

    November 16, 2011 Ted Holt

    Sometimes I find out that something useful has been available to me for a long time, but I didn’t know about it. Then I feel like a moron. Today I’m telling you that a certain DB2 for i feature has been around for decades, and you’re probably not using it. However, there’s no need to feel like a moron.

    The feature of which I speak is the database ALIAS. As you well know, DB2 for i, in its native architecture, permits database column (field) names of up to 10 characters. That’s certainly better than the six-character limit for identifiers in RPGs II and III, but laughable by today’s standards. ALIAS provides a way to assign a longer, alternate column name.

    Let’s start with the native interface. The code contains DDS for a database table (physical file):

    A                                      UNIQUE
    A          R CUSREC
    A            CUSTNBR        5P 0       ALIAS(CUSTOMER_NUMBER)
    A            CUSTNAME      20A         ALIAS(CUSTOMER_NAME)
    A            CUSTCLASS      2A         ALIAS(CUSTOMER_CLASS)
    A          K CUSTNBR
    

    In this code, we see the native interface supports alternate, long column names.

    Each column has a short name and a long name. For example, the first column, customer number, has two names: CUSTNBR, and CUSTOMER_NUMBER.

    The SQL equivalent is in this next bit of code:

    create table customers
      (Customer_number for custnbr   dec (5,0) not null with default,
       Customer_name   for custname  char(20)  not null with default,
       Customer_class  for custclass char(2)   not null with default,
       primary key (Customer_number))
      rcdfmt CUSREC
    

    With the SQL interface shown in this code, the short name is the alternate.

    When I say that the second piece of code is the SQL equivalent of the first piece, I’m serious. Both of these code examples produce the same database file, to the point that the record formats have the same level identifier. Yes, that’s right. A program compiled against one table will run against the other without a level check.

    What you do with these short and long names depends. RPG programmers and query users can’t use the long names. SQL users can use either one. The SQL query in the following code uses long names:

    select CUSTOMER_NUMBER, CUSTOMER_NAME, CUSTOMER_CLASS
    from customers
    where customer_class='CC'
    

    As you can see, SQL takes advantage of long column names.

    But SQL can just as easily use the short names. In fact, as this code illustrates, SQL can mix and match long and short names in the same query:

    select CUSTNBR, CUSTNAME, CUSTCLASS
    from customers
    where customer_class='CC'
    

    Here we see, SQL can use both short and long column names in the same query.

    Under IBM i 7.1, the RPG compiler can access the long column names through an externally defined data structure. I copied the RPG code in shown below from the RPG reference manual and modified it to fit my example:

    D custDs        e ds                  ALIAS
    D                                     QUALIFIED EXTNAME(custFile)
     /free
         custDs.customer_number = 12345; 
         custDs.customer_name = 'John Smith';
         custDs.customer_class = 'AA';
    

    As demonstrated here, under 7.1, RPG can access long column names as data structure subfields.

    Now that you know about aliases, what are you going to do with them? Today would be a great day to add the ALIAS keyword to the DDS of a physical file.



                         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
    Rocket Software

    Unlock the full potential of your data with Rocket Software. Our scalable solutions deliver AI-driven insights, seamless integration, and advanced compliance tools to transform your business. Discover how you can simplify data management, boost efficiency, and drive informed decisions.

    Learn more today.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Micro Focus:  RUMBA for iSeries, the world's most used terminal emulation software
    The 400 School:  Fall Training Sale – Discounts up to 40%! RPG IV COBOL CL Admin Security
    ASNA:  Wings™ - The faster, easier way to a better System i user interface

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Linoma: DMZ Gateway Tightens File Transfer Security Feeling Like A Heel

    Leave a Reply Cancel reply

Volume 11, Number 35 -- November 16, 2011
THIS ISSUE SPONSORED BY:

Vision Solutions
SEQUEL Software
WorksRight Software

Table of Contents

  • NULL and NOT IN
  • Wow! I Could Have Had Long Column Names!
  • Putting Your i System in Semi-Restricted State

Content archive

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

Recent Posts

  • 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
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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