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

View

Index

Table

Trigger

Procedure

inline

binary

internal

partitioned

IBM Data Studio

IBM Administrator

IBM Data Manager

IBM Command Center

DB2 for i

DB2 for AIX

DB2 for z/OS

DB2 for Linux

AIX

iOS

z/OS

Windows

Control Center

IBM Data Studio

Universal Connection Expert

DB2 Connection Pool Manager

SELECT name, COUNT(*) AS transactions FROM sales

SELECT name, COUNT(DISTINCT name) AS transactions FROM sales

SELECT name, COUNT(*) AS transactions FROM sales GROUP BY name

SELECT DISTINCT name, COUNT(*) AS transactions FROM sales GROUP BY transactions

When a ROLLBACK statement is executed, all locks held by the terminating transaction are released.

When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired up to the savepoint are
released.

When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired after the savepoint are
released.

When a ROLLBACK statement is executed, all locks acquired for open cursors that were declared
WITH HOLD, are held.

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.

SELECT * FROM employee WHERE salary < 100000

SELECT salary FROM employee WHERE salary > 100000

SELECT salary FROM employee WHERE salary > 100,000.00

SELECT * FROM employee WHERE salary GREATER THAN 100000

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

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

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

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

A Java stored procedure.

A REXX stored procedure.

A native SQL stored procedure.

An external SQL stored procedure.

SELECT workdept, AVG(salary) FROM employee ORDER BY workdept GROUP BY 2

SELECT workdept, AVG(salary) FROM employee ORDER BY workdept ARRANGE BY 2

SELECT workdept, AVERAGE(salary) FROM employee GROUP BY workdept ORDER BY 2

SELECT workdept, AVG(salary) FROM employee GROUP BY workdept ORDER BY 2

They store user-based period information.

They do not have a separate history table.

They can be queried without a time period specification.

They manage data based on time criteria specified by users or applications.

The statement will fail because a subquery cannot be used in an UPDATE statement.

The statement will fail because the result set produced by the subquery will contain more than one row.

The statement will succeed; the last record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation.

The statement will succeed; the first record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation

The UPDATE statement logs the before and after value of every column it modifies.

A searched UPDATE is used to update one or more rows and a positioned UPDATE is used to update exactly one row.

When the UPDATE statement modifies parent key columns, the value of corresponding foreign key columns are also modified.

The UPDATE statement modifies the values of specified columns in the rows of a table, view, or underlying table(s) of a specified fullselect.

SELECT state_cd, COUNT(*) FROM states ORDER BY COUNT(*);

SELECT state_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY state_cd;

SELECT region_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY COUNT(*);

SELECT region_cd, COUNT(*) FROM states GROUP BY region_cd ORDER BY COUNT(*);

The SYSIBM schema.

The SYSFUN schema.

A schema with the name of the UDF.

A schema with the userid of the UDF definer.

To provide a method of avoiding duplicate entries for a column

To provide a method of ensuring a column's value are within a specific range

To ensure that XML columns are properly populated in an associated XML table space

To enforce relationships between tables by checking the existence of rows in one table against another.

A table can have only one foreign key

Foreign keys can only be defined when a table is created

A foreign key can be defined on a column or a set of columns in a table

Foreign keys are enforced only during the execution of INSERT statements

Base table

Temporal table

Transition table

Temporary table

Multiple primary keys can be defined for a table

A foreign key can reference multiple primary keys

A primary key can be referenced by only one foreign key

Primary keys and foreign keys are used to define relationships

Alias

View

Index

Sequence

Define a view on one or more tables or views

Revalidate all invalid objects that are referenced on a view's fullselect

Ensure that any inserted or updated rows conform to the definition of the view

Define a view that only consists of columns that have check constraints defined

Check

Unique

Referential

Informational

Execution of an INSERT statement

Execution of LOAD REPLACE operation

Execution of an INSERT operation of a MERGE statement

Execution of an UPDATE operation of a MERGE statement

To override the limit on index key columns

To further enforce the uniqueness of index values

To combine two or more indexes to create a single unique index

To improve performance of some queries by allowing index-only access

To update data in other tables

To perform insert, update and delete operations against complex views

To check or modify values before they are actually stored in a database

To run non-database operations that are coded in user-defined functions

CREATE TABLE parts (part_no INTEGER, desc CLOB))

CREATE TABLE parts (part_no INTEGER, desc VARCHAR(200))

CREATE TABLE parts (part_no INTEGER, desc CLOB FOR BIT DATA))

CREATE TABLE parts (part_no INTEGER, desc VARCHAR(200) FOR BIT DATA)

Trigger

Procedure

Table function

Scalar function

BIGINT

BINARY

DOUBLE

DECIMAL

1) Create a primary key on table TAB1; 2) Create a foreign key on table TAB2 that references the primary key on table TAB1 and adheres to the ON DELETE CASCADE rule.

Create a primary key on table TAB2; 2) Create a foreign key on table TAB1 that references the primary key on table TAB2 and adheres to the ON DELETE CASCADE rule.

1) Create a primary key on table TAB1; 2) Create a foreign key on table TAB2 that references the primary key on table TAB1 and adheres to the ON DELETE RESTRICT rule.

1) Create a primary key on table TAB2; 2) Create a foreign key on table TAB1 that references the primary key on table TAB2 and adheres to the ON DELETE RESTRICT rule.

The victim will be rolled back.

The victim will read through the lock.

Both victim and holder are rolled back.

The victim must wait until the holder releases the lock.

Lock wait

Lock timeout

Lock escalation

Lock conversion

Lock exchange

Lock promotion

Lock escalation

Lock conversion

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.

Concurrent application processes from performing any operations on the table.

Concurrent application processes from performing any read-only operations on the table.

Concurrent applications processes that are running under the cursor stability (CS) isolation level from performing read-only operations on thetable.

Concurrent applications processes that are running under the uncommitted read (UR) isolation level from performing read-only operations onthe table.

Lock size

Number of locks

Lock mode needed

Available real storage

Read Stability (RS)

Cursor Stability (CS)

Repeatable Read (RR)

Uncommitted Read (UR)

Cursor Stability (CS)

Repeatable Read (RR)

Read Stability (RS) with Currently Committed semantics

Cursor Stability (CS) with Currently Committed semantics

Read stability (RS)

Cursor stability (CS)

Repeatable read (RR)

Uncommitted read (UR)