Do My Database and System Administrator Assignment

Question - Fundamentals of DB2 for z/OS Lab Exercise #1 (With hints) ------------------------- SYSTEM ADMINISTRATION -------------------------- 1)The address spaces for a DB2 for z/OS subsystem are established using started tasks. Locate the started tasks / address spaces for our DB2 subsystem (DB9G). In which order were the address spaces created? Which address space(s) are required? Which address space(s) are optional? The dataset DSN910.NEW.SDSNSAMP contains sample batch jobs used to in-stall DB2 version 9. One of the jobs, DSNTIJUZ, sets the subsystem parameters (DSNZPARMS) and the programming defaults (DSNHDECP). a)How much space is allocated to the EDM pool? b)How many locks can be set concurrently on each tablespace? c)Who is the inst ...Read More all sysadm for the subsystem? d)How many archive logs will be tracked by the BSDS? e)How many copies of each active log will be maintained? f)How often will checkpoints be performed? g)How many concurrent remote connections will be supported? h)Which default date format will be used by programs? contents of the batch job member.

Each active log in a DB2 subsystem is supported by a data set on DASD. How many active log data sets are there for our subsystem? b)What are the names of these active log data sets? c)How big is each log data set? DSN910, find the keyword LOG. Each archive log in a DB2 subsystem is supported by a data set, either on DASD or on some other storage media. How many archive log files are stored on DASD for our subsys-tem? b)What are the names of these archive log data sets? DSN910, find the keyword ARC. 5)Every DB2 subsystem needs a Boot Strap Data Set (BSDS), and usually main-tains dual copies. Does our DB2 subsystem use one or two BSDSs? b)What is/are the data set names used by the BSDS(s)? c)How big is/are the BSDS(s)? DSN910, find the string BSDS.

managed. You will use this stogroup when creating tablespaces and indexspaces. -- DATABASE -- •The name of the database should be xxx330D, where xxx are your initials. •The default bufferpool for data should be BP1. •The default bufferpool for indexes should be BP2. •The default storage group should be NCRT330S. Need a hint? BUFFERPOOL BP1 INDEXBP BP2 STOGROUP NCRT330S; 2. Query the SYSDATABASE catalog table to verify information about your database. What is the name of your database? b)Who was the creator of your database? c)What storage group does your database use by default? d)Which data bufferpool does your database use by default? e)Which index bufferpool does your database use by default? FROM SYSIBM.SYSDATABASE WHERE NAME = ‘SPB330D’;

3. Query the SYSDATABASE catalog table to locate the system catalog, system directo-ry, and system default databases. What is the name of the system catalog database? b)What is the name of the system directory database? c)What is the name of the system default database? d)Are there any implicitly created databases on our system? e)Which of these databases is not recorded in the system catalog? SELECT NAME, CREATOR, STGROUP, BPOOL, INDEXBP FROM SYSIBM.SYSDATABASE WHERE CREATOR = ‘SYSIBM’;

-- TABLESPACE -- The name of the tablespace should be TSCOURSE. •There should be 4 pages per segment. •The tablespace should belong to your database (xxx330D). IN SPB330D SEGSIZE 4; 4. Create a Universal partition-by-growth tablespace with the following attributes: •The name of the tablespace should be TSCLASS. •The tablespace should be allowed to grow to a maximum of 10 partitions. •Each dataset supporting the tablespace should be 1 gigabyte; •There should be 32 pages per segment. •The tablespace should belong to your database (xxx330D). IN SPB330D MAXPARTITIONS 10 SEGSIZE 32 DSSIZE 1G; 5. Create a Universal range-partitioned tablespace with the following attributes: The name of the tablespace should be TSSTU. •The tablespace should contain 3 partitions. •Each segment should contain 4 pages. •The tablespace should belong to your database (xxx330D). Need a hint?

IN SPB330D SEGSIZE 4 NUMPARTS 3;

6. Create a partitioned tablespace with the following attributes: The name of the tablespace should be TSINST. •The tablespace should contain 2 partitions. •The tablespace should belong to your database (xxx330D). IN SPB330D NUMPARTS 2; 7. Create a segmented tablespace with the following attributes: The name of the tablespace should be TSROST. •Each segment should contain 8 pages. •Each page should initially be left 25% empty. •An empty page should be left after every 4 used pages. •The tablespace should belong to your database (xxx330D). IN SPB330D SEGSIZE 8 PCTFREE 25 FREEPAGE 4; -lowing questions about your tablespaces: Which database contains your tablespaces? 2.Who was the creator of your tablespaces? 3.Which bufferpool will be used by your tablespaces? 4.What VCAT name will be used by your tablespaces? 5.For tablespace TSCOURSE: a.How many partitions does the tablespace contain? b.What is the page size for the tablespace? c.How many pages will be allocated per segment? d.How many tables are contained in the tablespace? e.What percentage of each page in the tablespace will be kept free during a reload?

f.How often will free pages be allocated in the tablespace during a reload? g.What is/are the fully qualified name(s) of the dataset(s) containing the ta-blespace? 6.For tablespace TSCLASS: a.What is the type of the tablespace? b.How many partitions does the tablespace contain? c.How many partitions can the tablespace contain? d.How many pages will be allocated per segment? e.What is/are the fully qualified name(s) of the dataset(s) containing the ta-blespace? 7.For tablespace TSSTU: a.What is the type of the tablespace? b.How many partitions does the tablespace contain? c.How many pages will be allocated per segment? d.What is/are the fully qualified name(s) of the dataset(s) containing the ta-blespace? 8.For tablespace TSINST: a.What is the type of the tablespace? b.How many partitions does the tablespace contain? c.How many pages will be allocated per segment? d.What is/are the fully qualified name(s) of the dataset(s) containing the ta-blespace? 9.For tablespace TSROST: a.How many pages will be allocated per segment? b.What percentage of each page will be left free during a reload? c.How often will a free page be left in the tablespace during a reload? d.What is/are the fully qualified name(s) of the dataset(s) containing the ta-blespace? 10.Locate the actual datasets being used by your tablespaces. Need a hint? PARTITIONS, MAXPARTITIONS, PGSIZE, STATUS, NTABLES, SEGSIZE FROM SYSIBM.SYSTABLESPACE WHERE CREATOR = USER; FREEPAGE, PCTFREE FROM SYSIBM.SYSTABLEPART WHERE DBNAME = ‘SPB330D’;

SPB330D” to locate your datasets.

-- TABLE -- 9. Create a table with the following attributes: The name of the table should be ROSTER. •You will be the owner/creator of the table. •The table should be stored in your TSROST tablespace. •The table should include columns with the following attributes: (CLASS BIGINT NOT NULL, STUDENT CHAR(5) NOT NULL, GRADE CHAR(2) ) IN SPB330D.TSROST; The name of the table should be CLASS. •You will be the owner/creator of the table. •The table should be stored in your TSCLASS tablespace. •The table should include columns with the following attributes: Data TypeNullabilityDescriptionCLASSBIG INTEGERNOClass numberSTUDENTCHAR(5)NOStudent numberGRADECHAR(2)YESStudent gradeColumn NameData TypeNullableDescriptionCLASSNOBIG INTEGERNOClass numberCOURSENOCHAR(8)NOCourse number

Need a hint? (CLASSNO BIGINT NOT NULL, COURSENO CHAR(8) NOT NULL, INSTRUCTOR INTEGER , STARTDATE DATE NOT NULL, ENDDATE DATE NOT NULL, CONSTRAINT CK_DATES CHECK (ENDDATE >= STARTDATE)) IN SPB330D.TSCLASS; The name of the table should be COURSE. •You will be the owner/creator of the table. •The table should be stored in your TSCOURSE tablespace. •The table should include columns with the following attributes: (COURSENO CHAR(8) NOT NULL, INSTRUCTORINTEGERYESInstructor numberSTARTDATEDATENOStart dateENDDATEDATENOEnd date, must be greater than or equal to start dateColumn NameData TypeNullableDescriptionCOURSENOCHAR(8)NOCourse numberTITLEVARCHAR(40)NOCourse titleCRHRSDECIMAL(6,2)NOCredit hours, must be greater than or equal to zero, default zero

TITLE VARCHAR(40) NOT NULL, CRHRS DECIMAL(6,2) NOT NULL DEFAULT 0, CONSTRAINT CK_CRHRS CHECK (CRHRS >= 0 )) IN SPB330D.TSCOURSE;

12. Create a table with the following attributes: The name of the table should be STUDENT. •You will be the owner/creator of the table. •The table should be stored in your TSSTU tablespace. •The rows in the table should be allocated to the partitions of the TSSTU table-space as follows: oStudents ‘00001’ through ‘09999’ in Part 1 oStudents ‘10000’ through ‘19999’ in Part 2 oStudents ‘20000’ through ‘99999’ in Part 3 •The table should include columns with the following attributes: (STUNO CHAR(5) NOT NULL, LNAME VARCHAR(30) NOT NULL, FNAME VARCHAR(20) NOT NULL, CITY VARCHAR(40) NOT NULL, STATE CHAR(2) NOT NULL) PARTITION BY (STUNO ASC) (PARTITION 1 ENDING AT (‘09999’), PARTITION 2 ENDING AT (‘19999’), PARTITION 3 ENDING AT (‘99999’)) IN SPB330D.TSSTU; Data TypeNullableDescriptionSTUNOCHAR(5)NOStudent id numberLNAMEVARCHAR(30)NOStudent last nameFNAMEVARCHAR(20)NOStudent first nameCITYVARCHAR(40)NOStudent home citySTATECHAR(2)NOStudent home state

The name of the table should be INSTRUCTOR. •You will be the owner/creator of the table. •The table should be stored in your TSINST tablespace. •The rows in the table should be allocated to the partitions of the TSINST ta-blespace as follows: oInstructor numbers 00001 – 14999 in partition 1. oInstructor numbers 15000 – 29999 in partition 2. •The table should include columns with the following attributes: Need a hint? (INSTNO INTEGER NOT NULL, LNAME VARCHAR(40) NOT NULL, FNAME VARCHAR(40) NOT NULL, SALARY DECIMAL(11,2) NOT NULL, CONSTRAINT CK_SALARY CHECK (SALARY > 0)) PARTITION BY (INSTNO ASC) (PARTITION 1 ENDING AT (14999), PARTITION 2 ENDING AT (29999)) IN SPB330D.TSINST; Data TypeNullableDescriptionINSTNOINTEGERNOInstructor numberLNAMEVARCHAR(40)NOInstructor last nameFNAMEVARCHAR(40)NOInstructor first nameSALARYDECIMAL (11,2)NOInstructor salary, must be greater than zero

SYSCHECKS, and SYSCHECKDEP catalog tables to answer the following questions about your tables: In which tablespace is each of your tables stored? 2.How many columns are in each table? 3.What is the record length for each table? 4.How many check constraints are on each table? 5.What is the status of each of your tablespaces? 6.Does each of your tablespaces contain the expected number of tables? 7.What is the last column in each of your tables? 8.How many columns in each table are nullable? 9.What are the names of your check constraints? 10.Which columns are involved in each of your check constraints? 11.Which of your tables are partitioned? 12.For each partitioned table, what are the upper boundary limits of each partition? SELECT NAME, TYPE, DBNAME, TSNAME, COLCOUNT, RECLENGTH, CHECKS FROM SYSIBM.SYSTABLES WHERE CREATOR = USER; FROM SYSIBM.SYSTABLESPACE WHERE CREATOR = USER; FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = USER ORDER BY TBNAME, COLNO; CHECKCONDITION FROM SYSIBM.SYSCHECKS WHERE TBOWNER = USER; LIMITKEY_INTERNAL FROM SYSIBM.SYSTABLEPART WHERE DBNAME = ‘SPB330D’;

-- INDEX -- Need a hint? ON ROSTER (CLASS,STUDENT) CLUSTER; ON CLASS (CLASSNO) CLUSTER; ON COURSE (COURSENO) CLUSTER; ON STUDENT (STUNO) PARTITIONED CLUSTER; Table NameIndex NameKey Column(s)UniqueClusteringPartitionedROSTERIDX1_ROSTERCLASS,STUDENTYESYESNOCLASSIDX1_CLASSCLASSNOYESYESNOCOURSEIDX1_COURSECOURSENOYESYESNOSTUDENTIDX1_STUDENTSTUNOYESYESYESINSTRUCTORIDX1_INSTRINSTNOYESYESYESROSTERIDX2_ROSTERSTUDENTNONONOCLASSIDX2_CLASSCOURSENONONONOCLASSIDX3_CLASSINSTRUCTORNONONO

ON INSTRUCTOR (INSTNO) PARTITIONED CLUSTER; ON ROSTER (STUDENT); ON CLASS (COURSENO); ON CLASS (INSTRUCTOR); -swer the following questions about indexes: a.What does UNIQUERULE P indicate? What does UNIQUERULE D indi-cate? b.How many indexspaces did DB2 create? How were they named? c.Which bufferpool will be used by your indexes? d.What percentage of each leaf page will initially be left free in your indexes? e.How many partitions does the IDX1_INSTR index have? How many parti-tions does the IDX1_STUDENT index have? f.Which ordering sequence is used for all of the indexes? INDEXSPACE, BPOOL FROM SYSIBM.SYSINDEXES WHERE CREATOR = USER ORDER BY NAME; FROM SYSIBM.SYSINDEXPART WHERE IXCREATOR = USER ORDER BY IXNAME, PARTITION; FROM SYSIBM.SYSKEYS

WHERE IXCREATOR = USER ORDER BY IXNAME, COLSEQ;

-- KEY CONSTRAINT -- Need a hint? ADD CONSTRAINT PK_ROSTER PRIMARY KEY (CLASS, STUDENT); ADD CONSTRAINT PK_CLASS PRIMARY KEY (CLASSNO); ADD CONSTRAINT PK_COURSE PRIMARY KEY (COURSENO); ADD CONSTRAINT PK_STUDENT PRIMARY KEY (STUNO); ADD CONSTRAINT PK_INSTRUCTOR PRIMARY KEY (INSTNO); Table NamePrimary Key Column(s)Constraint NameROSTERCLASS, STUDENTPK_ROSTERCLASSCLASSNOPK_CLASSCOURSECOURSENOPK_COURSESTUDENTSTUNOPK_STUDENTINSTRUCTORINSTNOPK_INSTRUCTOR

18. Define foreign keys for your tables, as follows: ADD CONSTRAINT FK_CLR FOREIGN KEY (CLASS) REFERENCES CLASS ON DELETE RESTRICT; ADD CONSTRAINT FK_SR FOREIGN KEY (STUDENT) REFERENCES STUDENT ON DELETE RESTRICT; ADD CONSTRAINT FK_COCL FOREIGN KEY (COURSENO) REFERENCES COURSE ON DELETE RESTRICT; ADD CONSTRAINT FK_ICL FOREIGN KEY (INSTRUCTOR) REFERENCES INSTRUCTOR ON DELETE RESTRICT; -EIGNKEYS catalog tables to answer the following questions about primary keys and for-eign keys: Child Table NameForeign Key Column(s)Constraint NameDelete RuleCLASSROSTERCLASSFK_CLRRESTRICTSTUDENTROSTERSTUDENTFK_SRRESTRICTCOURSECLASSCOURSENOFK_COCLRESTRICTINSTRUCTORCLASSINSTRUCTORFK_ICLRESTRICT

1.Which kind of constraint (primary / foreign) is recorded in the SYSTABCONST table? 2.Do these constraints require indexes? 3.Which kind of constraint (primary / foreign) is recorded in the SYSRELS table? 4.Do these constraints require indexes? 5.Which table contains information on the columns used for primary keys? 6.Which table contains information on the columns used for foreign keys? Need a hint? FROM SYSIBM.SYSTABCONST WHERE TBCREATOR = USER; FROM SYSIBM.SYSKEYCOLUSE WHERE TBCREATOR = USER; FROM SYSIBM.SYSRELS WHERE CREATOR = USER; FROM SYSIBM.SYSFOREIGNKEYS WHERE CREATOR = USER; LNAMEFNAME SALARY900SmithToni1000001010We s tSusan12000014092AlverezTara13000015330MartinLarry87000

CLASS OlsonDenise5900020041MarquetteAlan140000COURSENOTITLECRHRSDB01INTRODUCTION TO DB23.0DB02MANAGING AND OPTOMIZING DB23.0DB03INTRODUCTION TO SQL2.0DB04PERFORMANCE TUNING4.0CLASSNOCOURSENOINSTRUCTORSTARTDATEENDDATE20080001DB0190009-07-200812-11-200820080002DB01101009-07-200812-11-200820080003DB021533009-07-200812-11-200820080004DB021409209-07-200812-11-200820090005DB0301-11-200905-07-200920090006DB031593001-11-200905-07-200920090007DB042004101-11-200905-07-200920090008DB0490001-11-200905-07-2009STUNOLNAMEFNAMECITYSTATE

VALUES (900, ‘Smith’, ‘Toni’, 100000); INSERT INTO INSTRUCTOR 00001GLOVERJANEDENVERCO10390CARVERJOHNCHICAGOIL32849NELSENSUESAN JOSECA21100ANDERSONBETHNEWARKNJ93903DAVIESBOBMIAMIFL13902ALLBEEJOEAT L A N TAGA03394KARTERFREDDALLASTX89090MILTONMARYSEATTLEWACLASSSTUDENTGRADE2008000110390B2008000113902A2008000103394C2008000189090B20090006328492009000613902200900060339420090006000012009000610390

VALUES (1010, ‘West’, ‘Susan’, 120000); INSERT INTO INSTRUCTOR VALUES (14092, ‘Alvarez’, ‘Tara’, 130000); INSERT INTO INSTRUCTOR VALUES (15330, ‘Martin’, ‘Larry’, 87000); INSERT INTO INSTRUCTOR VALUES (15930, ‘Olson’, ‘Denise’, 59000); INSERT INTO INSTRUCTOR VALUES (20041, ‘Marquette’, ‘Alan’, 140000); INSERT INTO COURSE VALUES (‘DB01’,’INTRODUCTION TO DB2’,3.0); INSERT INTO COURSE VALUES (‘DB02’,’MANAGING AND OPTOMIZING DB2’,3.0); INSERT INTO COURSE VALUES (‘DB03’,’INTRODUCTION TO SQL’,2.0); INSERT INTO COURSE VALUES (‘DB04’,’PERFORMANCE TUNING’,4.0); INSERT INTO CLASS VALUES (20080001, ‘DB01’, 900, ’2008-09-07’, ’2008-12-11’); INSERT INTO CLASS VALUES (20080002, ‘DB01’, 1010, ’2008-09-07’, ’2008-12-11’); INSERT INTO CLASS VALUES (20080003, ‘DB02’, 15330, ’2008-09-07’, ’2008-12-11’); INSERT INTO CLASS VALUES (20080004, ‘DB02’, 14092, ’2008-09-07’, ’2008-12-11’); INSERT INTO CLASS VALUES (20090005, ‘DB03’, NULL, ’2009-01-11’, ’2009-05-07’); INSERT INTO CLASS VALUES (20090006, ‘DB03’, 15930, ’2009-01-11’, ’2009-05-07’); INSERT INTO CLASS VALUES (20090007, ‘DB04’, 20041, ’2009-01-11’, ’2009-04-07’); INSERT INTO CLASS VALUES (20090008, ‘DB04’, 900, ’2009-01-11’, ’2009-05-07’); INSERT INTO STUDENT VALUES(‘00001’,’GLOVER’,’JANE’,’DENVER’,’CO’); INSERT INTO STUDENT VALUES(‘10390’,’CARVER’,’JOHN’,’CHICAGO’,’IL’);

INSERT INTO STUDENT VALUES(‘32849’,’NELSEN’,’SUE’,’SAN JOSE’,’CA’); INSERT INTO STUDENT VALUES(‘21100’,’ANDERSON’,’BETH’,’NEWARK’,’NJ’); INSERT INTO STUDENT VALUES (‘93903’,’DAVIES’,’BOB’,’MIAMI’,’FL’); INSERT INTO STUDENT VALUES(‘13902’,’ALLBEE’,’JOE’,’ATLANTA’,’GA’); INSERT INTO STUDENT VALUES(‘03394’,’KARTER’,’FRED’,’DALLAS’,’TX’); INSERT INTO STUDENT VALUES(‘89090’,’MILTON’,’MARY’,’SEATTLE’,’WA’); VALUES (20080001,’10390’,’B’); INSERT INTO ROSTER VALUES(20080001,’13902’,’A’); INSERT INTO ROSTER VALUES (20080001,’03394’,’C’); INSERT INTO ROSTER VALUES (20080001,’89090’,’B’); INSERT INTO ROSTER VALUES (20090006,’32849’,NULL); INSERT INTO ROSTER VALUES(20090006,’13902’,NULL); INSERT INTO ROSTER VALUES (20090006,’03394’,NULL); INSERT INTO ROSTER VALUES (20090006,’00001’,NULL); INSERT INTO ROSTER VALUES (20090006,’10390’,NULL);

21. Attempt to insert the following data into your tables. Explain the errors that you re-ceive – WHY did you get the error? CLASS STUDENT ROSTER LNAMEFNAME SALARY03994MILLERJEAN ANN0COURSENOTITLECRHRSDB03OPEN SYSTEMS3.0CLASSNOCOURSENOINSTRUCTORSTARTDATEENDDATE20090009DB01101009-07-200812-11-200720090010DB0590009-07-200812-11-200820090011DB0293409-07-200812-11-2008STUNOLNAMEFNAMECITYSTATE12930THOMASMARINECLASSSTUDENTGRADE

Need a hint? VALUES (03994,’MILLER’,’JEAN ANN’,0); VALUES (‘DB03’,’OPEN SYSTEMS’,3.0) ; VALUES (20090009,’DB01’,1010,’2008-09-07’,’2007-12-11’); INSERT INTO CLASS VALUES (20090010,’DB05’,900,’2008-09-07’,’2008-12-11’); INSERT INTO CLASS VALUES (20090001,’DB02’,934,’2008-09-07’,’2008-12-11’); INSERT INTO STUDENT VALUES (‘12930’,’THOMAS’,’MARI’,NULL,’NE’); VALUES (20080001,’99300’,’B’); INSERT INTO ROSTER VALUES (20080010,’00001’,’A’); 22. Attempt to delete the following data from your tables. Explain the errors that you receive – WHY did you get the error? WHERE STUNO = ‘10390’; 2008000199300B2008001000001A

23. Attempt to update the following data in your tables. Explain the errors that you re-ceive – WHY did you get the error? SET INSTRUCTOR = 20042 WHERE CLASSNO = 2009007; 24. Create SELECT statements to produce the following reports. Display the lastname, firstname, instructor number, and salary of each instruc-tor, sorted by firstname within lastname. b.Display the course number and title of all 3 credit hour courses. c.Display the class number, start date, end date, and instructor’s name for every scheduled DB01 class, sorted by class number. d.Display the sum of the salaries of all instructors. e.Display the number of students in each class that has a roster. f.Display the class number, course number, title, instructor number, and instruc-tor name for all currently running classes. g.Display a grade report listing student names and grades for class 20080001, sorted by student name. Need a hint? FROM INSTRUCTOR ORDER BY LNAME, FNAME; FROM COURSE WHERE CRHRS = 3.0; SELECT CLASSNO, STARTDATE, ENDDATE, LNAME, FNAME FROM CLASS, INSTRUCTOR

WHERE CLASS.INSTRUCTOR = INSTRUCTOR.INSTNO AND COURSENO = ‘DB01’ ORDER BY CLASSNO; FROM INSTRUCTOR; SELECT CLASS, COUNT(*) AS NUM_STUDENTS FROM ROSTER GROUP BY CLASS ORDER BY CLASS;

SELECT CLASSNO, CLASS.COURSENO, SUBSTR(TITLE,1,15) AS TITLE, INSTRUCTOR, LNAME || ', ' || FNAME AS INST_NAME FROM CLASS, COURSE, INSTRUCTOR WHERE CLASS.ENDDATE >= CURRENT DATE AND CLASS.STARTDATE ...Read Less

Solution Preview - No Solution Preview Available

Original Question Documents

N/A