Troubleshooting
Problem
Customer would like to know if there is a way to reduce the size of their Controller application database repository.
Cause
There are (of course) many different potential causes of a large Controller database.
- For example, one cause (especially in Controller 10.1) is large amounts of data inside the table XDBTRICKLE. For more details on this, see separate Technote #1624409.
This IBM Technote deals specifically with the scenario where the size of the database table 'XACCLOCK' has grown to be large.
More Information:
Customer is using the "company locking" functionality.
- This locks the data by company and account, and additionally by multi period locking
- In other words, it allows the system to be locked so further Data Entry is not possible.
- This creates many 'period locks by company' entries inside the database
- These entries are stored inside the database table 'xacclock'
The Controller system does not delete these automatically. Therefore, over many years, these 'period lock by company' entries will be part of the cause why the database has grown.
- Therefore, some customers may wish to delete these entries periodically (in order to keep the size of the Controller application repository database 'in shape').
What are the possible drawbacks of deleting data inside 'xacclock'?
For most customers, when thy close their period/actuality combination (called "perakt" in the database) they will never reopen it again (in the future). In this scenario, the information in xacclock is NOT useful.
- Therefore, a lot of the information in xacclock can normally be deleted
However, imagine a scenario where:
- Some companies have closed one or more submissions, and then the whole 'perakt' (period/actuality combination) is closed.
- After this, the period is reopened (for some reason) and you want to come back to exactly the same state (regarding what is locked and what is not).
In this scenario you would actually need the information in xacclock (for that perakt) to allow you to do this.
Example:
A totally locked 'perakt' is very seldom reopened. One possible scenario is that you temporarily need to lock down the system (i.e. have no updates) while there is some kind of maintenance done, but you decide that you need to be back in the exact same lock state (as before the perakt was locked).
- This is a rare scenario, but it might theoretically happen.
Environment
The instructions/scripts here are mainly based on Microsoft SQL.
- However, at the end of this Technote there is a section for DB2.
Diagnosing The Problem
To see how large your xacclock table is, use the instructions inside separate IBM Technote #1345780.
- Compare this size with the size of your entire Controller database.
Alternatively, for a quick/simple count of the number of rows in the xacclock table use this SQL query:
- SELECT COUNT(*) FROM owner.xacclock;
Resolving The Problem
Reduce the size of the xacclock table by deleting period locks for historic periods.
- In other words, delete the period locks for old months/years which the financial users no longer have any use for.
Only the customer's finance superuser can decide which periods can be defined as 'Historic'.
- In general terms, these will be periods that never will be opened again for data entry.
IMPORTANT
Make sure you understand the consequences of deleting data inside the table 'xacclock':
Deleting (truncating) that table means that if you perform both of the following actions...
- open a period that has been closed
- and unlock a Company that has been locked
However, it is important to remember that the main locking of period/submissions/companies is stored in a different table ('xopen'). Therefore, even though the locking history has been lost, the following is true:
- the periods (that are locked) remain locked
- and also companies (that are locked) remain locked.
========================================================================
** WARNINGS **
Do not use the instructions contained inside this article unless you are 100% confident you know exactly what the consequences are
- MAKE SURE that you 100% backup the Controller database *before* performing any of the actions listed
- These processes can generate large transaction log (LDF file) usage on the SQL server. Therefore make sure you have very large free space on the SQL server's hard drive(s).
- Only apply the script (see below) to a period which is already locked by multi-period locking!
========================================================================
Steps:
The historic locks can be deleted by either of the following two methods:
- Method (1) Using the menus inside Controller (inside the Controller client GUI)
- This does not rely on the I.T. department
- However, it is slow and inconvenient, therefore not recommended for most situations.
- Method (2) (Recommended method) Running SQL scripts (directly on the database)
- This requires assistance from the I.T. department's SQL DBA (administrator)
- It is often the quickest method, therefore it is recommended for most situations.
Method #1 - Using the menus inside Controller
Contact IBM Support for instructions.
Method #2 - (Recommended for most customers) Run SQL scripts directly on the database
Let us imagine a scenario where:
- The finance superuser has decided that they do not need any locks that exist before the period 2001.
- In other words, for most customers (who have 12 periods per calendar year) this means they wish to delete all locks for any periods 1912 or older (which for many customers means periods in the years 2019 and older).
- Customer using Microsoft SQL
- The Cognos Controller database tables are all owned by the user "dbo".
- If they are owned by a different user (for example 'fastnet') then you can change all tables/stored-procedures/views to dbo by using instructions inside separate IBM Technote #383859.
In this scenario:
1. Ensure no users using Controller (downtime)
2. Backup the Controller database as a precaution
- TIP: You may find it useful to keep this database archived (stored somewhere safe) forever, since it contains some information that will soon be lost.
3. Check current size of database
4. Launch a SQL tool (for example SQL Management Studio)
5. When connecting to the SQL server, logon using the same SQL login user that Cognos Controller uses to connect to the database
- For example, perhaps logon as: fastnet
6. Run the SQL script below (naturally change 2000 and dbo entries to the values that are relevant to your needs):
DECLARE @perakt char(6)
SET @perakt = '2000 '
SELECT * INTO dbo.xacclock_temp1 from dbo.xacclock WHERE perakt >= @perakt
SELECT DISTINCT perakt , '*' AS bol,'*' AS konto, subm, userid INTO dbo.xacclock_temp2 FROM dbo.xacclock WHERE perakt < @perakt AND userid = ' '
TRUNCATE TABLE dbo.xacclock
INSERT into dbo.xacclock SELECT * FROM dbo.xacclock_temp1
INSERT into dbo.xacclock SELECT * FROM dbo.xacclock_temp2
DROP TABLE dbo.xacclock_temp1
DROP TABLE dbo.xacclock_temp2
7. Test system works OK
8. Run a database optimisation routine (from within the Controller client GUI)
9. Check new size of database, and the size of the table XACCLOCK
10. To reclaim all the 'empty' (blank) space inside the database, you should now perform a 'shrink' on both the log (LDF) and data (MDF) portions of the database.
- TIP: For instructions, see separate IBM Technote #1367388.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTE: By Microsoft design, the next time that you rebuild the database indexes (for example as part of your Maintenance Plan on Sunday morning) the database file will grow (quite significantly in many cases).
- This is no cause for alarm. Do not re-shrink the database (after the re-index) otherwise you will lose performance. If your SQL server's hard drives are getting full, use other methods (such as increasing the disk size) rather than regularly shrinking the database.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
11. Finally, to improve performance, it is recommended to now rebuild your SQL indexes.
- For example, inside Controller click "Maintain - Database Optimise" and tick/enable 'rebuild indexes' option.
- Replace all occurrences of FASTNET with the name of the user that Controller uses to connect to the DB2 database
- Replace 2000 with the value which is less than all the periods that you want to keep. For example, if you want to keep all information from period 1906 onwards, then the value would be 1905
- In one real-life customer environment, the script below took exactly 30 minutes to delete 43 million rows
- You should see a window appear ("Script1.sql")
- Check that this is connected to the correct database (for example CCR01) and using the correct admin user (for example 'db2admin'):
SET SCHEMA FASTNET;
CREATE VARIABLE FASTNET.perakt_var CHAR(6);
-- set the value of this variable to be the old date to be retained
-- the format is YYMM
SET FASTNET.perakt_var = '2000 ';
CREATE TABLE FASTNET.XACCLOCK_TEMP1 (
"PERAKT" CHAR(6 OCTETS) NOT NULL DEFAULT ' ',
"BOL" CHAR(6 OCTETS) NOT NULL DEFAULT ' ',
"KONTO" CHAR(13 OCTETS) NOT NULL DEFAULT ' ',
"SUBM" INTEGER NOT NULL DEFAULT 0,
"USERID" CHAR(8 OCTETS) NOT NULL DEFAULT ' '
);
CREATE TABLE FASTNET.XACCLOCK_TEMP2 (
"PERAKT" CHAR(6 OCTETS) NOT NULL DEFAULT ' ',
"BOL" CHAR(6 OCTETS) NOT NULL DEFAULT ' ',
"KONTO" CHAR(13 OCTETS) NOT NULL DEFAULT ' ',
"SUBM" INTEGER NOT NULL DEFAULT 0,
"USERID" CHAR(8 OCTETS) NOT NULL DEFAULT ' '
);
-- load the temp tables
INSERT INTO FASTNET.XACCLOCK_TEMP1 (PERAKT, BOL, KONTO, SUBM, USERID)
SELECT PERAKT, BOL, KONTO, SUBM, USERID FROM FASTNET.XACCLOCK
WHERE PERAKT >= FASTNET.perakt_var;
SELECT DISTINCT PERAKT , '*' AS bol,'*' AS KONTO, SUBM, USERID
FROM FASTNET.XACCLOCK WHERE PERAKT < FASTNET.perakt_var AND userid = ' ';
TRUNCATE TABLE FASTNET.XACCLOCK;
INSERT INTO FASTNET.XACCLOCK
SELECT * FROM FASTNET.XACCLOCK_TEMP1;
INSERT INTO FASTNET.XACCLOCK
SELECT * FROM FASTNET.XACCLOCK_TEMP2;
DROP VARIABLE FASTNET.perakt_var;
DROP TABLE FASTNET.XACCLOCK_TEMP1;
DROP TABLE FASTNET.XACCLOCK_TEMP2;
Related Information
1364473 - Accounts of one company are locked for data e
1345780 - How to find out the size of each table inside
1624409 - How to reduce the size of a Controller databa
1646654 - How to delete data in Controller database (IB
1367388 - How to shrink a Microsoft SQL database transa
383859 - How to change the SQL login for Controller, by modifying the tables, v…
Historical Number
1020388
Was this topic helpful?
Document Information
Modified date:
19 September 2020
UID
swg21374227