Databases

Going Global with Data Mart Consolidation: Part 2

Going global with global variables in DB2 V10.1

Part 1 of this article demonstrated how the security features in DB2 V10.1 could meet several key business requirements for organizations that are consolidating data from multiple countries into a single set of tables:

  1. Allow local users to only have access to data for their country
  2. Allow regional users to only have access to data for their region
  3. 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)
  4. Mask column data based on whether or not a user is a local user or regional user

This article builds on Part 1 by demonstrating how global variables and database roles can be used to 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 us to specify a “constant” where the name of the global variable represents the operation the business query is performing. These are a few common date/time periods:

  1. Today
  2. Yesterday
  3. Tomorrow
  4. First day of the month
  5. Last day of the month
  6. First day of the week
  7. 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 above.

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. Table 1 lists the fiscal years for the countries used in Part 1 of this article (in some cases, however, there is more than one fiscal year definition for a country):

 

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

Calendar year

Table 1. Fiscal year definitions for various countries

To demonstrate how to calculate a country’s fiscal year, we will 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, we need to 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 code below 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 a 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 a 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

Would resolve 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 would you calculate the close of business day for Argentina, Brazil, and Mexico? If we assume that close of business day is the time that the stock market closes in each of those countries, this 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

)

 

Conclusion

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.

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.