Wednesday, May 4, 2011

SQL: Database Objects


SQL: Database Objects


Unit 1. SQL Overview



This unit presents introductory information that is helpful while you're learning SQL — IBM's Structured Query Language.
In this unit, you will learn concepts about relational databases, and you will see the data used in this course. You will also be introduced to some general topics about SQL.

After completing this unit, you should be able to:
  • Describe how data is stored in a relational database

  • Recall the general format of the tables used in this course

  • Explain the functions of SQL and its three components


Topic 1.1: Relational Concepts

*Columns and Rows
In a relational database model, the data is stored in one or more tables.
Each table consists of a specific number of columns and some number of unordered rows.
One row of a sample table named STUDENT is displayed below.
Examine the following table
STUDENTNO FIRSTNAME MIDINIT LASTNAME MAJOR SEX
S10 GERALD A ATTRIX MATH M


*Example Table
Examine the following table
STUDENTNO FIRSTNAME MIDINIT LASTNAME MAJOR SEX
S10 GERALD A ATTRIX MATH M
S20 PHYLISS A LUTZ PHYSICS F
S30 DELBERT A SPRAY MATH M
S40 JAMES E SHUE    M
S50 BEVERLY A BOGGS HISTORY F
S60 DAVE I HICKS MATH M
S70 JACKIE I RICTER BUSINESS F
S80 MARGE E GILHOULY    F
S90 IRMA A SPRAY HOME ECON F

Above is the complete table. As you can see, the table consists of six columns: STUDENTNO, FIRSTNAME, MIDINIT, LASTNAME, MAJOR and SEX.
It also consists of nine rows, one for each student. The next section goes over this table and the others used in this course.


*Column Characteristics
Columns of a table have the following characteristics:
  • Appear vertically when displayed on a report
  • Contain the same kind of data
  • Have names which are used as column titles when they are displayed on a report
*Row Characteristics
Rows of a table have the following characteristics:
  • Appear horizontally when displayed on a report
  • Contain one or more columns of data
  • Unlike columns, they have no names


*How Large Can a Table Be?
The tables you work with in this course are relatively small for ease of use and reference. However, this does not mean you are limited to working with small tables.
A table can be virtually any number of rows with one or more columns.
There is a limitation, but it is unlikely to be reached under normal conditions.
A table with zero rows is called an empty table.


*The Relational Database Model
Relational databases are based on a model formulated by Dr. E. F. Codd of IBM in 1968. He used very formal terms to describe the concepts you just saw.
Here are the equivalent terms he used:
Examine the following table
Formal Relational Term Informal Equivalents
Relation Table
Tuple Row or record
Attribute Column or field

So you can see where the terms relational model and relational database come from. Tables were called relations.
In this course, you see the less formal terms — table, row, and column.

Topic 1.2: Course Tables

*Example Tables
Throughout this course several tables are used for examples and exercises. This section describes each and shows the table entries.
Throughout this course, the tables may be modified slightly to illustrate a point. Also, due to the size of the screen, only portions are displayed at times.

*The STUDENT Table
The STUDENT table contains student information. Each row describes one student. The columns are:
Examine the following table
STUDENTNO - student number FIRSTNAME - first name
MIDINIT - middle initial LASTNAME - last name
MAJOR - major subject SEX - M or F

Examine the following table
STUDENTNO FIRSTNAME MIDINIT LASTNAME MAJOR SEX
S10 GERALD A ATTRIX MATH M
S20 PHYLISS A LUTZ PHYSICS F
S30 DELBERT A SPRAY MATH M
S40 JAMES E SHUE    M
S50 BEVERLY A BOGGS HISTORY F
S60 DAVE I HICKS MATH M
S70 JACKIE I RICTER BUSINESS F
S80 MARGE E GILHOULY    F
S90 IRMA A SPRAY HOME ECON F


*The INSTRUCT Table
The INSTRUCT table contains information about instructors. Each row describes one instructor. The columns are:
Examine the following table
INSSN - social security no. DEPTNO - department no.
IFIRSTNAME - first name IMIDINIT - middle initial
ILASTNAME - last name SALARY - salary
EXEMPTIONS - no. of exemptions      

Examine the following table
INSSN     DEPTNO IFIRSTNAME IMIDINIT ILASTNAME SALARY EXEMPTIONS
334838999 MTH    ELIZABETH      A      SONS      35000       3      
393209494 MTH    ALYCE          P      SEEGERS   40000       3      
382949898 ENG    STEWART        I      GOTZ      31000       4      
893993999 ENG    JOHN           A      RABBET    41000       0      
998329399 HST    THOMAS         A      SEEGERS   40000       2      


*The GRADE Table
The GRADE table contains numeric and character data for grades. Each row is for one grade. The columns are:
GRADE    - character grade value         NUMGRADE - numeric grade value
Examine the following table
GRADE NUMGRADE
   A      4.0000  
   B      3.0000  
   C      2.0000  
   D      1.0000  
   F      0.0000  


*The CLASS Table
The CLASS table contains data about specific classes. Each row defines one class. The columns are:
CLASSNO   - class number         CLASSNAME - class name
CLASSCOST - cost for class         COURSEHRS - course hours
Examine the following table
CLASSNO CLASSNAME                  CLASSCOST   COURSEHRS
E01        ENGLISH 101                    500.00      4      
E02        ENGLISH 102                    450.00      3      
M01        MATH 101                       600.00      3      
M02        MATH 102                       700.00      3      
M03        MATH 103                       800.00      3      
P01        PHYSICS 101                    600.00      3      
H01        HISTORY                        510.00      4      


*The SECTION Table
The SECTION table contains information about each section that is taught for a specific class. Each row describes one section. The columns are:
Examine the following table
CLASSNO - class number SECTIONNO - section number
INSSN - instructor's social security no. ROOMNO - room number

Examine the following table
CLASSNO SECTIONNO INSSN       ROOMNO    
E01        001       893993999       301     
E01        002       893993999       302     
E02        001       382948398       205     
M01        001       334838999       389     
M02        001       334838999       239     
M02        002       393209494       222     
M03        001       393209494       203     
P01        001       393209494       105     
H01        001       998329399       333     


*The ROSTER Table
The ROSTER table contains data that identifies each student signed up for a specific section of a class. Each row describes one class for a student. The columns are:
Examine the following table
CLASSNO - class number SECTIONNO - section number
STUDENTNO - student number GRADE - grade

Examine the following table
CLASSNO SECTIONNO STUDENTNO GRADE
E01          001        S20         A   
E01          001        S30         B   
E01          001        S70         B   
E01          002        S80         A   
E01          002        S10         C   
E01          002        S40         B   
E02          001        S50         C   
E02          001        S60         A   
E02          001        S90         D   
M01          001        S40         A   
M01          001        S70         D   
M02          001        S90         B   
M02          001        S80         F   
M02          001        S10         C   
M02          001        S30         B   
M02          002        S20         C   
M02          002        S60         D   
M02          002        S80         A   
M02          002        S90         A   
M02          002        S50         F   
M03          001        S10         D   
M03          001        S40         D   
M03          001        S70         D   
M03          001        S60         C   
M03          001        S90         A   
H01          001        S10         C   
H01          001        S40         B   
H01          001        S70         B   
H01          001        S80         B   
H01          001        S30         B   
P01          001        S10         B   
P01          001        S20         B   
P01          001        S30         B   
P01          001        S40         B   
P01          001        S50         B   
P01          001        S60         B   
P01          001        S70         B   
P01          001        S80         B   
P01          001        S90         B   


Topic 1.3: SQL Concepts

*SQL Features
In this section, you will learn a little bit about the features and capabilities of SQL. This information provides a foundation for the following units which cover specific uses of SQL.

*What Is SQL?
SQL is a high-level language that allows you to access and maintain data stored in relational databases, such as DB2 and SQL/DS. Products like FOCUS, SAS, and Oracle, which create and maintain relational tables, also use SQL. SQL is one interface to products in these relational systems.
SQL is designed for programmers, system administrators and data processing professionals. Yet, it is especially nice for users who have little training in computer programming. With SQL you specify what you want rather than how to get it. You do not have to be concerned with how the data is stored, or how it is retrieved.

*Three Components
SQL is rapidly becoming the industry standard for fourth generation query languages. SQL is easy to learn and easy to use.
Power and simplicity are both features of SQL. You can use single SQL statements to perform functions that require many lines of conventional code.
SQL has these three basic components:
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Authorization Language




*Data Definition Language
DDL (Data Definition Language) is used to define new tables. The columns to be included in your table are defined using the CREATE statement. Here is a sample CREATE statement for the STUDENT table used in this course.
CREATE TABLE STUDENT
    (STUDENTNO CHAR(3) NOT NULL,
     FIRSTNAME VARCHAR(20),
     MIDINIT CHAR(1),
     LASTNAME VARCHAR(25) NOT NULL,
     MAJOR CHAR(15),
     SEX CHAR(1) NOT NULL)
As you can see, the table consists of 6 columns: STUDENTNO, FIRSTNAME, MIDINIT, LASTNAME, MAJOR and SEX.
The other entries will be discussed in a later unit.

*DDL Statements
DDL statements permit you to create, change, and delete various components of the relational database. In fact, you can define and modify the database online without special utility programs.
Thus, you can interactively
  • Create and manipulate tables
  • Test SQL statements you plan on using in programs
and not affect the ongoing processes and data already established in your database.



*Data Manipulation Language
DML (Data Manipulation Language) has two basic functions:
  • Creating reports
  • Maintaining tables
One of the simplest, and most common report requests is to view a complete table. All you need is the name of the table.
Let's look at a sample report request.

*Example DML Statement
For example, the statement below requests a report of all columns and all rows for the table named STUDENT.
SELECT * FROM STUDENT
Examine the following table
STUDENTNO FIRSTNAME MIDINIT LASTNAME MAJOR SEX
S10 GERALD A ATTRIX MATH M
S20 PHYLISS A LUTZ PHYSICS F
S30 DELBERT A SPRAY MATH M
S40 JAMES E SHUE    M
S50 BEVERLY A BOGGS HISTORY F
S60 DAVE I HICKS MATH M
S70 JACKIE I RICTER BUSINESS F
S80 MARGE E GILHOULY    F
S90 IRMA A SPRAY HOME ECON F


*The SELECT Statement
SELECT * is the simplest retrieval request. Other functions of the SELECT statement include:
  • Limiting the request to specific columns
  • Limiting the request to specific rows
  • Ordering the rows presented on the request
  • Developing new column values through calculations
  • Accumulating numeric column values within specific groupings
  • Combining data from two or more tables through a common key
Note that the SELECT statement is covered in other courses in this series. Information presented in those courses will not be repeated here.

*Table Maintenance
The other function of DML is to maintain tables.
Here's a simple request to create a new row in the STUDENT table.
INSERT INTO STUDENT
   VALUES ('S02', 'RALPH', 'A', 'LARSON'    , 'PHYSICS', 'M')
This request adds the new row containing the values listed in parentheses. You'll learn how to code this type of request later in the course.



*Authorization Language
The function of the Authorization Language is to provide effective control over the database resources.
Security in relational databases like DB2 and SQL/DS can be summarized in one sentence: Nothing can be done unless you have been given the authority.
Authority is controlled by the data administration area through SQL commands.

GRANT and REVOKE
Although you need authority to do anything with the database, it is a relatively simple matter to obtain it.
Authority is given through the GRANT command. For example, the command below gives user PROG1 authority to create a table in the database named SKILLS.
GRANT CREATETAB ON DATABASE SKILLS TO PROG1
Authority may also be removed with the REVOKE command. Here's an example removing CREATE authority for tables in database SKILLS from PROG1.
REVOKE CREATETAB ON DATABASE SKILLS FROM PROG1



*SQL Implementations
You can use SQL statements interactively; for example, via IBM's Query Management Facility (QMF) or an interactive facility provided with the relational database
You can also use SQL statements embedded in application programs, such as APL2, BASIC, COBOL, PL/I, FORTRAN, LISP, or assembler language programs
These various implementations of SQL, however, are semantically and syntactically the same. What you learn here can be carried to any SQL-based database management system.

*Syntax Rules
When coding SQL statements, follow these syntax rules:
  • Variables and keywords must be separated by one or more spaces.
  • Parentheses are often required around a keyword's values. Commas must separate values in a list (e.g. column names, table names, data values)
  • Spacing around commas, parentheses and operators (=,>,+,*,etc.) is for readability. Spaces are not required.
  • Character strings are sequences of characters that begin and end with a string delimiter. A string delimiter is either ' (apostrophe) or " (quotes) depending on the host language and the character chosen when the database system is installed. Use the same character (' or ") for the beginning and ending delimiter.


   INSERT INTO GRADE VALUES ('E01',0.000)
   INSERT INTO GRADE VALUES('E01',0.000)
   INSERT INTO GRADE VALUES ( 'E01', 0.000 )
*Equivalent Statements
Above are three valid codings of the same statement showing the syntax rules.
Spaces are required between each keyword or variable (INSERT, INTO, GRADE, VALUES). In the VALUES portion, parentheses are needed around the values and commas must separate the values. The character string E01 is delimited by apostrophes. Spaces around parentheses and commas are optional.



*Course Conventions
In this course, you will see a few conventions followed for readability or emphasis. These conventions are
  • Each clause is entered on a separate line, for example

    SELECT * FROM ROSTER
          WHERE STUDENTNO = 'S10'

    rather than

    SELECT * FROM ROSTER WHERE STUDENTNO = 'S10'
  • Information coded between the symbols < > is optional.
  • ... indicates information that repeats.


Topic 1.4: Unit 1 Summary

In this unit, you learned the following:
  • Databases based on a relational model store data in one or more tables.

  • Each table in a relational database consists of a specific number of columns, and some number of unordered rows.

  • Columns in a relational database appear vertically in a report, contain the same kind of data, and have names that are used as column titles.

  • Rows in a relational database appear horizontally in a report, contain one or more columns of data, and do not have names.

  • In formal terms, tables are named relations, columns are called attributes, and rows are known as tuples.

  • Structured Query Language (SQL) is a high-level language that allows you to access and maintain data stored in relational databases, such as DB2 and SQL/DS. Products like FOCUS, SAS and Oracle, which create and maintain relational tables, also use SQL.

  • DDL (Data Definition Language) allows you to define new tables in the database. You can define and modify the database online without special utility programs.

  • DML (Data Manipulation Language) is used to create reports and maintain tables.

  • The Authorization Language provides control of database resources.

  • When coding SQL statements, follow these syntax rules:
    1) Variables and keywords must be separated by one or more spaces.
    2) Parentheses are often required around a keyword's values. Commas must separate values in a list.
    3) Spacing around commas, parentheses and operators is for readability and is not required.
    4) Character strings are sequences of characters that begin and end with ' or ". Use the same character (' or ") for the beginning and ending delimiter.


Unit 2. Table Views



In the previous course, you saw how to maintain tables in a relational database. In this course, you will learn how to create, change, and delete other database objects.
In this unit, you will learn about the table view database object.

After completing this unit, you should be able to:
  • Create a table view

  • Recall the rules for issuing an UPDATE, DELETE, or INSERT on a view, updating the definition of a view, and dropping a view


Topic 2.1: Creating Views

*What Is an Object?
The term database object refers to anything that can be manipulated through SQL statements. You already saw one database object — tables. In this section you will see table views. Other objects are presented in the following units.

*A Different View
A table view (or simply view) is nothing more than a portion of one table or a combination of several tables.
A table view is derived from existing source tables each time it is accessed. Therefore, the view does not really exist.
The view appears to be a table which can be queried and in certain cases updated. It stays in effect until it is dropped.

*View Components
A view can be any combination of the following:
  • A subset of the columns in a given table
  • A subset of the rows in a given table
  • A combination of columns from multiple tables
  • Computed columns




*Creating a View Definition
The definition of the view is created and stored in the form of a SELECT statement. Here is the syntax.
CREATE VIEW view-name <(column-name,...)>
  AS SELECT ...
   <WITH CHECK OPTION> 
Where:
Examine the following table
                       
view-name   Follows the same rules as a table name.
(column-name,...)   Optionally coded, it defines the names of the columns. If column names are not specified, the names are taken from the original tables.
AS SELECT ... A SELECT statement that defines the view.
WITH CHECK OPTION Optionally coded, it indicates updates are checked against the view definition. This is discussed in more detail later.


*Reasons to Create a View
Before getting too involved with how to create views, note these reasons why a view is created.
For security reasons, you might want to limit access to a table to only certain rows, columns, or a combination of both.
If you are working with inexperienced users, you can create views that combine columns from multiple tables thereby avoiding complex SELECTs.
Some requests are much easier to work with if you are utilizing a view.





*An Example View
This example creates a view that is a subset of columns.
The view is created from the INSTRUCT table. It contains all rows, but is limited to the columns named in the SELECT statement. Notice that the SALARY and EXEMPTIONS columns are not included in this view.
CREATE VIEW INSTRUCT2
  AS SELECT INSSN, IFIRSTNAME, IMIDINIT, ILASTNAME, DEPTNO
    FROM INSTRUCT
Users who are not authorized to display and/or update the SALARY and EXEMPTIONS columns, but are authorized to display and update other columns can use this view.

*Another Example
This example creates a view named MATHSTUDS. It is a subset of the rows from the ROSTER table containing those students who are taking math classes.
CREATE VIEW MATHSTUDS
  AS SELECT * FROM ROSTER
      WHERE CLASSNO LIKE 'M%'
        WITH CHECK OPTION
Since no column names are specified, the original column names from the ROSTER table are used.
The WITH CHECK OPTION clause prevents any UPDATEs or INSERTs to rows that do not qualify according to the view definition. In this case, the WITH CHECK OPTION guarantees that anyone using the view MATHSTUDS is not able to change or insert any rows for classes other than MATH.

WITH CHECK OPTION
If the WITH CHECK OPTION is not specified, the default is no checking. This allows INSERTs and UPDATEs of rows outside the view definition.
For example, if WITH CHECK OPTION is not stated, you can specify this INSERT even though it creates a row with a CLASSNO that does not satisfy the view.
INSERT INTO MATHSTUDS
  (CLASSNO, SECTIONNO, INSSN, ROOMNO)
      VALUES('E01', '001', '334347949', '301')
By specifying the WITH CHECK OPTION, the INSERT is not allowed and an error message displays.

  CREATE VIEW STUD_GRADES
    AS (SELECT FIRSTNAME, LASTNAME, CLASSNAME, ROSTER.GRADE, NUMGRADE
      FROM STUDENT, CLASS, ROSTER, GRADE
         WHERE STUDENT.STUDENTNO=ROSTER.STUDENTNO
                         AND
               CLASS.CLASSNO=ROSTER.CLASSNO
                         AND
               ROSTER.GRADE=GRADE.GRADE)
*Views on Multiple Tables
Views can also save you from having to create queries that join tables. The view can do the join for you.
Here is a view created with columns from four different tables — CLASS, ROSTER, STUDENT and GRADE. It's as if STUD_GRADES were a single table with this information in it.

  CREATE VIEW STUD_GRADES
    AS (SELECT FIRSTNAME, LASTNAME, CLASSNAME, ROSTER.GRADE, NUMGRADE
      FROM STUDENT, CLASS, ROSTER, GRADE
         WHERE STUDENT.STUDENTNO=ROSTER.STUDENTNO
                         AND
               CLASS.CLASSNO=ROSTER.CLASSNO
                         AND
               ROSTER.GRADE=GRADE.GRADE)
*Easier Reporting
This view makes it easier to produce reports using calculated grades, particularly grade point averages.
For example, the SQL request...
SELECT FIRSTNAME, LASTNAME, AVG(NUMGRADE) FROM STUD_GRADES
  GROUP BY LASTNAME,FIRSTNAME
    ORDER BY LASTNAME,FIRSTNAME
produces a report displaying each student's grade point average.



Topic 2.2: Updating Views

*Restrictions on Views
There are certain conditions when views cannot be updated.
You cannot issue an SQL UPDATE, DELETE or INSERT against a view when
  • The view involves joining columns from multiple tables.
  • The view includes a GROUP BY clause.
  • The view uses a built-in function, for example AVG(column).
  • The view uses the DISTINCT option.
Moreover, if any columns not accessible via the view are null, you cannot perform an INSERT.

*Justifying the Restrictions
The rules prohibiting updates make sense when you think about them.
In the sample view below, average salary is included as a value. Since the view involves a built-in function, you cannot issue any SQL INSERTs, DELETEs, or UPDATEs.
The average salary is developed from multiple rows. Thus, when issuing an UPDATE, INSERT or DELETE, the database does not know which rows are affected.
CREATE VIEW AVGSALARY AS
  (SELECT DEPTNO AVG(SALARY) FROM INSTRUCT
        GROUP BY DEPTNO
        ORDER BY DEPTNO)



*Changing the View Definition
To change the definition of a view, you must first delete the view and then recreate the view.

*Dropping a View
To delete a view, use the DROP statement.
The format you need is DROP VIEW view-name
For example, to drop the view named ENGLISH you enter the statement
DROP VIEW ENGLISH



*Creating a View
Now you get to build a complete CREATE VIEW statement.
You will enter one clause at a time based on the information presented.





  CREATE VIEW MAJOR_GRADES
       AS (SELECT MAJOR, SEX, NUMGRADE
            FROM STUDENT, ROSTER, GRADE
                 WHERE (STUDENT.STUDENTNO = ROSTER.STUDENTNO)
                                    AND
                       (ROSTER.GRADE = GRADE.GRADE))
*The Completed Statement
Above you can see the entire view definition.
Using a view, you can generate reports. For example, here's a SELECT statement using the view you created.
SELECT * FROM MAJOR_GRADES  WHERE MAJOR IS NOT NULL

Topic 2.3: Unit 2 Summary

In this unit, you learned the following:
  • A table view (view) is nothing more than a portion of one table or a combination of several tables. A table view is derived from existing source tables each time it is accessed. Therefore, the view does not really exist.

  • The definition of a view is created and stored in the form of a SELECT statement. Here is the syntax:

    CREATE VIEW view-name <(column-name,...)>
    AS SELECT ...
    <WITH CHECK OPTION>

  • The WITH CHECK OPTION clause prevents any UPDATEs or INSERTs to rows that do not qualify according to the view definition. If the WITH CHECK OPTION is not specified, the default is no checking. This allows INSERTs and UPDATEs of rows outside the view definition.

  • You cannot update a view when the view involves joining columns from multiple tables, when the view includes a GROUP BY clause, when the view uses a built-in function, for example AVG(column), or when the view uses the DISTINCT option.

  • To change the definition of a view, you must first delete and then recreate the view. Use the SQL DROP statement to delete a view. The format is

    DROP VIEW view-name


Unit 3. Objects for Storing Tables



This unit discusses some of the database objects that control how tables are stored on direct access space. These objects include databases, storage groups, and table spaces.

After completing this unit, you should be able to:
  • Describe the function of a database

  • Explain the purpose and characteristics of storage groups

  • Distinguish between the two types of table spaces


*Database Objects
Before getting into the specifics of how to create these objects, here is a brief description of all objects in a database.
Examine the following table
Table A set of rows that have certain columns.
Index An ordered set of pointers to the rows in a relational table.
Table space A physical storage area that holds one or more tables.
Index space A physical storage area that holds one or more indexes.
Database A named collection of integrated objects.
Storage group A named group of direct access volumes.


*How They Fit Together
These definitions will be expanded as you go along. For now, the diagram above depicts how these objects fit together.


*Databases
A database is a named collection of integrated objects. This database contains two table spaces and one index space.

*Table Spaces
A table space is a physical space to hold tables. Notice a table space can contain only one table or it can hold many.

Topic 3.1: Databases

*The Function of the Database
Within a relational database system, a database
  • Is a convenient way of referring to a set of tables and its associated objects.
  • Defines an operational object that can be started or stopped via operator commands.
  • Provides an object that can be used for authorization.


*A Database Contains Related Tables
It is recommended that related tables be stored in the same database.
For example, a set of tables used in an order entry application should be stored in the same database. Then you can assign a database administrator that has complete authority over the named database.
Databases provide an easy way to control availability and authority over a collection of database objects.



Topic 3.2: Storage Groups

*Storage Groups Defined
A storage group is a set of direct access volumes to which a name is assigned.
The database system uses a storage group to decide the direct access device type used to store your tables.
Storage groups are used to simplify the creation of data sets used by the database system. Storage groups also free you from having to define and delete data sets.

*Rules for Storage Groups
Here are the rules for storage groups:
  • All the volumes in a storage group must be of the same device type (for example, all 3350s or all 3380s).
  • Storage groups may overlap. That is, the same volume may belong to multiple storage groups.
  • Volumes may contain data sets that are not controlled by the database system.



Topic 3.3: Table Spaces

*Types of Table Spaces
Table spaces hold one or more tables. A table space provides the recoverable units. That is, you recover table spaces, not databases, to recover data after failures.
There are two types of table spaces.
Examine the following table
Simple A simple table space must reside in one storage group. It may contain one or more tables.
Partitioned A partitioned table space can span multiple storage groups. It may contain only one table.


*One Table Per Space
In general, it is better to limit a table space to a single table. Here are a few reasons for having only one table in a table space:
  • You can backup and recover one table independently of any other.
  • Scanning a table takes longer when tables are intermixed in the table space.
  • It offers flexibility in controlling the locks active on the table space. Refer to the reference manuals for your database system if you want more information about locks.
  • When certain utilities are running, the entire table space is locked.


*Multiple Tables Per Space
When do you put multiple tables in a table space? The only time you want to do this is when there are many small, related tables — particularly if they are frequently accessed together; that is, if they are joined by a common key.
Here are some reasons for having multiple tables in a table space:
  • You may save storage space.
  • You may improve access time.
  • You can lock them all by locking the table space.
  • You can backup and recover them all at once.



*Partitioned Table Spaces
The diagram above depicts a partitioned table space in Table Space 1. Note the following:
  • Table Space 1 contains one partitioned table (Table A).
  • Table Space 1 spans two storage groups (Groups G1 and G2).


*Uses of Partitioned Tables
Partitioned tables are used only for very large tables. A partitioned table can be defined as ranges of values for an indexed column. For example, you might partition the table space containing a table of employee information by ranges of employee numbers.

*Advantages of Partitioned Table Spaces
Here are some advantages partitioned table spaces have:
  • Large databases are easier to handle because the database system sets up a separate VSAM data set for each partition.
  • One partition can be reorganized and recovered individually without affecting other partitions.
  • Since they can be assigned to different storage groups, more active data can be put in partitions that are located on faster direct access devices.
When an index is established, you need to define what range of keys is to go into which partition. A discussion on how to define an index occurs later in this unit.

Topic 3.4: Unit 3 Summary
In this unit, you learned the following:
  • The objects in a relational database are tables, indexes, table spaces, index spaces, databases, and storage groups.

  • Within a relational database system, a database is a convenient way of referring to a set of tables and its associated objects, defines an operational object that can be started or stopped via operator commands, and provides an object that can be used for authorization.

  • A storage group is a set of direct access volumes to which a name is assigned. Storage groups simplify the creation of data sets used by the database system.

  • Table spaces hold one or more tables. A table space provides the recoverable units in a relational database. The two types of table spaces are simple and partitioned.

  • A simple table space must reside in one storage group. It may contain one or more tables.

  • A partitioned table space can span multiple storage groups. It may contain only one table. Partitioning a large table makes it more manageable.

  • Limiting a table space to a single table permits you to backup and recover one table independent of any other, reduces the time to scan a table, and offers flexibility in controlling the locks active on the table space.

  • Putting multiple tables in a table space is appropriate when there are many small, related tables — especially if they are frequently accessed together.


Unit 4. Modifying Databases and Storage Groups



The previous unit gave you an understanding about the relationship and purpose of databases, storage groups, and table spaces. Now you need to see how to define, modify, and delete these database objects.
This unit covers databases and storage groups.

After completing this unit, you should be able to:
  • Create and change databases

  • Create and change storage groups

  • Delete databases and storage groups


*Functions for Controlling Objects
Three SQL functions control database objects.
Examine the following table
CREATE Defines a new object
ALTER    Modifies an object
DROP     Deletes an object

These can be entered interactively or embedded in an application program.

*Valid Objects
This table indicates the functions that apply to each object.
Examine the following table
Object CREATE ALTER DROP
Storage Group X X X
Table Space X X X
Table X X X
Index Space         
Index X X X
Database X X X
View X    X

Notice that none of the functions are performed for the object index spaces.



Topic 4.1: Creating & Altering Databases

*Creating a Database
Creating a database is very easy. You simply enter an SQL statement with this format.
CREATE DATABASE
<STOGROUP storage-group>
  <BUFFERPOOL buffer-pool>
   <ROSHARE database-access>
database-name — 1 to 8 character name assigned to the database.

*Establishing a Storage Group
Creating a database is very easy. You simply enter an SQL statement with this format.
CREATE DATABASE database-name
<STOGROUP >
  <BUFFERPOOL buffer-pool>
   <ROSHARE database-access>
storage-group — Establishes a default storage group that is used for objects in this database. If none is entered, a default is assigned. For example, in DB2 the default is SYSDEFT.

*Establishing a Bufferpool
Creating a database is very easy. You simply enter an SQL statement with this format.
CREATE DATABASE database-name
<STOGROUP storage-group>
  <BUFFERPOOL >
   <ROSHARE database-access>
buffer-pool — Establishes a default bufferpool for objects associated with this database.
BP0, BP1, and BP2 are 4K bufferpools BP32K is a 32K bufferpool used only when you anticipate retrieving rows with data lengths greater than 4K. The default is the default bufferpool of the database system.

*Establishing Sharing
Creating a database is very easy. You simply enter an SQL statement with this format.
CREATE DATABASE database-name
<STOGROUP storage-group>
  <BUFFERPOOL buffer-pool>
   <ROSHARE >
database-access — Indicates how the database will be shared. The default is that the database is not shared.

Note: For more information on shared databases, see your SQL reference manuals.


*The Database Administrator
To keep things in perspective, remember, not everyone will be creating databases. In fact, a very select few will.
To create a database, you must have system administration authority, or you must be granted authority to create a database by someone with system administration authority. In general, most installations have a database administrator create and maintain specific databases. They are responsible for granting the authority through the systems administrator.
If you are given the authority to create a database, you are automatically given complete authority and control over the objects that are assigned to this database.



CREATE DATABASE SCHOOL
     STOGROUP SYS3380A
          BUFFERPOOL BP1
*The Completed Statement
Above you can see the entire CREATE statement.
Remember that defaults are assigned for storage group and bufferpool if you do not code them.



*Altering a Database
Use the ALTER DATABASE statement to change the description of a database.
ALTER DATABASE database-name
<STOGROUP storage-group>
  <BUFFERPOOL buffer-pool>
   <ROSHARE database-access>
Examine the following table
                    
database-name     1 to 8 character name assigned to the database.
storage-group     Changes the default storage group that is used for objects in this database. Doesn't apply to objects that already exist.
buffer-pool       Changes the default bufferpool for objects associated with this database. Doesn't apply to objects that already exist.
database-access   Indicates how the database will be shared. The default is that the database is not shared.




Topic 4.2: Creating & Altering Storage Groups

*Creating a Storage Group
To create a storage group, you use an SQL statement that follows this format.
CREATE STOGROUP stogroup-name
VOLUMES (volid1<, volid2....>)
  VCAT catalog-name
  <PASSWORD password>
Examine the following table
stogroup-name 1 to 8 character name assigned to the storage group.
volid          The volume serial number of the OS/VS storage volume assigned to this particular storage group.
catalog-name    The name of a VSAM catalog used for this storage group.
password        The password for the catalog, if one exists.


*Check with the Database Administrator
Again, not everyone will be creating storage groups. To create storage groups, you also must have system administration authority or have been granted the authority to create storage groups.
In general, most installations have only a few people creating and maintaining storage groups.
The majority of users will simply be told what storage group to use.

*Altering a Storage Group
One more thing about storage groups...
You can always add or drop a volume from a storage group by using an ALTER statement. Here is the format.
ALTER STOGROUP stogroup-name
<ADD VOLUMES (volid1 <,volid2...>)>
  <REMOVE VOLUMES (volid1 <,volid2...>)>
   <PASSWORD password>
If you remove a volume from the list of volumes in a storage group, you still are able to access any tables that were created using that volume.



*The Completed Statement
CREATE STOGROUP SYS3380A VOLUMES (DISK01, PROD01) VCAT VSAMCAT1
The example above
  • Names the storage group SYS3380A.
  • Specifies that DISK01 and PROD01 are the storage volumes assigned to this storage group.
  • Identifies VSAMCAT1 as the VSAM catalog used for this storage group.


Topic 4.3: Deleting Databases & Storage Groups

*Dropping an Object
To delete a database or a storage group, you use the DROP statement.
The formats for these statements are
DROP DATABASE database-name
DROP STOGROUP stogroup-name
Note that when you drop a database you also drop all objects that are members of that database. That is, you drop all tables, table spaces, indexes, index spaces, and views.



Keep in mind that the rules for assigning database objects vary by installation. Be sure to check with your database administrator before creating any objects.
You should know the standards for
  • Who has responsibility for creating all the database objects
  • What naming conventions are used for database objects
  • What your responsibilities are as a database user


Topic 4.4: Unit 4 Summary

In this unit, you learned the following:
  • Three SQL functions — CREATE, ALTER and DROP — control database objects. You cannot use some of these functions on a few of the database objects. None of these functions are performed for index spaces.

  • To create a database you use the CREATE DATABASE statement.

  • Use the ALTER DATABASE statement to change the description of a database.

  • To create a storage group, you use a CREATE STOGROUP statement.

  • In general, most installations have only a few people creating and maintaining databases and storage groups. Usually you are told which database and storage group to use.

  • You can add or drop a volume from a storage group by using an ALTER statement.

  • To delete a database or a storage group, you use the DROP statement.


Unit 5. Modifying Table Spaces



Recall that three database objects control how tables are stored on direct access space. You've seen two — databases and storage groups — in the previous unit. Here you'll see the third one — table spaces.
Let's see how you create, modify, and delete table spaces.

After completing this unit, you should be able to:
  • Create table spaces

  • Change and delete table spaces


Topic 5.1: Creating Table Spaces

*Review of Table Spaces
Remember that
  • A table space is a physical storage area that holds one or more tables.
  • Tables spaces provide the recoverable units.
  • The two types of table spaces are simple and partitioned.


*The Statement Format
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE
<IN database-name>
  <USING STOGROUP stogroup-name
         PRIQTY k-bytes SECQTY k-bytes>
   <BUFFERPOOL BPx>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
space-name – 1 to 8 character name assigned to the table space.

*Specifying the Database
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN >
  <USING STOGROUP stogroup-name
         PRIQTY k-bytes SECQTY k-bytes>
   <BUFFERPOOL BPx>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
database-name – The name of the database you want to relate this table space to. If omitted, the default database for the database system is used.

*Specifying the Storage Group
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN database-name>
  <USING STOGROUP
         PRIQTY k-bytes SECQTY k-bytes>
   <BUFFERPOOL BPx>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
stogroup-name – Specifies the name of the storage group this table space is assigned to.

*Assigning the Primary Space
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN database-name>
  <USING STOGROUP stogroup-name
          SECQTY k-bytes>
   <BUFFERPOOL BPx>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
PRIQTY k-bytes – Assigns a primary space amount for the table space in kilobytes.

*Assigning the Secondary Space
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN database-name>
  <USING STOGROUP stogroup-name
         PRIQTY k-bytes >
   <BUFFERPOOL BPx>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
SECQTY k-bytes – Assigns a secondary space amount for the table space in kilobytes.

*Assigning the Bufferpool
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN database-name>
  <USING STOGROUP stogroup-name
         PRIQTY k-bytes SECQTY k-bytes>
   <>
    <LOCKSIZE ANY/PAGE/TABLESPACE>
BUFFERPOOL BPx – Assigns the table space to a specific bufferpool.
BP0, BP1, and BP2 - 4K bufferpools
BP32K - 32K bufferpool used only if you anticipate retrieving rows with data lengths greater than 4K.
The default is the default bufferpool of the database system.

*Specifying Locking
Here is the format of the SQL statement that creates a table space.
CREATE TABLESPACE space-name
<IN database-name>
  <USING STOGROUP stogroup-name
         PRIQTY k-bytes SECQTY k-bytes>
   <BUFFERPOOL BPx>
    <>
LOCKSIZE ANY/PAGE/TABLESPACE – The database system uses locks to minimize the interference between concurrent users. Locks are controlled at the page or table space level. The default value of ANY is the best choice. It allows the database system to decide at what level to control locking.



*Creating a Partitioned Table Space
When a partitioned table space is created, you must identify the storage groups where each partition is to be stored.
To do this, you use a special form of the CREATE TABLESPACE statement. This is not something you'll be doing every day. If you need this format, refer to the reference manuals for your database system.
An important point to remember, though, is that the rows that go into a partition are not defined until you create the index that goes with the table.


*Creating Tables
Now that you know about databases, storage groups, and table spaces, let's look at the CREATE TABLE statement again.
When you create a table, you may specify a database and/or table space for the table. If you do not specify these, defaults are assigned. For example, the database system will create a table space in its default database.
For non-production tables the defaults are usually acceptable.
However, for a production database you probably want to assign a table to a specific database. Moreover, you may even want to assign it to a specific table space as well.

*Options for Creating a Table
To assign a table to a specific database and/or table space, you add one of these three options to the end of the CREATE TABLE statement:
IN DATABASE database-name
IN database-name.tablespace-name
IN tablespace-name

*Investigating the Options
IN DATABASE database-name
A default table space is created for your table and it is associated with the named database.
IN database-name.tablespace-name
Your table is created in the existing table space and it is associated with the named database.
IN tablespace-name
Your existing table space is associated with the default database of the database system. For example, in DB2 this is DSNDB04.

*Letting the System Create the Table Space
In general, the most common option is to allow the database system to create the table space in a specific database.
Here is an example that creates a table in database SCHOOL, but allows the database system to create the table space.
CREATE TABLE CLASS
  (CLASSNO      CHAR(3)      NOT NULL,
   CLASSNAME    VARCHAR(50)  NOT NULL,
   CLASSCOST    DECIMAL(8,2) NOT NULL,
   COURSEHRS    SMALLINT     NOT NULL)
   IN DATABASE SCHOOL
Only create a table space if you want to have some level of control over how the table space is created.

*Defining Your Own Table Space
Here are some reasons to define and use your own table space.
  • To specify a specific storage group.

  • To partition a large table.

  • To request a specific bufferpool other than BP0.

  • To force page or table space locking. By default, the database system decides on the type of locking dynamically.

  • To control the amount of space assigned.

  • To assign multiple tables per table space.
In general, if you are unsure, allow the database system to create the table space. You can always reassign it later if a specific table space requirement is needed.



Topic 5.2: Altering & Deleting Table Spaces

*Changing a Table Space
To change a table space, use the ALTER TABLESPACE statement. The entries have the same meaning as the corresponding CREATE TABLESPACE entries.
Here is the format of this SQL statement.
ALTER TABLESPACE database-name.tablespace-name
<BUFFERPOOL BPx>
  <LOCKSIZE ANY/PAGE/TABLESPACE>
At least one of the keywords — BUFFERPOOL or LOCKSIZE — must be coded on the ALTER TABLESPACE statement.

*Dropping a Table Space
To delete a table space you use the DROP statement. The format of the DROP statement is
DROP TABLESPACE database-name.tablespace-name
Note that when you drop a table space, you also drop all tables in the table space and all objects dependent on those tables.



Here are a few common-sense rules for creating database objects.
  • Normally, when creating temporary tables or test tables, it is acceptable to let them default and not specify a database or create an index.
  • Permanent tables used in a production environment will normally be assigned to a specific database. The database is created by the person who has database administrator authority over this database.
  • Normally, you let the database system create the table space for you unless a specific requirement is needed.


Topic 5.3: Unit 5 Summary

In this unit, you learned the following:
  • The CREATE TABLESPACE statement defines a table space.

  • When a partitioned table space is created, you use a special form of the CREATE TABLESPACE statement. You must identify the storage groups where each partition is to be stored. The rows that go into a partition are not defined until you create the index that goes with the table.

  • To assign a table to a specific database and/or table space, you add one of these three options to the end of the CREATE TABLE statement:

    IN DATABASE database-name
    IN database-name.tablespace-name
    IN tablespace-name

  • To change a table space, use the ALTER TABLESPACE statement. The entries have the same meaning as the corresponding CREATE TABLESPACE entries.

  • To delete a table space you use the DROP statement.


Unit 6. Maintaining Other Database Objects



The previous units have covered table views, databases, storage groups, and table spaces. In this unit you will learn about the two remaining database objects: indexes and index spaces.
You will also learn about a special set of tables that are created and maintained by the database system. These tables are called the system catalog.

After completing this unit, you should be able to:
  • Create an index

  • Use the system catalog


Topic 6.1: Indexes and Index Spaces

*What Is an Index?
When you go to the library looking for a specific book, what do you use? You use an index, because it speeds up the process of finding the book. Let's see how indexes provide the same feature for relational databases.
An index is an ordered set of pointers to the rows in a relational table.
An index speeds up access to the rows in a table. It logically reorders data so that the database system can access the data without lengthy sequential searches.

*Reasons to Use Indexes
Here are four reasons to use an index:
  • To ensure uniqueness of a key value. For example, if a unique index on STUDENTNO is set up, the database system will not allow another row to be created with the same value of STUDENTNO.
  • To improve performance. You use an index on key values of large tables that are used in search conditions, or in joins.
  • To cluster rows in a certain order. Normally, the rows created are unordered. If you request a clustering index, the rows' order approximates the order of the indexed column(s).
  • To create a partitioned table you are required to create a clustering index to define the range of keys for partitions.



*Characteristics of Indexes
Indexes have the following characteristics:
  • There can be more than one index per table.
  • An index can be made up of one or more columns of a table. The maximum is 63 columns.
  • An index can be established in ascending or descending order.
  • An index is an object completely separate from table data.
  • Adding an index to a table does not affect how any SQL statements are issued.
  • You can identify an index as being unique. This means the database system does not allow rows with the same index values.


*The Database Decides
By the way, the fact that you have an index does not guarantee that the database system will always use it.
The database system decides when to use it. Among other things, the decision is based on the size of the table and the type of access.
For example, if you are using all the rows in the table, it is less efficient to use the index. In this case, the database system does not use the index.
The database system automatically maintains indexes. Any add, delete, or update to a table triggers modification to any index defined on that table.



Topic 6.1.1: Clustering Indexes

*Using the Table's Order
A clustering index causes the rows of the table to be stored in a physical order that approximates the order of the indexed column(s).
There are two reasons for using a clustering index:
  • The rows of the table should be stored in the order accessed most frequently. For example, if the STUDENT table is accessed in order by STUDENTNO, then a clustering index speeds up access — particularly for large tables.
  • A partitioned table must have a clustering index in order to define the range of values that fall in each partition.
Since a clustering index defines an order for the rows to be stored, only one clustering index can be defined per table.



Topic 6.1.2: Creating an Index

*The Statement Format
The CREATE INDEX SQL statement creates an index. Here is its general format.
CREATE <UNIQUE> INDEX index-name ON table-name
(column-name1 <DESC> <,column-name2 <DESC>>...)
  <USING STOGROUP stogroup-name
         PRIQTY kbytes SECQTY kbytes>
         <CLUSTER>
Here's an example that creates a clustered index named STUDNO.
CREATE UNIQUE INDEX STUDNO ON STUDENT
(STUDENTNO) USING STOGROUP SYS3350A PRIQTY 5 SECQTY 5
   CLUSTER
Let's look at each entry of the CREATE INDEX in more detail.

 CREATE
      <USING STOGROUP stogroup-name
               PRIQTY kbytes SECQTY kbytes>
         <CLUSTER>
*Naming Names
Examine the following table
               
UNIQUE Requests a unique index.
index-name A 1 to 8 character name that identifies the index.
table-name The name of the table the index applies to.
column-name One or more columns that make up the index.
DESC Requests the column(s) be in descending order. The default is ascending (ASC).


 CREATE <UNIQUE> INDEX index-name ON table-name
   (column-name1 <DESC> <,column-name2 <DESC>>...)
      < STOGROUP stogroup-name
               PRIQTY kbytes SECQTY kbytes>
         <>
*Optional Entries
Examine the following table
USING Defines the storage group and space requirements. If it is not entered, the default storage group for the database is used.
CLUSTER Defines the index as a clustering index.

The CLUSTER clause is extended for the specification of the key range of each partition. Refer to the reference manuals for your database system if you need this format.



Topic 6.1.3: Index Spaces

*Storing the Index
An index space is to an index what a table space is to a table. That is, an index space is the place where indexes are stored.

*Index Spaces Are Created by the System
However, there are several important differences between index spaces and table spaces.
  • Only one index is stored in an index space.
  • You never issue a SQL CREATE statement to create an index space. The database system creates them automatically on the storage group specified.
There is no need to worry about index spaces, but be aware of them.



Topic 6.2: The System Catalog

*Contents of the Catalog
Information about the data managed by the database system is contained in the system catalog. Information found in the system catalog tables relates to data storage, programs and authorization.
The system catalog contains the following information:
  • Information on each active database object
  • Cross-reference information that depicts how objects relate to one another
  • Authorization information
  • Statistics about active database objects
This information is automatically created and maintained by the database system.

SELECTing from the Catalog
The system catalog is very useful to database administrators, but it also has some information every database user finds helpful.
You can use normal SELECT statements to access data in the system catalog tables. In the general format, you identify the desired catalog table in the FROM clause.
SELECT column-name FROM system-tablename  WHERE condition
The naming convention for the system tables varies with the database system you're using — DB2 or SQL/DS.
For DB2, you use:        SYSIBM.tablename
For SQL/DS, you use:   SYSTEM.tablename



*Catalog Tables Vary
The number and contents of the system catalog tables vary in DB2 and SQL/DS. Here are a few that are similar in both database systems.
Examine the following table
SYSCOLUMNS    Contains one row for every column of each table and view.
SYSINDEXES    Describes each index currently in existence.
SYSKEYS      Contains one row for each column of an index key.
SYSUSERAUTH   Records the system privileges held by users.
SYSVIEWS     Lists each view known to the system and the options used to create them.


*Accessing the Catalog
In order to access the system catalog, you must have SELECT authority on these tables. Most installations grant SELECT authority to those system catalog tables commonly used by the public.
These tables are fully described in the back of the reference manuals for your database system. Refer to that section for more information.
Let's look at example SELECT statements to access the system catalog tables.

      DB2 Example                      SQL/DS Example
 SELECT CLASSNO                    SELECT CCLASSNO
   FROM SYSIBM.SYSCOLUMNS            FROM SYSTEM.SYSCOLUMNS
      WHERE (TBNAME='ROSTER')          WHERE (TNAME='ROSTER')
        AND                              AND
            (CLASSNO LIKE 'E0%')             (CCLASSNO LIKE 'E0%')
*Example SELECT Statements
These examples select columns in the ROSTER table which begin with 'E0' (that is, all English classes).
Notice that the coding is very similar for both database systems. What may vary are table names and column names.

Topic 6.3: Unit 6 Summary
In this unit, you learned the following:
  • An index is an ordered set of pointers to the rows in a relational table. There can be more than one index per table.

  • An index can be made up of one or more columns of a table and can be established in ascending or descending order.

  • An index is an object completely separate from table data.

  • Adding an index to a table does not affect how any SQL statements are issued.

  • You can identify an index as being unique.

  • The database system decides when to use an index. The decision is based on the size of the table and the type of access.

  • The database system automatically maintains indexes. Any add, delete, or update to a table triggers modification to any index defined on that table.

  • A clustering index causes the rows of the table to be stored in a physical order that approximates the order of the indexed column(s). Since a clustering index defines an order for the rows to be stored, only one clustering index can be defined per table.

  • The CREATE INDEX SQL statement creates an index.

  • An index space is the place where indexes are stored. Only one index is stored in an index space. You never issue an SQL CREATE statement to create an index space. The database system creates them automatically on the storage group specified.

  • The system catalog is a special set of tables created and maintained by the database system. Information about the data managed by the database system is contained in the system catalog. Information found in the system catalog tables relates to data storage, programs and authorization.

  • You can use normal SELECT statements to access data in the system catalog tables. In the general format, you identify the desired catalog table in the FROM clause.

  • The naming convention for the system tables varies with the database system you're using — DB2 or SQL/DS. The number and contents of the system catalog tables also vary in DB2 and SQL/DS, with the exception of a few tables that are used in both systems.
Posted by at
Labels: , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)