• Before I get started, what should I know about Database objects?
• Before I get started, what should I know about Database Access?
• Where do I get the ODBC database drivers from?
• Before I get started, who should I talk to about Database permissions?
• Will I see only those business elements that I have permissions for?
• What does “metadata” mean?
• What is the difference between a “Search” and a “Query”?
• What types of databases can and cannot I search and query?
• Can I search multiple databases at once?
• How do I get to a query after a search?
• Do I always need to search before I can query?
• How fast can I expect a search to be?
• How can I speed up my search?
• After a search, how do I know if a result is what I’m looking for?
• How can I see information related to my data?
• Can I save the results of a search and a query?
• What types of SQL queries can I perform?
• Can I save SQL and open SQL text?
• How can I see the results of my query?
• How can I view multiple tables and queries at once?
• How can I create a local copy of a database?  

Before I get started, what should I know about Database objects?

Databases are the generic term for a structure of data controlled by a database management system and organized generally (except for desktop databases like ACCESS ) according to the following general-to-specific structure:
 
Server -> Database -> Schema/Owner -> Table/View -> Column
 
Servers are usually equated with a physical computer (mainframe, mini, or workstation) but actually more than one database server may reside on the same computer.  If your company has an e-commerce website using Oracle, the database server may be called “Oracle_Web”.  The server may contain two databases: a production database and a testing database (where changes are tested out before rolling out).  So inside of the Oracle_Webserver is a “Oracle_Prod” database and an “Oracle_Test” database.  Now if your company, say, has two major subsidiaries (e.g. Commodities and Credit), each of these may have their own schema inside of the Oracle databases,  “Commodities” and “Credit” (schemas are also sometimes called “owners” or “creators”).  Each of these schemas may have their own “Customer” and “Account” tables or other business tables that contain columns such as “Name”, “Address” or “Balance”.  MetaTrieve will search all servers, databases, schemas, tables and columns as determined by the options on the administration and search pages.  MetaTrieve will also search views (which are defined subsets or joins of tables) which can have their own unique column names that may match the business semantic searched for even if the source table/column does not.
                                             
Before I get started, what should I know about Database Access?

MetaTrieve is an enterprise tool; this means that to use the search and query features on any but desktop databases requires the drivers, setup and configuration of the network databases in the company.  You will need the following to use MetaTrieve on remote databases:
 
1.  Network and domain access to the database server desired.  The network administrator can provide this if the server does not show in “Network Neighborhood” of Windows Explorer.

2.  Database ODBC drivers for the database vendors (providers) used in the tool. 

3.  ODBC (Open Database Connectivity) setup.  You will need to create a DSN (Data Source Name) for each database server you wish to use in MetaTrieve (if not already on your system).  For Windows, this is usually a System DSN and for Windows XP, a User DSN.  (For ACCESS, this is always a User DSN and is probably already present--you will only need to assign a default database on the configuration page.)  To configure a DSN, you will need the server name, Userid and password (and for some databases, a database name--some default).  Your administrator can supply you with these items.
 
Where do I get the ODBC database drivers from?
 
While the drivers for Microsoft SQL Server and other databases may already be on your workstation from your install of Windows, Excel, Access or Word, other vendor drivers (IBM’s DB2, Oracle, Sybase, etc.) can be downloaded from the provider websites such as www.microsoft.com,www.oracle.com, www.sybase.com, www.MySQL.com and www.ibm.com.  Third-party vendors such as StarQuest also provide database drivers.  You can view your database drivers on your computer via the “Drivers” tab on the ODBC panel off of the Windows Control Panel or the “Administrative Tools” in Windows XP.
 
Before I get started, who should I talk to about Database permissions?
 
You should talk to your database administrator about database permissions.  You will need to acquire a Userid and a Password from the database administrator for each database you need to search and/or query.  Database permissions generally involve read (query), insert, update and delete permissions for database schemas and tables.  While MetaTrieve itself will allow you to execute queries that involve all of these, the database security system may prevent you from any of these activities.
 
Will I see only those business items that I have permissions for?
 
You will be able to search database metadata and view the results (database names, table names, column names and column comments/remarks) but you will not be able to see entire table descriptions, sample data or queried data without read permissions.
 
What does “metadata” mean?
 
Metadata is a generic term for “data about data” or “descriptions of data.”  It is primarily the description of the business category of the data:  “Joe Williams” is a customer name, “Chicago” is a city name, Z12E34536 is a purchase order number, etc.  This category definition in databases in normally contained in the labels of tables and columns, sometimes divided between the two (the table may be labeled “Customer” and the column “Name” or “FirstNm” and “LstNm”, for example).
 
Finding these categories of data is what is normally referred to as “locating the data” in order to query or process.  “Discovery” of data usually means determining what categories of data are in a database or databases and in what database, table, and column the category is defined.
 
In web documents and XML, the metadata descriptions or categories (if present at all) are wrapped around the data in the same document and you can search on a city name (e.g. Atlanta) or on the term “city name” and hope to get relevant information back; in databases, you generally cannot get to the actual data (“Atlanta”) without first locating the metadata or placeholder category (“city name”) that pertains to the information desired.  Then you can query or report on the data (“Where are all my company stores in Atlanta?”).  
 
What is the difference between a “Search” and a “Query”?
 
A “Search” is a scan of database metadata for tables and columns that may match your business search term.  It finds the “location” (database name, table name and column name) of the data.  A “Query” is an SQL request for the actual data from a database, table(s) and column(s) that was located with the metadata search.  An example of a MetaTrieve search is simply a business term “product price” entered that returns, say, Database: eCommerce; Table: “Catalog”; Column: “Prod_Price”.  An example of a query after the search has found the database objects is: “Select Product_Cd, Prod_Price from Catalog where Prod_Price > 129.00”.  First you search, then you query.          
 
What types of databases can and cannot I search and query?
 
You can search and query any ODBC compatible relational database (e.g. SQL Server, DB2/UDB, Oracle, MySQL, Sybase, etc.).  You cannot search and query legacy databases such as IBM’s IMS and VSAM, object-oriented databases such as POET, or XML databases (although MetaTrieve will process XML data types stored in a relational database as tables and columns).
 
Can I search multiple databases at once?
 
Yes, you can search up to four distinct databases with one search execution.  For example, with one search, you can scan your company’s mainframe DB2 database, data warehouse SQL Server database, eCommerce Oracle database and a shared-drive ACCESS database.   Once your database are setup in ODBC and on the administration page, you can simply click on all of these on the search page to scan your enterprise.
 
How do I get to a query after a search?
 
You simply click on a search result row and this takes you to the full table display with a template query which you can modify.
 
Do I always need to search before I can query?
 
No, you can go directly to a query by selecting the “Query” menu option, or by clicking on “Search” without entering a search term.
 
How fast can I expect a search to be?
 
The response time varies depending on the desktop processor and memory, the size and type of the company databases, database access contention, and if synonym matching is desired.  For example, on a Dell P4 with 256 RAM a search against a typical CRM or data warehouse SQL Server database with over 2000 business columns takes about 1-3 seconds; against a large mainframe operational DB2 database with over 30,000 business columns, the response time may run 4-10 seconds.
 
How can I speed up my search?
 
Generally, large databases have many schemas/owners, only one or two of which actually contain the production business information.  Your database administrator may provide you with this schema name and you can select it on the administration page.  This will speed up the search as will not selecting synonym matching (on the admin page) unless needed.
 
After a search, how do I know if a result is what I’m looking for?
 
While MetaTrieve calculates a confidence value for each candidate match, only the human eye can truly discern the meaning of a metadata term.  Often, you will need to display the entire table (by clicking on the match result row) or query the data to get a complete picture of the context and meaning of a database element.
 
How can I see information related to my data?
 
Often, the search itself will display data that is related semantically to the business item(s) you are seeking.  However, MetaTrieve also displays explicitly related tables and columns Foreign Keys) that have been identified as such by the database designer.  You can view these related elements via “Related Data” button on the full table/query page.  Once on the “Related Data” page, you can continue to follow a chain of relation by selecting a desired row (this will bring up the related table which, in turn, may have other related data.
 
Can I save the results of a search and a query?
 
Yes, you can save the results sheet as test or as a spreadsheet and open it later for further querying.  You can also save the data results of a query as text or Excel sheet or Microsoft Access table (if displayed in Excel or Access).

What types of SQL queries can I perform?
 
You can perform any ANSI relational queries (including joins, unions, sub-selects, updates, inserts, deletes, etc.) that you have database permissions for.  For specific vendor databases, you can also perform those unique SQL statements.

Can I save SQL and open SQL text?
 
Yes, you can save SQL into a folder and open it later for querying.  You can also create generic queries by entering “?” prompts and MetaTrieve will request you to replace these prompts at run time.

How can I see the results of my query?
 
You can see the results of your query in a MetaTrieve window, in Microsoft Excel or Microsoft Access.  If you expect the results to return a large volume of data, Access will hold the largest result set.  MetaTrieve has a 32,000 row limit, Excel 75,000 and Access is effectively unlimited.

How can I view multiple tables and queries at once?
 
You can view multiple tables by clicking on multiple search result rows or by selecting different tables on the “Select Table” window (from the “Query” menu option) or by selecting tables from “Related Data” display rows.
 
How can I create a local copy of a database? 
           
If you wish to create a Microsoft Access copy of a database table or joined table in Access, first you need to create a query on the MetaTrieve query page and display the results in Access.  This will display as a pass-through query where you can convert it to a table.  Follow the Access instructions for converting a pass-through query to a table as outlined in the Help documentation for Access (type in “pass-through query” in the indexsearch).