DB2 SELECT statements acquire share locks on rows or pages while processing, which can sometimes result in contention or concurrency issues when other in-flight transactions have updated the data (either via INSERT, UPDATE, or DELETE statements) that the SELECT statement is trying to read, but have not yet committed their updates. This will cause the reader to go into a lock wait state, and it will remain in that lock wait state until the updater either issues a commit or a rollback (thus releasing its locks), or until it has surpassed a DB2 system defined lock timeout threshold.
Lock avoidance for readers can be attained by performing an uncommitted read (“UR”), in which the read transaction will return uncommitted updates in its result set. While this may be acceptable for some kinds of processing (i.e. rough counts or aggregations, where 100% accuracy or data integrity is not necessarily needed), it may not be acceptable for other processing in which there can be zero tolerance for inaccurate data.
Access to “currently committed” data is an option that is available in DB2 v10. When enabled it will allow for lock waits to be avoided when uncommitted inserts or deletes are encountered during the processing of a select statement. Avoiding a lock wait for uncommitted updates is not currently available.
When access to currently committed data is enabled, any rows that are encountered by the select statement that were inserted by in-flight uncommitted transactions will be ignored. Likewise, any rows that are encountered that have been deleted by in-flight uncommitted transactions will be included. In essence, the select statement is only looking at the rows that existed in the table before the in-flight updating (insert and/or delete) transaction started.
As mentioned previously, access to currently committed updates (i.e. accessing the “old”, or currently committed, version of a row before it was updated) is not yet allowed, and lock waits will continue to occur whenever rows that have been updated by in-flight uncommitted transactions are encountered. However, it is not unreasonable to assume that IBM is working on this restriction, and that it will be allowed in some future release or update to DB2.
Access to currently committed data is enabled by a new BIND parameter for packages and plans, and also by the use of new keywords during the PREPARE of dynamic SQL statements.
Wednesday, June 5, 2013
Hash Tables
Hash table access is now available in DB2. Hash organized tables allow for direct access to a specific row based on a hashing algorithm that is applied to the primary key of the table, without the need for an index. This can greatly improve performance for certain kinds of tables, in that “traditional” access via an index can typically involve up to four bufferpool getpages, and possibly up to four physical I/O’s to disk. With a well designed and well organized hash table, these numbers can ideally be reduced to one bufferpool getpage and zero or one physical I/O.
Hash tables should generally only be considered for tables that are stable or predictable in size and where the data is mostly accessed by its primary key (in particular, the entire primary key, if the PK consists of multiple columns). The primary key data should be diverse enough to minimize the probability of collisions that result from too many rows hashing to the same location. When a collision occurs, subsequently inserted rows will need to be relocated from their targeted hashed location, with a pointer inserted in the target location that points to the new location. The more relocated rows there are in the hash table, the less benefit hash table access will provide as additional getpages and I/O’s may be needed to retrieve data, thus negating the intent of hashing.
Since the nature of hash tables dictates that rows are randomly distributed throughout the physical tablespace, clustering indexes are not allowed to be defined on them, though non-clustered indexes can be. Therefore, hash organization for a table is not a viable choice if sequential processing of large portions of the table is desired.
Hash tables should generally only be considered for tables that are stable or predictable in size and where the data is mostly accessed by its primary key (in particular, the entire primary key, if the PK consists of multiple columns). The primary key data should be diverse enough to minimize the probability of collisions that result from too many rows hashing to the same location. When a collision occurs, subsequently inserted rows will need to be relocated from their targeted hashed location, with a pointer inserted in the target location that points to the new location. The more relocated rows there are in the hash table, the less benefit hash table access will provide as additional getpages and I/O’s may be needed to retrieve data, thus negating the intent of hashing.
Since the nature of hash tables dictates that rows are randomly distributed throughout the physical tablespace, clustering indexes are not allowed to be defined on them, though non-clustered indexes can be. Therefore, hash organization for a table is not a viable choice if sequential processing of large portions of the table is desired.
Trusted Context and Roles
A trusted context establishes a trusted relationship between DB2 and an external entity, such as an application server or another DB2 system. When the external entity (i.e. DB2 client) connects to DB2, DB2 determines if that entity and connection can be trusted by evaluating a series of attributes defined in the trusted context. After a trusted connection has been established, then the DB2 authorization ID that has been used for the connection can have access to a set of privileges via the trusted context that would not normally be available to it outside of the trusted connection. This is done through the assigning of privileges to roles rather than directly to IDs, RACF groups or UN IX groups. Roles are only available within the confines of trusted contexts, and provide the ability to more finely control from where one or more privileges can be exercised.
Trusted context, in conjunction with roles, thus allow you to restrict the privileges associated with a particular ID based on from where that ID is attempting to perform its activities.
For example: a trusted context can be set up for functional ID “XAPPID1” and application server “APPSRV1” and assigning it a role of “APPSIUD”, which gives it SELECT, INSERT, UPDATE and DELETE privileges on all the tables in a particular database. This is a level of access that has not been granted directly to the XAPPID1 itself, or to any RACF or UNIX group that XAPPID1 may be a member of. When XAPPID1 connects to DB2 from application server APPSRV1, it will be able to read and update the tables in question as it has established a trusted connection via the trusted context and has picked up these privileges through the role associated with that trusted context. However, if somebody happens to know or learn the password associated with XAPPID1 (which is a common occurrence at Phoenix) and attempts to connect to DB2 from another source (say, their workstation if they have a DB2 Connect client, or by logging on to the mainframe or UNIX database server directly with that ID and password), they will not have established a trusted connection, and therefore will not have access to the database in question.
Trusted context, in conjunction with roles, thus allow you to restrict the privileges associated with a particular ID based on from where that ID is attempting to perform its activities.
For example: a trusted context can be set up for functional ID “XAPPID1” and application server “APPSRV1” and assigning it a role of “APPSIUD”, which gives it SELECT, INSERT, UPDATE and DELETE privileges on all the tables in a particular database. This is a level of access that has not been granted directly to the XAPPID1 itself, or to any RACF or UNIX group that XAPPID1 may be a member of. When XAPPID1 connects to DB2 from application server APPSRV1, it will be able to read and update the tables in question as it has established a trusted connection via the trusted context and has picked up these privileges through the role associated with that trusted context. However, if somebody happens to know or learn the password associated with XAPPID1 (which is a common occurrence at Phoenix) and attempts to connect to DB2 from another source (say, their workstation if they have a DB2 Connect client, or by logging on to the mainframe or UNIX database server directly with that ID and password), they will not have established a trusted connection, and therefore will not have access to the database in question.
Clone Tables
Clone tables allow for two exact structural duplicate instances of a specific table to exist in a database. One instance will be the “active” instance, and the second instance will be the clone. They will be structurally identical in almost every way (columns, indexes, check constraints, tablespace, etc.), with the exception that the clone instance of the table will have a unique name. The only significant difference would be in the data they contain.
The intent of clone tables is to allow for a fast replacement of the data loaded in a table. The active instance of the table will contain the active data, while the clone table will initially be empty when it is first created. The clone can then be populated with data using normal DB2 processing, such as SQL INSERT statements or a DB2 LOAD utility. This allows for a new copy of the data to be staged in the clone instance of the table without impacting the active instance of the table.
When it is time to replace the active instance of the table with the new data that resides in the clone instance of the table, the EXCHANGE DATA statement will be issued. This will cause the active and clone instances of the table to be swapped such that the active table now contains the new data and the clone table now contains the old data. Any references to the active table now will have access to the new data. The data in the clone instance of the table can now be prepared for the next refresh.
The main advantage of clone tables is that they allow for very quick refreshes of data with nearly zero outage time for the table in question. The EXCHANGE DATA statement just requires a momentary outage in order to perform the physical swap of the underlying VSAM datasets that make up the active table tablespace and associated indexes.
Prior to the availability of clone tables, the most practical way to do a full refresh of data in a table was via a LOAD REPLACE utility operation. Depending on the amount of data being loaded, this could result in an outage of anywhere from seconds to minutes to hours. The utilization of clone table greatly enhances the availability of DB2 tables that need to be periodically refreshed with new data.
The intent of clone tables is to allow for a fast replacement of the data loaded in a table. The active instance of the table will contain the active data, while the clone table will initially be empty when it is first created. The clone can then be populated with data using normal DB2 processing, such as SQL INSERT statements or a DB2 LOAD utility. This allows for a new copy of the data to be staged in the clone instance of the table without impacting the active instance of the table.
When it is time to replace the active instance of the table with the new data that resides in the clone instance of the table, the EXCHANGE DATA statement will be issued. This will cause the active and clone instances of the table to be swapped such that the active table now contains the new data and the clone table now contains the old data. Any references to the active table now will have access to the new data. The data in the clone instance of the table can now be prepared for the next refresh.
The main advantage of clone tables is that they allow for very quick refreshes of data with nearly zero outage time for the table in question. The EXCHANGE DATA statement just requires a momentary outage in order to perform the physical swap of the underlying VSAM datasets that make up the active table tablespace and associated indexes.
Prior to the availability of clone tables, the most practical way to do a full refresh of data in a table was via a LOAD REPLACE utility operation. Depending on the amount of data being loaded, this could result in an outage of anywhere from seconds to minutes to hours. The utilization of clone table greatly enhances the availability of DB2 tables that need to be periodically refreshed with new data.
MERGE SQL Statement
The MERGE statement allows for a target table to be updated using the specified input data. It basically combines the functionality of an INSERT statement and an UPDATE statement into a single statement. The MERGE statement is issued with a search condition, which is usually searching on a key value. If a match is found, then the target row is updated with the specified input data. If a match is not found, then a new row is inserted into the target table with the specified input data.
This can simplify traditional processing in which an application program will execute a SELECT statement to determine the existence of a target row in a table. If the row is found, then the application program will execute an UPDATE statement to update the row; otherwise, the application program will execute an INSERT statement to add a new row to the table. The MERGE statement will now combine these three separate statements into a single SQL operation.
The MERGE statement is also enabled for multi-row processing, similar to multi-row fetch and multi-row insert processing.
This can simplify traditional processing in which an application program will execute a SELECT statement to determine the existence of a target row in a table. If the row is found, then the application program will execute an UPDATE statement to update the row; otherwise, the application program will execute an INSERT statement to add a new row to the table. The MERGE statement will now combine these three separate statements into a single SQL operation.
The MERGE statement is also enabled for multi-row processing, similar to multi-row fetch and multi-row insert processing.
TRUNCATE TABLE SQL Statement
The TRUNCATE TABLE statement, which has long been available in other DBMS’s such as SQL Server, is now available in DB2. It allows for a quick deletion of all data in a DB2 table, as opposed to a mass DELETE DML statement.
If used with the IMMEDIATE keyword, then the truncate operation is processed immediately and cannot be undone by a subsequent ROLLBACK statement. If the IMMEDIATE keyword is not included in the TRUNCATE TABLE statement, then a ROLLBACK will undo the truncate operation.
There are some restrictions to the use of the TRUNCATE TABLE statement, foremost of which is that a TRUNCATE TABLE statement cannot be issued against a parent table in a referential foreign key constraint. This is regardless of whether the child table in the referential relationship contains any rows.
If used with the IMMEDIATE keyword, then the truncate operation is processed immediately and cannot be undone by a subsequent ROLLBACK statement. If the IMMEDIATE keyword is not included in the TRUNCATE TABLE statement, then a ROLLBACK will undo the truncate operation.
There are some restrictions to the use of the TRUNCATE TABLE statement, foremost of which is that a TRUNCATE TABLE statement cannot be issued against a parent table in a referential foreign key constraint. This is regardless of whether the child table in the referential relationship contains any rows.
OLAP Functionality
Three OLAP functions are now available in DB2: RANK, DENSE_RANK and ROW_NUMBER.
RANK and DENSE_RANK are very similar in that they will assign a ranking value to a row with respect to how it fares in comparison to other rows in the result set, based on a given criteria. RANK will assign a value that is relative to the top of the list , where DENSE_RANK will eliminate gaps in the sequencing of the assigned ranking.
ROW_NUMBER will simply assign the relative position of the row in the result set, starting at one.
These three functions can best be understood through a simple example that uses all three functions.
Given the following table:
CREATE TABLE SALARY_TABLE
(NAME VARCHAR(20)
,SALARY DECIMAL(8,2)
);
. . . that is populated with 20 rows of data, the following query is executed:
SELECT NAME, SALARY,
RANK () OVER (ORDER BY SALARY DESC) AS RANK,
DENSE_RANK () OVER (ORDER BY SALARY DESC) AS DENSE_RANK,
ROW_NUMBER () OVER (ORDER BY SALARY DESC) AS ROW_NUMBER
FROM SALARY_TABLE
ORDER BY SALARY DESC;
The result of the query is shown below:
NAME SALARY RANK DENSE_RANK ROW_NUMBER
ELLEN 200000.00 1 1 1
PETE 175000.00 2 2 2
FRANK 150000.00 3 3 3
CATHY 125000.00 4 4 4
TOM 120000.00 5 5 5
JOE 100000.00 6 6 6
SUE 100000.00 6 6 7
DEB 90000.00 8 7 8
JOAN 85000.00 9 8 9
DAVE 80000.00 10 9 10
SALLY 80000.00 10 9 11
BILL 75000.00 12 10 12
BOB 70000.00 13 11 13
ANN 60000.00 14 12 14
PAUL 50000.00 15 13 15
KAREN 50000.00 15 13 16
MARY 50000.00 15 13 17
RAY 45000.00 18 14 18
TRACY 40000.00 19 15 19
JIM 25000.00 20 16 20
Ellen has the highest salary, and is therefore assigned a RANK value of 1. She is followed by Pete, Frank, Cathy and Tom who are ranked 2, 3, 4 and 5 respectively. Joe and Sue both have identical salaries and are therefore both assigned a RANK value of 6. They are followed by Deb, who has a RANK value of 8. Deb’s RANK value of 8 has been assigned because there are seven other people who have a salary greater than hers. Other “ties” in the ranking can be seen at numbers 10 and 15.
DENSE_RANK follows a similar pattern, except that in the case of ties in the ranking criteria, the next sequential value is assigned to avoid skips in the DENSE_RANK values assigned. In this example, following a tie between Joe and Sue at number 6, Deb is assigned a DENSE_RANK value of 7 as opposed to the assigned RANK value of 8.
RANK and DENSE_RANK are very similar in that they will assign a ranking value to a row with respect to how it fares in comparison to other rows in the result set, based on a given criteria. RANK will assign a value that is relative to the top of the list , where DENSE_RANK will eliminate gaps in the sequencing of the assigned ranking.
ROW_NUMBER will simply assign the relative position of the row in the result set, starting at one.
These three functions can best be understood through a simple example that uses all three functions.
Given the following table:
CREATE TABLE SALARY_TABLE
(NAME VARCHAR(20)
,SALARY DECIMAL(8,2)
);
. . . that is populated with 20 rows of data, the following query is executed:
SELECT NAME, SALARY,
RANK () OVER (ORDER BY SALARY DESC) AS RANK,
DENSE_RANK () OVER (ORDER BY SALARY DESC) AS DENSE_RANK,
ROW_NUMBER () OVER (ORDER BY SALARY DESC) AS ROW_NUMBER
FROM SALARY_TABLE
ORDER BY SALARY DESC;
The result of the query is shown below:
NAME SALARY RANK DENSE_RANK ROW_NUMBER
ELLEN 200000.00 1 1 1
PETE 175000.00 2 2 2
FRANK 150000.00 3 3 3
CATHY 125000.00 4 4 4
TOM 120000.00 5 5 5
JOE 100000.00 6 6 6
SUE 100000.00 6 6 7
DEB 90000.00 8 7 8
JOAN 85000.00 9 8 9
DAVE 80000.00 10 9 10
SALLY 80000.00 10 9 11
BILL 75000.00 12 10 12
BOB 70000.00 13 11 13
ANN 60000.00 14 12 14
PAUL 50000.00 15 13 15
KAREN 50000.00 15 13 16
MARY 50000.00 15 13 17
RAY 45000.00 18 14 18
TRACY 40000.00 19 15 19
JIM 25000.00 20 16 20
Ellen has the highest salary, and is therefore assigned a RANK value of 1. She is followed by Pete, Frank, Cathy and Tom who are ranked 2, 3, 4 and 5 respectively. Joe and Sue both have identical salaries and are therefore both assigned a RANK value of 6. They are followed by Deb, who has a RANK value of 8. Deb’s RANK value of 8 has been assigned because there are seven other people who have a salary greater than hers. Other “ties” in the ranking can be seen at numbers 10 and 15.
DENSE_RANK follows a similar pattern, except that in the case of ties in the ranking criteria, the next sequential value is assigned to avoid skips in the DENSE_RANK values assigned. In this example, following a tie between Joe and Sue at number 6, Deb is assigned a DENSE_RANK value of 7 as opposed to the assigned RANK value of 8.
Subscribe to:
Posts (Atom)