通过数据集市整合实现全球化，第 2 利用

通过数据集市整合实现全球化，第
2 部分
英文原文：
http://ibmdatamag.com/2012/04/going-global-with-data-mart-consolidation-part-two/
利用 DB2 V10.1 中的全局变量实现全球化
作者：Dan Gibson |
发步日期：2012 年 4 月 30 日
本文的第 1 部分 展示了 DB2 V10.1 的安全性特性如何满足将来自多个国家（行政区）的
数据整合到单独一组表中的组织的关键业务需求：
1. 使本地用户仅能访问其所在国家（行政区）的数据
2. 使地区用户仅能访问其所在地区的数据
3. 针对地区用户，实现“地方敏感型”，自动将不同国家（行政区）的币种值转为通用币
种（例如，为了汇总分别使用新加坡货币和中国香港货币的两种商品的价格，需要进行
币种转换）
4. 根据用户是本地用户还是地区用户来屏蔽列数据
本文以第 1 部分作为基础，展示了如何利用全局变量和数据库角色来降低代码复杂性、提
高有用性，同时动态计算对地方敏感的日期/时间段。
提高指定日期/时间值时的有用性
全局变量可用于使 SQL 更易理解。您不必再在 SQL 语句中指定复杂的公式，而是可以为
该公式创建一个全局变量，直接指定该变量。全局变量提供了共享的通用例程，可在任何
SQL 中使用它们，这些例程消除了重复编写相同代码的需要。除此之外，利用全局变量时，
只需在指定公式的位置创建变量一次，因此可减少 SQL 中的错误。
全局变量支持使用描述性名称，这有助于用户或读者确定应执行哪些计算。例如，指定一周
的第一天时，可以在查询中包含这样的计算：
SELECT … FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘CONDO’
AND EFFECTIVE_DATE =
CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)
我们也可以创建一个全局变量来改善可读性。这允许任何查看 SQL 语句的用户确定查询执
行的日期计算：
CREATE VARIABLE FIRST_DAY_OF_WEEK DATE
DEFAULT
(CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)
现在，可将查询写为：
SELECT … FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘CONDO’
AND EFFECTIVE DATE = FIRST_DAY_OF_WEEK
全局变量允许我们指定一个“常量”，全局变量的名称表示业务查询执行的操作。下面给出
了几个常用的日期/时间段：
1. 今天
2. 昨天
3. 明天
4. 一个月的第一天
5. 一个月的最后一天
6. 一个星期的第一天
7. 当前季度的第一天
利用全局变量，可以通过用户友好、易于阅读的格式表示这些时间段。下面给出了上述时间
段的定义。
今天：
CREATE VARIABLE TODAY DATE DEFAULT CURRENT DATE
昨天：
CREATE VARIABLE YESTERDAY DATE DEFAULT CURRENT DATE – 1 DAY
明天：
CREATE VARIABLE TOMORROW DATE DEFAULT CURRENT DATE + 1 DAY
一个月的第一天：
CREATE VARIABLE FIRST_DAY_OF_CURRENT_MONTH DATE
DEFAULT
(CURRENT_DATE – (DAY (CURRENT_DATE)-1) DAYS)
一个月的最后一天：
CREATE VARIABLE LAST_DAY_OF_CURRENT_MONTH DATE
DEFAULT
(LAST_DAY (CURRENT DATE))
一个星期的第一天：
CREATE VARIABLE FIRST_DAY_OF_WEEK DATE
DEFAULT
(CURRENT_DATE – (DAYOFWEEK_ISO (CURRENT_DATE)-1) DAYS)
当前季度的第一天（请注意，定义中也使用了全局变量
FIRST_DAY_OF_THE_CURRENT_MONTH）：
CREATE VARIABLE FIRST_DAY_OF_QUARTER DATE
DEFAULT
(ROUND (FIRST_DAY_OF_CURRENT_MONTH,’Q'))
自动计算特定于地点的时间段
至此，我们已经计算了并未与一个国家（行政区）的业务规则相关联的日期/时间值。为了
演示如何“编写”全局变量，从而根据特定的国家（行政区）确定“行为”，下面我们来看
一个所有国家（行政区）均会使用的业务时间段：财年。
计算一个国家（行政区）的财年的开始和结束日期
每个国家（行政区）都有着不同的财年开始和结束日期。因此，任何需要计算这些值的全局
变量都必须能够辨识国家（行政区），确保返回的是正确的值。表 1 列出了本系列第 1 部
分中提到的国家（行政区）的财年（在某些情况下，一个国家或行政区内可能有多种财年定
义）：
国家（行政区）
财年
阿根廷
日历年
澳大利亚
从七月的第一天开始，至下一年的 6 月 30 日结束
巴西
日历年
中国
日历年
埃及
从七月的第一天开始，至下一年的 6 月 30 日结束
中国香港
从四月的第一天开始，至下一年的 4 月 31 日结束
印度
从四月的第一天开始，至下一年的 4 月 31 日结束
肯尼亚
从七月的第一天开始，至下一年的 6 月 30 日结束
墨西哥
日历年
新西兰
从七月的第一天开始，至下一年的 6 月 30 日结束
新加坡
从四月的第一天开始，至下一年的 4 月 31 日结束
南非
从四月的第一天开始，至下一年的 4 月 31 日结束
阿联酋
日历年
表 1. 不同国家（行政区）的财年定义
为了展示如何计算一个国家（行政区）的财年，我们将利用第 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
接下来，我们需要定义更多全局变量来表示当年、下一年和上一年：
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)
下面的代码展示了定义名为 START_OF_CURRENT_FISCAL_YEAR 和
END_OF_CURRENT_FISCAL_YEAR 的两个全局变量的 SQL 代码。请注意，计算基于已
经为用户授予的数据库角色。为了简便起见，本文仅介绍澳大利亚和中国香港。
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
)
如果授予某个用户的角色是 AUSTRALIA_ROLE，则查询：
SELECT …. FROM REAL_ESTATE_SALES
WHERE
EFFECTIVE_DATE BETWEEN
START_OF_CURRENT_FISCAL_YEAR AND END_OF_CURRENT_FISCAL_YEAR
应解析为：
SELECT …. FROM REAL_ESTATE_SALES
WHERE
EFFECTIVE_DATE BETWEEN ‘2011-07-01’ AND ‘2012-06-30’
可以看到，根据用户的角色，当前财年的值反映了澳大利亚的财年日期。
计算营业日的结束时间
特定于行业或特定于国家（行政区）的日期/时间段的另外一个例子就是营业日的结束时间。
各国家（行政区）、行业和/或法律要求对这个术语有着不同的定义。为了理解营业日结束
时间的重要意义，一个常见的例子就是我们在日常生活中经常用到的银行余额。通常情况下，
计算可用余额（可立即使用的现金）和经常项目结余（银行认为尚未清算、完成或正在处理
的所有交易）的公式中会使用营业日的结束日期。
您应如何计算阿根廷、巴西和墨西哥的营业日结束时间？如果我们假设，这几个国家的营业
日结束时间就是其股市闭市时间，那么下面这个例子展示了如何计算这几个国家的营业日结
束时间：
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
)
结束语
将全局变量与 DB2 10.1 的行列访问特性相结合，就可以开发安全的全球数据仓库，同时
解决特定于地点的需求。全局变量使 DBA 能够一次性地定义一个公式，不需要用户在其
多个 SQL 语句中编写正确的计算代码，因此全局变量同样有助于提高有用性，减少错误。
无相关文章。