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.

An error will be returned and the operation will fail.

A warning will be returned and the unique index INDX1 will become the index for the primary key.

The primary key will be successfully added and the unique index INDX1 will be dropped and recreated
as the primary key index.

The primary key will be successfully added, the unique index INDX1 will be dropped, and a new index
will be created for the primary key.

The data in the table is deleted and the table persists.

The data in the table is deleted and the table is implicitly dropped.

The temporary table is converted to a base table and the data is deleted.

The temporary table is converted to a base table and the data is persistent.

The SYSIBM schema.

The SYSFUN schema.

A schema with the name of the UDF.

A schema with the userid of the UDF definer.

A trigger is designed to return specific types of values and contains executable code.

A trigger is designed to return specific types of values but contains no executable code.

A trigger performs actions to capture details about an activity like a join operation against multiple
tables.

A trigger performs actions in response to an event like an INSERT, UPDATE or DELETE operation on
a table.

The foreign key constraint columns must be supported by an index.

The foreign key constraint columns must be defined as NOT NULL.

The number and data types of foreign key constraint columns must match the parent key.

The columns of one foreign key constraint cannot be used in another foreign key constraint.

To define a foreign key constraint there must be an associated primary key or unique key in the same
or different table.

To enable uniqueness.

To ensure data clustering.

To combine data from multiple tables.

To optimize queries ran against multiple tables.

No fractional digits or time zone.

6 fractional digits and no time zone.

12 fractional digits plus a time zone.

12 fractional digits and no time zone.

To allow queries to run more efficiently.

To enforce constraints such as uniqueness on index keys.

To order the columns of a table in ascending or descending sequence according to values in a row.

To order the rows of a table in ascending or descending sequence according to the values in a column.

Unique constraint

Primary key constraint

Informational constraint

Column default constraint

A table can only have one unique key constraint.

A table can have multiple primary key constraints.

Informational constraints tell DB2 what rules the data conforms to, but the rules are not enforced.

Foreign key constraints are enforced on the values within the rows of a table, or between the rows of
two tables, by a unique index on a foreignkey.

In an XML index.

In line with the rest of the data for the table.

In an XML storage object that is separate from the table.

In line with the rest of the data for the table, for XML columns less than 32KB.

A BEFORE trigger can be used to perform validation of input data.

A BEFORE trigger can be used to automatically generate values for newly inserted rows.

A BEFORE trigger is fired for each row in the set of affected rows before the trigger event executes.

A BEFORE trigger is fired for each row in the set of affected rows instead of executing the trigger
event.

Primary key

Partitioning key

Check constraint

Unique constraint

Change the data type of the sequence.

Reset the sequence to its starting value.

Establish new minimum or maximum values.

Change the increment between future values.

USER1 can only remove the SALES table.

USER1 can only remove records from the SALES table.

USER1 can remove the SALES table and grant the privilege to remove the SALES table to other
users.

USER1 can remove records from the SALES table and grant the privilege to remove records from the
SALES table to other users.

Authentication

Authorities and privileges

Label-based access control

Row and column access control

REVOKE USE OF TABLESPACE mytbsp FROM user1

REVOKE ALTER ON TABLESPACE mytbsp FROM user1

REVOKE UPDATE OF TABLESPACE mytbsp FROM user1

REVOKE CREATETAB ON TABLESPACE mytbsp FROM user1

Locked

Waiting

Pending

Unconnected

When mandatory access control (MAC) is used to protect the table.

When discretionary access control (DAC) is used to protect the table.

When label-based access control (LBAC) is used to protect the table.

When row and column access control (RCAC) is used to protect the table.

INSERT

SELECT

CREATE

TRIGGER