DB2 LUW – Handling database objects with mixed case and special characters in their names

Handling tables (or other database objects) that have mixed case names and/or with special character(s) in their names needs special effort. In this blog post, we will look at our options; essentially, what works and what does not work.

The Basics and the defaults:

By default, DB2 is case-insensitive. That means by default, the table s1.t1 and S1.T1 are the same. Let us say we create a table s1.t1 and query table S1.T1 or s1.t1 or s1.T1 or S1.t1, we are essentially running the query against the same DB2 table because DB2 stores object’s metadata (name etc.) in upper case unless the object was intentionally created in lower-case. That means, results should be the same in all the cases.
Here is an example:

db2 "create table s1.t1 (col1 integer, col2 char(1))"
DB20000I  The SQL command completed successfully.
db2 "insert into s1.t1 values (10,'Y')"
DB20000I  The SQL command completed successfully.
$db2 "select * from s1.t1 with ur"
COL1        COL2
----------- ----
         10 Y
  1 record(s) selected.
$db2 "select * from s1.T1 with ur"
COL1        COL2
----------- ----
         10 Y
  1 record(s) selected.


As DB2 stores object’s metadata in upper case (unless the object was intentionally created in lower-case / mixed-case), attention needs to be paid when querying DB2’s catalogs for character/string data.
For example, if we want to know the latest runstats time for the above created table, the query would look like below. Note how we are searching for ‘S1’ / ‘T1’ (upper case) instead of ‘s1’ / ‘t1’ (lower case).

$db2 “select char(tabschema,20), char(tabname, 20), 
type from syscat.tables where tabschema = ‘S1’ and tabname = ‘T1’ with ur";
1                    2         STATS_TIME
-------------------- ----------------------------- 
S1             T1            2015-08-18-23.00.30.372461
  1 record(s) selected. 


So, how are tables with lower/mixed case in their names created? More importantly, how are they handled? How about tables with special characters in their names?
Let us look at them with few examples.

Tables with lower/mixed case in their names:

In applications like SAP, it is common to have database objects with lower/mixed case in their names. Writing queries against table names with mixed case and/or special characters requires handling such table names with care.
First to create a table with ‘mixed case’, the table name needs to be enclosed in double quotes.
Here is an example.

$ cat table1
create table DB2INST1."t1_iQxt"(col1 integer, col2 char(1));
$ db2 -tvf table1 | tee table1.log
create table DB2INST1."t1_iQxt"(col1 integer, col2 char(1))
DB20000I  The SQL command completed successfully.

In the above example, we created the table DB2INST1.t1_iQxt by embedding the ‘create table’ statement in an SQL file and then running it. How to create such a table with mixed-case statement at the command prompt? Just take sufficient care to ‘escape’ double quotes.
Here is how a table with mixed-case is created at the command prompt:

$db2 "create table DB2INST1.\"t1_iQxt\" (col1 integer, col2 char(1))"
DB20000I  The SQL command completed successfully.

Now, let us look at how to query this table. We will look at working with queries that are written to a file and also ones that are directly written at the command prompt.

Queries in a file:

$ cat query
select * from DB2INST1.t1_iQxt;
$ db2 -tvf query
select * from DB2INST1.t1_iQxt
SQL0204N  "DB2INST1.T1_IQXT" is an undefined name.  SQLSTATE=42704

Let us now see if we can get this working by enclosing the table name in double-quotes.

$ cat query
select * from DB2INST1."t1_iQxt";
$ db2 -tvf query
select * from DB2INST1."t1_iQxt"
COL1        COL2
----------- ----
  0 record(s) selected.

That worked. So, when the query is in a file, table name with mixed-case needs to be enclosed in double quotes for the query to work as expected.

Querying at the command prompt:

When writing queries at the command prompt, extra care needs to be taken because there is a UNIX SHELL in works as well. This is especially true when dealing with objects with mixed-case and/or special characters. From experience we now know that a simple query against our table would not work.

$db2 "select * from DB2INST1.t1_iQxt"
SQL0204N  "DB2INST1.T1_IQXT" is an undefined name.  SQLSTATE=42704

Note how DB2 is looking for the ‘Upper Case’ version of the table ‘t1_iQxt’ and fails to find such a table. This is expected because DB2 converts (by default) all the object names in a query to upper case unless handled in a special way. We need to tell DB2 NOT to convert the table name to upper case, essentially to keep the table name intact.
Just like we tried Let us now see if enclosing the table name with double-quotes would help.

$db2 "select * from DB2INST1."t1_iQxt""
SQL0204N  "DB2INST1.T1_IQXT" is an undefined name.  SQLSTATE=42704

That did not help.
How about two sets of double-quotes?

$db2 "select * from DB2INST1.""t1_iQxt"""
SQL0204N  "DB2INST1.T1_IQXT" is an undefined name.  SQLSTATE=42704

The above approach did not help either. However, when we prefix double-quotes with a ‘\’, it works. This approach is calling ‘escaping’. By ‘escaping’ a character, we are asking to treat the string as-it-is without being interpreted.

$db2 "select * from DB2INST1.\"t1_iQxt\""
COL1        COL2
----------- ----
  0 record(s) selected.

So, when querying at the command prompt, table name with mixed-case needs to be enclosed in double quotes and then double quotes need to be escaped by a ‘\’.

Tables with special characters in their names:

What if there are special characters in the table name? Most of the time, we do not have this problem. However, I remember working with DB2 database objects with special characters in their names. The database was for an ‘SAP’ application.
When there are special characters, sufficient care needs to be taken to ‘escape’ them as well. Here is an example:

$cat table2
create table DB2INST1."t1_Nyt$#"(col1 integer, col2 char(1));
$db2 -tvf table2 | tee table2.log  
create table DB2INST1."t1_Nyt$#"(col1 integer, col2 char(1))
DB20000I  The SQL command completed successfully.

Let us now try querying this table.

Queries in a file:

$ cat query
select * from DB2INST1."t1_Nyt$#";
$ db2 -tvf query
select * from DB2INST1."t1_Nyt$#"
SQL0204N  "DB2INST1.T1_NYT$#" is an undefined name.  SQLSTATE=42704

Let us now see if we can get this working by enclosing the table name in double-quotes.

$ cat query
select * from DB2INST1."t1_Nyt$#";
$ db2 -tvf query
select * from DB2INST1."t1_Nyt$#"
COL1        COL2
----------- ----
  0 record(s) selected.

That worked. So, when the query is in a file, table name with mixed-case and/or special characters needs to be enclosed in double quotes for the query to work as expected.

Querying tables with mixed-case at the command prompt:

At the command prompt, it gets lot more interesting to work with tables that have special characters in their names. Let us see what works:

$db2 "select * from DB2INST1.t1_Nyt$#"
SQL0204N  "DB2INST1.T1_NYT0" is an undefined name.  SQLSTATE=42704

We will try if escaping quotes with a ‘\’ works.

db2 "select * from DB2INST1.\"t1_Nyt$#\""
SQL0204N  "DB2INST1.t1_Nyt0" is an undefined name.  SQLSTATE=42704

That did not work either. Note how ‘$#’ is being converted into ‘0’ (zero). This is because, In UNIX, $# is a special variable and is equal to the number of arguments supplied to a script. In this case, the shell is interprets $# as 0.
This means, we have to additionally deal with ‘$#”. Let us see if ‘escaping’ ‘$#’ with ‘\’ works.
Here is how to do it:

$db2 "select * from DB2INST1.\"t1_Nyt\$#\""
COL1        COL2
----------- ----
  0 record(s) selected.

Using awk when dealing with multiple tables:

Suppose there are lots of tables whose names have special characters and/or mixed-case and you are required to drop them. It is going to be tiring to handle such table names manually. In such cases, we can use awk to help us out.
Let us look at an example. Suppose you have this list of tables that you want to work on, in a file.

$ cat tables.list
DB2INST1.T1$
DB2INST1.TABLE1
DB2INST1.t1_Nyt$#
DB2INST1.t1_iQxt

The goal is to use awk to enclose double quotes around the table name. In my previous blog post, I discussed an easy way to handle quotes in awk. As discussed in that blog post, we will use awk’s assignment operator. One new option we will find below is awk’s field separator operator ‘-F’. By default, awk’s field separator is ‘space’ or ‘tab’. In our file, our record separator is ‘.’. We will ask awk to treat ‘.’ as a field separator by using ‘-F’ option.

$cat tables.list | awk -v x="\"" -F '.' '{print "drop table "$1"."x$2x";"}' > drop.sql
drop table DB2INST1."T1$";
drop table DB2INST1."TABLE1";
drop table DB2INST1."t1_Nyt$#";
drop table DB2INST1."t1_iQxt";

Next step would be to run the SQL that has drop statements.

$db2 +c -stvf drop.sql | tee drop.sql.log
drop table DB2INST1."T1$"
DB20000I  The SQL command completed successfully.
drop table DB2INST1."TABLE1"
DB20000I  The SQL command completed successfully.
drop table DB2INST1."t1_Nyt$#"
DB20000I  The SQL command completed successfully.
drop table DB2INST1."t1_iQxt"
DB20000I  The SQL command completed successfully.
$db2 commit
DB20000I  The SQL command completed successfully.

In this blog post, we looked at what works and what does not work when handling that have mixed case names and/or with special character(s) in their names. In short, the solution is to enclose table names in double quotes when the SQL is in a file. When dealing at the command prompt, we need a combination of double quotes and an escape character ‘\’. We also looked at a simple example about how to use awk to enclose table names with double quotes to show that awk could be handy tool when working on a large number of tables that have mixed case and/or special characters in their names.

Advertisements

One thought on “DB2 LUW – Handling database objects with mixed case and special characters in their names

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s