SYSADM is the only authority which can:
- update the DMB CFG file
- grant SYS* authorities to other groups. Grant DBADM authority to users / groups
- access data within any database
- do anything SYSCTRL can do
- only access data in database if given privilege
- can create/drop databases, tablespaces
- do anything SYSMAINT can do
- db2start/ db2stop / backup / restore/ runstats
- do anything for a particular database
- grant load authority to other users
The following Authentication types are available:
SERVER: authentication takes place on the Server
SERVER_ENCRYPT: authentication takes place on the Server username / password is encrypted on the client before being sent.
CLIENT, KERBEROS, KRB_SERVER_ENCRYPT, DATA_ENCRYPT, DATA_ENCRYPT_CMP, GSSPLUGIN, GSS_SERVER_ENCRYPT.
If a Client uses the SERVER_ENCRYPT and the Server uses SERVER authentication type and error will occur when client tries to connect to the Server.
Common Table Expressions
Common Table Expressions exist only for the life of the SQL statement that created them. They are used for special cases e.g. recursion in a query.
The syntax is: WITH [tablename] ...
Interactive GUI for SQL commands
The Configuration Assistant enables users to configure clients so that they can access databases stored on remote DB2 servers. The configuration assistant allows users to catalog, uncatalog databases, bind applications, set DB2 registry parameters, changes passwords, test database connections.
Connect Enterprise Edition
Connect Enterprise Edition is an add on product that allows data to be moved between Linux, Unix, Windows, iSeries and zSeries based DB2 servers.
Constraints are used to enforce business rules (e.g. attributes in a column cannot be null). The SQL used to create constraints is stored in the System Catalog.
Performs admin for instances / databases / bufferpools / tablespaces/ views / indexes... Catalog / Uncatalog databases. And all sorts of other DB tasks.
Update and delete operations can be performed using position operations or search operations. In a position operation the cursor must be first created, opened and then positioned. When a cursor is declared with 'WITH HOLD' option, it will remain open across transactions until it is explicitly closed.
Cursor Usage Steps
The steps to use a cursor are: DECLARE CURSOR, OPEN, FETCH, CLOSE.
Database Manager Configuration File
Stores the names of the groups which have been given instance level authorities (SYSADM, SYSCTRL, SYSMAINT, SYSMON)
There four ways to define decimal types: DECIMAL(percision, scale), DEC(precision, scale), NUMERIC(precision, scale) and NUM(precision, scale)
The DCS directory stores database information for remote databases on z/OS iSeries.
Declared temporary tables
User defined tables to hold non persistent data. They are created by the application and destroyed when the application terminates.
The design advisor makes recommendations for new indexes, deletion of indexes, Materialized Query Tables, Multi Dimensional Clustering
Developer Workbench (Development Center in v8)
Used to create, edit, debug, deploy, test DB2 stored procedures and user defined functions. Also to develop and run XML queries.
A distinct data type is a user-defined data type that is derived from one of the built in data types in DB2. Example of syntax creation: CREATE DISTINCT TYPE euro AS DECIMAL (9,3) WITH COMPARISON. Disinct types are strongly typed; they cannot be used as an argument for a built-in data type in a built-in function, even if they are derived from them (and vice versa). If the WITH COMPARISON syntax is used during creation, it means that comparison functions (<>, <, > , <=, >=, >) and ORDER BY, GROUP BY clauses can be used for the distinct type. Two casting functions are created anytime a distinct type is created. This is used to convert to / from its base type and has the same name as the distinct type.
Extenders are advanced data types that are not part of the built-in datatypes. There are 6 types of extenders in DB2.
- DB2 AVI extender
- DB2 Text extender
- DB2 Net search extender
- DB2 XML extender
- DB2 Spatial extender
- DB2 Geodetic extender - can treat earth like globe rather than flat map.
Grant All (table)
GRANT ALL privileges for table means: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES privileges. Note there is no 'ADD' privilege.
Graphic is a fixed length double-byte character type.
Used to specify columns that are to be grouped together and to provide input into aggregate functions such as SUM() and AVG()
Group By Cube
Used to group in multiple dimensions e.g. SELECT workdept, gender, AVG(salaray) AS avg_salary FROM employee GROUP BY CUBE (workdept, gender);
The having clause is used to by further selection criteria to a GROUP BY clause. It refers to data that has already been grouped by a GROUP BY clause. It uses same syntax as WHERE clause and can only be used in by the GROUP BY clause.
The Health Center tool is used to select instance and database objects that you want to monitor
Identity columns must be a numeric data type with a scale of 0.
The creation of an index provides logical ordering of rows in a table in ascending order of the index.
Repeatable read isolation level will lock rows scanned in a query.
Read stability isolation level will only lock the rows returned in the result data set.
Cursor stability isolation level will only lock the result set that the cursor is currently pointing to.
The uncommitted read isolation level will not lock any rows during normal read processing (unless another user tries to alter or drop the table being read).
The DB2 journal is an interactive GUI that tracks historical information about tasks, database actions and operations
the License Center Allows users to view information about licenses
Like (table creation)
CREATE TABLE ... LIKE ... - creates table which has exact same name, datatype and nullability characteristics.
Locks can only be applied to table spaces, tables and rows.
The act of changing a lock to a more restrictive state. In most cases, lock conversation happens for row level locks, e.g. if an Update(U) lock is held and an Exclusive(X) lock is needed, the update(U)lock will be converted to an Exclusive lock
Lock escalation is when the size of a lock changes. For example from Row to Table size. This is usually to free up some space in the Lock list.
The specific amount of memory set aside to hold a structure that DB2 uses to manage locks.
Lock state (or mode)
DB2 locks can have various states: Intent None, Intent share, Next Key Share, Share, Intent Exclusive, Share with Intent Exclusive, Update, Next Key Exclusive, Exclusive, Weak Exclusive, Super Exclusive. DB2 determines the lock state by looking at isolation level and the SQL being executed.
Materialised Query Tables:
User defined tables whose definition is based on the result of a query used for query optimization.
For predicates use IS NULL. In result sets, - means null. Unique indexes can one null value. Unique constraints can have never have a null value. Nulls can't be in used with IN clauses.
Operating System Support
|DB2 Type / OS||Linux||Windows||Solaris||Mobile OS||AIX||HP-UX||System i|
|DB2 Express C||Yes||Yes||No||No||No||No||No|
|DB2 i5 / Os||No||No||No||No||No||No||Yes|
A package is an object that contains the information needed to process SQL statements associated with a source code file of an application program.
Privilege: Alter (Table)
The alter table privilege allows user to add columns to a table, add / change table comments, create a table pk, unique/check constraint, triggers for table.
Reference table privilege allows a user to create and drop foreign key constrains that reference a table in a parent relationship. This can be granted for entire table of just specific columns in
Privilege: Usage (sequences)
Allows the PREVIOUS VALUE and NEXT VALUE expression associated with the sequence to be changed.
The BINDADD privilege at database allows a user to create packages in the database.
Privileges (Explicit / Implicity)
Explicit privileges have to be granted explicitly. Implictly privileges do not. For example, CONTROL is granted to anyone who creates a Table or View. CREATEIN, ALTERIN, DROPIN is granted to anyone who creates a schema.
Privileges: View / Nickname
There is no Alter, Index or Reference privilege for View. Otherwise they have have the same privileges as Tables. Nickhaves have the same privileges possible as Tables.
There is only one Index privilege - it is Control.
Facilitates data replication between a DB2 database and any other relational database
A routine is a user defined function or stored procedure
Satellite Admin Center
Allows users set up and administer a group of DB2 Servers
Sequences, identity columns and triggers can be used to generate values for a column. But, only sequences can be referenced in an INSERT statement.
A set operator is is used to combined two or more queries into a single query. Examples: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL
Spatial extender treats the world as flat map; the DB2 geodetic extender treats the world as a globe.
SQL Performance Monitor
To analyse database operations performed againsts a DB2 for i5/OS database
Char = (number of characters * 1) bytes required; 1 and 254 characters
Varchar = (number of characters + 4) bytes requires; 32,672 characters
LONG VARCHAR = (number of characters + 24) bytes required; 32,672 characters (table space agnostic)
GRAPHIC = (number of characters * 2) bytes required; 127 characters
VARGRAPHIC = (number of characters * 2) + 4 bytes required; 16, 336
LONG VARGRAPHIC = (number of characters * 2) + 24 bytes required; 16,350 characters (table space agnostic)
Structured Data Type
A structured data type is a user-defined type that contains one or more attributes, each of which has a name and a data type of its own. A SDT can also be created as a subtype of another structured type. SDT are created by the CREATE TYPE sql statement.
Share mode - Other transactions are allowed read data but not change the data that is locked.
Exclusive mode - Other transactions can neither read nor modify the data that is locked.
Allows users schedule, run tasks and send notifications about them
Time / Timestamp
Timestamp can store date time. Time can only store time.
- A trigger can be activated whenever an insert, update or delete operation is performed (not a select).
- A trigger event can be activated before, after or instead of the trigger event
- Trigger granularity: They can be activated for every row updated (FOR EACH ROW) or just for every statement (FOR EACH STATEMENT)
- To stop trigger events setting off other triggers use the NO CASCADE option
- A trigger event can reference old or new data using the 'REFERENCE OLD AS' or 'REFERENCE NEW AS' syntax
- A trigger can send signals.This can be used to prevent actions, for example: SIGNAL SQLSTATE '75002' SET MESSAGE TEXT 'Deletes not allowed'.
- The SQL used to create Triggers is stored in the system catalog.
User defined tables whose column definitions are based on the attributes of a user defined structured data type.
Universal Developer's Edition
Contains tools to build application supported Linux, UNIX, Windows and DRDA Application Requestor
Do not contain real data but instead refer to data in real tables. Only the view definition itself is stored in the database.
Useful for controlling access to data. The WITH LOCAL CHECK OPTION can be used to enforce data constraints for inserts, updates.
The WITH CASCADED CHECK OPTION can be used to cascade constraints to subsequent views. Characteristics of View tables are stored in the system catalog not the SQL that created them.
Gives visual representation of data access plan