IBM Support

Problems with Alternative or Long Names and CPYF, MOVOBJ, or CRTDUPOBJ

Troubleshooting


Problem

This document explains that special considerations are provided for system commands used against files with long file names.

Resolving The Problem

This document explains that special considerations are provided for system commands used against files with long file names.

Problem: CPYF, MOVOBJ, and CRTDUPOBJ fail with message CPF327E RC2.

Solution: When a table is created with a long/alternative table name (more than 10 characters), the system naming rules are used to create the object in the specified library. The system-wide database catalog files are updated with the long name information. System naming rules for a table name create an IBM OS/400 or IBM i5/OS object with the first 5 characters of the name followed by a 5-digit number. More detailed information on System Naming rules can be found in the SQL Reference.

For example:

CREATE TABLE    LIBA/VERYLONGTABLENAME   (ONE CHAR ( 10) NOT NULL WITH
DEFAULT, TWO CHAR ( 20) NOT NULL WITH DEFAULT)      

Library LIBA contains the following physical file as a result of the CREATE TABLE:
                                                 
 Opt  Object      Type      Library     Attribute
      VERYL00001  *FILE     LIBA        PF  
                                                 
The system cross-reference files contain the following entry for the table:

....+....1....+....
 TABLE_NAME        
                   
 VERYLONGTABLENAME  

..252....+..253....+..254....+..255....+
 SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
                                       
                 VERYL00001          LIBA        


When a CPYF of the file using the system name is done to a new file, the CPYF fails with a message CPF327E RC2. See the example below:

CPYF FROMFILE(LIBA/VERYL00001) TOFILE(LIBA/MYTABLE) CRTFILE(*YES)
 Alternative name for file MYTABLE not allowed.                    
 Error creating file MYTABLE in library LIBA.                      
 Copy command ended because of error.  

The CPYF command fails because the CRTFILE(*YES) option causes a CRTDUPOBJ to be attempted for the VERYL00001 file. Even though a new file name is specified, the long name associated with the file remains with the new file and the create duplicate object fails because there is already a long name (VERYLONGTABLENAME) in the system cross-reference files in library LIBA.

Additional Message Information                        
                                                                               
 Message ID . . . . . . :   CPF327E       Severity . . . . . . . :   40        
 Message type . . . . . :   Diagnostic                                        
 Date sent  . . . . . . :   04/30/02      Time sent  . . . . . . :   16:48:30  
                                                                               
 Message . . . . :   Alternative name for file MYTABLE not allowed.            
 Cause . . . . . :   An attempt was made to create, move, or change database  
   file MYTABLE with alternative name VERYLONGTABLENAME into library LIBA.    
   However, the operation was not done for reason code 2.  The reason codes    
   are:                                                                        
     1 - The alternative name is not a valid database alternative name.        
     2 - The alternative name is a duplicate of the alternative name for      
   database file VERYL00001 that already exists in library LIBA.              
     3 - The alternative name could not be verified as a duplicate of other    
   file's alternative names in library LIBA because of problems with the system
   cross reference files.                                                      
     4 - Only one alternative name is allowed.                                

The same CPYF to library LIBB works because there is no alternative file name present for LIBB.

CPYF FROMFILE(LIBA/VERYL00001) TOFILE(LIBB/MYTABLE) CRTFILE(*YES)
Physical file MYTABLE created in library LIBB.                    
Member VERYL00001 added to file MYTABLE in LIBB.                  
1 records copied from file VERYL00001 in LIBA.  

The system cross-reference files now contain the information for two objects with the same long or alternative name in different libraries.

SELECT * FROM QSYS2/SYSTABLES WHERE SYSTEM_TABLE_SCHEMA= 'LIBA'
  or SYSTEM_TABLE_SCHEMA= 'LIBB'                                
                                                                                                                 
 ....+....1....+....2....
 TABLE_NAME              
                         
 VERYLONGTABLENAME      
 VERYLONGTABLENAME      


.252....+..253....+..254....+..255....
SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
                                     
   VERYL00001          LIBA          
MYTABLE              LIBB .

An IBM SQL/400 RENAME can be used to change the long or alternative table name. In this example, LIBA must be in your library list, and *SYS naming must be used for the ISQL session, for example:

ADDLIBLE LIBA
STRSQL
rename verylongtablename to verylongtablenameold for system name    
veryl00001                                                          
RENAME for VERYL00001 in LIBA completed.  

Now the system cross-reference files contain the following entries. Note that VERYLONGTABLENAME is now unique.

....+....1....+....2....+....3....+.
TABLE_NAME                          
                                   
VERYLONGTABLENAMEOLD                
VERYLONGTABLENAME                  
********  End of data  ********    
                                   
..252....+..253....+..254....+..255....+
 SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
                                       
    VERYL00001          LIBA            
    MYTABLE              LIBB            


Starting in R610, if the library is QTEMP you will see this issue.

The logic is as follows:

o If you are allowed to create two files in QTEMP with the same "long name", then when you run a query over that long name, which file should we return records from?  
o After the CRTDUPOBJ both copies of the file may have the same records; however, they may not (in other words, one could be empty).
o Additionally, a process could INSERT, UPDATE, and/or DELETE rows in one version ofthe file, but not the other.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0;6.1.1;6.1.0;5.4.5;5.4.0","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

Historical Number

26753550

Document Information

Modified date:
18 December 2019

UID

nas8N1017004