Database Resource (Magic xpi 3.x)

« Go Back


Created ByKnowledge Migration User
Approval Process StatusPublished

Database Resource (Magic xpi 3.x)

Magic xpi stores information about database resources in the Resource Repository. This repository contains a list of the databases that have data that can be mapped using the Data Mapper.

The Database Resource Setting window contains the following parameters (parameters in bold are mandatory):




The type of database. Select the database type from the drop-down list. Microsoft SQL Server, Oracle, ODBC, DB2/400, DB2, and Pervasive SQL are included in the list.

Database Name

Connect String

Data Source Name

Database Alias

Library/Schema Name

Enter the following depending on the type of database:

  • For Microsoft SQL Server and DB2 enter the actual Database Name.


If the database name includes a space, because it contains more than one word, you must surround it with square brackets. For example, [My Database Name].

  • For Oracle 10g and 11, enter the name of the Connect String.


You have to use an alias to connect to Oracle via Magic xpi, even if the database is local.

  • For ODBC/Pervasive enter the ODBC Data Source name. Make sure that the ODBC Data Source is set in your computer's Windows settings. Magic xpi can connect to table names that are a maximum of 30 characters. If you have a table name that is longer, see this Technical Note for further information about handling it.

  • For DB2/400, click the Library button to select from the libraries available in the DB2/400 Libraries dialog box.


Verify that the appropriate Database gateway is uncommented in the [MAGIC_GATEWAYS] section of the Magic.ini file. If the entry is commented, which means that it has a semicolon (;) before it, remove the comment to enable the gateway.

Server Name

The name of the physical computer where the database is installed. For DB2/400, the server name cannot exceed 19 characters.


The name of the user who is authorized to work with the database. See also Filters for Database.


Make sure the name entered in the User field also exists on the server where the database is located.

For DB2/400 databases:

  • On the IBM i server where your database is installed make sure that the User Profile for the User entered in the Resource Repository does not contain the number 65535 in the Coded character set ID option.

  • Multiple sessions to the same server with a different user login are not supported. Magic xpi takes the first session to the server that it finds.


The user’s password.

Starting owner

The owner to filter with when using this database entry. Click Owner to open the Owners List. Select the required owner from the list. See also Filters for Database.

The Database resource also gives you the following additional buttons:




Click this button to verify that the connection to the database is valid. You will receive a message indicating that the connection is established, or an error message if the connection failed. When you select the DB2/400 database, you can click this button to check accessibility to libraries. A table selection form displays tables from specified libraries, prefixed by the library name (not by owner). When you select a table, the owner details are displayed at the bottom of the screen. The Available Columns list in the Database Wizard column selection form displays columns from the tables that you selected in the library/schema.


This button opens the Filters for Database window. This window lets you select which tables are used in this database connection.


This button opens the Owners List. Here, you can select the required starting owner for this database entry.


This button is only available when you select a DB2/400 database. It lets you select one or more libraries to work with.


You can use environment variables for the following fields:

  • Database name; Connect String; Data Source name; Database alias

  • Server name

  • User

  • Password

Environment variables are an alias for the actual database that let you dynamically define the field. Environment variables must be surrounded (back and front) with a percent (%) sign. You must also define the environment variable in the Environment Variables Repository. For more information on environment variables, see Using Environment Variables.

If there are two or more libraries defined with environment variables that translate to the same value, the "Incomplete libraries/schema definition" error appears.

When two libraries (real library names) are defined with the same value (name), you cannot leave the Libraries screen if you type or select a duplicate name.