Db2 Practice 1

4

8

16

32

Materialized Query Table

Sequence Object

SQL Procedure

Trigger

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 BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.

A history table must be defined and associated with the base 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

JDBC

XML

RUBY

Alias

Index

Schema

Collection

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

Data partitioning feature

Workload manager

Self-tuning memory manager

Reference

Address

Pointer

Locator

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

Universal Connection Expert

DB2 Connection Pool Manager

IBM Data Studio

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 conversion/promotion

Lock switch/exchange

Lock substitution

Uncommitted read (UR)

Read stability (RS)

Cursor stability (CS)

Repeatable read (RR)

SELECT

GRANT

DROP

BIND

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

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

Data stored in global temporary tables is automatically deleted.

All locks held on the database are automatically released.

ROLLBACK TO SAVEPOINT

COMMIT TO SAVEPOINT

ROLLBACK

COMMIT

CALL conv_temp(climate_info.avg_temp);

SELECT conv_temp(avg_temp) FROM climate_info;

CALL convert_ftoc(climate_info.avg_temp);

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

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

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS ONLY

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS ONLY

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5 ROWS

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

Assign the SSN column to a restricted role that only authorized users can access.

Only grant ACCESSCTRL authority for the SSN column to users who need to access SSN data.

Alter the table definition so that SSN data is stored in a separate schema that only authorized users can access.

Create a view for the EMPLOYEE table that does not contain the SSN column and require unauthorized users to use the view.

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