Wow! I Could Have Had Long Column Names!

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