Скачать презентацию Introduction to DB 2 Course Objective
ee9554d685fc1cd2eff3e53bfdc0d1de.ppt
- Количество слайдов: 79
Скачать презентацию Introduction to DB 2 Course Objective
Скачать презентацию Introduction to DB 2 Course Objective
Introduction to DB 2
Course Objective • To illustrate DB 2 Architecture • To explain DB 2 Objects – Database, Table space, Table, Index • Datatypes DB 2 support • Integrity – Foreign keys • Security – Views – Grant and Revoke Copyright © 2005, Infosys Technologies Ltd 2 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Course Objective • Embedded SQL • Program Preparation – Precompile, Bind – DBRM's, Plans, and Packages – Compile a Cobol-DB 2 Program • Concurrency and utilities Copyright © 2005, Infosys Technologies Ltd 3 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Course Plan • Day 1: Overview of DB 2 • Day 2: SPUFI, DCLGEN & Embedded SQL • Day 3: Program Preparation and Execution • Day 4: OLTP Issues • Day 5: Project Evaluation and Final Test Copyright © 2005, Infosys Technologies Ltd 4 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Prerequisites • MVS and TSO • Programming in COBOL • RDBMS Concepts • Working knowledge of SQL • OLTP Concepts Copyright © 2005, Infosys Technologies Ltd 5 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Session Plan • To illustrate DB 2 Architecture • To explain DB 2 Objects – Database, Tablespace, Table, Index • Datatypes DB 2 support • Integrity – Foreign keys • Security – Views – Grant and Revoke Copyright © 2005, Infosys Technologies Ltd 6 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
RDBMS Review • Database Management System • Database Models • Relational Database Management System Copyright © 2005, Infosys Technologies Ltd 7 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Introduction to DB 2 • What is DB 2? – A subsystem of the MVS operating system – An abbreviation for ‘IBM Database 2’ – Was announced in June 1983 – Supports SQL (Structured Query Language) Copyright © 2005, Infosys Technologies Ltd 8 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
System Architecture • Major Components of DB 2 – SSAS (System Services Address Space) • Thread creation, Program tracing, Logging – DBAS (Database Services Address Space) • Execution of SQLs, Database objects management, buffer management, Data read/write, etc. – IRLM (IMS Resource Lock Manager) • Locking Copyright © 2005, Infosys Technologies Ltd 9 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
System Architecture Major Components of DB 2 – DDF (Distributed data facility component ) is optional • Distributed Database functionality – SPAS (Stored Procedure Address Space) • For the execution of the stored procedures • Each of these components runs in a separate address space and is made up of numerous sub-components. Copyright © 2005, Infosys Technologies Ltd 10 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
DB 2 System Architecture DSNMSTR DSNDBM 1 IRLMPROC DSNDDF SSAS DBAS IRLM DDF (Optional) Database functions Locking Distributed Requests Logging Attachment co-ordination Buffering Free Space MVS Common Area Copyright © 2005, Infosys Technologies Ltd 11 Free Space MVS Common Area ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
System services component- (Contd. . ) • Handles DB 2 startup and shutdown. • Control and co-ordinate connections to other MVS subsystems such as CICS, IMS and TSO. • Manages the System log. The system log is a set of datasets that used to record information about every transaction. • Is responsible for establishing and maintaining all the threads for DB 2. Copyright © 2005, Infosys Technologies Ltd 12 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
System services component - (Contd. . ) • Has a sub-component called Instrumentation facility. • The instrumentation facility gathers statistical information about the work done by DB 2 at user specified intervals of time. • This information is either – Written to a System Management facility (SMF) or Generalized Trace Facility (GTF) dataset or – Passed to a performance monitor program provided some third party vendor. Copyright © 2005, Infosys Technologies Ltd 13 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Database services component • Is responsible for execution of SQL statements and management of Buffer pools. • Comprises of 3 major sub-components Viz. 1. Relational Data System (RDS) 2. Data Manager (DM) and 3. Buffer Manager (BM) • The RDS manages Stage 2 predicates , does Auth check , SQL statement checking , Sorting and Optimizer • The DM is responsible for managing the data at the physical level. Does Stage 1 predicates , Indexable predicates and DML • The DM invokes other system components for performing functions such as locking, logging, etc. , Copyright © 2005, Infosys Technologies Ltd 14 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Database services component - (Contd. . ) • The BM manages all the Buffer pools required by DB 2 for database operations. It keeps the frequently used pages in the buffer. • The BM is responsible for opening and closing all the datasets used by application data and work areas. • The BM instructs the Data Facility Product (DFP) to do a physical I/O as and when required. Copyright © 2005, Infosys Technologies Ltd 15 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
STAGE 1 PREDICATES COLUMN_NAME operator VALUE COLUMN_NAME IS NULL COLUMN_NAME BETWEEN val 1 AND val 2 COLUMN_NAME IN List COLUMN_NAME LIKE pattern COLUMN_NAME LIKE : Host-variable A. COLUMN_NAME 1 operator B. COLUMN_NAME 2 COLUMN_NAME Operator (non correlated sub query) COLUMN_NAME Operator (non column expression) Copyright © 2005, Infosys Technologies Ltd 16 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
STAGE 1 / 2 PREDICATES CORRELATED SELECT EMP, LASTNAME FROM EMP A WHERE EXISTS (SELECT 1 FROM DEPT B WHERE A. DEPTNO=B. DEPTNO AND DEPTDESC=‘EDUCATION’) NON CORRELATED SELECT EMP, LASTNAME FROM EMP A WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DEPTDESC=‘EDUCATION’) Copyright © 2005, Infosys Technologies Ltd 17 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
FROM RDS to DM to BM and back -- > SQL Relational Data System Optimized SQL Read Buffer Or Request Data Manager Results Apply Stage 2 Predicates & Sort Data Apply Stage 1 Predicates Buffer Manager Copyright © 2005, Infosys Technologies Ltd VSAM Media Manager DATA 18 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Locking Services Component • The locking services are provided by a component called Integrated Resource Lock Manager (IRLM). • The IRLM takes care of all the concurrency control issues. Copyright © 2005, Infosys Technologies Ltd 19 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Distributed Data Facility Component • Provides distributed database functionality. • Is an optional component. Copyright © 2005, Infosys Technologies Ltd 20 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Threads • Are memory structures used by DB 2 to communicate with an application program. • Serves as links between DB 2 and application programs. • Application programs send all requests to DB 2 through threads. • DB 2 communicates the status of each SQL statement to the application using threads through SQLCA. Copyright © 2005, Infosys Technologies Ltd 21 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Threads CICS PGM Call Attach pgm IMS/DC PGM TSO O/L PGM DB 2 Utility TSO Batch pgm DB 2 QMF Or DB 2 I IMS Batch pgm Copyright © 2005, Infosys Technologies Ltd 22 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
DB 2 Objects DATABASE TABLESPACE TABLE INDEX SYNONYM VIEW Copyright © 2005, Infosys Technologies Ltd 23 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
DB 2 Objects Hierarchy STOGROUP DATABASE TABLESPACE VIEW ALIAS SYNONYM TABLE INDEX COLUMN Copyright © 2005, Infosys Technologies Ltd 24 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Storage Groups • Are a collection of one or more (maximum of 133) DASD volumes of the same type. • Provides space for storing all the datasets that DB 2 uses. • Are created by System administrator as shown below. CREATE STOGROUP STOUDB 6 VOLUMES (DAVP 7 C, DAVP 8 C, DAVP 9 E) PASSWORD infosys; Copyright © 2005, Infosys Technologies Ltd 25 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Storage Groups - (Contd. . ) • The number volumes in a storage group can be changed dynamically as shown below. ALTER STOGROUP STOUDB 6 ADD VOLUMES (DAVP 9 F, DAVP 8 C, DAVP 9 E) REMOVE VOLUMES (DAVP 7 C, DAVP 8 C) PASSWORD infosys; Copyright © 2005, Infosys Technologies Ltd 26 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Database • Is a collection of one or more Tablespaces and Index spaces. • Generally every application will have a unique database. This simplifies administrative tasks and also improves application performance. • One DB 2 system can manage up to 65, 279 databases. Copyright © 2005, Infosys Technologies Ltd 27 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating a Database • To create a database use CREATE DATABASE EMPDB; Maximum 8 characters name CREATE DATABASE DBENR STOGROUP STOUDB 6 BUFFERPOOL (BP 0, BP 32); • To remove a database use DROP DATABASE DBENR; • To change the definition use ALTER DATABASE EMPDB ALTER DATABASE DBENR ROSHARE {OWNER, NONE} STOGROUP STOUDB 6; Copyright © 2005, Infosys Technologies Ltd 28 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Database Descriptor (DBD) • Is a DB 2 component that is created whenever a database is created. • Stores control and descriptive information about every object in the corresponding database. • Whenever a database is in use, the associated DBD is buffered. • To maintain database integrity the DBD is locked whenever an object is being created, updated or deleted in the corresponding database. • Instead of accessing DB 2 catalog for object information , DBD which is housed in DB 2 directory is accessed which is more efficient. Copyright © 2005, Infosys Technologies Ltd 29 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Tablespace • A Tablespace is one or more VSAM datasets • Three types – Segmented TS (default) – Simple TS – Partitioned TS (for large databases) Copyright © 2005, Infosys Technologies Ltd 30 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating a TS • Created under an existing DB using CREATE TABLESPACE EMPTS IN EMPDB PRIQTY 10000 SECQTY 1000 PCTFREE 10 FREEPAGE 63 LOCKSIZE ANY BUFFERPOOL BP 0 SEGSIZE 64 Copyright © 2005, Infosys Technologies Ltd 31 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
More on TS • To remove a tablespace use DROP TABLESPACE EMPTS • To change the definition use ALTER TABLESPACE EMPTS ALTER TABLESPACE DBENR. ENR PRIQTY 200 SECQTY 200 ERASE YES LOCKSIZE ANY BUFFERPOOL BP 1; Copyright © 2005, Infosys Technologies Ltd 32 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Simple TS • Can house one or more tables • There is no limit for number of tables • Rows from multiple tables can be interleaved on a page Copyright © 2005, Infosys Technologies Ltd 33 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Segmented TS • Can house one or more tables • TS is divided into segments of 4 to 64 pages in increments of 4 • One segment contains data from exactly one table • One table can occupy many segments • Good for performance and this is DB 2 default Copyright © 2005, Infosys Technologies Ltd 34 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Partitioned tablespace • allows a table to be divided by rows into partitions • Each partition can be placed on different storage devices Copyright © 2005, Infosys Technologies Ltd 35 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Table • A tablespace within a database can have any number of tables • Create table using CREATE TABLE EMPLOYEE (EMP_NO EMP_NAME SMALLINT NOT NULL, CHAR(15), EMP_ADDRESS VARCHAR(25) NOT NULL WITH DEFAULT, PRIMARY KEY (EMP_NO)); Copyright © 2005, Infosys Technologies Ltd 36 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
More on Table • To remove a table DROP TABLE EMPLOYEE • To change the definition use ALTER TABLE EMPLOYEE For example, to add a new field ALTER TABLE EMPLOYEE ADD EMP_SALARY INTEGER Copyright © 2005, Infosys Technologies Ltd 37 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Data types Data Type String Character Datetime Graphic Date Timestamp Numeric Time Integer Fixed Length Decimal Variable Length Small Copyright © 2005, Infosys Technologies Ltd Large 38 Single ER/CORP/CRS/DB 01/003 Version No: 2. 0 b Floating Point Double
Data types- (Contd. . ) • Integer – 4 bytes (5 if nullable) – PIC S 9(9) COMP • Smallint – 2 bytes (3 if nullable) – PIC S 9(4) COMP Copyright © 2005, Infosys Technologies Ltd 39 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Data types- (Contd. . ) • Char(n) – max. 254 bytes – PIC X(n) • Varchar(n) – max. 4046 bytes – A structure containing PIC S 9(4) COMP for length and PIC X(n) for the data Copyright © 2005, Infosys Technologies Ltd 40 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Data types- (Contd. . ) • Time – 3 bytes (4 if nullable) – PIC X(8) • Date – 4 bytes (5 if nullable) – PIC X(10) • Timestamp – 10 bytes (11 if nullable) – PIC X(26) Copyright © 2005, Infosys Technologies Ltd 41 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Nulls • DB 2 adds an extra byte to all nullable columns • This extra byte has the information whether the field contains NULL or not • The NULL values do not participate while taking AVERAGE, SUM, etc. • Need to have special care while inserting, updating, or retrieving nullable fields in the host language program Copyright © 2005, Infosys Technologies Ltd 42 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
WITH DEFAULT • DB 2 puts the default value of the data type in that field while inserting a record • For character fields, spaces, for numeric fields, zeros, for date fields, current date for time fields, current time. . . and so on Copyright © 2005, Infosys Technologies Ltd 43 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Index • Is a structure used for faster retrieval of data. • Can be unique or non-unique. • In DB 2, we need to explicitly create a unique index for the primary key. • Is stored in B - Tree format. Copyright © 2005, Infosys Technologies Ltd 44 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Root Page Level 0 Non-Leaf Page 1 Level 1 100 200 300 400 Leaf Page 1 Level 2 10 20 30 40 DP 1 DP 2 DP 3 DP 4 Data Page 10 101 Roopa 19 102 Deeptha 20. . . . L 1 L 2 L 3 L 4 1000 2000 3000 4000 NL 1 NL 2 NL 3 NL 4 Index L 11 L 12 L 13 L 14 1101 DP 50. 1 1102 DP 50. 2. . . 101 DP 10. 1 102 DP 10. 2. . . 151 DP 20. 1 Data Page 50 Data Page 20 Technologies Ltd 1100 1200 1300 1400 Leaf Page 12 Level 2 Leaf Page 2 Level 2 151 Bhavana 20. . Copyright ©. . Infosys. . . . 2005, . . Non-Leaf Page 2 Level 1 45 1101 Vijay 22 1102 Harish 25 ER/CORP/CRS/DB 01/003. . . No: 2. 0 b. . . . Version. .
Index – (Contd…) • Root Page Only one root page is available per Index. It should exist at the highest level of the hierarchy. It can be structured as Leaf pages or Non leaf pages. • Non Leaf pages are intermediate-level Index pages in the b-tree hierarchy. It need not exist. If they exist they contain the pointers to the Leaf pages. • Leaf Pages Leaf pages contain pointers to the data rows of a table. Leaf page must always exist. In a single page Index, the root page is a Leaf page Copyright © 2005, Infosys Technologies Ltd 46 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Index – (Contd…) • Columns that are good for indexing 1. Primary key and Foreign key columns, 2. Column having unique values, 3. Columns that are frequently used in the WHERE clause and 4. Columns that frequently used in an ORDER BY, GROUP BY and DISTINCT clauses. • Columns that are not good for indexing 1. Frequently updated, 2. Longer than 30 characters and 3. Containing redundant values. Copyright © 2005, Infosys Technologies Ltd 47 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating Index 1 of 2 • DB 2 creates Index spaces for every index (one index space for one index) • Is a page set used to store information about one index. • Only one index per index space. • Index space pages are 4 k pages. • Contains value from indexed columns and an RID to the corresponding row. Copyright © 2005, Infosys Technologies Ltd 48 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating Index 2 of 2 • An index and its associated table must be in the same database • Index pages can be locked by sub page increments (1/2, 1/4, 1/8 and 1/16). The default is 1/4. • Often the response time will be slow due to Indexspace lock contention rather than Tablespace lock contention. In such cases the response time can be improved by increasing the value of the SUBPAGE parameter. • Indexes are created using CREATE [UNIQUE] INDEX EMPNOINDX ON EMPLOYEE(EMP_NO ASC) Copyright © 2005, Infosys Technologies Ltd 49 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
More on Index • To remove an index DROP INDEX EMPNOINDX • To change the definition use ALTER INDEX EMPNOINDX Copyright © 2005, Infosys Technologies Ltd 50 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Clustered index (1 of 2) • Data is physically ordered in the sequence of the index. • Only one clustering index per table. • Good for columns – used in BETWEEN , >, <, LIKE – used in GROUP BY, ORDER BY, DISTINCT – for PRIMARY and FOREIGN KEYS Copyright © 2005, Infosys Technologies Ltd 51 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Clustered index – (Contd…) CREATE INDEX CLUSTER_EMP_INX ON ENR. EMP (EMPNO ASC) PRIQTY 36 CLUSTER (PART 1 VALUES('H 99'), PART 2 VALUES('P 99'), PART 3 VALUES('Z 99'), PART 4 VALUES('999')) BUFFERPOOL BP 1; Copyright © 2005, Infosys Technologies Ltd 52 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Catalog Tables • When tables and other objects are created, DB 2 records all these information into a set of system tables called “catalog tables” • SYSDATABASE, SYSTABLESPACE, SYSTABLES, SYSCOLUMNS, SYSINDEXES, SYSVIEWS, etc. are examples • If you have authority, you can query on these tables as any other table Copyright © 2005, Infosys Technologies Ltd 53 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Synonyms and Aliases • Are used for creating alternate names for existing tables. • Synonyms can refer only to local tables. • Aliases can refer to both local as well as remote tables. In fact aliases were designed for distributed environment to avoid references to the location qualifier. • When a table is dropped all the synonyms get dropped automatically where as aliases on that table remains. Copyright © 2005, Infosys Technologies Ltd 54 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Synonyms and Aliases • CREATE SYNONYM EMP FOR EMPDB. EMPLOYEE • CREATE ALIAS EMP FOR BANGALORE. EMPDB. EMPLOYEE • Use DROP to drop these objects Copyright © 2005, Infosys Technologies Ltd 55 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Integrity Copyright © 2005, Infosys Technologies Ltd 56 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Referential Integrity • Suppose you want to make sure that all the employee numbers in PROJ_ALLOCATION(PROJ_ID, EMP_NO) are valid employees and valid projects (i. e. , they belong to EMPLOYEE(EMP_NO, EMP_NAME) and PROJECT(PROJ_ID, PROJ_NAME) master tables • You use referential integrity support DB 2 provides to enforce this (through definition of foreign keys) Copyright © 2005, Infosys Technologies Ltd 57 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Referential Integrity - Keys • Unique Key § A unique key is defined as a column (or set of columns) where no two values same. § The columns of a unique key cannot contain null values. § A table can have multiple unique keys. § Unique keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements. Copyright © 2005, Infosys Technologies Ltd 58 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Referential Integrity - Keys • Primary Key § A primary key is a unique key that is a part of the definition of the table. § A table cannot have more than one primary key, and the columns of a primary key cannot contain null values. § Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements. Copyright © 2005, Infosys Technologies Ltd 59 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Referential Integrity - Keys • Foreign Key A foreign key is a column (or set of columns) which is a primary key in another table. • Unique Constraint § § A unique constraint ensures that values of a key are unique within a table. Unique constraints are optional, and can be defined CREATE TABLE or ALTER TABLE statements by specifying the PRIMARY KEY or UNIQUE clause. For example, Unique constraint can be defined on the employee number column of a table to ensure that every employee has a unique number. § Copyright © 2005, Infosys Technologies Ltd 60 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating a Foreign key • CREATE TABLE PROJ_ALLOCATION (PROJ_ID EMP_NO CHAR(8) NOT NULL, SMALLINT NOT NULL, PRIMARY KEY(PROJ_ID, EMP_NO), FOREIGN KEY PROJ_FK(PROJ_ID) REFERENCES PROJECT(PROJ_ID), FOREIGN KEY EMP_FK(EMP_NO) REFERENCES EMPLOYEE(EMP_NO) ) Copyright © 2005, Infosys Technologies Ltd 61 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Delete Rules • What happens if one employee is deleted from employee master table? • DB 2 supports 3 delete rules – DELETE RESTRICT – DELETE CASCADE – DELETE SET NULL • When defining foreign keys give these rules FOREIGN KEY PROJ_FK(PROJ_ID) REFERENCES PROJECT(PROJ_ID) DELETE CASCADE Copyright © 2005, Infosys Technologies Ltd 62 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Referential Integrity Employee Table Invalid Record Foreign key Primary key Department Table Copyright © 2005, Infosys Technologies Ltd 63 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Foreign Key Rules – Delete on Cascade Department Table When dept No : 1000 is deleted then. . Employee Table The child records are also deleted automatically Copyright © 2005, Infosys Technologies Ltd 64 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Foreign Key Rules – Delete on Restrict Department Table When dept No : 1000 is deleted then. . It gives an error and cannot delete as there are child records for the parent record. Employee Table Copyright © 2005, Infosys Technologies Ltd 65 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Foreign Key Rules – Delete on SET NULL Department Table When dept No : 1000 is deleted then. . Employee Table Empname Dept No Tim Jim Tom 2000 Mary The child records foreign key values are set to Null Copyright © 2005, Infosys Technologies Ltd 66 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Self Referencing Tables • Consider this table EMPLOYEE(EMP_NO, PL_EMP_NO) • PL_EMP_NO is a foreign key referencing to the EMP_NO field of the same table • DB 2 does not allow creation of these foreign keys while creating tables • We need to use ALTER TABLE to add the foreign key constraint later Copyright © 2005, Infosys Technologies Ltd 67 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Security Copyright © 2005, Infosys Technologies Ltd 68 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Security • Security means the protection of the data in the data base against unauthorized disclosure, alteration or destruction. Copyright © 2005, Infosys Technologies Ltd 69 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Views • Unlike actual tables, views defined on a table are just definitions • DB 2 keeps all view definitions in SYSVIEWS catalog table • All the views depend on one or more tables or views • We can have views created using other views Copyright © 2005, Infosys Technologies Ltd 70 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Creating a View CREATE VIEW RICH_EMP AS SELECT EMP_NO, EMP_NAME FROM WHERE • EMPLOYEE SALARY > 25000 Now we can use SELECT EMP_NO, EMP_NAME FROM RICH_EMP in our program • Views provide – security, independence from the base tables Copyright © 2005, Infosys Technologies Ltd 71 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Updating using a View • DB 2 allows updation of only “simple” views • You can issue update statement as if you are updating an actual table • DB 2 does not allow you to update – views using joins – views using DISTINCT and aggregates – views using GROUP BY Copyright © 2005, Infosys Technologies Ltd 72 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
More on View • Like any other object, use DROP VIEW RICH_EMP to delete a view • When a table or a view is dropped all dependent views are automatically dropped • DB 2 does not allow you to create views with ORDER BY, FOR UPDATE OF, or UNION Copyright © 2005, Infosys Technologies Ltd 73 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
GRANT • Use GRANT keyword to grant permissions on database objects GRANT SELECT ON EMPLOYEE TO PUBLIC; GRANT UPDATE (EMP_ADDRESS) ON EMPLOYEE TO SMITH; GRANT INSERT, DELETE ON EMPLOYEE TO TRAINEES; • A table creator has implicit authority to – alter, drop, create a view/index, select/insert/update/delete Copyright © 2005, Infosys Technologies Ltd 74 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
REVOKE • Use REVOKE keyword to take away permissions on database objects REVOKE SELECT ON EMPLOYEE FROM PUBLIC; REVOKE UPDATE (EMP_ADDRESS) ON EMPLOYEE FROM SMITH; REVOKE INSERT, DELETE ON EMPLOYEE FROM TRAINEES; REVOKE ALL ON EMPLOYEE FROM PUBLIC; Copyright © 2005, Infosys Technologies Ltd 75 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
More on Permissions • You can GRANT a privilege to someone with GRANT option GRANT ALL ON EMPLOYEE TO PUBLIC WITH GRANT OPTION; • Suppose Jones grants select permission on view RICH_EMP to Smith with grant option; Smith in turn grants select permission to Adams on the same view; What happens if Jones revokes permission from Smith? Copyright © 2005, Infosys Technologies Ltd 76 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
BUNDLED PRIVILEGES • SYSDM • SYSCTRL • DBADM • DBCTRL • DBMAINT • SYSOPR Copyright © 2005, Infosys Technologies Ltd 77 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Summary • DB 2 Architecture • DB 2 Objects – Database, Tablespace, Table, Index • Datatypes DB 2 support • Integrity – Foreign keys • Security – Views – Grant and Revoke Copyright © 2005, Infosys Technologies Ltd 78 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Thank You! Copyright © 2005, Infosys Technologies Ltd 79 ER/CORP/CRS/DB 01/003 Version No: 2. 0 b
Скачать презентацию Introduction to DB 2 Course Objective
ee9554d685fc1cd2eff3e53bfdc0d1de.ppt