4

8

16

32

ALTER TABLE sales ADD COLUMN item CHAR(40);

ALTER TABLE sales ALTER COLUMN item CHAR(40);

ALTER TABLE sales MODIFY COLUMN item CHAR(40);

ALTER TABLE sales ALTER COLUMN item SET DATA TYPE CHAR(40);

Trigger

SQL Procedure

Sequence Object

Materialized Query Table

When you want to control the order in which triggers are fired.

When you want to control the order in which stored procedures can be invoked.

When you want to automatically generate a numeric value that is not tied to any specific column or table.

When you want to automatically generate a numeric value for each row that is added to a specific table.

ADD COLUMN

ALTER COLUMN

APPEND COLUMN

RENAME COLUMN

Table

Schema

Package

Database

A transaction-start-id column must be defined for the table.

A history table must be defined and associated with the base table.

A BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.

A unique index must be created that prevents overlapping of the BUSINESS_TIME period of the table.

Oracle connect

Trusted context

Oracle federation

Distributed request

CLI

XML

JDBC

RUBY

Alias

Index

Schema

Collection

Tree

Node

Trigger

Contexts

Sequence

Bitemporal

Time-period

System-period

Application-period

In the table definition

In the system catalog.

In the schema that the index resides in.

In the metadata of the table the index was created for.

pureScale

Workload manager

Data partitioning feature

Self-tuning memory manager

locator

pointer

address

reference

inline

binary

internal

partitioned

To automatically capture and stop rogue queries.

To provide recommendations and analysis for tuning a single query.

To provide recommendations and analysis for tuning up to 100 queries.

To recommend indexes and to guide DBAs through the process of creating new indexes.

Control Center

IBM Data Studio

Universal Connection Expert

DB2 Connection Pool Manager

Confirm product inventory.

Monitor product availability.

Summarize sales by region.

Identify patterns for products sold in the last five years.

Associate one or more products with a purchase order.

To combine data from multiple sources.

To discover hidden relationships in data.

To analyze large amounts of data to find patterns.

To make changes to a small number of records within a single transaction.

Read Stability (RS)

Cursor Stability (CS)

Repeatable Read (RR)

Uncommitted Read (UR)

Table size

Buffer space

Number of locks

Available real storage

Share (S)

Update (U)

Exclusive (X)

Intent Exclusive (IX)

Read Stability (RS)

Repeatable Read (RR)

Uncommitted Read (UR)

Cursor Stability (CS)

LOCK TABLE tab1 IN READ MODE

LOCK TABLE tab1 IN SHARE MODE

LOCK TABLE tab1 IN EXCLUSIVE MODE

LOCK TABLE tab1 IN READ-ONLY MODE

When a transaction intends to read or change data.

When a transaction intends to change but not read data.

When a transaction intends to read but not change data.

When a transaction intends to change the system catalog.

Lock escalation

Lock substitution

Lock switch/exchange

Lock conversion/promotion

Read stability (RS)

Cursor stability (CS)

Repeatable read (RR)

Uncommitted read (UR)

BIND

DROP

GRANT

SELECT

All locks held on the database are automatically released.

Data stored in global temporary tables is automatically deleted.

Open cursors defined WITH HOLD are closed, but their data is retained.

The current transaction is terminated and a new transaction boundary is started.

COMMIT

ROLLBACK

COMMIT TO SAVEPOINT

ROLLBACK TO SAVEPOINT

CALL conv_temp(climate_info.avg_temp);

CALL convert_ftoc(climate_info.avg_temp);

SELECT conv_temp(avg_temp) FROM climate_info;

SELECT convert_ftoc(avg_temp) FROM climate_info;

The last 5 rows in the table will be deleted.

The first 5 rows in the table will be deleted.

The statement will fail because a subquery cannot be used with a DELETE statement.

The statement will fail because a table name was not specified with the DELETE statement.

SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON employees.id =
sales.emp_id

SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON sales.emp_id =
employees.id

SELECT employees.name, sales.revenue FROM sales LEFT OUTER JOIN employees ON
employees.id = sales.emp_id

SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON
employees.id = sales.emp_id

CALL statements.

External file access.

Use of a scratch pad.

COMMIT statements.

SQL control statements.

TRIM

DROP

DELETE

TRUNCATE

XMLTABLE

XMLPARSE

XMLEXISTS

XMLATTRIBUTES

SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000) OR (education ='University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01') OR (salary < 40000) OR (education ='University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01' OR (salary < 40000 AND (education ='University')

SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000 AND (education ='University')

SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' AND salary < 35000

SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary <
35000

SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' OR salary <
35000

SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' OR salary < 35000

The INSERT statement is used to insert rows into a table, view, or table function.

Inserted values must satisfy the conditions of any check constraints defined on the table.

If an INSERT statement omits any column from the inserted row that is defined as NULL or NOT NULL
WITH DEFAULT, the statement will fail.

If the underlying table of a view being referenced by an INSERT statement has one or more unique
indexes, each row inserted does not have to conform to the constraints imposed by those indexes.

Consists of explicitly-supplied timestamps and a separate associated history table.

Is based on explicitly-supplied timestamps that define the time periods during which data is valid.

Is useful when one wants to keep both user-based period information and system-based historical
information.

Consists of a pair of columns with database-manager maintained values that indicate the period when
a row is current.

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job;

SELECT job, MIN(salary), MAX(salary) FROM employee ORDER BY job;

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job, MIN(salary), MAX(salary);

SELECT JOB, MIN(salary), MAX(salary) FROM employee ORDER BY job, MIN(salary), MAX(salary);

Locked

Waiting

Pending

Unconnected

Load operations

Backup/copy table spaces

Create and drop databases

Monitor database performance

To define exactly what a specific user can do within a particular database once they have been
authenticated.

To group a collection of privileges together so that they can be simultaneously granted to and revoked
from multiple users.

To establish a credible relationship between DB2 and an external entity, such as a database
administrator or a middleware server.

To group a collection of users together so that they can be simultaneously granted and revoked
specific authorities and privileges.

GRANT ALTER ON SEQUENCE myseq TO user1

GRANT USAGE ON SEQUENCE myseq TO user1

GRANT SELECT ON SEQUENCE myseq TO user1

GRANT COMMENT ON SEQUENCE myseq TO user1

The column mask must be enabled; the EMPLOYEE table must be altered to activate column access
control.

The column mask must be enabled (column access control for the EMPLOYEE table will be activated
automatically).

The EMPLOYEE table must be altered to activate column access control (the column mask will be
enabled automatically).

The EMPLOYEE table must be altered to activate column access control; row permission must be
granted to everyone who needs to use thecolumn mask.

DBADM

DBCTRL

SECADM

DBMAINT

GRANT CREATETAB ON DATABASE TO user1

GRANT SELECT ON TABLE employee TO user1

GRANT CREATEVIEW ON DATABASE TO user1

GRANT REFERENCES ON TABLE employee TO user1

DBCTRL authority

SQLADM authority

DATAACCESS authority

ACCESSCTRL authority

CALL

USAGE

EXECUTE

REFERENCES

It must not contain more than one column.

It cannot be used in a referential constraint.

It must be defined with the UNIQUE attribute.

It must be defined as UNIQUE WHERE NOT NULL.

Only one INSERT, UPDATE, or DELETE trigger can be defined per table.

Triggers cannot be modified, they must be dropped and recreated with new definitions.

Adding a trigger to a table that already has rows in it will cause triggered actions to be fired.

Triggers can only be fired one time per statement irrespective of the number of rows affected.