In this article, we will discuss how to find database backup time in a Netezza environment, troubleshoot when the backup status shows active, and provide useful queries for tracking backups.
You can use the following query to find database backup time in a Netezza environment. This query will provide the database backup type (Full / Differential / User backup) for each database along with the backup start and finish time. The query will not display any data for failed database backup attempts.
select DBName, OPTYPE, starttime, lastupdate, (lastupdate-starttime)/60 as DurationInMinutes
from _v_backup_history
where status='COMPLETED' and DBNAME is NOT NULL
and StartTime>'2012-10-01 00:00:00'
order by DBName, starttime
Unfortunately, we cannot retrieve the backup location as it is not stored in any system table/view in Netezza. For that, we have to look into the backup log file.
When you check the database backup history using the following query for your Netezza environment, you may find some active backups.
select * from _V_Backup_History
Now when you try to check active connections, you are not able to find any backup running. Here you may also see the start time being too old. So why is the system table/view showing us wrong information? The reason for this incorrect information is that if you kill any backup process directly from the OS by issuing Kill Linux process id, then Netezza is not able to understand if the backup is really killed and mark it as failed or if the backup is still running. So, the system table is not updated, and you get wrong information.
To fix this, you may have to directly update the system table _T_Backup_History, which holds this information.
select * from _V_Backup_History where status = 'ACTIVE'
Select DBNAME, STATUS,USERNAME,STARTTIME, LASTUPDATE from _V_Backup_History
where DBNAME != 'NULL' and status = 'ACTIVE'
Here we have put DBNAME not equal to NULL because if a database is dropped from Netezza, then it updates the backup table, and replaces the database name with NULL.
In this article, we will discuss how to check the status of active backups in Netezza and understand their implications for your database management. We'll provide examples, tables, and illustrations to help you easily follow along.
It is crucial to verify the backup status in Netezza to ensure data protection, monitor ongoing operations, and resolve potential issues before they cause significant downtime. An active backup indicates that the system is currently backing up a database or table, preventing new write operations until the process completes.
To check the status of active backups in Netezza, you can utilize the following SQL command:
SELECT * FROM QUERY_MANAGER.QUERY WHERE TASK_STATE IN ('RUNNING', 'WAITING');
This command selects all running and waiting tasks from the QUERY_MANAGER.QUERY table, which contains information about ongoing queries in Netezza. The TASK_STATE clause filters for only active tasks (RUNNING) and those waiting to start (WAITING).
The output of the command will display various columns containing information about each active task, including its ID, database name, task type, and status. To identify backups, look for tasks with a type matching "BACKUP" or "DIFFBACKUP." Here's an example:
| TASK_ID | DATABASE_NAME | TASK_TYPE | TASK_STATE |
|--------|--------------|-------------|------------|
| 123456 | mydb | BACKUP | RUNNING |
The above table illustrates an example of an active backup task for the "mydb" database.
Checking and understanding the status of active backups in Netezza is essential to efficient data management. By following this guide, you can ensure that your backups are running smoothly and take appropriate actions if any issues arise.
Explore other SQL commands and techniques to further enhance your Netezza backup management skills. Happy learning!