(cid:1)(cid:2)(cid:3) IBM® DB2 Universal Database™ Data Warehouse Center Administration Guide Version 8 SC27-1123-00 (cid:1)(cid:2)(cid:3) IBM® DB2 Universal Database™ Data Warehouse Center Administration Guide Version 8 SC27-1123-00 Beforeusingthisinformationandtheproductitsupports,besuretoreadthegeneralinformationunderNotices. ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotectedby copyrightlaw.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-2002.Allrightsreserved. USGovernmentUsersRestrictedRights–Use,duplicationordisclosurerestrictedbyGSAADPScheduleContract withIBMCorp. Contents Aboutthisbook . . . . . . . . . . xiii VerifyingthatthezSerieswarehouseagent Whoshouldreadthisbook. . . . . . . xiii isrunning. . . . . . . . . . . . 13 Prerequisitepublications . . . . . . . xiii Verifyingcommunicationbetweenthe warehouseserverandthewarehouseagent 14 Chapter1.Aboutdatawarehousing. . . . 1 Stoppingthewarehouseagentdaemon . . . 15 Whatisdatawarehousing? . . . . . . . 1 Stoppingthewarehouseagentdaemon Warehousetasks . . . . . . . . . . . 2 (WindowsNT,Windows2000,Windows Datawarehouseobjects . . . . . . . . 2 XP) . . . . . . . . . . . . . . 15 Subjectareas . . . . . . . . . . . 2 Stoppingthewarehouseagentdaemon Warehousesources . . . . . . . . . 3 (AIX,SolarisOperatingEnvironment, Warehousetargets . . . . . . . . . 3 Linux) . . . . . . . . . . . . . 15 Warehouseagentsandagentsites . . . . 3 StoppingtheiSerieswarehouseagent Processesandsteps . . . . . . . . . 4 daemon . . . . . . . . . . . . 16 Stoppingthewarehouseagentdaemon Chapter2.Settingupyourwarehouse. . . 7 (zSeries) . . . . . . . . . . . . 16 Buildingadatawarehouse . . . . . . . 7 Definingagentsites . . . . . . . . . 16 StartingtheDataWarehouseCenter . . . . 7 Defininganagentsite. . . . . . . . 17 StartingandstoppingtheDataWarehouse Agentsiteconfigurations. . . . . . . 17 Centerserverandlogger . . . . . . . . 8 Updatingyourenvironmentvariableson Startingandstoppingthewarehouseserver z/OS . . . . . . . . . . . . . 19 andlogger(WindowsNT,Windows2000, DataWarehouseCentersecurity . . . . . 20 WindowsXP) . . . . . . . . . . . 8 Definingwarehousesecurity . . . . . . 22 Startingandstoppingthewarehouseserver andlogger(AIX). . . . . . . . . . 8 Chapter3.SettingupDB2warehouse Verifyingthatthewarehouseserverand sources . . . . . . . . . . . . . 25 loggerdaemonsarerunning(AIX). . . . 9 SupportedDB2datasources . . . . . . 25 Startingthewarehouseagentdaemon . . . 10 WarehouseagentsupportforDB2sources . . 26 Warehouseagentdaemons . . . . . . 10 SettingupconnectivityforDB2sources Connectivityrequirementsforthe (WindowsNT,Windows2000,WindowsXP) . 26 warehouseserverandthewarehouseagent 10 SettingupconnectivityforDB2Universal Startingthewarehouseagentdaemon Databasedatabases(WindowsNT, (WindowsNT,Windows2000,orWindows Windows2000,WindowsXP) . . . . . 26 XP) . . . . . . . . . . . . . . 10 SettingupconnectivityforDB2DRDA StartingtheiSerieswarehouseagent databases(WindowsNT,Windows2000, daemon . . . . . . . . . . . . 11 WindowsXP) . . . . . . . . . . 27 VerifyingthattheiSerieswarehouseagent SettingupconnectivityforDB2sources(AIX) 27 started . . . . . . . . . . . . . 11 SettingupconnectivityforaDB2 VerifyingthattheiSerieswarehouseagent UniversalDatabasesource(AIX) . . . . 27 daemonisstillrunning . . . . . . . 11 SettingupconnectivityforaDB2DRDA ConfiguringTCP/IPonz/OS . . . . . 12 databasesource(AIX). . . . . . . . 28 StartingthezSeriesagentdaemoninthe SettingupconnectivityforDB2sources foreground . . . . . . . . . . . 12 (SolarisOperatingEnvironment,Linux). . . 29 StartingthezSerieswarehouseagent SettingupconnectivityforaDB2 daemoninthebackground . . . . . . 13 UniversalDatabasesource(Solaris OperatingEnvironment,Linux) . . . . 29 © Copyright IBM Corp. 1996 - 2002 iii SettingupconnectivityforaDRDAdata Supportednon-DB2datasources . . . . . 43 source(SolarisOperatingEnvironment, Warehouseagentsupportfornon-DB2 Linux) . . . . . . . . . . . . . 30 sources. . . . . . . . . . . . . . 43 SettingupconnectivityforDB2sources Columnsizetruncationfornon-DB2 (OS/2). . . . . . . . . . . . . . 31 warehousesources. . . . . . . . . . 44 SettingupconnectivityforaDB2 Settingupconnectivityfornon-DB2sources UniversalDatabasesource(OS/2). . . . 31 (WindowsNT,Windows2000,WindowsXP) . 45 SettingupconnectivityforaDRDAdata SettingupconnectivityforaSybase source(OS/2) . . . . . . . . . . 31 AdaptiveServersource–withoutclient Accessingsourcedatabases . . . . . . . 32 (WindowsNT,Windows2000,Windows SettingupaccesstoDB2Universal XP) . . . . . . . . . . . . . . 45 Databasedatasources. . . . . . . . 32 SettingupconnectivityforanOracle DefiningprivilegestoDB2Universal source(WindowsNT,Windows2000, Databasedatasources. . . . . . . . 32 WindowsXP) . . . . . . . . . . 45 EstablishingconnectivitytoDB2Universal SettingupconnectivityforanInformix9.2 Databasesources . . . . . . . . . 33 source–withclient(WindowsNT, SettingupaccesstoDB2DRDAdata Windows2000,WindowsXP) . . . . . 46 sources. . . . . . . . . . . . . 33 SettingupconnectivityforanInformix9.2 DefiningprivilegestoDB2DRDAdata source–withoutclient(Windows,NT, sources. . . . . . . . . . . . . 34 Windows2000,WindowsXP) . . . . . 47 SettingupaDB2Connectgatewaysite SettingupconnectivityforaMicrosoft (WindowsNT,Windows2000,Windows SQLServerdatasource(WindowsNT, XP) . . . . . . . . . . . . . . 35 Windows2000,WindowsXP) . . . . . 47 ConnectingtoDB2DRDAdatasources . . 35 SettingupconnectivityforaMicrosoft Connectivityrequirementsforremote Accesssource(WindowsNT,Windows databases . . . . . . . . . . . . . 36 2000,WindowsXP) . . . . . . . . 48 Connectivityrequirementsforremote SettingupconnectivityforaMicrosoft databasesfromtheiSerieswarehouse Exceldatasource(WindowsNT,Windows agent . . . . . . . . . . . . . 36 2000,WindowsXP) . . . . . . . . 48 Connectingtolocalandremotedatabases SettingupconnectivityforanIMS,ora fromtheiSerieswarehouseagent . . . . 37 VSAMdatasource(WindowsNT, Exampleofhowtocataloglocaland Windows2000,WindowsXP) . . . . . 49 remotedatabasenamesfortheiSeries Managingthewarehouseagentfornon-DB2 warehouseagent . . . . . . . . . 38 sources. . . . . . . . . . . . . . 50 Viewing,adding,changing,andremoving Verifyingthewarehouseagent(AIX, remoterelationaldatabasedirectoryentries 38 SolarisOperatingEnvironment,Linux) . . 50 Requirementsforaccessingremotedata Switchingbetweenversionsofthe sourceswiththezSerieswarehouseagent . 39 warehouseagent . . . . . . . . . 50 RequirementsforaccessingDB2Relational Settingupconnectivityfornon-DB2sources ConnectdatasourceswiththezSeries (AIX) . . . . . . . . . . . . . . 51 warehouseagent . . . . . . . . . 39 SettingupconnectivityforaSybase DB2warehousesources . . . . . . . . 39 AdaptiveServer(withoutclient)source Ordinaryidentifiers . . . . . . . . 39 (AIX) . . . . . . . . . . . . . 51 Delimitedidentifiers . . . . . . . . 40 SettingupconnectivityforanOracle Importingmetadatafromtables . . . . 40 source(AIX) . . . . . . . . . . . 51 Agentsites . . . . . . . . . . . 40 SettingupconnectivityforanInformix9.2 DefiningDB2warehousesources . . . . . 40 source–withclient(AIX) . . . . . . 52 SettingupconnectivityforanInformix9.2 Chapter4.Settingupnon-DB2warehouse source–withoutclient(AIX) . . . . . 53 sources . . . . . . . . . . . . . 43 iv DataWarehouseCenterAdminGuide SettingupconnectivityforaMicrosoft ConfiguringtheODBCdriverfora SQLServersource(AIX). . . . . . . 54 MicrosoftSQLServer(WindowsNT, Settingupconnectivityfornon-DB2sources Windows2000,WindowsXP) . . . . . 68 (SolarisOperatingEnvironment,Linux). . . 54 ConfiguringaMicrosoftAccessclient SettingupconnectivityforaSybase (WindowsNT,Windows2000,Windows AdaptiveServersource–withoutclient XP) . . . . . . . . . . . . . . 69 (SolarisOperatingEnvironment,Linux). . 54 CreatingaMicrosoftAccesssource SettingupconnectivityforanOracle8 database(WindowsNT,Windows2000, datasource(SolarisOperating WindowsXP) . . . . . . . . . . 70 Environment,Linux) . . . . . . . . 55 Creatingatargetwarehousedatabase SettingupconnectivityforanInformix9.2 (WindowsNT,Windows2000,Windows source–withclient(SolarisOperating XP) . . . . . . . . . . . . . . 70 Environment,Linux) . . . . . . . . 56 Definingawarehousethatusesthe SettingupconnectivityforanInformix9.2 MicrosoftAccessandwarehousetarget source–withoutclient(SolarisOperating databases(WindowsNT,Windows2000, Environment) . . . . . . . . . . 57 WindowsXP) . . . . . . . . . . 71 SettingupconnectivityforaMicrosoft Importingtabledefinitionsfroma SQLServersource(SolarisOperating MicrosoftAccessdatabase(WindowsNT, Environment,Linux) . . . . . . . . 58 Windows2000,WindowsXP) . . . . . 72 Configuringnon-DB2warehousesources . . 59 CatalogingaMicrosoftAccesssource ConfiguringanInformix9.2client databaseinODBC(WindowsNT, (WindowsNT,Windows2000,Windows Windows2000,WindowsXP) . . . . . 73 XP) . . . . . . . . . . . . . . 59 Catalogingatargetwarehousedatabasefor ConfiguringanInformix9.2server–with usewithaMicrosoftAccesssource client(WindowsNT,Windows2000, database(WindowsNT,Windows2000, WindowsXP) . . . . . . . . . . 59 WindowsXP) . . . . . . . . . . 74 ConfiguringahostforanInformix9.2 UsingaMicrosoftExcelspreadsheetasa source—withclient(WindowsNT, warehousesource(WindowsNT,Windows Windows2000,WindowsXP) . . . . . 60 2000,WindowsXP) . . . . . . . . 75 ConfiguringtheODBCdriverforInformix CatalogingaMicrosoftExceldatasourcein 9.2–withoutclient(WindowsNT, ODBC(WindowsNT,Windows2000, Windows2000,WindowsXP) . . . . . 61 WindowsXP) . . . . . . . . . . 75 ConfiguringtheInformix9.2server– CreatingnamedtablesforMicrosoftExcel withoutclient(WindowsNT,Windows datasources(WindowsNT,Windows2000, 2000,WindowsXP) . . . . . . . . 62 WindowsXP) . . . . . . . . . . 76 ConfiguringtheODBCdriverforaSybase Creatingatargetwarehousedatabasefor AdaptiveServersource–withoutclient usewithaMicrosoftExceldatasource (WindowsNT,Windows2000,Windows (WindowsNT,Windows2000,Windows XP) . . . . . . . . . . . . . . 62 XP) . . . . . . . . . . . . . . 76 ConfiguringanOracle8client(Windows Catalogingatargetwarehousedatabasefor NT,Windows2000,WindowsXP). . . . 64 usewithaMicrosoftExceldatasource ConfiguringtheODBCdriverforan (WindowsNT,Windows2000,Windows Oracle8client(WindowsNT,Windows XP) . . . . . . . . . . . . . . 77 2000,WindowsXP) . . . . . . . . 67 DefiningsourcesandtargetstotheData TestinganOracle8clientconfiguration WarehouseCenterthatuseaMicrosoft (WindowsNT,Windows2000,Windows Exceldatasource(WindowsNT,Windows XP) . . . . . . . . . . . . . . 67 2000,WindowsXP) . . . . . . . . 77 ConfiguringaMicrosoftSQLServerclient AccessinganIMSorVSAMsource (WindowsNT,Windows2000,Windows databasefromtheDataWarehouseCenter. 78 XP) . . . . . . . . . . . . . . 68 Contents v Configuringnon-DB2warehousesources Requirementsforaccessingaremotefile (AIX,Linux,SolarisOperatingEnvironment) . 79 fromafileserver(WindowsNT,Windows ConfiguringanInformix9.2client(AIX, 2000,WindowsXP) . . . . . . . . 91 SolarisOperatingEnvironment) . . . . 79 Settingupconnectivityforaremotefile SQLHostsfile . . . . . . . . . . 79 source(WindowsNT,Windows2000, ConfiguringtheODBCdriverforaccessing WindowsXP) . . . . . . . . . . 91 anInformix9.2database(AIX,Solaris Settingupconnectivityforfilesources(AIX) 92 OperatingEnvironment) . . . . . . . 79 Settingupconnectivitytoaz/OSorVM ConfiguringtheODBCdriverforthe filesource(AIX) . . . . . . . . . 92 SybaseAdaptiveServer–withoutclient Settingupconnectivityforalocalfile (AIX,Linux,SolarisOperating source(AIX) . . . . . . . . . . . 93 Environment) . . . . . . . . . . 80 Settingupconnectivityforaremotefile ConfiguringanOracle8client(AIX,Linux, source(AIX) . . . . . . . . . . . 93 SolarisOperatingEnvironment) . . . . 81 Settingupconnectivityforfilesources Exampleofanentryforatnsnames.orafile (SolarisOperatingEnvironment,Linux). . . 94 (AIX,Linux,SolarisOperating Settingupconnectivityforaz/OSorVM Environment) . . . . . . . . . . 81 filesource(SolarisOperatingEnvironment, ConfiguringtheODBCdriverforan Linux) . . . . . . . . . . . . . 94 Oracle8database(AIX,Linux,Solaris Settingupconnectivityforalocalfile OperatingEnvironment) . . . . . . . 82 source(SolarisOperatingEnvironment, ConfiguringaMicrosoftSQLServerclient Linux) . . . . . . . . . . . . . 94 (AIX,Linux,SolarisOperating Settingupconnectivityforaremotefile Environment) . . . . . . . . . . 82 source(SolarisOperatingEnvironment, ConfiguringtheODBCdriverforaccessing Linux) . . . . . . . . . . . . . 95 aMicrosoftSQLServerdatabase(AIX, Example.odbc.inifileentryfora Linux,SolarisOperatingEnvironment) . . 83 warehousefilesource(AIX,Solaris Defininganon-DB2warehousesourceinthe OperatingEnvironment,Linux) . . . . 95 DataWarehouseCenter . . . . . . . . 83 NetworkFileSystem(NFS)protocol . . . . 96 Specifyingdatabaseinformationfora AccessingdatafileswithFTP . . . . . . 96 non-DB2warehousesourceintheData AccessingdatafileswiththeCopyfileusing WarehouseCenter . . . . . . . . . . 84 FTPwarehouseprogram. . . . . . . . 97 DefiningwarehousesourcesforusewithDB2 DefiningafilesourcetotheDataWarehouse RelationalConnect. . . . . . . . . . 85 Center . . . . . . . . . . . . . . 98 Servermappingsandnicknametablesfor warehousesourcesaccessedbyDB2 Chapter6.Settingupaccesstoa RelationalConnect. . . . . . . . . . 86 warehouse . . . . . . . . . . . . 99 DefiningsourcetablesforDB2Relational SettingupaDB2UniversalDatabase Connectwarehousesources. . . . . . . 87 warehouse . . . . . . . . . . . . 99 DefiningprivilegesforDB2Universal Chapter5.Settingupwarehousefile Databasewarehouses . . . . . . . . 99 sources . . . . . . . . . . . . . 89 ConnectingtoDB2UniversalDatabase Warehouseagentsupportforfilesources . . 89 andDB2EnterpriseServerEdition Settingupconnectivityforfilesources warehouses. . . . . . . . . . . 100 (WindowsNT,Windows2000,WindowsXP) . 89 SettingupaDB2foriSerieswarehouse . . 100 Settingupconnectivityforaz/OSorVM DefiningprivilegestoDB2foriSeries filesource(WindowsNT,Windows2000, warehouses. . . . . . . . . . . 100 WindowsXP) . . . . . . . . . . 90 SettingupaDB2Connectgatewaysite Settingupconnectivityforalocalfile (iSeries) . . . . . . . . . . . . 101 source(WindowsNT,Windows2000, ConnectingtoaDB2foriSeries WindowsXP) . . . . . . . . . . 90 warehousewithDB2Connect. . . . . 101 vi DataWarehouseCenterAdminGuide CheckingthecurrentlevelofyouriSeries User-definedprograms . . . . . . . . 132 operatingsystem . . . . . . . . . 102 Columnmapping. . . . . . . . . . 132 Verifyingthatalloftheserversinan Runningwarehousesteps . . . . . . . 134 iSeriessystemarerunning. . . . . . 102 Steppromotionandtesting . . . . . 134 SettingupaccesstoaDB2foriSeries Verifyingtheresultsofastepthatisrun warehousewithiSeriesClientAccess/400. 103 intestmode . . . . . . . . . . 135 SettingupaDB2forz/OSwarehouse . . . 104 Runningstepsthatusetransienttablesas SettingupaDB2UniversalDatabasefor sources . . . . . . . . . . . . 135 z/OSwarehouse . . . . . . . . . 104 RunningastepfromoutsidetheData DefiningprivilegesforaDB2forz/OS WarehouseCenter . . . . . . . . 136 warehouse . . . . . . . . . . . 104 Syntaxforstartingtheexternaltrigger ConnectingtoaDB2forz/OSwarehouse server. . . . . . . . . . . . . 136 database. . . . . . . . . . . . 104 Syntaxforstartingtheexternaltrigger DefiningaDB2forz/OSwarehouseto client . . . . . . . . . . . . . 137 theDataWarehouseCenter . . . . . 105 DataWarehouseCentermessagesforthe CreatingtheDataWarehouseCenterstatus externaltriggerprogram . . . . . . 138 table . . . . . . . . . . . . . . 105 Printingstepinformationtoatextfile 139 SettingupaDB2EnterpriseServerEdition Promotingwarehouseprocesses . . . . . 139 warehouse . . . . . . . . . . . . 106 Aboutpromotingwarehouseprocesses 139 DefiningprivilegestotheDB2Enterprise Managingsteptaskflow . . . . . . . 139 ServerEditiondatabase. . . . . . . 107 Steptaskflow . . . . . . . . . . 140 DefiningtheDB2EnterpriseServer Cascadingprocesses . . . . . . . . . 140 EditiondatabasetotheDataWarehouse Processtaskflow . . . . . . . . . 140 Center . . . . . . . . . . . . 107 Monitoringtheprogressofstepsand DefiningawarehouseforusewithDB2 processes . . . . . . . . . . . . 141 RelationalConnect . . . . . . . . . 108 Monitoringtheprogressofastepor Creatingtargettablesinaremotedatabase 110 processusingtheWorkinProgress MovingatargettablefromaDB2Relational window . . . . . . . . . . . . 141 Connectdatabasetoaremotedatabase. . . 111 Stepandprocesserrormessages. . . . 142 Updatingatableinaremotedatabase . . . 112 Samplingdata . . . . . . . . . . 142 Chapter7.Settingupawarehousetarget 113 Chapter9.Selecting,insertingand Warehousetargets. . . . . . . . . . 113 updatingsourcedatainatargettable . . 143 Supportedwarehousetargets . . . . . . 114 SQLsteps . . . . . . . . . . . . 143 Definingawarehousetarget . . . . . . 115 DefininganSQLstep . . . . . . . . 143 Warehouseprimaryandforeignkeys . . . 116 Incrementalcommit . . . . . . . . . 144 Joiningsourcecolumns . . . . . . . . 145 Chapter8.Definingandrunning Definingajoin. . . . . . . . . . 145 processes . . . . . . . . . . . . 117 Removingajoin . . . . . . . . . 146 Warehouseprocesses. . . . . . . . . 117 Transformingcodes . . . . . . . . 146 Definingthetransformationandmovement Aboutaddingnullstojoins . . . . . 147 ofdatawithintheDataWarehouseCenter . 118 Starjoins. . . . . . . . . . . . 149 Warehousesteps . . . . . . . . . . 118 Filteringdata . . . . . . . . . . 150 Warehousefileprograms . . . . . . . 121 Specifyingsearchconditionswhen DB2warehouseutilities. . . . . . . . 122 filteringdata . . . . . . . . . . 151 DB2OLAPServerprograms . . . . . . 123 Creatingcompositesummarysteps . . . 151 Replicationprograms . . . . . . . . 125 Definingacalculatedcolumn. . . . . 152 WarehouseDeprecatedprograms . . . . 126 Addingacalculatedcolumntoastep . . 152 Warehousetransformers . . . . . . . 127 Addingafunctiontoanexpressioninthe Statisticaltransformers . . . . . . . . 129 ExpressionBuilder . . . . . . . . 153 Contents vii Addingaconstanttoanexpression. . . 154 SettingupreplicationintheData WarehouseCenter . . . . . . . . 177 Chapter10.Loadingandexportingdata 155 ReplicationsourcetablesintheData DataWarehouseCenterloadandexport WarehouseCenter . . . . . . . . 178 utilities . . . . . . . . . . . . . 155 Creatingreplicationcontroltables . . . 178 Exportingdata. . . . . . . . . . . 155 DefiningareplicationstepintheData DefiningvaluesforaDB2UDBexport WarehouseCenter . . . . . . . . 179 utility. . . . . . . . . . . . . 155 Usingareplicationstepinaprocess . . 179 DefiningvaluesfortheDataexportwith Replicationpasswordfiles . . . . . . 180 ODBCtofileutility . . . . . . . . 156 Loadingdata . . . . . . . . . . . 157 Chapter12.Transformingdata. . . . . 183 DefiningvaluesforaDB2Universal UsingtransformersintheDataWarehouse Databaseloadutility. . . . . . . . 157 Center . . . . . . . . . . . . . 183 DefiningaDB2foriSeriesDataLoad Transformingtargettables . . . . . . 183 Insertutility . . . . . . . . . . 158 CleanDatatransformer. . . . . . . 184 DefiningaDB2foriSeriesDataLoad RulestablesforCleanDatatransformers 188 Replaceutility . . . . . . . . . . 160 Keycolumns . . . . . . . . . . 190 ModstringparametersforDB2foriSeries Periodtable. . . . . . . . . . . 191 Loadutilities . . . . . . . . . . 161 InvertDatatransformer. . . . . . . 191 TracefilesfortheDB2foriSeriesLoad Pivotdatatransformer . . . . . . . 192 utilities . . . . . . . . . . . . 162 FormatDatetransformer . . . . . . 193 ViewingtracefilesfortheDB2foriSeries Changingtheformatofadatefield. . . 193 Loadutilities . . . . . . . . . . 163 Specifyingtheinputformatforadate DefiningafileextensiontoClient field . . . . . . . . . . . . . 194 Access/400 . . . . . . . . . . . 164 Specifyingtheoutputformatforadate DefiningaDB2forz/OSLoadutility . . 165 field . . . . . . . . . . . . . 194 CopyingdatabetweenDB2utilities. . . 166 Cleaningnameandaddressdata. . . . . 194 SAPR/3. . . . . . . . . . . . . 167 Cleaningnameandaddressdatawiththe LoadingdataintotheDataWarehouse TrilliumSoftwareSystem . . . . . . 194 CenterfromanSAPR/3system. . . . 167 TrilliumSoftwareSystemcomponents . . 196 DefininganSAPsourceintheData Trilliummetadata. . . . . . . . . 196 WarehouseCenter . . . . . . . . 168 ImportingTrilliummetadata . . . . . 197 DefiningthepropertiesofSAPsource TrilliumBatchSystemJCLfiles . . . . 198 businessobjectsintheDataWarehouse Example:Jobstepthatincludesa Center . . . . . . . . . . . . 169 SYSTERMDDstatement . . . . . . 199 WebSphereSiteAnalyzer . . . . . . . 169 TrilliumBatchSystemuser-defined LoadingdatafromaWebSphereSite program . . . . . . . . . . . . 199 AnalyzerdatabaseintotheData ParametersfortheTrilliumBatchSystem WarehouseCenter . . . . . . . . 169 scriptorJCL . . . . . . . . . . 199 DefiningaWebSphereSiteAnalyzer ErrorhandlingforTrilliumBatchSystem sourceintheDataWarehouseCenter . . 170 programs . . . . . . . . . . . 200 Errorreturncodesfornameandaddress Chapter11.Movingfilesandtables . . . 173 cleansing. . . . . . . . . . . . 201 ManipulatingfilesusingFTPortheSubmit JCLjobstreamwarehouseprogram . . . . 173 Chapter13.Calculatingstatistics. . . . 203 Unabletoaccessaremotefileonasecure DefiningstatisticaltransformersintheData UNIXorUNIXSystemServicessystem . . 175 WarehouseCenter . . . . . . . . . 203 Replication . . . . . . . . . . . . 175 ANOVAtransformer. . . . . . . . . 203 ReplicationintheDataWarehouseCenter 175 CalculateStatisticstransformer . . . . . 204 CalculateSubtotalstransformer . . . . . 205 viii DataWarehouseCenterAdminGuide