Data Dictionary Maintenance

Bulk Database Export Utility

The Bulk Database Export Utility allows you to define a connection to an existing database (ODB, MySQL, OCI, ADO or DB2) and then selectively export table definitions from the PxPlus data dictionary into the specified database.

In addition, this utility can be used to:

The Create DB File button launches a separate window that allows you to create either a Link file or a Prefix file entry that points to the database itself.

(The Bulk Database Export Utility was added in PxPlus 2023.)
(The Create DB File button was added in PxPlus 2023 Update 1.)

The utility remembers database connection information after successfully connecting to the database when the Load Tables button is selected. Multiple database connections can be remembered, one connection for each database type. The connection information is remembered on a "per project" basis. This allows one project to use one database while another project can use a different database easily.

When the utility is invoked, the connection information for the last database that was successfully connected to is automatically loaded into the fields in the Target Database section. If you change the Database Type field and a database of that type previously connected successfully, the remembered database connection information will automatically be loaded.

(The remembering of database connection information was added in PxPlus 2023 Update 1.)

This utility lists all the tables in the PxPlus data dictionary, sorted in alphabetical order by default. A check mark indicates which table definitions are currently in the database. Options for different Actions can be selected individually for each table, or the Edit All drop down can be used to select an option for all the tables. The selected actions are used to validate native PxPlus data files, create/merge/replace database tables, load a database table with data from a native PxPlus data file, create database link files, and add database table entries to the prefix file.

To invoke this utility, use one of the following methods:

Location

Method

From Database Export Utility

Click the Bulk Export button.

From Data Dictionary Maintenance

Click the Export button in the Database section of the tool bar if no table with a native PxPlus data file is currently selected.

From Data Dictionary Maintenance

From the menu bar, select File > Database Export Utility if no table with a native PxPlus data file is currently selected.

From the PxPlus Command line

Enter: RUN "*dict/expdde"

Call Export actions programmatically

See Calling Export Actions Programmatically.

The Bulk Database Export Utility is displayed below with a sample entry:

This window consists of the following:

Target Database

Enter the database information.

The utility remembers the database connection information after successfully connecting to the database (when the Load Tables button is selected) and automatically loads this information into these fields when the utility is invoked.

(The automatic loading of database connection information was added in PxPlus 2023 Update 1.)

Database Type

Type of database. Selections are ODB, MySQL, OCI, DB2 and ADO. Defaults to ODB.

Data Source or Server

(This field changes depending on the Database Type selected)

If Database Type is ODB: Enter the name of the Data Source. (Required for ODB)

If Database Type is MySQL, OCI or ADO: Enter a Server address. If the database is running locally, this value may be omitted. (Required for ADO)

If Database Type is DB2: The Server field is disabled, as only the Database Name is used and the server is defined in the db2cli.ini file.

Database Name or System ID

(This field changes depending on the Database Type selected)

If Database Type is ODB, MySQL, DB2 or ADO: Enter the Database Name. (Required for MySQL or DB2)

If Database Type is OCI: Enter the Oracle System ID value. (Required for OCI)

User Name

User login name.

Password

User password.

Additional Options

Parameters to be included in the OPT= string when opening the database or updating the prefix file. All parameters are semi-colon separated.

Defaults to NONULLS=Y;NULLPADKEY=Y for all database types except OCI, which defaults to NULLPADKEY=Y.

(The additional option NULLPADKEY=Y was added in PxPlus 2023.)

Create DB File

Button that launches the Create Database Connection File window that allows you to create either a Link file or a Prefix file entry that points to the database itself.

    

A direct connection to the database may be useful so that you can do a global OPEN to the database, providing the UserID and Password. This avoids needing the UserID and Password on every link/prefix entry.

It can also be used to get a list of tables from the database or if your program needs to make direct queries against the database.

This window consists of the following:

Database File Type

Select the database file type to create, either Link File (Default) or Prefix File Entry.

Link File Path

(Applicable when Database File Type is Link File)

Enter the path for the link file or click the Query button.

Prefix File Entry Name

(Applicable when Database File Type is Prefix File Entry)

Enter the prefix file entry name.

Create

Creates the link file in the specified path or creates the entry in the prefix file, depending on which Database File Type is selected.

Cancel

Closes the Create Database Connection File window with no further action taken.

(The Create DB File button was added in PxPlus 2023 Update 1.)

Load Tables

Button that is used to load tables from the PxPlus data dictionary into the Tables grid. If changes have been made in the grid but not applied, a message will display.

When the database connection is successful, the connection information (one connection for each database type) is remembered on a "per project" basis and is automatically loaded when the utility is invoked.

(The remembering of database connection information was added in PxPlus 2023 Update 1.)

(Tables Grid)

Grid that lists all the tables in the PxPlus data dictionary.

This list can be sorted by clicking on the column header for the following columns only: Table, Group, Description and In DB. Subsequent clicks toggle the sort between ascending/descending order. By default, the list is sorted by Table name in ascending order.

(Column sorting for applicable columns was added in PxPlus 2023 Update 1.)

This grid consists of the following:

Edit All

The changes made to the Validate, DB Action, Load Table and File Action options in this row will be applied to all the tables in the list.

For DB Action, available selections are:

No Action

(Default) No action to apply.

Create or Merge Table

Sets the DB Action to either Create Table or Merge Table for each table in the list, depending on whether the table is in the database.

Create or Replace Table

Sets the DB Action to either Create Table or Replace Table for each table in the list, depending on whether the table is in the database.

Table

(Display Only) Displays the name of each table from the PxPlus data dictionary.

Group

(Display Only) Displays the current group name of the table from the data dictionary.

(The Group column was added in PxPlus 2023 Update 1.)

Description

(Display Only) Displays the table description from the data dictionary.

In DB (DB = Database)

(Display Only) Displays a check mark if the table is in the database or an X if it is not in the database.

File Type

(Display Only) Displays the current physical file type from the data dictionary.

Possible values are:

No File

No physical file exists.

Native

A native PxPlus data file exists.

Native Prefix

A prefix file entry points to a native PxPlus data file.

[ODB|MySQL|ADO|DB2|OCI] Prefix

A prefix file entry points to an external database table.

[ODB|MySQL|ADO|DB2|OCI] Link

A link file points to an external database table.

(The File Type column was added in PxPlus 2023 Update 1.)

Validate

Check box to validate the existing data in the PxPlus native data file. Will be locked if no native file exists.

DB Action (DB = Database)

Action options that can be set, depending on whether the table is in the database. Click the drop down arrow for available selections. To edit this option for all the tables in the list, use the Edit All drop down (in top row).

If the table is in the database, available options are No Action, Merge Table or Replace Table.

If the table is not in the database, available options are No Action or Create Table.

No Action

(Default) No action to apply.

Merge Table

(Available when table is in the database)

Updates an existing table in the database so that data stored in that database table is preserved and only what comes from the data dictionary is updated.

Keys/Indices will not be merged or changed.

A merge will attempt to update/add all columns in the data dictionary record to the table in the database. If a column cannot be added, the merge will be considered incomplete, not a failure, as the columns that can be added will still be added and the table in the database may be changed.

A merge is considered a success if it connected and completed all changes requested.

A merge is considered a failure if it could not connect or request any changes.

Replace Table

(Available when table is in the database)

Deletes an existing table in the database and creates a new table.

A message will display to warn that any data currently in the table will be overwritten, which could lead to possible data loss, and to ask if you want to proceed unless the Disable Data Loss Warning check box is set.

Create Table

(Available when table is not in the database)

Creates a new table in the database.

Load Table

Check box to populate the database table with the data in the native PxPlus data file. This is locked if no native file exists.

A message will display to warn that any matching records in the table will be overwritten, which could lead to possible data loss, and to ask if you want to proceed unless the Disable Data Loss Warning check box is set.

If a failure happens while the load is in progress, the table in the database may have some but not all of the records from the PxPlus native file.

File Action

Action options that are related to creating/editing files - a prefix (PFX) file entry or a link file. Click the drop down arrow for available selections. To edit this option for all the tables in the list, use the Edit All drop down (in top row).

No Action

(Default) No action to apply.

Add to PFX File

Creates a new entry in the prefix file to the table in the database.

If no prefix file is defined, the user will be prompted to select or enter a name for a prefix file to be updated/created.

Create Link File

Creates a link file that points to the table in the database.

If a prefix file entry, link file or a native file already exists and this action is specified, it will remove the existing file/entry. If an existing native file is found, a message will display to warn about possible data loss and ask if you want to proceed unless the Disable Data Loss Warning check box is set.

Strip User/Password from Link Files and Prefix Entries

Controls whether the User and Password connection options are stripped from link files and prefix file entries.

By default, this check box is selected. For security reasons, it is strongly recommended not to save the User and Password information in plain text.

Disable Data Loss Warning

If selected, disables a warning message from displaying for each DB Action or File Action that will possibly cause data loss.

By default, this check box is not selected to help prevent accidental data loss. If you are applying several DB Actions or File Actions and are knowingly overwriting data and/or replacing native data files, you can set this option to avoid displaying a warning message for each table.

View Log

(Available when changes have been applied)

Displays a log of applied changes as a PDF, which also provides options for printing or saving the log, if desired.

Apply

(Available when Validate, DB Action, Load Table or File Action is set to other than No Action)

Applies changes to all the tables that had a Validate, DB Action, Load Table or File Action set to something other than No Action. A message displays that lists which changes were applied successfully, which changes were incomplete (partially successful), and which changes failed.

Exit

Closes the Bulk Database Import Utility. If changes have been made in the grid but not applied, a message will display. If records exist in the log file, a message will ask if you want to view the log.

Calling Export Actions Programmatically

For information on invoking the individual Export actions (i.e. Validate, CreateTable, LoadTable, etc.) programmatically, see Calling Export Actions Programmatically.

(The Call routines for invoking individual Export actions were added in PxPlus 2023.)

See Also

Database Export Utility
Database Import Utility
Creating a Link File
Creating the Prefix File