Please go through the below Details and provide us the suggestions for ITM/ITCAM(DB2) Issue.

Environment Details:
Windows -> ITMV6.2 FP1
Windows -> DB2V9.5 (Warehouse)
Setup -> Production

Issue:
DB2V9.5(Warehouse) Database SQLOGDIR size is increasing frequently. But we don't have that much data. Even-though the DB is keep on growing. Now we want to reduce the SQLOGDIR size without any impact on the production Database. SQLOGDIR is E:\DB2\NODE0000\SQL00002\SQLOGDIR\*
We created & started the Warehouse database on 02/06/10.The log files under SQLOGDIR is updating from 02/06/10 to till-date. Now we have seen more than 3500 log files. Each log file is 12-16 MB size. Now the total size of the folder(E:\DB2\NODE0000\SQL00002\SQLOGDIR\*) is more than 400 GB.

I have gone through the below links
Controlling DB2 log file growth - Database Forum
developerWorks : Information Management : DB2 for Linux, UNIX, and Windows : Controlling DB2 log file growth ...

Above links doesn’t mentioned whether there will be any problem or not if we delete old log files(JUNE 10 to FEB 11) under SQLOGDIR. We just want to know if there is any impact if we delete old log files under SQLOGDIR . Let me can i delete old log files or not

Are you archiving log files, such as by using LOGARCHMETH1 in db cfg? You can safely delete an files in the log archive path so long as you don't need them for rollforward recovery after restoring a previous backup.

Hi,
Can you just let me know how to enable the LOGARCHMETH1 in db cfg. Also currently my disk space is very low in SQLOGDIR. so Can i delete log files in SQLOGDIR..Or let me know how to set archive and to delete archive files. As my database is in production .provide me quick resolution. Thanks in advance for your support

Look at the parameter "First active log file = S000nnn.LOG" from the "get db cfg for <yourdb>" command.
You cannot delete any log files whose "nnn" number is bigger than the one specified in that parameter.
If archiving is enabled, then normally the older files reside at the path indicated by the parameter
"First log archive method (LOAGARCHMETH1)", as told by Marcus above.

Zevang

Hi,

Thanks for your support.. Please provide me the command how to enable the LOGARCHMET1 in db2 cfg for <db> ..And let me know where to delete this Archive log files. Currently my disk space under SQLOGDIR is HIgh. so without archiving the log files, can i directly delete log files under SQLOGDIR

First thing, you have to create a new folder (usually C:\LOGARCHIVE), where DB2 will archive the log files. May be in other disk your server has access to, like E: or F:, for example.
The command to change the parameter is:
db2 update db cfg for <yourdbname> using logarchmeth1 <pathtonewfolder>

I'm not sure if this change is made right away, but you may check it with the command:
db2 connect to <yourdbname>
db2 get db cfg for <yourdbname> show detail

The result is a list with parameters and their old (first) and new (last) values.

If the values for the parameter "logarchmeth1" are equal it means that it is already changed from your last update command.
If not, you must RESTART your db, so the changes are commited.

Remember, you cannot delete any log file whose number is higher than that registered at the "First active log file" parameter.
In my case, I keep 30 days of log files only. The key is to keep enough log files so you can do "ROLL FORWARD" to complete the BACKUP file you possibly will have to restore when a crash occurs.

Also, only delete files located at the ARCHIVE folder, not at the primary folder.

I'd suggest you to talk to your DBA before deleting the logs and enabling archival logging (if it's currently using circular) in production.

Thanks Zevang.I will be more happy if you can give me some suggestions on below part .I have used the command
db2 get db cfg for <dbname>

LOGFILSIZ : 4000
path to log files = E:\DB2\NODE0000\SQL00002\SQLOGDIR\* (where 400 GB space was occupied) for this folder
First Log archive method (LOGARCHNETH1) = DISK:\Backup\database\warehous\archivelog\
I have checked in D drive, but i dont have Backup folder in my D drive, so i think archiving is not happening for my logs.
First Active Log file = S0027392.LOG

Let me know the following
1)I am having 4000 Log files in SQLOGDIR starting from S0000001.LOG to S0027416.LOG. so what are all the log files i can delete directly from SQLOGDIR without any impact on my Database.
2)I dont have backup folder in my D drive. I think t is not archiving. If i use the other drive to store the archive log with the following command
db2 update db cfg for <yourdbname> using logarchmeth1 <pathtonewfolder>
It wont be any affect on my database right?
suppose if i run the above archive command, how much size it can archive and let me know its usage in details. Thank you very much .. Please help me..

[QUOTE=harikiran;6494529]Thanks Zevang.
I will be more happy if you can give me some suggestions on below part .
I have used the command
db2 get db cfg for <dbname>

LOGFILSIZ : 4000
path to log files = E:\DB2\NODE0000\SQL00002\SQLOGDIR\* (where 400 GB space was occupied) for this folder
First Log archive method (LOGARCHNETH1) = DISK:\Backup\database\warehous\archivelog\
I have checked in D drive, but i dont have Backup folder in my D drive, so i think archiving is not happening for my logs.
First Active Log file = S0027392.LOG
My Database Logging Type : ARCHIVE ( find the attachment for further refernce)
Let me know the following
1)I am having 4000 Log files in SQLOGDIR starting from S0000001.LOG to S0027416.LOG. so what are all the log files i can delete directly from SQLOGDIR without any impact on my Database.
2)I dont have backup folder in my D drive. I think it is not archiving. If i use the other drive to store the archive log with the following command
db2 update db cfg for <yourdbname> using logarchmeth1 <pathtonewfolder>
It wont be any affect on my database right?
suppose if i run the above archive command, how much size it will reduce the disk space.
Also find the attachment(NewMicrosoft DOC 2) for where i took a backup of DB including log files in Image format. After taking backup of my DB. the backup folder is having just 18GB. what i am asking here is, my DB size is 98GB and the SQLOGDIR size is 400GB, but my backup folder is showing just 18GB. does backup will take the SQLOGDIR log files? Let me know. waiting for your reply. Thanks.

Check the following article for information about db2 logging:
An Overview of Transactional Logging in DB2 Universal Database

It was written before LOGARCHMETH1 existence so also check the following:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


And read the following to understand how db2 is managing the logs:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


If all logs remain in E:\DB2\NODE0000\SQL00002\SQLOGDIR\, then archival is not working (you can check db2diag.log for errors). The path pointed to by LOGARCHMETH1 has to exist in order for db2 to archive the logs. As previously mentioned, the "First Active Log file" is the first active log. Logs with the sequence # less than that are not active and can be removed as long as they're not required for restore/rollforward.

You need to decide how many backup images you have to keep (in case you have to restore this db) and based on that determine which logs are not needed and can be removed. You can execute "db2 list history backup all for db <db name>" to get a list of backup images and its associated logs. The backup command will backup the data stored in the database and include the logs that were active during backup (if it's an online backup).

It seems to me that you need just to restart the server so the cfg above become active. Sometimes only "db2 deactivate db <yourdbname>" and "db2 activate db <yourdbname>" do the job.