Synergy is important to a DBA. Throughout our day, we work toward outcomes that are greater than the sum of the individual tasks. When DB2 10 for LUW was released recently, I decided that synergy was important when it came to training as well. Fortunately, the timing of the release of DB2 10.1 for LUW closely coincided with the International DB2 User Group (www.idug.org) conference, held this year in Denver, Colorado. I knew from past experience that attending the conference would provide solid information about the latest features of DB2, so I registered and decided to test my synergistic plan to maximize learning, jump start my path to achieving DB2 10 certification and ensure that my skillset was strong enough to support my client’s future needs.
During the conference, I like to spend my time listening to the presenters and don’t like to be distracted by taking notes. Unfortunately, there are so many sessions that it becomes a challenge to retain all that information. So, this year, I decided to maximize my time by jotting down short tips and keywords during each session that would help me remember specific areas that I wanted to review after the conference. Now that I’ve had some time to consolidate those quick notes, I’ve come up with ten tips for learning DB2 10.
The first five tips discuss learning preparation steps while tips six through ten examine five features in DB2 10 that should be at the top of the learning list.
Tip #1: Attend IDUG Conferences and Collect Email Addresses
IDUG DB2 10 presentations, which were given by users, DB2 10 beta testers and IBM experts, proved to be a perfect resource for my objectives. The sessions provided quick information that facilitated invaluable learning short cuts and allowed me to gain a foundation of knowledge with minimal personal effort. Attending these sessions made it seem that I was studying with the smartest kid in the school Additionally, I have the email address for that smart kid now in case I have questions after the conference.
Tip #2: Install the Product
Of course, knowledge needs to be translated into action or it can be quickly forgotten. Fortunately, IBM has solved that problem for those of us who have limited training budgets. There is now a new (and still free) version of DB2 10 Express C, which can be found here: http://www.ibm.com/developerworks/downloads/im/udbexp/index.html. If you prefer a virtual appliance as I do, you can download that here: http://www.ibm.com/developerworks/wikis/display/im/IBM+Virtual+Appliances#IBMVirtualAppliances-db2
Tip #3: Download the Product Documentation
IBM has made it easy to get the product documentation. You can download specific pdfs or a zip file of the entire set at http://www-01.ibm.com/support/docview.wss?uid=swg27024478.
Tip #4: Read ‘What’s New for DB2 Version 10.1’
Once you’ve downloaded the documentation, read the ‘What’s New’ pdf. With each release, I read these from cover-to-cover to find out what I would gain and what I would risk by moving from the current release to the latest product level.
If you are installing DB2 10 in a pristine environment, this specific document is not as critical for you. However, if you are thinking of upgrading to DB2 10 from an earlier DB2 release, it is important that you pay particular attention to the chapters that discuss changes as well as reviewing deprecated and discontinued functionality. In the current document, this information is covered in detail in Part 2.
Tip #5: Make Friends with the Infocenter
When it comes to DB2, the learning resource I use most frequently is the DB2 Information Center (Infocenter). For DB2 10, the online version of the Information Center is http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp. A great feature of the Infocenter provides the ability to set up “scopes” to filter the information returned. For example if I am working on setting up the Workload Manager, I will create a scope option to select only those topics that relate to my current effort.
Tip #6: Business Time Travel
By far, one of the most interesting new features with DB2 10.1 is the possibility of time travel! Unfortunately, IBM hasn’t yet solved the time travel dilemma for individuals, but DB2 10 for LUW introduces native support for temporal data. One particular challenge I often face requires that I design solutions around “effective time”. Business time, also called application time or valid time, coupled with some new SQL keywords provides an easy DB2 10.1 option to solve that challenge.
Business time tables require three additional columns and an addition to the primary key. As an example, if my business sold songs and I designed my pricing structure based on calendar dates with the 30 days after a release commanding a higher price and subsequent periods offering a successively lower price, I might create a business time table like this:
CREATE TABLE "LCKSMITH"."BUSINESS_PERIOD" ( "UNIQUE_ID" BIGINT NOT NULL , "SONG_ID" BIGINT NOT NULL , "PRICE" DECIMAL(8,2) NOT NULL , "START_DATE" DATE NOT NULL , "END_DATE" DATE NOT NULL , PERIOD BUSINESS_TIME ("START_DATE","END_DATE") ) IN "USERSPACE1" ; ALTER TABLE "LCKSMITH"."BUSINESS_PERIOD" ADD PRIMARY KEY ("UNIQUE_ID", BUSINESS_TIME WITHOUT OVERLAPS);
Using the BUSINESS_TIME WITHOUT OVERLAPS syntax disallows overlapping periods and ensures that values for the rest of the keys are unique with respect to any period of BUSINESS_TIME. This allows me to automatically manage ‘effective’ date versions of the data. With this table, I can return the price of a song for a specific effective date or a range of dates.
To return records from this table, I can use a new SQL clause FOR BUSINESS_TIME with options:
As of <value> From <value1> to <value 2> Between <value1> and <value2>
The DB2 SQL Reference Volumes 1 and 2, which are included in the product manual download (see link in tip #3) have more information about BUSINESS_TIME tables, including all the options that can help tailor a solution to your particular requirement.
Note that DB2 10 uses an inclusive/exclusive approach for time periods, which means that the specified end point is the first point in time when the given information is no longer valid.
Tip #7: System Time Travel
IBM gave us not just one time travel option with DB2 10.1, but two. System time tables are optimal for maintaining historical versions of data and can be used to answer the age old question “What changed and when?”
Using a System Time table requires both a base table and a history table. The base table will hold the most current information and the history table will hold the ‘old’ rows. DB2 will transparently use the history table when needed to satisfy queries.
Both the base and historical tables must have a beginning and ending timestamp and a period designated by the keyword SYSTEM_TIME. In addition there is a hidden column that is generated by DB2 as a timestamp. Using my music store as my example, I might want a table that would capture historical changes to the royalty price so that I could have an audit trail. My base table might look like this:
CREATE TABLE "LCKSMITH"."MUSIC" ( "ID" INTEGER NOT NULL , "SONG_NAME" VARCHAR(32) , "ROYALTY_PRICE" DECIMAL(10,2) , "SYS_START" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN , "SYS_END" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END , "TRANS_ID" TIMESTAMP(12) IMPLICITLY HIDDEN GENERATED ALWAYS AS TRANSACTION START ID , PERIOD SYSTEM_TIME ("SYS_START","SYS_END") ) IN "IBMDB2SAMPLEREL" ; ALTER TABLE "LCKSMITH"."MUSIC" ADD PRIMARY KEY ("ID");
To maintain the historical information I will create a history table, which I will name MUSIC_HISTORY. On the MUSIC_HISTORY table, however, I will not create the primary key since there can be multiple versions of the historical rows stored there. I will also set the history table to APPEND ON for better performance and set RESTRICT ON DROP to avoid losing access to my historical information if the base table is dropped.
$> db2 "create table music_history like music with restrict on drop" $> db2 "alter table music_history append on"
I will also alter the base table as follows:
$> db2 "alter table music add versioning use history table music_history"
Be sure to check out the SQL Reference volumes mentioned above for information on the new SQL constructs to support this functionality. If you are wondering if you can combine both business and system time period information into a single table, the answer is “yes”. Look for the term bitemporal in the manuals.
Tip #8: Row and Column Access Control (RCAC)
I struggled to wait this long to discuss security, but it was worth the wait. RCAC is new in DB2 10.1 and provides significant strength to your data protection arsenal. RCAC allows the SECADM the ability to subset the data to enforce access by only those who need to know.
RCAC is used to create controlling rule permissions for rows and provides masking options for columns without the necessity of changing applications to accommodate the enhanced security. Unlike Label Based Access Controls, which are designed to enable protections based on data hierarchies, RCAC provides a general purpose solution and is much easier to set up.
To create row permissions, the SECADM first designs and then runs a CREATE PERMISSION on a table.
Note that the default for the ENABLE/DISABLE option is DISABLE. The outcome of that particular clause is dependent on the current state of activation, so it is important to read and understand the documentation before implementing. After the row permission work has been completed, the SECADM will then ALTER the table to activate the row access control.
For column protection, DB2 10.1 gives us COLUMN MASK. This is perfect for columns that store highly sensitive information, such as SSN or account balances. Using a CASE statement, the SECADM can provide options to mask the data and display it only to appropriate users.
Taking a financial example, the SECADM might want to provide column protection so that only those who have a business need to see the SSN, such as loan managers, will be able to access that data. Others who attempt to see the SSNs will be shown a null value. An example might be:
CREATE MASK SSN_MASK ON ACCOUNT_HOLDER FOR COLUMN SSN RETURN CASE WHEN VERIFY_ROLE_FOR_USER(SESSION_USER, 'MANAGER') = 1 THEN SSN ELSE NULL END ENABLE;
After the mask is created, the table has to be altered to make the column mask active.
ALTER TABLE ACCOUNT_HOLDER ACTIVATE COLUMN ACCESS CONTROL;
One great security concept here is that even users who hold DATAACCESS authority can be prevented from accessing sensitive data thanks to RCAC. Based on the mask that was created for the ACCOUNT_HOLDER table, here’s an example of a before and after test.
But the data is still there.
Note that I hold DATAACCESS authority for the database and that after the mask is activated, I am still prevented from seeing the SSN values because I am not a member of the MANAGER role.
This is just a small sample of the power of RCAC. There is a significant amount of additional information regarding RCAC in the product manuals and information center to help you get started.
Tip #9: HADR Enhancements
HADR users will be happy to know that now, in addition to the principal HADR standby, you can also have two auxiliary standbys. DB2 10 also introduced a new option of delayed replay for designated standby servers. I can see this functionality being very useful in situations where something unexpected has occurred that caused data corruption on the primary server. For example, if important data was overwritten on the primary, with the log replay delayed, the ‘good’ data would still exist on the lagging standby allowing an easy data recovery option. That functionality alone would make me consider an auxiliary standby. If that wasn’t enough, HADR log spooling is another new DB2 10 feature that can help avoid performance issues on the primary due to spikes in logging activity on the standby.
There are some considerations regarding auxiliary standbys. For example, there is no automated takeover support for the auxiliary standbys as there is for the principal standby. However, both Role Switch (graceful) and Failover (when the primary has failed) manual takeover are supported and any of the standby databases can take over as primary.
As you can imagine, there are some additional setup steps and configuration changes that are necessary when you add auxiliary HADR standby servers. Based on what I learned at IDUG, while the configuration steps seem simple enough, it is important to make sure they are correctly completed to avoid “orphans”.
If you want to learn more about maximizing your use of standby databases, this DB2 9.7 article may offer some tips: https://www.ibm.com/developerworks/data/library/techarticle/dm-1205hadrstandby/index.html
Tip #10: Multi-Temperature Data Storage
All data warehouse data is not equal. Various categories of data will have different SLAs and therefore, different storage requirements. While the term multi-temperature storage has been in use for a while, prior to DB2 10, effectively managing storage for multi-temperature warehouse environments required substantial planning and coordination.
A significant design and storage challenge is that data warehouse data changes ‘temperature’ as it ages. Recently loaded data might be heavily accessed (hot) for reporting, but as it ages, its historical significance, while still valuable from the business perspective, may not be as useful for reporting and it cools to warm or cold. Add this dynamic to the fact that the fastest storage is the most expensive and it is easy to see that as data ages, it needs to be moved to less expensive devices.
To address this issue, DB2 10 provides new DDL options that are used for creating, altering and dropping storage groups. These storage groups can be defined to allow storage of data based on its desired data access. Faster media can be defined to hold ‘hot’ or frequently accessed data, while slower media can be assigned to storage groups that hold less frequently accessed data.
Table spaces that use automatic storage will be associated to a storage group when created and/or altered and will automatically inherit the media attributes of the storage group. Table spaces can then be moved online from one storage group to another as the data temperature changes.
If you want to learn specifics about Multi-Temperature storage, there is an excellent white paper on the topic. You can download it from http://public.dhe.ibm.com/software/dw/data/dm-1205multitemp/DB2V10_Multi-Temperature_0412.pdf
A Bonus Tip:
There is much more to the DB2 LUW 10.1 release than is covered here and, obviously, my learning priorities may or may not be similar to yours. I encourage you to implement your own synergistic approach to learning about all the great features of DB2 10.1 and the value they can offer your enterprise.