In the past, I’ve written about the performance considerations of using longer, more descriptive names
for you SQL object names on Db2 for i. SQL object names can be up to 128
characters long. In contract, the names for DDS-created Db2 objects are limited
to 10 characters. There’s also a usability consideration when utilizing SQL
object or column names greater than 10 characters in length.
The reason that there’s a usability
concern is that IBM i commands (e.g., Save Object) and interfaces support
object names up to 10 characters in length. Does that mean you’re out of luck
when it comes to using these legacy interfaces with SQL objects which have long
object names? No - Db2 for i “helps” you
out by generating a system name which can be used with these non-SQL interfaces
when your SQL object has a long name greater than 10 characters in length.
The good news is that Db2
automatically generates these system names for you … and the bad news is that Db2 automatically
generates these system names for you. This
good news and bad news conundrum is why this entry title asks: Do I feel lucky?
When an SQL table
name is greater than 10 characters in length (e.g., EMPLOYEE_MASTER), Db2 for i
will generate a 10 character system name. The Db2 system name generation
algorithm takes the first 5 characters of the table (i.e., EMPLO) and then uses
a 5 digit number for the suffix. The 5 digit number is incremented for each SQL
object that starts with the same 5 characters to guarantee that the system name
will be unique within the target schema (or library) that will house the SQL table.
Assuming that the
EMPLOYEE_MASTER is the first table created within the database, the generated
system name will be: EMPLO00001. This generated name can be used on IBM i
commands and will be used on interfaces or commands that return a list of
objects such as the Display Library (DSPLIB) command. Obviously, this generated
system name is not user friendly. In addition, the generated system name can
vary from system to system depending on the order your SQL objects get created.
Let’s say the employee database has a second table named EMPLOYEE_EMAIL in
addition to EMPLOYEE_MASTER. On the development system, EMPLOYEE_MASTER was
created first and EMPLOYEE_EMAIL table created second. The system names for
these two tables respectively would be EMPLO00001 and EMPLO00002. Let’s assume that on the test system, the
table creation process is reversed, and the EMPLOYEE_EMAIL table is created
first. As a result, the EMPLOYEE_EMAIL table will have a system name of
EMPLO00001 on the test system and EMPLO00002 on the development system.
This potential
difference in system names is not ideal, so it’s strongly recommended to
provide a system name for Db2 to use instead of having Db2 for i generate a
random name. A system name can be easily provided using the FOR SYSTEM NAME clause on
your SQL CREATE statements as shown in the following example.
CREATE TABLE employee_master FOR SYSTEM NAME empmast (
emp_id CHAR(6),
emp_fname VARCHAR(20),
emp_lname VARCHAR(30),
emp_dept_number CHAR(3))
Now, the EMPLOYEE_MASTER
table will always have a system name of EMPMAST. Luck is no longer involved in
terms of the table creation order determining if the system name is EMPLO00001
or EMPLO00002. Just as important, the user now has a system name which is user friendly. Let’s say you’re looking at output from the
DSPLIB command would you rather see EMPMAST or EMPLO00001?
The same usability
issue exists with column names longer than 10 characters in length - the emp_dept_number
column is an example of this in the earlier example. The generated system name would
be EMP_D00001. By the way, this ugly
generated column name will be what you get by default if you have the compiler
generate an externally described data structure based on the table definition using
DCL-DS in RPG or the COBOL COPY statement.
The good news is
that the FOR COLUMN clause can be used to override the generated name as the
following example demonstrates. The system name for EMP_DEPT_NUMBER is now EDEPTNUM
instead of the ugly and hard to comprehend name of EMP_D00001.
CREATE TABLE employee_master FOR SYSTEM NAME empmast (
emp_id CHAR(6),
emp_fname VARCHAR(20),
emp_lname VARCHAR(30),
emp_dept_number FOR COLUMN edeptnum CHAR(3))
It should now be clear
that the lucky developer is the one using their own system names (EMPMAST
& EDEPTNUM) instead of relying on the names automatically generated by Db2
(EMPLO00001 & EMP_D00001).
