In DB2, how to know date/time a particular Database was put to RO?

[saurabhgaur]

Is there any place where one can find at what date/time a particular Database was put to RO? Or got any change in status ? Is that available to see somewhere in the subsystem catalog or BSDS?

[Anuj Dhawan]

Hi SaurabhGaur,

Welcome to the forums!I've created a new post for your question to help you get the best possible attention. It's generally recommended to start new threads for new questions, and then link to any relevant previous discussions within your post, which you've looked at. This helps others searching for similar information find your question more easily.

If you had added your question to an older thread, it might have been harder for others to notice and respond.

Please feel free to ask your question here, and I'm sure the community will be happy to help!

[DB2 Guy]

As far as I know, there isn't a single, definitive way to pinpoint the exact moment a DB2 database was switched to read-only (RO) status. However, there are several methods that can help you narrow down the timeframe:1. DB2 Logs:

• Check the DB2 diagnostic log files (db2diag.log and others) for relevant entries. These logs may contain timestamps and messages indicating the database being placed in RO mode. Look for keywords like "READONLY" or "STOP DATABASE".
• The timeframe covered by the logs will depend on your log rotation settings.

2. System Operations Staff:

• If the RO switch was due to a planned maintenance activity, system administrators or operations staff likely have records of the specific time window.

3. Monitoring Tools:

• If your organization uses database monitoring tools, they might have logged events related to the database status change. These tools can provide a more precise timestamp for the RO transition.

4. DB2 Commands: While not necessarily revealing the exact time, you can use the

Code: Select all

DISPLAY DATABASE

command to check the current status of the database, including whether it's read-only or read-write. This might help confirm if it's currently in RO mode. By combining information from these sources, you can get a clearer picture of when the database was switched to RO status.

[alexm3913]

Great question about tracking DB2 database status changes! While there's no single catalog table or BSDS record that directly logs read-only transitions, you have a few options to piece together the timeline.One approach is to query the SYSIBM.SYSLGRNX table which contains a history of logged events like -STO[P] DB commands that would put a database in RO mode. You can filter for rows with DBNAME and TYPE='P' (event marker) to narrow down the list of suspects.For example:SELECT TIMESTAMP, LRHTIME, AUTHID, PLANNAME FROM SYSIBM.SYSLGRNX WHERE DBNAME = 'MYDB' AND TYPE = 'P' ORDER BY LRHTIME DESC;This will show you recent commands run against the database along with the authorization ID and plan name. Look for -STO DB or similar that would have flipped the read-only switch.Another option is to enable audit tracing for the database with the ACCESS_CONTROL_LIST parameter. This will log changes to the database status in IFCID 0142 which you can then review in the audit log.As a last resort, you could also try searching through your SYSLOG or job output for the timeframe in question. Grep for things like "-STO DB" or "DSNT501I" messages indicating a read-only transition.It takes a bit of detective work, but with some SQL sleuthing and log diving you should be able to pinpoint when the database went read-only. Let me know if you have any other questions!