From the December 2018 Segus Db2 newsletter:
Db2 Checklist: SQLCODEs as never seen before
Thursday, December 20, 2018
Friday, June 1, 2018
Db2 Timestamps, Java, and Daylight Savings Time
The June 2018 Segus Db2 newsletter discusses an issue with Db2 timestamps, Java drivers, and daylight savings time:
DST Db2 timestamp problems: I really hate Daylight Saving Time
DST Db2 timestamp problems: I really hate Daylight Saving Time
Tuesday, June 14, 2016
Migrating a DB2 LUW Database From Big Endian to Little Endian
This article from Roger Sanders describes the differences between "big endian" and "little endian", and how to migrate a DB2 LUW database to an environment with a different endianness.
Migrating a DB2 database from a Big Endian environment to a Little Endian environment
Migrating a DB2 database from a Big Endian environment to a Little Endian environment
DB2 z/OS Buffer Pool Thresholds Overview
An overview of some buffer pool tuning threshold parameters that can be tuned for DB2 z/OS, from Craig Mullins:
Four Important Buffer Pool Tuning Knobs in DB2 for z/OS
Four Important Buffer Pool Tuning Knobs in DB2 for z/OS
Wednesday, June 1, 2016
Effectively Using the "LIKE" Predicate in SQL
Here's a good article by Craig Mullins on effectively using the "LIKE" predicate in your SQL statements.
Carefully Code Your DB2 LIKE Predicates
Carefully Code Your DB2 LIKE Predicates
Monday, April 11, 2016
The Most Misunderstood Features of DB2
From Craig Mullins: The Most Misunderstood Features of DB2:
Part 1: Locking
Part 2: Optimize vs. Limited Fetch
Part 3: Nulls
Part 4: Base Table Views
Part 5: Choosing the Clustering Key
Part 6: Not Indexing
Part 7: It Depends!
Part 8: Do I Have to Pick Just One?
Part 1: Locking
Part 2: Optimize vs. Limited Fetch
Part 3: Nulls
Part 4: Base Table Views
Part 5: Choosing the Clustering Key
Part 6: Not Indexing
Part 7: It Depends!
Part 8: Do I Have to Pick Just One?
Tuesday, February 16, 2016
Understanding the Complexity of Time Travel
Another article about temporal tables and DB2, from Dan Luksetich.
Understanding the Complexity of Time Travel
Understanding the Complexity of Time Travel
Tuesday, February 9, 2016
System-Time Temporal versus Archive Tables
Two related blog articles from Robert Catterall on DB2 System-Time Temporal and Archive tables on DB2 z/OS:
Part 1: DB2 for z/OS-Managed Archiving, or System-Time Temporal?
Part 2: Thoughts on History and Archive Tables
Part 1: DB2 for z/OS-Managed Archiving, or System-Time Temporal?
Part 2: Thoughts on History and Archive Tables
Thursday, January 21, 2016
Tuesday, August 18, 2015
Static versus Dynamic SQL in Stored Procedures
There are two different types of SQL that can be coded in a native SQL stored procedure in DB2: static SQL and dynamic SQL. Dynamic SQL can also be written in two different ways, thus leading to three different ways to code SQL in a stored procedure. This blog post will describe the differences between these three different approaches, highlight when one approach should be used over the other two, and give some performance metrics based on each approach.
Static SQL is almost always preferable over dynamic SQL because optimization of the SQL statements will occur only one time, at the time the stored procedure is created. An optimized access path to execute each static SQL statement is stored in the DB2 package that is associated with the stored procedure, and no additional overhead is incurred at run time.
There are two different "flavors" of dynamic SQL, and those would be (1) dynamic SQL that uses parameter markers as "place holders" for search criteria in the WHERE clauses of the SQL statements, and (2) dynamic SQL that uses hard coded literals for search criteria in the WHERE clauses of the SQL statements. When using parameter markers, the "variable" is represented by a question mark ("?") in the constructed SQL statement that is passed to the PREPARE statement, and the actual values to be used during execution are supplied via a USING clause in either the EXECUTE statement or the OPEN statement (if using a cursor).
At a high level, when doing a PREPARE of a dynamic SQL statement, the following activities take place:
1) The dynamic SQL cache is searched to determine if the SQL statement being prepared has previously been prepared and is resident in cache. DB2 will look for an EXACT MATCH on the SQL statement in the cache to determine residency. If a match is found, then DB2 will reuse the previously created optimized execution plan for that statement for execution. The dynamic cache look-up is a relatively inexpensive component of the overall execution of a dynamic SQL statement.
2) If no match is found in the dynamic cache for the SQL statement being prepared, then DB2 will have to parse the statement for syntactical accuracy, perform authorization checking, and then optimize the statement to generate an optimized execution plan for that statement. The statement and the corresponding execution plan is then stored in the dynamic cache. If the cache is full, then DB2 uses a "least recently used" algorithm to choose a victim to discard from the cache to make room for the new statement. The generation of the optimized execution plan is a relatively expensive component of the overall execution of a dynamic SQL statement.
A PREPARE that only has to go through step 1 as described above is called a short prepare. A PREPARE that has go to through both steps 1 and 2 is called a full prepare.
The performance benefits of dynamic SQL that uses parameter markers versus dynamic SQL that uses hard coded literals is achieved via the PREPARE statement. If a particular statement in a stored procedure is coded to use parameter markers, every PREPARE of that statement will be using an identical string. The first prepare of that statement will need to do a full prepare, but every subsequent prepare will only need to do a short prepare because the previously prepared statement will most likely be resident in the cache and can be reused. However, when using dynamic SQL statements that use literals, then every statement will be seen as unique, and virtually every PREPARE for that statement will result in the more expensive full prepare.
So when would somebody choose to use dynamic SQL over static SQL when coding a stored procedure? There are basically three situations in which dynamic may be preferable to static.
1) The business requirements are dictating a very flexible set of search predicates for a particular invocation of a stored procedure. For example, you may want to search a customer table on any combination of attributes, such as first name, last name, customer ID, city, state, region, etc. In such a design, it would be cumbersome to try to code a single SQL statement that can handle multiple search parameters, some of which may be supplied and some of which may not be at execution time. Even coding this as individual and separate SQL statements for each combination of parameters would be cumbersome as the number of combinations can grow exponentially as you add more parameters to the mix. This would be an ideal candidate for dynamic SQL with parameter markers as you are trading off the hit in performance for having to do a full prepare of a "unique" combination of search attributes for a flexible design.
2) Non uniformity of data distribution. The actual values of data stored in a particular table may have a non-uniform distribution of data, such that some values appear very frequently, while others appear infrequently. The DB2 optimizer may chose to perform a table scan when optimizing a query that will return a large number of rows (based on the search criteria), while an index may be chosen by the optimizer for other search values that it determines will return a small number of rows for the same query. When using static SQL, the DB2 optimizer will make an informed decision as to what would be the best access path for most queries, but for some particular values a "one size fits all" access path may actually result in poor performance for those search values that are part of the data skew. In this case, dynamic SQL with literals may be a better choice than static SQL as the DB2 optimizer can make an informed decision at prepare time for the optimal access path based on the actual values being searched on.
3) Range predicates. Very similar to non uniformity of data distribution, an SQL query that searches on a range of data may benefit from being written as dynamic SQL with literals than as a static SQL statement. Range predicates would include greater than, less than, BETWEEN, and LIKE predicates. Based on the actual values you are searching on, you may be asking DB2 to return a small amount of data (in which case an index access would be preferable) or a large amount of data (in which case a table scan may be preferable). Writing these statements as dynamic with literals may provide the most appropriate access path for a given query based on the actual values being searched on.
To gather some actual performance metrics on each of these three approaches to writing SQL in a native SQL stored procedure in DB2, I wrote three nearly identical stored procedures and ran them against a test database in our environment to get some performance benchmarks. That particular database had 2,961 unique values of a particular search column in a "master" table, and my programs accessed all 2,961 of them using an SQL statement from one of our business applications. The only difference in the three SP's were the manner in which the SQL statement in question was written: static, versus dynamic with parameter markers versus dynamic with literals. Here are the results:
Static SQL
Total elapsed time: 3.224109 seconds
User CPU time: 0.109829 seconds
System CPU time: 0.055669 seconds
Dynamic SQL with Parameter Markers
Total elapsed time: 3.554240 seconds
User CPU time: 0.130988 seconds
System CPU time: 0.058271 seconds
Dynamic SQL with Literals
Total elapsed time: 72.297577 seconds
User CPU time: 12.478055 seconds
System CPU time: 1.642622 seconds
As we can see from the above numbers, dynamic SQL with parameter markers can achieve performance that approaches (but will not meet or exceed) static SQL, while dynamic SQL coded with literals will perform significantly worse than either of the other two options.
It is important to note that dynamic with parameter markers still performs slightly worse than static SQL. This is because of the overhead incurred by the necessary PREPARE statements for dynamic SQL. The slight overhead of doing short prepares with dynamic SQL using parameter markers is still an expense that static SQL does not incur, and has a cumulative impact on performance over many executions. Plus, in a "real life" environment in which you have multiple stored procedures each executing multiple SQL statements, if the number of "unique" dynamic statements (with parameter markers) exceeds the capacity of the dynamic SQL cache, statements can and will get discarded from the cache and the reuse of the statements in the cache can degrade over time as the workload grows.
The drastic difference in performance for the dynamic SQL with literals test over the other two tests can be attributed to the cost of doing full prepares for every single statement during the execution of the SP.
Static SQL is almost always preferable over dynamic SQL because optimization of the SQL statements will occur only one time, at the time the stored procedure is created. An optimized access path to execute each static SQL statement is stored in the DB2 package that is associated with the stored procedure, and no additional overhead is incurred at run time.
There are two different "flavors" of dynamic SQL, and those would be (1) dynamic SQL that uses parameter markers as "place holders" for search criteria in the WHERE clauses of the SQL statements, and (2) dynamic SQL that uses hard coded literals for search criteria in the WHERE clauses of the SQL statements. When using parameter markers, the "variable" is represented by a question mark ("?") in the constructed SQL statement that is passed to the PREPARE statement, and the actual values to be used during execution are supplied via a USING clause in either the EXECUTE statement or the OPEN statement (if using a cursor).
At a high level, when doing a PREPARE of a dynamic SQL statement, the following activities take place:
1) The dynamic SQL cache is searched to determine if the SQL statement being prepared has previously been prepared and is resident in cache. DB2 will look for an EXACT MATCH on the SQL statement in the cache to determine residency. If a match is found, then DB2 will reuse the previously created optimized execution plan for that statement for execution. The dynamic cache look-up is a relatively inexpensive component of the overall execution of a dynamic SQL statement.
2) If no match is found in the dynamic cache for the SQL statement being prepared, then DB2 will have to parse the statement for syntactical accuracy, perform authorization checking, and then optimize the statement to generate an optimized execution plan for that statement. The statement and the corresponding execution plan is then stored in the dynamic cache. If the cache is full, then DB2 uses a "least recently used" algorithm to choose a victim to discard from the cache to make room for the new statement. The generation of the optimized execution plan is a relatively expensive component of the overall execution of a dynamic SQL statement.
A PREPARE that only has to go through step 1 as described above is called a short prepare. A PREPARE that has go to through both steps 1 and 2 is called a full prepare.
The performance benefits of dynamic SQL that uses parameter markers versus dynamic SQL that uses hard coded literals is achieved via the PREPARE statement. If a particular statement in a stored procedure is coded to use parameter markers, every PREPARE of that statement will be using an identical string. The first prepare of that statement will need to do a full prepare, but every subsequent prepare will only need to do a short prepare because the previously prepared statement will most likely be resident in the cache and can be reused. However, when using dynamic SQL statements that use literals, then every statement will be seen as unique, and virtually every PREPARE for that statement will result in the more expensive full prepare.
So when would somebody choose to use dynamic SQL over static SQL when coding a stored procedure? There are basically three situations in which dynamic may be preferable to static.
1) The business requirements are dictating a very flexible set of search predicates for a particular invocation of a stored procedure. For example, you may want to search a customer table on any combination of attributes, such as first name, last name, customer ID, city, state, region, etc. In such a design, it would be cumbersome to try to code a single SQL statement that can handle multiple search parameters, some of which may be supplied and some of which may not be at execution time. Even coding this as individual and separate SQL statements for each combination of parameters would be cumbersome as the number of combinations can grow exponentially as you add more parameters to the mix. This would be an ideal candidate for dynamic SQL with parameter markers as you are trading off the hit in performance for having to do a full prepare of a "unique" combination of search attributes for a flexible design.
2) Non uniformity of data distribution. The actual values of data stored in a particular table may have a non-uniform distribution of data, such that some values appear very frequently, while others appear infrequently. The DB2 optimizer may chose to perform a table scan when optimizing a query that will return a large number of rows (based on the search criteria), while an index may be chosen by the optimizer for other search values that it determines will return a small number of rows for the same query. When using static SQL, the DB2 optimizer will make an informed decision as to what would be the best access path for most queries, but for some particular values a "one size fits all" access path may actually result in poor performance for those search values that are part of the data skew. In this case, dynamic SQL with literals may be a better choice than static SQL as the DB2 optimizer can make an informed decision at prepare time for the optimal access path based on the actual values being searched on.
3) Range predicates. Very similar to non uniformity of data distribution, an SQL query that searches on a range of data may benefit from being written as dynamic SQL with literals than as a static SQL statement. Range predicates would include greater than, less than, BETWEEN, and LIKE predicates. Based on the actual values you are searching on, you may be asking DB2 to return a small amount of data (in which case an index access would be preferable) or a large amount of data (in which case a table scan may be preferable). Writing these statements as dynamic with literals may provide the most appropriate access path for a given query based on the actual values being searched on.
To gather some actual performance metrics on each of these three approaches to writing SQL in a native SQL stored procedure in DB2, I wrote three nearly identical stored procedures and ran them against a test database in our environment to get some performance benchmarks. That particular database had 2,961 unique values of a particular search column in a "master" table, and my programs accessed all 2,961 of them using an SQL statement from one of our business applications. The only difference in the three SP's were the manner in which the SQL statement in question was written: static, versus dynamic with parameter markers versus dynamic with literals. Here are the results:
Static SQL
Total elapsed time: 3.224109 seconds
User CPU time: 0.109829 seconds
System CPU time: 0.055669 seconds
Dynamic SQL with Parameter Markers
Total elapsed time: 3.554240 seconds
User CPU time: 0.130988 seconds
System CPU time: 0.058271 seconds
Dynamic SQL with Literals
Total elapsed time: 72.297577 seconds
User CPU time: 12.478055 seconds
System CPU time: 1.642622 seconds
As we can see from the above numbers, dynamic SQL with parameter markers can achieve performance that approaches (but will not meet or exceed) static SQL, while dynamic SQL coded with literals will perform significantly worse than either of the other two options.
It is important to note that dynamic with parameter markers still performs slightly worse than static SQL. This is because of the overhead incurred by the necessary PREPARE statements for dynamic SQL. The slight overhead of doing short prepares with dynamic SQL using parameter markers is still an expense that static SQL does not incur, and has a cumulative impact on performance over many executions. Plus, in a "real life" environment in which you have multiple stored procedures each executing multiple SQL statements, if the number of "unique" dynamic statements (with parameter markers) exceeds the capacity of the dynamic SQL cache, statements can and will get discarded from the cache and the reuse of the statements in the cache can degrade over time as the workload grows.
The drastic difference in performance for the dynamic SQL with literals test over the other two tests can be attributed to the cost of doing full prepares for every single statement during the execution of the SP.
Friday, July 11, 2014
Redefining High Availability for a Data Warehouse
A brief article about high availability in a data warehouse environment.
IDUG : Blogs : Redefining High Availability for a Data Warehouse
IDUG : Blogs : Redefining High Availability for a Data Warehouse
Thursday, July 10, 2014
Wednesday, July 2, 2014
June 2014 Addendum: DB2 10 & DB2 9 Product Publications have been refreshed
Updated DB2 10 for z/OS product manuals:
June 2014 Addendum: DB2 10 & DB2 9 Product Publications have been refreshed
June 2014 Addendum: DB2 10 & DB2 9 Product Publications have been refreshed
Sunday, June 22, 2014
IDUG : Blogs : Help Learning to use IBM Data Studio
Here is a blog article which contains additional links to videos, presentations and IBM Redbooks concerning the use of IBM Data Studio:
IDUG : Blogs : Help Learning to use IBM Data Studio
IDUG : Blogs : Help Learning to use IBM Data Studio
Friday, June 6, 2014
Database Recovery - Application Design
Database recovery is not always entirely within the realm of your favorite Database Administrator. There are a number of steps that can and should be taken by the application developers when constructing their applications and code. Here are a few thoughts:
Database Recovery - Application Design
Database Recovery - Application Design
Thursday, June 5, 2014
IDUG : Blogs : An Introduction to IBM Data Studio
An introduction to IBM Data Studio, which may be replacing DB2 Control Center as the desktop tool of choice for developers when we upgrade to DB2 LUW v10.5.
IDUG : Blogs : An Introduction to IBM Data Studio
IDUG : Blogs : An Introduction to IBM Data Studio
Tuesday, June 11, 2013
What's New in DB2 LUW?
Here is a high-level summary of some of the new features that have been added to DB2 LUW over the past couple of releases (v8.x and v9.x).
If you would like more in-depth information about any of these new features, please contact Phoenix DBA and we can explore a “deeper dive” into the features you are interested in, as well as recommendations as to if and how they can solve current or future business problems that you may be facing.
New in DB2 LUW v8.1:
New in DB2 LUW v8.2:
New in DB2 LUW v9.1:
New in DB2 LUW v9.5:
New in DB2 LUW v9.7:
If you would like more in-depth information about any of these new features, please contact Phoenix DBA and we can explore a “deeper dive” into the features you are interested in, as well as recommendations as to if and how they can solve current or future business problems that you may be facing.
New in DB2 LUW v8.1:
New in DB2 LUW v8.2:
New in DB2 LUW v9.1:
New in DB2 LUW v9.5:
New in DB2 LUW v9.7:
Enhanced OLAP Functionality
In addition to OLAP functions such as RANK, DENSE_RANK and ROW_NUMBER, which were already supported in previous releases of DB2 LUW, v9.5 introduces five additional OLAP functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE and RATIO_TO_REPORT.
The LAG function returns the expression value for the row that is “x” rows before the current row in an OLAP window of data.
The LEAD function returns the expression value for the row that is “x” rows after the current row in an OLAP window of data.
The FIRST_VALUE function returns the expression value for the first row in an OLAP window of data.
The LAST_VALUE function returns the expression value for the last row in an OLAP window of data.
The RATIO_TO_REPORT function returns the ratio of an argument to the sum of the arguments in an OLAP window of data.
These new functions can best be understood through a simple example that uses three of these new 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,
(LEAD (SALARY, 1) OVER (ORDER BY SALARY)) – SALARY AS LEAD,
FIRST_VALUE (SALARY) OVER (ORDER BY SALARY DESC) – SALARY AS FRST_VAL,
DECIMAL(RATIO_TO_REPORT (SALARY) OVER (), 5, 4) AS RATIO_TO_REPORT
FROM SALARY_TABLE
ORDER BY SALARY DESC;
The result of the query is shown below:
NAME SALARY LEAD FRST_VAL RATIO_TO_REPORT
ELLEN 200000.00 - 0.00 0.1130
PETE 175000.00 25000.00 25000.00 0.0989
FRANK 150000.00 25000.00 50000.00 0.0847
CATHY 125000.00 25000.00 75000.00 0.0706
TOM 120000.00 5000.00 80000.00 0.0678
JOE 100000.00 20000.00 100000.00 0.0565
SUE 100000.00 0.00 100000.00 0.0565
DEB 90000.00 10000.00 110000.00 0.0508
JOAN 85000.00 5000.00 115000.00 0.0480
DAVE 80000.00 5000.00 120000.00 0.0452
SALLY 80000.00 0.00 120000.00 0.0452
BILL 75000.00 5000.00 125000.00 0.0424
BOB 70000.00 5000.00 130000.00 0.0395
ANN 60000.00 10000.00 140000.00 0.0339
PAUL 50000.00 10000.00 150000.00 0.0282
KAREN 50000.00 0.00 150000.00 0.0282
MARY 50000.00 0.00 150000.00 0.0282
RAY 45000.00 5000.00 155000.00 0.0254
TRACY 40000.00 5000.00 160000.00 0.0226
JIM 25000.00 15000.00 175000.00 0.0141
First, let’s look at the LEAD function. We want to get the difference between each person’s salary, and the salary of the person directly before them in result set. Since the result set is returned in descending order of SALARY, Ellen is listed first with the highest salary and has a null value for LEAD, since nobody is listed before her. Pete is next, and his salary is 25,000.00 less than Ellen’s. Frank’s salary is 25,000.00 less than Pete’s, and so on. In the event of a two or more people with equal salaries, the first one listed has a delta salary compared to the person before him/her, and the second (and subsequent) “ties” have a LEAD value of 0.00.
The FIRST_VALUE function in this example is showing the difference between the person’s own salary and the salary of the highest paid person in the department (Ellen, with 200,000.00).
The RATIO_TO_REPORT function in this example shows the ratio (or percentage) of each person’s salary compared to the total salary of everybody listed. The total salary of the entire table is 1,770,000.00. With a salary of 200,000.00, Ellen is making 0.1130 of the total salary in the table. Jim, at the bottom with a salary of only 25,000.00, is only making 0.0141 of the total salary in the table.
OLAP functions can also be “partitioned” by another value. If our table contained a column for the DEPARTMENT that each employee worked in, the OLAP functions could be further broken down such that the FIRST_VALUE and RATIO_TO_REPORT functions would be relative to each employee’s standing within their department rather than the entire table as a whole.
The LAG function returns the expression value for the row that is “x” rows before the current row in an OLAP window of data.
The LEAD function returns the expression value for the row that is “x” rows after the current row in an OLAP window of data.
The FIRST_VALUE function returns the expression value for the first row in an OLAP window of data.
The LAST_VALUE function returns the expression value for the last row in an OLAP window of data.
The RATIO_TO_REPORT function returns the ratio of an argument to the sum of the arguments in an OLAP window of data.
These new functions can best be understood through a simple example that uses three of these new 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,
(LEAD (SALARY, 1) OVER (ORDER BY SALARY)) – SALARY AS LEAD,
FIRST_VALUE (SALARY) OVER (ORDER BY SALARY DESC) – SALARY AS FRST_VAL,
DECIMAL(RATIO_TO_REPORT (SALARY) OVER (), 5, 4) AS RATIO_TO_REPORT
FROM SALARY_TABLE
ORDER BY SALARY DESC;
The result of the query is shown below:
NAME SALARY LEAD FRST_VAL RATIO_TO_REPORT
ELLEN 200000.00 - 0.00 0.1130
PETE 175000.00 25000.00 25000.00 0.0989
FRANK 150000.00 25000.00 50000.00 0.0847
CATHY 125000.00 25000.00 75000.00 0.0706
TOM 120000.00 5000.00 80000.00 0.0678
JOE 100000.00 20000.00 100000.00 0.0565
SUE 100000.00 0.00 100000.00 0.0565
DEB 90000.00 10000.00 110000.00 0.0508
JOAN 85000.00 5000.00 115000.00 0.0480
DAVE 80000.00 5000.00 120000.00 0.0452
SALLY 80000.00 0.00 120000.00 0.0452
BILL 75000.00 5000.00 125000.00 0.0424
BOB 70000.00 5000.00 130000.00 0.0395
ANN 60000.00 10000.00 140000.00 0.0339
PAUL 50000.00 10000.00 150000.00 0.0282
KAREN 50000.00 0.00 150000.00 0.0282
MARY 50000.00 0.00 150000.00 0.0282
RAY 45000.00 5000.00 155000.00 0.0254
TRACY 40000.00 5000.00 160000.00 0.0226
JIM 25000.00 15000.00 175000.00 0.0141
First, let’s look at the LEAD function. We want to get the difference between each person’s salary, and the salary of the person directly before them in result set. Since the result set is returned in descending order of SALARY, Ellen is listed first with the highest salary and has a null value for LEAD, since nobody is listed before her. Pete is next, and his salary is 25,000.00 less than Ellen’s. Frank’s salary is 25,000.00 less than Pete’s, and so on. In the event of a two or more people with equal salaries, the first one listed has a delta salary compared to the person before him/her, and the second (and subsequent) “ties” have a LEAD value of 0.00.
The FIRST_VALUE function in this example is showing the difference between the person’s own salary and the salary of the highest paid person in the department (Ellen, with 200,000.00).
The RATIO_TO_REPORT function in this example shows the ratio (or percentage) of each person’s salary compared to the total salary of everybody listed. The total salary of the entire table is 1,770,000.00. With a salary of 200,000.00, Ellen is making 0.1130 of the total salary in the table. Jim, at the bottom with a salary of only 25,000.00, is only making 0.0141 of the total salary in the table.
OLAP functions can also be “partitioned” by another value. If our table contained a column for the DEPARTMENT that each employee worked in, the OLAP functions could be further broken down such that the FIRST_VALUE and RATIO_TO_REPORT functions would be relative to each employee’s standing within their department rather than the entire table as a whole.
Table Range Partitioning
Table range partitioning has been available in DB2 z/OS since the early days of that product, and received a significant refresh in functionality in DB2 z/OS v8, but did not become available in DB2 LUW until DB2 LUW v9.1.
Table range partitioning is a data organization scheme in which table data is stored across multiple physical partitions (or ranges) based on the value in one of more table columns. Each data partition can be stored in separate tablespaces, or in the same tablespace, as desired. Table partitioning can improve performance of queries by eliminating large amounts of I/O for range based queries as only the partitions containing data in scope of the queries will need to be accessed.
One significant difference between the implementation of table range partitioning in DB2 LUW as opposed to DB2 z/OS is that in DB2 LUW, partitions can be named, and they can be attached or detached from tables as needed. This allows for easy roll-in or roll-out of data from a partitioned table via ALTER TABLE statements with either the ATTACH PARITION or DETACH PARTITION options.
The DETATCH PARITION option of an ALTER TABLE statement allows for a named partition of a table to be detached into a separate, standalone table. The separate table can then be dropped if the data is to be deleted or destroyed; it can be attached to another partitioned table that is acting as a history or archive table; or the data can be refreshed and then reattached back to the original table as a new partition containing new data.
Example of detaching and re-attaching a table partition:
Let’s say we have a table named SALES that is partitioned by year and month. We want to remove all the data for June 2012 (which resides in named partition JUN12) and replace it with data for June 2013 in a new partition. To accomplish this, we would perform the following steps:
1) Remove the June 2012 data by detaching it to a separate table named TEMPSALES with the following statement:
ALTER TABLE SALES
DETACH PARTITION JUN12 INTO TEMPSALES;
2) Replace the data in the TEMPSALES table with the new data for June 2013 with the following command:
LOAD FROM jun2013.txt OF DEL REPLACE INTO TEMPSALES;
3) Attach the TEMPSALES table, which contains the new data, to the SALES table as a new partition by executing the following statement:
ALTER TABLE SALES
ATTACH PARTITION JUN13
STARTING ‘2013-06-01’ ENDING ‘2013-06-30’
FROM TEMPSALES;
4) At this point, the new data is not yet accessible until a SET INTEGRITY statement is executed to update the indexes for the SALES table to include the new partition. This can be done with the following statement:
SET INTEGRITY FOR SALES
ALLOW WRITE ACCESS
IMMEDIATE CHECKED;
Table range partitioning is a data organization scheme in which table data is stored across multiple physical partitions (or ranges) based on the value in one of more table columns. Each data partition can be stored in separate tablespaces, or in the same tablespace, as desired. Table partitioning can improve performance of queries by eliminating large amounts of I/O for range based queries as only the partitions containing data in scope of the queries will need to be accessed.
One significant difference between the implementation of table range partitioning in DB2 LUW as opposed to DB2 z/OS is that in DB2 LUW, partitions can be named, and they can be attached or detached from tables as needed. This allows for easy roll-in or roll-out of data from a partitioned table via ALTER TABLE statements with either the ATTACH PARITION or DETACH PARTITION options.
The DETATCH PARITION option of an ALTER TABLE statement allows for a named partition of a table to be detached into a separate, standalone table. The separate table can then be dropped if the data is to be deleted or destroyed; it can be attached to another partitioned table that is acting as a history or archive table; or the data can be refreshed and then reattached back to the original table as a new partition containing new data.
Example of detaching and re-attaching a table partition:
Let’s say we have a table named SALES that is partitioned by year and month. We want to remove all the data for June 2012 (which resides in named partition JUN12) and replace it with data for June 2013 in a new partition. To accomplish this, we would perform the following steps:
1) Remove the June 2012 data by detaching it to a separate table named TEMPSALES with the following statement:
ALTER TABLE SALES
DETACH PARTITION JUN12 INTO TEMPSALES;
2) Replace the data in the TEMPSALES table with the new data for June 2013 with the following command:
LOAD FROM jun2013.txt OF DEL REPLACE INTO TEMPSALES;
3) Attach the TEMPSALES table, which contains the new data, to the SALES table as a new partition by executing the following statement:
ALTER TABLE SALES
ATTACH PARTITION JUN13
STARTING ‘2013-06-01’ ENDING ‘2013-06-30’
FROM TEMPSALES;
4) At this point, the new data is not yet accessible until a SET INTEGRITY statement is executed to update the indexes for the SALES table to include the new partition. This can be done with the following statement:
SET INTEGRITY FOR SALES
ALLOW WRITE ACCESS
IMMEDIATE CHECKED;
HADR (High Availability Disaster Recovery)
The HADR (High Availability Disaster Recovery) feature is a database replication feature that provides a high availability solution to protect against a failure of a database server. Database structure changes and data changes are automatically replicated from the active or primary database to a standby database. HADR is essentially an active/passive HA solution, though read-only access on the standby database can optionally be enabled to make it a fully-active/limited-active HA solution as of DB2 LUW v9.7.
Data replication from the primary database to the standby database can be in one of three modes: synchronous, near-synchronous and asynchronous.
An additional feature that works in conjunction with HADR is ACR (automatic client reroute), in which client applications have knowledge of both the primary and the standby servers in an HADR pairing. In the event that the primary database is no longer responding, such as when the client receives a communication error, the client will automatically attempt to connect to the secondary server (i.e. the new primary server, after a TAKEOVER HADR command has been issued).
It should be noted that in its current implementation, HADR can only be practically used either as a high-availability solution or as a disaster recovery solution. By their natures, HA and DR standby databases have one significant mutually exclusive trait, and that is physical proximity to the primary database. Ideally, an HA standby database will be physically co-located in the same data center (and on the same network segment) as the primary database, so that replication between primary and standby will not be impacted by network latency, and also so that there will be little degradation in network communication times between the application servers and the standby server in the event of an HADR takeover. This is in contrast to a DR standby database, which will be physically located at a different site than the primary database, to protect itself from a site disaster that occurs at the primary data center. This limitation of “HA or DR” will be lifted in a future release of DB2 LUW, as multiple standby databases will be allowed. This will enable a true HA-DR solution as one standby database can be located locally as an HA standby, and a second standby database can be located remotely as a DR standby.
Data replication from the primary database to the standby database can be in one of three modes: synchronous, near-synchronous and asynchronous.
- Synchronous replication allows for the greatest protection against transaction loss but at the cost of longer transaction response time, as update transactions are considered successful only after they have been applied to the primary database, and after confirmation that they have been received by and applied to the standby database.
- Near-synchronous replication provides slightly less protection against transaction loss while providing better performance of transaction response time. In near-synchronous HADR mode, update transactions are considered successful after they have been applied to the primary database and after confirmation that the standby database has received the updates (though has not yet applied them).
- Asynchronous replication allows for the highest risk of transaction loss in the event of failure, but also provides for the shortest transaction response time. In asynchronous mode, update transactions are considered successful after they have been applied to the primary database and after the updates have been sent to the standby database. There is no wait for confirmation that the standby database has actually received or applied the updates.
In the event of a failure of the primary database server, failover to the standby server can be accomplished by issuing a “TAKEOVER HADR” command from the standby database server for the database in question. When the takeover command has been issued, the standby database now becomes the primary database and can resume full and normal processing. Caution must be taken when issuing a takeover command that the former primary database is no longer accessible. If it is, then this can result in a situation in which both nodes in an HADR pairing are functioning as primary databases, and are both accepting and processing update transactions. This is called a “split-brain” scenario, and makes the resynchronization of data extremely difficult as both databases have different instances of updated data.
Subscribe to:
Posts (Atom)