x

To set up the PMF data mart, you need to:

1. Set up, or gain access to, an existing RDBMS resource. Most typically you can set up a PMF data mart in the MS SQL Server, Oracle, or DB2 RDBMS. If you need the data mart to be different from one of these, contact your Information Builders support representative.
2. Have security credentials for the RDBMS that are at sufficient level to allow the following options:
   • Create, alter, and drop of tables, views, and procedures.
   • For Oracle, create and drop public synonyms, triggers, and sequences. You can also enable and disable triggers.
   • Select, update, and delete for all tables, and select on all views.

   Test and confirm the credentials you have given these rights, before attempting to use the credentials to create the PMF Data mart in your RDBMS.

3. If necessary, have your DBA review the provided, packaged PMF SQL DDL code that creates and sets up the PMF data mart.
4. From your SQL tool of choice, either run the provided, packaged PMF yourself or have your DBA run it and save the SQL output files from the process, at full error reporting levels so it will be possible to see any error messages.
5. Review the SQL output files from the process and confirm all ran properly with no displayed errors.
6. Make sure connectivity software (e.g., the database connection client) for your Reporting server has been properly set up.
7. Make sure the WebFOCUS RDBMS connection called mainstreet has been properly configured for connection to the RDBMS.
8. If desired, perform a snapshot restore of demonstration data for PMF.
9. Log into PMF as an administrator and resynch the PMF metadata from the newly connected data mart.

x

The recommended way to build a new PMF Data Mart is using the provided SQL DDL.

1. Start your SQL tool for MS SQL Server. Most typically, DB users use Microsoft provided SQL Server Management Studio. Make sure to log in to this tool using the Owner ID that has all proper rights to create, read, and drop tables, views and triggers.
2. Check your MS SQL Server owner name for the data mart. PMF currently requires the default DBO.
3. Create the new database in your MS SQL Server environment using SQL Server Management Studio.
4. In the SQL Server Management Studio console, run the provided SQL DDL called sqlmss.sql. This script is located on the WebFOCUS Reporting Server in /ibi/apps/pmfdbms/Create_Database. The DDL script creates and populates the new data mart with the minimum amount of data required to start PMF.
5. Personally review the SQL output in SQL Server Management Studio from the DDL process and confirm all ran properly with no displayed errors. If there were errors, double-check that your DB Owner name has the proper rights to create, read, and drop tables, views and procedures, and that the physical connection from SQL Server Management Studio to your RDBMS is correct.
6. Double-check that you have any proper DB Client software installed on your WebFOCUS Reporting Server, as needed, and that the physical connection to the RDBMS server and database has been set up in that client.
7. Double-check that an adapter connection called mainstreet has been set up for MS SQL Server in the WebFOCUS Reporting Server console for the Reporting Server(s) used for PMF.
8. If needed, to provide sample data for PMF, or to restore your previous PMF data from another installation of PMF, restore a snapshot into the data mart. For more information, see How to Restore a Snapshot.
9. Log into PMF as an administrator. You should be prompted to resynch PMF. Perform the resynch. If there are any issues, check to make sure you followed every aspect of this procedure. If it was followed perfectly and there are still issues, see Troubleshooting Common Issues.

x

Note the following before performing the procedure:

• When upgrading PMF to a new release, the upgrade migration scripts now run within PMF using auto-migration functionality.
• Sample DDL for creating the default databases and Oracle owner name (schema) can be found in the sqlora_env_defaults.sql file, which is located in the \ibi\apps\pmfdbms\Create_Database folder in the standard PMF installation.
• It is recommended that tablespaces and schema names be composed of uppercase standard ANSI characters. Doing so helps avoid problems that might occur while running the PMF scripts.
• Only one PMF datamart can be created per Oracle database, since PMF uses Oracle Public Synonyms. Each PMF schema must reside in a separate database.

If you are planning to create a new data mart for PMF, perform the following steps:

1. Create the new database in your Oracle environmental using your standard toolset.
2. Create table spaces for your tables and indexes.
3. Create your Oracle owner name for the data mart. We recommend using PMF for simplicity, but you can use a different name if required for your standards.
4. Set up the Oracle SID.
5. Edit the sqlora.sql file located in the \ibi\apps\pmfdbms\oCreate_Database directory and set the proper values for the three environmental variables. These values must be identical to those used in steps 2 and 3. It is recommended that you use upper standard ANSI characters only.

   For details about variable settings, see Settings for Oracle Data Mart Build and Upgrade.

6. Start Oracle SQL*Plus.
7. In the SQL*Plus session, run sqlora.sql.

   The DDL script creates and populates the new data mart with the minimum amount of data required to start PMF.

x

The following system settings enable PMF to automatically upgrade the PMF data mart without requiring further input from you (other than initially authorizing PMF to perform the upgrade). These entries specifically support Oracle RDBMS tables.

Setting	Controls
PMF_DB_OWNER	The configured owner of the Oracle database that contains the PMF data mart.
PMF_DB_TABLESPACE	The defined and configured tablespace for the Oracle database.
PMF_DB_INDEXSPACE	The defined and configured index space for the Oracle database.

During installation, the PMF installer prompts for these settings if you select an Oracle data mart option. After the upgrade is completed, PMF updates the settings table with the confirmed values that you provided.

Important: Do not enter dummy or placeholder values for these settings.

Note:

• If you need to specify additional storage specifications, you can do so using the global parameters. The following is an example:

  define PMF_DB_TABLESPACE = 'PMF_DATAMART_DATA'
  --define PMF_DB_TABLESPACE = 'PMF_DATAMART_DATA storage 
  (initial 64K next 0K minextents 1 maxextents 2147483645 pctincrease 0
  freelists 1 freelist groups 1)' 

• The scripts that are required to create the PMF data mart require specific settings for certain Oracle system parameters. In particular, NLS_DATE_FORMAT, NLS_TIMESTAP_FORMAT, and NLS_LENGTH_SEMANTICS.

  These settings will override your site standard settings for the duration of your Oracle session unless you specifically reset them after you have finished running the PMF scripts. Once you close your session, the settings will revert back to your site standards. For more information, consult your Oracle documentation.

• If you change your Oracle configuration or security settings, you have to change the PMF_DB_OWNER, PMF_DB_TABLESPACE, and PMF_DB_INDEXSPACE settings. Otherwise, automatic upgrade processing will stop working and the Resync panel will display an error message.
• If you are re-creating the PMF datamart under a different schema, drop all existing PMF objects using Oracle tools first.

x

The Resync panel will appear the first time that you log into PMF after creating a new database, or if you are upgrading from a PMF release prior to 5.1.3.

It will prompt you for the Schema, Tablespace, and Indexspace values, as shown in the following image.

Once the values are entered, the Oracle system settings will be verified for legitimacy in your database. You cannot continue with the installation or upgrade if any of these values are not legitimate.

Note: Once these values have been entered and accepted by the Resync panel, they cannot be changed in the PMF application.

Important: If you have changed your Oracle configuration or security settings, the settings are not blank and the PMF Resync panel does not prompt for them. However, the PMF Resync panel will display an error message, as shown in the following image.

To correct the error, you have to restore your data mart from the backup and change the PMF_DB_OWNER, PMF_DB_TABLESPACE, and PMF_DB_INDEXSPACE settings.

x

Establishing and maintaining a DB2/UDB database is a complex task that requires knowledge of DB2/UDB and of any site specific standards. The following procedure should be performed by an experienced database administrator.

1. You will need to attach your RDBMS query tool to your DB2/UDB system with an ID that has all necessary privileges to perform the following steps. This includes, but is not limited to, create database, schema, tables, views, indexes, procedures, and so on. Make sure that such an ID is available.
2. Create the new database and schema in your DB2/UDB environmental using your standard toolset.

   It is recommended that you use PMF for the schema name but a different name can be used if required for your standards.

3. Create table spaces for your tables and indexes using your standard toolset.

   It is recommended that you use PMF_DATAMART_DATA for the tablespace for the tables and PMF_DATAMART_INDEX for the tablespace for your indexes. Different names can be used if required for your standards.

   Note: Sample code to create the database, schema, and table spaces is in the supplied script db2_env_defaults.sql which is located in the apps/pmfdbms/Create_Database folder.

4. Run the supplied script db2.sql which is also in apps/pmfdbms/Create_Database folder.

   If you are not using the recommended schema or tablespace names, you will have to first edit the db2.sql file and substitute the proper names where ever they are used. View the output listing and verify that all statements completed successfully.

5. Using the WebFOCUS Server Administration console, create and test your mainstreet connection to the database you just created.
6. The next series of steps are to be performed only if you are attaching to your PMF mainstreet connection using a different ID/Schema than the one you used in step 2. For example, if you created your database using schema PMF_ABC and your mainstreet connection is also using PMF_ABC, then you may proceed to step 7. Otherwise, using the WebFOCUS Server Administration console, open your Server Profile (edasprof.prf) and perform steps a and b:
   1. Ensure that the mainstreet connection is the default DB2 connection.
   2. Place a SQL DB2 SET CURRENT SQLID = 'ownerid' following the connection. This ensures that the SET CURRENT SQLID command is properly applied to the mainstreet connection

   Note: Once these steps have been completed, proceed to step 8.

7. Login to your PMF application using the supplied PMF administrator ID pmfadmin. A form similar to the following image prompts you for the Schema, Tablespace for tables, and Table Space for Indexes (for example, Indexspace).

   

   Enter the same values you used when running the db2.sql script in the previous step. You must enter proper values, otherwise PMF will not allow further processing

8. If you are attaching to your PMF mainstreet connection using a different ID/Schema than the one you used in step 2 and you performed the steps in step 6, you must remove the SET CURRENT SQLID from eadsprof.prf to avoid interfering with other applications after the PMF configuration/resynch functions are completed in step 7.

x

The procedures for creating a DB2/zOS database are similar to those in the previous procedure for DB2/UDB, but note the following:

1. The DB and tablespace creation must be run directly on the z/OS system. Sample SQL to do this is located in zosv8_env_defaults.sql in /apps/pmfdbms/Create_Database supplied with PMF.
2. The DDL must be run to create the stored procedures directly on the mainframe. The SQL to do this is located in zosv8_procedures.sql in /apps/pmfdbms/Create_Database supplied with PMF. The DB Owner ID needs select access to the SYSIBM.SYSTABLES catalog table.
3. The DB Owner ID must have full capabilities on objects owned by itself, for example, DBADM.
4. The DB Owner ID must have permissions to run the two stored procedures. Also, if the procedures are prepared as external SQL procedures (this is the only option in DB2 z/OS V8), then the id must also have access to the packages associated with the procedures. Refer to the CALL statement section in the DB2 z/OS SQL Reference for information on the authorization requirements.
5. To create and populate the PMF data mart, you must run the supplied script (zosv8.sql) from a DB2 client. The minimum client configuration is DB2 Connect.
   • If you are using DB2 Connect release 8.x, make sure you have applied FixPak 18 (also known as Version 8.2 FixPak 11) from IBM. You may obtain this at http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg24024075.
   • If you connect to the z/OS database from the client using an ID other than the owner ID, then that ID must have OS privileges to impersonate that actual owner ID.
6. If you create the mainstreet connection using the DB Owner for authentication inside of WebFOCUS, you do not need to make any changes (temporary or otherwise) to edasprof.
7. If the WebFOCUS connection is not using the DB Owner to connect, you must apply z/OS permissions (for example, RACF) on the connect ID in order to:
   • Be able to use the attach ID.
   • Be able to use the stored procedures.
8. You may need to edit the lsched.acx metadata files. lsdched is a cluster join used by PMF in the PMF interface to the Report Caster. The issue is that there can be multiple ReportCaster databases on any given LPAR and there may be no way to point to a database without editing ldsched.acx to qualify the tables.