MDBitz Development Resources

Ever find yourself working in an unfamiliar Database or table and in need of knowing what are the data types for the different columns? Well that recently happened to me when I had to expand some queries in a DB2 environment.

As it turns out there exists the Describe command that outputs the columns of a table and their data type. The basic command is as follows that can be used on a TABLE or VIEW:

DESCRIBE TABLE schema.table_name

The command will output the following information for each column.

• Column Name
• Type Schema
• Type Name
• Length
• Scale(precision)
• Nulls(is nullable)

In addition to obtaining the column information you can also use the command to get a table’s indexes.

DESCRIBE INDEXES FOR TABLE schema.table_name

Resources

• DB2 Universal Database – Describe Command

While continuing my exploration into the world of DB2 databases, I found myself confused as to why my basic SELECT … WHERE … statement were failing when I tried to do AND/OR clauses. After a little fumbling around I found the solution… parentheses.

Parenthesis? you ask, yes. Simply put when you want to write a compound WHERE clause using AND and/or OR statements you need to wrap each clause in parentheses. To illustrate lets start with a basic SQL select query and then we’ll show how it needs to be written for DB2

SQL Statement

SELECT first_name, last_name, id 
FROM APP_DB..APP_USERS 
WHERE active=1 
    AND  is_admin=1

DB2 SQL Statement

SELECT first_name, last_name, id
FROM APP_SCHEMA.APP_USERS
WHERE ( active = 1 ) 
    AND  ( is_admin = 1 );

As you can see for the DB2 query to function properly it needs the parentheses around the clauses while most other SQL languages don’t. Hopefully this little tidbit will save others the half hour I spent fumbling around with this little subtle syntax difference.

Back from an extended vacation and I find myself in the middle of a project needing to export data from a DB2 database. Fortunately I have decent knowledge of SQL and the basic syntax matches up but then I found myself wanting to limit the results returned. Now in T-SQL and MySQL I often would use the, LIMIT, TOP or SET ROWCOUNT commands but they aren’t available in DB2. Instead you use the FETCH FIRST command.

To put things simply to limit the number of rows returned the command is:

FETCH FIRST 10 ROWS ONLY

What this means is that your basic SELECT statement would be entered as:

SELECT * FROM mySchema.myTable WHERE TRAN_TS >= '05/01/2010' FETCH FIRST 10 ROWS ONLY