The following commands will delete historical messages in order to purge a database in AS400.
The HSTMONITOR database: B_DETECTOR/BDHST02X.
You can purge it with the command: B_DETECTOR/DLTHSTMSG
The Job History database: B_DETECTOR/HSJOB01P
You can purge it with the command: B_DETECTOR/DLTJOBMSG
The USRMONITOR database: B_DETECTOR/USHST01P
You can purge it with the command: B_DETECTOR/DLTUSRMSG
The SQLMONITOR databases: B_DETECTOR/SLLOG01X and B_DETECTOR/SLDBM01X
You can purge it with the commands: B_DETECTOR/DLTSQLMSG and B_DETECTOR/DLTDBMMSG
The SQI Monitor database: B_DETECTOR/SILOG01X
You can purge it with the command: B_DETECTOR/DLTSQIMSG
BDHST02X: Historical database for VISUAL Message Center.
HSJOB01P: Historical database for all jobs retrieved from system logging messages. Apart from the qualified job name you can get information about the job subsystems in this file. This file is filled by HSTMONITOR.
USHST01P: Historical database with information about the real users for each job in the system. You can also get information about the source IP address of a specific job in the system. This file is filled by USRMONITOR.
SLDBM01X: This is the file to which the performance statistics are to be written. It is fill by system logger.
SLLOG01X: The report generated for SQLMONITOR that contains historical SQL sentences.
SILOG01X: The report generated for SQLMONITOR that contains interactive SQL sentences.
Be careful when launching the B_DETECTOR/BDHST02X command at the parameter selection. The REORGANIZE *YES option will strongly decrease the system performance. So this option should be set as *YES only during a week-end, or during scheduled maintenances. That option will reduce the size of the database, and reorganize all the registries. Without that option, the events will be deleted, but the space won't be reallocated.
Some REORGANIZE won't reorganize if the objects are locked. If that is the case, end T4NICELINK SBS before running the Reorganize.
Note: the following commands could be used to stop and start T4NICELINK subsystem:
Stop: ENDSBS SBS(T4NICELINK) OPTION(*IMMED)
Start: STRSBS SBSD(T4NICELINK/T4NICELINK)
There are two alternative options you can choose in order to persist the information:
Option 1 - Create a DTS to migrate information to a SQL Server on Windows Server. This will be cheaper regarding to storage, availability and performance. With this option it will be possible to execute reports without having to restore any backup or tape.
The DTS can be scheduled to be performed automatically and this could be helpful also with automatic database clean up with the DLTHSTMSG command on the iSeries.
Option 2 - Store all the information on Tape. This could be done faster than option one but to be able to execute reports the files will need to be restored on the iSeries so the reports could be executed. Restoring the files will demand human time consumption and might not be available 24/7 to execute reports.
Besides of what option will be chosen it has to be taken into consideration a periodical purge for Tango04 files. There are many files that can be purged in order to retrieve system space. The most important File to backup is BDHST02X that is where all agents information is stored. All the other files are intermediate tables.
Still have questions? We can help. Submit a case to Technical Support.