Technologies

Going Global with Data Mart Consolidation: Part 2

Going global with global variables in DB2 V10.1

Part 1 of this series demonstrates how the security features in IBM® DB2® Version 10.1 database could meet several of the following key business requirements for organizations that are consolidating data from multiple countries into a single set of tables:

  • Allow local users to only have access to data for their country
  • Allow regional users to only have access to data for their region
  • Be “locale sensitive” for regional users and automatically convert different countries’ currency values into a common currency (for example, to add the prices of two items using the currencies of Singapore and Hong Kong)
  • Mask column data based on whether or not a user is a local user or regional user

Here, take a look at how to use global variables and database roles to help reduce code complexity and improve usability, as well as dynamically calculate locale-sensitive date/time periods.

Improving usability when specifying date/time values

Global variables can be used to make SQL simpler to understand. Instead of specifying complex formulas as part of a SQL statement, you can create a global variable for that formula and specify the variable instead. Global variables provide a shared common routine that can be used in any SQL—eliminating the need to write the same code again and again. Furthermore, you can use variables to reduce errors in your SQL by creating just one place where the formula is specified.

Global variables support the use of a descriptive name, which helps the user or reader to determine which calculation is being performed. For example, when specifying the first day of the week, one could include the calculation as part of the query:

SELECT … FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘CONDO’
AND EFFECTIVE_DATE =
CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)

Or we could create a global variable to improve readability. This allows anyone who views the SQL statement to infer which date calculation the query is performing:

CREATE VARIABLE FIRST_DAY_OF_WEEK DATE
DEFAULT
(CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)

The query can now be written as:

SELECT … FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘CONDO’
AND EFFECTIVE DATE = FIRST_DAY_OF_WEEK

Global variables allow for specifying a constant for which the name of the global variable represents the operation the business query is performing. The following are a few common date/time periods:

  • Today
  • Yesterday
  • Tomorrow
  • First day of the month
  • Last day of the month
  • First day of the week
  • First day of the current quarter

Using global variables, we can represent these time periods in a user-friendly, easy-to-read format. Here are the definitions for the time periods mentioned previously.

Today:
CREATE VARIABLE TODAY DATE DEFAULT CURRENT DATE

Yesterday:
CREATE VARIABLE YESTERDAY DATE DEFAULT CURRENT DATE – 1 DAY

Tomorrow:
CREATE VARIABLE TOMORROW DATE DEFAULT CURRENT DATE + 1 DAY

First day of the month:

CREATE VARIABLE FIRST_DAY_OF_CURRENT_MONTH DATE
DEFAULT
(CURRENT_DATE – (DAY (CURRENT_DATE)-1) DAYS)

Last day of the month:

CREATE VARIABLE LAST_DAY_OF_CURRENT_MONTH DATE
DEFAULT
(LAST_DAY (CURRENT DATE))

First day of the week:

CREATE VARIABLE FIRST_DAY_OF_WEEK DATE
DEFAULT
(CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)

First day of the current quarter (notice that global variable FIRST_DAY_OF_THE_CURRENT_MONTH is also used as part of the definition):

CREATE VARIABLE FIRST_DAY_OF_QUARTER DATE
DEFAULT
(ROUND (FIRST_DAY_OF_CURRENT_MONTH,’Q’))

Automatically calculating locale-specific time periods

So far, we have been calculating date/time values that are not tied to a country’s business rules. To illustrate how to “program” global variables to “behave” based on a particular country, let’s look at a business time duration that all countries use: fiscal year.

Calculating the beginning and end of a country’s fiscal year

The beginning and end of a fiscal year is different for each country. Because of this, any global variable that would calculate these values would need to be country-aware to ensure that the values returned are correct. See the figure for the fiscal years for the countries used in Part 1 of this series (in some cases, however, there is more than one fiscal year definition for a country):

Fiscal year definitions for various countries

Country Fiscal Year
Argentina Calendar year
Australia First day of July and ends on June 30 of the following year
Brazil Calendar year
China Calendar year
Egypt First day of July and ends on June 30 of the following year
Hong Kong First day of April and ends on March 31 of the following year
India First day of April and ends on March 31 of the following year
Kenya First day of July and ends on June 30 of the following year
Mexico Calendar year
New Zealand First day of July and ends on June 30 of the following year
Singapore First day of April and ends on March 31 of the following year
South Africa First day of April and ends on March 31 of the following year
United Arab Emirates Calendar year

 
To demonstrate how to calculate a country’s fiscal year, use the same roles defined in Part 1:

ARGENTINA_ROLE
AUSTRALIA_ROLE
BRAZIL_ROLE
CHINA_ROLE
EGYPT_ROLE
HONG_KONG_ROLE
INDIA_ROLE
KENYA_ROLE
MEXICO_ROLE
NEW_ZEALAND_ROLE
SINGAPORE_ROLE
SOUTH_AFRICA_ROLE
UNITED_ARAB_EMIRATES_ROLE

Next, define a few more global variables to represent current year, next year, and last year:

CREATE VARIABLE CURRENT_YEAR CHAR (4)
DEFAULT (YEAR (CURRENT DATE))

CREATE VARIABLE NEXT_YEAR CHAR (4)
DEFAULT (YEAR (CURRENT DATE) +1)

CREATE VARIABLE LAST_YEAR CHAR (4)
DEFAULT (YEAR (CURRENT DATE)-1)

The following code shows the SQL that defines two global variables named START_OF_CURRENT_FISCAL_YEAR and END_OF_CURRENT_FISCAL_YEAR. Notice that the calculation is based on the database role an end user has been granted. For the sake of brevity, only Australia and Hong Kong are displayed.

START_OF_CURRENT_FISCAL_YEAR

CREATE VARIABLE START_OF_CURRENT_FISCAL_YEAR TIMESTAMP DEFAULT ( CASE WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,'AUSTRALIA_ROLE') = 1             THEN CASE                              WHEN MONTH (CURRENT DATE) < 7                                          THEN LAST_YEAR||'-07-01'                         ELSE                              CURRENT_YEAR||'-07-01'                   END ELSE CASE WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,'HONG_KONG_ROLE') = 1             THEN CASE                              WHEN MONTH (CURRENT DATE) < 5                                         THEN LAST_YEAR||'-04-01'                        ELSE                             CURRENT_YEAR||'-04-01'                   END END END ) END_OF_CURRENT_FISCAL_YEAR CREATE VARIABLE END_OF_CURRENT_FISCAL_YEAR TIMESTAMP DEFAULT ( CASE WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,'AUSTRALIA_ROLE') = 1             THEN CASE                              WHEN MONTH (CURRENT DATE) > 6                                           THEN NEXT_YEAR||'-06-30'                         ELSE                               CURRENT_YEAR||'-06-30'                   END ELSE CASE WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,'HONG_KONG_ROLE') = 1              THEN CASE                               WHEN MONTH (CURRENT DATE) > 4                                          THEN NEXT_YEAR||'-03-31'                        ELSE                              CURRENT_YEAR||'-03-31'                    END END END )

If an end user was granted the role AUSTRALIA_ROLE, then the query:

SELECT …. FROM REAL_ESTATE_SALES
WHERE
EFFECTIVE_DATE BETWEEN
START_OF_CURRENT_FISCAL_YEAR AND END_OF_CURRENT_FISCAL_YEAR

Resolves to:

SELECT …. FROM REAL_ESTATE_SALES
WHERE
EFFECTIVE_DATE BETWEEN ‘2011-07-01’ AND ‘2012-06-30’

As you can see, the values for the current fiscal year reflect the dates for Australia based on the user’s role.

Calculating close of business day

Another example of an industry or country-specific date/time period is close of business day. The definition of this term differs by country, industry, and/or legal requirements. A common example of how close of business day plays a role in our daily lives is one’s bank balance: Often close of business day is used as part of a formula to calculate available balance (funds immediately available) and current balance (all transactions that the bank considers either not yet settled, completed, or in progress).

How can the close of business day for Argentina, Brazil, and Mexico be calculated? Assuming that close of business day is the time that the stock market closes in each of those countries, the following example shows how to calculate the close of business day for these countries:

CREATE VARIABLE CLOSE_OF_BUSINESS_DAY TIMESTAMP DEFAULT ( CASE WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,'ARGENTINA_ROLE') = 1 THEN                   CHAR (CURRENT DATE, ISO) ||'-17.00.00' WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,’BRAZIL_ROLE') = 1 THEN                         CHAR (CURRENT DATE, ISO) ||'-17.00.00' WHEN VERIFY_ROLE_FOR_USER (SESSION_USER,’MEXICO_ROLE') = 1 THEN                         CHAR (CURRENT DATE, ISO) ||'-15.00.00' END )

By combining global variables with the row column access features of DB2 10.1, you can develop a secure global data warehouse while also addressing locale-specific requirements. Global variables also help to improve usability and can decrease errors by enabling DBAs to define a formula once rather than requiring users to code the correct calculation in their SQL statements in multiple places.

Please share any thoughts or questions in the comments.

Previous post

Overcoming the Test Data Conundrum

Next post

Going Global with Data Mart Consolidation: Part 1

Dan Gibson

From his position as the DB2 Lab Advocate at the Atlanta, Nagano, and Sydney Olympic Games to his Master of Science Degree in Computer Science, Dan knows technology—specifically DB2—inside and out. Since joining IBM over a decade ago, Dan has dedicated his career to diving in with DB2.