Modifying sizes of sequence caches

A sequence is a database object that automatically generates unique key values. A sequence can generate one value at a time or can generate a cache of multiple values. The use of sequence caches improves system performance because processes can obtain values from the cache without waiting for the sequence to generate individual values.

About this task

The maxseq sequence is used to generate values for the rowstamps column in database tables. Because the rowstamps column requires values frequently, set the sequence cache size for maxseq to 500. For all other sequences, set the sequence cache size to 50.

If IBM® Tivoli® Asset Management for IT is installed, you must remove some specified sequence names from your SQL script because those sequences must not be altered manually.

Procedure

  1. Run the following command to generate a script file that contains the SQL statements to set the sequence cache size:
    Option Description
    If you are using DB2® db2 "select ’alter sequence maximo.’ || sequencename || ’ cache 50 ;’ from maximo.maxsequence" > change_seq_cache.sql
    If you are using Oracle sqlplus "select ’alter sequence maximo.’ || sequencename || ’ cache 50 ;’ from maximo.maxsequence" > change_seq_cache.sql
  2. Edit the change_seq_cache.sql file to change the sequence cache size for the maxseq sequence to 500.
  3. If Tivoli Asset Management for IT is installed, edit the change_seq_cache.sql file to remove any entries that match the following sequence names:
    Table 1. Sequences to remove from the change_seq_cache.sql file
    Sequence cache name Sequence cache name Sequence cache name
    ASSETATTRIBUTESEQ DPADISKSEQ DPAMSWSUITECOMPSEQ
    CDMCITYPESSEQ DPADISPLAYSEQ DPAMSWSUITESEQ
    CLASSANCESTORUSEQ DPAFILESEQ DPAMSWUSAGERANGESEQ
    CALSSSPECSEQ DPAIMAGEDEVICESEQ DPAMSWUSAGESEQ
    CLASSSPECUSEWITHSEQ DPAIPXSEQ DPAMSWVARIANTSEQ
    CLASSSTRUCTURESEQ DPALOGICALDRIVESEQ DPANETADAPTERSEQ
    OMPSEQ DPAMADAPTERSEQ DPANETDEVCARDSEQ
    RELATIONRULESSEQ DPAMADPTVARIANTSEQ DPANETDEVICESEQ
    RELATIONSEQ DPAMEDIAADAPTERSEQ DPANETPRINTERSEQ
    ACTCIRELATIONSEQ DPAMMANUFACTURERSEQ DPAOSSEQ
    ACTCISEQ DPAMMANUVARIANTSEQ DPASOFTWARESEQ
    ACTCISPECSEQ DPAMOSSEQ DPASWSUITESEQ
    DEPLOYEDASSETSEQ DPAMOSVARIANTSEQ DPATCPIPSEQ
    DPACOMMDEVICESEQ DPAMPROCESSORSEQ DPAUSERINFOSEQ
    DPACOMPUTERSEQ DPAMPROCVARIANTSEQ OMPCIRLNSEQ
    DPACPUSEQ DPAMSOFTWARESEQ  
  4. Run the SQL script on the database to change the sequence cache values.


Feedback