13.3 Manually Generating the Database Schema

You can recreate the database tables after installation without having to reinstall. This section helps you create the database schema.

1. Stop Tomcat by using the services.msc file.

2. (Conditional) Create a new database.

   If your database is running on a separate server, you must connect to that database server. For a remotely installed PostgreSQL database, verify that the database server is running. To connect to a remote PostgreSQL database, see Connecting to a Remote PostgreSQL Database. If you are connecting to an Oracle database, ensure that you have created an Oracle database instance in that database server. For more information, see Oracle documentation.

3. Add the required roles to the database using the following SQLs from C:\NetIQ\idm\apps\IdentityReporting\sql.

   • PostgreSQL: create_dcs_roles_and_schemas.sql and create_rpt_roles_and_schemas.sql

   • Oracle: create_dcs_roles_and_schemas-orcale.sql and create_rpt_roles_and_schemas-orcale.sql

4. To create IDM_RPT_DATA, IDM_RPT_CFG and IDMRPTUSER roles, perform the following actions:

   • PostgreSQL: Run the following commands in the given order:

     Select CREATE_DCS_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_DATA>');

     Select CREATE_RPT_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_CFG>', '<Set pwd for IDMRPTUSER>');

   • Oracle: Run the following commands in the given order:

     begin
     CREATE_DCS_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_DATA>');
     end;
     
     begin
     CREATE_RPT_ROLES_AND_SCHEMAS('<Set pwd for IDM_RPT_CFG>', '<Set pwd for IDMRPTUSER>');
     end; 

5. Add get_formatted_user_dn function to the IDM_RPT_DATA schema.

   1. Log in to the database as a database admin user.

   2. Add get_formatted dn function from C:\NetIQ\idm\apps\IdentityReporting\sql.

   Locate get_formatted_user_dn.sql for PostgreSQL and get_formatted_user_dn-oracle.sql for Oracle.

6. Clear the database checksums for the following .sql files located in C:\NetIQ\idm\apps\IdentityReporting\sql:

   • DbUpdate-01-run-as-idm_rpt_cfg.sql

   • DbUpdate-02-run-as-idm_rpt_cfg.sql

   • DbUpdate-03-run-as-idm_rpt_data.sql

   • DbUpdate-04-run-as-idm_rpt_data.sql

   • DbUpdate-05-run-as-idm_rpt_data.sql

   • DbUpdate-06-run-as-idm_rpt_cfg.sql

   1. Append the following line at the beginning of each SQL:

      update DATABASECHANGELOG set MD5SUM = NULL; 

      The modified content should look similar to the following:

      -- *********************************************************************
      -- Update Database Script
      -- *********************************************************************
      -- Change Log: IdmDcsDataDropViews.xml
      -- Ran at: 2/23/18 5:17 PM
      -- Against: IDM_RPT_CFG@jdbc:oracle:thin:@192.99.170.20:1521/orcl
      -- Liquibase version: 3.5.1
      -- *********************************************************************
      update databasechangelog set md5sum = null; 

   2. Run each SQL with the corresponding user.

7. Commit the changes to the database.

8. Start Tomcat by using the services.msc file.