working with data objects pertemuan 6 matakuliah: t0413 tahun: 2009

Working with Data ObjectsPertemuan 6

Matakuliah : T0413Tahun : 2009

Bina Nusantara University 3

Database ObjectsSchema: All DB2 objects have a two part name and the

schema is the first half of that name.Table: A collection of data logically arranged in columns

and rowsView: A representation of the data in tablesIndex: An ordered set of keys each pointing to a row in a

tableDatabase Application Objects: (not all will be covered in

this section)SequencesTriggers (covered in another presentation)User Defined Functions (UDFs) (covered in another

presentation)Stored Procedures (covered in another presentation)


1) Schema

• Schemas are name spaces for a collection of database objects

• Schemas are primarily used to:– Provide an indication of object ownership or

relationship to an application– Logically group related objects together

• All database objects belong to schemas and are qualified by a two-part name:

<schema_name>.<object_name>– A fully qualified object name must be unique

• When you connect to a database and create or reference an object without specifying the schema, DB2 uses the user ID you connected to the database with for the schema name


CREATE TABLE artists(artno SMALLINT not null, name VARCHAR(50) with default 'abc', classification CHAR(1) not null, bio CLOB(100K) logged, picture BLOB(2M) not logged compact)in mytbls1

2) Tables


Data Types• DB2 Data Types Data Types

Numeric

IntegerSMALLINTINTEGERBIGINT

DECIMAL

FloatingPoint

REALDOUBLE

String

CharacterString

Single ByteCHARVARCHARLONG VARCHARCLOB

GRAPHICVARGRAPHICLONG VARGRAPHICDBCLOB

Double Byte

Binary String

BLOB

Datetime

DATETIMETIMESTAMP

XML

DECIMAL


ActionNews

DB2By The Book

DB2

B inaryL argeOB ject

D ouble B yte C haracterL arge OB ject

C haracterL argeOB ject

To store large character strings or filesTo store large binary strings or files

Large Objects


CREATE DISTINCT TYPE POUND AS INTEGER WITH COMPARISONS

CREATE DISTINCT TYPE KILOGRAM AS INTEGERWITH COMPARISONS

CREATE TABLE person (f_name varchar(30), weight_p POUND NOT NULL, weight_k KILOGRAM NOT NULL )

SELECT F_NAME FROM PERSON WHERE weight_p > POUND(30)

SELECT F_NAME FROM PERSON WHERE weight_p > weight_k

FAILS

User-Defined Types


A null value represents an unknown stateƒ The CREATE TABLE statement can contain the phrase NOT

NULL following the definition of each column. ƒ This will ensure that the column contains a known data value.

Can specify a default value if NOT NULL is entered

CREATE TABLE Staff ( ID SMALLINT NOT NULL, NAME VARCHAR(9) , DEPT SMALLINT not null with default 10, JOB CHAR(5) , YEARS SMALLINT , SALARY DECIMAL(7,2) , COMM DECIMAL(7,2) with default 15 )

Null Values


System Catalog Tables• Each database has its own system catalog tables/views• These store meta data about the database objects• You can query these tables just like any other tables• Reside in three schemas:

– SYSIBM - base tables, optimized for DB2– SYSCAT - views based on SYSIBM tables, optimized for ease of

use– SYSSTAT - database statistics

examples:» SYSCAT.TABLES» SYSCAT.INDEXES» SYSCAT.COLUMNS» SYSCAT.FUNCTIONS» SYSCAT.PROCEDURES


Created and used by an application and dropped (automatically) when the application terminates

Can only be accessed by the application that created the tableNo entry exists in any catalog tableLogging

ƒ NOT LOGGED clause optionalAutomatic cleanupPerformace

ƒ avoid catalog contentionƒ no locking of rowsƒ no logging (but logging is optional)ƒ no authority checking

Index supportƒ any standard index can be created on a temporary table

Statistics support (RUNSTATS supported against the table)

Declared Temporary Tables


Declared temporary tables reside in a user temporary tablespaceƒ Must be defined prior to creating any declared temporary tables

CREATE USER TEMPORARY TABLESPACE apptemps MANAGED BY SYSTEM USING ('apptemps');

DECLARE GLOBAL TEMPORARY TABLE temployess LIKE employee NOT LOGGED;

DECLARE GLOBAL TEMPORARY TABLE tempdept ( deptid CHAR(6), deptname CHAR(20) ) ON COMMIT DELETE ROWS NOT LOGGED ;

DECLARE GLOBAL TEMPORARY TABLE tempprojects AS ( fullselect ) DEFINITION ONLY ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE IN TABLESPACE apptemps;

Temporary Tables


A numeric column in a table which automatically generates a unique numeric value for each row that is inserted

One Identity column per table maximum

Values can be generated by DB2 always or by defaultƒ Generated always

–values are always generated by DB2–applications are not allowed to provide an explicit value.

ƒ Generated by default–values can be explicitly provided by an application or if no value is given, then DB2 generates one

–DB2 cannot guarantee uniqueness–intended for data propagation, unload/reload of a table

Identity Columns


Unlike identity columns, sequences are independent of tablesexample:

CREATE SEQUENCE myseqSTART WITH 1INCREMENT BY 1NO CYCLE

INSERT INTO t1 VALUES (nextval for myseq, ...)

SELECT prevval for myseq FROM sysibm.sysdummy1

SEQUENCE objects


Row Compression (Not available in DB2 Express-C)


Table Partitioning (not available in DB2 Express-C)

64G

32K Partitions

A-Z

64G

A-C

64G

D-M

64G

N-Q

64G

R-Z

BackupLoad

Recover

BackupLoad

Recover

BackupLoad

Recover

BackupLoad

Recover

BackupLoad

Recover

BackupLoad

Recover


Data for view not stored separatelyNested view supportedView information kept in: SYSCAT.VIEWS, SYSCAT.VIEWDEP, SYSCAT.TABLES

CONNECT TO MYDB1

CREATE VIEW MYVIEW1 AS SELECT ARTNO, NAME, CLASSIFICATION FROM ARTISTS

SELECT * FROM MYVIEW1

ARTNO NAME CLASSIFICATION

------ ----------------- --------------

10 HUMAN A

20 MY PLANT C

30 THE STORE E ...

3) Views


Index Characteristics:

ƒ ascending or descendingƒ Unique or non-uniqueƒ compoundƒ clusterƒ bi-directional (default behavior))

Examples:

create unique index artno_ix on artists (artno)

4) Indexes


Launching the Design Advisor• Advises you on the design of your database to

optimize it for a given SQL workload

Control Center > (expand) All Databases Folder > (right-click) Database > Design Advisor


Design Advisor


or unique constraint

create table employee (empno ............. primary key (empno) foreign key (workdept) references department on delete no action) in DMS01

Referential Integrity


Insert Rulesƒ Rule is implicit when a foreign key is specified.ƒ backout insert if not found

Delete Rulesƒ Restrict

–Parent row not deleted if dependent rows are found. ƒ Cascade

–Deleting row in parent table automatically deletes any related rows in dependent tables.

ƒ No Action (default)–Enforces presence of parent row for every child after all other referential constraints applied

ƒ Set Null–Foreign key fields set to null; other columns left unchanged.

Referential Integrity Rules

working with data objects pertemuan 6 matakuliah: t0413 tahun: 2009

Documents