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.