B.0 Using a SQL Script to Update the PostgreSQL Schema

To update your PostgreSQL database schema using the included SQL script, complete the following steps.

NOTE:You should only use the SQL script to update the schema if your organization requires users make all database updates using scripts or if the User Application tables were originally created or updated using the SQL script option during the Identity Manager Roles Based Provisioning Module 4.0.2 installation process.

  1. Navigate to the User Application deploy directory within the JBoss installation. For example: /opt/novell/idm/jboss/server/IDMProv/deploy

  2. In the deploy directory, use a text editor to open the IDMProv-ds.xml file.

  3. In the <connection-url> element, append “?compatible=true” to the existing text. For example:

    <connection-url>jdbc:postgresql://localhost:5432/rbpmprov?compatible=true</connection-url>

  4. Save and close the IDMProv-ds.xml file.

  5. In a command prompt, navigate to the deploy directory and enter the following command:

    unzip IDMProv.war WEB-INF/web.xml

  6. Use a text editor to open the WEB-INF/web.xml file and set the value of the create-db-on-startup parameter to false.

  7. Save and close the web.xml file.

  8. In the command prompt, enter the following command:

    zip -u0 IDMProv.war WEB-INF/web.xml

  9. Delete the WEB-INF directory and all its contents.

  10. Navigate to the User Application installation directory. For example: /opt/novell/idm

  11. In a text editor, open the Novell-Custom-Install.log file.

  12. Search for the following text:

    ***************************************************
If a failure is encountered while creating the tables, verify that this string is correct
If not , you can modify this string and copy/paste to a command line to run
***************************************************

  13. At a command prompt, navigate to the User Application directory.

  14. Copy the command specified in the Novell-Custom-Install.log file and paste the command into the command prompt.

  15. Replace the asterisks (*) within the command with the database username and password and changing --contexts="prov,newdb" to --contexts="prov,updatedb" if necessary. Ensure the name of the SQL file is unique.

  16. Before running the command, add the following new property before the -jar option:

    -Ddriver.dn="DriverDN"

    Where DriverDN is the full DN of the User Application driver. For example:

    /opt/novell/idm/jre/bin/java -Xms256m -Xmx256m -Dwar.context.name=IDMProv -Ddriver.dn="cn=User Application Driver,cn=driverset1,o=system" -jar /opt/novell/idm/liquibase.jar --databaseClass=liquibase.database.core.PostgresDatabase --driver=org.postgresql.Driver --classpath=/opt/novell/idm/Postgres/postgresql-8.4-701.jdbc4.jar:/opt/novell/idm/jboss/server/IDMProv/deploy/IDMProv.war --changeLogFile=DatabaseChangeLog.xml --url="jdbc:postgresql://localhost:5432/idmuserappdb" --contexts="prov,updatedb" --logLevel=info --logFile=/opt/novell/idm/db.out --username=idmadmin --password=password updateSQL > /opt/novell/idm/UA-update.sql

  17. Press Enter.

  18. Provide the generated SQL file for your Identity Manager environment to your database administrator to import into the database server.

  19. After the database administrator imports the SQL file, start the User Application server.