DB2 Demo for CS338

Use command prompt and
ssh linux.student.cs.uwaterloo.ca

Use session on linux028
login shell
If you are not sure, you can type
bash or tcsh

Let me use tcsh

source ~cs338/public/db2cshrc
cat ~cs338/public/db2cshrc 
db2

For bash, you would have to run
. ~cs338/public/db2profile
and add it to the end of the .profile file or run it every time when you start
db2

list db directory

connect to cs338

Type in sql commands

Simple Usage and DDL
====================
list tables for all
list tables for user
list tables for schema cs338

quit to leave db2 and keep any connection active
terminate to leave db2 and reset the connection

The "-t" option below tells db2 that we will terminate a command with ";" (which allows us to easily have multi line definitions).

db2 -t

create table Customer
(AccNum integer not null,
 Cname varchar(20) not null,
 Prov varchar(20),
 Cbal decimal(6,2) not null,
 Climit decimal(4,0) not null,
 primary key (AccNum));
 
Insert
======

insert into customer values
(101, 'Smitherman', 'QC', 0, 1000);

insert into customer values
(107, 'Smith', 'ON', -765.34, 2000);

insert into customer values
(115, 'Jones', 'BC', 247.65, 500);

delete from customer where accnum = 102;

select * from customer;

select * from customer where climit>500;

quit
db2

Statements on one line
if you want to split accross multiple lines, use \
select cname, prov, climit \
from customer \
where cbal>=0

drop table customer
terminate

put the above create table command and the inserts (with ";"'s) into a file called populate.sql  the -f options means to executre commands from a file, while the -v command means to echo the the commands that you give.  From the command line execute:

db2 -t -v -f populate.sql -z pop-output.txt

For the assignment you'll be creating three files, populate.sql, ddl.sql and showtable.sql  While you're getting these working, you'll probably use something like:
db2 -t -v -f ddl.sql -z ddl-output.txt
db2 -t -v -f showtable.sql -z st-output.txt

Submit
======
submit -L cs338 1
submit -l cs338 1
submit cs338 1 .
submit -l cs338 1
man submit

Schema and Metadata
===================
connect to cs338;
(use your own userid instead of JCHAMPAI)
select tabname from syscat.tables where tabschema = 'JCHAMPAI';
select tabname from syscat.tables where tabschema = 'jchampai';

? list tables
?

select * from customer where cname = 123;

gives us an error, SQL0420N

? SQL0206N;

gives us more detail about the error