Javascript Closure's and Currying!

Closures are a powerful and important feature of Javascript. Before trying to understand the syntax of how to create one it's important to understand just why they are an important feature of the language. Firstly closures are a way of creating objects. But, so what? Javascript already provides two ways to do this: Object literals and Constructor functions. So what's wrong with these mechanisms? Well they do not provide any sort of encapsulation. Get a handle to an object created via the literal object approach or the constructor function approach and you can pretty much can change anything in the object.

Watch this poor literal object's encapsulation dreams smash...

myObject = {
    myProperty:"I wish I was encapsulated"
}
console.log(myObject.myProperty);  //outputs I wish I was encapsulated
myObject.myProperty = "sorry mate you're note";
console.log(myObject.myProperty);  //outputs sorry mate you're note

As for the Constructor function...

var Person = function(name) {
    this.name = name;
    this.speak = function () {
        return "I am " + this.name;
    }
}

var tony = new Person("Tony");
console.log(tony.speak());  // outputs I am Tony

// Tony's encapsulation dreams are going to get smashed.
tony.name = "Fatso";
console.log(tony.speak());  // outputs I am fatso

This is awful. Put yourselves in Tony's shoes and think about how he feels. This lack of encapsulation causes problems. Some programmers used the convention of an underscore before a property (as in _name) to try to indicate they wished they could make a property private but they couldn't. The underscore was saying: "please, please don't touch me".  But who wants to engineer logical systems around emotional pleas?

Closures provide a mechanism for encapsulating objects. You want to encapsulate Tony's name (and every other person's name) do this:

var person = function(name) {
    console.log(">> setting name to " + this.name);
    return {
        getName: function(){
           return name;
        } 
    }
}
var tony = person("Tony");  // outputs setting name to Tony
console.log(tony.getName());  // outputs Tony
tony.name = "Fatso"   // it won't change the name in the closure
console.log(tony.getName()); // still outputs Tony

WOW! at last some encapsulation. Let me try to explain... The outer function returns an Object literal. This lives longer than the outer function. The outer function effectively ends as soon as person("tony") is finished. The object literal lives longer because it is returned. Properties and methods in the object literal can access variables in the outer function. Just like the way any inner function can access variables in the outer function. Now, when the object literal is returned it "closes" over the values of the variables in the outer function it can access. Effectively, getting keeping a copy of them. It would be the exact same for an inner function if it was returned. What is returned is called the closure. The outside world cannot directly access what the closure closes over. It can only access what the closure itself advertises to the outside world. In this case, that is just one function getName(). This all means we can make encapsulated person objects.

Currying
Currying is the process of reducing the number of arguments passed to a function by setting some argument(s) to predefined values.  Consider this function...

function outputNumbers(begin, end) {
    var i;
    for (i = begin; i <= end; i++) {
        print(i);
    }
}
outputNumbers(0, 5);  // outputs 0, 1, 2, 3, 4, 5
outputNumbers(1, 5);  // outputs 1, 2, 3, 4, 5

Now suppose we want a similar function with a fixed "begin" value.  Let's say the "begin" value was always 1.  We could do:

function outputNumbersFixedStart(start) {
    return function(end) {
        return outputNumbers(start, end);
    }
}

And then defined a variable to be this new function...

var outputFromOne = outputNumbersFixedStart(1);
outputFromOne(3);  1, 2, 3
outputFromOne(5);  1, 2, 3, 4, 5

As can be seen, the number of arguments has been reduced from 2 to 1. And this has been enabled because the argument that has been eliminated has been set to a fixed value, in this case its value is 1.

Currying works by creating a closure that holds the original function and the arguments to curry.  A generic solution to currying is to add a "curry" function to every function in our code by augmenting function's prototype to include a curry function.  

Function.prototype.curry = function() {
    if (arguments.length<1) {
        return this; //nothing to curry with - return function
    }
    var that = this;
    var slice = Array.prototype.slice;
    var args = slice.apply(arguments);
    return function() {
        var innerFunctionSlice = slice.apply(arguments);
        return that.apply(null, args.concat(slice.apply(arguments)));
    }
}

This curry function can be applied to any function.

var outputFromZeroCurried = outputNumbers.curry(0);  
var outputFromOneCurried = outputNumbers.curry(1);   

outputFromZeroCurried(3);  // outputs 0, 1, 2, 3
outputFromOneCurried(5);  // outputs 1, 2, 3, 4, 5

Note: One thing to bear in mind in the curry prototype function is that the value of arguments in the outer function is different to the value of arguments in inner function.  When outputNumbers.curry(0) is invoked, arguments in the outer function is {0}. When outputFromZeroCurried(3) is invoked, arguments for inner function is {3}, but arguments for the outer function is still {0}. Essentially, 'arguments' in the outer function refers to arguments passed to the outer function; 'arguments' for the inner function refers to arguments for the inner fuction.

This smells like a Curry!

Are you confused by "this" Javascript?

George Bush found many things confusing!

The keyword "this" is important in javascript but it is also confusing.  The reason is because it's not always easy to ascertain what it actually means. It has different values depending on where it is in a piece of code and more importantly what context the code is invoked in.

In 'Javascript: The Good Parts', Javascript Guru Doug Crockford describes four different invocation patterns, the "this" parameter is initialised differently in all four patterns: the method invocation pattern, the function invocation patter, the constructor invocation pattern and the apply invocation pattern

The Method Invocation Pattern
When a function is property of an object in javascript it is a method. When the method is invoked, the 'this' refers to that object.  For example, in the code below the property getName is an anonymous function.  If the anonymous function wishes to refer to the name variable it uses the 'this' notation.

var myObject = {
    name: "Staveley",
    getName: function () {
        return this.name;
    }
};

console.log(myObject.getName());// Staveley.

It is worth stressing the importance of 'this' in the example above.  If it is not used, the name will be the global one.

name="GlobalName";
var myObject = {
    name: "Alex",
    getName: function () {
        return name; // refers to name in global namespace.
    }
};
console.log(myObject.getName());// Outputs GlobalName not Alex.

The Function Invocation Pattern
When a function is defined not as a method but just as a function (i.e. there is no property defining it in an object),  the keyword 'this' refers to the the global object.

var getNameFunction = function() {
    name: "Alex";
    console.log(">>getNameFunction(),name=" + this.name);  
};
// Invoking getNameFuction() outputs: undefined.  
// Because, this is equal to the global object 
// which has no name property.
getNameFunction(); 

When another object has a property which calls the getNameFunction, the 'this' inside the
getNameFunction refers to that other object.

var obama = {
    name: "obama"
};

obama.getName = getNameFunction;
obama.getName();  // outputs obama!

But, an inner function does not share the method's access to the object as its 'this' is bound to the wrong value.

obama.getFullName = function() {
    var outputName = function() {
        console.log("barack " + this.name);
    };
    outputName();
} 
// Invoking getFullName() outputs barack undefined. Why? 
// Because the innerfunction's 'this' refers to the global variable. 
// The global variable has no name property.
obama.getFullName(); 

The solution is to assign another variable to the value this. By convention this variable takes the name 'that'.

obama.getFullName = function () {
    var that = this;
    var outputName = function() {
        console.log("barack " + that.name);
    };
    outputName();
};
obama.getFullName(); // outputs barak obama

Inner functions confused Obama!

The Constructor Invocation Pattern
Objects can be easily defined using object literal syntax. However, sometimes when many objects of the same type need to be defined there is a need for consistency. There is also a need to avoid repetition. Two good reasons to use constructor functions! 

var Car = function(colour, reg){
    this.colour= colour;  // this refers to the object being created.
    this.reg = reg;
};

var myRedCar = new Car("Red", "00D901");
var myGreenCar = new Car("Green", "00D902");
var myBlueCar = new Car("Blue", "00D903");  

// Now test those assignments worked. 
console.log(myRedCar.colour + ", " + myRedCar.reg);   // outputs Red, 00D901
console.log(myGreenCar.colour + ", " + myGreenCar.reg); // outputs Green, 00D902
console.log(myBlueCar.colour + ", " + myBlueCar.reg);  // outputs Blue, 00D903

Now suppose we want to add a method common to all 'Car' objects. We do:

Car.prototype.getColour = function() {
    console.log(">> getColour(), colour = " + this.colour);
};

In this case, the 'this' refers to the object created with the new prefix

myRedCar.getColour();  // outputs Red
myGreenCar.getColour(); // outputs Green
myBlueCar.getColour();  // outputs Blue

The Apply Invocation Pattern
When Apply (or Call) is used we are allowed to choose the value of 'this'.  To do this,
we simply specify it as an argument.

myRedCar.getColour.apply(myGreenCar);  // outputs green
myGreenCar.getColour.apply(myRedCar); // outputs red

References:
1. Brilliant tutorial on Javascript objects: http://helephant.com/2008/08/17/how-javascript-objects-work/
2. Douglas Crockford: http://javascript.crockford.com/javascript.html

Problems debugging Java after ANT compile

ANT    

Ok, this one is pretty easy but worth posting. I lost a few hours because of it and I don't want the same to happen to you! Suppose you want to be able to pass a switch into an ANT target which performs a javac to tell it to include debug information or not.

Your properties file will contain properties such as:
The ANT compile target then uses these properties:

However, the debug information is not there when you are debugging.  You run
ant -v compile to get more information. You see:

Now, as we can see the properties are echo'd as expected.  But, the "-g:none" indicates the compile won't include debug information.  Before you tear your hair out, relax! You have just made a very silly mistake we all make at sometime. When ANT reads property files it reads all property values literally. This means there is a difference between "true" and "true ", i.e. boolean properties should not have trailing spaces otherwise they will not be read as boolean properties. This is what happened here.  Ouch!

Ouch - trailing space!

As it states in http://ant.apache.org/manual/Tasks/property.html, regarding property files: "Trailing spaces are not stripped. It may have been what you wanted."

So rip that trailing space and re-run the ant compile target.

You should see:


which means the compiler is going to add lines, vars and source debug information.  Happy debugging!


References:
1. http://ant.apache.org/manual/Tasks/property.html

DB2 cheat sheet

Some useful DB2 commands

DB2 System Commands

• DB2LEVEL -- checks version of DB2 installed.
• DB2ILIST -- lists all instances installed
• DB2CMD -- opens a command line processor
• DB2CC -- opens db2 control center
• DB2LICM -l -- gets db2 type. 

Command Line Processor Commands

• DB2 LIST NODE DIRECTORY -- Lists all nodes
• DB2 CATALOG TCPIP NODE DB2NODE REMOTE MACHINE215 SERVER 50000 -- catalogs node.  In this case, node is db2Node on the machine with name machine215. Port is 50000.
• DB2 LIST DATABASE DIRECTORY -- list databases
• DB2 GET DB CFG FOR SAMPLE -- get configuration info for the SAMPLE db.
• DB2 CONNECT TO alexDB USER myuser USING mypass -- connect to db. In this case, database is alexdb, usern is myuser and password is mypass.
• DB2 DISCONNECT alexdb  -- disconnects
• DB2 LIST APPLICATIONS SHOW DETAIL -- shows all running db's
• DB2 GET DBM CFG -- view authentication paramater (e.g. something like server_encrypt)
• DB2 UPDATE DBM CFG USING AUTHENTICATION SERVER_ENCRYPT -- alter the authentication mechanism to server_encrypt 
• DB2 GET AUTHORIZATIONS -- get authorisation level. 

Database commands via Command Line Processor (CLP)

• DB2 GET DATABASE CONFIGURATION -- gets current database configuration
• DB2 VALUES CURRENT USER - - gets the current user
• DB2 VALUES CURRENT SCHEMA -- gets the current schema
• DB2 VALUES CURRENT QUERY OPTIMIZATION -- get query optimization level.

Schemas

• DB2 SELECT SCHEMANAME FROM SYSCAT.SCHEMATA -- list all schemas
• DB2 VALUES CURRENT SCHEMA -- gets the current schema
• DB2 SET SCHEMA ALEXSCHEMA -- set schema

Tables

• DB2 LIST TABLES FOR schema_name -- list all tables for particular schema
• DB2 LIST TABLES SHOW DETAIL; -- show detail about tables
• DECLARE GLOBAL TEMPORARY TABLE -- declares a temporary table
• CREATE TABLE MQT AS (SELECT c.cust_name, c.cust_id, a.balance FROM customer c, account a WHERE c._cust_name IN ('Alex') AND a.customer_id - c.cust_id) DATA INITIALLY DEFERRED REFRESH DEFERRED -- Creates a materialised query table. In this case the MQT is based on a join query from the customer and account table.

Tablespaces

• DB2 LIST TABLESPACES SHOW DETAIL -- show detail about table spaces
• SELECT * FROM SYSCAT.TABLESPACES;  -- show what syscat has about tablespaces
• SELECT tbspace, bufferpoolid from syscat.tablespaces;  -- get tablespace and bufferpoolid
• SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACE=2; -- Check what TABLES are in tablespace where id = 2.

Constraints

• SELECT * FROM SYSCAT.TABCONST;  -- Table constraints
• SELECT * FROM SYSCAT.CHECKS;  -- Colum checks
• SELECT * FROM SYSCAT.COLCHECKS; -- Column constraints 
• SELECT * FROM SYSCAT.REFERENCES; --  Referential constraints

Sequences

• CREATE SEQUENCE STESTRESULT AS INTEGER INCREMENT BY 1 START WITH 1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 10 ORDER;  -- Create Sequence starting with 1 which cache 10 values
• SELECT * FROM SYSCAT.SEQUENCES; -- Gets systcat info on sequences
• VALUES NEXT VALUE FOR MYSEQ; -- Gets next value from sequence myseq
• ALTER SEQUENCE MYSEQ RESTART WITH 11 INCREMENT BY 1 MAXVALUE 10000 CYCLE CACHE 12 ORDER -- Changes MySeq sequence

Locksize

• SELECT TABNAME, LOCKSIZE FROM SYSCAT.TABLES WHERE TABNAME = ' EMPLOYEES';  -- Check locksize which can be tablespace, table, partition, page, row - (usually row).

Bufferpools

• SELECT bpname, npages, pagesize from syscat.bufferpools -- get useful buffer pool info.
• SELECT buffer.bufferpoolid, buffer.bpname, buffer.npages, buffer.pagesize, tablespace.tbspace, tablespace.tbspaceid from syscat.bufferpools buffer, syscat.tablespaces tablespace where tablespace.bufferpoolid = buffer.bufferpoolid;  -- gets buffer pool and corresponding tablespace info.

Indexes

• SELECT * FROM SYSCAT.INDEXES --  show all indexes
• SELECT COLNAMES, TABNAME, INDEXTYPE, CLUSTERRATIO, CLUSTERFACTOR FROM SYSCAT.INDEXES WHERE TABNAME = 'TPERSON';  -- some useful columns

Functions

• SELECT * FROM SYSCAT.FUNCTIONS;  -- check what functions DB has.

SYSDUMMY1 commands

• SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1; -- gets current date.
• SELECT HEX(36) FROM SYSIBM.SYSDUMMY1;  -- same as VALUES HEX(36)
• SELECT XMLCOMMENT ('This is an XML comment') FROM SYSIBM.SYSDUMMY1;

Runstats

• RUNSTATS ON TABLE TAUSER1.TOSUSER FOR INDEXES ALL;  -- runstats for all indexes

Checking the last time runstats was run...

• SELECT CARD, STATS_TIME FROM SYSCAT.TABLES WHERE TABNAME = 'TOSUSER';
• SELECT NLEAF, NLEVELS, FULLKEYCARD, STATS_TIME, TABNAME, INDNAME FROM SYSCAT.INDEXES WHERE TABNAME = 'TOSUSER';

The following catalog columns can be queried to determine if RUNSTATS has been performed on the tables and indexes:

• If the CARD column of the SYSCAT.TABLES view displays a value of -1, or the STATS_TIME column displays a NULL value for a table, then the RUNSTATS utility has not run for that table.
• If the NLEAF, NLEVELS and FULLKEYCARD columns of the SYSCAT.INDEXES view display a value of -1, or the STATS_TIME column displays a NULL value for an index, then the RUNSTATS utility has not run for that index.

The A to Z of DB2

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!

Seán O'Brien