The backup of the metadata repository can be undertaken as a separate exercise from the general backup of the data warehouse. The metadata backup does not backup any of the actual data warehouse tables. It only saves the table definitions, index definitions, procedures etc., so is not normally large.
The backup includes any tables that begin with "dss_". In this way, key metadata tables such as dss_parameter, dss_source_system and dss_fact_table are included in the backup. These tables are required if the restored metadata is to function correctly.
It is recommended that the metadata is backed up at least daily when possible. There are two methods for backing up the meta repository. These are through a scheduled UNIX task, and through the main WhereScape RED tool.
UNIX Backups
Where the data warehouse database is running on a UNIX server, it is possible to schedule a regular backup of the metadata. The UNIX scripts shipped with WhereScape RED include a script called from cron to perform daily backup of the metadata. These backups are in turn compressed and saved for 60 days by default. Refer to the RED Installation Guide for details on these scripts and their setup.
Windows Backups
Two main methods of Windows backup exist within WhereScape RED. The first is a database independent backup, which is designed purely for moving the meta repository, and should not be used for regular backups. The other method is the database specific backup. See the following sections for the appropriate database.
Oracle Windows Backups
An Oracle export based backup can be taken from the WhereScape RED tool by selecting the Backup > Export the metadata (Oracle Export) menu option. This option assumes that the Oracle Client on the PC is the same version as that of the Oracle database where the WhereScape RED meta repository is stored. The backup may not work if the versions differ.
When executed, this menu option attempts to locate the Oracle export utility. This utility is normally called 'exp' and resides in the Oracle bin directory. If WhereScape RED cannot locate this utility or it has not been loaded onto the PC, then the export does not proceed, and an error message is displayed.
A pop-up window asks for a file name for the export. A directory should be chosen, and a name entered. When the Save button is clicked, the export will start. The following files are created (where 'file' is the name of the chosen file). A dialog box appears to show the results of the backup.
File name | Purpose |
|---|---|
file.bat | Windows command file containing the export command. |
file.dbl | Command file for the re-creation of any database links. |
file.drp | Command file for dropping all metadata tables. (Used in the restore prior to a reload). |
file.exp | Oracle export file. |
file.log | Log file of the export. |
file.par | Parameter file for the export. Contains an entry for each meta table to be exported. |
file.pro | A file containing each procedure stored in the metadata. |
file.seq | Command file to re-create the sequences used by both the metadata and the data warehouse tables. |
If problems are encountered with the backup, it may be possible to manually run the generated .bat script file to ascertain what has gone wrong.
SQL Server Windows Backups
A SQL Server bcp based backup can be taken from the WhereScape RED tool by selecting the Backup > Export the metadata (Sql server bcp) menu option. This option assumes that the SQL Server bcp utility is available on the PC.
When executed, this menu option attempts to locate the SQL Server bcp utility. This utility is normally called 'bcp' and resides in the SQL Server bin directory. If WhereScape RED cannot locate this utility or it has not been loaded onto the PC, then the export does not proceed, and an error message is displayed.
A pop-up window asks for a directory name for the export—a directory must be chosen or created. The export starts when the Save button is clicked. The following files are created. A window appears to show the results of the backup.
File name | Purpose |
|---|---|
wsl_bcp_out.bat | Windows batch file used to run all the bcp table exports |
wsl_bcp_out.err | Errors from the bcp export of each table |
wsl_bcp_out.log | Log file of the bcp table exports |
*.bcp | Each metadata table is stored with a .bcp extension. This is the export of each table. |
wsl_bcp.par | Parameter file for the export. Contains an entry for each meta table to be exported. Unused by SQL Server. |
wsl_bcp.drp | Command file for dropping all metadata tables. (Used in the restore prior to a reload). |
wsl_bcp.pro | A file containing each procedure stored in the metadata. |
wsl_bcp.seq | Unused by SQL Server. |
wsl_bcp.dbl | Command file for the rebuild of all the linked servers. Used during the restore. |
wsl_bcp.bld | Basis of the command file that will be used to restore the metadata. This file is modified to include the target directory prior to restore. |
If problems are encountered with the backup, it may be possible to manually run the generated .bat script file to ascertain what has gone wrong.
DB2 Windows Backups
A DB2 IMPORT based backup can be taken from the WhereScape RED tool by selecting the Backup > Export the metadata (DB2 export) menu option. This option assumes that the IBM DB2 EXPORT command is available on the PC.
When executed, this menu option attempts to locate the SQL Server bcp utility. This utility is normally called bcp and resides in the SQL Server bin directory. If WhereScape RED cannot locate this utility or it has not been loaded onto the PC, then the export does not proceed and an error message is displayed.
A pop-up window asks for a directory name for the export. A directory should be chosen or created. When the Save button is clicked, the export will start. The following files are created. A dialog box appears to show the results of the backup.
File name | Purpose |
|---|---|
RED.bat | Windows batch file used to run the DB2 EXPORT for each metadata table. |
RED.cmd | Control file for the export. Contains an entry for each meta table to be exported. |
RED.log | Log file of the EXPORT. |
RED.pro | A file containing each procedure stored in the metadata. |
*.ixf | Each metadata table is stored in a file with a ixf extension. This is the export of each table. |
restore.drp | Command file for dropping all metadata tables. (Used in the restore prior to a reload). |
restore.lst | Basis of the command file that will be used to restore the metadata. This file is modified to include the target directory prior to restore. |
If problems are encountered with the backup, it may be possible to manually run the generated RED.bat script file to ascertain what has gone wrong.