The connection associated with a load table can be changed through the Properties of that table.
Connections can also be changed in bulk by using the following process:
- Double-click the Load table object group in the left pane. This displays all the Load tables in the middle pane.
- Select the Load tables that you want to change, using standard Windows selection.
- Right-click to bring up the context menu and select Change Connect/Schema.
- Select the new connection to change all the selected Load tables.
Note
You cannot change the connection type but it is possible to change from Database to ODBC connections when the following considerations are taken into account.
Switching Connection from ODBC to Database
This switch should be successful in most cases, although it may not provide the most performant load possible. By default ODBC connections use the source table/column transformation loading method as dates and potentially other data types need to be converted.
When switching to a database link load any transformations will still occur although they may no longer be necessary.
Switching Connection from Database to ODBC
Although it is possible to switch from a Database connection to an ODBC connection, the resultant extract may not function correctly. The main issue is around the conversion of date fields under Oracle. SQL Server tends to handle the date conversion better in most cases. When an extract occurs over an ODBC connection the date is converted in the extract from the source system into a standard ASCII format. This ASCII formatted date is then converted back when it is loaded into the load table. To resolve this problem place a transformation on any date fields. Examples of typical Oracle transformations are:
to_date('''||to_char(ORDER_DATE,'YYYYMMDD')||''',''YYYYMMDD'')
to_char(ORDER_DATE,'YYYY-MM-DD HH24:MI:SS')
where ORDER_DATE is the name of the column being extracted. In the first example the conversion string is enclosed within quote marks (') so that it is passed as a literal to the select statement that will be built. Quote marks within the conversion string are protected from premature evaluation by the inclusion of a second quote mark alongside the original.
Note
If a load table is created via drag and drop from an ODBC based connection WhereScape RED will build all the required date transformations.
There are several supplied APIs for changing schema and connections programmatically. Refer to Callable Routines for details.