Db2 system names for SQL objects - do I feel lucky?

 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).