Guru: Wow! I Could Have Had Long Column Names! – Take 2

October 23, 2017 Ted Holt

In 1990, three computer science professors named Gorla, Benander, and Benander wrote about debugging effort in COBOL programs. Among their claims was that debugging is easier if variable names were between 10 and 16 characters long. The original native data definition facilities allowed variable names up to 10 characters, but nowadays we can define alias names that Gorla, Benander, and Benander would be proud of.

It is common in many DB2 for i shops to have physical files with field names of six characters or less. This practice dates to predecessor systems, such as the System/36, for which the programming language of choice was RPG II. Replacing short field names with longer ones is impractical in many cases, but if you are in this situation, you can easily add alias column names.

Adding Alias Names to Physical Files

Adding alias field names is easy with DDS-described files. Use the ALIAS keyword and run the Change Physical File (CHGPF) command.

Here’s part of the DDS for a file named NCUST.

R CUSTREC            
  COMPANY        3P 0
  CUSTID         7P 0
  NAME          20A  
  CRDLMT         7P 2

Here’s the same DDS after alias names have been added.

R CUSTREC                                          
  COMPANY        3P 0 
  CUSTID         7P 0       ALIAS(CUSTOMER_ACCOUNT)
  NAME          20A 
  CRDLMT         7P 2       ALIAS(CREDIT_LIMIT)

Here’s the necessary CHGPF command.

CHGPF FILE(MYLIB/NCUST) SRCFILE(MYLIB/QDDSSRC)

Let me add a few points.

First, the record level ID does not change when you add aliases. You will not have to recompile the programs that use the file.

Second, logical files that use the same record format that the physical file does will also have the new aliases. Logical files that do not use the same record format will not have the aliases.

Third, most native interfaces cannot use the aliases. For example, Copy File (CPYF) must refer to CRDLMT, not CREDIT_LIMIT.

CPYF FROMFILE(NCUST) . . . INCREL((*IF CRDLMT *LE 250))

The RPG IV and COBOL compilers can use aliases. In RPG, add the ALIAS keyword to the file definition (F spec or DCL-F).

FNCust     if   e             disk    alias

dcl-f  NCust  alias;

COBOL programs use the DD option of the COPY directive.

environment division.
input-output section. 
    select Customer-file assign database-ncust.

data division. 
file section. 
fd  Customer-file. 
01  copy dd-custrec-i of ncust.

Adding Alias Names to SQL Tables

It is also possible to add alias names to SQL tables. To do so, use CREATE OR REPLACE TABLE, a fairly new option for the CREATE TABLE statement.

Assume table NCUSTOMERS was created with the short names that had been in use for decades.

create table ncustomers
   ( Company     dec(3),
     CustID      dec(7),
     Name        char(20),
     CrdLmt      dec(7,2),
  primary key (Company, CustID))

Here’s how to add the alias names.

create or replace table ncustomers
(Company                            dec(3),
 CustomerAccount for column CustID  dec(7),
 Name                               char(20),
 CreditLimit     for column CrdLmt  dec(7,2) )

The system column names and definitions are the same as before. Only the SQL column names (i.e. the aliases) have changed.

Any views over this table will not automatically get the alias names. You’ll have to recreate those views.

It’s been almost six years since I last wrote about aliases. I’m glad to see that some things have changed for the better in the meantime.

RELATED STORIES

Debugging Effort Estimation Using Software Metrics

Wow! I Could Have Had Long Column Names!