Guru: Retrieving The Long And Short Object Name

August 14, 2023 Bob Cozzi

Many releases ago, IBM i received “Long SQL Names” for files and libraries. These new longer names (up to 128 characters) were well received by SQL enthusiasts, but largely ignored by the mainstream IBM i developer. As each version of IBM i emerged, more and more shops experienced one or more objects with a longer-than-10-character name.

Recently I created a file named BOAT_TRAFFIC. This name is clearly longer than 10 characters. I used SQL DDL (the CREATE or REPLACE TABLE statement) to create the file. Using SQL DDL is the only real way to create an object with a long name – the IBM i system Command Interface (i.e., CL commands) haven’t been enhanced to support long name. This isn’t a CL or command limitation. CL commands have always been able to support 128-byte names – in fact, back in 1980 when I wrote my first user-written CL command, it accidentally specified a length of 32 for the object name parameter and it worked! It seems that updating core CL to support long object and library names is “never gonna happen.”

Here’s a shorten version of the SQL DLL I used to create the BOAT_TRAFFIC file (I mean Table):

CREATE or REPLACE customs.BOAT_TRAFFIC (
   CID  INTEGER  PRIMARY KEY                             
        GENERATED ALWAYS AS IDENTITY                   
          (START WITH 101, INCREMENT BY 1),           
 INVNBR      DEC(7,0) NOT NULL DEFAULT 0 ,               
 INVDATE     DATE NOT NULL DEFAULT CURRENT_DATE ,        
 CONTACT     VARCHAR(30) NOT NULL DEFAULT '',            
 DESTINATION VARCHAR(50) NOT NULL DEFAULT '',            
 BOATNAME    VARCHAR(30) NOT NULL DEFAULT ''
) RCDFMT TRAFFIC;

No Place For Ye Olde CPYF Command

If I were to copy this file to a developer library or a back-up library, I would typically use CRTDUPOBJ or CPYF depending on the context. Let’s say I want to use CPYF because I want to refresh the data in a developer library with the current data. I would end up with a Command Entry screen that looks like the image below:

I could actually use SQL to do the copy operation. I’d first have to clear the data in the target file, and then copy the new data over using the SQL INSERT statement. Perhaps something like this:

INSERT INTO BOBDEV.BOAT_TRAFFIC
SELECT * FROM customs. BOAT_TRAFFIC

I say this kind of works, because I have to (A) clear the target file using the TRUNCATE or similar statement, and (B) copy the records while accommodating the IDENTITY column. To do that, I add the OVERRIDING XXX VALUE to the INSERT statement like this:

TRUNCATE TABLE BOBDEV.BOAT_TRAFFIC
     REUSE STORAGE
     IGNORE DELETE TRIGGERS
     IMMEDIATE;
INSERT INTO BOBDEV.BOAT_TRAFFIC OVERRIDING SYSTEM VALUE
SELECT * FROM customs. BOAT_TRAFFIC;

Normally when rows are added to a file that has an IDENTITY column the database increments the IDENTITY column automagically. To circumvent that, you can use the OVERRIDING USER VALUE or the OVERRDING SYSTEM VALUE clause.

OVERRIDING USER VALUE means the INSERT’s subquery (i.e., the SELECT statement) contains the IDENTITY column value, but you want the system to ignore that value and generate a new one for each newly added row.

OVERRIDING SYSTEM VALUE means the INSERT’s subquery (i.e., the SELECT statement) contains the IDENTITY column value and you want the system to retain that value by copying it to the target file.

Clearly in this example, we want OVERRIDING SYSTEM VALUE.

Other Long Names

Besides database files, SQL has other objects; SEQUENCE, ALIAS, FUNCTION, PROCEDURE, etc. All of these support up to 128-byte long SQL names. For example, suppose we create an SQL ALIAS and given it a long name. ALIAS objects are often used by IBM i shops typically for Member processing. Since SQL is unaware of Members, IBM enhanced ALIAS’s so they can be used to access specific members of a file.

CREATE or REPLACE ALIAS CUSTOMS.BOAT_TARIFF_BAHAMAS 
        FOR CUSTOMS.TARIFF(BAHAMAS);      

In this example, I have created an ALIAS named BOAT_TARIFF_BAHAMAS in the CUSTOMS library. I can on use this ALIAS in a SELECT statement to query the BAHAMAS member of the TARIFF file. ALIAS’s, like all other SQL types store the long name to IBM i system object name in the SQL Catalog.

SQL Catalog

Database files do store their long SQL name in the file’s object description and provide access to that name via the QDBRTVFD API. There is also another API named Retrieve Short Name (QDBRTVSN) that returns the IBM i object name for long SQL names. However, it too only applies to database files and more recently, library names.

All other object types that have a long SQL name are cross-referenced in the SQL catalog. IBM makes uses a fairly consistent naming convention with their catalog names making it somewhat easy to remember which catalogs you need to query. Here’s a list of the catalog file names contains the properties for all SQL objects and many IBM i objects that are implicitly SQL objects (such as Physical and Logical files):

The SYSTABLES catalog is used for various types of files/tables, including:

Seamless Short To Long Name Function

I started out a few years ago using the QDBRTVSN API to convert a long SQL name to the short IBM i system object name. But once I started using SEQUENCES, Functions, Triggers, and so forth, I got frustrated. My initial approach was to use the catalog and dynamically query the specific catalog file based on the type. But this wasn’t always intuitive or practical. So, I developed a better interface.

The GETOBJNAME SQL Function accepts a regular IBM i short system object name and library, or a long SQL name and long or short schema name and returns both the long and short name for the object to the caller. Only 1 row is returned with all six attributes, including:

Here’s an example of it being used in RPG IV:

   dcl-s objname  varchar(10);
   dcl-s objlib   varchar(10);
   dcl-s objtype  varchar(10);
   dcl-s longName varchar(128);
   dcl-s longLib  varchar(128);
   dcl-s sType    varchar(18);

   EXEC SQL SELECT objname, objlib, objtype,
                   longObjName, longObjLib, SQL_OBJECT_TYPE
             INTO :objName, :objLib, :objType,
                  :longName,:longLib, :sType
             FROM TABLE(SQLTOOLS.getObjName(
                         '*LIBL',' BOAT_TARIFF_BAHAMAS','ALIAS')
                        );
    If (SQLState < '02000'); 
      // We have the Short Object Name!  
    endif;   

The GETOBJNAME SQL Function

The GetObjName SQL function returns both the long and short names for any object. The vast majority of your objects will only have short names and likely no SQL Object Type. When that is the case the LONGOBJNAME and LONGOBJLIB columns will contain the same values as the OBJNAME and OBJLIB column respectively, and the SQL_OBJECT_TYPE will be NULL.

GETOBJNAME works on IBM i 7.2 and later. To create the function on your system, cut/paste the code the follows into a source member and run the RUNSQLSTM command over that member. Alternatively, you can paste the code into IBM ACS RUN SQL Scripts interface and run it directly. Note that since it is shipped with SQL Tools, that is the library/schema name being used.

If you prefer the GitHub thingy, you can go over to my GitHub page at https://github.com/bobcozzi/GETOBJNAME and download it directly.

Bob Cozzi is an IBM i contractor and consultant as well as the author of The Modern RPG Language, developer of SQL iQuery and SQL Tools, and a speaker on SQL and RPG IV topics you can actually use.

RELATED STORIES

Guru: Object Usage Statistics

Guru: Binding Directory Entries

Guru: Find Unused Objects On IBM i Using SQL

What’s In the Top 5 Hottest IBM i RFEs

Cozzi Refines Data-Centric Ideas For IBM i Report Writer

Cozzi Updates RPG xTools, Partners with Linoma