The SQL source (SRC = "SQL") enables data to be read in any SQL database accessible by an Adelia SQL Driver (Oracle, DB2, SQL Server and ODBC).
The following source-related parameters are recognized:
Input parameters (DATA_SRC)
Parameter name |
Type |
Mandatory |
Description |
DBM |
string |
Yes (*) |
DBMS type (alias): - DB2: API driver for DB2 UDB, - Oracle: API driver for Oracle (8.0 and >), - SQL Serv class="mt2">- ODBC: API driver for ODBC. The Oracle7 and Oracle8 values are recognized for compatibility with existing scripts. In the Java version, this parameter is optional if the DATABASE parameter contains a JDBC URL (see below) and can also indicate a JDBC driver which will be instantiated by the tool. |
DLL |
string |
Yes (*) |
Replacement for DBM. Enables an API driver to be specified directly using its DLL name (e.g.: apiora.dll). |
DATABASE DB |
string |
Yes |
Name, alias or URL access of the database containing the data to be transferred. In the Java version, this parameter may contain an access URL in Windows format (JDBC URL derived from DBM and DATABASE parameters), or a JDBC URL. |
USER |
string |
No |
User connected to the database. |
PASSWORD |
string |
No |
User's password. |
CONNECT |
string |
No |
Database connection string in the form "user.password". Parameter supported for compatibility. We recommend using the USER and PASSWORD parameters. |
CONNECT |
string |
No |
Database connection string in the form "user.password". If this parameter is not specified, a system connection will be attempted. |
REQUEST |
string |
Yes (**) |
SQL query producing all the data to be exported. Any valid "SELECT" type SQL query can be used to produce the data. |
TABLE |
string |
Yes (**) |
Depreciated: The parameter is accepted if the script's version is 1.0. Name of the table (schema.tablename) whose data are to be exported. Shortcut for (REQUEST="SELECT * FROM schema.tablename") |
| FETCHSIZE | integer | No | For JAVA implementation of the CopyB only, this parameter sets the buffer used to read the SQL cursor. The default value is 25 (25 lines of data per page is read). |
Output parameters (DATA_DEST)
Parameter name |
Type |
Mandatory |
Description |
DBM |
string |
Yes (*) |
DBMS type (alias): - DB2 : API driver for DB2 UDB, - Oracle: API driver for Oracle (8.0 and >), - SQL Server: API driver for SQL Server, - ODBC: API driver for ODBC. The Oracle7 and Oracle8 values are recognized for compatibility with existing scripts. In the Java version, this parameter is optional if the DATABASE parameter contains a JDBC URL (see below) and can also indicate a JDBC driver which will be instantiated by the tool. |
DLL |
string |
Yes (*) |
Replacement for DBM. Enables an API driver to be specified directly using its DLL name (e.g.: apiora7.dll). |
DATABASE DB |
string |
Yes |
Name, alias or URL access of the database containing the data to be transferred. In the Java version, this parameter may contain an access URL in Windows format (JDBC URL derived from DBM and DATABASE parameters), or a JDBC URL. |
USER |
string |
No |
User connected to the database. |
PASSWORD |
string |
No |
User's password. |
CONNECT |
string |
No |
Database connection string in the form "user.password". Parameter supported for compatibility. |
CONNECT |
string |
No |
Database connection string in the form "user.password". If this parameter is not specified, a system connection will be attempted. |
SCHEMA |
string |
No |
Owner schema for the table (version 2.0). |
TABLE |
string |
Yes |
Name of the table (schema.tablename) receiving the data. Note: The format (schema.tablename) is supported for compatibilty reasons only if the script's version is earlier than 2.0. If not, the format will be interpreted as a long SQL name containing a dot. In version 2.0, the owner schema must be specified in the SCHEMA parameter. |
MODE |
string |
No |
Data insertion mode: - INSERT: the data are inserted into the table (default setting). - REPLACE: the data replace the data already in the table. You can also specify a DELETE clause, failing which all the existing data in the table are deleted. - UPDATE: the data is updated or added, based on the table's primary key. If the table does not have a key, or if the key contains all the columns, the data is added. - CREATE: the table is recreated using the input information. This option is only possible if the data source is able to accurately describe the structure of the table to be generated. |
DELETE |
string |
No |
If the DELETE clause is specified (in REPLACE mode), a "DELETE FROM table WHERE clause" query is run before the data are inserted. |
COMMIT |
integer |
No |
Estimated volume in Kb of the data to be inserted into the table between two validation (Commit) points. If this parameter is not specified, a value of "1000" (1 Mb) will be set by default. Setting this parameter to zero or a negative value causes the system to run in AUTOCOMMIT mode (validating each inserted line automatically). |
Notes:
(*) |
Parameters DBM and DLL are mutually exclusive. |
(**) |
Parameters REQUEST and TABLE are mutually exclusive. |
Specific features of the Java version:
The SQL source is compatible with the scripts written for the Windows version. A JDBC URL will be automatically derived from the DBM and DATABASE parameters. "DB2", "ORACLE", "SQL SERVER", "MYSQL" and "POSTGRESQL" values are supported. "ODBC" and "ACCESS" data sources are not supported.
However, limitations apply:
- Generally speaking, JDBC drivers do not support the database manager's local client configuration (DB2 catalog, TNSNAMES.ORA, etc.). If the script simply indicates a database name and not a URL in Windows format (DATABASENAME@serveur:port), the generated URL will indicate "localhost" as the server name, which will not work if the local catalog references a remote database.
- The JDBC drivers do not necessarily support user authentication in the operating system (connection without user/password parameters). It is therefore preferable for the connection parameters to be indicated in the script.
- The SQL source accepts the definition of a JDBC URL in the "DATABASE" parameter. In this case, the script will not be portable to the Windows version and the DBM parameter is ignored.
Example of automatic URL rewriting:
(DBM="DB2")(DB="TEST") | |
(DBM="DB2")(DB="TEST@db2host") | jdbc:db2://db2host:50000/TEST |
(DBM="Oracle")(DB="TEST@orahost") | jdbc:oracle:thin:@orahost:1521/TEST |
Click below for further details about:
The DATA_SRC command.
The DATA_DEST command.