Develop Informix Open Admin Tool (OAT) plug-ins: Backup history

Helpful OAT enhancements

Backups taken with Informix® ON-Bar are recorded in a separate database called sysutils. This article explains how to create a plug-in for the Informix OpenAdminTool (OAT) to query and display a list of backups and restores along with additional information such as the date when a backup was verified. This information can be very helpful for administrators who are keeping an overview about the backup history.

Share:

Gerd Kaluzinski (gerd.kaluzinski@de.ibm.com), Certified IT Specialist, IBM

Gerd KaluzinskiGerd Kaluzinski is a Certified IT-Specialist who has worked with Informix since 1992. He works as consultant at customer sites in the IBM Software Group Services team and as a trainer for IBM education. His specialties are Informix replication (HDR/RSS/ER/GRID), Informix Warehouse Accelerator, Datablades (for example TimeSeries, Excalibur, Spatial) and Stored Procedures. He is the editor of the monthly "German IBM Informix Newsletter" (repository at: http://informix-zone.com/informix-german-newsletter).



20 June 2013

Also available in Chinese

Overview

This article explains how to display the information about the backup history and the amount of logical logs used each day by querying the tables from the database sysutils. You will learn how to extract the information using SQL statements and how to provide the data for a backup history table. Furthermore, the article explains how to include this information in a PHP program and how to display a data table and a line chart showing the data. Keep in mind that the structure of the sysutils database could change in new versions, so it might be necessary to adapt changes if a new version of Informix is released.

The next section guides you through the steps that are needed to create an OAT plug-in. A later section will show how language support is added to this plug-in. Finally, you'll learn how to install the plug-in. The following figures show you how this will appear in OAT. Figure 1 (see larger image) shows the backup history in OAT.

Figure 1. Backup history in OAT
Backup history for the rootdbs selected from sysutils

In addition to the backup history, this plug-in displays information about the amount of logs that are consumed each day. The line plot in Figure 2 provides a quick view to detect exceptional situations and the trend over a long period of time.

Figure 2. Backup history about the amount of logical logs each day
A line graph showing the amount of logical logs each day

Query the sysutils database

The sysutils database contains information collected during backup or restore using the ON-Bar command. When you are using ONTAPE, there is no backup information stored in the sysutils database. In this case, the only information you can get about a backup is the time stamp of the latest valid backup of the instance; you do this by querying the sysmaster database.

The sysutils database tables are:

  • bar_server holds the name of the Informix instance and the host name or IP-Address
  • bar_object contains all objects that have been saved
  • bar_action contains a list of all backup and restore action commands using ON-Bar
  • bar_instance contains a list of successfully saved objects

Note: Figure 3 (see larger image), showing the sysutils database, was created using the scripts described in the developerWorks article "Create an ER diagram for an Informix database."

Figure 3. Sysutils database
DDL of the sysutils database

The table bar_action is the key for this evaluation. Here, you can find the start and end date of a backup. Within bar_action, an object is only referenced by the object ID. So the table bar_object is needed to get the name of the object. In the case of logical log backups, the name contains the unique logical log ID.

The table bar_instance holds additional information such as if and when a backup has been verified and the name and number of the storage manager. In addition, there is information about the point in time when the logical log sealed and about the sealing of the previous logical log.

Listing 1 shows what the query to extract the backups taken with ON-Bar could look like.

Listing 1. Query for all backups
SELECT act_aid AS backup_id,
    DECODE(act_type,
            1, 'Backup',
            2, 'Restore',
            3, 'Imported Restore',
            4, 'Fake Backup',
            5, 'Whole-System Backup',
            6, 'Whole-System Restore',
            7, 'Expired Backup',
            8, 'External Restore',
            act_type::varchar(20)
    ) AS act_type,
    DECODE(obj_type,
            'L', 'Logical log', 'Dbspace'
    ) AS obj_type,
    SUBSTR(obj_name,1, 8) AS object_name,
    act_start AS start_of_backup,
    act_end AS end_of_backup,
    act_end - act_start AS duration_of_backup,
    ins_verify_date AS verify_date
    FROM sysutils:bar_action a, sysutils:bar_instance i, sysutils:bar_object o
    WHERE   
            a.act_aid = i.ins_aid AND
       		a.act_oid = o.obj_oid AND
       		a.act_oid = i.ins_oid AND
       		o.obj_type in ('R', 'CD', 'ND', 'L')
    ORDER BY act_start desc, backup_id

In this query, the action type is already decoded, and the object type is decoded to logical log or dbspace. With some variation, this query delivers the list of backups of the ROOTDBS (obj_type 'R'), the backups of logical logs (obj_type 'L'), or a list of so-called Whole-System Backups (act_type '5'). If the backup is done using ONTAPE instead of ON-Bar, then the information is not stored in the sysutils database. To get information about the latest successful backup, a query is included in table sysdbstab in the sysmaster database.

Listing 2. Query last backup from sysmaster database
SELECT 
        dbsnum,
        name[1,24], 
        logid0, logpos0,
        DBINFO('utc_to_datetime',level0) AS backup_L0, 
        DBINFO('utc_to_datetime',rest_time) AS restore
FROM sysmaster:sysdbstab
WHERE BITVAL(flags, '0x2000') != 1

Some important information for the administrator is the number of logical logs that are consumed during one day. This information can also be selected using the sample query shown in Listing 3.

Listing 3. Query the number of logical logs filled each day
SELECT sysmaster:l2date(i.seal_time) AS day, 
        MIN(obj_name)::char(8) AS from_log, 
        MAX(obj_name)::char(8) AS to_log,
        COUNT(*) as cnt 
FROM sysutils:bar_object c, sysutils:bar_instance i
WHERE c.obj_type = 'L'
        AND i.ins_oid = c.obj_oid
GROUP BY 1
ORDER BY 1 DESC

Now, you have the queries about the backup and restore built and are able to test them using dbaccess. The next step is to include the queries in a PHP script that can be called from the OAT.


Building a PHP application

If you are not familiar with building PHP applications, you can take one of the OAT modules as a template to build the new module (as I did in this article).

The PHP module must start with <php? and must end with ?>. Listing 4 contains the page header and the language for the menu.

Listing 4. Class definition with header and language setting
class BACKUP {

        public $idsadmin;

        function __construct(idsadmin)
        {
            $this->idsadmin = idsadmin;
            $this->idsadmin->load_lang("BACKUP");
            $this->idsadmin->html->set_pagetitle($this->idsadmin->lang('mainTitle'));
        }

            ... here starts the run() section followed by the separate queries ...

}

The run() part is a simple section to switch to the different functions that can be chosen from the menu. Each section must have a different identifier and an associated function in the actual class definition.

The run() section is displayed as the sub-menu of the menu (here: BACKUP) and looks like Figure 4.

Figure 4. Backup history menu choice
Backup history run section
Listing 5. The run() function to call the queries
function run()
{
    $this->idsadmin->setCurrMenuItem("BACKUP");
    switch($this->idsadmin->in['do'])
    {
        case 'BACKUP_list':
            $this->BACKUP_list();
            break;
        case 'BACKUP_list_w':
            $this->BACKUP_list_w();
            break;
        case 'BACKUP_list_r':
            $this->BACKUP_list_r();
            break;
	...
        default:
            $this->idsadmin->error("Sorry I don't know what you want to do");
            break;
        }
} // end run

The following functions are included in this plug-in:

  • BACKUP_list (all)
  • BACKUP_list (-w)
  • BACKUP_list ROOTDBS
  • BACKUP_list LOGS
  • RESTORE_list
  • LOGs per day
  • Last Backup

The only things missing are the modules to query the data and display it within a table. In Listing 6, you see the module that lists all backups of the rootdbs. After setting the page title and the highlighting of the menu choice, the query is handed over to a standard function of the OAT, display_tab_by_page(). This function is called with the arguments Page header, an array with the column headers, the query, the number of values to display, and the default number of values to display.

Listing 6. Module to list the backups of the rootdbs
    function BACKUP_list_r()
{
    /**
    * Set the page title and the menu item that should be highlighted for this report
    */
    $this->idsadmin->html->set_pagetitle("BACKUP_list_r");
    $this->idsadmin->setCurrMenuItem("BACKUP_list_r");

    $db = $this->idsadmin->get_database("sysutils");
    require_once ROOT_PATH."lib/gentab.php";

    $tab = new gentab($this->idsadmin);

    $qry = "SELECT
    act_aid AS backup_id,
    DECODE(act_type,
            1, 'Backup',
            2, 'Restore',
            3, 'Imported Restore',
            4, 'Fake Backup',
            5, 'Whole-System Backup',
            6, 'Whole-System Restore',
            7, 'Expired Backup',
            8, 'External Restore',
            act_type::varchar(20)
    ) AS act_type,
    DECODE(obj_type,
            'L', 'Logical log', 'Dbspace'
    ) AS obj_type,
    substr(obj_name,1, 8) AS Object_Name,
    act_start AS Backup_Start,
    act_end AS Backup_End,
    act_end - act_start AS duration,
    ins_verify_date AS Verify_Date
    FROM sysutils:bar_action A, sysutils:bar_instance I, sysutils:bar_object O
    WHERE
    A.act_aid = I.ins_aid AND
    A.act_oid = O.obj_oid AND
    A.act_oid = I.ins_oid AND
    O.obj_type in ('R')
    ORDER BY act_start desc, Backup_ID";

    $qrycnt=100;

    $tab->display_tab_by_page("Backup History (rootdbs)",
    array(
    "1" => "Backup_ID",
    "2" => "Type",
    "3" => "Object_Type",
    "4" => "Object",
    "5" => "Start time",
    "6" => "Stop time",
    "7" => "Duration",
    "8" => "Verify"
    ),
    $qry,$qrycnt,100);

}// end of function BACKUP_list_r

The SQL statements used here are the same that I created in the first part of this article. If you need additional information about the backups, just adapt the query and include it in a new module.

Now the module is complete. The only things missing are the language section and the packaging to create a valid plug-in from this module.


Display the amount of logs

Regarding the operation of backup and restore, the administrator has two tasks: to guarantee that the backups are done and have been verified and to keep the log backup running. So knowing the amount of logs consumed each day helps to keep the system stable. To identify possible upcoming problems regarding the amount of consumed logical logs, the function in Listing 7 might be helpful.

Listing 7. Module to list the amount of logs each day
function LOG_list()
{
    /**
    * Set the page title and the menu item that should be highlighted for this report
    */
    $this->idsadmin->html->set_pagetitle("LOG_list");
    $this->idsadmin->setCurrMenuItem("LOG_list");

    $db = $this->idsadmin->get_database("sysutils");
    require_once ROOT_PATH."lib/gentab.php";

    $tab = new gentab($this->idsadmin);

    $qry = "
            SELECT sysmaster:l2date(i.seal_time) AS day, 
                    min(obj_name)::char(8) AS from_log, 
                    max(obj_name)::char(8) AS to_log,
                    count(*) AS cnt 
            FROM sysutils:bar_object c, sysutils:bar_instance i
            WHERE c.obj_type = 'L'
                    AND i.ins_oid = c.obj_oid
            group by 1
            order by 1 desc
    ";

    $qrycnt=100;

    $tab->display_tab_by_page("LOGs per day",
    array(
    "1" => "day",
    "2" => "first log",
    "3" => "last log",
    "4" => "number",
    ),
    $qry,$qrycnt,100);

}// end of function LOG_list
Figure 5. Backup history of logical logs per day (list)
Backup history of logical logs per day (list)

To analyse the number of logical logs consumed each day, a line chart provides a quick overview about exceptional events.

Figure 6. Backup history of logical logs per day (graph)
Backup history of logical logs per day (graph)

The menu list depends on the chosen language. If you do not provide language files, a fallback to the English version will be chosen. In this example, I provide an example for German users to get the adapted menu labels in German.

The following section includes the language files for English and German. The location for the language files is (module_name)/lang/(language)/lang_menu.xml. So you create the modules for the module BACKUP and for the languages en_US and de_DE.

Listing 8. Menu labels for English version: BACKUP/lang/en_US/lang_menu.xml
<lang module="lang_menu">
<BACKUP><![CDATA[BACKUP]]></BACKUP>
<BACKUP_list><![CDATA[BACKUP_list (all)]]></BACKUP_list>
<BACKUP_list_w><![CDATA[BACKUP_list (-w)]]></BACKUP_list_w>
<BACKUP_list_r><![CDATA[BACKUP_list ROOTDBS]]></BACKUP_list_r>
<BACKUP_list_l><![CDATA[BACKUP_list LOGS]]></BACKUP_list_l>
<RESTORE_list><![CDATA[RESTORE_list]]></RESTORE_list>
<LOG_list><![CDATA[LOGs per day]]></LOG_list>
<ONTAPE_list><![CDATA[Last Backup]]></ONTAPE_list>
</lang>
Listing 9. Menu labels for German version: BACKUP/lang/de_DE/lang_menu.xml
<lang module="lang_menu">
<BACKUP><![CDATA[Sicherungen]]></BACKUP>
<BACKUP_list><![CDATA[Sicherungen (alle)]]></BACKUP_list>
<BACKUP_list_w><![CDATA[Sicherungen (Whole System)]]></BACKUP_list_w>
<BACKUP_list_r><![CDATA[Sicherungen ROOTDBS]]></BACKUP_list_r>
<BACKUP_list_l><![CDATA[Sicherung der Logs]]></BACKUP_list_l>
<RESTORE_list><![CDATA[Rücksicherungen]]></RESTORE_list>
<LOG_list><![CDATA[Genutzte Logs je Tag]]></LOG_list>
<ONTAPE_list><![CDATA[Neuestes Backup]]></ONTAPE_list>
</lang>

The German menu looks like Figure 7.

Figure 7. Backup history menu choice (German)
Backup history run section

Now, you must create a plug-in header file with version information and a short description of the module. This file should include the server version where this version was created and some information about the author.

Listing 10. Header file for the plug-in: plugin.xml
<oat_plugin>
    <plugin_info>
        <plugin_name>Backup explorer</plugin_name>
        <plugin_author>Gerd Kaluzinski</plugin_author>
        <plugin_desc>Plug-in für Backup Liste</plugin_desc>
        <plugin_version>1.42.xC3</plugin_version>
        <plugin_buildtime>05.05.2013 23:42:24</plugin_buildtime>
        <plugin_server_version>11.70.xC7</plugin_server_version>
        <plugin_upgrade_url>none</plugin_upgrade_url>
        <!-- <plugin_license></plugin_license> -->
    </plugin_info>
    <plugin_menu>
        <menu_pos>Logs</menu_pos>
        <menu id="BACKUP" name="BACKUP" lang="BACKUP" >
        <item name="BACKUP list"  link="index.php?act=BACKUP/BACKUP&
		do=BACKUP_list" title="List Backups" lang="BACKUP_list" />
        <item name="BACKUP list_w"  link="index.php?act=BACKUP/BACKUP&
		do=BACKUP_list_w" title="List Backups (-w)" lang="BACKUP_list_w" />
        <item name="BACKUP list_r"  link="index.php?act=BACKUP/BACKUP&
		do=BACKUP_list_r" title="List Backups (ROOTDBS)" lang="BACKUP_list_r" />
        <item name="BACKUP list_l"  link="index.php?act=BACKUP/BACKUP&
		do=BACKUP_list_l" title="List Backups (LOGS)" lang="BACKUP_list_l" />
        <item name="RESTORE_list list_l"  link="index.php?act=BACKUP/BACKUP&
		do=RESTORE_list" title="List Restores" lang="RESTORE_list" />
        <item name="Log information"  link="index.php?act=BACKUP/BACKUP&
		do=LOG_list" title="List Log count" lang="LOG_list" />
        <item name="BACKUP (ontape)"  link="index.php?act=BACKUP/BACKUP&
		do=ONTAPE_list" title="BACKUP (ontape)" lang="ONTAPE_list" />
        </menu>
    </plugin_menu>
</oat_plugin>

Now all parts are complete and you can create the plug-in by collecting them within a zip archive. If you want, you can create a README file to provide more information about this plug-in.

Listing 11. Create a zip archive containing the plug-in
zip -r backup.zip *

./plugin.xml
./BACKUP
./BACKUP/lang
./BACKUP/lang/en_US
./BACKUP/lang/en_US/lang_menu.xml
./BACKUP/lang/de_DE
./BACKUP/lang/de_DE/lang_menu.xml
./BACKUP/BACKUP.php
./README

This zip archive must be placed in the plugin_install directory of the OAT: $InformixDIR/OAT/Apache_2.4.2/htdocs/openadmin/plugin_install.

After this, the plug-in is ready to be installed using the admin interface of the OAT. Figure 8 (see larger image) and Figure 9 (see larger image) show the list of installation candidates and installed plug-ins.

Figure 8. List of plug-ins and installation candidates
List of plug-ins and install candidates
Figure 9. List of installed plug-ins
List of installed plug-ins

Changes in OAT versions

The plug-ins for the OAT described in this article are created to work with OAT version 2.76 and above. If you are still using the OAT in older versions, then you must use the alternate package in Downloads. The changes within the script are minor. The only change was that the call $tab = new gentab(&$this->idsadmin); was replaced by $tab = new gentab($this->idsadmin);.


Summary

Additional functionality within the OpenAdminTool can be included by self-written plug-ins. In this example, the administrator missed an overview about the verification of backups and wanted to know how many logical logs were used per day over the last weeks.

Based on this example, the article demonstrated how easy it is to include an SQL statement as a plug-in in the OpenAdminTool. Using the built-in functions you can display the query results as table, line chart, or pie chart.


Send us Your OAT plug-in

If you have already built an OAT plug-in and want the community within IBM developerworks to take advantage of it, then send the plug-in with a short description to the author Gerd Kaluzinski.


Downloads

DescriptionNameSize
The plug-in to work with OAT 2.76 and abovebackup.zip10KB
The plug-in to work with OAT 2.75 and belowbackup_2.75.zip10KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=934507
ArticleTitle=Develop Informix Open Admin Tool (OAT) plug-ins: Backup history
publish-date=06202013