IBM Support

IC86703: SYSPROC.ALTOBJ FAILS WITH -204 ERROR FOR LOWERCASE OBJECT NAMES AND -305 ERROR IN DB2_COMPATIBILITY_VECTOR

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • This APAR deals with two issues with the SYSPROC.ALTOBJ routine:
    
    Problem 1:
    
    SYSPROC.ALTOBJ returns a -443 error with a SQLCODE of -204
    (object not found) when the object passed into SYSPROC.ALTOBJ is
    in lowercase as opposed to uppercase.
    
    For example:
    
    C:\>db2 "create table test(id varchar(20),name varchar(40),phone
    varchar(20))"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "insert into test values ('xx','bill','1234567890')"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "insert into test values ('yy','sam','1234567890')"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR', 'create
    table test(id varchar(20), name varchar(50), phone
    varchar(20))', -1, ?)"
    SQL0443N  Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
    returned an error SQLSTATE with diagnostic text "SQL0204 Token:
    DB2ADMIN .test".
    SQLSTATE=38553
    
    C:\>db2 "CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR', 'create
    table TEST(id varchar(20), name varchar(50), phone
    varchar(20))', -1, ?)"
    
      Value of output parameters
      --------------------------
      Parameter Name  : ALTER_ID
      Parameter Value : 1
      Parameter Name  : MSG
      Parameter Value : SELECT OBJ_TYPE, OBJ_SCHEMA, OBJ_NAME,
    SQL_OPERATION, SQL_STMT, EXEC_MODE, EXEC_SEQ FROM
    SYSTOOLS.ALTOBJ_INFO_V WHERE ALTER_ID=1 AND EXEC_MODE LIKE
    '_1______'  ORDER BY EXEC_SEQ.
      Return Status = 0
    
    
    Using lowercase "test" failed, but uppercase "TEST" worked.
    SYSPROC.ALTOBJ should be able to process the statement by
    defaulting the object name in uppercase unless the object name
    is enclosed in double quotes.
    
    
    Problem 2:
    
    SYSPROC.ALTOBJ returns a -443 error with a SQLCODE of -305 (NULL
    cannot be used) when DB2_COMPATIBILITY_VECTOR=ORA is set.
    
    For example:
    
    C:\>db2set -all
    ...
    [i] DB2_COMPATIBILITY_VECTOR=ORA
    ...
    
    C:\>db2 connect to sample
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.6
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    C:\>db2 "create table test(id varchar(20),name varchar(40),phone
    varchar(20))"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "insert into test values ('xx','bill','1234567890')"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "insert into test values ('yy','sam','1234567890')"
    DB20000I  The SQL command completed successfully.
    
    C:\>db2 "CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR', 'create
    table TEST(id varchar(20), name varchar(50), phone
    varchar(20))', -1, ?)"
    SQL0443N  Routine "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
    returned an
    error SQLSTATE with diagnostic text "SQL0305  ALTER_ID=1".
    SQLSTATE=38553
    

Local fix

  • For problem 1, specify the object name in uppercase.    For
    problem 2 manually alter the table using the "ALTER" DB2 command
    if DB2_COMPATIBILITY_VECTOR is required.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * users who use altobj procedure                               *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * upgrade to v9.7fp8                                           *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC86703

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-09-20

  • Closed date

    2013-04-01

  • Last modified date

    2013-04-01

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC87091

Fix information

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC86703

Modified date: 01 April 2013