XMA: AUDSESS table is full - how to rebuild and resize the AUDSESS table

XMA: AUDSESS table is full - how to rebuild and resize the AUDSESS table

The steps below can be used to rebuild the AUDSESS table and its indexes. This process rebuilds the table and indexes and then reloads the old data, so nothing is lost. 

Initial sizing suggestions are provided and can be modified as needed.

 
1. Logon as the XMA owner and stop XMA pathway 
XMA_PWSTOP
 
2.   go to the XMA database location:
XMA_DBVOLUME
 
3.   start SQLCI:
SQLCI
 
4.  Rename the current table and its indexes to a new location:
ALTER TABLE XMADAT.AUDSESS  RENAME XMADATO.AUDSESS;
ALTER INDEX XMADAT.AUDSEIND RENAME XMADATO.AUDSEIND;
ALTER INDEX XMADAT.AUDSRIND RENAME XMADATO.AUDSRIND;
 
5.  Create a new table and indexes with larger extents:
CREATE TABLE XMADAT.AUDSESS
  (
    PARTITIONKEY                     NUMERIC(4, 0) DEFAULT 1 NOT NULL
  , RECORDSESSIONKEY                 TIMESTAMP DEFAULT CURRENT NOT NULL
  , RECORDINSTALLKEY                 TIMESTAMP NO DEFAULT NOT NULL
  , SESSIONID                        CHAR(36) NO DEFAULT NOT NULL
  , FOUNDSESSIONSTART                CHAR(1) DEFAULT 'N' NOT NULL
  , FOUNDSESSIONEND                  CHAR(1) DEFAULT 'N' NOT NULL
  , IPADDRV46                        CHAR(46) DEFAULT NULL
  , SESSIONNAME                      VARCHAR(40) DEFAULT NULL
  , PROCESSTHREADID                  VARCHAR(50) DEFAULT NULL
  , PROCESSTHREADID2                 VARCHAR(50) DEFAULT NULL
  , CLIENTPROGRAM                    VARCHAR(50) DEFAULT NULL
  , ANCESTORPROCESSTHREADID          VARCHAR(50) DEFAULT NULL
  , DNSNAME                          VARCHAR(100) DEFAULT NULL
  , CLIENTCURRDIR                    VARCHAR(200) DEFAULT NULL
  , PRIMARY KEY (PARTITIONKEY ASC, RECORDSESSIONKEY ASC)
  )
  CATALOG XMACAT
  PARTITION ARRAY FORMAT2ENABLED
  SECURE "NUCU"
  SIMILARITY CHECK ENABLE
  EXTENT (20000, 10000)
  MAXEXTENTS 400
  NO AUDIT
  BUFFERED
  ;
CREATE UNIQUE INDEX XMADAT.AUDSEIND ON XMADAT.AUDSESS
  (
    RECORDINSTALLKEY ASC, SESSIONID ASC
  )
  CATALOG XMACAT
  KEYTAG 2
  EXTENT (10000, 10000)
  MAXEXTENTS 400
  ;
CREATE UNIQUE INDEX XMADAT.AUDSRIND ON XMADAT.AUDSESS
  (
    RECORDSESSIONKEY ASC
  )
  CATALOG XMACAT
  KEYTAG 1
  EXTENT (10000, 10000)
  MAXEXTENTS 400
  ;
 

6. Temporarily turn off TMF auditing for the new table
ALTER TABLE $<vol>.XMADAT.AUDSESS NO AUDIT;
(Where $<vol> is the disk volume location for the new AUDSESS table)


7.  Load the data from the old table into the new table. This will also load the indexes automatically:

LOAD XMADATO.AUDSESS,XMADAT.AUDSESS,SORTED,SLACK 0;
 

8. Turn the TMF AUDIT flag back on and enable disk caching:

ALTER TABLE XMADAT.AUDSESS AUDIT BUFFERED;
EXIT;
 

9.  Update the database statistics and re SQLCOMPILE all the XMA programs:
XMA_SQLCOMPALL
 

10.  Start XMA:
XMA_PWCOOL


11. If everything works as expected you can drop the old table and indexes:

SQLCI
DROP TABLE XMADATO.AUDSESS;
EXIT;

    • Related Articles

    • XMA: AUDDET table is full - how to rebuild and resize the AUDDET table

      1. stop XMA: XMA_PWSTOP 2. go to the XMA database location: XMA_DBVOLUME 3. start SQLCI: SQLCI 4. Rename the current table and its indexes to a new location: ALTER TABLE XMADAT.AUDDET RENAME XMADATO.AUDDET; ALTER INDEX XMADAT.AUDGMIND RENAME ...

    • XMA: SYSLOGQ table is full - how to rebuild and resize the SYSLOGQ table

      The steps below can be used to rebuild and resize the SYSLOGQ and its associated index. Stop XMA pathway (Where $<vol> is the disk volume where XMA is installed) RUN $<vol>.XYGATEMA.XMA INSTALL XMA_PWSTOP Go to the XMA database location XMA_DBVOLUME ...

    • XMA: How to Resize an Index File

      Enlarging the index files BEFORE installing XMA: The ADDINDX macro was created to allow the creation of index files using larger (FORMAT2-sized) attributes as a separate task, for customers who wish/need to increase the size of the indexes outside of ...

    • XMA: how to resize DETSEIND

      To resize DETSEIND, Turn on the screen capture. XMA_PWSTOP (stop XMA) XMA_LOAD_DEFINES In SQLCI, DROP INDEX =XMA_DETSEIND; CREATE INDEX =XMA_DETSEIND ON =XMA_AUDITDETAILTBL ( RECORDSESSIONKEY ) CATALOG =XMA_CAT EXTENT (<extent>,<extent>) format 2 ...

    • XMA: Safeguard mover gets error 45 on AUDSEIND index.

      Resize AUDSEIND table using the following procedure. Shutdown XMA TACL> Volume <XMA Installation Subvolume> TACL> RUN NewDefs -- Loads defines for all the tables. TACL> info define =XMA_AUDSESSIONIND TACL> fup info =XMA_AUDSESSIONIND, DETAIL TACL> ...