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_MANAGER macro, select ?Option 1. PATHWAY? and then select ?Option 3. STOP?)
Step: Option 1.2. Cleanup the obsolete data records - run the XMA_MANAGER macro
- Select ?Option 3: Database?
- Select ?Option 3: Cleanup Database? and hit ?N? at the ?Do you want to empty the session and detail tables? prompt
- == comment enter the range of data and time of obsolete data records to be deleted, for example:
- Select ?Option 1: Begin LCT Date? and enter the 'begin date' of obsolete data records
- Select ?Option 2: Begin LCT Time? and enter the 'begin time' of obsolete data records
- Select ?Option 3: End LCT Date? and enter the 'end date' of obsolete data records
- Select ?Option 4: End LCT Time? and enter the 'end time' of obsolete data records
- Hit the ?R? and validate whether the Cleanup process is completed successfully
Step: Option 1.3. Run FUP RELOAD to reclaim space freed up by CLEANUP at TACL - TACL> FUP reload /out <output>/ $<partion-1>.XMADAT.AUDDET,PARTOF <$xma-db-vol>,DEALLOCATE
- TACL> FUP reload /out <output>/ $<partion-2>.XMADAT.AUDDET,PARTOF <$xma-db-vol>,DEALLOCATE
- Validate there are no errors on the <output>
Step: Option 1.4. Run XMA_SQLCOMPALL at TACL to update the database statistics and SQLCOMPILE all the XMA programs
Step: Option 1.5. Start XMA by running XMA_PWCOOL at TACL
Note: For more information, please refer to 'Removing Obsolete Records' in 'Chapter 10: Maintaining the XMA Database.'
Below are the Steps for ?Option 2? increasing MAXEXTENTS:
Step: Option 2.0. Logon as an owner of XMA installation and install XMA macro at TACL
Step: Option 2.1. Stop XMA Pathway
- by running the XMA_PWSTOP macro at TACL (or at the XMA_MANAGER macro, select ?Option 1. PATHWAY? and then select ?Option 3. STOP?)
Step: Option 2.2. Increasing the number of MAXEXTENTS at SQLCI
Example of increasing MAXEXTENTS - TACL> SQLCI
- alter table $<partion-1>.XMADAT.AUDDET partonly maxextents 900 ;
- alter table $<partion-2>.XMADAT.AUDDET partonly maxextents 900 ;
- alter index $<xma-db-vol>.XMADAT.AUDTRIND maxextents 900 ;
Step: Option 2.3. Run XMA_SQLCOMPALL at TACL to update the database statistics and SQLCOMPILE all the XMA programs
Step: Option 2.4. Start XMA by running XMA_PWCOOL at TACL
Note: For more information, please refer to 'Resizing the Data Tables' in 'Chapter 10: Maintaining the XMA Database.'
Below are the Steps for ?Option 3? rebuilding the partitioned AUDDET with a large number of EXTENTS, MAXEXTENTS:
Step: Option 3.0. Logon as an owner of XMA installation and install XMA macro at TACL
Step: Option 3.1. Stop XMA Pathway - by running the XMA_PWSTOP macro at TACL (or at the XMA_MANAGER macro, select ?Option 1. PATHWAY? and then select ?Option 3. STOP?)
Step: Option 3.2. Renaming the current AUDDET table and its indexes at SQLCI
Example of renaming AUDDET and its indexes - TACL> == comment changing subvolume to XMA DB location
- TACL> XMA_DBVOLUME
- TACL> == comment rename XMADAT.AUDDET and its indexes
- TACL> == for example, rename from $<xma-db-vol>.xmadat.AUDDET to $<xma-db-vol>.xmadatO.AUDDET
- TACL> SQLCI
- >> alter table $<xma-db-vol>.XMADAT.AUDDET rename $<xma-db-vol>.XMADATO.AUDDET ;
- >> alter index $<xma-db-vol>.XMADAT.AUDGMIND rename $<xma-db-vol>.XMADATO.AUDGMIND ;
- >> alter index $<xma-db-vol>.XMADAT.AUDLCIND rename $<xma-db-vol>.XMADATO.AUDLCIND ;
- >> alter index $<xma-db-vol>.XMADAT.DETSEIND rename $<xma-db-vol>.XMADATO.DETSEIND ;
- >> alter index $<xma-db-vol>.XMADAT.RECAUDKY rename $<xma-db-vol>.XMADATO.RECAUDKY ;
- >> alter index $<xma-db-vol>.XMADAT.AUDTRIND rename $<xma-db-vol>.XMADATO.AUDTRIND ;
Step: Option 3.3. Create a new AUDDET with larger extents at SQLCI - TACL> == comment run NEWDEFS to add defines for creating a new partitioned table and its indexes
- TACL> XMA_VOLUME
- TACL> run NEWDEFS
- TACL> SQLCI
CREATE TABLE =XMA_AUDITDETAILTBL
(
PARTITIONKEY NUMERIC(4) DEFAULT 1 NOT NULL
,RECORDGMT TIMESTAMP NO DEFAULT NOT NULL
,GMTSEQNO CHAR(2) NO DEFAULT NOT NULL
,RECORDLCT TIMESTAMP NO DEFAULT NOT NULL
,RECORDAUDITKEY TIMESTAMP DEFAULT CURRENT NOT NULL
,RECORDSESSIONKEY TIMESTAMP 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
,PRIMARY KEY (PARTITIONKEY,RECORDGMT,GMTSEQNO)
)
CATALOG =XMA_CAT
ORGANIZATION K
-- EXTENT (nnnnn,nnnnn) MAXEXTENTS nnn
EXTENT (30000,30000) format 2
MAXEXTENTS 700
SIMILARITY CHECK ENABLE
NO AUDIT
;
ALTER TABLE =XMA_AUDITDETAILTBL
ADD PARTITION <$partition-1>.XMADAT.AUDDET
-- FIRST KEY between 2 and 255
FIRST KEY 2
CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) MAXEXTENTS nnn
EXTENT (30000,30000) format 2
MAXEXTENTS 700;
ALTER TABLE =XMA_AUDITDETAILTBL
ADD PARTITION <$partition-2>.XMADAT.AUDDET
-- FIRST KEY between 2 and 255
FIRST KEY 3
CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) MAXEXTENTS nnn
EXTENT (30000,30000) format 2
MAXEXTENTS 700;
Step: Option 3.4. Load the data from the old partition into the new partition at SQLCI
LOAD <$partition-1>.XMADAT.AUDDET,<$partition-1>.XMADATO.AUDDET,PARTONLY,SORTED,SLACK 0;
LOAD <$partition-2>.XMADAT.AUDDET, <$partition-2>.XMADATO.AUDDET,PARTONLY,SORTED,SLACK 0;
Step: Option 3.5. Create a new AUDDET?s indexes with larger extents at SQLCI
CREATE UNIQUE INDEX =XMA_AUDGMTIND ON =XMA_AUDITDETAILTBL ( RECORDGMT,GMTSEQNO )
CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) format 2 MAXEXTENTS nnn
EXTENT (10000,10000) format 2 MAXEXTENTS 700;
CREATE INDEX =XMA_AUDLCTIND ON =XMA_AUDITDETAILTBL ( RECORDLCT ) CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) format 2 MAXEXTENTS nnn
EXTENT (10000,10000) format 2 MAXEXTENTS 700;
CREATE INDEX =XMA_DETSEIND ON =XMA_AUDITDETAILTBL ( RECORDSESSIONKEY ) CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) format 2 MAXEXTENTS nnn
EXTENT (10000,10000) format 2 MAXEXTENTS 700;
CREATE INDEX =XMA_RECAUDKEY ON =XMA_AUDITDETAILTBL ( RECORDAUDITKEY ) CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) format 2 MAXEXTENTS nnn
EXTENT (10000,10000) format 2 MAXEXTENTS 700;
CREATE INDEX =XMA_AUDTRMIND ON =XMA_AUDITDETAILTBL ( TERMINAL, RECORDLCT, RECORDSESSIONKEY) CATALOG =XMA_CAT
-- EXTENT (nnnnn,nnnnn) format 2 MAXEXTENTS nnn
EXTENT (20000,20000) format 2 MAXEXTENTS 700;
Step: Option 3.6. Turn the TMF AUDIT flag back on and enable disk caching at SQLCI - ALTER TABLE =XMA_AUDITDETAILTBL AUDIT BUFFERED;
Step: Option 3.7. Run XMA_SQLCOMPALL at TACL to update the database statistics and SQLCOMPILE all the XMA programs
Step: Option 3.8. Start XMA by running XMA_PWCOOL at TACL
Note 1: Rebuilding the partitioned AUDDET may require a large amount of Disk space and may use more CPU and DISK resources than usual to load data from the old partitions to the new ones and to create indexes. Running SQLCI with a lower PRI value may help reduce system impacts, but it will take longer.
Note 2: For creating TABLEs, please refer to the SQL Statements on the NEWTBLS file in the XMA installation
Note 3: For more information, please refer to 'Partitioning the XMA Database' in 'Chapter 10: Maintaining the XMA Database.'