DB2 Survival Guide

DB2 Environment Basics

Creating a Database and Tables (v6.0 & v7.0)

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

Using: DB2 Command Center

Launch: Start, Programs, IBM DB2, Command Center.

Topic

Description

Importing external Scripts

Import and run an external Script

Launch the "Script Center": Tools, Script Center

Import a .sql script file.

I've found that DB2 does not like carriage returns or line feeds. Make your commands be on one long line or use "\" for multiple lines.

Fill out the prompted information.

Select the script. Click on "Selected", then "Run Now"

You'll need to launch the "Journal" to see if the script ran successfully.

Notes:

The script is imported into DB2 not linked to, therefore changes made to the script's physical file after the import will not change the imported script.

Connect to a DB

SQL: (Connect to a Database).

connect to <dbname> user <username> using <userpswd>

ex: connect to TEST user db2admin using db2admin

View a tables structure

SQL:

	Connect to the DB first.
	describe table <schema>.<tablename>

Interactive Tab vs Script Tab

Interactive Tab - only allows one SQL command.
Script Tab - You can place many SQL commands and highlight the ones you want to run.

I like to use the Script Tab.

Using: DB2 Control 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.

Using: Command Line Processor

Launch: Start, Programs, IBM DB2, Command Center.

Topic

Description

Command help

Command Help

	? <enter> - List commands
	? backup command - List help on a specific command.

Backup and Restore or Copy a DB.

Backup and Restore (also Copy a DB).

	Create a directory to backup to: Ex: C:\db2\mybackup
	Backup your DB: Ex: db2 => backup database SAMPLE TO c:\db2\mybackup
	Restore the DB: db2 => restore database SAMPLE from C:\db2\mybackup
	Restore the DB to another name (ie: Copy DB): db2 => restore database SAMPLE from C:\db2\mybackup into MYSAMPLE

Application Heap Size

If you're getting Heap Size errors, the double the size of the heap.

db2 =>

get dbm cfg - Displays the current aslheapsz:

update dbm cfg using aslheapsz 30 - Sets the heap size to 30.

Run SQL Commands

db2=> (examples)

	list database directory - lists databases on DB2 server
	connect to sample user db2admin using db2admin - connects to a db.
	list tables - lists tables in the DB.
	*describe select from emp_act** - lists the structure of a table.
	*select from EMP_ACT** - list the contents in the table.

Help

db2 => ? - this will give a list of commands.
db2 => ? connect to - this will give help on the "connect to" command.

Using: Command Window

Topic

Description

Command Window

Initializes the DB2 Command Line environment and then takes you to the directory: C:\PROGRAM~1\SQLLIB\BIN

Uses:

You can run DB2 exe programs.

You can run DB2 SQL commands. (Examples)

	db2 list database directory - lists databases on DB2 server
	db2 - connects to a db.
	db2 list tables - lists tables in the DB.
	db2 - lists the structure of a table.
	*db2 select from EMP_ACT** - list the contents in the table.

Using: DB2 Scripts & DOS

Topic

Description

Running External Scripts

Name your scripts with a .ddl extension.

Example script: (filename: myplaytestscript.ddl)
DROP DB MYPLAY
CREATE DB MYPLAY
CONNECT TO MYPLAY
CREATE TABLE MYTESTTABLE ( \
"TESTVARCHAR" VARCHAR(50) \
)
DESCRIBE SELECT * FROM MYTESTTABLE
SELECT * FROM MYTESTTABLE
DISCONNECT MYPLAY
DROP DB MYPLAY
TERMINATE

Create the following batch file:
(Filename: myplaytestscript.bat)

db2cmd db2 -f myplaytestscript.ddl
pause

Run the batch file.

NOTES

db2cmd.exe default install directory is:
"C:\Program Files\SQLLIB\bin\db2cmd.exe"

Basic SQL for DB2 (examples)

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

	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

	select * where MYSTRING in ('hello','world')
	select * where MYINTEGER in (1,2,3,4,5)

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

Command Window (DOS)

	Access by: Start, Program Files, DB2 for Win NT, Command Window. This will normally open up a DOS window in the directory "\SQLLIB\BIN"
	Now add "db2" infront of SQL commands. (ex: db2 connect to <database name>)

Data Types

Documentation: DB2 Information Center, then "Books" tab, then click on "SQL Getting Started", then look for "Data Types" in the left navigation window.

Datatype	Notes
INTEGER	range is -2147483648 to 2147483647
SMALLINT	range is -32768 to 32767
BIGINT	range is -9223372036854775808 to 9223372036854775807
CHAR(x)	Maximum length of 254 Ex: 'Some info '
VARCHAR(x)	Maximum length of 32672 Ex: 'Some info '
LONG VARCHAR
DATE	3 Parts: 1991-10-27
TIMESTAMP	7 Parts: 1991-10-27-13.30.05.000000
TIME	3 Parts: 13.30.05
DOUBLE	range is -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308 or zero
CLOB	Length in Bytes
BLOB	Length in Bytes
DECIMAL(x,x)	Precision & Scale range is -1031+1 to 1031-1
REAL	range is -3.402E+38 to -1.175E-37 or 1.175E-37 to -3.402E+38 or zero
GRAPHIC(x)	x must be between 1 and 127, inclusive. A graphic string is a sequence of double-byte character data.