In nearly every software architecture there is a relational database - somewhere. And in nearly every relational database there is a range of concepts and buzzwords. Some unique to a particular database vendor but many not. In this post we run through some concepts / buzzwords for DB2 - alphabetically!
Authority Levels
A DB2 Authority Level is a security level representing a collection of privileges and higher-level database manager maintenance and utility operations. SYSADM, SYSCTRL, SYSMAINT, SYSMON are instance level authorities and can only be assigned to a group. DBADM, SECADM and LOAD are database level authorities.
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
SYSCTRL:
- only access data in database if given privilege
- can create/drop databases, tablespaces
- do anything SYSMAINT can do
SYSMAINT can:
- db2start/ db2stop / backup / restore/ runstats
DBADM can:
- do anything for a particular database
- grant load authority to other users
Authentication Types
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] ...
Command Editor
Interactive GUI for SQL commands
Configuration Assistant
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
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.
Control Center
Performs admin for instances / databases / bufferpools / tablespaces/ views / indexes... Catalog / Uncatalog databases. And all sorts of other DB tasks.
Cursor Operations
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)
Decimal
There four ways to define decimal types: DECIMAL(percision, scale), DEC(precision, scale), NUMERIC(precision, scale) and NUM(precision, scale)
DCS directory
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.
Design Advisor
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.
Distinct Type
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
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.
Foreign Key Constraints for Delete:
ON DELETE CASCADE: When entity is deleted from parent table, any entity which has a FK to the parent entity will be deleted.
ON DELETE SET NULL: When entity is deleted from parent table, FK will be set to null
ON DELETE RESTRICT: When entity is deleted from parent table, FK values must point to same value
ON DELETE NO ACTION: When entity is deleted from parent table, FK values must point to something valid but can change
Foreign Key Constrains for Update:
ON UPDATE RESTRICT: When entity is updated in parent table, the FK values must have to have the same values
ON UPDATE NO ACTION: When the entity is updated in the parent table, the FKs values can change but must always point at something.
Grant All (table)
GRANT ALL privileges for table means: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES privileges. Note there is no 'ADD' privilege.
Graphic
Graphic is a fixed length double-byte character type.
Group By
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);
Having
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.
Health Center
The Health Center tool is used to select instance and database objects that you want to monitor
Identity Column
Identity columns must be a numeric data type with a scale of 0.
Indexes
The creation of an index provides logical ordering of rows in a table in ascending order of the index.
Isolation levels
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).
Journal
The DB2 journal is an interactive GUI that tracks historical information about tasks, database actions and operations
License Center
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
Locks can only be applied to table spaces, tables and rows.
Lock conversion
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
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.
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.
Null
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 Everyplace | No | No | No | Yes | No | No | No |
| DB2 PE | Yes | Yes | No | No | No | No | No |
| DB2 Express C | Yes | Yes | No | No | No | No | No |
| DB2 Express | Yes | Yes | Yes | No | No | No | No |
| DB2 i5 / Os | No | No | No | No | No | No | Yes |
| DB2 WSE | Yes | Yes | Yes | No | Yes | Yes | No |
| DB2 ESE | Yes | Yes | Yes | No | Yes | Yes | No |
Packages
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.
Privilege: Control
The control privilege that applies to Table, View, Nicknames, Packages and Indexes. It includes every privilege including the privilege to drop the object from the DB. The owner of a table automatically receives control privilege (and all other privileges). Only users with SYSADM or DBADM authority are allowed to explictily grant CONTROL privilege on any object. A user with CONTROL privilege can grant any table privilege except CONTROL privilege.
Privilege: Connect
(Database)
Connect Privilege applies to a database. It allows users to connect.
Privilege: References (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
the table.
Privilege: Usage (sequences)
Allows the PREVIOUS VALUE and NEXT VALUE expression associated with the sequence to be changed.
Privileges: Packages
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.
Privileges:Index
There is only one Index privilege - it is Control.
Replication Center
Facilitates data replication between a DB2 database and any other relational database
Routine
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
Sequences, identity columns and triggers can be used to generate values for a column. But, only sequences can be referenced in an INSERT statement.
SET operators
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 Extenders
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
Storage
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.
Table Locks
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.
Task Center
Allows users schedule, run tasks and send notifications about them
Time / Timestamp
Timestamp can store date time. Time can only store time.
Triggers
- 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.
Typed tables
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
View Tables
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.
Visual Explain
Gives visual representation of data access plan
XML Columns
XML columns are used to store documents as a hierarchial set of entities. The XML data type does not have a fixed length.
P.S. Well done Seán O'Brien on another super performance! Well done Ireland beating Italy and making the World Cup Quarter finals!
P.S. Well done Seán O'Brien on another super performance! Well done Ireland beating Italy and making the World Cup Quarter finals!
 |
| Seán O'Brien |
