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.