Note:

Data field names need to conform to the rules of the underlying SQL database.

Property

Description

Information for SQL Database

Lets you supply database-dependent information that Magic xpa can pass to the underlying RDBMS. The use of this property is optional.

See also Database Information - Database Property.

Owner

The database’s owner of the table or view. This property supports logical names.

Position

This setting determines the Position index for a table. The available options are:

Default – Magic xpa uses its own default as a Position index for a table.

Row ID – In Oracle tables, Magic xpa uses the ROWID column.

Unique index – In other RDBMSs, Magic xpa uses the shortest unique index. This can be overwritten by using another index as the Position index.

Index

If the position was chosen as Index, then the Index property will allow you to choose one of the table’s unique indexes.

Default Position

This is the unique identifier that Magic xpa uses as the default position. This property is read-only.

Magic xpa chooses an automatic unique identifier with a minimum number of segments (not the shortest).

Check Existence

Check Existence determines whether Magic xpa checks the existence of every SQL table it tries to access in Runtime, and creates the table if it does not exist. The available options are:

Yes – Magic xpa can create tables in the database and also check for the existence of every table it tries to access. This check may cause performance degradation.

No – Magic xpa will not check if the table already exists before accessing it. If the table does not exist, a database error message appears. In Runtime, you should set the Check Existence property to No for enhanced performance.

As Database (default) – This option uses the value of the Check Existence property from the Database Properties dialog box.

Table Type

The table type can either be Table or View. If the table type is View, the DbDel and DbCopy functions will not work, because a View cannot be created, deleted, or altered by Magic xpa. If you do get a definition or a View, you must define a unique index as the Position Key.

Hint

Some RDBMSs allow hinting the optimizer for processing a query. In this field the programmer can enter a string that will be concatenated to the SELECT statement.

Yes – Activates the Hint property. The hint will be used when this property is set to Yes and when the Hint value is blank.

No – De-activates the Hint property.

Magic xpa does not evaluate the string. It is the developer’s responsibility to use correct syntax. It is recommended to only use Hints in special cases.
The Hint value can be inherited from the Data Source properties (only when it is set to Yes).

Relevant interfaces: MSSQL and Oracle

See also How Do I Affect the Database Optimizer Behavior?

Cursor

When using the MSSQL gateway, either internal DB commands or cursors are used. Using DB commands requires separate connections for each result set. Performance is enhanced when the result set is large.

The available options are:

Yes – Enables the use of cursors on the specific table

No – Disables the use of cursors on the specific table and uses DB commands instead

Default – Magic xpa uses cursors or DB commands according to the following considerations for MSSQL tables:

• Magic xpa uses cursors when the MSSQL table is designated as the main task table.

• Magic xpa uses DB commands when the MSSQL table is designated as a linked table.

Relevant interfaces: MSSQL

Default value: Default

Array Size

The Magic xpa gateways to the various SQL databases support array processing. When fetching records from the database, the gateway fetches a group of records, rather than one record at a time, thereby reducing network traffic. The array size is the number of rows fetched together from the database in a single fetch operation. The data is stored in an engine-side array.

The default for this property is 0. When this property is set to 0, the Magic xpa array size default is used, which can be overwritten. When scanning a large table, increasing the array size can enhance performance. It is recommended, however, to use the Magic xpa default of 0, which takes into account the record length of a table and a predefined volume data size. Changing the array size in the table overwrites the Data Source property settings.

The number in the left bottom corner of the dialog box, is the record length of the data source.

Effect on Online and Batch tasks

When working with an Oracle database, a change of the array size has an effect in both Online and Batch tasks.

When working with an MSSQL database, a change has an effect only when the engine uses KeySet cursors for Batch tasks with a default cursor setting. This has no effect for Online tasks, because by default for MSSQL, dynamic cursors are used.

You can see the difference in the fetch process by looking at a developer level gateway log.

A real fetch from the database is done according to the Array size property.

In the log, search for the phrase a real fetch from, and this line will show that the number of records fetched from the database is according to the value of the Array size calculation (for default 0, the calculation mentioned above or the value as defined by the developer).

Relevant interfaces: MSSQL, Oracle and DB2

Default values:

• Oracle and DB2 – calculated as 1200 / record length of a table.

• MSSQL – calculated as 4096 / record length of a table.

See also How Do I Reduce Database Access?