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

Like most individuals I find the built int SUM function of SQL very helpful when performing computations. Recently however I found myself wondering how to compute the product of a result set grouped by some factor, and I found myself slightly at a lost as no Product function exists.

Fortunately for us we can use some basic Math to do our aggregate multiplication. As you may or may not remember we can Log and AntiLog to perform multiplication.

X * Y * Z = ANTILOG( LOG( X ) + LOG( Y ) + LOG( Z ) )

Looking closely at the above formula you can see that these functions all exist in most Database Servers and in T-SQL specifically you can rewrite the formula as :

SELECT EXP ( SUM ( LOG (myColumn) ) ) FROM myTable

If you wanted to get even fancier and get totals based on some criteria then you could add in GROUP BY and WHERE clauses to your queries. For example lets say you are doing some probability calculations and want to have the product of some percentages then you could do something like:

SELECT EXP(SUM(LOG(r.MEAN)))
FROM RESOURCES r
WHERE r.MODEL_ID = "TEMP"
GROUP BY r.GROUP_ID
ORDER BY r.GROUP_ID

Also it is worth wild to mention this only works for positive values. If you want to have the product of values that could potentially be negative you will have to keep track of the number of negative values and then set the result as necessary.

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

In most instances you will be using a User Interface of one sort or another to execute Transact-SQL commands against your Sybase Database Servers. In rare occasions however I find that I execute commands through the dos prompt via the Interactive SQL Utility or iSQL as it is more commonly referred.

Opening a Connection

BCP and iSQL both use the same parameter flags when specifying the database server and user credentials for connecting to a server.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)

Using these variables you would connect to the Sybase Database Server with the following command.

isql -S server -U userName -P password

Issuing Transact-SQL Commands

A successful connection will present you with the > carat letting you know you can now enter your T-SQL commands. To do so you simply type the command line by line and when finished type the GO command to issue the SQL.

> USE db2
> GO
> SELECT *
> FROM books b
> WHERE b.pub_date >= '09/01/2009'
> GO

Understanding the output from iSQL

iSQL will output the results of the command to standard output. However you have the option of formatting the output to your own preferences.

• Headers Flag -h to modify the number of lines between the column headers and the data rows use the -h flag followed by the number of desired lines.
• Column Seperator -s by default a space is used to separate the columns in a row you can use the -s flag to change this to your desired delimiter.
• Column Width -w by default the output is limited to 80 characters per line use the -w flag to modify this to the length you want.

Interactive SQL is a useful tool when you need to quickly execute commands against your database. For that reason alone it is worth knowing about the utility, but in most cases you will find yourself using other UI based applications to issue commands against your Sybase Server.

References

• Sybase Online Manual – Using the iSQL Utility