XMA: How to Resize an Index File

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 the regular installation procedure.  Paste the script below into a file and place the file into the XYGATEMA product subvolume on the NonStop.

The ADDINDX macro:

?TACL routine
#frame
run newdefs
#push ext command commandin fullname indxloc

#setmany _ _ _ fullname,[#DEFINEINFO =XMA_AUDGMTIND]
#set indxloc [#fileinfo/system/[fullname]]
#set indxloc [indxloc].[#fileinfo/volume/[fullname]]
#set indxloc [indxloc].[#fileinfo/subvol/[fullname]]
#set  ext [#fileinfo /PRIMARY/ =XMA_AUDGMTIND]

[#if [#fileinfo /existence/ [indxloc].RECAUDKY] <> -1 |then|
      #set    commandin CREATE INDEX [indxloc].RECAUDKY ON =XMA_AUDITDETAILTBL
      #append commandin ( RECORDAUDITKEY ) CATALOG =XMA_CAT
      #append commandin EXTENT ([ext],[ext]) format 2 MAXEXTENTS 900;
      #set command SQLCI /inv commandin/
      #output Creating new audit detail table index (XMA_RECAUDKEY) ...
      [command]
|else|
      #output [indxloc].RECAUDKY already exists.
]

#set ext [#compute [ext] * 4]
[#if [#fileinfo /existence/ [indxloc].AUDTRIND] <> -1 |then|
      #set    commandin CREATE INDEX [indxloc].AUDTRIND ON =XMA_AUDITDETAILTBL
      #append commandin (TERMINAL, RECORDLCT, RECORDSESSIONKEY) CATALOG =XMA_CAT
      #append commandin EXTENT ([ext],[ext]) format 2 MAXEXTENTS 900;
      #set command SQLCI /inv commandin/
      #output
      #output Creating new audit detail table index (XMA_AUDTRMIND) ...
      [command]
|else|
      #output
      #output [indxloc].AUDTRIND already exists.
]
#pop ext command commandin fullname indxloc
run deldefs
#unframe


How to use the ADDINDX macro:

1. Logon as XMA "owner"
2. Put the script file (ADDINDX) into the $<vol>.XYGATEMA subvolume
3. Shut down the XMA pathway, via the XMA_MANAGER tool (or the XMA_PWSTOP macro).
4. RUN $<vol>.XYGATEMA.ADDINDX
5. This run may take some time, depending on how large your database is
6. Either run the "normal" INSTALL or, if that's not desired, run (at least) the XMA_SQLCOMPALL macro.

Just a note, the INSTALL script is coded so that if it discovers that the "new" indexes already exist, it skips the creation to prevent overwriting the files.


Enlarging the index files AFTER installing XMA:
To ensure that these indexes are sized correctly, re-create the indexes with larger extents using the following guidelines:

1. Shutdown the XYGATEMA Pathway.
2. Volume to where the AUDDET table is located.
3. FUP INFO DETSEIND,DETAIL
4. Look at the catalog location and the extent sizes of the above table for use in the next command. Use SQLCI to add index RECAUDKY to table AUDDET:

SQLCI
DROP INDEX RECAUDKY;
CREATE INDEX RECAUDKY ON AUDDET(RECORDAUDITKEY)
CATALOG <catalog-loc>
EXTENT(<extent-value>,<extent-value>) FORMAT 2
MAXEXTENTS 900;
EXIT;

5. Use SQLCI to add index AUDTRIND to table AUDDET, making the extents 4 times bigger than the previous index above:

SQLCI
DROP INDEX AUDTRIND;
CREATE INDEX AUDTRIND
ON AUDDET(TERMINAL, RECORDLCT, RECORDSESSIONKEY)
CATALOG <catalog-loc>
EXTENT(<extent-value>,<extent-value>) FORMAT 2
MAXEXTENTS 900;
EXIT;

6. SQLCOMP the XYGATEMA program objects:
VOLUME <xygatema-install-loc>
RUN <xygatema-macro-name> INSTALL
<xygatema-macro-name>_SQLCOMPALL

Example:
VOLUME XYGATEMA
RUN XMA INSTALL
XMA_SQLCOMPALL

7. Restart the XYGATEMA Pathway.

NOTE:  A FUP INFO AUDDET,STAT and a FUP INFO on the Index file (AUDTRIND for example), STAT should show the number of records in the index table is equal to or lower than in the AUDDET table. If the record count shows the index at or lower than the table, a resize is in order.



If this article did not provide a solution, please open a case with our support team by sending an email message to support@xypro.com.

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

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