Hyperstage MySQL-based to

PostgreSQL-based Migration
Release 8.1 Version 04

4501575.0815
Active Technologies, EDA, EDA/SQL, FIDEL, FOCUS, Information Builders, the Information Builders logo, iWay, iWay Software,
Parlay, PC/FOCUS, RStat, Table Talk, Web390, WebFOCUS, WebFOCUS Active Technologies, and WebFOCUS Magnify are
registered trademarks, and DataMigrator and Hyperstage are trademarks of Information Builders, Inc.

Adobe, the Adobe logo, Acrobat, Adobe Reader, Flash, Adobe Flash Builder, Flex, and PostScript are either registered
trademarks or trademarks of Adobe Systems Incorporated in the United States and/or other countries.

Due to the nature of this material, this document refers to numerous hardware and software products by their trademarks.
In most, if not all cases, these designations are claimed as trademarks or registered trademarks by their respective companies.
It is not this publisher's intent to use any of these names generically. The reader is therefore cautioned to investigate all
claimed trademark rights before using any of these names other than to refer to the product described.

Copyright © 2015, by Information Builders, Inc. and iWay Software. All rights reserved. Patent Pending. This manual, or parts
thereof, may not be reproduced in any form without the written permission of Information Builders, Inc.
Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Related Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Customer Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Information You Should Have . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
User Feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Information Builders Consulting and Training . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Hyperstage Technical Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2. Installing and Configuring Hyperstage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Installing Hyperstage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Procedure: How to Install Hyperstage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Configuring the Hyperstage (PG) Adapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Procedure: How to Configure the Hyperstage (PG) Adapter. . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Configuring Hyperstage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Configuration Tips and Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
3. Hyperstage MySQL to PostgreSQL Migration Using the External Migrator . . . . . . . 21
Hyperstage MySQL to PostgreSQL Migrator (“External Migrator”) . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Limitations and Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Using the External Migrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
MySQL to PostgreSQL Data Type Mappings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy . . . . . . . . . . . . . 27
Migration Using Quick ETL Copy With the MySQL ODBC Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Procedure: How to Install and Configure the MySQL ODBC Driver. . . . . . . . . . . . . . . . . . . . . . 27
Procedure: How to Configure the Hyperstage MySQL ODBC Adapter Connection. . . . . . . . . 33
Procedure: How to Run Quick ETL Copy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Migration Using Quick ETL Copy With the MySQL JDBC Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Procedure: How to Install and Configure the MySQL JDBC Driver. . . . . . . . . . . . . . . . . . . . . . 39
Procedure: How to Configure the Hyperstage MySQL JDBC Adapter Connection. . . . . . . . . . 39
Procedure: How to Run Quick ETL Copy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Customer Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Hyperstage MySQL-based to PostgreSQL-based Migration 3

Contents

4 WebFOCUS
Preface
This document describes the tools for migrating from Hyperstage MySQL to Hyperstage PostgreSQL.

How This Manual Is Organized

This manual includes the following chapters:

Chapter/Appendix Contents

1 Introduction Describes the technical requirements for installing

Hyperstage.

2 Installing and Configuring Describes the installation and configuration steps to

Hyperstage install and configure Hyperstage.

3 Hyperstage MySQL to Describes how to use the External Migrator to migrate

PostgreSQL Migration Using from MySQL to PostgreSQL.
the External Migrator

4 Hyperstage MySQL to Describes how to use Quick ETL Copy to migrate from
PostgreSQL Migration Using MySQL to PostgreSQL.
Quick ETL Copy

Documentation Conventions
The following table describes the documentation conventions that are used in this manual.

Convention Description

THIS TYPEFACE Denotes syntax that you must enter exactly as shown.
or
this typeface

Represents a placeholder (or variable) in syntax for a value that you

this typeface or the system must supply.

Indicates a default setting.

underscore

Hyperstage MySQL-based to PostgreSQL-based Migration 5

Related Publications

Convention Description

this typeface Represents a placeholder (or variable), a cross-reference, or an

important term. It may also indicate a button, menu item, or dialog
box option that you can click or select.

Key + Key Indicates keys that you must press simultaneously.

{ } Indicates two or three choices. Type one of them, not the braces.

[ ] Indicates a group of optional parameters. None are required, but

you may select one of them. Type only the parameter in the brackets,
not the brackets.

| Separates mutually exclusive choices in syntax. Type one of them,

not the symbol.

... Indicates that you can enter a parameter multiple times. Type only
the parameter, not the ellipsis (...).

. Indicates that there are (or could be) intervening or additional

commands.
.

Related Publications
Visit our Technical Content Library at http://documentation.informationbuilders.com. You can also
contact the Publications Order Department at (800) 969-4636.

Customer Support
Do you have questions about this product?

Join the Focal Point community. Focal Point is our online developer center and more than a
message board. It is an interactive network of more than 3,000 developers from almost every
profession and industry, collaborating on solutions and sharing tips and techniques. Access Focal
Point at http://forums.informationbuilders.com/eve/forums.

6 WebFOCUS
Preface

You can also access support services electronically, 24 hours a day, with InfoResponse Online.
InfoResponse Online is accessible through our website, http://www.informationbuilders.com. It
connects you to the tracking system and known-problem database at the Information Builders
support center. Registered users can open, update, and view the status of cases in the tracking
system and read descriptions of reported software issues. New users can register immediately
for this service. The technical support section of www.informationbuilders.com also provides
usage techniques, diagnostic tips, and answers to frequently asked questions.

Call Information Builders Customer Support Services (CSS) at (800) 736-6130 or (212) 736-
6130. Customer Support Consultants are available Monday through Friday between 8:00 a.m.
and 8:00 p.m. EST to address all your questions. Information Builders consultants can also give
you general guidance regarding product capabilities. Please be ready to provide your six-digit site
code number (xxxx.xx) when you call.

To learn about the full range of available support services, ask your Information Builders
representative about InfoResponse Online, or call (800) 969-INFO.

Information You Should Have

To help our consultants answer your questions effectively, be prepared to provide the following
information when you call:

Your six-digit site code (xxxx.xx).

Your WebFOCUS configuration:

The front-end software you are using, including vendor and release.

The communications protocol (for example, TCP/IP or HLLAPI), including vendor and release.

The software release.

Your server version and release. You can find this information using the Version option in
the Web Console.

The stored procedure (preferably with line numbers) or SQL statements being used in server
access.

The Master File and Access File.

The exact nature of the problem:

Are the results or the format incorrect? Are the text or calculations missing or misplaced?

Provide the error message and return code, if applicable.

Is this related to any other problem?

Hyperstage MySQL-based to PostgreSQL-based Migration 7

User Feedback

Has the procedure or query ever worked in its present form? Has it been changed recently?
How often does the problem occur?

What release of the operating system are you using? Has it, your security system,
communications protocol, or front-end software changed?

Is this problem reproducible? If so, how?

Have you tried to reproduce your problem in the simplest form possible? For example, if you
are having problems joining two data sources, have you tried executing a query containing
just the code to access the data source?

Do you have a trace file?

How is the problem affecting your business? Is it halting development or production? Do you
just have questions about functionality or documentation?

User Feedback
In an effort to produce effective documentation, the Technical Content Management staff welcomes
your opinions regarding this document. You can contact us through our website
http://documentation.informationbuilders.com/connections.asp.

Thank you, in advance, for your comments.

Information Builders Consulting and Training

Interested in training? Information Builders Education Department offers a wide variety of training
courses for this and other Information Builders products.

For information on course descriptions, locations, and dates, or to register for classes, visit our
website (http://education.informationbuilders.com) or call (800) 969-INFO to speak to an Education
Representative.

8 WebFOCUS
Chapter 1 Introduction

Hyperstage combines the Hyperstage storage engine with PostgreSQL server implementation.
Hyperstage consists of several layers. The upper layers are provided by the PostgreSQL
server implementation, and the lower layers are provided by Hyperstage.

Hyperstage ships with the full PostgreSQL binaries required. PostgreSQL is used to store
catalog information (as with other storage engines). You can use the PostgreSQL instance
for other purposes, but joining PostgreSQL and Hyperstage tables may result in reduced
performance as the PostgreSQL query engine will be used.
In this chapter:

Hyperstage Technical Requirements

Hyperstage Technical Requirements

Before installing Hyperstage, review the following technical requirements.

Requirements Description

Platforms Windows Server® 2003

Windows Server 2008

Red Hat® Enterprise Linux® 5.x and 6.x

CentOS® 5.x and 6.x

Debian® 6

Novell® SUSE® Linux Enterprise 11

Processor Architecture Intel® 64-bit

AMD® 64-bit

Hyperstage MySQL-based to PostgreSQL-based Migration 9

Hyperstage Technical Requirements

Requirements Description

For Personal Evaluation and Application Development

CPU Speed 1.8GHz minimum, 2.0GHz or faster dual core or quad

core recommended

Memory 2GB minimum, 4GB or higher recommended

For Multi-User Evaluation or Production Deployment

CPU Speed 2.0GHz minimum, 2.0GHz or faster dual core or quad

core recommended

Memory 4GB minimum, 8GB or higher recommended

10 WebFOCUS
Chapter 2 Installing and Configuring Hyperstage

The following section describes the installation and configuration steps for Hyperstage.
In this chapter:

Installing Hyperstage

Configuring the Hyperstage (PG) Adapter

Configuring Hyperstage

Installing Hyperstage
Hyperstage is packaged as part of the Hyperstage version of the Reporting Server installation.
This version of the Reporting Server installation installs a Reporting Server and Hyperstage, and
configures the Reporting Server for use with Hyperstage.

Procedure: How to Install Hyperstage

1. Download the installation package for Hyperstage for the desired platform.
2. Follow the Reporting Server installation instructions in the Server Installation manual.
3. By default, Hyperstage data directories (ib_data and pg_data) will be installed under the
ibi\HyperstagePG directory.

Hyperstage MySQL-based to PostgreSQL-based Migration 11

Installing Hyperstage

In order to customize the location of the Hyperstage data directories, select the Customize
default directory locations check box in the Select the Program Folder and Standard Location
Prompt dialog box, as shown in the following image.

12 WebFOCUS
2. Installing and Configuring Hyperstage

4. Enter the desired Hyperstage directory location, as shown in the following image.

Hyperstage MySQL-based to PostgreSQL-based Migration 13

Configuring the Hyperstage (PG) Adapter

5. By default, the HTTP Listener Port on the Configure Basic Server Information dialog box is
8121, as shown in the following image.

The port for Hyperstage will automatically configure to three port numbers higher than the
HTTP Listener Port (for example, 8124).

Configuring the Hyperstage (PG) Adapter

When installing the Hyperstage version of the WebFOCUS Reporting Server, the Hyperstage (PG)
adapter will automatically be configured. If the WebFOCUS Reporting Server and Hyperstage
version of the Reporting Server exist on different boxes, then the Hyperstage (PG) adapter needs
to be manually configured on the WebFOCUS Reporting Server pointing to the Hyperstage port.

Procedure: How to Configure the Hyperstage (PG) Adapter

1. Launch the Web Console and click the Adapters tab.

14 WebFOCUS
2. Installing and Configuring Hyperstage

2. Expand Available and then expand the SQL folder.

3. Right-click Hyperstage (PG) and click Configure, as shown in the following image.

The Add Hyperstage (PG) Configuration window opens.

4. Complete the following fields:

In the Connection Name box, type a name for the connection.

In the URL box, type the URL to the Hyperstage port and database, for example,
jdbc:postgresql://hsserver:28124/webfocus.

In the Driver Name box, type org.postgresql.Driver.

In the IBI_CLASSPATH box, add the location of the JDBC Jar file.

Note: The PostgreSQL jar files exist in the home\hs\java directory of the Hyperstage
version of the Reporting Server, for example, \ibi\srv77\home06HSstandalone\hs\java.
These could be copied to a location on the machine where the WebFOCUS Reporting
Server resides, for example, C:\ibi\SQLJDBC\postgresql-9.2-1003.jdbc3.jar and
C:\ibi\SQLJDBC\postgresql-9.2-1003.jdbc4.jar.

In the Home Directory box, enter the location of the home directory for Hyperstage (PG).

Note: The location is the home\hs directory of the Hyperstage version of the Reporting
Server, for example, D:\ibi\srv77\home06HSstandalone\hs.

Hyperstage MySQL-based to PostgreSQL-based Migration 15

Configuring the Hyperstage (PG) Adapter

In the Tools Directory box, type the location of the tools directory for Hyperstage (PG).

Note: The location is the home\hs\bin directory of the Hyperstage version of the
Reporting Server, for example, D:\ibi\srv77\home06HSstandalone\hs\bin.

Type the User and Password for the PostgreSQL database. By default, the credentials
are srvadmin/srvadmin.

The following image shows the window with all of the fields completed.

5. Click Configure.

16 WebFOCUS
2. Installing and Configuring Hyperstage

Configuring Hyperstage
The Hyperstage configuration file is called brighthouse.ini and is located in the ib_data subdirectory
within the Hyperstage Data directory installation directory (for example,
C:\ibi\HyperstagePG\ib_data). The configuration file is a text file containing the Hyperstage
configuration parameters.

Each parameter is shown on a separate line and uses the following form:
ParameterName=ParameterValue
If a parameter is not present in the configuration file or if the configuration file does not exist,
the default values are used. Blank lines and comments (lines starting with #) are ignored.

Be sure to customize the following parameters to optimize performance. These tuning parameters
are case-sensitive and must be typed as shown in the following table.

Parameter Syntax Value Description

ServerMainHeapSize=size Not less than Size of the main memory heap in the
320 server process, in MB. The larger the
heap size, the more effectively the
Default: 600
server works. However, the sum of the
heap sizes in the server and the loader
should not exceed physical memory
installed in the machine. Otherwise,
performance decreases radically.

LoaderMainHeapSize=size Not less than Size of the memory heap in the loader
320 process, in MB. The sum of the heap
sizes in the server and the loader
Default: 320
should not exceed physical memory
installed in the machine. Otherwise,
performance decreases radically.

CacheFolder=directory Directory name This is a mandatory parameter. Path

to the directory where temporary files
Default: none
will be created and stored. This is set
as one of the installation script
parameters.

Hyperstage MySQL-based to PostgreSQL-based Migration 17

Configuring Hyperstage

Note: The values are commented out (preceded by #) in the brighthouse.ini file, which causes
them to default to the application minimum allowed values of 600 and 320 for
ServerMainHeapSize and LoaderMainHeapSize, respectively.

The following table describes additional Hyperstage parameters.

Parameter Syntax Value Description

KNFolder=directory Directory name Directory where the Knowledge Grid is stored. If not
specified, these files are located in a subdirectory of
Default:
the data directory. Allow free space of at least 1% of
BH_RSI_Repository
database size (compressed).

ControlMessages=number 0, 1, 2, 3, 4, 5 Set to 2 to turn the control messages on with

timestamps. This is usually needed by Hyperstage to
Default: 0 (lowest level of
support performance investigation.
detail)
For descriptions of other Control Messages levels, see
https://www.infobright.org/index.php//ICE_Wiki/wiki-
4/troubleshooting/query-execution-log/.

Configuration Tips and Examples

Important: You must properly configure your memory settings to ensure optimal performance.

The following table shows sample, recommended memory configurations for different systems.

System Memory Server Main Heap Size Loader Main Heap Size

64GB 48000 800

48GB 32000 800

32GB 24000 800

16GB 10000 800

8GB 4000 800

4GB 1300 400

18 WebFOCUS
2. Installing and Configuring Hyperstage

System Memory Server Main Heap Size Loader Main Heap Size

2GB 600 320

In most cases, the loader does not benefit from larger memory settings. However, increasing the
LoaderMainHeapSize can help when:

A table to be loaded has very long text values.

or

The table has many columns (for example, 1000 columns).

You can use more memory at import if you are planning to execute several concurrent load tasks
to different data tables. However, disk access may become a bottleneck.

ServerMainHeapSize should be as large as possible, but safely smaller than the amount of
physical memory on the machine. If performance decreases because of memory swapping by the
operating system, try to set lower heap sizes. We also recommend decreasing the heap size if
many users are running queries in parallel.

Note:

Hyperstage may use additional memory for heavy loads or queries. Also, other applications
on your server will use memory for their processes. It is important that the total of
ServerMainHeapSize and LoaderMainHeapSize is less than the total available physical
memory. If the system needs to swap memory, performance will be severely impacted.

For information on configuring the Hyperstage adapter and connecting to the database
using the Reporting Server, see Using the Adapter for Hyperstage in the Adapter
Administration manual.

Hyperstage MySQL-based to PostgreSQL-based Migration 19

Configuring Hyperstage

20 WebFOCUS
Chapter 3 Hyperstage MySQL to PostgreSQL Migration
Using the External Migrator

The following section describes how to use the External Migrator to migrate Hyperstage
MySQL data to Hyperstage PostgreSQL.
In this chapter:

Hyperstage MySQL to PostgreSQL Migrator (“External Migrator”)

Limitations and Notes

Using the External Migrator

MySQL to PostgreSQL Data Type Mappings

Hyperstage MySQL to PostgreSQL Migrator (“External Migrator”)

The Hyperstage External Migrator allows for migration of Hyperstage MySQL data to Hyperstage
for PostgreSQL. The current version of the utility works under the following basic assumptions
and conditions.

Assumptions and Conditions

Migrates data from version 4 (latest Hyperstage MySQL) to data version 5 (latest PostgreSQL
version).

Destination data directories must be created for PostgreSQL.

Migration of text types is supported under the following conditions (all conditions must be
satisfied):

If UTF-8 is a charset in all text columns and no other charset is used.

If binary collations used.

If max text length from a column does not exceed 16K.

Both PostgreSQL and the MySQL instances must be offline.

Columns of time types must not contain 0 (zeros).

Specific data types will require more space.

Hyperstage MySQL-based to PostgreSQL-based Migration 21

Limitations and Notes

After the conversion to PostgreSQL, VARCHAR(n) types will require more than 64KB for a
single value. Hyperstage for MySQL using UTF-8 may have to up 3 bytes whereas Hyperstage
for PostgreSQL uses up to 4 bytes. The maximum value for n is 16K characters.

Limitations and Notes

Table migration is done by copying the data. In-place migration is not supported.

Tables with decomposition rules are currently not supported.

The External Migrator will change all ‘0000-00-00’ DATE values to ‘100-01-01’.

The External Migrator will change all ‘0000-00-00 00:00:00’ DATETIME and TIMESTAMP
values to ‘100-01-01 00:00:00’ and ‘1970-01-01 00:00:00’.

The External Migrator will apply a common character set to all columns being migrated. This
is necessary because Hyperstage for PostgreSQL requires that all columns within a given
database have the same character set.

The External Migrator will recalculate Data Pack Nodes and Knowledge Nodes.

Tables previously using LOOKUP columns will be migrated to DIMENSION columns.

There is no support for Default values within PostgreSQL. Therefore, this modifier will not be
migrated.

Using the External Migrator

To use the External Migrator, run the following command:
./ibextmigrator options
The available External Migrator options are listed in the following table.

Option Description

-h [ - -help] Prints help messages.

-f [ - -force ] Continues migration, even if an error occurs.

-v [ - -verbose ] Shows more details.

-b [ - -pg-bin ] arg PostgreSQL installation path.

-u [ - -pg-user ] arg PostgreSQL user used to create the migration database.

22 WebFOCUS
3. Hyperstage MySQL to PostgreSQL Migration Using the External Migrator

Option Description

-s [ - -src-datadir ] arg Source MySQL datadir.

-i [ - -dst-ibdatadir ] arg Destination Hyperstage Server datadir (ib_data).

-p [ - -dst-pgdatadir ] arg Destination PostgreSQL datadir (pg_data).

-d [ - -dst-db ] arg Destination PostgreSQL database name.

-t [ - -tables ] arg List of tables to migrate in the form "db1.t1 db2.t3 db2.*" where
* implies migration of every table in the database. If not specified,
the External Migrator will attempt to migrate the entire datadir.

-c [ - -dst-schema ] arg Name of destination schema to which tables specified with the -t
(=public) option should be migrated. Defaults to public.

- -connection-db arg Database that External Migrator will use to connect to PostgreSQL.
(=template1)

- -force-charset- Specifying this option will turn off the option to check if all data
conversion [=arg(=utf8)] selected for migration has a common character set, and will trigger
the conversion to the specified character set, if necessary. The
default character set is UTF8. You can also use this option to trigger
conversion of all data to specified charset.

Note: Using this setting will significantly increase the time that
it takes to complete the migration.

- -version Print program version number and exit.

The following code is an example of the migration command for all the tables within a MySQL
database named salesdatabase, to a PostgreSQL database named salesdatabase.
c:\ibi\srv77\home06Hyperstage\hs\bin>ibextmigrator.exe
-b "C:\ibi\srv77\home06Hyperstage\hs\bin" -u srvadmin
-s "C:\HyperstageMySQL\Data"
-p "C:\HyperstagePG\pg_data"
-i "C:\HyperstagePG\ib_data"
-d salesdatabase
-t "salesdatabase.*"

Hyperstage MySQL-based to PostgreSQL-based Migration 23

MySQL to PostgreSQL Data Type Mappings

The following code is an example of the migration command for all of the tables within a MySQL
database named salesdatabase, to a PostgreSQL database named salesdatabase. In this
example, the option to check if all data selected for migration has a common character set is
also included, and will force the character set conversion to UTF8.
c:\ibi\srv77\home06Hyperstage\hs\bin>ibextmigrator.exe
-b "C:\ibi\srv77\home06Hyperstage\hs\bin" -u srvadmin
-s "C:\HyperstageMySQL\Data"
-p "C:\HyperstagePG\pg_data"
-i "C:\HyperstagePG\ib_data"
-d salesdatabase
-t "salesdatabase.*"
--force-charset-conversion=utf8

MySQL to PostgreSQL Data Type Mappings

The following table lists the data type mappings for MySQL to PostgreSQL.

MYSQL TO POSTGRESQL DATA TYPE MAPPINGS

Hyperstage MySQL Data Type Hyperstage PostgreSQL Data Type

BOOL SMALLINT

TINYINT SMALLINT

MEDIUMINT INT

INT INT

BIGINT BIGINT

FLOAT REAL

DOUBLE DOUBLE PRECISION

DECIMAL(M,N) DECIMAL(M,N)

YEAR (To be decided)

TIME INTERVAL HOUR TO SECOND

DATE DATE

24 WebFOCUS
3. Hyperstage MySQL to PostgreSQL Migration Using the External Migrator

MYSQL TO POSTGRESQL DATA TYPE MAPPINGS

Hyperstage MySQL Data Type Hyperstage PostgreSQL Data Type

DATETIME TIMESTAMP WITHOUT TIME ZONE)

TIMESTAMP TIMESTAMP WITH TIME ZONE)

CHAR(N) CHAR(N)

VARCHAR(N) VARCHAR(N)

TINYTEXT VARCHAR(255)

TEXT VARCHAR(N)

BINARY(N) BYTEA(N)

VARBINARY(N) BYTEA(N)

Hyperstage MySQL-based to PostgreSQL-based Migration 25

MySQL to PostgreSQL Data Type Mappings

26 WebFOCUS
Chapter 4 Hyperstage MySQL to PostgreSQL Migration
Using Quick ETL Copy

Migration of Hyperstage MySQL to PostgreSQL can be done using the Quick ETL Copy tool.
The following section describes how to use Quick ETL Copy to migrate using the MySQL
ODBC or JDBC driver.
In this chapter:

Migration Using Quick ETL Copy With the MySQL ODBC Driver

Migration Using Quick ETL Copy With the MySQL JDBC Driver

Migration Using Quick ETL Copy With the MySQL ODBC Driver
Quick ETL Copy using the MySQL ODBC driver allows for migration of individual Hyperstage MySQL
tables to Hyperstage for PostgreSQL. Once you have installed and configured the ODBC Driver
on your machine, you will access the Reporting Server Web Console to configure the adapter
connection and run the Quick ETL Copy.

Note: You must access the Hyperstage version of the Reporting Server Web Console to
complete these steps.

Procedure: How to Install and Configure the MySQL ODBC Driver

1. Go to the following webpage:
http://dev.mysql.com/downloads/connector/odbc/5.2.html

2. Scroll to Windows (x86, 64-bit), MSI Installer and click Download to download the installation
file for the MySQL ODBC Driver. The mysql-connector-odbc-5.2.7-winx64.msi file will be
downloaded.
3. Double-click mysql-connector-odbc-5.2.7-winx64.msi to start the Driver installation.
4. Click Run.

Hyperstage MySQL-based to PostgreSQL-based Migration 27

Migration Using Quick ETL Copy With the MySQL ODBC Driver

5. Click Next, as shown in the following image.

6. Select I accept the terms in the license agreement, and click Next, as shown in the following
image.

28 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

7. Select Typical, and click Next, as shown in the following image.

8. Click Install, as shown in the following image.

Hyperstage MySQL-based to PostgreSQL-based Migration 29

Migration Using Quick ETL Copy With the MySQL ODBC Driver

The MySQL ODBC Driver will start installing, as shown in the following image.

9. Click Finish, as shown in the following image.

30 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

10. From Windows Control Panel/Administrative Tools, double-click Data Sources (ODBC), as
shown in the following image.

11. Click the System DSN tab, and click Add, as shown in the following image.

Hyperstage MySQL-based to PostgreSQL-based Migration 31

Migration Using Quick ETL Copy With the MySQL ODBC Driver

12. Select either MySQL ODBC 5.2 ANSI Driver or MySQL ODBC 5.2 Unicode Driver, and click
Finish, as shown in the following image.

13. Complete the fields in the MySQL Connector/ODBC Data Source Configuration dialog box,
and then click Details, as shown in the following image.

32 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

14. In the Cursors/Results tab, select the Don't cache results of forward-only cursors and Force
use of forward-only cursors check boxes, as shown in the following image.

15. Click OK.

Procedure: How to Configure the Hyperstage MySQL ODBC Adapter Connection

1. Launch the Web Console and click the Adapters tab.

Hyperstage MySQL-based to PostgreSQL-based Migration 33

Migration Using Quick ETL Copy With the MySQL ODBC Driver

2. Expand Available. Then expand the SQL and Hyperstage folders, as shown in the following
image.

3. Right-click ODBC, and click Configure, as shown in the following image.

34 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

4. Enter a Connection Name, the Datasource name used when configuring the MySQL ODBC
driver, User, and Password, as shown in the following image.

5. Click Configure.
6. Click Restart Java Services.

Procedure: How to Run Quick ETL Copy

1. Ensure that the Lite mode for Quick ETL Copy is turned off.
2. Launch the Web Console and click the Adapters tab.
3. Right-click Adapters and then click Change Common Adapter Settings, as shown in the following
image.

The Change Settings for Common Adapter window opens.

4. From the LITE-MODE drop-down menu, select No.

Hyperstage MySQL-based to PostgreSQL-based Migration 35

Migration Using Quick ETL Copy With the MySQL ODBC Driver

5. From the ETL-TRG-DBMS drop-down menu, select blank space, as shown in the following
image.

6. Click Save.

36 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

7. Right-click the Hyperstage MySQL Master file for the data to be migrated, and then click
Quick ETL Copy, as shown in the following image.

8. Select a Target Application for the Hyperstage PostgreSQL metadata.

9. From the Target Adapter drop-down menu, select Hyperstage (PG).
10. From the Load Type drop-down menu, select Extended Bulk Load Utility.

Hyperstage MySQL-based to PostgreSQL-based Migration 37

Migration Using Quick ETL Copy With the MySQL JDBC Driver

The following image shows the Quick ETL Copy window with all of the fields completed.

11. In order for the Partition parameters for Quick ETL Copy to appear, the KEYS attribute must
be set in the Access File that pertains to the MySQL table.
For example:
SEGNAME=FACT_SALES,TABLENAME=wrd_fact_sales,CONNECTION=CON1,KEYS=1,$

Migration Using Quick ETL Copy With the MySQL JDBC Driver
Quick ETL Copy using the MySQL JDBC driver allows for migration of individual Hyperstage MySQL
tables to Hyperstage for PostgreSQL. Once you have installed and configured the JDBC Driver on
your machine, you will access the Reporting Server Web Console to configure the adapter
connection and run the Quick ETL Copy.

Note: You must access the Hyperstage version of the Reporting Server Web Console to
complete these steps.

38 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

Procedure: How to Install and Configure the MySQL JDBC Driver

To install the MySQL JDBC driver:
1. Download the installation file for the MySQL JDVX driver from the following location:
http://dev.mysql.com/downloads/connector/j/.
2. From the Select Platform drop-down menu, select Platform Independent, as shown in the
following image.

3. In the Platform Independent (Architecture Independent), ZIP Archive row. click Download.
The mysql-connector-java-5.1.35.zip file is downloaded.

4. Unzip mysql-connector-java-5.1.35-bin.jar to the desired location, for example, C:\ibi\SQLJDBC,

as shown in the following image.

Procedure: How to Configure the Hyperstage MySQL JDBC Adapter Connection

1. Launch the Web Console and click the Adapters tab.

Hyperstage MySQL-based to PostgreSQL-based Migration 39

Migration Using Quick ETL Copy With the MySQL JDBC Driver

2. Expand Available, and then expand the SQL and Hyperstage folders, as shown in the following
image.

3. Right-click JDBC, and then click Configure, as shown in the following image.

The Add Hyperstage JDBC to Configuration window opens.

4. Complete the following fields:

In the Connection Name box, type a name for the connection.

In the URL box, type the JDBC URL in the following format:
jdbc:mysql://host:port/webfocus?useCursorFetch=true&/defaultFetchSize=50000

In the Driver Name box, type com.mysql.jdbc.Driver.

In the IBI_CLASSPATH box, add the fully qualified path of the JDBC Jar file, for example,
C:\ibi\SQLJDBC\mysql-connector-java-5.1.35-bin.jar.

40 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

In the Home Directory box, enter the location of the home directory for Hyperstage MySQL,
for example, C:\HyperstageMySQL.

In the Tools Directory box, type the location of the tools directory for Hyperstage MySQL.

Type the User and Password for the MySQL database.

The following image shows the window with all fields completed.

5. Click Configure.

Procedure: How to Run Quick ETL Copy

1. Ensure that the Lite mode for Quick ETL Copy is turned off.
2. Launch the Web Console and click the Adapters tab.

Hyperstage MySQL-based to PostgreSQL-based Migration 41

Migration Using Quick ETL Copy With the MySQL JDBC Driver

3. Right-click Adapters and then click Change Common Adapter Settings, as shown in the following
image.

The Change Settings for Common Adapter window opens.

4. From the LITE-MODE drop-down menu, select No.

5. From the ETL-TRG-DBMS drop-down menu, select blank space, as shown in the following
image.

6. Click Save.

42 WebFOCUS
4. Hyperstage MySQL to PostgreSQL Migration Using Quick ETL Copy

7. Right-click the Hyperstage MySQL Master file for the data to be migrated, and then click
Quick ETL Copy, as shown in the following image.

8. Select a Target Application for the Hyperstage PostgreSQL metadata.

9. From the Target Adapter drop-down menu, select Hyperstage (PG).
10. From the Load Type drop-down menu, select Extended Bulk Load Utility.

Hyperstage MySQL-based to PostgreSQL-based Migration 43

Migration Using Quick ETL Copy With the MySQL JDBC Driver

The following image shows the Quick ETL Copy window with all of the fields completed.

11. In order for the Partition parameters for Quick ETL Copy to appear, the KEYS attribute must
be set in the Access File that pertains to the MySQL table.
For example:
SEGNAME=FACT_SALES,TABLENAME=wrd_fact_sales,CONNECTION=CON1,KEYS=1,$

44 WebFOCUS
Customer Connections
Where users partner with Information Builders to provide feedback and create quality
documentation.

Partner With Us
Information Builders Technical Content Management team is comprised of many talented
individuals who work together to design and deliver quality technical documentation products.
Your feedback supports our ongoing efforts!

You can also join a usability study to get an early look at new documentation products and
services. Your participation helps us create documentation products that meet your needs.

To send us feedback or make a customer connection, contact Michele Brady, Service Director,
Technical Content Management at [email protected].

Request Permission to Use Copyright Material

To request permission to repurpose copyrighted material, please contact Frances Gambino, Vice
President, Technical Content Management at [email protected].

Visit the Information Builders Technical Content Library

To visit the Technical Content Library, go to http://documentation.informationbuilders.com/.
Hyperstage MySQL-based to PostgreSQL-based Migration
Creating Reports With
Release 8.1 Version 04
WebFOCUS Language

Version 7 Release 6

Information Builders
Printed on recycled paper in the U.S.A.
Two Penn Plaza
New York, NY 10121-2898