 | Level: Introductory Sermsak Sukjirawat (sermsak@us.ibm.com), DB2 Data Warehouse Consulting, IBM Software Services for DB2 Information Management
01 Jul 2004 DB2 UDB Data Warehouse Enterprise Edition (DWEE), Version 8.1.2, is IBM's complete business intelligence platform, bringing together DB2 UDB, DB2 UDB Database Partitioning Feature, DB2 Cube Views, DB2 Intelligent Miner Modeling, Scoring and Visualization, DB2 Office Connect, DB2 Information Integrator Standard Edition, and DB2 Warehouse Manager. To plan and correctly set up all components in DB2 DWEE presents some challenges. This article provides a complete guide for planning and setting up DB2 UDB Data Warehouse Enterprise Edition on AIX.
Get the products and tools used in this article
If you are a developerWorks subscriber, you have a single user license to use
DB2 Universal Database, DB2 Information Integrator, Intelligent Miner, DB2 Cube views, DB2 Warehouse Manager, and other DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products -- including the Eclipse-based WebSphere Studio IDE -- to develop, test, evaluate, and demonstrate your applications. If you are not a subscriber, you can subscribe today.
Introduction
DB2 Data Warehouse Edition (DB2 DWE) provides the essential infrastructure you need for a comprehensive platform for On Demand Business Intelligence. It combines a selected set of IBM business intelligence (BI) products, providing everything you need to deploy the next generation data warehouse. Yet implementing DB2 DWE requires careful planning and knowledge to support many software components. Installing and getting all components up and running can present some challenges. Although DB2 DWE already provides an integrated installation program that helps you install all software components in a single task, you still have to perform many post-installation tasks to set up each product correctly.
In this article I'll give a comprehensive guide for installing and setting up all components in DB2 Data Warehouse Enterprise Edition Version 8.1.2. I'll first give you an overview about each component in DB2 DWE package. Then I'll discuss some pre-installation considerations and the installation process. Toward the end, I'll give some guidelines on how to get started with each product and point out some interesting links you can go to explore each product component further.
Please note that DB2 DWE is available on Linux, Windows, Solaris and AIX. And some of you may already aware of the next release of DB2 UDB codenamed "Stinger" and the next release of DB2 DWE, Version 8.2, which will contain a lot of new features. However, as the basis for this article, I will only focus on the current release of DB2 DWE, Version 8.1.2, on AIX operating system.
This article assumes you already have basic knowledge of DB2 UDB in a partitioning environment.
DB2 Data Warehouse Edition overview
There are two versions of DB2 Data Warehouse Edition: Standard and Enterprise.
DB2 Data Warehouse Standard Edition (DWSE) is designed for data mart infrastructures and includes DB2 Workgroup Unlimited Edition with three other business intelligence components: DB2 Cube Views, DB2 Intelligent Miner (IM) Scoring, Modeling and Visualization, and DB2 Office Connect Web Edition and Analytic Edition.
DB2 Data Warehouse Enterprise Edition (DWEE) includes and extends DB2 Enterprise Server Edition (DB2 UDB ESE) with over 7 other components-- DB2 Data Partitioning Feature (DPF), DB2 Cube Views, DB2 Intelligent Miner Scoring, Modeling and Visualization, DB2 Office Connect Enterprise Web Edition and Analytic Edition, DB2 Warehouse Manager Standard Edition (DB2 WHM), DB2 Query Patroller, and DB2 Information Integrator Standard Edition (limited use license)-- bringing a complete software infrastructure for enterprise-wide data warehouses.
The following figure illustrates how each product in DB2 DWE is positioned in a BI platform.
Figure 1: The DB2 Data Warehouse Edition Platform BI Services
To briefly illustrate how the products function together, consider DB2 UDB as the foundation for your BI platform. Combining DB2 UDB ESE with DB2 Data Partitioning Feature, the data warehouse can support very large database from hundreds gigabytes to terabytes and virtually has no-limit scalability due to DB2 shared-nothing architecture. Extract-transform-load (ETL) process can be managed and automated using DB2 Warehouse Manager. You can also extend ETL processing with DB2 Information Integrator, which provides native connectors for accessing data from Oracle, Sybase, Informix, Microsoft SQL Server and Teradata. (Note that DB2 DWEE license restricts DB2 Information Integrator to be used only in conjunction with DB2 Warehouse Manager.)
DB2 Cube Views improves OLAP performance in DB2 by designing optimized relational objects based on a multidimensional cube model. With DB2 Cube Views, DB2 becomes aware of multidimensional structure and business entities within DB2 star schemas and/or snowflake models. The information is stored in DB2 as OLAP metadata, which includes information about dimensions, hierarchies, attributes, measures, calculated measures, business names and OLAP cubes. Using the OLAP metadata, Cube Views Optimization Advisor can now analyze underlying dimensional data and create the most efficient set of materialized query tables (MQTs) for your OLAP applications. The MQTs generated by Cube Views can significantly improve performance of OLAP queries. Since summary data is precomputed in the MQTs, DB2 optimizer may rewrite a specific query to access an MQT instead of based tables because the optimizer can recognize if result sets can be derived from an MQT with less predicted cost. And since MQTs tend to be much smaller than the base tables, response time of OLAP queries can improve significantly when the queries are rerouted to the MQTs. DB2 Cube Views metadata can also be shared with query tools. For example, DB2 Office Connect, DB2 QMF and Cognos are able to connect directly to DB2 Cube Views metadata through Cube Views API. Other partner tools such as Hyperion, MicroStrategy, Cognos, Business Objects can access to DB2 Cube Views metadata via a bridge.
A light query and reporting tool provided with DB2 DWE is DB2 Office Connect Web Edition and Analytic Edition. The tool enables Microsoft Excel users to build BI applications with DB2 Office Connect Excel Add-in. The Web Edition lets you access DB2 data from Excel spreadsheet without any ODBC or JDBC driver on the client by connecting over the web to a middle-tier Office Connect server which maintains the database connection. Result workbooks can be saved into Office Connect's DB2 repository and can be made available to enterprise users over the web. Additionally, Office Connect Analytic Edition also let users perform OLAP queries-- i.e. drilling up/down, slice & dice-- from their Excel spreadsheet into an OLAP cube defined in DB2 Cube Views.
Data mining and real-time analytic applications such as customer segmentation, customer retention, product affinity analysis and fraud detection are enabled with DB2 Intelligent Miner Modeling, Scoring and Visualization. IM Modeling and Scoring enable data mining algorithms and scoring functions embedded in a DB2 database as stored procedures and SQL extenders. Analysts can use IM Modeling to run various algorithms such as classification, clustering and association against the database to discover new relationships or unknown patterns. A data mining model developed in IM Modeling or any Predictive Modeling Markup Language (PMML) compliant tools such as SAS and SPSS is stored in DB2. PMML models in DB2 can be viewed in a Java applet provided with IM Visualization. Once you have the mining models, you can use IM Scoring to perform either a batch scoring in the data warehouse or a real-time scoring as new records enter the database, such as in a Call Center application. Some data mining models such as fraud detection models may have a short useful life and require to be recalibrated at a frequent interval. With IM Modeling, an SQL job can be scheduled to mine the data and update the mining model without needing highly skilled data miners. This is to produce a model quickly in a production environment, rather than to have a data mining analyst to create a perfect model everyday.
Finally, as you start to have multiple concurrent users accessing the data warehouse whether they are ETL developers or business users, DB2 Query Patroller can become very helpful in managing queries and administering warehouse resources. It can be used to prioritize queries based on different users or user groups and assign incoming queries based on projected cost. Expensive, long-running queries can be held and run later, and users can be notified by email when they complete. Result sets from the queries can be cached in result tables and re-used as customized data marts. DB2 Query Patroller is also a useful tool in analyzing query history, execution time, and number of hits on each table and index either for tuning the database or administering warehouse usage for cost accounting and chargeback applications.
Determine the right configuration
Whether your enterprise data warehouse is a single SMP, a system with multiple LPARs, or a system with multiple servers, every DB2 data warehouses with data partitioning feature contains basic building blocks of hardware/software stack putting together in a shared-nothing design. To determine the best configuration in each building block, we have to look into several components: processors, memory, I/O capacity, storage layout, operating system and software configuration. This process can be complex and is beyond the scope of this article. However, I'd like to point out some key items that should be reviewed before doing the installation.
How large should each DB2 partition be? If we have a 16-way IBM pSeries 690, should we have 16 DB2 partitions, 8 DB2 partitions or 4 DB2 partitions on the server? While the answer depends on several factors, bottom line here is you want to allocate proper resources such that each DB2 partition is equally busy. The following rules of thumb can be applied to most initial installations.
- Number of Processors: Generally ratio of 1-2 CPUs per DB2 partition works well since you can potentially get all CPUs working for each query. With faster processors, such as the IBM pSeries 690, you could allocate as small as 1 CPU per partition.
- Memory: Generally, on a 32-bit implementation, you should allocate about 2 GB of memory per DB2 partition as a starting point. In the 32-bit mode, each DB2 partition can exploit only up to 1.75 GB of shared memory on AIX, 3.35 GB on Solaris, and 3 GB on Windows Advanced Server, which limits amount of buffer pool you can allocate. For 64-bit implementation, memory is not a major boundary; allocating 3-4 GB memory per CPU would be a good start. However, it is important to note that not all components in DB2 DWEE support 64-bit implementation. In fact, DB2 DWE Getting Started Guide (Chapter 3) suggests that you implement DB2 DWEE under a 32-bit operating system. However, it is possible to have a mix of 64-bit DB2 instance and 32-bit DB2 instance in your environment so you can run large database in 64-bit mode. We will discuss about support of 32-bit and 64-bit implementation later in this section.
- Disk and Storage: Plan for total storage capacity of 4 times amount of raw data. It is important to allocate enough disk arms in each partition, at least 6-10 physical disks per CPU. Twelve disks per CPU works well in most storage system. Try to bring I/O throughput to at least 50-125 MB/sec per CPU. Each physical disk must be allocated only to a DB2 partition and not be shared with other partitions.
- Network: Each DB2 partition contains a fast communication facility (FCM) which communicates via TCP/IP. It is recommended that you dedicate a Gigabit Ethernet for every one or two partitions.
Example of DB2 DWEE Configuration
Many new data warehouse start with a single SMP server, e.g. a 16-way pSeries 690 with 16 DB2 partitions. Software configuration for this environment would be rather simple as all software components are installed and run on a single computer. The configuration gets more complex as we move to a massively parallel processing (MPP) environment. Figure 2 shows a basic configuration of DB2 Data Warehouse Enterprise Edition on an MPP system with four computers. Notice that in this configuration, I have a primary server running all DB2 DWEE software components, and three participating partition servers running only DB2 UDB ESE. The primary server is our DB2 instance owning server and contains a catalog partition at database partition 0. The server is also dedicated to be our coordinator node where all database requests are initiated and sent to subagents in other partitions, and all results from the other partitions are consolidated. Consequently, we use this server to run DB2 Warehouse Manager Server and Agent, Query Patroller Server, Cube Views, and federated data access. On the other hand, the participating partition servers do not contains DB2 DWE components except DB2. They are dedicated for large tables, which spread evenly across multiple partitions. More servers will typically be added as your data warehouse grows.
Tip: DB2 Cube Views, DB2 Query Patroller, DB2 Warehouse Manager, DB2 Information Integrator, and DB2 Intelligent Miner Scoring and Modeling are not required to be installed on all partition servers. In practice, we install and configure these products on the server where we want to run jobs or applications associated with each of the products; typically this will be on a dedicated coordinator node. DB2 Query Patroller can be installed and run on any partition server (preferably a server with extra resources, such as a coordinator partition that contains small amount of data).
Figure 2: A Sample Configuration for DB2 Data Warehouse Enterprise Edition
Figure 3 shows a more complex configuration as we add high availability (HA) solution into the system. Server 1 and Server 2 are paired up as a cluster so they can mutually take over each other if a node fails. Server 3 and 4 are also another mutual-takeover HA cluster. Server 1 has all DB2 DWE components installed since it is dedicated to be our coordinator node, federated server, Query Patroller server and Warehouse Manager Server. Server 2 should also have the same software configuration so DB2 Information Integrator, DB2 Query Patroller, DB2 Cube Views, DB2 Intelligent Miner, and Warehouse Manager can still function properly if Server 1 fails.
Figure 3: A Sample High Availability Configuration for DB2 Data Warehouse Enterprise Edition
Tip: Multiple-Server Environment: Depending on business needs, you may install DB2 DWE components on many or all servers. Figure 3 shows an example when HA is implemented. You may install DB2 Query Patroller on one or more computers; each Query Patroller server may be used to manage different databases. DB2 Warehouse Manager Agents may be installed on multiple servers so you can reduce workload on coordinator partition when processing ETL jobs concurrently.
Other Configuration Considerations
- Operating system:Generally, we recommend running the latest level of operating system since it contains the latest fixes. Check system requirements for DB2 UDB Data Warehouse Edition for the supported operating system.
- 32-bit and 64-bit Support: Currently, not all software components in DB2 DWEE provide 64-bit support. On AIX, DB2 Cube Views, DB2 Query Patroller, and DB2 Information Integrator Wrappers for DB2, Informix, Oracle NET8 and Sybase CTLIB support running under 64-bit DB2 instance. However, DB2 Warehouse Manager and DB2 Intellient Miner Modeling, Scoring and Visualization must run in 32-bit mode and under a 32-bit DB2 instance. In other words, if your warehouse database runs under a 64-bit DB2 ESE instance, you can not perform Intelligent Miner modeling and scoring functions on the database. Additionally, you can not use DB2 Warehouse Manager to perform ETL processing on the database directly. A work around to this problem is to create a seperated 32-bit DB2 instance on a seperated LPAR. This instance is configured for DB2 Warehouse Manager Server/Agents and DB2 Intelligent Miner Modeling/Scoring, thus containing two databases: one is DB2 Warehouse Manager Control Database and another is a datamart database created specifically for data mining applications. Data mining tasks are then performed on the 32-bit DB2 instance rather than on the 64-bit DB2 data warehouse. For DB2 Warehouse Manager to work with the 64-bit warehouse database, the 64-bit instance is cataloged to this 32-bit instance as a remote instance. DB2 Warehouse Manager Agent can then be used to manage ETL processing on the 64-bit warehouse as a remote database. (See DB2 Warehouse Manager Support for 64-bit Database technote.)
Due to the limited 64-bit support, DB2 DWE Getting Started Guide (Chapter 3) suggests running DB2 DWE components on a 32-bit operating system. This article will therefore focus on 32-bit implementation.
- Java Environment: DB2 DWE also requires JDK 1.3.1 be installed. If you will install Intelligent Miner Scoring Bean, JRE 1.3.0 should also be installed.
 |
DB2 Data Warehouse Edition installation process
Depending on server configuration, installing DB2 DWE can be done differently. You can either install each software component separately, one at a time, or use DB2 DWE Integrated Installation program which is the quickest and easiest approach. DB2 DWE Integrated Installation program can install all software components, except DB2 Office Connect, in a single task which can save you some time. However, there are still many post-installation tasks you need to do to get all components up and running.
Tip: It is recommended that if you will run DB2 UDB ESE on multiple partitions, you should first install and set up DB2 UDB ESE V8.1.2 on all partition servers, then use DWE Integrated Installation to install FixPak and the remaining DB2 DWE components on the primary server (See Figure 2). This process can also be applied to a non-partitioning environment, such as DB2 Data Warehouse Standard Edition.
DB2 DWE Integrated Installation Process
The following is a recommended procedure for setting up DB2 DWE on multiple-partition environment:
- Pre-installation Assessment. Set up and verify operating system parameters. Install all software prerequisites. Create DB2 users. If running DB2 on multiple servers, set up a network file system (NFS) on DB2 instance owner $HOME path. Download the latest FixPak for each software component.
- Install and set up DB2 UDB ESE V8.1.2 on all partition servers. Make sure DB2 is up and running properly on all partitions. If you already have DB2 installed, you must ensure that DB2 is at least FixPak 2 level. If not, apply DB2 FixPak 2 before you proceed.
- Use DB2 DWE Integrated Installation program to install the remaining software components on the primary server. DWE Installation program will install the following programs in sequence:
DB2 Warehouse Manager (DB2WHM)--> DB2 Integration Integrator --> DB2 Fixpack 2+
--> DB2 Cube Views --> DB2 Query Patroller --> DB2 Intelligent Miner Scoring
--> DB2 Intelligent Miner Modeling --> DB2 Intelligent Miner Visualization
|
- (Optional) Install additional software components on other servers if needed (See Figure 3).
- Apply the latest DB2 FixPak on all partition servers. Apply the latest Intelligent Miner Patches.
- Install and set up DB2 Administration Clients, Cube Views OLAP Center and Query Patroller Center.
- Configure each DB2 DWE components.
- Install IBM Websphere Application Server (or Jakarta Tomcat). Install and set up DB2 Office Connect Web Edition.
DB2 DWE Manual Installation Process
Alternatively, you may choose to install each product component in DB2 DWE separately, one at a time, using installation programs provided with each software components. An advantage of this method is that the installation wizards provided with each product can simplify many manual setups you have to otherwise perform if using DB2 DWE Integrated Installation.
Setting up DB2 DWE on multiple servers usually requires a lot of customization especially when you run DB2 DWE components on two or more servers. By setting up each product one at a time, you can be certain that you have each software component up and running properly before proceeding to the next product installation. In a multiple-server environment, you can install a product on the primary server and use its installation response file to easily install the remaining partition servers.
Figure 4 shows the overall process of installing and setting up each component in DB2 Data Warehouse Enterprise Edition individually.
Figure 4: DB2 Data Warehouse Enterprise Edition 8.1.2 Manual Installation
In this article I'll not discuss DB2 DWE manual installation since we can easily follow the Installation manual of each product as long as the product installation is performed in the above sequence. Our focus will be on the Integrated Installation process since DB2 DWE installation manual does not discuss about the post-installation set up steps in detail.
The next sections describe each step for setting up DB2 DWE in a multiple-server environment. The same process can also be applied to a single-server installation.
Step 1: Pre-installation assessment
AIX System Check
The following lists some AIX settings that effect DB2 performance and are worth looking into.
maxuproc
Maximum number of processes per user (maxuproc) should be set to 4096.
chdev -l sys0 -a maxuproc='4096' |
file size limit
fsize for DB2 instance owner must be set to unlimited (or -1). To change the hard and soft limit of fsize to unlimited, run the following to command (as ROOT):
ulimit -Hf unlimited (for Hard Limit)
ulimit -Sf unlimited (for Soft Limit) |
data and stack limit
For 32-bit implementation, user limits should be set to 245760 KB for data and 16384 KB for stack. Change the settings in /etc/security/limits or use SMIT.
minperm%, maxperm% and maxclient%
By default, AIX has minperm% and maxperm% set to 20 and 80. This means that AIX can use from 20% to 80% of real memory for file system caching. The default settings are too high since DB2 utilizes its own buffer pool for data caching. Consider setting these parameters very low so more memory can become available for other things, such as bufferpools and sortings. The recommended value for minperm% is between 5-10. maxperm% should be between 10-50. maxclient% which is the upper memory limit on client (NFS) file pages should also be altered to a value less than maxperm%. Use vmo command to alter the value of these parameters. For example,
vmo -p -o minperm% =10
vmo -p -o maxperm% =20
vmo -p -o maxclient% =15
|
maxpgahead
maxpgahead is the upper limit for AIX file system prefetching so it affects efficiency when doing large scans and I/O on SMS or DMS file containers. In general, maxpgahead should be set to min(128, 16 * number of disk in striped logical volume).
ioo -p -o maxpgahead= new value |
You should modify maxfree to accommodate the change in maxpgahead so that: maxfree = maxpgahead + minfree
vmo -p -o maxfree= new value |
lvm_bufcnt
If you plan to use striped raw logical volumes in DMS containers, consider increasing size of the LVM buffer (lvm_bufcnt) from default to 16.
memory_affinity
IBM POWER-based SMP system contains several multichip modules (MCMs), each containing multiple processors. Although any processor can access all of the memory in the system, a processor has faster access when addressing memory that is attached to its own MCM. By enabling the memory affinity, AIX attempts to satisfy a page fault using memory attached to the MCM containing the processor that caused the page fault. This can improve DB2 performance with multiple logical partitions on an SMP system.
vmo -p -o memory_affinity=1 |
network file system (NFS)
If you run DB2 on multiple servers or LPARs, NFS must be running on each computer. To verify that NFS is running: lssrc -g nfs
We also recommend that you run 10 biod processes on every computer and set number of network file system daemons (nfsd) on the primary server as:
# of nfsd = min(120, # of computer * 10)
For example, if you have 4 servers, you would use 10 biods on all servers and 40 nfsds on the primary server. To update new values of nfsd and/or biod, run:
smit nfs --> Network File System --> Configure NFS on This System
--> Change # of nfsd, biod, lockd Daemons
|
Software Prerequisite
Verify that JDK 1.3.1 which is required by DB2 Warehouse Manager Transformers has been installed on all servers. If not, install JDK 1.3.1 supplied in DB2 UDB ESE CD-ROM /cdrom/db2/aix/Java-1.3. JRE1.3.0 should also exist on the server as a prerequisite for DB2 Intelligent Miner Scoring Bean.
UNICODE support
Ensure that Unicode support is available on your system. This is required by DB2 Warehouse Manager.
lslpp -al | grep -i iconv |
bos.iconv.ucs.com and bos.iconv.ucs.pc should be listed.
DB2 users
There are at least three users required to run DB2 on UNIX: DB2 instance owner, DB2 Administration Server user, and DB2 Fenced user. In a multiple-server environment, $HOME path for DB2 instance owner is created on the primary server and shared to all participating servers.
- Create DB2 instance owner home file system, e.g.
/db2home, on the primary server, where you decided to locate DB2 instances. Allocate about 500 MB. Mount automatically at system restart.
- (Optional) If you run DB2 UDB DPF on MPP system, NFS export DB2 home file system from the primary server and NFS mount the home file system on each participating server. Use soft mounting file system option.
- Create required users and groups on all servers. Make sure UIDs and GIDs be identical on all servers. Assume we create db2inst1 as DB2 Instance owner, db2fence1 as DB2 Fenced user, and db2das1 as DB2 Administration Server user. Execute the following commands to create the users.
mkgroup id=999 db2iadm1
mkgroup id=998 db2fadm1
mkgroup id=997 dasadm1
mkuser id=10001 pgrp=db2iadm1 groups=db2iadm1 home=/db2home/db2inst1
core=-1 data=491519 stack=32767 rss=-1 fsize=-1 db2inst1
|
- Run
java -version with all DB2 users. Make sure it is 1.3.1. If not, add /usr/java131/bin in $PATH variable by editing .profile file of each user.
Copy contents of DB2 DWE peoduct CD-ROMs to the server
Copy the contents of DB2 DWE product CD-ROMs and DB2 FixPak into an network file system (NFS) that are exported to all servers.
- Create an NFS file system with about 3.5 GB space to store product images and DB2 FixPak. Here we assume we created an NFS as
/DB2DWE.
- Download DB2 FixPak2+ and the latest DB2 FixPak. At the time of this writing, DB2 FixPak 5. Extract (untar) DB2 FixPak archived image to
/DB2DWE/DB2FP2plus and /DB2DWE/DB2FP5.
- Download the latest Intelligent Miner Modeling and Scoring Patch and copy into
/DB2DWE/IM_Fix. At the time of this writing, IP22677 and IP22683.
- Create the following path. Directory should contain no space.
/DB2DWE/IntegratedInstall
/DB2DWE/DB2ESE
/DB2DWE/DB2II
/DB2DWE/DB2QP
/DB2DWE/DB2WHM
/DB2DWE/DB2CV
/DB2DWE/IMINER
/DB2DWE/temp |
- Copy content of each product component into the directories you created. If we downloaded DB2 DWE image of each products as .tar files, extract all .tar files before you proceed.
Step 2: Install and set up DB2 UDB DPF
During this step, we will set up DB2 UDB ESE DPF across all partition servers. We start by installing DB2 UDB Enterprise Server Edition V8.1.2 using DB2 Setup Wizard on the primary server (See Figure 2). You need to use an X-Windows to perform the installation.
- On the primary server, extract DB2 UDB ESE tar.Z file from the directory on the /DB2DWE file system where you copied the content of DB2 UDB ESE product CD-ROM. From ROOT user, run
./db2setup from to start DB2 Setup Wizard.
- Follow the installation wizard as follow:
- From welcome page, click Install Products. Then select DB2 UDB Enterprise Server Edition.
- Installation type: Choose Custom installation.
- Check both Install DB2 UDB Enterprise Server Edition on this computer and Save your settings in a response file.
- Features: Select all components unless you do not wish to install certain components. Be sure all components under Business Intelligence are selected. Also select Warehouse Sample Database Source and Informix data source support. They are not installed by default.
Figure 5: DB2 Setup wizard- Features to install
- Languages: Select a language of your choice.
- DAS User: Select Existing user. Enter
db2das1 or your DAS user.
- Instance setup: Select Create a DB2 instance - 32 bit.
- Instance use: Select Partitioned instance.
- Instance owing: Select Existing user. Enter
db2inst1 or your instance owner user.
- Fenced user: Select Existing user. Enter
db2fenc1 or your fenced user.
- Instance TCP/IP Communication: Select Configure and use the default
db2c_db2inst1 with a port number. (Default is 50000.) The port number can be changed if needed.
- Instance properties: Enter maximum logical nodes (default is 4) on the server. E.g. enter 16 if you run 16 partitions on this server. Select an appropriate Authentication type for the instance (e.g.
Server). And check Autostart the instance. Uncheck Autostart, if you run HACMP.
- Prepare metadata: Select Do not prepare any metadata at this time. We will defer this setup until we have all DB2 partitions are up and running. Otherwise, you will need to redistribute the database when you add more partitions during the next steps.
- In the next two screens, enter your Administration Contact list.
- Informix data source, enter your Informix client installation path and Informix Server. Or you may defer Informix data source set up at this time.
- Summary. Enter locations where response files will be created. I will assume we save response file for additional database partition servers into
/db2home/db2part.rsp. Click Finish to start the installation.
- Once the installation is finished, review log file from
/tmp/db2setup.log.
- Stop DB2 and DB2 Administration Server before proceeding to the next step.
- (Optional) If you will run DB2 ESE DPF on 2 or more servers, install DB2 UDB ESE on the remaining participating servers using response file produced from the previous step.
./db2setup -r /db2home/db2part.rsp |
- Verify /etc/services file on all servers. Make sure Fast Communication Manager (FCM) port range correspond to the number of partition on each server. For example, if you runs db2inst1 instance on with 4 partitions on each server, you should have:
DB2_db2inst1 60000/tcp
DB2_db2inst1_END 60003/tcp
db2c_db2inst1 50000/tcp |
- Edit $INSTHOME/sqllib/db2nodes.cfg. Add additional partitions. The column format in db2nodes.cfg should be:
dbpartitionnum hostname FCM_port_offset netname
The following example shows db2nodes.cfg file for a system with total of 8 DB2 partitions on host1 and host2. Each server contains 4 partitions and 2 Gbit Ethernet interfaces.
0 host1 0 netname1_a
1 host1 1 netname1_a
2 host1 2 netname1_b
3 host1 3 netname1_b
4 host2 0 netname2_a
5 host2 1 netname2_a
6 host2 2 netname2_b
7 host2 3 netname2_b |
- Update database default path so it is not on the instance owner path, which is a network file system.
db2 update dbm cfg using dftdbpath defaultpath |
|
Step 3: DB2 DWE Integrated Installation
Once we have DB2 ESE DPF V8.1.2 up and running on all servers, we can use DB2 DWE Integrated Installation program to install the remaining products on the primary server and all coordinator nodes.
On the primary server or the coordinator nodes:
- Stop all DB2 processes and unload shared libraries from memory before starting the installation.
su - db2inst1
db2stop
db2licd -end # run on each physical server
su - db2das1
db2admin stop
su - root
/usr/sbin/slibclean |
- Run
./aixsetup from /DB2DWE/IntegratedInstall to start DB2 DWE integrated installation program.
- Select a language to be used. Then click OK.
- Select the product you are licensed: DB2 Data Warehouse Enterprise Edition
Figure 6: DB2 Data Warehouse Edition Selection Window
- Select Install Type: Server.
- In the next two screens, accept all license agreements.
- The Integrated Installation program will then show the list of products to be installed.
Figure 7: DB2 Data Warehouse Edition components to be installed
- The next 9 screens will let you enter location of the install image of each product, DB2 FixPak 2+ and a path for temporary files.
Note that the path of each product is not always
/DB2DWE/productname where you copied and extracted the installation image, but may be deeper in the directory. Specifying incorrect path will result an incomplete installation. The trick here is to use the location where either .toc or readme.txt file exists. For example, the location I used for DB2 Query Patroller is /DB2DWE/DB2QP/DB2_V81_QP_AIX5_3264_NLV, while the location I used for DB2 Cube Views is /DB2DWE/DB2CV.
Figure 8: Location where Installer can find DB2 Query Patroller Image
- After the installation is completed you should see the following screen. Detail installation log file can also be seen in db2setup.log, db2setup.err and db2setup.his from /tmp.
Figure 9: Installation Summary Window
- Stop all DB2 instances and DB2 Administrative Server before performing additional installation.
su - db2inst1
db2stop
db2licd -end # run at each physical server
su - db2das1
db2admin stop
su - root
/usr/sbin/slibclean
|
|
Step 4:(Optional) Install DWE components on additional servers
This step applies when you implement High Availability or plan to have multiple coordinator nodes in the system. In this circumstance, you also install DB2 DWE components on many servers. (See Figure 3 as an example of High Availability (HA) configuration). HA couple for the primary instance owning server should have all products installed. All coordinator nodes should have all products installed as well.
You may either use DB2 DWE Integrated Installation or install each product individually on additional servers. If you choose individual product installation, you should apply DB2 FixPak 2+ before installing DB2 Cube Views and/or Query Patroller. Then, run ./db2_install program from the product image to install individual product.
Step 5: Apply latest FixPak
At this point, DB2 is running at FixPak 2+ level. We want to bring the system up to the latest FixPak level. To apply DB2 FixPak, follow the steps below:
- Stop all DB2 instances and DB2 Administration Server.
su - root
/usr/opt/db2_08_01/bin/db2vwsvr stop # run at each server where DB2 WHM is running
su - instanceowner
db2stop
db2licd -end # run at each physical server
su - DASowner
db2admin stop # run at each physical server
su - root
/usr/sbin/slibclean # run at each physical server
|
- Run installFixPak from DB2 FixPak install image location using root.
- Repeat step 2 on all partition servers.
- Update DB2 instance on the instance owning server and DB2 Administration Server on all servers to the code level as the FixPak using root.
/usr/opt/db2_08_01/instance/db2iupdt instancename
/usr/opt/db2_08_01/instance/dasupdt dasname
|
- Issue db2start and db2stop. Verify that DB2 is started and stopped without errors from
$INSTHOME/sqllib/db2dump/db2diag.log.
Also apply Intelligent Miner patches. Follow the instruction provided with the patch that you downloaded.
Step 6: Install DB2 client
You can use DB2 DWE Integrated Installation to install DB2 DWE client. Use CLIENT Install Type option. Or you may install each product separately. For Windows client, you need to download DB2 FixPak for DB2 UDB, DB2 Cube Views and DB2 Query Patroller separately. You need to apply DB2 FixPak 2+ or higher to DB2 Client before installing DB2 Cube Views and DB2 Query Patroller Clients. The latest FixPak then needs to be applied again after the installation. Here is the summary sequence for client installation:
DB2 Administration Client --> DB2 UDB FixPak 2+ or later --> DB2 Cube Views OLAP Center --> DB2 Query Patroller Client --> DB2 FixPak 5 or later (same FixPak level as server)
Intelligent Miner Visualization does not require DB2 client as prerequisite. However, most Intelligent Miner users will have access to DB2 data warehouse; typically only DB2 Runtime Client is needed.
Step 7: Configure DB2 DWE components
Up to this point, we only installed product image on the server and clients. We created one DB2 instance on the server. But we have not created any databases and have not done any configuration on each product component.
During the next steps we will create and configure DB2 databases for:
- DB2 Warehouse Manager Control Database. In this article, it will be created as
DWCTRLDB.
- Information Catalog Manager Database. In this article, it will be created as
IWCATDB.
- Office Connect Report Repository. In this article, it will be created as
OFFCCONN.
- Enterprise data warehouse database. In this article, it will be created as
EDWDB.
We then configure each of the components in DB2 DWE.
Creating databases
When creating a database, it is important to implicitly define the location of system and temporary tablespace. After the database is created, you can define additional system temporary tablespaces, user tablespaces and buffer pools. Here are some tips when setting up database and tablespaces.
- Use SMS tablespace for catalog and temporary tablespace.
- Using DMS raw for user tablespace can increase performance by 10% or better compared to SMS or DMS file tablespace.
- Never put more than one container of the same tablespace on the same physical disk. However, one physical disk may contain multiple containers for different tablespaces, but you should verify that a query will not require to access the two tablespaces from the same disk at the same time. See Figure 10. In addition, ensure that each physical disk is only used by one DB2 partition and not be shared with other DB2 partitions.
Figure 10: Tablespace container layout on a database partition
- Ensure that tablespace container names are unique across all database partitions even if they are on different servers. Using database partition expression $N in tablespace definition can simplify the CREATE TABLESPACE statement. For example, specifying container name as
'edwdp $N ts1cont1', DB2 creates container 'edwdp0ts1cont1' on database partition 0, 'edwdp1ts1cont1' on database partition 1, 'edwdp2ts1cont1' on database partition 2 and so on.
- Since data warehouse applications typically access large numbers of consecutive rows at a time, using larger page size, e.g. 16-K page or 32-K page, is usually better because it can reduce number of I/O requests. However, having page size larger than row size * 255 will waste disk space and buffer pool, especially in a large table, since one page can only contain up to 255 records.
- Create fewest number of system temporary tablespaces. Page size of temporary tablespace should be equal to the page size used in the majority of your user table spaces and should be large enough to hold the largest row in your queries. Usually, you will have a temporary tablespace with the same page size as your largest and frequently used table.
- Extend size of a tablespace should be the same as stripe width or multiple of strip size of the RAID device. Set prefetch size to:
min (512, extend size * # of containers in tablespace in the database partition) |
- Dedicate a separated file system for logging for different database partition.
- Allocate buffer pools for every page size you defined for tablespace. In a new system, a good starting point is to allocate 75% of real memory for DB2 bufferpool and sorting and 25% for the operating systems and other DB2 memory areas. Within the 75%, allocate 50% for buffer pool(s), and the other 50% for sheapthres. Then make your sortheap equal to:
sortheap = sheapthres / (# of complex concurrent queries * max # of concurrent sorts
and hash joins in your average query) |
Note that a good starting number for maximum number of concurrent sorts and hash joins would be between 5 or 6.
- You can use DB2 Configuration Advisor to suggest initial parameter settings for your environment. In most case, the Configuration Advisor should bring substantial performance improvement. The input you specify is per partition. So if you run 4 DB2 partitions on a server, you want to specify target memory of 25% or less since each partition consumes about 25% of the server resources.
Figure 11: Configuration Advisor
- If possible, allocate one database partition to hold just catalogs and small tables. This frees up some resources on that partition so it can be used as a coordinator partition or to run DB2 Warehouse Manager and Query Patroller server.
- Changes in some DB2 registry variables may improve performance. Note that in multiple-server environment, you should run
db2set -all on every server to ensure same DB2 registries are applied to all servers.
- DB2_PARALLEL_IO: Use DB2_PARALLEL_IO if you have small number of containers in each table space and each container span across multiple RAID disks. This is to enable parallel I/O within a single container; otherwise, DB2 will use one I/O server per each container.
- DB2_FORCE_FCM_BP: By enabling this registry to YES, communication between FCM daemons of different partitions on the same physical server is done through shared memory instead of through UNIX sockets. This enables faster communication between DB2 partitions at the cost of reduced size of shared memory segment available for other uses such as the database buffer pools.
db2set DB2_FORCE_FCM_BP=YES |
The following is a sample SQL used to create our EDW database:
create database EDW on /db2/databases
catalog tablespace managed by system using ('/EDW/catfs0', '/EDW/catfs1')
extendsize 32 prefetchsize 64
temporary tablespace managed by system using ('/EDW/tempdpt $N fs0', '/EDW/tempdpt $N fs1')
extendsize 32 prefetchsize 64
|
DB2 Warehouse Manager Post-installation Setup
- Ensure that you have environment variable EXTSHM set to ON. This is required before running Warehouse Manager and Query Patroller.
Log in as db2inst1. Stop DB2 instance. Edit
$INSTHOME/sqllib/db2profile. Add the following entries:
Update DB2 registry by running db2set on every server.
- From an X-Window, run
db2wcdbm to create and initialize DB2 Warehouse Manager Control Database. Enter the following information and click OK. The program will create and populate Warehouse Manager Control tables, which will reside in a single-partition SMS tablespace on database partition number 0.
New control database: DWCTRLDB
Schema: IWH
User ID: db2inst1
Password: xxxxx
- To start and stop DB2 Warehouse Manager, log in as root, run:
/usr/opt/db2_08_01/bin/db2vwsvr stop
/usr/opt/db2_08_01/bin/db2vwsvr start
|
- (Optional) By default, Warehouse Manager Agent uses DB2 CLI to access data sources and target databases. With DB2 Information Integrator, you can use Warehouse Manager Agent to access to Oracle, Sybase, Microsoft SQL Server and Teradata through DB2 CLI.
However, if you installed Warehouse Manager Agent on multiple servers, you may wish to configure the Agent to use DB2 CLI on one server and ODBC on another. By configuring the Agent to use ODBC, you can have an Agent to perform SQL SELECT directly to flat files with InterSolv ODBC Driver. To configure ODBC, you have to create odbc.ini and ODBCINI variable. A Sample is provided in
/usr/opt/db2_08_01/odbc/odbc.ini.intersolv. Run the following command with root on the server where you want to switch the Agent to use ODBC instead. /usr/opt/db2_08_01/bin/IWH.agent.db.interface odbc |
To switch the Agent to use DB2 CLI, run the following./usr/opt/db2_08_01/bin/IWH.agent.db.interface db2cli |
- (Optional) Edit
$INSTHOME/sqllib/bin/IWH.environment file for customize settings.
- To verify if DB2 Warehouse Manager Server and Agents starts successfully, you can check log files in
/var/IWH (the default location). You may use "db2 list applications" to verify that Warehouse Manager Server is up and running.
$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Agents
-------------------------------------------------------------------------------
DB2INST1 iwh2serv 53 *N0.db2inst1.043635040849 DWCTRLDB 4
DB2INST1 iwh2log 52 *N0.db2inst1.094875040849 DWCTRLDB 1
|
- Catalog DWCTRLDB database on DB2 Administration Client. DB2 Warehouse Manager is now ready to be used. If you are new to DB2 Warehouse Manager, DB2 provides a tutorial which can be seen in:
Start--> Programs--> IBM DB2--> Set-up Tools--> First Steps--> Work with Tutorials
DB2 Information Catalog Manager Post-installation Setup
- Create information catalog database. Assume we store information catalog as IWCATDB database. All information catalog tables should be stored in a single-partition tablespace. Use script similar to the following to create an information catalog database.
create database IWCATDB on /db2/databases
catalog tablespace managed by system using ('/IWCATDB/catfs0') extendsize 32 prefetchsize 32
temporary tablespace managed by system using ('/IWCATDB/tempdp $N fs0')
extendsize 32 prefetchsize 32
create database partition group PG0 on dbpartitionnum(0)
create regular tablespace ICATSPACE in database partition group PG0
managed by system using ('/IWCATDB/tsdp0fs0')
|
- From X-Windows, with DB2 instance owner user run
$INSTHOME/sqllib/bin/db2iccwz. Select the first option, Prepare an information catalog. Enter the following:
Database Name: Here, we will use IWCATDB with the ICATSPACE tablespace
Database Schema: Enter ICM, which is the default for Information Catalog Manager.
Figure 12: Information Catalog Set up Wizard
- In the Options page. Enter the default end-user group, e.g.
db2icgrp, and default power user group, e.g. db2iadm1, for Information Catalog. This group should have authority to access the database. Enter the single partition table space we created earlier as ICATSPACE. Then click Finish.
- To use Information Catalog from Windows Client, click Information Catalog Center from IBM DB2.
- If you are new to DB2 Information Catalog, DB2 provides a tutorial which can be seen in
Start--> Programs--> IBM DB2--> Set-up Tools--> First Steps--> Work with Tutorials
DB2 Query Patroller Post-installation Setup
- Ensure that the environment variable
EXTSHM is set to ON and that the DB2 registry variable DB2ENVLIST includes EXTSHM.
- Select a database you want Query Patroller to intercept. As an example, we will use
EDWDB.
- Use qpsetup to create Query Patroller control tables and register a target tablespace to store query result tables. If you use the program to create table spaces, you will not be able to specify more than one table space container per partition, and the table space will be created using the default buffer pool, IBMDEFAULTBP, which has 4K page size. Therefore, you may want to create table spaces for Query Patroller using CREATE TABLESPACE if you want to have a different page size and more than one containers. Then, use qpsetup with the new table spaces you created.
QPCONTROL table space should be created on a single database partition groups on the same partition where you run Query Patroller Server. Usually, this is on the coordinator partition.QPRESULT table space can span more than one partition and can have a different page size and buffer pool.
Create database partition group QPRESULTGROUP on ALL dbpartitionnums;
Create tablespace QPRESULT in database partition group QPRESULTGROUP
managed by system using ('/QPRESULT/qpresultdp $N'); |
- From a telnet session, run qpsetup program with
db2inst user to create Query Patroller tables.
qpsetup USER db2inst1 password passwd DB DBNAME control_tablespace QPCONTROL
result_tablespace QPRESULT |
- Enable
dyn_query_mgmt configuration parameter on Query Patroller managed databases on all partitions.
db2 deactivate database EDWDB
db2_all db2 update db cfg for EDWDB using DYN_QUERY_MGMT enable
db2 activate database EDWDB
|
- Start Query Patroller from the partition where you have selected to run Query Patroller Server:
- To verify Query Patroller set up, open Query Patroller Center from Windows Client.
Connect to EDWDB--> Click User Administration and Submitters--> Right click PUBLIC, then Properties--> From Resources tab: change minimum cost to manage from 15000 to a very lower number such as 10--> OK
Test if you can manage a query:
- Open an ODBC query tool, such as Microsoft Access. Try issuing a query to the server. For example,
SELECT count(*) from syscat.tables, syscat.tables, syscat.tables, syscat.tables
- From Query Patroller Center, click Monitoring --> Managed Queries
- You should see that there is a query running. You can click on the query and see its properties such as SQL statement, query cost, etc.
- Let it run or you can try to cancel the query by right clicking Cancel.
Figure 13: Query Patroller Center
- Change minimum cost to manage back to default value 15000 or an appropriate value in your environment.
- To stop Query Patroller, run qpstop dbname.
- To get start with using Query Patroller, please refer to DB2 documentation for sample scenarios that Query Patroller can be applied.
DB2 Cube Views Post-installation Setup
Here we assume we will use Cube Views with our EDWDB database, which contains snowflake or star-schema tables of our enterprise data warehouse.
- Create DB2 Cube Views stored procedures and metadata catalog tables by running:
db2 connect to EDWDB
db2 -tvf $INSTHOME/sqllib/misc/db2mdapi.sql > $HOME/db2mdapi.out |
Note: The script creates database partition group DB2INFOGROUP on partition 0 and SMS table space, DB2INFOSPACE, in DB2INFOGROUP. You may modify the script to specify appropriate table space containers for DB2INFOSPACE.
- Optional) DB2 Cube Views provides a sample database, MDSAMPLE. To create MDSAMPLE. Run the following.
From the server:
export DB2NODE=0
db2 create db mdsample
db2 connect to mdsample
db2_all db2 update db cfg for MDSAMPLE using DFT_REFRESH_AGE ANY
db2 -tvf $INSTHOME/sqllib/misc/db2mdapi.sql
cd $INSTHOME/sqllib/samples/olap/mdsample
db2 -tvf MDSampleTables.sql
db2 terminate
db2 deactivate db mdsample |
From DB2 Client:
Catalog MDSAMPLE database from DB2 Administration Client --> Open OLAP Center --> Connect to MDSAMPLE database --> Click OLAP Center --> Import --> Locate MDSampleMetadata.xml in x:\sqllib\samples\olap\xml\input --> Click Next --> Finish
Create MQTs using Cube Views Optimization Advisor.
Right click SalesModel --> Optimization Advisor --> Next --> Next --> Next --> Save create summary tables SQL script --> Run the script from DB2 command line to create the MQT
Note that you may need to modify the MQT script to specify proper partition key. As the MQT is created, use Visual Explain to see if DB2 reroutes the following sample query to MQT:
SELECT T1.State, SUM(T2.sales) as Sales
from MDSAMPLE.SALESFACT as T2, MDSAMPLE.MARKET as T1
where T1.STATEID = T2.STATEID
group by T1.State order by Sum(T2.sales) DESC |
DB2 optimizer will reroute the above query to MQT. Cost of the query is much smaller than the original query without presence of an MQT. The following shows the rewritten SQL text for the query above.
SELECT Q3.$C1 AS "STATE", Q3.$C0 AS "SALES"
FROM (SELECT SUM(Q2.$C1), Q2.$C0
FROM (SELECT Q1.STATE, Q1.SALES FROM DB2INFO.MQT0000000001T01 AS Q1)
AS Q2
GROUP BY Q2.$C0) AS Q3 ORDER BY Q3.$C0 DESC |
- For additional information on how to build cube models with DB2 Cube Views, please refer to the following DB2 Cube Views publications.
DB2 Information Integrator Post-installation Setup
In this section, we will configure DB2 Information Integrator to access Oracle 8. Same process can be applied for Informix, Microsoft SQL, Sybase and Teradata data sources.
- Stop DB2 instance.
- Install client software for each data sources. See the DB2 Information Integrator software requirement and 32-bit and 64-bit consideration for appropriate client software for each data source.
- Add DB2 Information Integrator registry on all coordinator nodes.
db2set DB2_DJ_INI=$HOME/sqllib/cfg/db2dj.ini |
- Log in as DB2 instance owner. Edit
$INSTHOME/sqllib/cfg/db2dj.ini. Add environment variables for each data source you have. See valid data source variables for db2dj.ini.
For example, db2dj.ini for Oracle data source is as follow:
ORACLE_HOME=/OraHome/OraHome1
ORA_NLS33=/OraHome/OraHome1/ocommon/nls/admin/data |
- Log in as
root. Run djxlink script to create wrapper libraries for every data source. djxlink script will run all of the following djxlink files in /usr/opt/db2_08_01/bin.
|
Data Sources
|
Link Scipt Name
|
Informix
Microsoft SQL Server
Oracle
Sybase
Teradata
|
djxlinkInformix
djxlinkMssql
djxlinkOracle
djxSybase
djxlinkTerdata
|
In our example, since we only have Oracle data source, djxlinkOracle will run successfully and will create a library file libdb2net8F.a in /usr/opt/db2_08_01/lib. However, djxlinkInformix, djxlinkMssql, djxlinkSybase and djxlinkTeradata will fail, which is what we expect.
- Ensure DB2 users have permissions to read and execute the wrapper library. The proper permissions are: rwx r-x r-x. To permit other users to read and execute the library, use: chmod o+rx <filename>
- From DB2 instance owner, set FEDERATED parameter in database manager configuration to YES.
update database manager configuration using FEDERATED YES |
- Start DB2 instance.
- Create a Wrapper.
Open Control Center --> Select your database, e.g. EDWDB --> Federated Database Objects --> Create wrapper --> Select a data source --> Enter a unique wrapper name --> Use default setting: the wrapper will run in unfenced mode
Figure 14: Create Wrapper
CREATE WRAPPER "OraNet8" LIBRARY 'libdb2net8.a' |
- Create a Server that will use this wrapper.
Click Servers --> Create --> Input a server name --> Select type Oracle Version 8 --> Update options in Settings panel, e.g. NODE, PASSWORD, COLLATING SEQUENCE, CPU_RATIO, etc.
Figure 15: Create Server
CREATE SERVER ORA_SAM TYPE ORACLE VERSION '8' WRAPPER "ORANET8"
OPTIONS (ADD NODE 'OraSID', PASSWORD 'Y') |
- Create user mapping.
User Mappings --> Create --> Select one or more DB2 users or groups --> In setting tab, enter data source userID and password, where the DB2 user will map to
CREATE USER MAPPING FOR "DB2INST1" SERVER "ORA_SAM" OPTIONS
(ADD REMOTE_AUTHID 'orauser', ADD REMOTE_PASSWORD 'Orapassword')
|
- Test DB2 connection to Oracle database. Issue a SQL directly to Oracle table. Notice, we use actual table name in Oracle.
connect to EDWDB user db2inst1 using password
set passthru ora_sam
select * from sermsak.test1
set passthru reset
|
- Create nickname from DB2 command or Control Center. Ensure that data source table has updated statistics. If not, update the statistics on the data source before creating DB2 nickname.
CREATE NICKNAME DB2INST1.TEST1 FOR ORA_SAM.ORAUSER.TEST1 |
- Test a query to Oracle table using DB2 nickname:
SELECT * FROM db2inst1.test1
- Repeat steps 11, 13 and 14 for additional user mappings and nicknames.
DB2 Intelligent Miner Modeling / Scoring / Visualization Set up
This session describes the configuration process for DB2 Intelligent Miner Scoring, Modeling and Visualization.
- Log on as DB2 instance owner to a coordinator partition. Create symbolic links for mining functions from
/usr/lpp/IMinerX/lib to $INSTHOME/sqllib/function.
cd /usr/lpp/IMinerX/bin
$ ./idminstfunc |
- Enable DB2 instance and database for mining functions. As an example, we will use our EDWDB.
db2 update database manager configuration using UDF_MEM_SZ 60000
# Recycle DB2 instance
db2stop
db2start
db2_all db2 update db cfg for EDWDB using APP_CTL_HEAP_SZ 10000
db2_all db2 update db cfg for EDWDB using APPLHEAPSZ 1024
db2_all db2 update db cfg for EDWDB using LOGFILSZ 2000
# Enable the database for Intelligent Miner UDF, UDT and UDM by running
/usr/lpp/IMinerX/bin/idmenabledb EDWDB tables
|
- Verify Intelligent Miner set up on the database.
/usr/lpp/IMinerX/bin/idmcheckdb EDWDB
The database "EDWDB" is enabled for IM Modeling and IM Scoring in "fenced" mode
|
Verify IM Modeling by running. The following SQL should not return any errors.
db2 connect to EDWDB
db2 "values(IDMMX.DM_MiningData()..DM_defMiningData('TESTTABLE'))"
|
Verify IM Scoring by running by issuing the following SQL. You should receive 1 record.
db2 "values(IDMMX.DM_applData('Test',4)) "
|
- (Optional) Create Intelligent Miner Modeling Sample. Run DB2 scripts as described in
/usr/lpp/IMinerX/samples/ModelingDB2/readme.txt. Additional sample is also described in Chapter 6: Sample Applications in DB2 IM Modeling Administration and Programming (SH12-6736-00) from page 75-90.
- (Optional) Create Intelligent Miner Scoring Sample. Run DB2 scripts as described in
/usr/lpp/IMinerX/samples/ScoringDB2/readme.txt. Additional sample is also described in Chapter 4: Getting Started in DB2 IM Modeling Administration and Programming (SH12-6745-00) from page 23-32.
- To set up DB2 Intelligent Miner Visualization from Windows Client, install DB2 IM Visualization with DB2 V8 client at the same DB2 FixPak level as the server. After installation, copy the following 6 files from your
C:\Program Files\IBM\SQLLIB\java to C:\Program Files\IBM\IMVisualization\lib. These files should be the same as ones on DB2 server. If you apply later DB2 FixPak, the files may need to be replaced.
Common.jar
db2fs.jar
db2jcc.jar
db2java.zip
db2jcc_license_cisuz.jar
db2jcc_license_cu.jar
|
- Use Intelligent Miner to browse mining models. This step assumes you have created sample applications from Step 4 and 5.
Start --> Programs --> IBM Intelligent Miner Visualization
Before you can view a PMML model that is stored in DB2, you must specify a profile for the desired model.
Select Database tab --> New --> Enter Profile name --> Start --> Use com.ibm.db2.jcc.DB2Driver as JDBC Driver --> Enter database URL and user ID --> Click Retrieve Database Information
Figure 15: Create Mining Profile
When finished, click OK.
Click Connect to show the list of existing models. Then click Open to browse the model. Figure 16 shows a Cluster Model from the tutorial.
Figure 17: Cluster Model
|
Step 8: Set up DB2 Office Connect for the Web
DB2 Office Connect Web Edition consists of client component which is the Office Connect Microsoft Excel Add-in and Web server component which is the Office Connect Servlet. Office Connect accesses to DB2 database through JDBC. When a user creates a report in the client during development mode, it can be saved into Office Connect's DB2 repository and can be shared to other users. Other users can view the shared reports by connecting to the Office Connect Servlet through the Office Connect Web Edition plug-in in Microsoft Excel.
In this article, I will illustrate the installation process of DB2 Office Connect Web Edition with DB2 UDB and IBM Websphere Application Server.
- Create a DB2 database for Office Connect Repository. Configure DB2 client connectivity from Windows to the database. Here, we will use
OFFCONN database.
- Install JDK 1.3 or higher. If you use JDK 1.3.1 installed by default with DB2 Client installation, verify that
DB2Path\SQLLIB\java\jdk\bin and DB2 JDBC drivers, such as db2java.zip, are in %CLASSPATH% variable.
- Install IBM Websphere Application Server Version 4.0 or above on Windows NT or AIX. Use the following link for Websphere Application Server Installation Tip.
- Install IBM Office Connect V4.0 from the product CD-ROM on a Windows client. After you reboot the machine and start Excel, you should see Office Connect menu in Excel menu bar.
- If the menu does not appear, choose Tools --> Add-ins --> Browse and add the following:
TMSExceladdin.xla
WebDeployment\OfcLight.xla
|
- Create Office Connect repository. From Excel Menu, Select IBM Office Connect --> Repository Manager --> Connect to Repository. Then, enter database information as the following:
Database Server: DB2 UDB
URL jdbc:db2:OFFCONN
JDBC Driver COM.ibm.db2.jdbc.app.DB2Driver
User Name db2inst1
Password xxxxx
|
- Click Next. Update LDAP information if also connecting through an LDAP server. Then Finish.
- Enter initial user ID and password. By default, Office Connect has initial user ID and password as user admin and password password.
- Close and exit Excel.
- Next step is to set up Office Connect Servlet in Websphere Application Server.
- Copy oc.war from
OfficeConnectPath\WebDeployment\server to a temp directory on AIX or NT where Websphere Server is running.
- Open Websphere First Steps launchpad. Start Application Server.
- Launch IBM Websphere Administrative console. Click:
Nodes --> hostname --> Enterprise Applications --> Install
- Complete the wizard as follow:
Application Name: ocweb
Context Root: oc
Path: oc.war Path
|
- As the installation is completed, click Save to save the configuration file.
- Setup JDBC Driver. From Administrative console, click Resources --> JDBC Drivers --> Db2JdbcDriver. Update the driver information as the following:
Server Class Path: DB2Path\sqllib\java\db2java.zip
Name: Db2JdbcDriver
Description: DB2 JDBC Driver
Implementation Classname: COM.ibm.db2.jdbc.DB2ConnectionPoolDataSource
|
Click Save to save the configuration file.
- Edit ofc.settings in
WebspherePath\installedApps\ocweb.ear\oc.war\WEB-INF. Update DB2 JDBC lines as the following. Change DBNAME to Office Connect repository.
# IBM DB2 UDB JDBC driver
ReposDriverName=COM.ibm.db2.jdbc.app.DB2Driver
# IBM DB2 UDB JDBC URL
ReposConnURL=jdbc:db2:DBNAME;user=db2user;password=db2passwd
|
- Restart Websphere Application Server.
- Test JDBC connectivity. Open http://WebpshereNodename:9080/oc/ofc?cmd=cmd_test where
WebsphereNodename is your web server name. Office Connect Web Edition should now function properly.
- DB2 Office Connect provides some tutorials with the program, which can be found in Start --> Programs --> IBM Office Connect. There is also an article, Client-side Information Integration Using Office Connect, which can quickly get you familiar with DB2 Office Connect user interface.
Install DB2 Office Connect Analytic Edition
To install DB2 Office Connect Analytic Edition, simply run setup.exe from the product CD-ROM. Office Connect Analytic Edition connects to DB2 Cube Views database via ODBC; therefore, ensure that the databases are registered for ODBC on the Office Connect client.
Where to go from here
As the objective of this article is to focus on setting up DB2 Data Warehouse Edition, I will not discuss in detail on how to work with each product but would rather point out some tutorials and references that should get you started quickly and become an expert on each of the product. Please refer to the Reference section to find the links to those resources.
Conclusion
In this article, we went through a process of setting up DB2 Data Warehouse Enterprise Edition on AIX platform, starting from planning the server configuration, to the installation and finally post-installation set up. We also covered steps for verifying that all software components are up and running properly. This should provide you enough information to get started with DB2 Data Warehouse Edition quickly; although, there are still a lot of things you will have to continue to explore to become an expert in one of the product.
Resources
About the author | | | Sermsak Sukjirawat (a.k.a. Sam) is a Senior IT Specialist from DB2 Data Warehouse Consulting Practices, IBM Software Services for DB2 Information Management at Research Triangle Park, NC. He has been supporting DB2 and business intelligence solution for over 6 years and has worked with several customers to implement data warehouses and OLAP applications with DB2 UDB, DB2 Cube Views, DB2 OLAP Server, DB2 OLAP Analyzer, DB2 Warehouse Manager, DB2 Information Integrator, DB2 Query Patroller and DB2 Intelligent Miner. Sam is a DB2 Certified Solutions Expert in both DB2 UDB Administration and Business Intelligence. |
Rate this page
| |
