| Topic |
Description |
| Create a Database |
- Using "Command Line Processor" to Create a Database
- Start, Programs, IBM DB2, Command Line Processor
- db2 => create database <database name>
Ex: create database test
- db2 => quit
- Using "Command Center" to Create a Database
- Start, Programs, IBM DB2, Command Center
- Click on the "Interactive" tab.
- type: create database LS5
- Click on the "Gears" icon. Wait for the message
"CREATE DATABASE command completed successfully."
- Using "Control Center" to Create a Database
- Start, Program Files, DB2 for Win NT, Control Center.
- Now that you are in Control Center, click down the tree by choosing:
<computer>, Instances, DB2, Databases.
- Right click, then click on "Create", "Database using Smart
Guide". You can choose the defaults from there.
- Some fields you'll need to fill out:
- "New Database Name" (only 8
characters),
- Alias (can leave blank)
- Comment - anything you want.
- Other Screens
- You can accept all of the defaults by just clicking on
"Next" or "Finish"
|
| Create a Table in a Database |
- Create a system user which will
be used to logon to the database & it's tables. Now login as that
user. Now when you create the TABLE it will assign the user name as
the SCHEMA name. If you don't specify the schema DB2 will use the name
of the user as the SCHEMA prefix to the table name.
Ex: THOMASMA.TEST - <schema name>.<table name>
- Using "Command Line Processor" to Create a Table
- create table test <... rest of SQL code ....>
- Using "Control Center" to Create a Database
- Start, Program Files, DB2 for Win NT, Control Center.
- Now that you are in Control Center, click down the tree by choosing:
<computer>, Instances, DB2, Databases. Then click on the
Database name.
- Right click and choose: "Create using Wizard". You can
choose the defaults from there.
- Some fields you'll need to fill out:
- Table schema: leave blank to default to the UserID you are
logged in as.
- Table name: (8 characters)
- Next create the fields, primary key, etc...
- SQL example:
- connect to SAMPLE
- create table TEST ( "USERID" character (8) not null,
"FIRST" character (15), "LAST" character(20),
primary key (USERID) )
- Example SQL Code:
drop table test;
create table TEST (
"USERID" character (8) not null,
"FIRST" character (15),
"LAST" character(20), primary key (USERID)
);
insert into Test (userid, first, last) values ('1','Michael','Thomas');
insert into Test (userid, first, last) values ('2', 'Adam',
'Thomas');
insert into Test (userid, first, last) values ('3', 'Stephen',
'Thomas');
insert into Test (userid, first, last) values ('4', 'Jane',
'Doe');
insert into Test (userid, first, last) values ('5', 'John',
'Doe');
select userid, first, last, concat(first, concat(' ',last)) from
Test with UR;
drop view v_test1;
CREATE VIEW v_test1 ( userid, first, last, fullname )
AS
(select userid, first, last, concat(first, concat(' ',last))
from Test);
|
| |
|
Launch: Start, Programs, IBM DB2, Command Center.
Launch: Start, Programs, IBM DB2, Command Center.
| Topic |
Description |
| Privileges, Authorities and Access Rights |
Grant Access to a Database
- Launch "Control Center"
- <MachineName>, Instances, DB2, Databases. Next
right click on the DB and choose "Authorities".
Grant access to a Table
- Launch "Control Center"
- <MachineName>, Instances, DB2, Databases, <db
name>, tables. Next right click on the Table.
Choose "Privileges".
|
| View Sample Data from a Table |
Steps:
- Go to the Tables View (Ex: Systems, <MachineName>, Instances,
DB2, Databases, <DatabaseName>, Tables.
- Right Click on the Table you want to view Sample contents.
- Choose "Sample Contents"
- Shows the first 50 records. Press next for the next 50.
- "Filter" - click on "Filter" to run a basic
query and filter out records.
|
| View and Modify a Table Structure |
Steps:
- Go to the Tables View (Ex: Systems, <MachineName>, Instances,DB2,
Databases, <DatabaseName>, Tables.
- Double click on the Table you want to View/Modify. (Note: You can
also right click on the Table and choose "Alter".)
- There is a Table for the following:
- Table
- Columns - shows the structure of the table.
- Primary Key
- Foreign Keys
- Check Constraints
- Note: There is a "Show Sql" tab that will show you
the SQL that DB2 will perform to make the changes - really neat!
|
| Copy a Database |
Steps:
- Optional: If the Database is in use when you go to backup, you
may need to Stop and Start DB2:
Right click on "DB2" (Ex: Systems, <MachineName>, Instances,
DB2)
Click Stop.
Next, Click Start
- Right click on the DB Name: Choose "Backup",
"Database". Now complete the information on the
screens and then click "Backup Now".
- Note: Directory must already exist to where you want to
backup up to.
- To view the status and output of the job, use the Jobs page on the
Journal (Tools, Journal). Or you can wait for the second
message to pop up that tells you if the job completed
successfully. If you get a backup error you need to go to
the Journal to see why.
- To Restore a DB from a backup.
Right click on the DB Name: Choose "Restore",
"Database".
Note: All connections to the DB must be released. You can
use the following SQL:
connect reset
force application all
Also, you may need to close "Control Center" and launch it
again to release the connection.
- To Restore to a new DB name from a backup!
Right click on the DB Name: Choose "Restore to New"
Note: If you get error, you may want to exit "Control
Center" and launch it again prior to the below steps.
- First, click on the tab "Backup Image" and select the
backup file.
- Second, click on the tab "Description" and specify the
"new database name". Enter the new database name.
- "Show SQL" - This button will show you the SQL code
for the Backup and Restore operation it will run.
|
| |
|
Launch: Start, Programs, IBM DB2, Command Center.
| Topic |
Description |
| General Notes |
- Strings are enclosed in single quotes (ex: 'My string') not in
double quotes.
- Coding
- Comments: -- This is a comment.
- Multiple lines: use the character "\".
ex: select * from TEST \
order by ID
- Escape character.
- To insert a single quote, use 2 single quotes ( '' ).
To insert pet's use the following pet''s.
Example:
insert into MYTABLE (question,answer) values ('What is your pet''s
name?','blacky')
|
Connect/Use a Database
(Also Disconnect) |
Connect to a Database.
(Warning: If you don't specify a
username & password you will connect to the DB as your current
Windows login. Check the "Schema" via the Control
Center to see what user you need to log in as.)
- connect to <database name>
- connect to <database name> user <username> using
<userpassword> (Remember that correct
authorities must be granted at the database level to access the
database. You can use "Control Center" for
this.)
- disconnect <database name> - allows you to clear
the connection.
- Good way to commit, close a database, and terminate script.
COMMIT WORK
CONNECT RESET
TERMINATE
|
| List structure of a Table |
List Structure of a Table
- DESCRIBE SELECT * FROM [table]
|
| Tables |
SQL commands
- List tables in a Database:
- list tables for all - this will show you all of the
tables.
- Another more technical way:
select name, creator from sysibm.systables order by name
- this will only show you the tables you have
access to.
- Create a Table:
create table <table name>(<column name1> integer, <column
name2> char(12), [etc...])
- Sequence Numbers (see AutoNumber below)
|
| SQL commands |
- Select data.
select * from <table name>
- Insert a row.
insert into <table name> values (1,'hello')
(Note: 1 goes into the first field, 'hello' into the 2nd
etc...)
insert into <table name> (<column1>, <column2)
values (<value1>, <value2>)
- Update fields.
update <table> set <field> = 'New info' where <field> =
'Old Info'
|
AutoNumber for ID's
Sequence Numbers. |
Example of having DB2 create an ID number
- create table TESTING ( TEST_ID integer not null generated always
as identity (start with 0, increment by 1, no cache) primary key,
NAME varchar (30) )
|
| Special Functions |
Special Functions
- where DOUBLE(MYSTRING) > 0.0 - converts a from a
string to a double.
- where INTEGER(MYSTRING) > 0.0 - converts from a string to
an integer.
- SUBSTR - SUBSTR(<string>,<start>,<length>)
Example:
select FIRSTNME, LASTNAME, SUBSTR(FIRSTNME,1,4), SUBSTR(LASTNAME,2,4) from db2admin.EMPLOYEE
Returns:
JOHN PARKER JOHN ARKE
PHILIP SMITH PHIL MITH
MAUDE SETRIGHT MAUD ETRI
|
| Lower/Upper Case |
Lower/Upper Case: On CHARACTER or VARCHAR
- LOWER(<fieldname>)
LCASE(<filedname>)
- UPPER(<filedname>)
UCASE(<filedname>)
- Examples using the Sample DB (SAMPLE) that is installed with
DB2. Make sure your commands are on one line. These
examples were tested in the "Command Center's" Script
area. Highlight the code you want to run and press the
"Gears" icon.
connect to SAMPLE
insert into DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES ('4AA', '4Test','X','X','X'), ('AA4',
'Test','X','X','X')
select * from DEPARTMENT
SELECT LCASE(DEPTNAME) FROM DEPARTMENT WHERE DEPTNAME = '4Test'
SELECT LCASE(DEPTNAME) FROM DEPARTMENT WHERE LCASE(DEPTNAME) = '4test'
SELECT DEPTNAME FROM DEPARTMENT WHERE LCASE(DEPTNAME) = '4test'
SELECT LCASE(DEPTNAME) FROM DEPARTMENT WHERE LCASE(DEPTNAME) like '4%'
SELECT LCASE(DEPTNAME) FROM DEPARTMENT WHERE LCASE(DEPTNAME) like 't%'
SELECT LCASE(DEPTNAME) FROM DEPARTMENT WHERE LCASE(DEPTNAME) like '%t%'
SELECT DEPTNAME FROM DEPARTMENT WHERE LCASE(DEPTNAME) like '4%'
delete from DEPARTMENT where DEPTNAME = 'Test'
delete from DEPARTMENT where DEPTNAME = '4Test'
select * from DEPARTMENT
|
| Date Time |
Date/Time
- CURRENT TIMESTAMP - insert the current date/time as a
TIMESTAMP.
Ex: insert into testing.TEST (timestamp) \
values (CURRENT TIMESTAMP)
- CURRENT DATE
- CURRENT TIME
|
| Comparators |
Compare
IN
- select * where MYSTRING in ('hello','world')
- select * where MYINTEGER in (1,2,3,4,5)
|
| Left Join |
Left Join
Example #1: Joins the MYADDRESS & MYSTATE tables
together using the STATE field as the common value so that the
STATENAME can be retrieved.
select MYADDRESS.STATE AS STATE, MYSTATE.STATENAME AS STATENAME \
from MYADDRESS \
left join MYSTATE as MYSTATE \
on MYSTATE.STATE = STATE \
|
| Left Join Examples using the
"SAMPLE" DB |
-- List Employee and the name of his work department.
select EMPLOYEE.EMPNO as EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, DEPARTMENT.DEPTNAME as WORKDEPT_NAME from db2admin.EMPLOYEE as EMPLOYEE \
left join db2admin.DEPARTMENT as DEPARTMENT on DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT \
where EMPLOYEE.EMPNO = '000010'
-- List Departments and it's Administrative Department
select DEPARTMENT.DEPTNO, DEPARTMENT.DEPTNAME, DEPARTMENT.ADMRDEPT, ADMINDEPT.DEPTNAME as ADMIN_DEPTNAME from db2admin.DEPARTMENT as DEPARTMENT \
left join db2admin.DEPARTMENT as ADMINDEPT on ADMINDEPT.DEPTNO = DEPARTMENT.ADMRDEPT
-- List of Employees and the projects that they have worked on.
select EMPLOYEE.EMPNO as EMPNO, FIRSTNME, MIDINIT, LASTNAME, PROJNO from db2admin.EMPLOYEE as EMPLOYEE \
left join db2admin.EMP_ACT as EMP_ACT on EMP_ACT.EMPNO = EMPLOYEE.EMPNO \
where EMPLOYEE.EMPNO = '000010'
-- List of Employees and the projects that they have worked on and the Project's name.
select EMPLOYEE.EMPNO as EMPNO, FIRSTNME, MIDINIT, LASTNAME, PROJNO from db2admin.EMPLOYEE as EMPLOYEE \
left join db2admin.EMP_ACT as EMP_ACT on EMP_ACT.EMPNO = EMPLOYEE.EMPNO \
where EMPLOYEE.EMPNO = '000010'
-- List of Employees and the projects that they have worked on and the Project's name with the Major Project name.
select EMPLOYEE.EMPNO as EMPNO, EMPLOYEE.FIRSTNME as FIRSTNME, EMPLOYEE.MIDINIT as MIDINT, EMPLOYEE.LASTNAME as LASTNAME, \
EMP_ACT.PROJNO as PROJNO, \
PROJECT.PROJNAME as PROJNAME, PROJECT.MAJPROJ as MAJPROJ, \
MAJOR_PROJECT.PROJNAME as MAJ_PROJ_NAME \
from db2admin.EMPLOYEE as EMPLOYEE \
left join db2admin.EMP_ACT as EMP_ACT on EMP_ACT.EMPNO = EMPLOYEE.EMPNO \
left join db2admin.PROJECT as PROJECT on PROJECT.PROJNO = EMP_ACT.PROJNO \
left join db2admin.PROJECT as MAJOR_PROJECT on MAJOR_PROJECT.PROJNO = PROJECT.MAJPROJ \
where EMPLOYEE.EMPNO = '000010'
|
| Date example using the
"Sample" DB |
-- Select records less than equal to a date ( <= )
select EMPNO, EMSTDATE from db2admin.EMP_ACT \
where EMSTDATE <= '01/01/1982'
-- Select records for a date ( = )
select EMPNO, EMSTDATE from db2admin.EMP_ACT \
where EMSTDATE = '06/01/1982'
-- Select records bases on a range.
select EMPNO, EMSTDATE from db2admin.EMP_ACT \
where EMSTDATE >= '06/01/1982' and EMSTDATE <= '07/01/1982'
-- Select records based on a year.
select EMPNO, EMSTDATE from db2admin.EMP_ACT \
where year(EMSTDATE) > 1982 |
Documentation: DB2 Information Center, then "Books" tab, then click
on "SQL Getting Started", then look for "Data Types" in the
left navigation window.