The XMETA Repository for Information Server 8.1 grows rapidly until it runs out of disk space

Question

Why does the xmeta repository for Information Server v8.1 grow in size rapidly (much faster than in v8.0) until running out of disk space. Also, how do we recover from this situation if no more space can be allocated?

Note: The following document discusses several topics related to the size management of the Xmeta repository. Before you perform any of the suggested procedures to free up space in the Xmeta repository you must obtain a backup of the database.




Impact of Xmeta Logging (ORLogging) in the growth of the Repository


Beginning with Information Server (IIS) 8.1, sites may experience a rapid growth in the size of the xmeta repository due to IIS 8.1 changing the default logging method to "xmeta logging" resulting in all job log messages written to the xmeta repository. Prior to IIS 8.1, only non-job messages were stored in xmeta. As a result, sites that do not closely monitor or regulate the volume of logged messages may allow the xmeta repository too large on busy systems resulting in errors. Additionally, xmeta logging has performance implications and some known problems that can occur on systems that do not yet have fix pack 1 applied.


The following technote discusses the xmeta logging change in more detail, including how to switch to the original logging method, and also how to schedule a purge of DataStage job log messages already logged to the xmeta repository:


DataStage logging changes introduced in Information Server v8.1


In addition to the actions listed in the above technote, further analysis of the xmeta repository may be needed to determine if the growth was primarily due to message logging or other activities, and to adjust the Information Server and database configuration to best meet your needs.




Managing job log message histories with disk logging

• After RTLogging is enabled you won't be able to use the Information Server Console to see new job logs. It will still show old log messages saved before the configuration change, plus messages generated by other IIS components such as the Information Service Director (ISD), but new job logs won't be accessible thought this tool. To view job logs after RTLogging is enabled you need to use DataStage Director Client. 
• After you switch to RTLogging you can use DataStage Administrator client to define purge actions for all jobs in a specific project. Automatic purge can be configured based on either message age or the number of job runs (i.e. save messages from only the last 3 runs of a given job). By setting auto-purge you can prevent job logs from growing excessively. Be aware that changes here only affect new created jobs. 
• The DataStage Directory client can be used to purge the history of a job after RTLogging has been enabled. To purge logs already stored in xmeta repository you should use either the console options listed below, or the commands listed in the previously referenced technote. The instructions for purging messages via the consoles are described in the following InfoCenter topics:
  Purging log entries via the Web Console for Information Server
  Purging log entries via the Console for Information Server
• Note that purging messages from the xmeta repository will not directly reduce the size of the table or the tablespace used by the xmeta repository. The table size can be reduced after purging messages by performing a table reorganization as discussed at the end of this technote.

Identifying largest tables and purging LoggingEvent Table

Use the following queries to determine what are the largest tables in Xmeta and determine the best course of action:

• In DB2:
  
  SELECT substr(tabname, 1, 25) tabname, substr(tabschema, 1, 25) tabschema,
  ((fpages * pagesize) / (1024*1024)) Size_in_MB
  FROM syscat.tables t, syscat.tablespaces ts
  WHERE fpages > 0 AND t.tbspace = ts.tbspace
  ORDER BY card DESC
  FETCH FIRST 50 ROWS ONLY 
• In Oracle:
  
  SELECT * FROM
  (SELECT segment_name, owner, bytes/1024/1024 Size_in_MB
  FROM DBA_SEGMENTS ORDER BY bytes/1024/1024 DESC )
  WHERE ROWNUM <= 50

The above queries will show the 50 largest tables in the xmeta database. The column Size_in_MB gives an approximation of the spaced used by each table. If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you can purge messages in this table to remove unnecessary rows.

If the largest table is LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F then you should identify which message category is creating most of the rows in this table. You can run the following command to identify these categories:

SELECT CATEGORYNAME_XMETA, count(*)
FROM LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F
GROUP BY CATEGORYNAME_XMETA
ORDER BY count(*) DESC

Once you have identified the message category that has the most rows then you can delete messages belonging to that category. For example, if most of the messages have category of IIS-DSTAGE-RUN then those are job log messages and can be removed by running the LoggingAgent command to purge messages with that category as discussed in the following technote:

DataStage logging changes introduced in Information Server v8.1

NOTE: We recommend using only the methods discussed this technote to purge messages from the logging table. You should not need to directly clear the table via database commands. Also do not remove records from any table other than the LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table. Other tables in the xmeta repository store data that should not be deleted and doing so could render the repository unusable.


Analyzing Xmeta tablespaces in DB2

To view the size/status of the xmeta tablespace open a DB2 command prompt and enter commands:
CONNECT to xmeta
LIST TABLESPACES SHOW DETAIL

This will list all the table spaces, including xmeta. For each it will show the number of used and free pages and page size. If there are few free pages then your options are to increase the disk space available to the xmeta tablespace, or by freeing pages within the tablespace by purging unnecessary data such as old job log messages.

Note that if you purge old messages and then reorganize the table where messages were removed, that may free up pages within the tablespace but it will not reduce tablespace size on disk. For example, if the xmeta tablespace was using 25GB of space and 99% full, then after a purge/reorg the tablespace will still have size of 25GB but you may only be 50% full, meaning that additional usage of xmeta can occur without needing to increase tablespace size.

Normally DB2 can increase tablespace size as needed until the disk/volume runs out of space. Thus if you only had a small amount of space, such as 4GB, allocated for DB2, you may need to increase disk space allocation in addition to managing the deletion of aged messages.


Reorganizing DB2 tables after purging messages to free pages in tablespace
When messages have been purged from the LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F table, the space used by those messages is not immediately returned to the tablespace as free pages. That occurs when the table is reorganized. Some database configurations may periodically reorganized tables automatically. To force a DB2 table to reorganize immediately, you can issue the following DB2 command:

db2 REORG TABLE XMETA.LOGGING_XMETAGEN_LOGGINGEVENT1466CB5F use XMETATEMP;

Once the above command completes, you can run the LIST TABLESPACES SHOW DETAIL command again to confirm how many pages were freed in the tablespace and determine if additional action is still needed.

Please note that this action frees pages WITHIN the tablespace, it does not actually reduce the amount of disk space that the tablespace is using. To do that would require dropping and recreating the tablespace or moving the database to a new volume, which are actions beyond the scope of this technote.

If the large xmeta tablespace caused a disk full condition, then you will need to add additional space to that volume to prevent problems for any applications which run on that volume (including DB2 since it does have need for space outside the xmeta tablespace such as for other databases and DB2 log files).


Source : IBM FAQ