IBM(R) DB2(R) Web Query Tool for Multiplatforms, Version 1 Release 3 Fix Pack 6 Interim Fix 1 Install Instructions CONTENTS 1.0 Problem Description 2.0 Installation Instructions 2.1 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 6.0 on Multiplatforms 2.2 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 5.0.2 or 5.1 on Multiplatforms 2.3 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 5 or Version 6 on z/OS 3.0 Back out Instructions 2.1 To install DB2 Web Query Tool version 1.3 Fix Pack 6 when working on IBM WebSphere Application Server Version 6.0 on Multiplatforms 2.2 To install DB2 Web Query Tool version 1.3 Fix Pack 6 when working on IBM WebSphere Application Server Version 5.0.2 or 5.1 on Multiplatforms 2.3 To install DB2 Web Query Tool version 1.3 Fix Pack 6 when working on IBM WebSphere Application Server Version 5 or Version 6 on z/OS ________________________________________________________________________________ 1.0 Problem Description This interim fix fixes: - APAR PK13503 - Installation of Web Query requires a cancel command be added to WebSphere shutdown sequence - Edit Query Table button Next does not display data on WAS 5.1 Z/OS. It gives error: ERROR CWQ9102E (1) 2.0 Installation Instructions Install the selected updates using either PKZIP for zip file format or GUNZIP for tar file format. 2.1 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 6.0 on Multiplatforms 1. Start the WebSphere Application Server V 6.0 2. Open the WebSphere Application Server Administrative Console 3. Set the DB2 Web Query Install Root within the Administrative Console: a. Under Servers -> Application servers -> ServerName expand Java and Process Management. Where ServerName is the name of your server. b. Select Process Definition. c. From the Additional Properties box, select Java Virtual Machine d. In the Generic JVM arguments field, type: -Dcom.ibm.db2.cwq.installRoot=C:\IBM\CWQ1.3.6 -Dclient.encoding.override=UTF-8 Where C:\IBM\CWQ1.3.6 is the installRoot directory. These lines should be typed on one line separated by spaces with no carriage returns. Note: The variables, such as installRoot, in this section are case sensitive. e. Set the maximum heap size to 256. f. Click Apply. g. Click OK then save the configuration. 4. Create a JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box, select Data Sources (Version 4). Information : If the Sample DB driver has not already been created, you can create it as follows: i. From the JDBC Providers screen, press the New button. ii. Select DB2 as the database type. iii. Select DB2 Legacy CLI-based Type 2 JDBC Driver. iv. Select Connection pool data source as the implementation type. v. Click Next. vi. Click Apply. b. Click Data sources (version 4). c. Click New. The Configuration tab opens. d. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a database. iv. In the Userid field, type a userID to be used to connect to the specified database. v. In the Password field, type a password to be used to connect to the specified database. vi. Click Apply. vii. Click OK then save the configuration. d. Using your preferred query tool, connect to the cwqManagerDB database that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) 5. Provide the values for all the "WebSphere Variables" being used by the JDBC provider that you use. For example: in the case of "DB2 JDBC Provider" a value for the WebSphere Variable $DB2_JDBC_DRIVER_PATH must be provided. In this case the value is the location of db2java.zip. By default the location of db2java.zip is: C:\Program Files\IBM\SQLLIB\java To specify this value, save the selected JDBC provider to the master configuration and navigate on the WebSphere admin console to: Environment -> WebSphere Variables and click DB2_JDBC_DRIVER_PATH and type the location of the db2java.zip in the Value field. Click OK. 6. Create a "DB2 JDBC" shared library within the Administrative Console: a. In the Administrative console go to: Environment -> Shared Libraries b. Click New. The Configuration tab opens. c. On the Configuration tab, type: "DB2 JDBC" in the Name field. d. On the Configuration tab, type: the classpath of the JDBC driver in the Classpath field. For example, a typical path on Windows is: C:\Program Files\IBM\SQLLIB\java\db2java.zip e. Click Apply. f. Click OK then save the configuration. Information: To work with an Informix database, create a similar entry for the Informix JDBC driver. 7. Use WebSphere Application Server V6.0, install the DB2 Web Query application from the cwq-1.3.6.ear file. a. Select Applications --> Install New application. b. Select the Local Path radio button. c. In the Local Path type the fully defined file name where the DB2 Web Query .ear file is stored on your local machine, or use the browse button to navigate to it. d. Click Continue. The Generate Default Bindings box opens. e. Click Next. Click Continue until the Provide options to perform the installation page opens. f. Click the Map Resource references to resources link. g. On the Map Resource references to resources page, select: jdbc/cwqManagerDB Check to select the module: cwqManagerEJB Click Apply, then click Next. h. In the Application Installation wizard, go to the Summary page and click Finish. The Application Installation wizard closes and the following message is displayed: Application cwqEA installed successfully. i. Click the Save to Master Configuration link to save the configuration and click the Save button on the page that opens. j. In the WebSphere Administrative console, select: Applications -> Enterprise Applications -> cwqEA. The Application Properties panel opens. k. From the Additional Properties box, select Libraries from the Additional Properties box. Check to make sure the DB2 JDBC entry that you created in step 6 is listed. If it is not listed, add it by clicking the Add button and selecting the DB2 JDBC Library Name on the Configuration screen that follows. l. Click Apply. m. Click OK then save the configuration. n. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. Save your changes. o. Start the Enterprise Application cwqEA. To start the Enterprise application, Navigate to Applications -- > Enterprise Applications and select the Enterprise Application cwqEA then click Start. p. Stop WebSphere Application Server and re-start it. 8. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 9. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” in the DB2 Web Query User’s Guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP 10. Click Update. Click Login to login to DB2 Web Query and complete the configuration necessary to work with DB2 Web Query. For more information on configuring DB2 Web Query, see Creating the DB2 Web Query Tool Catalog in the DB2 Web Query user's guide. 2.2 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 5.0.2 or 5.1 on Multiplatforms 1. Start the WebSphere Application Server V 5.0.2 or 5.1 2. Open the WebSphere Application Server Administrative Console 3. Set the DB2 Web Query Install Root within the Administrative Console: a. Select: Servers -> Application Servers -> serverName Where serverName is the name of the name of your server. b. From the Additional Properties box, select Process Definition c. From the Additional Properties box, select Java Virtual Machine d. In the Generic JVM arguments field, type: -Dcom.ibm.db2.cwq.installRoot=C:\IBM\CWQ1.3.6 -Dclient.encoding.override=UTF-8 Where C:\IBM\CWQ1.3.6 is the installRoot directory. These lines should be typed on one line separated by spaces with no carriage returns. Note: The variables, such as installRoot, in this section are case sensitive. e. Set the maximum heap size to 256. f. Click Apply. g. Click OK then save the configuration. 4. Create a JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box, select Data Sources (Version 4). Information : If the Sample DB driver has not already been created, you can create it as follows: i. From the JDBC Providers screen, press the New button. ii. Select DB2 JDBC Provider and click OK. iii. Optional Change the name field to: Sample DB Driver iv. Click Apply. v. Click OK then save the configuration. b. Click New. The Configuration tab opens. c. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a database. iv. In the Userid field, type a userID to be used to connect to the specified database. v. In the Password field, type a password to be used to connect to the specified database. vi. Click Apply. vii. Click OK then save the configuration. viii. Optional: Test the connection to Data Source (Version 4). Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box select: Data Sources (Version 4). Then select the cwqManagerDB data source and click the Test Connection button. d. Using your preferred query tool, connect to the cwqManagerDB database that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) 5. Provide the values for all the "WebSphere Variables" being used by the JDBC provider that you use. For example: in the case of "DB2 JDBC Provider" a value for the WebSphere Variable $DB2_JDBC_DRIVER_PATH must be provided. In this case the value is the location of db2java.zip. By default the location of db2java.zip is: C:\Program Files\IBM\SQLLIB\java To specify this value, save the selected JDBC provider to the master configuration and navigate on the WebSphere admin console to: Environment -> Manage WebSphere Variables and click DB2_JDBC_DRIVER_PATH and type the location of the db2java.zip in the Value field. 6. Create a "DB2 JDBC" shared library within the Administrative Console: a. In the Administrative console go to: Environment -> Shared Libraries b. Click New. The Configuration tab opens. c. On the Configuration tab, type: "DB2 JDBC" in the Name field. d. On the Configuration tab, type: the classpath of the JDBC driver in the Classpath field. For example, a typical path on Windows is: C:\Program Files\IBM\SQLLIB\java\db2java.zip e. Click Apply. f. Click OK then save the configuration. Information: To work with an Informix database, create a similar entry for the Informix JDBC driver. 7. Use WebSphere Application Server V5.0.2, install the DB2 Web Query application from the cwq-1.3_FP6_IF1.ear file. a. Select Applications --> Install New application. b. Select the Local Path radio button. c. In the Local Path type the fully defined file name where the DB2 Web Query .ear file is stored on your local machine, or use the browse button to navigate to it. d. Click Next. The Generate Default Bindings box opens. e. Click Next. The Provide options to perform the installation page opens. f. Click the Map Resource references to resources link. g. On the Map Resource references to resources page, select: jdbc/cwqManagerDB Check to select the module: cwqManagerEJB Click Apply. h. In the Application Installation wizard, go to the Summary page and click Finish. The Application Installation wizard closes and the following message is displayed: Application cwqEA installed successfully. i. Click the Save to Master Configuration link to save the configuration and click the Save button on the page that opens. j. In the WebSphere Administrative console, select: Applications -> Enterprise Applications -> cwqEA. The Application Properties panel opens. k. From the Additional Properties box, select Libraries from the Additional Properties box. Check to make sure the DB2 JDBC entry that you created in step 6 is listed. If it is not listed, add it by clicking the Add button and selecting the DB2 JDBC Library Name on the Configuration screen that follows. l. Click Apply. m. Click OK then save the configuration. n. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. Save your changes. o. Stop then restart the application server: serverName. p. Start the Enterprise Application cwqEA. To start the Enterprise application, Navigate to Applications -- > Enterprise Applications and select the Enterprise Application cwqEA then click Start. 8. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 9. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” in the DB2 Web Query User’s Guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP 10. Click Update. The Configuration Utility closes. You can now login to DB2 Web Query and complete the configuration necessary to work with DB2 Web Query. For more information on configuring DB2 Web Query, see Creating the DB2 Web Query Tool Catalog in the DB2 Web Query user's guide. 2.3 To install DB2 Web Query Tool version 1.3 Fix Pack 6 Interim Fix 1 when working on IBM WebSphere Application Server Version 5 or Version 6 on z/OS When working on Z/OS with USS, the directory where DB2 Web Query is installed and its subdirectories must be owned by and writable by the ID that is running WebSphere. These steps should be done as soon as the Web Query tool is un-tarred. If you go back after you have started Web Query and change the ownership of these files, it may be necessary to stop and re-start WebSphere, in order for WebSphere to have the change. 1. Ensure that all setup has hold actions are performed before the DB2 Web Query install is attempted. 2. When working on Z/OS with USS, ensure that the directory where DB2 Web Query is installed and its subdirectories are owned by, and writable by the ID that is running WebSphere. This must be done before the Web Query application is installed into WebSphere. 3. Start the WebSphere Application Server. 4. Open the WebSphere Application Server Administrative Console. 5. Configure your application servers. a. Expand the Servers item in the left pane. b. Under the Servers item, click the Application Servers link. The Application Servers screen is displayed in the right pane. c. On the Application Servers screen, click the serverName link. Where serverName is the name of your server. d. When working wth WebSphere Applicaton Server V 5: In the Additional Properties table on the Configuration screen, click the Process Definition link. The process definition screen is displayed. When working with WebSpere Application Server V6: Under Server Infrastructure section, expand the Java and Process Management tree by clicking the plus sign. Click Process Definition. The process definition screen is displayed. e. On the Process Definition screen, click the Servant link. f. In the Additional Properties table on the Configuration screen, click the Java Virtual Machine link. The Java Virtual Machine screen displays. g. In the Additional Properties table on the Configuration screen, click the Custom Properties link. The Custom Properties screen displays. h. Click New to create the following entries in the Custom Properties table: * First entry: - Name: com.ibm.db2.cwq.installRoot - Value: installRoot directory (the path to the location where DB2 Web Query is installed. - Description: CWQ Install Directory * Second entry: - Name: client.encoding.override - Value: UTF-8 - Description: CWQ Encoding override 6. Configure JDBC providers. Install the DB2 Universal JDBC Driver Provider as the JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers the JDBC Providers page opens. b. Click the link corresponding to the DB2 Universal JDBC Driver Provider in the table on the JDBC Providers screen. The configure tab of the DB2 Universal JDBC Provider page opens. Information: If a DB2 Universal JDBC Driver Provider has not already been created, you can create it as follows: i. Press the New button. ii. Select DB2 Universal JDBC Driver Provider and click OK. c. Ensure that the following files are in the classpath for the JDBC provider: db2jcc.jar db2jcc_javax.jar db2jcc_license_cu.jar db2jcc_license_cisuz.jar sqlj.zip d. Ensure that the native lib path contains the db2 jcc jdbc driver lib directory. The default for this directory is: .../usr/lpp/db2/db2810/jcc/lib .../usr/lpp/db2/db2810/lib 7. Create the cwqManagerDB. a. In the Additional Properties table, click DataSources (Version 4). b. Click New. The Configuration tab opens. c. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a location or database alias. iv. In the Userid field, type a userID to be used to connect to the specified location or database alias. v. In the Password field, type a password to be used to connect to the specified location and database alias. d. In the Additional Properties table, click Custom Properties and define all of the required custom properties for the data source including: * driverType: The JDBC connectivity-type of a data source. If you want to use type 4 driver, set the value to 4. If you want to use type 2 driver, set the value to 2. On the WebSphere z/OS, driver, Type 2 uses RRS and supports 2-phase commit processing. * serverName: The TCP/IP address or host name for the DRDA server. * portNumber: The TCP/IP port number where the DRDA server resides. 8. Configure a JDBC (Version 4) data source for each database to which you want to connect using DB2 Web Query. You can name your data sources any name that fits with your organization. The recommended JNDI naming convention is: jdbc/ where is the location name of the database. Requirement: Data sources that are used to connect to a database must have the auto connection cleanup option disabled. This option is set on the Connection pool tab. To create a data source you must: a. Define all of the required custom properties for the data source including: * driverType: The JDBC connectivity-type of a data source. If you want to use type 4 driver, set the value to 4. If you want to use type 2 driver, set the value to 2. On the WebSphere z/OS, driver, Type 2 uses RRS and supports 2-phase commit processing. * serverName: The TCP/IP address or host name for the DRDA server. * portNumber: The TCP/IP port number where the DRDA server resides. b. Test each data source after configuration to ensure that it functions properly. If the connection test is not successful, consult the WebSphere documentation on instructions, hold actions, and paths to jar and native lib files. 9. Configure the DB2 Web Query EJB database. Using your preferred query tool, connect to the cwqManagerDB location or database alias that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) If the cwqManagerDB is a on z/OS or OS/390 system you must also run the following to make the table usable: CREATE UNIQUE INDEX wq.manageridx ON wq.manager(cwqname asc) 10. Deploy DB2 Web Query a. From the left pane, expand the Applications item. b. Click the Install New Application link. The Preparing for the application installation page opens. c. From the left pane, under the Applications item, click Install new application. Specify the path to the .ear file that you want to install under Server Path. Select cwq-1.3_FP6_IF1_390.ear. The Provide options to perform the installation page is displayed. d. On the Provide options to perform the installation page: * Check the Distribute Application check box. * Check the Create MBeans for Resources check box. * In the Application Name field, type cwqEA. e. Click the Next until the Map resource references to resources page opens. f. On the the Map resource references to resources page: i. Select jdbc/cwqManagerDB from the drop down box ii. Check the module cwqManagerEJBcheckbox. iii. Click Apply. iv. Click Next. jbdc/cwqManagerDB appears in the JNDI Name column. g. Click the Summary link. The Summary of Install Options page opens. h. On the Summary of Install Options page, click Finish to deploy DB2 Web Query. 11. Select Application -> Enterprise Applications in the left pane and ensure that cwqEA appears listed as an application. 12. Save the configuration by clicking Save on the menu bar at the top of the screen. 13. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. 14. Save the configuration by clicking Save on the menu bar at the top of the screen. 15. Stop then restart the application server. 16. Ensure that the cwqEA application has been started by selecting Enterprise Applications and then checking the box next to cwqEA. If the start button is available, then the application is not started. You can start it by clicking Start. 17. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application Server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 18. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” In the DB2 Web Query User’s guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP a. Ensure that the entry for managerDatasource is set to jdbc/cwqManagerDB b. Ensure that dbDriver# statements are created for each data source that you created in step 6 to which DB2 Web Query will connect. c. Ensure that the connection pool field is set to the JNDI name of the data source as defined in step 6. 19. Click Update. The Configuration Utility closes. You can now login to DB2 Web Query. 3.0 Back out Instructions To back out DB2 Web Query V 1.3 Fix Pack 5, Interim fix 1, install DB2 Web Query V1.3 Fix Pack 5. 3.1 Configuring IBM WebSphere Application Server Version 6.0 on Multiplatforms to work with IBM DB2 Web Query 1. Start the WebSphere Application Server V 6.0 2. Open the WebSphere Application Server Administrative Console 3. Set the DB2 Web Query Install Root within the Administrative Console: a. Under Servers -> Application servers -> ServerName expand Java and Process Management. Where ServerName is the name of your server. b. Select Process Definition. c. From the Additional Properties box, select Java Virtual Machine d. In the Generic JVM arguments field, type: -Dcom.ibm.db2.cwq.installRoot=C:\IBM\CWQ1.3.6 -Dclient.encoding.override=UTF-8 Where C:\IBM\CWQ1.3.6 is the installRoot directory. These lines should be typed on one line separated by spaces with no carriage returns. Note: The variables, such as installRoot, in this section are case sensitive. e. Set the maximum heap size to 256. f. Click Apply. g. Click OK then save the configuration. 4. Create a JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box, select Data Sources (Version 4). Information : If the Sample DB driver has not already been created, you can create it as follows: i. From the JDBC Providers screen, press the New button. ii. Select DB2 as the database type. iii. Select DB2 Legacy CLI-based Type 2 JDBC Driver. iv. Select Connection pool data source as the implementation type. v. Click Next. vi. Click Apply. b. Click Data sources (version 4). c. Click New. The Configuration tab opens. d. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a database. iv. In the Userid field, type a userID to be used to connect to the specified database. v. In the Password field, type a password to be used to connect to the specified database. vi. Click Apply. vii. Click OK then save the configuration. d. Using your preferred query tool, connect to the cwqManagerDB database that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) 5. Provide the values for all the "WebSphere Variables" being used by the JDBC provider that you use. For example: in the case of "DB2 JDBC Provider" a value for the WebSphere Variable $DB2_JDBC_DRIVER_PATH must be provided. In this case the value is the location of db2java.zip. By default the location of db2java.zip is: C:\Program Files\IBM\SQLLIB\java To specify this value, save the selected JDBC provider to the master configuration and navigate on the WebSphere admin console to: Environment -> WebSphere Variables and click DB2_JDBC_DRIVER_PATH and type the location of the db2java.zip in the Value field. Click OK. 6. Create a "DB2 JDBC" shared library within the Administrative Console: a. In the Administrative console go to: Environment -> Shared Libraries b. Click New. The Configuration tab opens. c. On the Configuration tab, type: "DB2 JDBC" in the Name field. d. On the Configuration tab, type: the classpath of the JDBC driver in the Classpath field. For example, a typical path on Windows is: C:\Program Files\IBM\SQLLIB\java\db2java.zip e. Click Apply. f. Click OK then save the configuration. Information: To work with an Informix database, create a similar entry for the Informix JDBC driver. 7. Use WebSphere Application Server V6.0, install the DB2 Web Query application from the cwq-1.3.6.ear file. a. Select Applications --> Install New application. b. Select the Local Path radio button. c. In the Local Path type the fully defined file name where the DB2 Web Query .ear file is stored on your local machine, or use the browse button to navigate to it. d. Click Continue. The Generate Default Bindings box opens. e. Click Next. Click Continue until the Provide options to perform the installation page opens. f. Click the Map Resource references to resources link. g. On the Map Resource references to resources page, select: jdbc/cwqManagerDB Check to select the module: cwqManagerEJB Click Apply, then click Next. h. In the Application Installation wizard, go to the Summary page and click Finish. The Application Installation wizard closes and the following message is displayed: Application cwqEA installed successfully. i. Click the Save to Master Configuration link to save the configuration and click the Save button on the page that opens. j. In the WebSphere Administrative console, select: Applications -> Enterprise Applications -> cwqEA. The Application Properties panel opens. k. From the Additional Properties box, select Libraries from the Additional Properties box. Check to make sure the DB2 JDBC entry that you created in step 6 is listed. If it is not listed, add it by clicking the Add button and selecting the DB2 JDBC Library Name on the Configuration screen that follows. l. Click Apply. m. Click OK then save the configuration. n. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. Save your changes. o. Start the Enterprise Application cwqEA. To start the Enterprise application, Navigate to Applications -- > Enterprise Applications and select the Enterprise Application cwqEA then click Start. p. Stop WebSphere Application Server and re-start it. 8. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 9. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” in the DB2 Web Query User’s Guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP 10. Click Update. Click Login to login to DB2 Web Query and complete the configuration necessary to work with DB2 Web Query. For more information on configuring DB2 Web Query, see Creating the DB2 Web Query Tool Catalog in the DB2 Web Query user's guide. 3.2 Configuring IBM WebSphere Application Server Version 5.0.2 or 5.1 on Multiplatforms to work with IBM DB2 Web Query 1. Start the WebSphere Application Server V 5.0.2 or 5.1 2. Open the WebSphere Application Server Administrative Console 3. Set the DB2 Web Query Install Root within the Administrative Console: a. Select: Servers -> Application Servers -> serverName Where serverName is the name of the name of your server. b. From the Additional Properties box, select Process Definition c. From the Additional Properties box, select Java Virtual Machine d. In the Generic JVM arguments field, type: -Dcom.ibm.db2.cwq.installRoot=C:\IBM\CWQ1.3.6 -Dclient.encoding.override=UTF-8 Where C:\IBM\CWQ1.3.6 is the installRoot directory. These lines should be typed on one line separated by spaces with no carriage returns. Note: The variables, such as installRoot, in this section are case sensitive. e. Set the maximum heap size to 256. f. Click Apply. g. Click OK then save the configuration. 4. Create a JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box, select Data Sources (Version 4). Information : If the Sample DB driver has not already been created, you can create it as follows: i. From the JDBC Providers screen, press the New button. ii. Select DB2 JDBC Provider and click OK. iii. Optional Change the name field to: Sample DB Driver iv. Click Apply. v. Click OK then save the configuration. b. Click New. The Configuration tab opens. c. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a database. iv. In the Userid field, type a userID to be used to connect to the specified database. v. In the Password field, type a password to be used to connect to the specified database. vi. Click Apply. vii. Click OK then save the configuration. viii. Optional: Test the connection to Data Source (Version 4). Select: Resources -> JDBC Providers -> Sample DB Driver and from the Additional Properties box select: Data Sources (Version 4). Then select the cwqManagerDB data source and click the Test Connection button. d. Using your preferred query tool, connect to the cwqManagerDB database that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) 5. Provide the values for all the "WebSphere Variables" being used by the JDBC provider that you use. For example: in the case of "DB2 JDBC Provider" a value for the WebSphere Variable $DB2_JDBC_DRIVER_PATH must be provided. In this case the value is the location of db2java.zip. By default the location of db2java.zip is: C:\Program Files\IBM\SQLLIB\java To specify this value, save the selected JDBC provider to the master configuration and navigate on the WebSphere admin console to: Environment -> Manage WebSphere Variables and click DB2_JDBC_DRIVER_PATH and type the location of the db2java.zip in the Value field. 6. Create a "DB2 JDBC" shared library within the Administrative Console: a. In the Administrative console go to: Environment -> Shared Libraries b. Click New. The Configuration tab opens. c. On the Configuration tab, type: "DB2 JDBC" in the Name field. d. On the Configuration tab, type: the classpath of the JDBC driver in the Classpath field. For example, a typical path on Windows is: C:\Program Files\IBM\SQLLIB\java\db2java.zip e. Click Apply. f. Click OK then save the configuration. Information: To work with an Informix database, create a similar entry for the Informix JDBC driver. 7. Use WebSphere Application Server V5.0.2, install the DB2 Web Query application from the cwq-1.3.6.ear file. a. Select Applications --> Install New application. b. Select the Local Path radio button. c. In the Local Path type the fully defined file name where the DB2 Web Query .ear file is stored on your local machine, or use the browse button to navigate to it. d. Click Next. The Generate Default Bindings box opens. e. Click Next. The Provide options to perform the installation page opens. f. Click the Map Resource references to resources link. g. On the Map Resource references to resources page, select: jdbc/cwqManagerDB Check to select the module: cwqManagerEJB Click Apply. h. In the Application Installation wizard, go to the Summary page and click Finish. The Application Installation wizard closes and the following message is displayed: Application cwqEA installed successfully. i. Click the Save to Master Configuration link to save the configuration and click the Save button on the page that opens. j. In the WebSphere Administrative console, select: Applications -> Enterprise Applications -> cwqEA. The Application Properties panel opens. k. From the Additional Properties box, select Libraries from the Additional Properties box. Check to make sure the DB2 JDBC entry that you created in step 6 is listed. If it is not listed, add it by clicking the Add button and selecting the DB2 JDBC Library Name on the Configuration screen that follows. l. Click Apply. m. Click OK then save the configuration. n. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. Save your changes. o. Stop then restart the application server: serverName. p. Start the Enterprise Application cwqEA. To start the Enterprise application, Navigate to Applications -- > Enterprise Applications and select the Enterprise Application cwqEA then click Start. 8. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 9. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” in the DB2 Web Query User’s Guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP 10. Click Update. The Configuration Utility closes. You can now login to DB2 Web Query and complete the configuration necessary to work with DB2 Web Query. For more information on configuring DB2 Web Query, see Creating the DB2 Web Query Tool Catalog in the DB2 Web Query user's guide. 3.3 Configuring IBM WebSphere Application Server Version 5 or Version 6 on z/OS to work with IBM DB2 Web Query When working on Z/OS with USS, the directory where DB2 Web Query is installed and its subdirectories must be owned by and writable by the ID that is running WebSphere. These steps should be done as soon as the Web Query tool is un-tarred. If you go back after you have started Web Query and change the ownership of these files, it may be necessary to stop and re-start WebSphere, in order for WebSphere to have the change. 1. Ensure that all setup has hold actions are performed before the DB2 Web Query install is attempted. 2. When working on Z/OS with USS, ensure that the directory where DB2 Web Query is installed and its subdirectories are owned by, and writable by the ID that is running WebSphere. This must be done before the Web Query application is installed into WebSphere. 3. Start the WebSphere Application Server. 4. Open the WebSphere Application Server Administrative Console. 5. Configure your application servers. a. Expand the Servers item in the left pane. b. Under the Servers item, click the Application Servers link. The Application Servers screen is displayed in the right pane. c. On the Application Servers screen, click the serverName link. Where serverName is the name of your server. d. When working wth WebSphere Applicaton Server V 5: In the Additional Properties table on the Configuration screen, click the Process Definition link. The process definition screen is displayed. When working with WebSpere Application Server V6: Under Server Infrastructure section, expand the Java and Process Management tree by clicking the plus sign. Click Process Definition. The process definition screen is displayed. e. On the Process Definition screen, click the Servant link. f. In the Additional Properties table on the Configuration screen, click the Java Virtual Machine link. The Java Virtual Machine screen displays. g. In the Additional Properties table on the Configuration screen, click the Custom Properties link. The Custom Properties screen displays. h. Click New to create the following entries in the Custom Properties table: * First entry: - Name: com.ibm.db2.cwq.installRoot - Value: installRoot directory (the path to the location where DB2 Web Query is installed. - Description: CWQ Install Directory * Second entry: - Name: client.encoding.override - Value: UTF-8 - Description: CWQ Encoding override 6. Configure JDBC providers. Install the DB2 Universal JDBC Driver Provider as the JDBC Provider within the Administrative Console: a. Select: Resources -> JDBC Providers the JDBC Providers page opens. b. Click the link corresponding to the DB2 Universal JDBC Driver Provider in the table on the JDBC Providers screen. The configure tab of the DB2 Universal JDBC Provider page opens. Information: If a DB2 Universal JDBC Driver Provider has not already been created, you can create it as follows: i. Press the New button. ii. Select DB2 Universal JDBC Driver Provider and click OK. c. Ensure that the following files are in the classpath for the JDBC provider: db2jcc.jar db2jcc_javax.jar db2jcc_license_cu.jar db2jcc_license_cisuz.jar sqlj.zip d. Ensure that the native lib path contains the db2 jcc jdbc driver lib directory. The default for this directory is: .../usr/lpp/db2/db2810/jcc/lib .../usr/lpp/db2/db2810/lib 7. Create the cwqManagerDB. a. In the Additional Properties table, click DataSources (Version 4). b. Click New. The Configuration tab opens. c. Fill in the fields on the Configuration tab: i. In the Name field, type: cwqManagerDB ii. In the JNDI Name field, type: jdbc/cwqManagerDB iii. In the Database field, specify a location or database alias. iv. In the Userid field, type a userID to be used to connect to the specified location or database alias. v. In the Password field, type a password to be used to connect to the specified location and database alias. d. In the Additional Properties table, click Custom Properties and define all of the required custom properties for the data source including: * driverType: The JDBC connectivity-type of a data source. If you want to use type 4 driver, set the value to 4. If you want to use type 2 driver, set the value to 2. On the WebSphere z/OS, driver, Type 2 uses RRS and supports 2-phase commit processing. * serverName: The TCP/IP address or host name for the DRDA server. * portNumber: The TCP/IP port number where the DRDA server resides. 8. Configure a JDBC (Version 4) data source for each database to which you want to connect using DB2 Web Query. You can name your data sources any name that fits with your organization. The recommended JNDI naming convention is: jdbc/ where is the location name of the database. Requirement: Data sources that are used to connect to a database must have the auto connection cleanup option disabled. This option is set on the Connection pool tab. To create a data source you must: a. Define all of the required custom properties for the data source including: * driverType: The JDBC connectivity-type of a data source. If you want to use type 4 driver, set the value to 4. If you want to use type 2 driver, set the value to 2. On the WebSphere z/OS, driver, Type 2 uses RRS and supports 2-phase commit processing. * serverName: The TCP/IP address or host name for the DRDA server. * portNumber: The TCP/IP port number where the DRDA server resides. b. Test each data source after configuration to ensure that it functions properly. If the connection test is not successful, consult the WebSphere documentation on instructions, hold actions, and paths to jar and native lib files. 9. Configure the DB2 Web Query EJB database. Using your preferred query tool, connect to the cwqManagerDB location or database alias that you specified in step 5c (using the userID and Password that you specified in step 5c) and run the following create statement: CREATE TABLE WQ.MANAGER (CWQNAME VARCHAR(40) NOT NULL, PRIMARY KEY(CWQNAME)) If the cwqManagerDB is a on z/OS or OS/390 system you must also run the following to make the table usable: CREATE UNIQUE INDEX wq.manageridx ON wq.manager(cwqname asc) 10. Deploy DB2 Web Query a. From the left pane, expand the Applications item. b. Click the Install New Application link. The Preparing for the application installation page opens. c. From the left pane, under the Applications item, click Install new application. Specify the path to the .ear file that you want to install under Server Path. Select cwq-1.3_FP6_390.ear. The Provide options to perform the installation page is displayed. d. On the Provide options to perform the installation page: * Check the Distribute Application check box. * Check the Create MBeans for Resources check box. * In the Application Name field, type cwqEA. e. Click the Next until the Map resource references to resources page opens. f. On the the Map resource references to resources page: i. Select jdbc/cwqManagerDB from the drop down box ii. Check the module cwqManagerEJBcheckbox. iii. Click Apply. iv. Click Next. jbdc/cwqManagerDB appears in the JNDI Name column. g. Click the Summary link. The Summary of Install Options page opens. h. On the Summary of Install Options page, click Finish to deploy DB2 Web Query. 11. Select Application -> Enterprise Applications in the left pane and ensure that cwqEA appears listed as an application. 12. Save the configuration by clicking Save on the menu bar at the top of the screen. 13. Select the cwqEA application and change the WAR Classloader Policy from Module to Application. 14. Save the configuration by clicking Save on the menu bar at the top of the screen. 15. Stop then restart the application server. 16. Ensure that the cwqEA application has been started by selecting Enterprise Applications and then checking the box next to cwqEA. If the start button is available, then the application is not started. You can start it by clicking Start. 17. Open the DB2 Web Query Configuration Utility by accessing the following URL: http://server:port/DB2Tools/WebQuery/Configure Where server is the name of your Web server and port is the WebSphere Application Server port. Information: The default WebSphere Application Server port is 9080. The initial user id is: cwq. The initial password is: cwq 18. In the DB2 Web Query Configuration Utility, specify the DB2 Web Query initial parameters. For more information on initial parameters, see “Parameter list” In the DB2 Web Query User’s guide. Requirement: Ensure that the user ID that you specify for the wqUserId parameter has select authority on the following tables at each location to which DB2 Web Query connects: SYSIBM.SYSTABLES SYSIBM.SYSTABAUTH SYSIBM.SYSADMAUTH SYSIBM.SYSDBAUTH WQ.STORE_TABLE WQ.INDEX_TABLE WQ.SMTP a. Ensure that the entry for managerDatasource is set to jdbc/cwqManagerDB b. Ensure that dbDriver# statements are created for each data source that you created in step 6 to which DB2 Web Query will connect. c. Ensure that the connection pool field is set to the JNDI name of the data source as defined in step 6. 19. Click Update. The Configuration Utility closes. You can now login to DB2 Web Query.