On December 12th, I’ll be presenting on the DB2 Night Show on HADR and TSAMP Advanced Topics. Sign up here: http://www.dbisoftware.com/db2nightshow/

DB2 Basics: Aliases

My blog entries covering the DB2 Basics continue to be my most popular blog entries. This is probably because they appeal to a wider audience – even non-DBAs are interested in them and I continue to rank highly in Google search results. My blog entry on how to catalog a DB2 database gets a ridiculous 50 page views per day, which is more than my whole blog got per day for the first year I was blogging. I am also amazed that I can still come up with topics in this series – it seems like there are only so many things that count as “basics”. But there are a lot of different things in DB2 and some can be covered at a simple level.

The other day ago, I had to look in depth at some table aliases, and knew I had made a mistake with one in another environment, so was checking what table each alias pointed to, and it occurred to me that it wasn’t something I had covered in the basics series. Both table and database aliases are discussed in this blog entry.

Table Aliases

What Table Aliases Are

Table aliases are essentially a different name that you give to an existing table within the database. They allow you to call the table by a different name without having a separate object.

An alias can be used in regular SQL, but you cannot use an alias for things like ALTER TABLE or DESCRIBE TABLE statements.

The table that an alias points to is called the base table for the alias.

Use Cases for Table Aliases

The most frequent place that I use table aliases is to allow a user to query things without specifying a schema. While one of my pet peeves is an application that does not specify a schema in it’s queries, WebSphere Commerce is just such an application. Since the majority of the databases I support are WebSphere Commerce databases, I have to deal with developers and others wanting to submit queries without specifying the schema name. This quickly gets unreasonable when there are many developers on a project.

There are some custom tables in my WebSphere Commerce databases that I know are not related to the base WebSphere Commerce functionality, but are related to integration points such as AEM or custom data load processes. I like to put these tables in separate schemas so I know in the future that they are indeed separate in some way – this is useful for upgrades of WebSphere Commerce. But the developers still want to be able to refer to them without specifying a schema name. So I place them in a separate schema (and sometimes a separate tablespace too), and create an alias for them in the schema that WebSphere Commerce uses.

Aliases can also be used to control table access or allow you to present different tables to the user with the same name – but not with as much power as views allow. For example, if you had two tables – FOO.BAR_V2 and FOO.BAR_V3, you could have an alias called FOO.BAR that pointed to FOO.BAR_V2, and when an upgrade required a change in the table, you could point your FOO.BAR alias at FOO.BAR_V3. This might result in less down time during upgrades, when done in conjunction with other strategies.

Aliases do not have independent permissions from their base tables, so they cannot be used like views can to restrict the permissions differently from the base table.

An alias in DB2 for z/OS can mean something different than LUW. In DB2 for z/OS, an alias can refer to what we in LUW call a nickname – a table on a remote server accessed using federation.

Creating Table Aliases

Table aliases are easy to create. An example of the basic syntax is below:

db2 "CREATE ALIAS WSCOMUSR.X_SALES_CATEGORIES FOR TABLE AEM.X_SALES_CATEGORIES"
DB20000I  The SQL command completed successfully.

Table aliases can be created for objects that do not exist – a warning is returned, but not an error. Table aliases are also NOT dropped when the base table they point to is dropped. This can be surprising behavior the first time you encounter it.

If the schema specified does not exist, DB2 will attempt to create the schema. If the user doesn’t have implicit schema permissions at the database level, they will not be able to create the schema and the CREATE ALIAS command will fail.

Investigating Table Aliases in a Database

It is very possible to make a mistake when creating an alias. DB2 will not care if you copy and paste the wrong table name, and that can be a bit frustrating for developers if it’s an alias they use. Below is an example of exploring the mapping of a base table to an alias.

select  type,
        substr(tabschema,1,12) as tabschema, 
        substr(tabname,1,30) as tabname, 
        substr(base_tabschema,1,12) as base_tabschema, 
        substr(base_tabname,1,30) as base_tabname 
    from syscat.tables 
    where tabname like 'X_CAT%'  
    with ur
TYPE TABSCHEMA    TABNAME                        BASE_TABSCHEMA BASE_TABNAME
---- ------------ ------------------------------ -------------- ------------------------------
T    AEM          X_CATEGORIES_PRODUCTS          -              -
A    WCR101       X_CATEGORY_NAME                WSCOMUSR       X_CATEGORY_NAME
A    WSCOMUSR     X_CATEGORIES_PRODUCTS          AEM            X_CATEGORIES_PRODUCTS
T    WSCOMUSR     X_CATEGORY_NAME                -              -

  4 record(s) selected.

Notice the TYPE in the query output above. Information about aliases and tables (and views) is stored in the same system catalog view, and this column tells us whether each row is for a table or a view. Note that tables will not have values for BASE_TABSCHEMA or BASE_TABNAME. Those fields will only be populated for aliases.

Dropping Table Aliases

You can drop table aliases independently of the tables they refer to. In fact you must do so if you want to get rid of the aliases, because dropping a table does not remove the aliases that refer to it. The key is to ensure that you use the ALIAS keyword – otherwise you may inadvertently drop the base table that the alias refers to. An example of the syntax for dropping an alias is below.

db2 drop alias WCR101.X_CATEGORY_NAME
DB20000I  The SQL command completed successfully.

Database Aliases

What Database Aliases Are

Database aliases are different from table aliases. Database aliases are defined using the CATALOG DATABASE command and stored in the database directory. They can be created for local or remote databases.

Use Cases for Database Aliases

Similar to some of the use cases for table aliases, you can use a database alias to swap connectivity from one database to another or even to prevent access to a database. For example, if I have a database named SAMPLE, I can catalog it as SAMP_USR – and then only have users connecting in to SAMP_USR. If I then want to prevent users from connecting, I can uncatalog the alias SAMP_USR, and the users who were connecting in to SAMP_USR will not be able to connect, but I or anyone connecting into SAMPLE will be able to.

I have seen aliases used to simplify connections for apps over multiple similar environments (they all use the same database name for different databases on different servers.

Database aliases are sometimes used as friendly names for databases – but aliases still must be 8 characters or fewer, just like database names.

Database aliases can be defined either at the DB2 server or at the DB2 client.

Creating Database Aliases

Database aliases are created using the catalog database command. Technically, there is an alias created on database creation that is identical to the database name. You can see this in the database directory:

db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /db_data
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

To explicitly create an alias for an existing database, use the CATALOG DATABASE command:

db2 catalog database sample as samp_usr
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

To see the results of this, list the db directory:

db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = SAMP_USR
 Database name                        = SAMPLE
 Local database directory             = /db_data
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /db_data
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Though the above is for a local database, you can also perform the same for a remote database.

All investigation of aliases can be done through the database directory.

If you have multiple aliases for a database, be cautious with any scripts that parse your database directory – you can end up doing something for the same database twice. For example, if you grepped the db directory from above on “Database name”, you would get two entries:

db2 list db directory |grep "Database name"
 Database name                        = SAMPLE
 Database name                        = SAMPLE

You get the same database twice. If you are scripting, use some method of making sure you have a unique value to avoid processing the same database twice.

Dropping Database Aliases

Much like table aliases, database aliases are not removed if you drop the database.

db2 drop db sample
DB20000I  The DROP DATABASE command completed successfully.
db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMP_USR
 Database name                        = SAMPLE
 Local database directory             = /db_data
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database aliases are removed with the uncatalog command:

db2 uncatalog db SAMP_USR
DB20000I  The UNCATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

Other Aliases

Aliases can also be created for modules and sequences, in a similar manner to table aliases.

1 comment… add one

  • Pretty much elaborate document on simple concept Alias. :-)
    Thank you.

    I just want to add one point which should be considered while dropping alias, that make sure you are using the word ‘alias’ but not ‘table’. Earlier days when I was learning DB2 ; I was playing with aliases & found that if you give syntax as shown below the base table will be dropped instead of alias even though the information you have to db2 is related to alias.
    db2 drop table .;

    Frankly, speaking I was expecting an error saying that table doesn’t exist.

    Reply

Leave a Comment