Importing Data

SQL Error Messages

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:

Display of the DDL generated in HCO Log tab of the IDE
Automatically defaults to connecting to the database specified in your Mainframe Express project

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:

Menu bar
Toolbar
Status line
Drop-down list of available databases to connect to
List box of tables for which aliases do not exist

13.3 The Toolbar

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

Button	Function
	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).

Option	Function
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.

Option	Function
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:

Select the database to connect to from the dropdown list
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:

Connect to a database
Select table(s) from the list box. To select all tables set Select all on the Options menu.
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
Either select the filename from the list box or specify the filename where you want to save DDL
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:

Select the database to which you want to connect from the dropdown list
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
Specify the Logon ID and optionally Table Name to get a list of tables without aliases
Click Restrict
The list box is updated with all tables found that meet that qualification. You are informed if no tables are found.
Select table(s) from the list box or to select all tables, set Select all on the Options menu
Click File or select Generate DDL on the Database menu.
The Save DDL window (see Figure 13-2) window then appears.
Either select from the list or specify the filename where you want to save DDL
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:

table_name.column_name

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:

EXEC SQL
  SELECT STAFF.ID, STAFF.NAME
  FROM STAFF
END-EXEC

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:

EXEC SQL
  SELECT STAFF.ID, STAFF.NAME
  FROM DEMO.STAFF
END-EXEC

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:

EXEC SQL
  SELECT DEMO.STAFF.ID, DEMO.STAFF.NAME
  FROM DEMO.STAFF
END-EXEC

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:

CREATE ALIAS USERID.STAFF FOR DEMO.STAFF;

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

EXEC SQL
  SELECT USERID.STAFF.ID, USERID.STAFF.NAME
  FROM USERID.STAFF
END-EXEC

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.

Importing Data

SQL Error Messages