(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