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

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.
 
  1. Stop XMA pathway
(Where $<vol> is the disk volume where XMA is installed)
RUN $<vol>.XYGATEMA.XMA INSTALL 
XMA_PWSTOP
 
  1. Go to the XMA database location
XMA_DBVOLUME
 
  1. Load the XMA defines
XMA_LOAD_DEFINES
 
  1. Rename the current table and its indexes to a new location:
      SQLCI
      ALTER TABLE XMADAT.SYSLOGQ  RENAME XMADATO.SYSLOGQ;
      ALTER INDEX XMADAT.SYSLOGQI RENAME XMADATO.SYSLOGQI;

 
  1. Create a new table and indexes with larger extents.  The below commands can be manually entered using SQLCI or can be placed into an obey file and the obey file executed from SQLCI.
CREATE TABLE =XMA_SYSLOGQ
         
                (   PARTITIONKEY              NUMERIC( 4, 0)                                    DEFAULT 1 NOT NULL
                   , ENTRYGMT                     TIMESTAMP                                           NO DEFAULT NOT NULL
                   , PART                               SMALLINT                                              NO DEFAULT NOT NULL
                   , ACTIONGMT                   TIMESTAMP                                          NO DEFAULT NOT NULL
                   , ALERTEDGMT                 TIMESTAMP                                          NO DEFAULT NOT NULL
                   , ACTION                          CHAR(1)                                                 NO DEFAULT NOT NULL
                   , TCP_PROCESS               CHAR(9)                                                 NO DEFAULT NOT NULL
                   , RETRIES                         SMALLINT                                              NO DEFAULT NOT NULL
                   , MESSAGE_LEN               SMALLINT                                              NO DEFAULT NOT NULL
                   , TARGET_PORT               SMALLINT                                              NO DEFAULT NOT NULL
                   , TARGET_HOST               VARCHAR(64)                                        NO DEFAULT NOT NULL
                   , PREFIX                           VARCHAR(79)                                        NO DEFAULT NOT NULL
                   , MESSAGE                        VARCHAR(1000)                                   NO DEFAULT NOT NULL
                   , PRIMARY KEY (PARTITIONKEY, ENTRYGMT, PART)
             )
                   CATALOG =XMA_CAT
                    ORGANIZATION K
                    EXTENT (10000, 10000) FORMAT 2
                    MAXEXTENTS 500
                    SIMILARITY CHECK ENABLE;
                                    
CREATE INDEX  =XMA_SYSLOGQIND ON =XMA_SYSLOGQ
                        ( ACTION ASC, ALERTEDGMT ASC, ACTIONGMT ASC, PART ASC )
                            CATALOG =XMA_CAT
                            EXTENT (3000,3000) FORMAT 2
                            MAXEXTENTS 500;
         
Note:  The extent sizes for the  new table and the index may vary from the example based on your requirements or available disk  space.
 
  1. Temporarily turn off TMF auditing for the new table
ALTER TABLE $<vol>.XMADAT.SYSLOGQ NO AUDIT;
(Where $<vol> is the disk volume location for the new SYSLOGQ table)
 
  1. Load the data from the old table into the new table*.     This will also load the indexes automatically.  From SQLCI:
       LOAD XMADATO.SYSLOGQ, =XMA_SYSLOGQ,SORTED,SLACK 0;
 
  1. Turn the TMF AUDIT flag back on and enable disk caching.  From SQLCI:
      ALTER TABLE =XMA_SYSLOGQ AUDIT BUFFERED;
  
  1. Exit SQLCI and update the database statistics and re-SQLCOMPILE all the XMA programs
     XMA_SQLCOMPALL
 
  1. Start XMA
    XMA_PWCOOL




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: 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: 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: 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: Example of a SQL query to determine the number of unsent records in the SYSLOGQ table

      The sample query statements below can also be used as an input file, for example, SQLCI /in <in-file>/ on the XMA DB subvolume. The query to determine unsent records in the SYSLOGQ table. set list_count 0; set headings off; select cast (ALERTEDGMT as ...

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