Summary

The Not Logged Initially attribute is not persistent to the table object and is a runtime option. This attribute must be activated from the application through an explicit Alter Table SQL statement when a transaction (Unit Of Work (UOW)) is started. Additionally, the attribute is absent in the metadata or catalog of DB2 and cannot be deactivated from the system. 

Changes made to the table by this statement, through an Insert, Delete, Update, Create Index, Drop Index, or Alter Table in the same Unit of Work (UOW) after the table is altered, are not logged. When the current UOW is completed, the Not Logged Initially attribute is deactivated and all operations performed on the table in subsequent UOW are logged.

On using this attribute from the application, multiple Access warnings (approximately 1600) display in the db2diag.log.

Following are the tables that are groomed using the Stored procedure:

• sppr_purge_table_no_logging  
• pr_log_reportstats
• pr_perf_stats
• pr4_log_rule_usage
• pr4_log_rule_usage_detail
• pc_events and
• pc_pegatask

These tables are affected during the database restore activities when the NOT LOGGED feature is enabled. Other tables that are purged by the system are cleaner and do not require grooming.

Note

The Stored procedure is used for purging tables.

For more information on Stored procedure, navigate to the corresponding database > Programmability > Stored Procedures.

Error Messages

ADM5530W
MESSAGE: ADM5530W The COMMIT processing of table "XXXX .YYYYYY" that used NOT LOGGED INITIALLY has been initiated. It is recommended that you take a backup of this table's table space(s).

Steps to Reproduce

Search the db2diag.log for the message.

Root Cause

The environment was installed as default which set the no-logging option to True. As a result, the tables were altered before they were purged (to stop the back up).

Resolution

Perform the following local-change to address the issue by disabling the NO LOGGING feature during Pega application installation:

Use the no-logging option (for performance).

To enable the no-logging option during the Pega application installation, add the following line of code to the setupdatabase.properties file pega.override.enable.nologging=false

The setting will have this effect:

The sppr_defragment_table procedure will not activate the Not logged initially option on the table.

The sppr_purge_table_no_logging procedure will not activate the Not logged initially option on the table.

The Truncate statements generated during the upgrade or update will not activate the Not logged initially option with empty table option on the table.

The change made to the setupdatabase.properties are correct, the above behavior persists across all the upgrades and updates.

However, if the application is already installed and if the no-logging option is not suitable for the environment, disable the no-logging option as below.

In the sppr_defragment_table Stored procedure, the current procedure is:

SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';

'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';

PREPARE S1 FROM stmt;

EXECUTE S1;

-- SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';

-- PREPARE S1 FROM stmt;

-- EXECUTE S1;

Modify the above procedure as below:

-- SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';

--'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';

-- PREPARE S1 FROM stmt;

-- EXECUTE S1;

SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';

PREPARE S1 FROM stmt;

EXECUTE S1;

Note: The first is commented out with '--' at the start of each line and the rest is active. In this scenario, the no-logging option is disabled. That is, the activity for the table is logged. To reverse the behavior, switch the set of lines commented.

In sppr_purge_table_no_logging, below are the lines to disable the no-logging behavior:

set sqlstmtAlterTableForNoLog = 'ALTER TABLE ' || tablename || ' activate not logged initially';

prepare s1 from sqlstmtAlterTableForNoLog;

execute s1;

To log all actions, comment out the lines above.

Alternatively,

Upgrade to Pega 8 where the issue is addressed.

The following statement is added in the Stored procedure in Pega 8 to address the issue:
set sqlstmtAlterTableForNoLog = 'ALTER TABLE ' || tablename || ' activate not logged initially';
prepare s1 from sqlstmtAlterTableForNoLog;
execute s1;