Where is my ADT database and how do I back it up?

Products

• Advantage Data Transformer

Issue/Introduction

Description:

There are 50 ADT tables. They may reside in one or two databases. This document describes how to locate your ADT database or databases and methods for creating backups.

Solution:

There are three database configurations

• All 50 ADT tables may be in one database that contains only ADT tables. In that case, the database is called IDB by default, but it may be any name chosen by the Administrator who configured ADT. It is owned by infopump.
  
  
• All 50 ADT tables may be in one database, but that database also contains tables owned by other CA products. This database is called MDB and is owned by another user such as uapmadmin or mdbadmin. The infopump user will have been granted full rights to the ADT tables.
  
  
• The ADT tables may be in two databases. The basic Idb contains the objects you view through Script Manager. The extended Idb is the metadata store which resides in a repository database owned by the CA Repository for Distributed Systems administrator. The objects in these tables are viewed through the ADT Mapper.

How to determine what you have

To determine which configuration you have review your ODBC DataSources used to connect through Mapper and Script Manager.

Run Mapper.

• If Mapper login asks for the metadata store connection and also for an IDB data source name, and you normally select "[Internal metadata store]," or if Mapper login prompts only for the IDB data source name; then all of your ADT tables are in one database. Make a note of the name of the data source at the top of the Mapper login screen.
  
  
• If Mapper login asks for the metadata store connection and also for an IDB data source name, and you normally use two different DSNs, then your ADT tables are in two databases. Make a note of the names of both data sources.

Use the ODBC Administrator DataSources configuration wizard to find the name of the Microsoft Access file or the database name for Microsoft SQL Server, Sybase SQL Server, or Ingres. For Oracle, use the ODBC Administrator DataSources configuration wizard to find the Oracle "server" name and then locate that name in the TNSnames.ora file.

Backing up the databases

When you want a complete copy of a database including logins, roles, table definition information, and data for the purpose of recreating the database from scratch, then use your database server's utility. If, though, you wish to backup ADT data only, ADT provides utilities to do this.

Database Server method:

Once you know where your ADT tables are, you can use your database server's back-up utility to save the entire database or schema. This kind of backup enables you to do a database server restore to completely recreate the database and all its tables, data, users, etc. If the database is the MDB, the backup will include restore information for ADT as well as for other CA products that own tables in the MDB.

ADT method:

Whether the ADT tables are in one or two databases, the ADT backup method uses two utilities, one for the metadata store and one for the basic IDB. These utilities will save the data that's in the ADT tables, but not database server-specific information, such as database logins.

• Backing up the Script Manager information
  
  Use the Script Dump Utility to back up the data contained in the ADT tables controlled by Script Manager, the basic IDB. These data would be profiles, scripts, fragments, ADT Server settings, logs such as Messages, Executions, and so on - anything you can view through the ADT Script Manager. The output from the Script Dump Utility is a .dmp file. Run the utility from Start, Programs, Computer Associates, Advantage, Data Transformer, Script Dump Utility to generate a file in the ADT bin folder called ip<today'sDate>.dmp.
  
  
• Backing up the Metadata Store
  
  Use the Mapper Export utility to back up the data contained in the Mapper tables, such as program layouts, reusable transformations, source and target metadata, etc. The output from this utility is an .xml file. Run the utility from Mapper, File, Export to generate a file called <DSNname Programs>.xml such as myIDB Programs.xml or any name of your own choosing, to any location you select using a Windows-style Save-As dialog.

Notes:

• The Mapper export utility can export as much as the entire metadata store or as little as the metadata for one table or one program. This depends on how high up the metadata tree is highlighted at the time of the export. The Script Dump Utility always dumps the entire basic Idb.
  
  
• To restore, use the Script Load Utility and Mapper Import. If an object of the same name exists in the Idb, the Script Load Utility will skip loading that object. Mapper provides import options. See Mapper Help Topics for more information.
  
  
• See TEC475048 for a list of the ADT tables and whether they belong to Mapper or Script Manager.
  
  
• See TEC495286 on how to purge the ADT log tables before you run a backup.
  
  
• Both Script Dump & Load and Mapper's Export & Import can be run by command-line executables. See the ADT Server User Guide for more information about the ipdump, ipload and dtexport, dtimport commands.

Environment

Release:
Component: DWM