TechTip: Direct SQL Control of System Names PDF Print E-mail
Tips & Techniques - SQL
Written by Kent Milligan   
Friday, 12 April 2013 00:00

Support MC Press - Visit Our Sponsors

Forums Sponsor



Search Sponsor



  Bookstore Sidebar Header




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.

Kent Milligan
About the Author:

Kent Milligan is a Senior Technical Team Lead on the Emerging Technology Delivery team for the IBM Watson for Clinical Trial Matching solution. Prior to joining the Watson team in 2015, Kent spent the first 25 years of his career working as a DB2 for IBM i consultant and developer working out of the IBM Rochester lab.



Read Kent's "DB2 farewell" blog:


Last Updated on Friday, 12 April 2013 00:00
User Rating: / 1