OA46615: USAGE IMPORT FAILS WHEN ATTEMPTING TO INSERT IDENTICAL RECORDS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE = -803, ERROR:  AN INSERTED OR UPDATED VALUE IS INVALID.
  Error occured during NODE_CAPACITY insertRecord
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All IBM Tivoli Asset Discovery for z/OS      *
  *                 V8.1 users.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: When an LPAR is run as a VM guest,      *
  *                      it is possible for duplicate records    *
  *                      to occur when inserting into the        *
  *                      NODE_CAPACITY table.                    *
  ****************************************************************
  * RECOMMENDATION: N/A                                          *
  ****************************************************************
  This type of error can occur for LPARs that are run as part of
  a VM Guest.  It is possible that duplicate records are
  generated but the existing index definition does not allow
  for insertion of duplicate records.
  

Problem conclusion

• Drop the existing index in the NODE_CAPACITY table and create
  a new index with a different key.
  
  The following action is required:
  
  COMMENT
  (***************************************************************
   * FUNCTION AFFECTED: Tivoli Asset Discovery for z/OS          *
   ***************************************************************
   * DESCRIPTION      : Installation notes                       *
   ***************************************************************
   * TIMING           : POST-APPLY                               *
   ***************************************************************
   **************************************************************)
  
   NOTE:
   The following changes only apply to existing repositories
   where you do not need to run the customization job HSISCUST.
  
   For repositories that use DB2 databases, run the following SQL
   statements to delete the existing index and create a new index
   with a different key in the repository database.  These
   changes are usually implemented by a DBA and should only be
   performed after the normal TADz database backup.
  
   Replace parameter values for:
    a. &REPZSCHM
    b. &SGHSIIDX
    c. &BPIX
  
   1. Delete duplicate records, but still retain the original
      record. In most cases, there are no duplicates to delete.
  
      DELETE FROM &REPZSCHM.NODE_CAPACITY
       WHERE RID(&REPZSCHM.NODE_CAPACITY) NOT IN
       (SELECT MAX(RID(&REPZSCHM.NODE_CAPACITY))
         FROM &REPZSCHM.NODE_CAPACITY
         GROUP BY NODE_KEY, PERIOD, METRIC_TYPE, QUANTITY,
          MODEL_CAPACITY);
      COMMIT ;
  
      SELECT COUNT(*) FROM &REPZSCHM.NODE_CAPACITY ;
  
   2. Drop Primary Key and existing index
  
      ALTER TABLE &REPZSCHM.NODE_CAPACITY DROP PRIMARY KEY ;
      DROP INDEX &REPZSCHM.PKNODECP ;
      COMMIT ;
  
   3. Create new index with different key.
      See existing member HSISSQ18 in PARMLIB for parameter values
  
      CREATE TYPE 2 UNIQUE INDEX &REPZSCHM.PKNODECP
         ON &REPZSCHM.NODE_CAPACITY
         (NODE_KEY, PERIOD, METRIC_TYPE, QUANTITY,
          MODEL_CAPACITY)
         USING STOGROUP  &SGHSIIDX
         PRIQTY      48
         SECQTY      -1
         ERASE NO
         BUFFERPOOL &BPIX
         CLOSE NO;
      COMMIT;
  
   4. Verify the new index contains a different key. You should
      expect 5 rows to be displayed.
  
      SELECT * FROM SYSIBM.SYSKEYS
      WHERE IXNAME = 'PKNODECP'
      AND IXCREATOR = '&REPZSCHM'
      ORDER BY COLNO;
  
   5. Copy and replace the following updated member from the
      target SHSIPARM to PARMLIB. As this member is quite large,
      just copy/paste the changes for table NODE_CAPACITY and
      its index PKNODECP from SHSIPARM to PARMLIB. After the
      copy/paste, replace values for &REPZSCHM, &SGHSIIDX
      and &BPIX.
  
      HSISSQ18
  
   6. Rerun Usage Import job HSISUIMP if you have previously
      encountered error SQLCODE -803. Use the same input Usage
      file for the run. If the job fails with the same error,
      review your implementation steps.
  
  
   ===============================================================
   For repositories that use SQLite databases, run the following
   SQL statements to delete the existing index and create a new
   index with a different key in the repository database.
  
   Within each SQL statement, replace parameter value for:
    &REPZSCHM
  
   1. Run job HSISUT01 from JCLLIB
  
      This will backup the zFS filesystem before database changes
      are applied. The name of the zFS filesystem is based on
      parameters defined in the customization job HSISCUST.
      It should be &HSIINST.&SYS.ZFS.
  
   2. Running SQL statements
  
      Make a copy of HSISTPRM job from JCLLIB.  In the new job,
      copy/paste the SQL statements after the //SYSIN DD *
      statement.  Using this new job, run each set of SQL
      statements separately.
  
   3. Drop the existing index
  
      DROP INDEX &REPZSCHM.PKNODECP ;
  
   4. Rename the existing table to a different name
  
      ALTER TABLE &REPZSCHM.NODE_CAPACITY RENAME TO
             NODE_CAPACITY_OLD ;
  
   5. Create table and new index with a different key
  
      CREATE TABLE &REPZSCHM.NODE_CAPACITY (
             NODE_KEY          CHAR(32) NOT NULL ,
             PERIOD            DATE NOT NULL ,
             START_TIME        TIMESTAMP NOT NULL ,
             END_TIME          TIMESTAMP ,
             METRIC_TYPE       CHAR(10) NOT NULL ,
             LAST_UPDATE_TIME  TIMESTAMP ,
             QUANTITY          INTEGER ,
             MODEL_CAPACITY    CHAR(4) )
             IN &REPZSCHM.VAGGR
             VOLATILE ;
  
      CREATE TYPE 2 UNIQUE INDEX &REPZSCHM.PKNODECP
             ON &REPZSCHM.NODE_CAPACITY
                (NODE_KEY, PERIOD, METRIC_TYPE, QUANTITY,
                 MODEL_CAPACITY)
             USING STOGROUP
             PRIQTY      48
             SECQTY      -1
             ERASE NO
             BUFFERPOOL
             CLOSE NO;
  
   7. Insert records in newly created table by copying contents
      from renamed table.
  
      INSERT INTO &REPZSCHM.NODE_CAPACITY
       SELECT * FROM &REPZSCHM.NODE_CAPACITY_OLD ;
  
      SELECT COUNT(*) FROM &REPZSCHM.NODE_CAPACITY ;
  
   8. Drop the renamed table
  
      DROP TABLE &REPZSCHM.NODE_CAPACITY_OLD ;
  
   9. In the event of a failure or if the SQLite database is
      corrupted, run HSISUT02 job from JCLLIB to restore the
      zFS filesystem. This will set the database back to the
      point before any changes were implemented.
      After the restore, repeat steps (3) through to (8).
  
   10. Copy and replace the following updated member from the
       target SHSIPARM to PARMLIB. As this member is quite large,
       just copy/paste the changes for table NODE_CAPACITY and
       its index PKNODECP from SHSIPARM to PARMLIB. After the
       copy/paste, replace parameter value for &REPZSCHM.
  
       For the index, refer to the existing member before the
       copy as parameters &SGHSIIDX and &BPIX have no values
       (blanks).  Only the columns for the key have changed.
  
       HSISSQ18
  
   11. Rerun Usage Import job HSISUIMP if you have previously
       encountered error SQLCODE -803. Use the same input Usage
       file for the run. If the job fails with the same error,
       review your implementation steps.
  
   ***************************************************************
  

Temporary fix

Comments

APAR Information

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

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

  UA75856

Modules/Macros

•    HSISSQ18
  

Fix information

• Fixed component name

  TIV ASSET DISC

• Fixed component ID

  5698B39TD

Applicable component levels

• R810 PSY UA75856

     UP14/12/17 P F412

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.