Data Warehouse Center Admin Guide

283 Pages·2004·2.26 MB·English

(cid:1)(cid:2)(cid:3)
IBM® DB2 Universal Database™
Data Warehouse Center
Administration Guide
Version 8.2
SC27-1123-01

(cid:1)(cid:2)(cid:3)
IBM® DB2 Universal Database™
Data Warehouse Center
Administration Guide
Version 8.2
SC27-1123-01
Beforeusingthisinformationandtheproductitsupports,besuretoreadthegeneralinformationunderNotices.
ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotected
bycopyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany
statementsprovidedinthismanualshouldnotbeinterpretedassuch.
YoucanorderIBMpublicationsonlineorthroughyourlocalIBMrepresentative.
v Toorderpublicationsonline,gototheIBMPublicationsCenteratwww.ibm.com/shop/publications/order
v TofindyourlocalIBMrepresentative,gototheIBMDirectoryofWorldwideContactsat
www.ibm.com/planetwide
ToorderDB2publicationsfromDB2MarketingandSalesintheUnitedStatesorCanada,call1-800-IBM-4YOU
(426-4968).
WhenyousendinformationtoIBM,yougrantIBManonexclusiverighttouseordistributetheinformationinany
wayitbelievesappropriatewithoutincurringanyobligationtoyou.
©CopyrightInternationalBusinessMachinesCorporation1996,2004.Allrightsreserved.
USGovernmentUsersRestrictedRights–Use,duplicationordisclosurerestrictedbyGSAADPScheduleContract
withIBMCorp.
Contents
About this book . . . . . . . . . . . ix Updatingyourenvironmentvariablesonz/OS 19
Whoshouldreadthisbook . . . . . . . . . ix DataWarehouseCentersecurity . . . . . . . 20
Prerequisitepublications . . . . . . . . . . ix Definingwarehousesecurity. . . . . . . . . 22
Chapter 1. About data warehousing. . . 1 Chapter 3. Setting up DB2 warehouse
Whatsolutionsdoesdatawarehousingprovide? . . 1 sources . . . . . . . . . . . . . . 25
Warehousetasks . . . . . . . . . . . . . 2 SupportedDB2datasources. . . . . . . . . 25
Datawarehouseobjects. . . . . . . . . . . 2 WarehouseagentsupportforDB2sources . . . . 26
Subjectareas . . . . . . . . . . . . . 2 SettingupconnectivityforDB2sources(Windows
Warehousesources . . . . . . . . . . . 3 NT,Windows2000,WindowsXP). . . . . . . 26
Warehousetargets . . . . . . . . . . . 3 SettingupconnectivityforDB2Universal
|| Warehousecontroldatabases . . . . . . . . 3 Databasedatabases(Windows). . . . . . . 26
Warehouseagentsandagentsites . . . . . . 3 SettingupconnectivityforDB2DRDAdatabases
Processesandsteps . . . . . . . . . . . 4 (Windows). . . . . . . . . . . . . . 26
SettingupconnectivityforDB2sources(AIX). . . 27
Chapter 2. Setting up your warehouse. . 7 SettingupconnectivityforaDB2Universal
Databasesource(AIX). . . . . . . . . . 27
StartingtheDataWarehouseCenter . . . . . . 7
SettingupconnectivityforaDB2DRDA
StartingtheDataWarehouseCenteradministrative
databasesource(AIX) . . . . . . . . . . 28
interface. . . . . . . . . . . . . . . . 8
SettingupconnectivityforDB2sources(Solaris
StartingandstoppingtheDataWarehouseCenter
OperatingEnvironment,Linux). . . . . . . . 28
serverandlogger. . . . . . . . . . . . . 9
SettingupconnectivityforaDB2Universal
Startingandstoppingthewarehouseserverand
Databasesource(SolarisOperatingEnvironment,
logger(Windows). . . . . . . . . . . . 9
Linux) . . . . . . . . . . . . . . . 28
Startingandstoppingthewarehouseserverand
SettingupconnectivityforaDRDAdatasource
logger(AIX) . . . . . . . . . . . . . 9
(SolarisOperatingEnvironment,Linux) . . . . 29
Verifyingthatthewarehouseserverandlogger
SettingupconnectivityforDB2sources(OS/2) . . 30
daemonsarerunning(AIX) . . . . . . . . 10
SettingupconnectivityforaDB2Universal
Startingthewarehouseagentdaemon . . . . . 11
Databasesource(OS/2) . . . . . . . . . 30
Warehouseagentdaemons . . . . . . . . 11
SettingupconnectivityforaDRDAdatasource
Connectivityrequirementsforthewarehouse
(OS/2) . . . . . . . . . . . . . . . 30
serverandthewarehouseagent . . . . . . 11
Accessingsourcedatabases . . . . . . . . . 30
Startingthewarehouseagentdaemon(Windows) 12
DefiningprivilegestoDB2datasources . . . . 31
StartingtheiSerieswarehouseagentdaemon . . 12
EstablishingconnectivitytoDB2datasources . . 31
VerifyingthattheiSerieswarehouseagentstarted 12
SettingupaccesstoDB2DRDAdatasources . . 31
VerifyingthattheiSerieswarehouseagent
DefiningprivilegestoDB2DRDAdatasources 32
daemonisstillrunning . . . . . . . . . 12
SettingupaDB2Connectgatewaysite
ConfiguringTCP/IPonz/OS . . . . . . . 13
(Windows). . . . . . . . . . . . . . 33
StartingthezSeriesagentdaemoninthe
ConnectingtoDB2DRDAdatasources . . . . 33
foreground . . . . . . . . . . . . . 13
Connectivityrequirementsforremotedatabases . . 34
StartingthezSerieswarehouseagentdaemonin
thebackground . . . . . . . . . . . . 14 Connectivityrequirementsforremotedatabases
fromtheiSerieswarehouseagent . . . . . . 34
VerifyingthatthezSerieswarehouseagentis
running. . . . . . . . . . . . . . . 14 Settingupaccesstolocalandremotedatabases
fromtheiSerieswarehouseagent . . . . . . 35
Verifyingcommunicationbetweenthewarehouse
serverandthewarehouseagent . . . . . . 15 Exampleofhowtocataloglocalandremote
databasenamesfortheiSerieswarehouseagent . 36
Stoppingthewarehouseagentdaemon . . . . . 15
Viewingandeditingremoterelationaldatabase
Stoppingthewarehouseagentdaemon
directoryentries. . . . . . . . . . . . 36
(Windows). . . . . . . . . . . . . . 15
Requirementsforaccessingremotedatasources
Stoppingthewarehouseagentdaemon(AIX,
withthezSerieswarehouseagent . . . . . . 36
SolarisOperatingEnvironment,Linux) . . . . 16
Requirementsforaccessingrelationaldata
StoppingtheiSerieswarehouseagentdaemon. . 16
sourceswiththezSerieswarehouseagent . . . 37
Stoppingthewarehouseagentdaemon(zSeries) 16
DB2warehousesources . . . . . . . . . . 37
Definingagentsites . . . . . . . . . . . 17
Ordinaryidentifiers . . . . . . . . . . 37
Agentsiteconfigurations. . . . . . . . . 17
Delimitedidentifiers . . . . . . . . . . 37
Defininganagentsite. . . . . . . . . . 18
© Copyright IBM Corp. 1996, 2004 iii
Metadatafromtables . . . . . . . . . . 37 UsingaMicrosoftExcelspreadsheetasa
Agentsites . . . . . . . . . . . . . 38 warehousesource(Windows) . . . . . . . 53
DefiningDB2warehousesources . . . . . . . 38 CatalogingaMicrosoftExceldatasourcein
ODBC(Windows) . . . . . . . . . . . 53
Chapter 4. Setting up non-DB2 CreatingnamedtablesforMicrosoftExceldata
warehouse sources . . . . . . . . . 39 sources(Windows) . . . . . . . . . . . 54
Creatingatargetwarehousedatabaseforuse
Supportednon-DB2datasources . . . . . . . 39
withaMicrosoftExceldatasource(Windows). . 54
Warehouseagentsupportfornon-DB2sources . . 40
Catalogingatargetwarehousedatabaseforuse
Columnsizetruncationfornon-DB2warehouse
withaMicrosoftExceldatasource(Windows). . 55
sources. . . . . . . . . . . . . . . . 40
DefiningsourcesandtargetstotheData
Settingupconnectivityfornon-DB2sources
WarehouseCenterthatuseaMicrosoftExceldata
(Windows). . . . . . . . . . . . . . . 41
source(Windows) . . . . . . . . . . . 55
SettingupconnectivityforaSybaseAdaptive
ConfiguringtheRedBrickclienttoaccessaRed
Serversource–withoutclient(Windows) . . . 41
BrickWarehousedatabase(Windows) . . . . 56
SettingupconnectivityforanOraclesource
Configuringnon-DB2warehousesources(AIX,
(Windows). . . . . . . . . . . . . . 41
Linux,SolarisOperatingEnvironment) . . . . . 57
SettingupconnectivityforanInformix9.2source
ConfiguringtheRedBrickclienttoaccessaRed
–withclient(Windows) . . . . . . . . . 42
BrickWarehousedatabase(AIX,Linux,Solaris
SettingupconnectivityforanInformix9.2source
OperatingEnvironment) . . . . . . . . . 57
–withoutclient(Windows) . . . . . . . . 42
Sample.odbc.inifilefortheRedBrickODBC
SettingupconnectivityforaMicrosoftSQL
driver . . . . . . . . . . . . . . . 58
Serverdatasource(Windows) . . . . . . . 42
Defininganon-DB2warehousesourceintheData
SettingupconnectivityforaMicrosoftAccess
WarehouseCenter . . . . . . . . . . . . 58
sourceoraMicrosoftExceldatasource
Specifyingdatabaseinformationforanon-DB2
(Windows). . . . . . . . . . . . . . 43
warehousesourceintheDataWarehouseCenter. . 59
SettingupconnectivityforanIMS,oraVSAM
DefiningwarehousesourcesforusewithaDB2
datasource(Windows) . . . . . . . . . 43
federatedsystem . . . . . . . . . . . . 60
Managingthewarehouseagentfornon-DB2sources 44
Serverdefinitionsandnicknametablesfor
Verifyingthewarehouseagent(AIX,Solaris
warehousesourcesaccessedthroughaDB2
OperatingEnvironment,Linux). . . . . . . 44
federatedserver. . . . . . . . . . . . . 60
Switchingbetweenversionsofthewarehouse
Definingsourcetablesforfederatedsystem
agent . . . . . . . . . . . . . . . 44
warehousesources . . . . . . . . . . . . 61
Settingupconnectivityfornon-DB2sources(AIX) 45
SettingupconnectivityforaSybaseAdaptive
Chapter 5. Setting up warehouse file
Serversource–withoutclient(AIX) . . . . . 45
sources . . . . . . . . . . . . . . 63
SettingupconnectivityforanOraclesource–
withoutclient(AIX) . . . . . . . . . . 45 Warehouseagentsupportforfilesources . . . . 63
SettingupconnectivityforanInformix9.2source DefiningafilesourcetotheDataWarehouseCenter 63
–withoutclient(AIX) . . . . . . . . . . 46 Accessingremotedatafiles . . . . . . . . . 64
SettingupconnectivityforaMicrosoftSQL AccessingdatafileswiththeCopyfileusingFTP
Serversource–withoutclient(AIX) . . . . . 47 warehouseprogram . . . . . . . . . . . 65
Settingupconnectivityfornon-DB2sources(Solaris Settingupconnectivityforfilesources(Windows
OperatingEnvironment,Linux). . . . . . . . 47 NT,Windows2000,WindowsXP). . . . . . . 65
SettingupconnectivityforaMicrosoftSQL Settingupconnectivityforaz/OSorVMfile
Serversource(SolarisOperatingEnvironment, source(WindowsNT,Windows2000,Windows
Linux) . . . . . . . . . . . . . . . 47 XP) . . . . . . . . . . . . . . . . 65
Configuringnon-DB2warehousesources . . . . 48 Settingupconnectivityforalocalfilesource
ConfiguringtheODBCdriverfornon-DB2 (WindowsNT,Windows2000,WindowsXP) . . 66
warehousesources–withoutclient(Windows). . 48 Requirementsforaccessingaremotefilefroma
|| HandlingerrorsforInformix9.2–withoutclient 49 fileserver(Windows) . . . . . . . . . . 66
Creatingatargetwarehousedatabase(Windows) 49 Settingupconnectivityforaremotefilesource
DefiningawarehousethatusestheMicrosoft (WindowsNT,Windows2000,WindowsXP) . . 67
Accessandwarehousetargetdatabases Settingupconnectivityforfilesources(AIX) . . . 67
(Windows). . . . . . . . . . . . . . 50 Settingupconnectivitytoaz/OSorVMfile
ImportingtabledefinitionsfromaMicrosoft source(AIX) . . . . . . . . . . . . . 67
Accessdatabase(Windows) . . . . . . . . 51 Settingupconnectivityforalocalfilesource
CatalogingaMicrosoftAccesssourcedatabasein (AIX) . . . . . . . . . . . . . . . 68
ODBC(Windows) . . . . . . . . . . . 51 Settingupconnectivityforaremotefilesource
Catalogingatargetwarehousedatabaseforuse (AIX) . . . . . . . . . . . . . . . 68
withaMicrosoftAccesssourcedatabase Settingupconnectivityforfilesources(Solaris
(Windows). . . . . . . . . . . . . . 52 OperatingEnvironment,Linux). . . . . . . . 69
iv DataWarehouseCenterAdminGuide
Settingupconnectivityforaz/OSorVMfile Chapter 8. Defining and running
source(SolarisOperatingEnvironment,Linux) . 69 processes . . . . . . . . . . . . . 87
Settingupconnectivityforalocalfilesource
Warehouseprocesses . . . . . . . . . . . 87
(SolarisOperatingEnvironment,Linux) . . . . 69
Definingthetransformationandmovementofdata
Settingupconnectivityforaremotefilesource
withintheDataWarehouseCenter . . . . . . 88
(SolarisOperatingEnvironment,Linux) . . . . 70
Warehousesteps. . . . . . . . . . . . . 88
Example.odbc.inifileentryforawarehousefile
Warehousefileprograms . . . . . . . . . . 91
source(AIX,SolarisOperatingEnvironment,
DB2warehouseutilities . . . . . . . . . . 91
Linux) . . . . . . . . . . . . . . . 70
DB2OLAPServerprograms. . . . . . . . . 92
NetworkFileSystemprotocol . . . . . . . . 71
Replicationprograms . . . . . . . . . . . 93
Warehousedeprecatedprograms . . . . . . . 94
Chapter 6. Setting up access to a
Warehousetransformers . . . . . . . . . . 96
warehouse . . . . . . . . . . . . . 73 Statisticaltransformers . . . . . . . . . . 96
SettingupaDB2UniversalDatabasewarehouse . . 73 User-definedprograms . . . . . . . . . . 97
DefiningprivilegesforDB2UniversalDatabase RedBrickWarehouseutilityprograms . . . . . 98
warehouses . . . . . . . . . . . . . 73 Columnmapping . . . . . . . . . . . . 98
ConnectingtoDB2UniversalDatabaseandDB2 Runningwarehousesteps . . . . . . . . . 100
EnterpriseServerEditionwarehouses . . . . 74 Steppromotionandtesting. . . . . . . . 100
SettingupaDB2foriSerieswarehouse . . . . . 74 Verifyingtheresultsofastepthatisrunintest
DefiningprivilegestoDB2foriSerieswarehouses 74 mode . . . . . . . . . . . . . . . 101
SettingupaDB2Connectgatewaysite(iSeries) 75 Runningstepsthatusetransienttablesas
ConnectingtoaDB2foriSerieswarehousewith sources . . . . . . . . . . . . . . 101
DB2Connect . . . . . . . . . . . . . 75 RunningastepfromoutsidetheData
SettingupaDB2forz/OSwarehouse . . . . . 75 WarehouseCenterusingtheexternaltrigger
SettingupaDB2UniversalDatabaseforz/OS program . . . . . . . . . . . . . . 102
warehouse. . . . . . . . . . . . . . 75 Syntaxforstartingtheexternaltriggerserver 103
DefiningprivilegesforaDB2forz/OS Syntaxforstartingtheexternaltriggerclient 103
warehouse. . . . . . . . . . . . . . 76 DataWarehouseCentermessagesforthe
ConnectingtoaDB2forz/OSwarehouse externaltriggerprogram . . . . . . . . 105
database . . . . . . . . . . . . . . 76 Printingstepinformationtoatextfile . . . . 106
DefiningaDB2forz/OSwarehousetotheData Promotingwarehouseprocesses . . . . . . . 106
WarehouseCenter . . . . . . . . . . . 76 Managingsteptaskflow . . . . . . . . . 106
Settingupawarehouseonadatabasewithmultiple Steptaskflow . . . . . . . . . . . . 106
partitions . . . . . . . . . . . . . . . 77 Cascadingprocesses . . . . . . . . . . . 107
Definingprivilegestoadatabasewithmultiple Processtaskflow . . . . . . . . . . . 107
partitions . . . . . . . . . . . . . . 77 Monitoringtheprogressofstepsandprocesses . . 108
Definingadatabasewithmultiplepartitionsto Monitoringtheprogressofasteporprocess
theDataWarehouseCenter . . . . . . . . 78 usingtheWorkinProgresswindow. . . . . 108
CreatingtheDataWarehouseCenterstatustable. . 78 Stepandprocesserrormessages . . . . . . 109
Definingawarehouseforusewithafederated Samplingdata . . . . . . . . . . . . 109
system . . . . . . . . . . . . . . . . 79
Creatingtargettablesinaremotedatabase. . . . 80 Chapter 9. Selecting, inserting, and
Movingatargettablefromafederateddatabasetoa updating source data in a target table. 111
remotedatabase. . . . . . . . . . . . . 81
SQLsteps. . . . . . . . . . . . . . . 111
Updatingatableinaremotedatabase . . . . . 82
DefininganSQLstep. . . . . . . . . . . 111
Incrementalcommit . . . . . . . . . . . 112
Chapter 7. Setting up a warehouse
Joiningsourcecolumns . . . . . . . . . . 112
target . . . . . . . . . . . . . . . 83 Definingajoin . . . . . . . . . . . . 112
Warehousetargets . . . . . . . . . . . . 83 Removingajoin . . . . . . . . . . . 113
OrdinaryidentifiersupportintheData Transformingcodes . . . . . . . . . . 114
WarehouseCenter . . . . . . . . . . . 83 Jointypes. . . . . . . . . . . . . . 114
DelimitedidentifiersupportintheData Starjoins. . . . . . . . . . . . . . 116
WarehouseCenter . . . . . . . . . . . 83 Filteringdata . . . . . . . . . . . . 117
Editioncolumns. . . . . . . . . . . . 83 Specifyingsearchconditionswhenfilteringdata 118
Supportedwarehousetargets . . . . . . . . 84 Creatingcompositesummarysteps . . . . . 118
Definingawarehousetarget. . . . . . . . . 84 Definingacalculatedcolumnandaddingittoa
Warehouseprimaryandforeignkeys. . . . . . 85 step . . . . . . . . . . . . . . . 119
RunningDB2WarehouseManagerona64-bitDB2 Addingafunctiontoanexpressioninthe
instance . . . . . . . . . . . . . . . 86 ExpressionBuilder . . . . . . . . . . 120
Addingaconstanttoanexpression . . . . . 120
Contents v
Chapter 10. Loading and exporting TrilliumSoftwareSystemcomponents . . . . 155
data . . . . . . . . . . . . . . . 121 Trilliummetadata . . . . . . . . . . . 156
ImportingTrilliummetadata . . . . . . . 156
DataWarehouseCenterloadandexportprograms 121
TrilliumBatchSystemJCLfiles . . . . . . 157
Exportingdata . . . . . . . . . . . . . 122
Example:JobstepthatincludesaSYSTERMDD
DefiningvaluesforaDB2UDBexportutility 122
statement. . . . . . . . . . . . . . 158
DefiningvaluesfortheDataexportwithODBC
TrilliumBatchSystemuser-definedprogram 158
tofileutility. . . . . . . . . . . . . 122
ParametersfortheTrilliumBatchSystemscript
DefiningvaluesfortheRedBrickSQLExport
orJCL. . . . . . . . . . . . . . . 159
utility . . . . . . . . . . . . . . . 123
ErrorhandlingforTrilliumBatchSystem
Loadingdata . . . . . . . . . . . . . 124
programs. . . . . . . . . . . . . . 160
DefiningvaluesforaDB2UniversalDatabase
Errorreturncodesfornameandaddress
loadutility . . . . . . . . . . . . . 124
cleansing. . . . . . . . . . . . . . 161
DefiningaDB2foriSeriesDataLoadInsert
utility . . . . . . . . . . . . . . . 125
Chapter 13. Calculating statistics. . . 163
DefiningaDB2foriSeriesDataLoadReplace
utility . . . . . . . . . . . . . . . 126 DefiningstatisticaltransformersintheData
ModstringparametersforDB2foriSeriesload WarehouseCenter. . . . . . . . . . . . 163
utilities . . . . . . . . . . . . . . 127 ANOVAtransformer . . . . . . . . . . . 163
TracefilesfortheDB2foriSeriesloadutilities 128 CalculateStatisticstransformer . . . . . . . 164
ViewingtracefilesfortheDB2foriSeriesload CalculateSubtotalstransformer . . . . . . . 164
utilities . . . . . . . . . . . . . . 129 Chi-squaretransformer . . . . . . . . . . 166
DefiningafileextensiontoClientAccess/400 129 Correlationanalysis . . . . . . . . . . . 166
DefiningaDB2forz/OSloadutility . . . . 130 Movingaverages . . . . . . . . . . . . 168
CopyingdatabetweenDB2utilities . . . . . 131 Regressiontransformer . . . . . . . . . . 169
DefiningvaluesfortheRedBrickTMULoad
utility . . . . . . . . . . . . . . . 132 Chapter 14. Exporting and importing
metadata in the Data Warehouse
Chapter 11. Moving files and tables 133 Center . . . . . . . . . . . . . . 171
ManipulatingfilesusingFTPortheSubmitJCL Metadataexportcapabilities . . . . . . . . 171
jobstreamwarehouseprogram. . . . . . . . 133 TaglanguageandXMLfiles . . . . . . . . 172
AccessingaremotefileonasecureUNIXorUNIX Importingmetadata . . . . . . . . . . . 173
SystemServicessystem . . . . . . . . . . 134 Publishingwarehousemetadata . . . . . . . 174
Replication . . . . . . . . . . . . . . 134 Increasingtheapplicationheapsizeforpublishing
ReplicationintheDataWarehouseCenter. . . 135 DataWarehouseCentermetadata . . . . . . 175
ReplicationsourcetablesintheDataWarehouse MQSeries. . . . . . . . . . . . . . . 175
Center. . . . . . . . . . . . . . . 136 MQSeriesdata . . . . . . . . . . . . 175
SettingupreplicationintheDataWarehouse CreatingviewsforMQSeriesmessages. . . . 176
Center. . . . . . . . . . . . . . . 137
ImportingMQSeriesmessagesandXML
Creatingreplicationcontroltables . . . . . 138 metadata. . . . . . . . . . . . . . 176
DefiningareplicationstepintheData MQSeriesstoredprocedure. . . . . . . . 178
WarehouseCenter. . . . . . . . . . . 139
ErrorreturncodesfortheMQXMLXFstored
Usingareplicationstepinaprocess. . . . . 139 procedure . . . . . . . . . . . . . 178
Replicationpasswordfiles . . . . . . . . 140 ErrorloggingforMQSeries. . . . . . . . 179
ImportingERwinmetadata. . . . . . . . . 180
Chapter 12. Transforming data . . . . 143
IBMERwin3.5.2metadataextractprogram . . 180
UsingtransformersintheDataWarehouseCenter 143 RunningtheERwinmetadataimportcommands 180
Transformingtargettables . . . . . . . . 143 | db2erwinimportcommandforERwin4.0or
CleanDatatransformer . . . . . . . . . 143 || later . . . . . . . . . . . . . . . 181
RulestablesforCleanDatatransformers . . . 148 CreatingtaglanguagefilesfortheIBMERwin
Keycolumns . . . . . . . . . . . . 149 MetadataExtractprogram . . . . . . . . 184
Periodtable . . . . . . . . . . . . . 149 MergingERwinmetadatawithexistingdatabase
InvertDatatransformer . . . . . . . . . 150 data . . . . . . . . . . . . . . . 185
Pivotdatatransformer . . . . . . . . . 151 ReturningtheER1filetoawritablestate . . . 185
FormatDatetransformer. . . . . . . . . 152 iwh2imp2commandsyntaxforERwin3.5.2to
Changingtheformatofadatefield . . . . . 152 importmetadataintotheDataWarehouse
Specifyingtheinputformatforadatefield . . 153 Center. . . . . . . . . . . . . . . 185
Specifyingtheoutputformatforadatefield 153 Changingthetaglanguagefiletochangethe
Cleaningnameandaddressdata. . . . . . . 153 DB2databasedefinitiontoasourceintheData
Cleaningnameandaddressdatawiththe WarehouseCenter. . . . . . . . . . . 186
TrilliumSoftwareSystem . . . . . . . . 153 IBMERwinMetadataExtractprogramerrors 186
vi DataWarehouseCenterAdminGuide
Chapter 15. Extending the Data Chapter 18. Managing the control
Warehouse Center . . . . . . . . . 189 database . . . . . . . . . . . . . 217
User-definedprograms . . . . . . . . . . 189 Backingupdata . . . . . . . . . . . . 217
Whatisaprogramgroup? . . . . . . . . 189 StoppingtheDataWarehouseCenterservices
Whatisauser-definedprogram?. . . . . . 189 (Windows) . . . . . . . . . . . . . 217
Usingauser-definedprogramintheData Backingupthewarehousecontroldatabase . . 217
WarehouseCenter. . . . . . . . . . . 190 Expandingyourwarehouse . . . . . . . . 217
Agentsitesforuser-definedprograms . . . . 190 Addingordeletingadministrativeinterfaces
Parametersforuser-definedprograms . . . . 191 andwarehouseagentsintheDataWarehouse
Writingyourownprogramforusewiththe Center. . . . . . . . . . . . . . . 218
DataWarehouseCenter . . . . . . . . . 191 Initializingawarehousedatabase. . . . . . . 218
MicrosoftOLEDBandDataTransactionServices Changingtheactivewarehousecontroldatabase 218
support . . . . . . . . . . . . . . . 197 Initializingawarehousecontroldatabaseduring
CreatingviewsforOLEDBtablefunctions . . 198 installation . . . . . . . . . . . . . 219
CreatingviewsforDTSpackages. . . . . . 198 MigratingaDB2UniversalDatabaseVersion7
ControlDatabasetoDB2UniversalDatabase
Chapter 16. Creating a star schema Version8whenVersion7andVersion8are
from within the Data Warehouse installedondifferentsystems . . . . . . . 220
DataWarehouseCenterconfiguration . . . . . 221
Center . . . . . . . . . . . . . . 201
DataWarehouseCenterconfiguration . . . . 221
Starschemas . . . . . . . . . . . . . 201
DesigningthewarehouseschemaintheData
Chapter 19. Refreshing an OLAP
WarehouseCenter. . . . . . . . . . . . 202
Server database . . . . . . . . . . 223
Warehouseschemas . . . . . . . . . . . 202
Addingtablesandviewstothewarehouseschema 202 LoadingdataintotheOLAPserverdatabasefrom
ExportingawarehouseschematotheDB2OLAP theDataWarehouseCenter. . . . . . . . . 223
IntegrationServer. . . . . . . . . . . . 203 RunningcalculationsonOLAPserverdatafrom
WorkingwithawarehouseschemaintheDB2 theDataWarehouseCenter. . . . . . . . . 224
OLAPIntegrationServer . . . . . . . . . 204 LoadingdatafromaflatfileintoanOLAPServer
Completingthedesignforyourcube . . . . 204 database . . . . . . . . . . . . . . . 224
CreatinganoutlineintheDB2OLAP LoadingdatafromatableintoanOLAPserver
IntegrationServer. . . . . . . . . . . 204 database . . . . . . . . . . . . . . . 225
Creatingaloadscripttoloaddataintothecube UpdatinganOLAPserveroutlinefromtheData
intheDB2OLAPIntegrationServer. . . . . 205 WarehouseCenter. . . . . . . . . . . . 225
Creatingabatchfiletoloadthecommandscript
fortheDB2OLAPIntegrationServer . . . . 206 Chapter 20. Data Warehouse Center
ExportingthemetadatatotheDataWarehouse logging and trace data . . . . . . . 227
Center. . . . . . . . . . . . . . . 207 Thebasicloggingfunction . . . . . . . . . 227
LoadingthemultidimensionalcubefromtheData Warehouselogfiles . . . . . . . . . . 227
WarehouseCenter. . . . . . . . . . . . 208 Viewingrun-timeerrorsusingthebasiclogging
DataWarehouseCenterobjectscreatedbythe function . . . . . . . . . . . . . . 227
DB2OLAPIntegrationServerexportprocess. . 208 Viewingbuild-timeerrorsusingthebasic
Creatingascheduletopopulateacube. . . . 209 loggingfunction . . . . . . . . . . . 228
Aboutpublishingmetadataaboutyour ViewinglogentriesintheDataWarehouse
warehouseschema . . . . . . . . . . 209 Center. . . . . . . . . . . . . . . 228
DataWarehouseCentercomponenttracedata . . 228
Chapter 17. Maintaining the Componenttracedata . . . . . . . . . 228
warehouse database . . . . . . . . 211 Runningwarehouseagentsasauserprocess
Reorganizingdata. . . . . . . . . . . . 211 (Windows) . . . . . . . . . . . . . 230
DefiningvaluesforDB2UDBREORGorDB2 RunningaDataWarehouseCentercomponent
forz/OSREORGutilities . . . . . . . . 211 trace . . . . . . . . . . . . . . . 230
DefiningvaluesforaDB2forz/OSUtility . . 211 Errorloggingforwarehouseprogramsand
ParametersforaDB2forz/OSUtility . . . . 211 transformers. . . . . . . . . . . . . . 231
Updatingsystemcatalogstatistics . . . . . . 215 TracingApplyprogramerrors. . . . . . . . 232
DefiningvaluesforaDB2UDBRUNSTATS Starterrortracefiles . . . . . . . . . . . 232
utility . . . . . . . . . . . . . . . 215
DefiningvaluesforaDB2forz/OSRUNSTATS Appendix A. Metadata mappings . . . 233
utility . . . . . . . . . . . . . . . 215 MetadatamappingsbetweentheDB2OLAP
IntegrationServerandtheDataWarehouseCenter. 233
Contents vii
| MappingERwinVersion4.0andlaterobject DefiningvaluesforaVisualWarehouse5.2
|| attributestoDataWarehouseCentertags . . . . 234 LoadflatfileintoDB2EnterpriseServerEdition
MappingERwinVersion3.5.2objectattributesto (VWPLDPR)program(AIX) . . . . . . . 247
DataWarehouseCentertags . . . . . . . . 236 DefiningvaluesfortheDataWarehouseCenter
TrilliumDDLtoDataWarehouseCentermetadata Version7.2CleanDatatransformer . . . . . . 249
mapping . . . . . . . . . . . . . . . 237 DataWarehouseCenterVersion7.2CleanData
MetadatamappingsbetweentheDataWarehouse transformer . . . . . . . . . . . . . 249
CenterandCWMXMLobjectsandproperties . . 238 RulestablesforDataWarehouseCenterVersion
7.2CleanDatatransformer. . . . . . . . 250
Appendix B. Defining values for DefiningaDataWarehouseCenterVersion7.2
warehouse deprecated programs and CleanDatatransformer . . . . . . . . . 252
transformers. . . . . . . . . . . . 241
Appendix C. Data Warehouse Center
DefiningvaluesforVisualWarehouse5.2
environment structure . . . . . . . 253
compatibleprograms. . . . . . . . . . . 241
DefiningvaluesforaVisualWarehouse5.2DB2 DataWarehouseCenterenvironmentvariables . . 253
UDBDataExport(VWPEXPT1)program . . . 241 DataWarehouseCenterregistryupdates . . . . 253
DefiningvaluesforaVisualWarehouse5.2DB2 DataWarehouseCenterconfigurationfiles . . . 254
UDBDataLoadInsert(VWPLOADI)program . 242
DefiningvaluesforaVisualWarehouse5.2DB2 Notices . . . . . . . . . . . . . . 255
UDBDataLoadReplace(VWPLOADR) Trademarks . . . . . . . . . . . . . . 257
program . . . . . . . . . . . . . . 244
Filetypemodifiers . . . . . . . . . . 245 Index . . . . . . . . . . . . . . . 259
DefiningvaluesforaVisualWarehouse5.2DB2
UDBREORG(VWPREORG)program . . . . 246
Contacting IBM . . . . . . . . . . 267
DefiningvaluesforaVisualWarehouse5.2DB2
Productinformation . . . . . . . . . . . 267
UDBRUNSTATS(VWPSTATS)program . . . 247
viii DataWarehouseCenterAdminGuide