Posted by 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.
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.
Hi Amber, Can the ALIAS be dropped from with in the SP like other objects.
CREATE PROCEDURE SP_DROP_ALIAS()
begin atomic
if(exists(select 1 from syscat.tables where tabschema = ‘MYSCHEMA’ and tabname = ‘MYTABLE’)) then
DROP ALIAS MYSCHEMA.MYTABLE;
end if;
End
Hi Ember – your posts have been an invaluable source of guidance, and I would like to thank you for maintaining this site with diligence.
Just noticed something regarding the creation of an alias for a database through the CATALOG DATABASE command. My local database was residing on a different path than the instance home directory(/home/db2inst1 in my case) and when I just created the alias – for example : db2 catalog database SAMPLE as LSAMPLE, and then refreshed the directory – the connection to the alias failed on account of it not being found on the database directory. Thereafter, I re-created the alias along with the ‘ON’ keyword in the CATALOG DATABASE statement – specifying the same database path as the local copy. It then worked. So, I guess if a database has been created locally on a different path than the instance home directory, we need to specify the same path while creating the database alias as well.