In a typical relational database, tables are joined together via foreign key relationships. It is also common for macros and procedures to reference tables, forming dependent relationships. When making administrative decisions about database objects, it is important to know about its object relationships. For example, copying or migrating a table or stored procedure from one database to another requires knowing what other tables the object is dependent on. These other tables must also be copied to maintain its integrity. It is also important when deleting an object from the database that you know what other objects (tables, macros, stored procedures) are referencing that object. The latest version of Teradata Plug-in for Eclipse provides a Dependency Browser that can discover object relationships

Prior to Teradata Plug-in for Eclipse 13.0, the user could only view database objects as they appear in the Data Source Explorer tree or Teradata View. The Data Source Explore simply displays the hierarchical relationships, such as schemas contain tables and tables have columns, or schemas contain views and views have columns. But it does not show that views reference tables that are joined by columns. Or the Teradata View displays the detailed data about the object, such as its list of columns, indexes, or constraints. But you would have to decipher the SQL to determine which tables a view references.

With Teradata Plug-in for Eclipse 13.0, the user can select an object from the Data Source Explorer and display its object relationships. Such relationships include what objects a particular object is referenced by and what objects a particular object references. The Dependency Browser uses Teradata Meta Data Services to find the dependent objects for a given object in the database. Then, through the Interdependency Browser user interface, displays a graphical representation of the object relationships. Refer to the section below, “What is Teradata Meta Data Services”, for more details on setting up Teradata Meta Data Services.

Show Interdependencies

To invoke the Interdependency Browser, open the Data Source Explorer, right click on the object you wish to browse, and select the Show Interdependencies menu option.

Below is a screen capture of the Interdependency Browser for the schema, “productA”. The diagram shows the schema object for “productA” highlighted, with its related tables, views, macros, and stored procedures.

The diagram also shows the following relationships:

  • Table 'Shipment' and its foreign key relationship to table 'Customer'
  • Macro 'ListCustomersRegion' and its dependent table 'Customer'
  • Stored procedure 'CostOverValue' and its dependent view 'CustomerShipments'
  • View 'CustomerShipments' and its join relationship to table 'Customer' and table 'Shipment'

 The Interdependency Browser further lets you customize the display by selecting the type of objects to show.

NOTE: You must have Teradata Meta Data Services configured for your database schema or you will get the following message when you select the “Show Interdependencies” option. Refer to the section below, “What is Teradata Meta Data Services”, for more details on setting up Teradata Meta Data Services.

Find Objects

The Teradata Plug-in for Eclipse also provides an interface to search for database objects given a matching text value. This feature uses the Dependency Browser to locate objects based on name searches. This feature is extremely useful in locating objects by name or finding unnamed objects, such as anonymous table constraints. Through the Find Objects Dialog, the user can enter the text value, a search pattern (such as starts with, ends with, or contains), and select what object types to search for. The results are displayed, grouping the objects by object type.

To invoke the Find Objects Dialog, right click on a database, schema,  or object folder in the Data Source Explorer and select the Find Objects option from the Teradata menu.

Below is a screen capture of the Find Objects Dialog, selecting the schemas, ‘ide’, ‘productA’, and ‘autoide’, to search and a search value that starts with the text ‘cust' across all object types.

Below is a screen capture of the results from the above search.

The results are arranged by object type. The user can double click on an object to see a more detailed display of the object in the Teradata View or compare two objects of the same type by selecting the objects from the Find Results window and clicking the ‘Compare Objects’ button .

Compare Objects

The following screen capture shows the object comparison dialog for the Customer tables from schemas ‘ide’ and ‘productA’.

What is Teradata Meta Data Services?

Teradata Meta Data Services (MDS) provides an infrastructure for managing Teradata Warehouse metadata. MDS includes a repository database where metadata is stored in Teradata tables, as well as two predefined Application Information Metamodels (AIMs) and administrative and support tools for managing metadata. Application Information Metamodel defines how the metadata is stored in the repository. It consists of classes, properties, and relationships that refer to metadata objects. The MDS metadata contains information on the database objects, such as properties and relationships or associations between objects. MDS is part of the Teradata Tools and Utilities product group. Refer to the Teradata Meta Data Services Installation and Administration Guide for more information on installing and administering Teradata MDS.

Teradata Meta Data Services also provides a web service interface, MetaJavaWS, for remote calls to MDS APIs. This web service interface is used by the Dependency Browser, mentioned above, to access MDS. Installation of MDS should also include setting up the MDS web service. This is described in the Teradata Plugin for Eclipse - Release Definition, Installation chapter, which is available on the Teradata Download Center’s Teradata Plug-in for Eclipse download page.

Once MDS and the MetaJavaWS web service are configured and your database has been loaded into the MDS repository, you will need to configure the Teradata Dependencies Preferences within the Eclipse environment. This allows the Dependency Browser to communicate with the MDS web service. Here are the steps for setting the Teradata Dependencies Preferences:

  • Go to the Eclipse toolbar and select Window>Preferences
  • Open the Teradata Datatools Preferences>Dependencies Preferences page
  • Enter the name of the Meta data Services Web Service system
  • Enter the port number for the web service
  • Enter the user credentials to access Meta Data Services
  • Click the 'Test User' button to verify that the information was entered correctly and the web service is running. If successful, a pop-up dialog appears displaying the list of Teradata Database systems managed by Meta Data Services.

If your system is not in the list, go to the Teradata Meta Data Services MetaManager application and use the Database Administration utility to load the system into the Meta Data repository.

Again, refer to the Teradata Plug-in for Eclipse - Release Definition, Installation - Teradata Meta Data Services Web Service Client Support section for more information.

 

For further information on installing the Teradata Plug-in for Eclipse, refer to the “Getting Started with Teradata Plug-in for Eclipse” article, also under the Tools category.