InfoSphere Warehouse for System z is a suite of products that combines the strength of DB2 for z/OS® with a data warehousing infrastructure from IBM. You can use InfoSphere Warehouse for System z to build a complete data warehousing solution that uses your existing relational database to perform multidimensional reporting and analysis of data with cubing services. You can also use the in-database data movement and manipulation capabilities of SQL Warehouse Tool (SQW) to transform and load your data.
This article describes the installation and configuration of the InfoSphere Warehouse Server product on System z. You'll walk through the product installation on a Linux on System z partition connecting to a remote DB2 for z/OS database server. Figure 1 shows the systems involved in the installation:
Figure 1. Systems involved in this installation
InfoSphere Warehouse on System z installs into a Linux partition on System z. It uses DB2 for z/OS as the target data warehouse and as the source for Cubing Services data. DB2 stored procedures are used during installation and for certain product functions. So both the DB2 environment and the InfoSphere Warehouse must be prepared.
- To prepare the DB2 environment, set up the following stored procedures on the DB2 for z/OS database server:
Before you can use certain functions of the DB2 Driver for JDBC and SQLJ on a DB2 for z/OS subsystem, you need to enable a set of stored procedures for JDBC. Both the cube server and the Administration console use JDBC. Use the product documentation for details on how to install.
Note: To set up and bind these stored procedures and related global tables, run the DB2-supplied SDSNSAMP batch job DSNTIJSG. The stored procedures ADMIN_JOB_FETCH, ADMIN_JOB_SUBMIT, ADMIN_JOB_CANCEL, and ADMIN_JOB_QUERY use the __login() function to switch users. Be sure that ADMIN_JOB_FETCH, ADMIN_JOB_SUBMIT, ADMIN_JOB_CANCEL, and ADMIN_JOB_QUERY stored procedures are defined to the RACF® program control. The executing WLM address spaces must be defined accordingly. Additional information is available in the DB2 V9 product documentation The IBM Redbooks® publication DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond describes how to set up and use stored procedures.
- To create and configure the InfoSphere Warehouse metadata database, follow these steps:
- Catalog the z/OS database using the following commands on the Linux for System z system:
db2 catalog tcpip node <node name> remote <remote z/OS machine name> server <remote machine port number> db2 catalog db <remote database name> as <local database name> at node <node name>
- Create a database for the InfoSphere Warehouse metadata tables. The default is SQWCTRL. This name can be changed and must be entered when prompted during installation.
db2 create database SQWCTRL stogroup <storage group>
- Grant authorities for the metadata database to the userid that will be connecting to the z/OS database during installation and configuration:
Grant DBADM, DBCTRL, CREATETAB, CREATETS, DISPLAYDB, DROP, LOAD, REORG, STATS ON DATABASE
This statement grants DBADM, create table, create index, create tablespace, drop, select and load authority to the userid.
Grant ALTERIN,CREATEIN,DROPIN on SCHEMA *
This statement grants create trigger authority to all schemas.
- Grant the userid that will be the creating the cube DBADM on the database where the cube will be created.
Note: The metadata tables are created when the user first logs into the InfoSphere Administration Console (at the end of this installation). After the metadata tables have been created, only SELECT, UPDATE, DELETE and INSERT authorities are necessary for normal day to day operations for the tables in the metadata database (or SQWCTRL) for the userid that will be creating and deploying the cube.
- Catalog the z/OS database using the following commands on the Linux for System z system:
Sometimes the following error is displayed while connecting to the z/OS database:
DB2 SQL Error: SQLCODE=-443, SQLSTATE=38112, SQLERRMC=SQLTABLES;SQLTABLES;-805 DWHD91T.DSNASPCC.DSNATBL8.1856182D029A8769
This problem can be fixed by either rerunning the DSNTIJSG job which will rebind all the stored procedures and eliminate consistency errors, or by installing UK38087 and rebinding.
Installation on Linux on System z
To install the InfoSphere Warehouse server on Linux on System z, you need to log in with a userid that has root access (an account that has sudo authority) or as root. The InfoSphere Warehouse Server can be installed in one of multiple ways: with the wizard, in console mode, or as a silent install with a response file. More information is provided in the product documentation.
This article focuses on installation using the wizard. In order to install using the wizard, you'll need to VNC (http://www.tightvnc.com/) into the Linux on System z box, or run an X-Server. In order to display the wizard on your machine use the following command:
export DISPLAY=<your IP>:0.0
Copy and extract the installation files as described in the product documentation.
To install using the installation wizard, run the install script from the directory where you unzipped the files, as shown in Figure 2:
Figure 2. Run the install script
The Dprofile parameter is necessary so that the z version of the product is installed.
The following options are presented to the user to customize their installation. On the first screen, select the language and click OK.
Figure 3. Selecting language
The next screen welcomes you to the installation. Click Next to continue.
Figure 4. Welcome screen
The next screen shows the license agreement. Click to accept and click Next.
Figure 5. License agreement
On the next screen, select Install IBM InfoSphere Warehouse on System Z on this computer.
Figure 6. Selecting installation options
On the next screen, specify the installation directory. If using the default, ensure that the directory /opt/IBM/ISWarehouse does not already exist. If you have uninstalled a previous installation of InfoSphere Warehouse, this directory will not be removed during the uninstall, so please remove, rename or provide another directory
Figure 7. Specify the installation directory
On the next screen, select the components. You should select all the components as shown, even if DB2 and WebSphere Application Server are already installed on the system. Options will be displayed later to pick your existing installation.
Figure 8. Selecting components
On the next screen, specify the login information to enable WebSphere Application Server global security. This is the userid that will be used to stop and start WebSphere Application Server. This userid must exist on the system. If you want to create a dedicated user for InfoSphere Warehouse, and not share "wasadmin" with WebSphere Application Server (if it is already installed on the system), then the userid you are providing will need to be created beforehand manually.
Figure 9. Specify login information
The next screen will be presented if there is already a copy WebSphere Application Sever on the system. You can use an existing copy of WebSphere Application Server if it meets the version requirements of InfoSphere Warehouse as documented in the install guide.
Figure 10. WebSphere Application Sever options
If a DB2 Connect Personal Edition already exists on the computer, the next screen will be presented. A valid DB2 Connect license is needed to connect with the JDBC driver from Linux on System z to DB2 for z/OS. DB2 Connect Personal Edition is delivered with InfoSphere Warehouse for z to provide this license file. The existing copy can be used.
Figure 11. DB2 Connect install option
If no DB2 Connect is available on the system, the next screen is presented. Provide the DB2 userid and password for the DB2 instance owner. A new directory is created.
Figure 12. Setting user information for the DB2 instance owner
On the next screen, specify the database to use for your warehouse metadata. This information is used to connect to the z/OS metadata database (which was cataloged earlier in the pre-installation activities). You cannot move ahead until a successful connection is established. This is the database created during the pre-installation activities above.
Note: Ensure the Location field is in uppercase. A lowercase location name will result in an error when you attempt to connect.
Figure 13. Specify database for warehouse metadata.
Figure 14 shows a summary dialog that displays the options and components to install. Click Install to proceed.
Figure 14. Options and components to install
The installation is complete.
Figure 15. Installation complete
Once the installation is complete, the administration console can be displayed using the URL:
http://<System name>:<WAS port>/ibm/warehouse/
For example: http://hostname:9080/ibm/warehouse/
You can now use the Administration Console to deploy and manage applications, control flows, database resources, and system resources. Learn more about the different components of InfoSphere Warehouse for System z and how to use them from the Information Center.
- Installing InfoSphere Warehouse: Use the Information Center to get more details on the installation process.
- Enabling the DB2-supplied stored procedures and defining the tables used by the IBM Data Server Driver for JDBC and SQLJ : The Information Center gives instructions for enabling the required stored procedures.
- DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond: This IBM Redbooks publication describes how to set up and use stored procedures (pages 508 â 512).
- In the Data warehousing, get the resources you need to advance your skills in InfoSphere Warehouse.
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Download a free trial version of IBM InfoSphere Warehouse 9.7 Enterprise Edition.
- Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.