Result Database (Magic xpa 2.x)
You can set the same value in the Result Database field as in the Input Database field.
In most cases, the underlying RDBMS allows an
INSERT INTO table AS SELECT...
statement, which copies all the data to a table in one command. This method is faster than opening a cursor. All of the records are retrieved from the client and inserted into the result table.
In such cases, the SQL gateway creates a table in the database and sends:
INSERT INTO temp_ table AS SELECT “direct SELECT statement”
The user may then scroll on that table to speed up the SELECT statement execution. This method is especially helpful when the result set is large because there is no retrieving and inserting of each record to the temporary table.
When the result database is different from the input database, a temporary table is created. A cursor is defined, and the records are retrieved and inserted one by one into the result table. The user may then scroll on that table.
When the result is large, it is best to use the option that makes the result database the same as the input database. Then the INSERT INTO AS SELECT statement will be used.
When the result is relatively small, it is best to use the result database as an ISAM database that will reside on the client. Then when you create the result and scroll on it, the work is only performed on the client, which reduces network traffic.
Use the memory gateway when the results are relatively small to enhance performance.
DB2 and ODBC cannot be used as the Result Database of a Direct SQL task.
