The <upgrade> database configuration element

The <database> element in file database-config.xml contains, at most, a single occurrence of subelement <upgrade>. The use of the <upgrade> element is optional. This element specifies various options related to database upgrade. One important area of configuration is the degree of database parallelism to use in an Oracle database. Database parallelism refers to the ability of an Oracle database to execute a database SQL statement such as CREATE or INSERT using simultaneous, parallel slave processes.

The <upgrade> element has the following syntax. The following code sample shows required attributes in bold font.

<database>
  <upgrade ... >

     <mssql-db-ddl>

       <!-- Sets SQL Server database options at the global, database level -->
      <mssql-compression index-compression="NONE|PAGE|ROW" table-compression="NONE|PAGE|ROW/>
      <mssql-filegroups admin="string" index="string" lob="string" op="string" staging="string"
            typelist="string"/>
      
      <!-- Set SQL Server options for the named table, overrides values set at database level -->
      <mssql-table-ddl table-name="string">
        <mssql-index-ddl filter-where="string"index-compression="NONE|PAGE|ROW"
              index-filegroup="string" key-columns="string" partition-scheme="string"/>
        <mssql-table-compression index-compression="NONE|PAGE|ROW" table-compression="NONE|PAGE|ROW"/>
        <mssql-table-filegroups="string" index-filegroup="string" lob-filegroup
              table-filegroup="string"/>
      </mssql-table-ddl>

     </mssql-db-ddl>

     <ora-db-ddl>

       <!-- Sets Oracle database options at the global, database level -->
      <ora-compression index-compression="true|false" table-compression="ADVANCED|BASIC|NONE"/>
      <ora-lobs caching="true|false" type="BASIC|SECURE|SECURE_COMPRESSED/>
      <tablespaces admin="string" index="string" lob="string" op="string" staging="string"
            typelist="string"/>

       <!-- Sets Oracle options for the named table, overrides values set at the database level -->
      <ora-table-ddl table-name="string">
        <ora-index-ddl index-compression="true|false" index-tablespace="string" key-columns="string"/>
        <ora-lobs caching="true|false" type="BASIC|SECURE|SECURE_COMPRESSED/>>
        <ora-table-compression index-compression="true|false" table-compression="ADVANCED|BASIC|NONE">
        <ora-table-date-interval-partitioning datecolumn="string"
              interval="DAILY|MONTHLY|QUARTERLY|WEEKLY|YEARLY">
        <ora-table-hash-partitioning hash-columns="string" num-partitions="integer"/>
        <ora-table-tablespaces index-tablespace="string" lob-tablespace="string"
              table-tablespace="string"/>
      </ora-table-ddl>

     </ora-db-ddl>

     <versiontriggers dbmsperfinfothreshold="integer">
      <!-- Sets override options for the named database version trigger -->
      <versiontrigger extendedquerytracingenabled="true|false" name="string" 
            parallel-dml="true|false" parallel-query="true|false"
            queryoptimizertracingenabled="true|false" recordcounters="true|false"
            updatejoinorderedhint="true|false" updatejoinusemergehint="true|false"
            updatejoinusenlhint="true|false"/>
    </versiontriggers>

   </upgrade>
</database>

The following list describes the attributes that you can configure on the <upgrade> element. All of these attributes are optional.

allowUnloggedOperations

Boolean. Whether to disable logging of certain SQL operations during the database upgrade.

Valid values are:

  • true – Run the upgrade with minimal database redo logging and enable direct-path INSERT operations.
  • false – Run the upgrade with standard database redo logging.

The default is false.

Note: If you run the upgrade with attribute allowUnloggedOperations set to true, then you need to take a full database backup after the upgrade.

collectstorageinstrumentation       

(Oracle) Boolean. Whether PolicyCenter collects tablespace usage and object size data before and after the upgrade.

Valid values are:

  • true – PolicyCenter collects tablespace usage and size of segments such as tables, indexes and LOBs (large object binaries) before and after the upgrade. You can then compare the before and after values to find the utilization change caused by the upgrade.
  • false – PolicyCenter does not collect this data.

The default is false.

defer-create-nonessential-indexes   

Boolean. Whether to defer creation of non-essential indexes during the upgrade process until the upgrade completes and the application server is back up. Creation of non-essential indexes can add significant time to the upgrade duration.

Valid values are:

  • true – Defer creation of non-essential indexes during upgrade.
  • false – Do not defer creation of non-essential indexes during upgrade.

The default is false.

Non-essential indexes are:

  • Performance-related indexes that do not enforce constraints.
  • Indexes on the ArchivePartition column on all entities that PolicyCenter can archive.

If you choose to defer creation of non-essential indexes, PolicyCenter runs the Deferred Upgrade Tasks batch process (DeferredUpgradeTasks) as soon as the upgrade completes and the server starts up. See Deferred Upgrade Tasks batch process for more information.

deferDropColumns

(Oracle) Boolean. Whether to drop table columns removed during upgrade immediately or leave their removal to a later time. The database upgrade removes some columns. For Oracle, you can configure whether the removed columns are dropped immediately or are marked as unused. Marking a column as unused is a faster operation than dropping the column immediately.

However, as PolicyCenter does not physically drop the removed columns from the database, the space used by these columns is not released immediately to the table and index segments.

Valid values are:

  • true – Defer dropping removed columns until after the upgrade, possibly during off-peak hours of operation. The PolicyCenter database upgrade marks the removed columns as unused instead.
  • false – Drop the removed columns immediately, during the upgrade process.

The default is true.

degree-of-parallelism

(Oracle) Controls the degree of database parallelism that Oracle uses for INSERT, UPDATE, and DELETE database operations.

Valid values are:

  • 0 – Defers to Oracle to determine the degree of database parallelism for the operations that the attribute configures. The Oracle automatic parallel tuning feature determines the degree based on the number of CPU processors involved and the value set for the Oracle parameter PARALLEL_THREADS_PER_CPU.
  • 1 – Disables the parallel execution of DDL statements.
  • Positive integer less than 1000 – Database parallelism, with the specified value as the degree of parallelism.

The default is 4.

degree-parallel-ddl

(Oracle) Controls the degree of database parallelism that Oracle uses to execute DDL (Data Definition Language) statements during the database upgrade. Use to configure the degree of database parallelism for commands such as CREATE INDEX and the ALTER TABLE commands.

Valid values are:

  • 0 – Defers to Oracle to determine to determine the degree of database parallelism for the operations that the attribute configures. The Oracle automatic parallel tuning feature determines the degree based on the number of CPUs involved and the value set for the Oracle parameter PARALLEL_THREADS_PER_CPU.
  • 1 – Disables the parallel execution of DDL statements.
  • Positive integer less than 1000 – Database parallelism, with the specified value as the degree of parallelism.

The default is 4.

If you set the value of ora-parallel-dml to enable or enable_all (default), then you need to provide a value for attribute degree-of-parallelism as well.

encryptioncommitsize

Sets the commit size for rows requiring encryption. If one or more attributes use PolicyCenter encryption, the PolicyCenter database upgrade commits batches of encrypted values. The upgrade commits encryptioncommitsize rows at a time in each batch.

The default value of encryptioncommitsize varies based on the database type:

  • Oracle – 10000
  • SQL Server – 100

Test the upgrade on a copy of your production database before attempting to upgrade the actual production database. If the encryption process is slow, and you cannot attribute the slowness to SQL statements in the database, try adjusting the encryptioncommitsize attribute. After you optimized the performance of the encryption process, use that value of encryptioncommitsize as you upgrade your production database.

ora-parallel-dml

(Oracle) Controls database parallelism usage by Oracle in the execution of DML (Data Manipulation Language) operations.

Valid values are:

  • disable – Oracle does not execute DML statements in parallel during upgrade.
  • enable – Oracle executes DML statements in parallel during upgrade, if configured to do so.
  • enable_all – Oracle executes DML statements in parallel during upgrade in all cases, unless turned off in the code or through configuration.

The default is enable_all.

If you set the value of ora-parallel-dml to enable or enable_all, then you need to provide a value for attribute degree-of-parallelism as well.

Note: The value of this attribute interacts with the parallel-dml attribute on the <versiontrigger> element.

ora-parallel-query

(Oracle) Controls parallel query usage by Oracle during a database upgrade.

Valid values are:

  • disable – Oracle does not use parallel queries during upgrade.
  • enable – Oracle uses parallel queries during upgrade, if configured to do so.

The default is enable.

The value of this attribute interacts with the parallel-query attribute on the <versiontrigger> element.

sqlserverCreateIndexSortInTempDB

(SQL Server) Boolean. Whether SQL Server stores temporary sort results in tempdb. By using tempdb for sort runs, disk input and output is typically faster, and the created indexes tend to be more contiguous. Valid values are:

  • true – SQL Server stores sort results in tempdb.
  • false – SQL Server stores sort results in the destination filegroup.

The default is false.

If you set sqlserverCreateIndexSortInTempDB to true, you must have enough disk space available to tempdb for the sort runs, which, for the clustered index, includes the data pages. You must also have sufficient free space in the destination filegroup to store the final index structure, because SQL Server creates the new index before deleting the old index.

Refer to the following web site for details on the requirements to use tempdb for sort results.

updatestatistics

(Oracle) Boolean. Whether to update table statistics during upgrade. The overall time that it takes to upgrade the database is shorter if the database upgrade does not update statistics.

Valid values are:

  • true – Enables the upgrader to update statistics on changed objects. It also allows the upgrader to maintain column level statistics consistent with what is allowed in the code, data model, and configuration.
  • false – Disable statistics generation during the upgrade.

If PolicyCenter does not update statistics during the upgrade:

  • It reports a warning that recommends that you run the database statistics batch process (DBStats) in incremental mode during the next maintenance window.
  • It updates the Server Tools Upgrade and Versions screen to show that the upgrade did not update statistics.

If PolicyCenter does generate statistics during the upgrade, it updates the Upgrade and Versions screen to report the runs of the statistics batch process, including incremental runs.

Note: Guidewire recommends that you run statistics in full mode after an upgrade to a major PolicyCenter version.

verifyschema

Boolean. Whether PolicyCenter automatically performs a verification of the database schema after a database upgrade. If the verification process determines that the logical and physical data model are not in agreement, the process generates schema verification errors in the Upgrade Info report.

The default value for this attribute is true. Guidewire recommends that you do not change the default value so that the schema verifier always executes after a database upgrade.

It is also possible to initiate the database schema verification process using the following system_tools command.

  • system_tools -password password -verifydbschema

The <upgrade> element has the following subelements. Each of these elements is optional. There is, at most, a single occurrence of each of these subelements on the <upgrade> element.

mssql-db-ddl

Specifies options for SQL Server database DDL (Data Definition Language) statements.

ora-db-ddl

Specifies options for Oracle database DDL (Data Definition Language) statements.

versiontriggers

Specifies options for named version triggers.