PreviousImporting Data SQL Error MessagesNext"

Chapter 13: Generate Alias

The Generate Alias tool is an aid for developing SQL applications that do not qualify table names. DB2 Universal Database automatically uses your network logon id as a schema name in a SQL query if the statement is not qualified.

13.1 What is an Alias?

An alias is an alternate name for a table or view. DB2 Universal Database supports creating aliases. This enables you to use one set of tables but still develop SQL applications that have unqualified table-names. Host Compatibility Option also provides an option in the DB2 ECM which enables you to specify a table qualifier. There are some situations where this function does not work. Using alias definitions solves those problem. See the section Column Names later in this chapter for more details.

The Generate Alias tool provides an easy way to generate DDL statements for all tables for which you do not already have an alias defined.

13.2 Generate Alias tool

The Generate Alias tool includes the following features:

The Generate Alias Tool (see Figure 13-1) window appears when you select HCO > Generate Alias on the IDE Tools menu.

Figure 13-1: Generate Alias Tool Window

The following elements make up the Generate Alias window:

13.3 The Toolbar

The following list shows the toolbar buttons and the functions that they invoke:

Connect to database selected from drop-down list
Enables you to specify the file in which to save DDL statements (see Figure 13-2). Default filename is specified in the configuration file.
Invoke the DDL Processor tool
Display the online HCO User's Guide which is a hypertext file that describes how the Generate Alias tool works.

13.4 The Menu Bar

The menu bar is an alternate way of invoking Generate Alias functions. The menu bar is composed of three menus: Database, Options and Help.

13.4.1 The Database Menu

You can use the Database menu to connect to a database, generate DDL or to exit the tool. You can display this menu by right-clicking with the mouse anywhere in the Generate Alias window (see Figure 13-1).

Generate DDL Specify a file in which to save DDL statements. The default filename is specified in the configuration file.
Connect Select a database to connect to
Restrict list Restrict tables included in list box when connecting to a database to a different logon id. See Generating DDL for Other Logon Ids for more details.
Disconnect Disconnect from the database you are currently connected to
Exit Exit Generate Alias tool.

13.4.2 The Options Menu

You can override options set in the configuration file by selecting the Options menu. These options are pre-defined. See the chapter Setting INI Options for more details. The settings depend on your current environment and what you have set as defaults.

Display DDL Display the file after DDL statements have been generated
Select all Select all the tables displayed in the list box

13.5 Connecting to Database

The Generate Alias tool automatically connects to the database specified in the Mainframe Express project unless you have disabled this feature in the configuration file. You can still connect to other databases by doing the following:

  1. Select the database to connect to from the drop­down list

  2. Click Connect or select Connect on the Database menu

You see a list of tables for which to generate aliases if the connection were successful (see Figure 13-1). The list is built using your network logon id.

13.6 Generating DDL Statements

To generate DDL statements, do the following:

  1. Connect to a database

  2. Select table(s) from the list box. To select all tables set Select all on the Options menu.

  3. Click File or select Generate DDL on the Database menu.

    The Save DDL To window then appears. (See Figure 13-2.)

    Figure 13-2: Save DDL To Window

  4. Either select the filename from the list box or specify the filename where you want to save DDL

  5. Click SAVE.

    The DDL statements are then generated for each table selected.

Note: If the file already exists, the DDL statements generated are appended to the existing file.

13.6.1 Generating DDL for Other Logon Ids

You can generate DDL statements for logon ids other than your own. To do this:

  1. Select the database to which you want to connect from the drop­down list

  2. Select Restrict list on the Database menu

    The Restrict Tables in List window then appears (see Figure 13-3).

    Figure 13-3: Restrict Tables in List Window

  3. Specify the Logon ID and optionally Table Name to get a list of tables without aliases

  4. Click Restrict

    The list box is updated with all tables found that meet that qualification. You are informed if no tables are found.

  5. Select table(s) from the list box or to select all tables, set Select all on the Options menu

  6. Click File or select Generate DDL on the Database menu.

    The Save DDL window (see Figure 13-2) window then appears.

  7. Either select from the list or specify the filename where you want to save DDL

  8. Click SAVE.

    The DDL statements are then generated for each table selected.

13.6.2 List Box Limits

The list box has a limit of 200 tables. If there are more than 200 tables for which tables exist without aliases, you need to generate the DDL statements, switch to DDL Processor tool and process the DDL statements. You can then return to Generate Alias tool and connect to database again. The list box does not include those tables that have aliases.

13.7 Column Names

Some organizations and code generators produce SQL statements with a column name format of:


If you create aliases for tables (as opposed to using the DB2 ECM directive QUALIFIER), DB2 Universal Database automatically appends your logon id to each column-name. For example, the following SQL statement selects data from table DEMO.STAFF:


If you use the DB2 ECM directive DB2(QUALIFIER=DEMO), and are not using DB2 Universal Database Version 5.2 or later, only the table reference is changed:


This would result in compiler errors indicating that the column could not be found in the table reference. For QUALIFIER to work properly in this situation, every column name referenced would have to be changed to add the schema name provided in QUALIFIER:


This task is beyond the DB2 ECM 's capabilities.

However, a more workable solution is to use aliases. The DB2 ECM does not have to change any code. DB2 Universal Database resolves all column names correctly with minimal extra effort. If your logon id were USERID, the following SQL statement would create an alias for the previously mentioned table:


This would cause the SQL statement to be treated as if this were coded:


The alias then causes DB2 Universal Database to resolve the references correctly by replacing all USERID's in the statement by DEMO.

If your organization develops SQL code using this coding format, the Generate Alias tool aids your development efforts using DB2 Universal Database by giving you a tool to generate CREATE ALIAS statements for your programmers.

Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.

PreviousImporting Data SQL Error MessagesNext"