Historical database schema

The database schemas describe the database tables and the relations among them. Using the database schemas, you can plan for the size of the database.

The information in the Historical database schemas helps you understand the mapping between the imported business measures model and the database tables. The dashboards use the Historical database for multidimensional analysis and generating reports.

Note:
  • The Repository, State, and Runtime databases are for internal use only, and they are subject to change without notice.
  • Customer written custom code that directly accesses the State, Runtime, or Repository databases is not supported by IBM®.
  • You cannot create your own dashboards using the Historical database schema.
The Historical database is initially populated with date/time data ranging from the year 1995 through the year 2009. If you anticipate recording dates/times (either as process start/termination times or as other metric data) which is outside this range of dates, you should use the following SQL script to add additional dates to the DIM_TIME table in the Historical database:
insert into <your WBI schema name>.dim_time( surrogate_key, year, month, day)
with WBITIME (skey, ldate) as 
(select surrogate_key+1 as skey, 
		COALESCE(	
			DATE(SUBSTR(DIGITS(YEAR),7,4) || '-' ||
				SUBSTR(DIGITS(MONTH),4,2) || '-' ||
				SUBSTR(DIGITS(DAY), 4,2)) + 1 DAYS,
			DATE('YYYY-MM-DD of the first day you'd want to start from,
						in case the DIM_TIME table is empty.')
		)as ldate 
	from sysibm.sysdummy1, <your WBI schema name>.dim_time 
	where 
				DATE(
					SUBSTR(DIGITS(YEAR) ,7,4) || '-' ||
					SUBSTR(DIGITS(MONTH),4,2) || '-' ||
					SUBSTR(DIGITS(DAY)  ,4,2)
				 		) = 
	(
		SELECT
				MAX(
				DATE(SUBSTR(DIGITS(YEAR),7,4) || '-' ||
				SUBSTR(DIGITS(MONTH),4,2) || '-' ||
				SUBSTR(DIGITS(DAY), 4,2)))
			  FROM <your WBI schema name>.DIM_TIME
	)
	UNION ALL 
	SELECT parent.skey+1, ldate + 1 DAYS 
	from WBITIME parent 
	where YEAR(ldate + 1 days) <  	where YEAR(ldate + 1 days) < 
  <YYYY 4 Digit YEAR FOR WHICH YOU DON't WANT DATA to end in>
)
select a.skey, year(a.ldate), month(a.ldate), day(a.ldate) 
from	WBITIME a
WHERE
a.ldate >= DATE('YYYY-MM-DD: The start of the range that should be inserted.')
AND	a.ldate <= DATE('YYYY-MM-DD: The end of the range that should be inserted.')
Note: There are four locations in this script which be updated to specify the beginning and ending dates for the data you wish to insert into DIM_TIME. There are also three locations in which you must specify your WBI Schema Name (typically "WBI")
Related reference
Historical database schema
Data movement service control table
Data movement service metadata and logging table

Copyright IBM Corporation 2005. All Rights Reserved.