If you had to work with IBM products, you probably have had to work with DB2. This page will get you up and running with DB2 in 10 minutes.

DB2 Reference

What	How
comment	/* */
select	select co1, co2 from tbl where fld = 3;
Insert (values)	insert into tbl (co1, co2) values (1, 'one');
Delete	delete from tbl where co1 = 1;

Comments

Comments are text written by developers to leave an explanation in the code. Comments are ignored by the database. DB2 supports two types of comments:

/* */
select * from employee; -- this is a comment
select * from employer; /* another comment */

Both SQL commands run with an error. Note that /* and */ have to end on the same line. They cannot span multiple lines.

Select

A select statement fetches data from database tables and stores the results in a table called resultset. Your SQL tool displays the contents of the resultset. Following are some sample select statements:

Select all rows

select * from mytable;

Select specific columns

select id, name from mytable;

Select data satisfying a condition

select id, name
from mytable
where year > 2003;

Select data from multiple tables

select a.id, a.name, b.pf
from mytable a, myprofession b;

Renaming resultset columns

select a.id, a.name, b.pf as profession
from mytable a, myprofession b;

Limiting results

select * from mytable
fetch first 10 rows only;

Limiting query results in DB2

DB2 provides the FETCH FIRST command to limit query results.

select * from mytable fetch first 10 rows only;

To get the first 10 by column

select * from mytable order by id fetch first 10 rows only;

To get the last 10 by column

select * from mytable order by id desc fetch first 10 rows only;

DB2 Insert

DB2 supports the standard SQL syntax to insert rows:

insert into mytable (name, phone) 
values ('nick', '123-456-7890');

If my table has an oid, the command above would auto increment the oid.

You can also insert a value from select as follows:

insert into phonebook(alphabet, phone) 
values ('A', '(select phone from mytable where name = 'nick')');

The value of the field phone is populated from the select.

Escaping single quotes in DB2

Unlike most programming languages and database management systems, DB2 does not use the backslash to escape a quote. Instead, replace a single quote with two single quote to escape. The following code would generate an error

insert into mycode (code, start, description) values ('82', '2012-01-01', 'Alzheimer's disease');

It generates the following error message

SQL0104N  An unexpected token ...  was found following ...

The following would work

insert into mycode (code, start, description) values ('82', '2012-01-01', 'Alzheimer''s disease');

DB2 substring function

DB2 provides a substr function for substrings. It takes three parameters. The first is the column name. The second is the index of the character. Note that DB2 index begins with 1, not 0. The third parameter specifies the number of parameters you are interested in. The following code selects first 5 characters of the string.

select substr(desc,1,5) from mytable where oid = 113;

Show tables

To list tables in DB2, use the following code:

select * from syscat.tables where type = 'T'

To filter by schema (e.g. "operations")

select * from syscat.tables where type = 'T' and tabschema = 'operations'

To show table names only

select tabname from syscat.tables where type = 'T' and tabschema = 'operations'

Adding current date to query

CURRENT DATE gets the date from the server's clock at the time the query is run. It also us to use the current date in a query. Example:

insert into employee (id, name, modified) values (1324, 'Jim', CURRENT DATE);