Of course, I had to have some tables to work with. So I created these tables in my SAMPLE database under my user name as the default: THACKER. You can FREELY use the tables provided in the SAMPLE if you don’t care to take this step.

Here is the source code. Feel free to let me know where I can improve it. As I said, I am new to all of this and I know I didn’t say anything about tablespace, etc. I don’t even know what tablespace I will ultimately use, as the DB2 Administrator here is non-existent, and the person who was going to set it up is leaving... so...

Step 1:

Telnet into your DB2 server. Type:
db2
connect to sample user <yourname> using <yourpassword>

This should give you a  connect to the database. Then cut and paste or type your file into a file in your directory. I called mine: “sched.doc” so that I could open it up from within Windows to edit it and then paste it into a VI file on the server for running.

Here is how to run it:
! db2 -tvf sched.doc -r testout.doc

By the way, I like to drop my tables before I create them so that if I just created them before, I can drop them before fixing the code and creating them again. So that is why the drop tables are in there before the create tables. This code will give you some tables to work with and some data in the tables. Not much data, but some.

 
drop table college ;
create table college 
( college_idsmallint  NOT NULL, 
  college_namevarchar(50),
  PRIMARY KEY (college_id ) 
) ;
 
insert into college values
(1,'Fairmount College of Liberal Arts & Sciences');
 
insert into college values
(2,'W. Frank Barton School of Business');
 
insert into college values
(3,'College of Engineering');
 
select * from college ;
 
drop table department ;
drop table dept ;
 
create table dept
( college_idsmallint,
  dept_idvarchar(5),
  dept_namevarchar(30) NOT NULL,
  roomvarchar(30),
  phonevarchar(8),
  PRIMARY KEY (dept_name ) ,
  FOREIGN KEY fk_coll_id
                  (college_id )
    REFERENCES thacker.college ON DELETE RESTRICT 
);
 
insert into dept values 
(2,'ACCT','ACCOUNTING','330 Clinton Hall','978-3215');
insert into dept values 
(2,'DS','DECISION SCIENCES','325 Clinton Hall','978-3219');
insert into dept values 
(2,'ECON','ECONOMICS','115 Clinton Hall','978-3220');
 
insert into dept values 
(3,'AE','AEROSPACE ENGINEERING','200 Wallace Hall','978-3410');
insert into dept values 
(3,'EE','ELECTRICAL ENGINEERING','300 Wallace Hall','978-3415');
insert into dept values 
(3,'IEN','INDUSTRIAL ENGINEERING','120 Engineering Building','978-3425');
 
insert into dept values 
(1,'AJ','ADMINISTRATION OF JUSTICE','208 Lindquist Hall','978-7230');
insert into dept values
(1,'ANTHR','ANTHROPOLOGY','130 McKinley Hall','978-3195');
insert into dept values
(1,'BIOL','BIOLOGY','537 Hubbard Hall','978-3111');
insert into dept values
(1,'CHEM','CHEMISTRY','326 McKinely Hall','978-3120');
insert into dept values
(1,'COMM','COMMUNICATIONS','102 Elliott Hall','978-3185');
insert into dept values
(1,'CS','COMPUTER SCIENCE','249 Jabara Hall','978-3156');
 
select * from dept ;
 
 
drop table course ;
 
create table course
( dept_idvarchar(5),
  coursenumvarchar(5) NOT NULL,
  coursenamevarchar(50),
  credithourssmallint,
  coursecommentvarchar(75),
  PRIMARY KEY (coursenum ) 
) ;
 
insert into course values
('CS','105','INTRODUCTION TO COMPUTERS & APPLICATIONS',3,'');
insert into course values
('CS','105A','MICROSOFT WORD-1',1,'');
insert into course values
('CS','350I','WEB DESIGN HTML',2,'This is a comment for web design HTML.');
insert into course values
('CHEM','111Q','GENERAL CHEMISTRY',5,'Comment for general chemistry.');
insert into course values
('CHEM','111QL','GENERAL CHEM LAB-REC',0,'');
 
select * from course ;
 
drop table section ;
 
create table section
( dept_idvarchar(5),
  coursenumvarchar(5),
  yearsemestervarchar(7),
  sectionsmallint   NOT NULL,
  termvarchar(4),
  timevarchar(11),
  daysvarchar(5),
  nitedayvarchar(1),
  roomvarchar(4),
  buildingvarchar(4),
  footnotevarchar(14),
  instructor_idsmallint,
  section_commidsmallint,
  openclosedvarchar(1),
  quotasmallint,
  PRIMARY KEY (section ) 
) ;
 
insert into section values
('CS','350I','1997Sum',0824,'1ST','9:00 11:30','MWF','D','*207','JB','P+',1,1,'O',30);
 
select * from section ;
 
drop table comment ;
create table comment
( section_commidsmallint NOT NULL,
  section_comment varchar(75),
  PRIMARY KEY (section_commid ) 
) ;
 
insert into comment values
(1,'Learn the Hypertext Markup Language and elements of design & style.');
select * from comment ;
 
drop table instructor ;
 
create table instructor
( instructor_idsmallint NOT NULL,
  instructor_lastvarchar(40),
  instructor_firstvarchar(30),
  instructor_mivarchar(1),
  bio_reportvarchar(75),
  emailvarchar(50),
  urlvarchar(50),
  PRIMARY KEY (instructor_id ) 
) ;
 
insert into instructor values
(1,'GREEN','TOM','L','Graduate of blah blah blah','tgreen@wsu.edu','www.wsu.edu/~tgreen');
 
select * from instructor ;

Modified: 02/11/99