Data Virtuality Upgrade Utility (Exporter)

You are looking at an older version of the documentation. The latest version is found here.

Please use the Data Virtuality Upgrade Utility (Exporter) supplied with your target server version to upgrade from an older version of the Data Virtuality Server.

This page gives a list of supported configuration options, some usage examples, and information on facades (specially designed sets of queries to facilitate upgrading from one version to another with newly added columns), JDK usage, and JDBC driver usage.

Properties

Mandatory Properties

Property	Description
`--username`	User name used to connect to the server
`--password`	User password
`--host`	The host where the Data Virtuality Server is running

Optional Properties

To view the full table, click the expand button in its top right corner

Property	Default value	Description
`--port`	`31000`	Server port
`--ssl`	`FALSE`	Indicates whether to use a secure connection or not
`--database`	`datavirtuality`	The Data Virtuality VDB schema
`--file`	`dv-export-<current-date>.sql`	Destination file name
`--dv-facade-file`	(none)	SQL facade file in the file system
`--skip-aggr`	`FALSE`	Indicates whether to skip aggregation optimizations or not
`--ds-validation`	`FALSE`	Indicates whether to validate data sources on import or not. If enabled, data sources are exported with the `createDatasource`/`Connection` statements or `importDatasource`/`Connection` otherwise. Please note that the import will fail if validation is enabled and the data source is unreachable
`--opt-validation`	`FALSE`	Indicates whether to validate optimizations on import or not. If enabled, optimizations are exported with the `createOptimization` statement or `importOptimization` otherwise. Please note that it is impossible to export materialized tables state if the validation is enabled
`--export-mattable-state`	`TRUE`	Indicates whether to export the state of materialized tables or not. It is used when the new Data Virtuality installation uses the same internal DWH database with materialized tables. Please note that this option requires the optimization validation (see previous entry) to be disabled
`--view-proc-validation`	`FALSE`	Indicates whether to validate virtual views and procedures on import or not. If enabled, virtual views and procedures are exported by printing definitions directly to the export script or wrapping them with the `importView`/`Procedure` otherwise
`--use-maintenance-mode`	`TRUE`	Runs export in maintenance server mode
`--purge-system-data`	`FALSE`	Indicates whether to purge the system data or not. If enabled, adds the script to purge the existing data at the beginning of the export script. With this option, the following objects will be purged: Data sources, Connections, Procedures, VirtualSchemas, ViewDefinitions, ScheduleJobs (except 'cleanup' and 'performance'), Schedules (except 'performance'), RecommendedOptimizations, Users, and Roles
`--use-model-file`	`FALSE`	Indicates whether to use a different file for "model" export or not. If enabled, separate files will be used for exporting data sources, virtual schemas with views and procedures, and the rest
`--export-jboss-settings`	`FALSE`	Indicates whether to export JBoss settings or not. If enabled, JBoss settings are exported to separate files
`--java-7`	`FALSE`	Needed to connect to server versions that use Java 7 (2.3 and below) when using an SSL connection
`--export-smtp-configuration`	`TRUE`	If enabled, SMTP configuration is exported, otherwise not
`--export-global-job-email-notifications`	`TRUE`	If enabled, Ggobal job email notifications are exported, otherwise not
`--export-job-specific-email-notifications`	`TRUE`	If enabled, job-specific email notifications are exported, otherwise not
`--export-global-job-slack-notifications`	`TRUE`	If enabled, global job Slack notifications are exported, otherwise not
`--export-job-specific-slack-notifications`	`TRUE`	If enabled, job-specific Slack notifications are exported, otherwise not

export-global-job-slack-notifications and export-job-specific-slack-notifications properties are available since v3.3

Usage Examples

1) The server has a standard port and database name:

BASH

export.bat --username admin --password admin --host localhost

2) The server has a custom configuration and custom destination script filename specified:

BASH

export.bat --username admin --password admin --host localhost --port 31002 --ssl true --database dvbase-1 --file upgrade-server1-to-server2.sql

3) Skipping export of aggregation optimizations:

BASH

export.bat --username admin --password admin --host localhost --skip-aggr true

4) Export using create* statements instead of import*:

BASH

export.bat --username admin --password admin --host localhost --ds-validation true --opt-validation true --export-mattable-state false --view-proc-validation true

Facade

An SQL facade is a context-specific interface for resolving compatibility issues between the source server and the target exporter.

Facades come in handy if the new version of the Data Virtuality Server adds new columns to system tables. If the newly added columns are non-nullable, the Data Virtuality Upgrade Utility must fill them with valid values. This is precisely what the facades are for: they are a set of queries that create dummy data.

Here is an example of such a facade with a detailed explanation:

SQL

getRecommendedIndexes=SELECT columnName, id, parentRecOptID, recOptID, type, cast('SINGLE' as varchar(30)) AS kind, cast(1 as biginteger) AS freq, cast('NEW' as varchar(20)) AS status FROM SYSADMIN.RecommendedIndexes
 
getRecommendedOptimizations=SELECT Enabled, Freq, ID, MatchDescriptor, Type, dwhState, dwhStateComment, lastMaterialized, lastReplicationState, lastReplicationStateComment, lastUsed, matTablePrefix, nextReplicationState, nextSelectState, sourceState, sourceStateComment, cast('ALL' as varchar(64)) AS allowIndexCreationByType, cast('NEW_ACCEPTED' as varchar(64)) AS allowIndexCreationByStatus FROM SYSADMIN.RecommendedOptimizations

In this example, we are updating two procedures, getRecommendedIndexes and getRecommendedOptimizations. In the target version, the first procedure receives new fields kind, freq, status, and they are assigned arbitrary default values. The second procedure has two such newly added fields: allowIndexCreationByType and allowIndexCreationByStatus, and they, too, are assigned some default values.

If the upgrade is performed from one version to the subsequent version, a single-step facade may be required. If the upgrade skips several versions, you may need either a single-step facade (if only one of the versions being stepped over requires facade usage) or a cumulative facade comprised of single-step facades for each (or some) of the steps. Please contact our support team for more information and help with getting the facade required for upgrading your instance to the version you need.

JDK Usage

If the JDK is included in the server and its path is %dvserver%/JDK, and the exporter's path is %dvserver%/bin, then it will use the server Java when running the export.

If the JDK is not included in the server or the exporter is not running in %dvserver%/bin, it will use the system Java. This will be reflected in the log:

TEXT

f:\datavirtuality\exporter\1_10\cli-export-1.0>export.bat
JDK is not defined at f:\datavirtuality\exporter\JDK\bin
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF8 -Duser.language=en -Duser.country=US
INFO: Export tool v.1.0 (rev.5525 built on 2016-07-26 03:54) [Tue Jul 26 09:54:56 MSK 2016]
=================================================================================================

If the JDK is not included in the server, or the exporter is not running in %dvserver%/bin, and system Java does not exist, you will see the following warning in the log:

TEXT

halina@halina-VirtualBox:/media/datavirtuality/exporter/1_11/cli-export-1.0$ ./export.sh
JDK is not defined at /media/datavirtuality/exporter/JDK/bin
JAVA_HOME is not set. Unexpected results may occur.
Set JAVA_HOME to the directory of your local JDK to avoid the message.
./export.sh: line 37: java: command is not found

JDBC Driver Usage

Sometimes, especially when upgrading between major server versions (e.g., from 2.1 to 2.3), it may make sense to use a specific JDBC driver to ensure that the data in the exported script is complete, correct, and usable with the target server (e.g., using the CLI-exporter v2.3 with a JDBC driver v2.1). You can specify the path to the JDBC driver in the jdbcDriverPathvariable of the export.bat/export.sh file.

Do not enclose the path in double quotes, even if it contains a space!