IBM Support

Improvement in terms of disk space and transaction log utilization, starting from ILMT v9.2.3, at the cost of possible risk of upgrade schema failure, when migrating from old ILMT version to a new v9.2.3 (applies only for DB2 environments)

Flashes (Alerts)


Abstract

ILMT v9.2.3 introduced a significant improvement in terms of disk space and transaction log utilization. However, there is a high possibility of upgrade schema failure when migrating from ILMT environments to a new 9.2.3 version. Likelihood is greater for those environments that have passed many ETL imports since the installation time. This directly increase the likelihood of hitting the "lack of disk space" issue or the "log transaction full" issue if the environment has improper resources/settings. Please note that the size of environment expressed in number of connected clients does not play role here!

Content

ILMT v9.2.3 introduced a significant improvement in terms of disk space and transaction log utilization. The improvement relays on the assumption that the proper cleaning of so called roll-up's tables needs to be ensured during the upgrading schema process. This, however, might have a negative side effect, when going through the upgrade schema process, as during that time the huge number of data is removed from the database, which could generate a possible risk of upgrade schema failure.

In order to verify if your environment is affected, please connect to your ILMT database and run the following query.

Please note that the below query is a reference query only. Currently we still do not know whether the relation between the output of the below query and the amount of transaction log needed is linear. Therefore increase of transaction log will not give a 100% guarantee that the issue will be mitigated. Taking this, we have prepared a sql clean-up script that needs to be run before upgrading the schema. If below query returns more than 1GB, please execute the repair procedure.



SELECT SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO where TABSCHEMA='SAM' and TABNAME = 'DISCOVERABLE_ROLLUPS'

REPAIR PROCEDURE:
1. Take the backup of the ILMT database
2. Download the fiximport.sql script from the attachment section
3. Edit the script and replace the date so that it will reflect
the current date - 1 day.
For example:
SET V_TIME = '2016-04-20 14:02:00.667000';
4. Stop ILMT as root
# /etc/init.d/LMTserver stop
5. Run the sql script from the level of database instance owner (by default db2inst1):
db2 -vtf fiximport.sql -td%
6. Start ILMT
# /etc/init.d/LMTserver start
7. If there is no errors, please proceed with schema upgrade by clicking on the Update Schema button
8. After successful upgrade of schema, please run the ETL import immediately.

For customers who already run into the problem, they may observe in the tema.log the following entries:
 
[4/12/16 12:22:28:422 UTC] 000009d4 com.ibm.ws.webcontainer.webapp I SRVE0292I: Servlet Message - [tema]:.[ERROR] Java::ComIbmDb2JccAm::SqlException: The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=3.64.104: DELETE FROM SAM.DISCOVERABLE_ROLLUPS

[4/12/16 12:27:05:583 UTC] 000009d4 com.ibm.ws.webcontainer.webapp I SRVE0292I: Servlet Message - [tema]:.[FATAL]

Sequel::DatabaseError (DBNAME: SUADB - Java::ComIbmDb2JccAm::SqlException: The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=3.64.104):

In such case it is highly recommended to restore the previous backup of the database and follow the above repair procedure.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS8JFY","label":"IBM License Metric Tool"},"Component":"Not Applicable","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"9.0;9.0.1;9.1;9.2","Edition":"All Editions","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
25 September 2022

UID

swg21980857