DB2
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);