TechTip: Direct SQL Control of System Names

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

TR6's new direct control functionality simplifies the creation and management of SQL objects with long names.


The recently announced Technology Refresh 6 for the IBM i 7.1 release includes an enhancement that allows users to directly control the short system name that is used SQL tables, views, and indexes.


One benefit of using SQL is that it allows you to create DB2 objects, such as tables and indexes, with names longer than 10 characters; the maximum identifier length is 128. With longer names at your display, your DB2 for i SQL object names can be self-describing and more user-friendly. However, one tradeoff when using the longer SQL names is the fact that all of the IBM i operating system commands, such as the Save Object (SAVOBJ) command, have an object name limit of 10 characters.


Obviously, you need to be able to use system commands with your SQL-created objects. Thus, DB2 for i tries to help you out by automatically generating a short system name whenever you create an SQL object with an identifier greater than 10 characters in length. This is a good news and bad news situation.


The good news is that you have a name that can be used with the IBM i system commands. The bad news is that the generated name is not user-friendly. DB2 takes the first five characters of the SQL name and then appends a five-digit number to the end of the string. For example, an SQL table with name of Customer_Master would have a system-generated name of CUSTO00001. If CUSTO00001 is already being used for another object in the target library, then DB2 will keep incrementing the numeric portion until a unique name is found.


This behavior means that not only is the system-generated name ugly, but it's also not guaranteed to be the same when the table is recreated in a different library or on a different system. Thus, IBM provided the RENAME TABLE and RENAME INDEX statements to enable developers to supply the system name. This technique is demonstrated in the following SQL script, where the RENAME statement is used to assign a system name of cusmst to replace the system name generated by DB2 on the CREATE TABLE statement.


CREATE TABLE dbtest/customer_master
(customer_name CHAR(20),
customer_city CHAR(40))


RENAME TABLE dbtest/customer_master TO SYSTEM NAME cusmst


Other developers mixed in the Rename Object (RNMOBJ) command to change the system name for an SQL object.


While this rename approach solves the system name issue, the problem is that it requires multiple SQL statements to be used. Developers have to remember that creation of the table requires more than one statement. In addition, when someone uses the IBM i Navigator Generate SQL function to retrieve the SQL source for the customer_master table, only the CREATE TABLE statement would be returned. There's no indication that a RENAME statement is associated with the table.


Thus, the most recent IBM i Technology Refresh delivers the FOR SYSTEM NAME clause shown in the following example to enable the CREATE TABLE statement to directly control the system name.


CREATE TABLE dbtest/customer_master

(customer_name CHAR(20),
customer_city CHAR(40))


Now, developers have only a single statement to manage and have a simple way of clearly identifying the system name in their SQL source. The FOR SYSTEM NAME clause is supported for the following SQL statements; all you have to do is load the IBM i 7.1 Database Group PTF level 22.




This new ability to directly control is sure to simplify the creation and management of SQL objects with long names.