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

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 XMADATO.AUDGMIND;
ALTER INDEX XMADAT.AUDLCIND RENAME XMADATO.AUDLCIND;
ALTER INDEX XMADAT.AUDTRIND RENAME XMADATO.AUDTRIND;
ALTER INDEX XMADAT.DETSEIND RENAME XMADATO.DETSEIND;
ALTER INDEX XMADAT.RECAUDKY RENAME XMADATO.RECAUDKY;
 
5.  Create a new table and indexes with larger extents:

CREATE TABLE XMADAT.AUDDET
(
    PARTITIONKEY                   NUMERIC( 4, 0) DEFAULT 1 NOT NULL
  , RECORDGMT                      DATETIME YEAR TO FRACTION(6) NO DEFAULT
                                   NOT NULL
  , GMTSEQNO                       CHAR(2) NO DEFAULT NOT NULL
  , RECORDLCT                      DATETIME YEAR TO FRACTION(6) NO DEFAULT
                                   NOT NULL
  , RECORDAUDITKEY                 DATETIME YEAR TO FRACTION(6) DEFAULT SYSTEM
                                   NOT NULL
  , RECORDSESSIONKEY               DATETIME YEAR TO FRACTION(6) NO DEFAULT
                                   NOT NULL
  , SEQNO                          CHAR(9) NO DEFAULT NOT NULL
  , OUTCOME                        CHAR(1) DEFAULT '1' NOT NULL
  , WARNINGMODE                    CHAR(1) DEFAULT 'N' NOT NULL
  , TESTMODE                       CHAR(1) DEFAULT 'N' NOT NULL
  , ALERTED                        CHAR(1) DEFAULT 'N' NOT NULL
  , SEVERITY                       CHAR(1) DEFAULT '1' NOT NULL
  , PRODUCTCODE                    CHAR(10) DEFAULT NULL
  , SUBJECT_USERNUMBER_MAJOR       CHAR(8) DEFAULT NULL
  , SUBJECT_USERNUMBER_MINOR       CHAR(8) DEFAULT NULL
  , TARGET_USERNUMBER_MAJOR        CHAR(8) DEFAULT NULL
  , TARGET_USERNUMBER_MINOR        CHAR(8) DEFAULT NULL
  , SUBJECTLOGIN                   VARCHAR(40) DEFAULT NULL
  , SUBJECTSYSTEM                  VARCHAR(64) DEFAULT NULL
  , TARGETLOGIN                    VARCHAR(40) DEFAULT NULL
  , OBJECTTYPE                     VARCHAR(32) DEFAULT NULL
  , OBJECTNAME                     VARCHAR(200) DEFAULT NULL
  , OPERATION                      VARCHAR(128) DEFAULT NULL
  , TERMINAL                       VARCHAR(64) DEFAULT NULL
  , MESSAGEID                      VARCHAR(20) DEFAULT NULL
  , MESSAGECODE                    VARCHAR(20) DEFAULT NULL
  , RULENAME                       VARCHAR(50) DEFAULT NULL
  , RESULT                         VARCHAR(256) DEFAULT NULL
  , USER_DATA                      VARCHAR(16) DEFAULT NULL
  )
  CATALOG XMACAT
  PARTITION ARRAY FORMAT2ENABLED
  SECURE "NUCU"
  SIMILARITY CHECK ENABLE
  EXTENT (10000, 10000)
  MAXEXTENTS 400
  NO AUDIT
  BUFFERED
  ;
 
CREATE UNIQUE INDEX XMADAT.AUDGMIND ON XMADAT.AUDDET
  (
    RECORDGMT ASC, GMTSEQNO ASC
  )
  CATALOG XMACAT
  KEYTAG 1
  EXTENT (2000, 2000)
  MAXEXTENTS 400
  ;
CREATE INDEX XMADAT.AUDLCIND ON XMADAT.AUDDET
  (
    RECORDLCT ASC
  )
  CATALOG.XMACAT
  KEYTAG 2
  EXTENT (2000, 2000)
  MAXEXTENTS 400
  ;
CREATE INDEX XMADAT.AUDTRIND ON XMADAT.AUDDET
  (
    TERMINAL ASC, RECORDLCT ASC, RECORDSESSIONKEY ASC
  )
  CATALOG XMACAT
  KEYTAG 5
  EXTENT (5000, 5000)
  MAXEXTENTS 400
  ;
CREATE INDEX XMADAT.DETSEIND ON XMADAT.AUDDET
  (
    RECORDSESSIONKEY ASC
  )
  CATALOG XMACAT
  KEYTAG 3
  EXTENT (2000, 2000)
  MAXEXTENTS 400
  ;
CREATE INDEX XMADAT.RECAUDKY ON XMADAT.AUDDET
  (
    RECORDAUDITKEY ASC
  )
  CATALOG XMACAT
  KEYTAG 4
  EXTENT (2000, 2000)
  MAXEXTENTS 400
  ;


6. Temporarily turn off TMF auditing for the new table
ALTER TABLE $<vol>.XMADAT.AUDDET 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.AUDDET,XMADAT.AUDDET,SORTED,SLACK 0;


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

ALTER TABLE XMADAT.AUDDET 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 PERFECTLY, you can drop the old table and indexes:

SQLCI
DROP TABLE XMADATO.AUDDET;
EXIT;



If you are unable to locate the solution needed or if you need additional assistance please Click Here to open a case from our Customer Care Portal.


    • Related Articles

    • 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: 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 ...

    • 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:Ways to address SQL error due to error 45 in AUDDET or AUDSESS

      Below are the Steps for ?Option 1? clean up the obsolete data records: Step: Option 1.0. Logon as an owner of XMA installation and install XMA macro at TACL Step: Option 1.1. Stop XMA Pathway by running the XMA_PWSTOP macro at TACL (or at the ...

    • 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 ...