1

My environment: DB2 10.1.4 Enterprise, IBM Security Identity Manager 6.0 FP6, AIX 7.1 Each archive log have a size of 40MB

Yesterday, we do a maintainance on ISIM database by doing online backup. The backup size is around 7GB. After that, we prune the ISIM database by using db2 prune history 20151001 and delete

Then we runstat and reorg all the table in ISIM database (No automatic maintainance is set on this database and never runstat/reorg for more than 6 months) using Data Studio 4.1.0.

After we reorg the database, we runstat again and notice an increase amount of space that archive logs take up (around 3 GB of archive log generated during runstat). So we thought something might hung inside db2 process so we restart db2 using db2stop / db2start

After restart DB2, we test the system and everything look fine, around 10GB available after we restart DB2 in the night. However, in the morning we found out that all spaces are used up by archive log.

We allocate 10GB to the disk in the morning and it exhausted very fast in a way that we never seen before. Normally, when ISIM reconcile one system, we didn't even notice the change in archive log's size even it is reconcile every hour, however after last night maintanace, The same reconcile took up more than 50 GB of space even it is just one time reconcile and it slow down ISIM reconcile process as well, all of those space occupied by archive logs that seems to generate new file every second during reconcile.

Now, our HADR DB can't catch up with this amount of archive log file and all are disconnected, we decided to fix the problem on main DB first but using db2pd to check any active runstat and reorg didn't give any result. Restore the database now is not an option as it is a production database. We think that we might do offline runstat / reorg tonight but we don't sure if it can solve problem or not.

Anyone have any suggestion?

closed as off-topic by mustaccio, Erik Darling, RDFozz, Mr.Brownstone, hot2use Nov 13 '17 at 14:39

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Erik Darling, RDFozz, Mr.Brownstone, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

0

Finally found what actually happen after 150GB in one day for archive log. After doing offline runstat again, It seems that we ignore the "SQL2310N The utility could not generate statistics. error -930" on table RECONCILIATION_INFO

At first, we thought it might not be an important error but when we dig through db2diag.log, we found error 964 (transaction log full) as well during runstat however, we didn't found any error during reconcile in db2diag.log

So we increase LOGPRIMARY value to 25 (ISIM default is 13) and then, runstat work smoothly with a few GB increased in archive log. After that, reorg and runstat again didn't eat up space anymore.

We did some test by reconcile as usual and now disk space is not increase anymore.

I still wonder why runstat can cause a disk space problem like this even we just run it manually, restart database didn't help at all once we trigger it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.