/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dbsample.sqc
**
** SAMPLE: Creates a sample database
**
** This program creates a sample database with both DB2 workstation
** and host system (OS/390 and AS/400) sample tables and views.
** The sample database consists of eleven tables. The database
** name is DBSAMPLE, and the table names are ORG, STAFF,
** DEPARTMENT, EMPLOYEE, EMP_ACT, PROJECT, EMP_PHOTO and EMP_RESUME,
** SALES, CL_SCHED, and IN_TRAY.
**
** If the DBSAMPLE database already exists, the program does not
** recreate it and continues. DBSAMPLE's access plan is then bound
** to the database, and the tables are created and populated.
** When the program completes, database connection is reset.
**
** DB2 APIs USED:
** db2DatabaseRestart -- RESTART DATABASE
** sqlecrea -- CREATE DATABASE
** sqlefrce -- FORCE APPLICATION
** sqlabndx -- BIND PACKAGE
**
** SQL STATEMENTS USED:
** INCLUDE
** CONNECT
** BEGIN
** END
** GRANT
** CONNECT
** EXECUTE
** COMMIT
**
** STRUCTURES USED:
** sqlca
** sqlopt
** sqloptions
** VARCHAR
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <db2ApiDf.h>
#include <sqle819a.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int execi(char s[1000]);
EXEC SQL BEGIN DECLARE SECTION;
struct VARCHAR {
short len;
char stmt[1000];
} sqlstmt = {14,"create schema "};/* Varchar for statement text */
EXEC SQL END DECLARE SECTION;
int DbConnect(char *, char *, char *);
int DbConnectCheck(char *, char *, char *);
int DbRestart(char *, char *, char *);
int DbDisconnect(void);
int AllApplicationsConnectedToAllDatabasesForceOff(void);
int BndFileCreate(char *);
int PackageCreate(char *);
int main(int argc, char *argv[])
{
int rc = 0;
char nodeName[SQL_INSTNAME_SZ + 1];
char sourceFileBaseName[50 + 1];
EXEC SQL BEGIN DECLARE SECTION;
char dbAlias[15];
char user[128 + 1];
char pswd[15];
EXEC SQL END DECLARE SECTION;
/* check the command line arguments */
rc = CmdLineArgsCheck3(argc, argv, dbAlias, nodeName, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE CREATES A SAMPLE DATABASE.");
printf("\n");
strcpy(dbAlias, "dbsample");
printf("Database name is %s", dbAlias);
printf("\n");
rc = DbConnectCheck(dbAlias, user, pswd);
if(rc == 0)
{
printf("\nDBSAMPLE ALREADY EXISTS!\n");
printf("\n");
return rc;
}
if(rc == 1)
{
printf("\nCreate Database DBSAMPLE\n");
printf("\n");
}
sqlecrea(dbAlias, NULL, NULL, NULL, NULL, 0, NULL, &sqlca);
printf("\nConnect DBSAMPLE");
rc = DbConnect(dbAlias, user, pswd);
EMB_SQL_CHECK("Check Connection");
strcpy(sourceFileBaseName, "./dbsample");
rc = PackageCreate(sourceFileBaseName);
rc = DbRestart(dbAlias, user, pswd);
EXEC SQL GRANT CONNECT ON DATABASE TO PUBLIC;
EMB_SQL_CHECK("Grant Connection");
EXEC SQL GRANT BINDADD ON DATABASE TO PUBLIC;
EMB_SQL_CHECK("Grant BindAdd");
EXEC SQL GRANT CREATETAB ON DATABASE TO PUBLIC;
EMB_SQL_CHECK("Grant createtab");
EXEC SQL GRANT EXECUTE ON PROGRAM DBSAMPLE TO PUBLIC;
EMB_SQL_CHECK("Grant execute");
printf("\ncreate table");
/*********************************************************************/
/* */
/* ORG */
/* */
/*********************************************************************/
/*
execi(
"CREATE TABLE ORG (DEPTNUMB SMALLINT NOT NULL, DEPTNAME VARCHAR(14),MANAGER SMALLINT, DIVISION VARCHAR(10), LOCATION
VARCHAR(13))" );
*/
execi(
"CREATE TABLE ORG "
" (DEPTNUMB SMALLINT NOT NULL, "
" DEPTNAME VARCHAR(14),MANAGER SMALLINT, "
" DIVISION VARCHAR(10), "
" LOCATION VARCHAR(13))" );
/*********************************************************************/
/* */
/* ORG */
/* */
/*********************************************************************/
execi(
"INSERT INTO ORG VALUES( "
" 10,'Head Office', 160,'Corporate','New York')" );
execi(
"INSERT INTO ORG VALUES( "
" 15,'New England', 50, 'Eastern','Boston')" );
execi(
"INSERT INTO ORG VALUES( "
" 20, 'Mid Atlantic', 10, 'Eastern', 'Washington')" );
execi(
"INSERT INTO ORG VALUES( "
" 38, 'South Atlantic', 30, 'Eastern', 'Atlanta')" );
execi(
"INSERT INTO ORG VALUES( "
" 42, 'Great Lakes', 100, 'Midwest', 'Chicago')" );
execi(
"INSERT INTO ORG VALUES( "
" 51, 'Plains', 140, 'Midwest', 'Dallas')" );
execi(
"INSERT INTO ORG VALUES( "
" 66, 'Pacific', 270, 'Western', 'San Francisco')" );
execi(
"INSERT INTO ORG VALUES( "
" 84, 'Mountain', 290, 'Western', 'Denver')" );
/*********************************************************************/
/* */
/* STAFF */
/* */
/*********************************************************************/
execi(
"CREATE TABLE STAFF \
(ID SMALLINT NOT NULL, \
NAME VARCHAR(9), \
DEPT SMALLINT, \
JOB CHAR(5), \
YEARS SMALLINT, \
SALARY DECIMAL(7,2), \
COMM DECIMAL(7,2))" );
/*********************************************************************/
/* */
/* STAFF */
/* */
/*********************************************************************/
execi(
"INSERT INTO STAFF VALUES( \
10, 'Sanders', 20, 'Mgr', 7, 18357.50, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45)" );
execi(
"INSERT INTO STAFF VALUES( \
30, 'Marenghi', 38, 'Mgr', 5, 17506.75, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
40, 'O''Brien', 38, 'Sales', 6, 18006.00, 846.55)" );
execi(
"INSERT INTO STAFF VALUES( \
50, 'Hanes', 15, 'Mgr', 10, 20659.80, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
60, 'Quigley', 38, 'Sales', NULL, 16808.30, 650.25)" );
execi(
"INSERT INTO STAFF VALUES( \
70, 'Rothman', 15, 'Sales', 7, 16502.83, 1152.00)" );
execi(
"INSERT INTO STAFF VALUES( \
80, 'James', 20, 'Clerk', NULL, 13504.60, 128.20)" );
execi(
"INSERT INTO STAFF VALUES( \
90, 'Koonitz', 42, 'Sales', 6, 18001.75, 1386.70)" );
execi(
"INSERT INTO STAFF VALUES( \
100, 'Plotz', 42, 'Mgr', 7, 18352.80, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
110, 'Ngan', 15, 'Clerk', 5, 12508.20, 206.60)" );
execi(
"INSERT INTO STAFF VALUES( \
120, 'Naughton', 38, 'Clerk', NULL, 12954.75, 180.00)" );
execi(
"INSERT INTO STAFF VALUES( \
130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, 75.60)" );
execi(
"INSERT INTO STAFF VALUES( \
140, 'Fraye', 51, 'Mgr', 6, 21150.00, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
150, 'Williams', 51, 'Sales', 6, 19456.50, 637.65)" );
execi(
"INSERT INTO STAFF VALUES( \
160, 'Molinare', 10, 'Mgr', 7, 22959.20, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
170, 'Kermisch', 15, 'Clerk', 4, 12258.50, 110.10)" );
execi(
"INSERT INTO STAFF VALUES( \
180, 'Abrahams', 38, 'Clerk', 3, 12009.75,236.50)" );
execi(
"INSERT INTO STAFF VALUES( \
190, 'Sneider', 20, 'Clerk', 8, 14252.75, 126.50)" );
execi(
"INSERT INTO STAFF VALUES( \
200, 'Scoutten', 42, 'Clerk', NULL, 11508.60, 84.20)" );
execi(
"INSERT INTO STAFF VALUES( \
210, 'Lu', 10, 'Mgr', 10, 20010.00, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
220, 'Smith', 51, 'Sales', 7, 17654.50, 992.80)" );
execi(
"INSERT INTO STAFF VALUES( \
230, 'Lundquist', 51, 'Clerk', 3, 13369.80, 189.65)" );
execi(
"INSERT INTO STAFF VALUES( \
240, 'Daniels', 10, 'Mgr', 5, 19260.25, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
250, 'Wheeler', 51, 'Clerk', 6, 14460.00, 513.30)" );
execi(
"INSERT INTO STAFF VALUES( \
260, 'Jones', 10, 'Mgr', 12, 21234.00, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
270, 'Lea', 66, 'Mgr', 9, 18555.50, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
280, 'Wilson', 66, 'Sales', 9, 18674.50, 811.50)" );
execi(
"INSERT INTO STAFF VALUES( \
290, 'Quill', 84, 'Mgr', 10, 19818.00, NULL)" );
execi(
"INSERT INTO STAFF VALUES( \
300, 'Davis', 84, 'Sales', 5, 15454.50, 806.10)" );
execi(
"INSERT INTO STAFF VALUES( \
310, 'Graham', 66, 'Sales', 13, 21000.00, 200.30)" );
execi(
"INSERT INTO STAFF VALUES( \
320, 'Gonzales', 66, 'Sales', 4, 16858.20, 844.00)" );
execi(
"INSERT INTO STAFF VALUES( \
330, 'Burke', 66, 'Clerk', 1, 10988.00, 55.50)" );
execi(
"INSERT INTO STAFF VALUES( \
340, 'Edwards', 84, 'Sales', 7, 17844.00, 1285.00)" );
execi(
"INSERT INTO STAFF VALUES( \
350, 'Gafney', 84, 'Clerk', 5, 13030.50, 188.00)" );
/*********************************************************************/
/* */
/* DEPARTMENT */
/* */
/*********************************************************************/
execi(
"CREATE TABLE DEPARTMENT ( \
DEPTNO CHAR(3) NOT NULL, \
DEPTNAME VARCHAR(36) NOT NULL, \
MGRNO CHAR(6), \
ADMRDEPT CHAR(3) NOT NULL, \
LOCATION CHAR(16), \
PRIMARY KEY (DEPTNO))" );
execi(
"ALTER TABLE DEPARTMENT \
ADD FOREIGN KEY ROD (ADMRDEPT) \
REFERENCES DEPARTMENT \
ON DELETE CASCADE" );
execi(
"CREATE INDEX XDEPT2 \
ON DEPARTMENT (MGRNO)" );
execi(
"CREATE INDEX XDEPT3 \
ON DEPARTMENT (ADMRDEPT)" );
execi(
"CREATE ALIAS DEPT FOR DEPARTMENT" );
/*********************************************************************/
/* */
/* DEPARTMENT */
/* */
/*********************************************************************/
execi(
"INSERT INTO DEPARTMENT VALUES( \
'A00' ,'SPIFFY COMPUTER SERVICE DIV.' , '000010', 'A00', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'B01', 'PLANNING', '000020', 'A00', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'C01', 'INFORMATION CENTER', '000030', 'A00', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'D01', 'DEVELOPMENT CENTER', NULL, 'A00',NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'E01', 'SUPPORT SERVICES', '000050', 'A00', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'E11', 'OPERATIONS', '000090', 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'E21', 'SOFTWARE SUPPORT','000100', 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'F22', 'BRANCH OFFICE F2',NULL, 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'G22', 'BRANCH OFFICE G2',NULL, 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'H22', 'BRANCH OFFICE H2',NULL, 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'I22', 'BRANCH OFFICE I2',NULL, 'E01', NULL)" );
execi(
"INSERT INTO DEPARTMENT VALUES( \
'J22', 'BRANCH OFFICE J2',NULL, 'E01', NULL)" );
/*********************************************************************/
/* */
/* EMPLOYEE */
/* */
/*********************************************************************/
execi(
"CREATE TABLE EMPLOYEE ( \
EMPNO CHAR(6) NOT NULL, \
FIRSTNME VARCHAR(12) NOT NULL, \
MIDINIT CHAR(1) NOT NULL, \
LASTNAME VARCHAR(15) NOT NULL, \
WORKDEPT CHAR(3), \
PHONENO CHAR(4), \
HIREDATE DATE, \
JOB CHAR(8), \
EDLEVEL SMALLINT NOT NULL, \
SEX CHAR(1), \
BIRTHDATE DATE, \
SALARY DECIMAL(9,2), \
BONUS DECIMAL(9,2),COMM DECIMAL(9,2), \
PRIMARY KEY (EMPNO))" );
execi(
"ALTER TABLE EMPLOYEE \
ADD FOREIGN KEY RED (WORKDEPT) \
REFERENCES DEPARTMENT \
ON DELETE SET NULL" );
execi(
"ALTER TABLE EMPLOYEE \
ADD CONSTRAINT NUMBER \
CHECK (PHONENO >= '0000' AND PHONENO <= '9999')" );
execi(
"CREATE INDEX XEMP2 \
ON EMPLOYEE (WORKDEPT)" );
execi(
"CREATE ALIAS EMP FOR EMPLOYEE" );
/*********************************************************************/
/* */
/* EMPLOYEE */
/* */
/*********************************************************************/
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000010','CHRISTINE', 'I', 'HAAS', 'A00', '3978', \
'1965-01-01','PRES' , 18 , 'F', '1933-08-24', 52750.00, \
1000,4220)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000020','MICHAEL', 'L', 'THOMPSON', 'B01', '3476', \
'1973-10-10','MANAGER', 18 , 'M', '1948-02-02', 41250.00, \
800,3300)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000030','SALLY', 'A', 'KWAN', 'C01', '4738', \
'1975-04-05', 'MANAGER', 20, 'F', '1941-05-11', 38250.00, \
800,3060)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000050','JOHN', 'B','GEYER', 'E01', '6789', \
'1949-08-17','MANAGER', 16, 'M', '1925-09-15', 40175.00, \
800,3214)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000060','IRVING', 'F', 'STERN', 'D11', '6423', \
'1973-09-14','MANAGER', 16, 'M', '1945-07-07', 32250.00, \
500,2580)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000070','EVA', 'D', 'PULASKI', 'D21', '7831', \
'1980-09-30','MANAGER', 16, 'F', '1953-05-26', 36170.00, \
700,2893)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000090','EILEEN', 'W', 'HENDERSON', 'E11', '5498', \
'1970-08-15','MANAGER', 16, 'F', '1941-05-15', 29750.00, \
600,2380)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000100','THEODORE', 'Q', 'SPENSER', 'E21', '0972', \
'1980-06-19','MANAGER', 14 , 'M', '1956-12-18', 26150.00, \
500,2092)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000110','VICENZO', 'G', 'LUCCHESSI', 'A00', '3490', \
'1958-05-16','SALESREP', 19, 'M', '1929-11-05', 46500.00, \
900,3720)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000120','SEAN', ' ', 'O''CONNELL', 'A00', '2167',\
'1963-12-05','CLERK', 14, 'M', '1942-10-18', 29250.00, \
600,2340)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000130','DELORES', 'M', 'QUINTANA', 'C01', '4578', \
'1971-07-28','ANALYST', 16, 'F', '1925-09-15', 23800.00, \
500,1904)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000140','HEATHER', 'A', 'NICHOLLS', 'C01', '1793', \
'1976-12-15','ANALYST', 18, 'F', '1946-01-19', 28420.00, \
600,2274)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000150','BRUCE', ' ', 'ADAMSON', 'D11', '4510', \
'1972-02-12','DESIGNER', 16, 'M', '1947-05-17', 25280.00, \
500,2022)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000160','ELIZABETH', 'R', 'PIANKA', 'D11', '3782', \
'1977-10-11','DESIGNER', 17, 'F', '1955-04-12', 22250.00, \
400,1780)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000170','MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', \
'1978-09-15','DESIGNER', 16, 'M', '1951-01-05', 24680.00, \
500,1974)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000180','MARILYN', 'S', 'SCOUTTEN', 'D11', '1682', \
'1973-07-07','DESIGNER', 17, 'F', '1949-02-21', 21340.00, \
500,1707)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000190','JAMES', 'H', 'WALKER', 'D11', '2986', \
'1974-07-26','DESIGNER', 16, 'M', '1952-06-25', 20450.00, \
400,1636)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000200','DAVID', ' ', 'BROWN', 'D11', '4501', \
'1966-03-03','DESIGNER', 16, 'M', '1941-05-29', 27740.00, \
600,2217)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000210','WILLIAM', 'T', 'JONES', 'D11', '0942', \
'1979-04-11','DESIGNER', 17, 'M', '1953-02-23', 18270.00, \
400,1462)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000220','JENNIFER', 'K', 'LUTZ', 'D11', '0672', \
'1968-08-29','DESIGNER', 18, 'F', '1948-03-19', 29840.00, \
600,2387)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000230','JAMES', 'J', 'JEFFERSON', 'D21', '2094', \
'1966-11-21','CLERK', 14, 'M', '1935-05-30', 22180.00, \
400,1774)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780',\
'1979-12-05','CLERK', 17, 'M', '1954-03-31', 28760.00, \
600,2301)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961',\
'1969-10-30','CLERK', 15, 'M', '1939-11-12', 19180.00, \
400,1534)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953',\
'1975-09-11','CLERK', 16, 'F', '1936-10-05', 17250.00, \
300,1380)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000270', 'MARIA', 'L', 'PEREZ', 'D21', '9001',\
'1980-09-30','CLERK', 15, 'F', '1953-05-26', 27380.00, \
500,2190)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997',\
'1967-03-24','OPERATOR', 17, 'F', '1936-03-28', 26250.00, \
500,2100)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000290', 'JOHN', 'R', 'PARKER', 'E11', '4502',\
'1980-05-30','OPERATOR', 12, 'M', '1946-07-09', 15340.00, \
300,1227)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000300', 'PHILIP', 'X', 'SMITH', 'E11', '2095',\
'1972-06-19','OPERATOR', 14, 'M', '1936-10-27', 17750.00, \
400,1420)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332',\
'1964-09-12','OPERATOR', 12, 'F', '1931-04-21', 15900.00, \
300,1272)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990',\
'1965-07-07','FILEREP', 16, 'M', '1932-08-11', 19950.00, \
400,1596)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000330', 'WING', ' ', 'LEE', 'E21', '2103',\
'1976-02-23','FILEREP', 14, 'M', '1941-07-18', 25370.00, \
500,2030)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698', \
'1947-05-05','FILEREP', 16, 'M', '1926-05-17', 23840.00, \
500,1907)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200010', 'DIAN', 'J', 'HEMMINGER', 'A00', '3978', \
'1965-01-01','SALESREP', 18, 'F', '1933-08-14', 46500.00, \
1000,4220)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200120', 'GREG', '', 'ORLANDO', 'A00', '2167', \
'1972-05-05','CLERK', 14, 'M', '1942-10-18', 29250.00, \
600,2340)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200140', 'KIM','N', 'NATZ', 'C01', '1793', \
'1976-12-15','ANALYST', 18, 'F', '1946-01-19', 28420.00, \
600,2274)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200170', 'KIYOSHI', '', 'YAMAMOTO', 'D11', '2890',\
'1978-09-15','DESIGNER', 16, 'M', '1951-01-05', 24680.00, \
500,1974)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200220', 'REBA', 'K', 'JOHN', 'D11', '0672', \
'1968-08-29','DESIGNER', 18, 'F', '1948-03-19', 29840.00, \
600,2387)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200240', 'ROBERT', 'M', 'MONTEVERDE', 'D21', '3780', \
'1979-12-05','CLERK', 17, 'M', '1954-03-31', 28760.00, \
600,2301)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200280', 'EILEEN', 'R', 'SCHWARTZ', 'E11', '8997',\
'1967-03-24','OPERATOR', 17, 'F', '1936-03-28', 26250.00, \
500,2100)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200310', 'MICHELLE', 'F', 'SPRINGER', 'E11', '3332', \
'1964-09-12','OPERATOR', 12, 'F', '1931-04-21', 15900.00, \
300,1272)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200330', 'HELENA', '', 'WONG', 'E21', '2103', \
'1976-02-23','FIELDREP', 14, 'F', '1941-07-18', 25370.00, \
500,2030)" );
execi(
"INSERT INTO EMPLOYEE VALUES( \
'200340', 'ROY', 'R', 'ALONZO', 'E21', '5698', \
'1947-05-05','FIELDREP', 16, 'M', '1926-05-17', 23840.00, \
500,1907)" );
execi(
"ALTER TABLE DEPARTMENT \
ADD FOREIGN KEY RDE (MGRNO) \
REFERENCES EMPLOYEE \
ON DELETE SET NULL" );
/*********************************************************************/
/* */
/* PROJECT */
/* */
/*********************************************************************/
execi(
"CREATE TABLE PROJECT \
(PROJNO CHAR(6) NOT NULL, \
PROJNAME VARCHAR(24) NOT NULL DEFAULT '', \
DEPTNO CHAR(3) NOT NULL, \
RESPEMP CHAR(6) NOT NULL, \
PRSTAFF DECIMAL(5,2), \
PRSTDATE DATE, \
PRENDATE DATE, \
MAJPROJ CHAR(6), \
PRIMARY KEY (PROJNO))" );
execi(
"ALTER TABLE PROJECT \
ADD FOREIGN KEY (DEPTNO) \
REFERENCES DEPARTMENT \
ON DELETE RESTRICT" );
execi(
"ALTER TABLE PROJECT \
ADD FOREIGN KEY (RESPEMP) \
REFERENCES EMPLOYEE \
ON DELETE RESTRICT" );
execi(
"ALTER TABLE PROJECT \
ADD FOREIGN KEY RPP (MAJPROJ) \
REFERENCES PROJECT \
ON DELETE CASCADE" );
execi(
"CREATE INDEX XPROJ2 \
ON PROJECT (RESPEMP)" );
execi(
"CREATE ALIAS PROJ FOR PROJECT" );
/*********************************************************************/
/* */
/* PROJECT */
/* */
/*********************************************************************/
execi(
"INSERT INTO PROJECT VALUES( \
'AD3100', 'ADMIN SERVICES', 'D01', '000010', \
6.50, '1982-01-01', '1983-02-01',NULL)" );
execi(
"INSERT INTO PROJECT VALUES( \
'AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', \
6.00, '1982-01-01', '1983-02-01','AD3100')" );
execi(
"INSERT INTO PROJECT VALUES( \
'AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', \
2.00, '1982-01-01', '1983-02-01','AD3110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', \
1.00, '1982-01-01', '1983-02-01','AD3110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', \
2.00, '1982-01-01', '1983-02-01','AD3110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'IF1000', 'QUERY SERVICES', 'C01', '000030', \
2.00, '1982-01-01', '1983-02-01',NULL)" );
execi(
"INSERT INTO PROJECT VALUES( \
'IF2000', 'USER EDUCATION', 'C01', '000030', \
1.00, '1982-01-01', '1983-02-01',NULL )" );
execi(
"INSERT INTO PROJECT VALUES( \
'MA2100', 'WELD LINE AUTOMATION', 'D01', '000010', \
12.00, '1982-01-01', '1983-02-01',NULL )" );
execi(
"INSERT INTO PROJECT VALUES( \
'MA2110', 'W L PROGRAMMING', 'D11', '000060', \
9.00, '1982-01-01', '1983-02-01','MA2100')" );
execi(
"INSERT INTO PROJECT VALUES( \
'MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', \
2.00, '1982-01-01', '1982-12-01','MA2110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'MA2112', 'W L ROBOT DESIGN', 'D11', '000150', \
3.00, '1982-01-01', '1982-12-01','MA2110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'MA2113', 'W L PROD CONT PROGS', 'D11', '000160', \
3.00, '1982-02-15', '1982-12-01','MA2110')" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP1000', 'OPERATION SUPPORT', 'E01', '000050', \
6.00, '1982-01-01', '1983-02-01',NULL )" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP1010', 'OPERATION', 'E11', '000090', \
5.00, '1982-01-01', '1983-02-01','OP1000')" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', \
5.00, '1982-01-01', '1983-02-01',NULL )" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', \
4.00, '1982-01-01', '1983-02-01','OP2000')" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP2011', 'SCP SYSTEMS SUPPORT', 'E21', '000320', \
1.00, '1982-01-01', '1983-02-01','OP2010')" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', \
1.00, '1982-01-01', '1983-02-01','OP2010')" );
execi(
"INSERT INTO PROJECT VALUES( \
'OP2013', 'DB/DC SUPPORT', 'E21', '000340', \
1.00, '1982-01-01', '1983-02-01','OP2010')" );
execi(
"INSERT INTO PROJECT VALUES( \
'PL2100', 'WELD LINE PLANNING', 'B01', '000020', \
1.00, '1982-01-01', '1982-09-15','MA2100')" );
/*********************************************************************/
/* */
/* PROJACT */
/* */
/*********************************************************************/
execi(
"CREATE TABLE PROJACT \
(PROJNO CHAR(6) NOT NULL, \
ACTNO SMALLINT NOT NULL, \
ACSTAFF DECIMAL(5,2), \
ACSTDATE DATE NOT NULL, \
ACENDATE DATE , \
PRIMARY KEY (PROJNO, ACTNO, ACSTDATE))" );
execi(
"ALTER TABLE PROJACT \
ADD FOREIGN KEY RPAP (PROJNO) \
REFERENCES PROJECT \
ON DELETE RESTRICT" );
/*********************************************************************/
/* */
/* PROJACT */
/* */
/*********************************************************************/
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3100', 10, '1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3110', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 60,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 60,'1982-03-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 70,'1982-03-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 80,'1982-04-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 180,'1982-10-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 70,'1982-02-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3111', 80,'1982-09-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 60,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 60,'1982-02-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 60,'1983-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 70,'1982-02-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 70,'1982-03-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 70,'1982-08-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 80,'1982-08-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 80,'1982-10-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3112', 180,'1982-08-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 70,'1982-06-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 70,'1982-07-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 80,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 80,'1982-03-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 180,'1982-03-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 180,'1982-04-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 180,'1982-06-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 60,'1982-03-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 60,'1982-04-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 60,'1982-09-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 70,'1982-09-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'AD3113', 70,'1982-10-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000', 10,'1982-06-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000', 90,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF1000', 100,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000', 100,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000', 100,'1982-03-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000', 110,'1982-03-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'IF2000', 110,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2100', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2100', 20,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2110', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111', 50,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111', 60,'1982-06-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2111', 40,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112', 60,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112', 180,'1982-07-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112', 70,'1982-06-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112', 70,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2112', 80,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113', 60,'1982-07-15')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113', 80,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113', 70,'1982-04-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113', 80,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'MA2113', 180,'1982-10-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1000', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1010', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP1010', 130,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2010', 10,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2011', 140,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2011',150,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2012', 140,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2012', 160,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2013', 140,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'OP2013', 170,'1982-01-01')" );
execi(
"INSERT INTO PROJACT(PROJNO, ACTNO, ACSTDATE) VALUES( \
'PL2100', 30,'1982-01-01')" );
/*********************************************************************/
/* */
/* EMPPROJACT (EMP_ACT is an ALIAS) */
/* */
/*********************************************************************/
execi(
"CREATE TABLE EMPPROJACT \
(EMPNO CHAR(6) NOT NULL, \
PROJNO CHAR(6) NOT NULL, \
ACTNO SMALLINT NOT NULL, \
EMPTIME DEC(5,2), \
EMSTDATE DATE, \
EMENDATE DATE)" );
execi(
"ALTER TABLE EMPPROJACT \
ADD FOREIGN KEY REPAPA (PROJNO, ACTNO, EMSTDATE) \
REFERENCES PROJACT \
ON DELETE RESTRICT" );
execi(
"CREATE ALIAS EMPACT FOR EMPPROJACT" );
execi(
"CREATE ALIAS EMP_ACT FOR EMPPROJACT" );
/*********************************************************************/
/* */
/* EMP_ACT */
/* */
/*********************************************************************/
execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'AD3100', 10, 0.5,'1982-01-01', '1982-07-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000070', 'AD3110', 10, 1.0,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111', 60, 1.0,'1982-01-01', '1982-03-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111', 60, 0.5,'1982-03-15', '1982-04-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111', 70, 0.5,'1982-03-15', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111', 80, 0.5,'1982-04-15', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000230', 'AD3111', 180, 0.5,'1982-10-15', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000240', 'AD3111', 70, 1.0,'1982-02-15', '1982-09-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000240', 'AD3111', 80, 1.0,'1982-09-15', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 60, 1.0,'1982-01-01', '1982-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 60, 0.5,'1982-02-01', '1982-03-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 60, 1.0,'1983-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 70, 0.5,'1982-02-01', '1982-03-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 70, 1.0,'1982-03-15', '1982-08-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 70,0.25,'1982-08-15', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 80,0.25,'1982-08-15', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 80,0.50,'1982-10-15', '1982-12-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000250', 'AD3112', 180,0.50,'1982-08-15', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 70,0.50,'1982-06-15', '1982-07-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 70,1.00,'1982-07-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 80,1.00,'1982-01-01', '1982-03-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 80,0.50,'1982-03-01', '1982-04-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 180,0.50,'1982-03-01', '1982-04-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 180,1.00,'1982-04-15', '1982-06-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000260', 'AD3113', 180,1.00,'1982-06-01', '1982-07-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 60,0.50,'1982-03-01', '1982-04-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 60,1.00,'1982-04-01', '1982-09-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 60,0.25,'1982-09-01', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 70,0.75,'1982-09-01', '1982-10-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 70,1.00,'1982-10-15', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 80,1.00,'1982-01-01', '1982-03-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000270', 'AD3113', 80,0.50,'1982-03-01', '1982-04-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000030', 'IF1000', 10,0.50,'1982-06-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000130', 'IF1000', 90,1.00,'1982-10-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000130', 'IF1000', 100,0.50,'1982-10-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF1000', 90,0.50,'1982-10-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000030', 'IF2000',10,0.50,'1982-01-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000', 100,1.00,'1982-01-01', '1982-03-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000', 100,0.50,'1982-03-01', '1982-07-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000', 110,0.50,'1982-03-01', '1982-07-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000140', 'IF2000', 110,0.50,'1982-10-01', '1983-01-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'MA2100', 10,0.50,'1982-01-01', '1982-11-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000110', 'MA2100', 20,1.00,'1982-01-01', '1983-03-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000010', 'MA2110', 10,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000200', 'MA2111', 50,1.00,'1982-01-01', '1982-06-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000200', 'MA2111', 60,1.00,'1982-06-15', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000220', 'MA2111', 40,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000150', 'MA2112', 60,1.00,'1982-01-01', '1982-07-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000150', 'MA2112', 180,1.00,'1982-07-15', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2112', 60,1.00,'1982-01-01', '1983-06-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2112', 70,1.00,'1982-06-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000190', 'MA2112', 70,1.00,'1982-01-01', '1982-10-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000190', 'MA2112', 80,1.00,'1982-10-01', '1983-10-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000160', 'MA2113', 60,1.00,'1982-07-15', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000170', 'MA2113', 80,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000180', 'MA2113', 70,1.00,'1982-04-01', '1982-06-15')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000210', 'MA2113', 80,0.50,'1982-10-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000210', 'MA2113', 180,0.50,'1982-10-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000050', 'OP1000', 10,0.25,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000090', 'OP1010', 10,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000280', 'OP1010', 130,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000290', 'OP1010', 130,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000300', 'OP1010', 130,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000310', 'OP1010', 130,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000050', 'OP2010', 10,0.75,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000100', 'OP2010', 10,1.00,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000320', 'OP2011', 140,0.75,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000320', 'OP2011', 150,0.25,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000330', 'OP2012', 140,0.25,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000330', 'OP2012', 160,0.75,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000340', 'OP2013', 140,0.50,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000340', 'OP2013', 170,0.50,'1982-01-01', '1983-02-01')" );
execi(
"INSERT INTO EMP_ACT VALUES( \
'000020', 'PL2100',30,1.00,'1982-01-01', '1982-09-15')" );
/*********************************************************************/
/* */
/* ACT */
/* */
/*********************************************************************/
execi(
"CREATE TABLE ACT ( \
ACTNO SMALLINT NOT NULL, \
ACTKWD CHAR(6) NOT NULL, \
ACTDESC VARCHAR(20) NOT NULL, \
PRIMARY KEY (ACTNO) )" );
execi(
"CREATE UNIQUE INDEX XACT2 \
ON ACT (ACTKWD)" );
/*********************************************************************/
/* */
/* ACT */
/* */
/*********************************************************************/
execi(
"INSERT INTO ACT VALUES( \
10,'MANAGE','MANAGE/ADVISE')" );
execi(
"INSERT INTO ACT VALUES( \
20,'ECOST','ESTIMATE COST')" );
execi(
"INSERT INTO ACT VALUES( \
30,'DEFINE','DEFINE SPECS')" );
execi(
"INSERT INTO ACT VALUES( \
40,'LEADPR','LEAD PROGRAM/DESIGN')" );
execi(
"INSERT INTO ACT VALUES( \
50,'SPECS','WRITE SPECS')" );
execi(
"INSERT INTO ACT VALUES( \
60,'LOGIC','DESCRIBE LOGIC')" );
execi(
"INSERT INTO ACT VALUES( \
70,'CODE','CODE PROGRAMS')" );
execi(
"INSERT INTO ACT VALUES( \
80,'TEST','TEST PROGRAMS')" );
execi(
"INSERT INTO ACT VALUES( \
90,'ADMQS','ADM QUERY SYSTEM')" );
execi(
"INSERT INTO ACT VALUES( \
100,'TEACH','TEACH CLASSES')" );
execi(
"INSERT INTO ACT VALUES( \
110,'COURSE','DEVELOP COURSES')" );
execi(
"INSERT INTO ACT VALUES( \
120,'STAFF','PERS AND STAFFING')" );
execi(
"INSERT INTO ACT VALUES( \
130,'OPERAT','OPER COMPUTER SYS')" );
execi(
"INSERT INTO ACT VALUES( \
140,'MAINT','MAINT SOFTWARE SYS')" );
execi(
"INSERT INTO ACT VALUES( \
150,'ADMSYS','ADM OPERATING SYS')" );
execi(
"INSERT INTO ACT VALUES( \
160,'ADMDB','ADM DATA BASES')" );
execi(
"INSERT INTO ACT VALUES( \
170,'ADMDC','ADM DATA COMM')" );
execi(
"INSERT INTO ACT VALUES( \
180,'DOC','DOCUMENT')" );
execi(
"ALTER TABLE PROJACT \
ADD FOREIGN KEY RPAA (ACTNO) \
REFERENCES ACT \
ON DELETE RESTRICT" );
/*********************************************************************/
/* */
/* EMP_PHOTO */
/* */
/*********************************************************************/
execi(
"CREATE TABLE EMP_PHOTO ( \
EMPNO CHAR(6) NOT NULL, \
PHOTO_FORMAT VARCHAR(10) NOT NULL, \
PICTURE BLOB(100K), \
PRIMARY KEY (EMPNO,PHOTO_FORMAT))" );
execi(
"ALTER TABLE EMP_PHOTO \
ADD FOREIGN KEY (EMPNO) \
REFERENCES EMPLOYEE \
ON DELETE RESTRICT" );
/*********************************************************************/
/* */
/* EMP_PHOTO */
/* */
/*********************************************************************/
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000130', 'bitmap', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000130','gif', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000140','bitmap', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000140','gif', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000150','bitmap', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000150','gif', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000190','bitmap', null)" );
execi(
"INSERT INTO EMP_PHOTO VALUES( \
'000190','gif', null)" );
/*********************************************************************/
/* */
/* EMP_RESUME */
/* */
/*********************************************************************/
execi(
"CREATE TABLE EMP_RESUME ( \
EMPNO CHAR(6) NOT NULL, \
RESUME_FORMAT VARCHAR(10) NOT NULL, \
RESUME CLOB(5K), \
PRIMARY KEY (EMPNO,RESUME_FORMAT))" );
execi(
"ALTER TABLE EMP_RESUME \
ADD FOREIGN KEY (EMPNO) \
REFERENCES EMPLOYEE \
ON DELETE RESTRICT" );
/*********************************************************************/
/* */
/* EMP_RESUME */
/* */
/*********************************************************************/
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000130','ascii', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000130','html', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000140','ascii', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000140','html', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000150','ascii', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000150','html', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000190','ascii', null)" );
execi(
"INSERT INTO EMP_RESUME VALUES( \
'000190','html', null)" );
/*********************************************************************/
/* */
/* SALES */
/* */
/*********************************************************************/
execi(
"CREATE TABLE SALES \
(SALES_DATE DATE, \
SALES_PERSON VARCHAR(15), \
REGION VARCHAR(15), \
SALES INTEGER)" );
/*********************************************************************/
/* */
/* SALES */
/* */
/*********************************************************************/
execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LUCCHESSI','Ontario-South', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Ontario-South', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Quebec', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'LEE','Manitoba', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'12/31/1995', 'GOUNOT','Quebec', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LUCCHESSI','Ontario-South', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LUCCHESSI','Quebec', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Ontario-South', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Ontario-North', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Quebec', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'LEE','Manitoba', 5)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Ontario-South', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Quebec', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/29/1996', 'GOUNOT','Manitoba', 7)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Ontario-South', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Quebec', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LUCCHESSI','Manitoba', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Ontario-South', 7)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Ontario-North', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Quebec', 7)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'LEE','Manitoba', 4)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Ontario-South', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Quebec', 18)" );
execi(
"INSERT INTO SALES VALUES( \
'03/30/1996', 'GOUNOT','Manitoba', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LUCCHESSI','Manitoba', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Ontario-South', 14)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Ontario-North', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Quebec', 7)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'LEE','Manitoba', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'GOUNOT','Ontario-South', 2)" );
execi(
"INSERT INTO SALES VALUES( \
'03/31/1996', 'GOUNOT','Quebec', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LUCCHESSI','Ontario-South', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LUCCHESSI','Manitoba', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Ontario-South', 8)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Ontario-North',NULL)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Quebec', 8)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'LEE','Manitoba', 9)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Ontario-South', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Ontario-North', 1)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Quebec', 3)" );
execi(
"INSERT INTO SALES VALUES( \
'04/01/1996', 'GOUNOT','Manitoba', 7)" );
/*********************************************************************/
/* */
/* CL_SCHED */
/* */
/*********************************************************************/
execi(
"CREATE TABLE CL_SCHED ( \
CLASS_CODE CHAR(7), \
""DAY"" SMALLINT, \
STARTING TIME, \
ENDING TIME)" );
/*********************************************************************/
/* */
/* CL_SCHED */
/* */
/*********************************************************************/
execi(
"INSERT INTO CL_SCHED VALUES( \
'042:BF' ,4,'12:10:00', '14:00:00')" );
execi(
"INSERT INTO CL_SCHED VALUES( \
'553:MJA' ,1,'10:30:00', '11:00:00')" );
execi(
"INSERT INTO CL_SCHED VALUES( \
'543:CWM' ,3,'09:10:00', '10:30:00')" );
execi(
"INSERT INTO CL_SCHED VALUES( \
'778:RES' ,2,'12:10:00', '14:00:00')" );
execi(
"INSERT INTO CL_SCHED VALUES( \
'044:HD' ,3,'17:12:30', '18:00:00')" );
/*********************************************************************/
/* */
/* IN_TRAY */
/* */
/*********************************************************************/
execi(
"CREATE TABLE IN_TRAY \
(RECEIVED TIMESTAMP, \
SOURCE CHAR(8), \
SUBJECT CHAR(64), \
NOTE_TEXT VARCHAR(3000))" );
/*********************************************************************/
/* */
/* IN_TRAY */
/* */
/*********************************************************************/
execi(
"INSERT INTO IN_TRAY VALUES( \
'1988-12-25-17.12.30.000000' ,'BADAMSON', \
'FWD: Fantastic year! 4th Quarter Bonus.', \
'To: JWALKER \
Cc: QUINTANA, NICHOLLS \
\
Jim, \
Looks like our hard work has paid off. I have some good beer in the \
fridge if you want to come over to celebrate a bit. Delores and \
Heather, are you interested as well? \
\
Bruce \
\
<Forwarding from ISTERN> \
Subject: FWD: Fantastic year! 4th Quarter Bonus. \
To: Dept_D11 \
\
Congratulations on a job well done. Enjoy this year''s bonus. \
\
Irv \
\
<Forwarding from CHAAS> \
Subject: Fantastic year! 4th Quarter Bonus. \
To: All_Managers \
\
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays. \
\
Christine Haas')" );
execi(
"INSERT INTO IN_TRAY VALUES( \
'1988-12-23-08.53.58.000000' ,'ISTERN', \
'FWD: Fantastic year! 4th Quarter Bonus.', \
'To: Dept_D11 \
\
Congratulations on a job well done. Enjoy this year''s bonus. \
\
Irv \
\
<Forwarding from CHAAS> \
Subject: Fantastic year! 4th Quarter Bonus. \
To: All_Managers \
\
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays. \
\
Christine Haas')" );
execi(
"INSERT INTO IN_TRAY VALUES( \
'1988-12-22-14.07.21.136421' ,'CHAAS', \
'Fantastic year! 4th Quarter Bonus.', \
'To: All_Managers \
\
Our 4th quarter results are in. We pulled together as a team and \
exceeded our plan! I am pleased to announce a bonus this year of \
18%. Enjoy the holidays. \
\
Christine Haas')" );
/*********************************************************************/
/*********************************************************************/
/* */
/* Create views on the sample tables */
/* */
/*********************************************************************/
/*********************************************************************/
execi(
"CREATE VIEW VDEPT \
AS SELECT ALL DEPTNO, \
DEPTNAME, \
MGRNO, \
ADMRDEPT \
FROM DEPT" );
execi(
"CREATE VIEW VHDEPT \
AS SELECT ALL DEPTNO , \
DEPTNAME, \
MGRNO , \
ADMRDEPT, \
LOCATION \
FROM DEPT" );
execi(
"CREATE VIEW VEMP \
AS SELECT ALL EMPNO , \
FIRSTNME, \
MIDINIT , \
LASTNAME, \
WORKDEPT \
FROM EMP" );
execi(
"CREATE VIEW VPROJ \
AS SELECT ALL \
PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, \
PRSTDATE, PRENDATE, MAJPROJ \
FROM PROJ" );
execi(
"CREATE VIEW VACT \
AS SELECT ALL ACTNO , \
ACTKWD , \
ACTDESC \
FROM ACT" );
execi(
"CREATE VIEW VPROJACT \
AS SELECT ALL \
PROJNO,ACTNO, ACSTAFF, ACSTDATE, ACENDATE \
FROM PROJACT" );
execi(
"CREATE VIEW VEMPPROJACT \
AS SELECT ALL \
EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, EMENDATE \
FROM EMPPROJACT" );
execi(
"CREATE VIEW VDEPMG1 \
(DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT, \
LASTNAME, ADMRDEPT) \
AS SELECT ALL \
DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT, \
LASTNAME, ADMRDEPT \
FROM DEPT LEFT OUTER JOIN EMP \
ON MGRNO = EMPNO" );
execi(
"CREATE VIEW VEMPDPT1 \
(DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT, \
LASTNAME, WORKDEPT) \
AS SELECT ALL \
DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1), MIDINIT, \
LASTNAME, WORKDEPT \
FROM DEPT RIGHT OUTER JOIN EMP \
ON WORKDEPT = DEPTNO" );
execi(
"CREATE VIEW VASTRDE1 \
(DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2, \
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) \
AS SELECT ALL \
D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT, \
D1.LASTNAME, '1', \
D2.DEPTNO,D2.DEPTNAME,D2.MGRNO,D2.FIRSTNME,D2.MIDINIT, \
D2.LASTNAME \
FROM VDEPMG1 D1, VDEPMG1 D2 \
WHERE D1.DEPTNO = D2.ADMRDEPT" );
execi(
"CREATE VIEW VASTRDE2 \
(DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2, \
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) \
AS SELECT ALL \
D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT, \
D1.LASTNAME,'2', \
D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT, \
E2.LASTNAME \
FROM VDEPMG1 D1, EMP E2 \
WHERE D1.DEPTNO = E2.WORKDEPT" );
execi(
"CREATE VIEW VPROJRE1 \
(PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT, \
LASTNAME,MAJPROJ) \
AS SELECT ALL \
PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT, \
LASTNAME,MAJPROJ \
FROM PROJ, EMP \
WHERE RESPEMP = EMPNO" );
execi(
"CREATE VIEW VPSTRDE1 \
(PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN, \
PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN) \
AS SELECT ALL \
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
P1.LASTNAME, \
P2.PROJNO,P2.PROJNAME,P2.RESPEMP,P2.FIRSTNME,P2.MIDINIT, \
P2.LASTNAME \
FROM VPROJRE1 P1, \
VPROJRE1 P2 \
WHERE P1.PROJNO = P2.MAJPROJ" );
execi(
"CREATE VIEW VPSTRDE2 \
(PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN, \
PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN) \
AS SELECT ALL \
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
P1.LASTNAME, \
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT, \
P1.LASTNAME \
FROM VPROJRE1 P1 \
WHERE NOT EXISTS \
(SELECT * FROM VPROJRE1 P2 \
WHERE P1.PROJNO = P2.MAJPROJ)" );
execi(
"CREATE VIEW VFORPLA \
(PROJNO,PROJNAME,RESPEMP,PROJDEP,FRSTINIT,MIDINIT,LASTNAME) \
AS SELECT ALL \
F1.PROJNO,PROJNAME,RESPEMP,PROJDEP, SUBSTR(FIRSTNME, 1, 1), \
MIDINIT, LASTNAME \
FROM VPROJRE1 F1 LEFT OUTER JOIN EMPPROJACT F2 \
ON F1.PROJNO = F2.PROJNO" );
execi(
"CREATE VIEW VSTAFAC1(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, \
LASTNAME, EMPTIME,STDATE,ENDATE, TYPE) \
AS SELECT ALL \
PA.PROJNO, PA.ACTNO, AC.ACTDESC,' ', ' ', ' ', ' ', \
PA.ACSTAFF, PA.ACSTDATE, \
PA.ACENDATE,'1' \
FROM PROJACT PA, ACT AC \
WHERE PA.ACTNO = AC.ACTNO" );
execi(
"CREATE VIEW VSTAFAC2 \
(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME, \
EMPTIME,STDATE, ENDATE, TYPE) \
AS SELECT ALL \
EP.PROJNO, EP.ACTNO, AC.ACTDESC, EP.EMPNO,EM.FIRSTNME, \
EM.MIDINIT, EM.LASTNAME, EP.EMPTIME, EP.EMSTDATE, \
EP.EMENDATE,'2' \
FROM EMPPROJACT EP, ACT AC, EMP EM \
WHERE EP.ACTNO = AC.ACTNO AND EP.EMPNO = EM.EMPNO" );
execi(
"CREATE VIEW VPHONE \
(LASTNAME, \
FIRSTNAME, \
MIDDLEINITIAL, \
PHONENUMBER, \
EMPLOYEENUMBER, \
DEPTNUMBER, \
DEPTNAME) \
AS SELECT ALL LASTNAME, \
FIRSTNME, \
MIDINIT , \
VALUE(PHONENO,' '), \
EMPNO, \
DEPTNO, \
DEPTNAME \
FROM EMP, DEPT \
WHERE WORKDEPT = DEPTNO" );
execi(
"CREATE VIEW VEMPLP \
(EMPLOYEENUMBER, \
PHONENUMBER) \
AS SELECT ALL EMPNO , \
PHONENO \
FROM EMP" );
/* Disconnect Database */
rc = DbDisconnect();
/* attach to a local or remote instance */
rc = InstanceAttach(nodeName, user, pswd);
if (rc != 0)
{
return rc;
}
/* The next function will disconnect all the applications from all */
/* the databases located in the instance you are attached to. */
/* Uncomment the next function if this is acceptable. */
/* rc = AllApplicationsConnectedToAllDatabasesForceOff(); */
/* detach from the local or remote instance */
rc = InstanceDetach(nodeName);
if (rc != 0)
{
return rc;
}
return rc;
} /* end main */
int DbConnect(char dbAlias[], char user[], char pswd[])
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CONNECT TO\n");
printf("TO CONNECT TO A DATABASE.\n");
/* connect to a database */
printf("\n Execute the statement\n");
printf(" CONNECT TO %s\n", dbAlias);
if (strlen(user) > 0)
{
EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
EMB_SQL_CHECK("database -- connect with userid and password");
}
else
{
EXEC SQL CONNECT TO :dbAlias;
EMB_SQL_CHECK("Database -- Connect");
}
return 0;
} /* DbConnect */
int DbRestart(char dbAlias[], char user[], char pswd[])
{
struct sqlca sqlca;
struct db2RestartDbStruct dbRestartParam;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 APIs:\n");
printf(" db2DatabaseRestart -- RESTART DATABASE\n");
printf("TO RESTART A DATABASE.\n");
/* restart a database */
dbRestartParam.piDatabaseName = dbAlias;
dbRestartParam.piUserId = user;
dbRestartParam.piPassword = pswd;
dbRestartParam.piTablespaceNames = NULL;
printf("\n Restart a database.\n");
printf(" database alias: %s\n", dbAlias);
/* restart database */
db2DatabaseRestart(db2Version710, &dbRestartParam, &sqlca);
DB2_API_CHECK("Database -- Restart");
return 0;
} /* DbRestart */
int DbDisconnect(void)
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" CONNECT RESET");
printf("\nTO DISCONNECT FROM THE CURRENT DATABASE.\n");
/* disconnect from the database */
printf("\n Execute the statement\n");
printf(" CONNECT RESET\n");
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("Database -- Disconnect");
return 0;
} /* DbDisconnect */
int AllApplicationsConnectedToAllDatabasesForceOff(void)
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 APIs:\n");
printf(" sqlefrce -- FORCE APPLICATION\n");
printf("TO FORCE OFF ALL THE APPLICATIONS CONNECTED TO ALL DATABASES.\n");
/* force off all the appl. connected to all databases */
printf("\n Force off all applications connected to all databases.\n");
/* force application */
sqlefrce(SQL_ALL_USERS, NULL, SQL_ASYNCH, &sqlca);
DB2_API_CHECK("DBM Config. -- Set");
return 0;
} /* AllApplicationsConnectedToAllDatabasesForceOff */
/*********************************************************************/
/* Function: execi */
/* Description: Execute an SQL statement */
/*********************************************************************/
int execi (char s[1000]) {
strcpy(sqlstmt.stmt, s); /* Copy the string to the */
/* varying length structure */
sqlstmt.len = strlen(sqlstmt.stmt);
/* Copy the length to the */
/* varying length structure */
/* Note that this assumes */
/* that we will not need a */
/* hex 00 in the string in */
/* this program */
EXEC SQL
EXECUTE IMMEDIATE :sqlstmt;
/* Execute the statement */
EMB_SQL_CHECK("SQL Check");
EXEC SQL COMMIT;
EMB_SQL_CHECK("COMMIT");
printf("\nRunning execi: %s", s);
return 0; /* Return to the mainline */
}
int PackageCreate(char *sourceFileBaseName)
{
struct sqlca sqlca;
char bndFileName[50 + 1 + 3 + 1];
char msgFileName[20 + 1 + 3 + 1];
struct sqlopt *pBindOptions;
struct sqloptions *optionsArray;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" sqlabndx -- BIND\n");
printf("TO CREATE A PACKAGE.\n");
/* create the package */
strcpy(bndFileName, sourceFileBaseName);
strcat(bndFileName, ".bnd");
/* strcpy(msgFileName, sourceFileBaseName);*/
strcpy(msgFileName, "dbsample");
/* msgFileName[6] = '\0';*/ /* truncate to 6 characters */
strcat(msgFileName, "_b.msg"); /* _b = bind */
printf("\n Create the package.\n");
printf(" bnd file name : %s\n", bndFileName);
printf(" package name : %s\n", sourceFileBaseName);
printf(" message file name: %s\n", msgFileName);
pBindOptions = (struct sqlopt *)malloc(sizeof(struct sqlopt) +
(2 - 1) * sizeof(struct sqloptions));
/* for 2 options one more struct sqloptions is needed because */
/* struct sqlopt already contains one */
pBindOptions->header.allocated = 2; /* number of options */
pBindOptions->header.used = 2;
optionsArray = pBindOptions->option;
optionsArray[0].type = SQL_BLOCK_OPT;
optionsArray[0].val = SQL_BL_ALL; /* cursor blocking = block all */
optionsArray[1].type = SQL_ISO_OPT;
optionsArray[1].val = SQL_CURSOR_STAB; /* isol. level = cursor stab. */
/* invoke the bind utility */
sqlabndx(bndFileName, msgFileName,NULL, &sqlca);
/*sqlabndx(bndFileName, msgFileName, pBindOptions, &sqlca);*/
DB2_API_CHECK("Package -- Create");
/* free the memory allocated */
free(pBindOptions);
return 0;
} /* PackageCreate */
int DbConnectCheck(char dbAlias[], char user[], char pswd[])
{
struct sqlca sqlca;
/*Check if the database exist*/
/* connect to a database */
if (strlen(user) > 0)
{
EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
}
else
{
EXEC SQL CONNECT TO :dbAlias;
if(sqlca.sqlcode < 0)
{
return 1;
}
}
return 0;
}