Step 1 – Specifying a Data Source and Target - Access Module

Teradata® Tools and Utilities Access Module Reference

Product
Access Module
Release Number
17.10
Published
October 2021
Language
English (United States)
Last Update
2021-11-02
dita:mapPath
uur1608578381725.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2425
lifecycle
previous
Product Category
Teradata Tools and Utilities
References to database for data sources and destinations are synonymous with a database in the database system.
  1. From the Windows desktop, click Start > Programs > Teradata Client > OleLoad to open the Teradata OleLoad main window.
  2. [Optional] To populate the window with parameters from a previously saved access module job (.amj) file, click File > Open.
  3. Select a data source from the Select a source list.
    The list contains all available OLE DB data sources, including Teradata Database.
    • Loading data into a database – Selecting a data source other than Teradata Database opens the Microsoft Data Link Properties dialog box. Use it to specify information for connecting to the source.
      Duplicate rows (multiset tables) are supported.

      If the data source is a text file with multi-lined strings and carriage returns (features not supported by Teradata utilities) select Microsoft Jet 4.0 OLE DB Provider. For information on setting up the schema.ini file for this type of data transfer, see Define a Schema File.

      Specify the options in the Data Link Properties dialog box.
      • Select the Blank password check box if a system administrator allows specific users to log on without a password; clear the check box if an administrator provides the password for accessing the database.
      • When a text file is selected as data source, an extended properties value must be set. From the Data Link Properties dialog box, click the All tab, highlight Extended Properties, then click Edit Value.

        In the Property Value field of the Edit Property Value dialog box that appears, type the word Text, then click OK. The Data Link Properties dialog box reappears. Click the All tab to verify that the new entry is listed under Extended Properties.

        If an error message appears stating Failed Properties: Persist Security Info (NOT SUPPORTED), ignore the message and click OK.

      After a data source is specified, you can click Connection Info to view its connection data.

    • Exporting data from a database – Select a database as source.
      It is possible to export data from one database to another, however, it is not recommended due to performance issues. Consider first exporting the data from a database, and then using an alternate data source such as Microsoft OLE DB Provider for ODBC Drivers or OLE DB Provider for Teradata to import the data.

      When a database is selected as source, the Teradata Connection Information dialog box appears.

      Specify the following connection options:
      • Teradata host – Enter the host name of the database. The value entered can be a COP entry in the hosts file/domain name services (DNS) or an IP address.
      • User id – Enter the user logon id for the connection.
      • Password – Enter the password associated with the specified user id.
      • Allow saving password – Select to save the password to the .amj file and prevent being prompted for this information when reconnecting to the database.
        When Allow saving password is selected, the associated .amj file might allow unauthorized access as it will contain password data.
      • Target database – Select or type the name of the database to be used as target. OleLoad populates the drop-down list from any previously used Target databases. Default is NULL.
      • More – Click to view additional options in an expanded dialog box.
      The following additional options appear:
      • Account – Enter the account identifier associated with the database user. Default is NULL.
      • Mechanism – Select from a list of available mechanisms in a drop-down combo box. The box is populated by OleLoad, and is retrieved from terasso.dll. Default is NULL.

        For more information about mechanisms, see the appropriate installation guide.

      • Parameter – Select the parameter associated with the selected mechanism from a list of previously used parameters. The parameters are populated by OleLoad in a drop-down combo box. Default is NULL.
      • Allow saving parameter – Select to save the selected parameter to the .amj file and avoid being prompted for this information when reconnecting to the database.
      • Use SSO – Select to use Single-Sign-On (SSO) feature. Selecting this option will disable options not required for logging onto the database. The Mechanism drop-down menu is updated with mechanisms supporting SSO and is required for SSO feature.
  4. When finished, click OK.
    The Teradata OleLoad connects to the database in UTF-16 session charset and the Teradata OleLoad main window refreshes. The left pane displays the hierarchy of items from the selected data source, if available.
  5. Select one of the following radio buttons to specify the data to be loaded:
    • Selection – Displays the hierarchy of the data source in the left pane. Select the data needed for the operation.

      This button is only available if the data source supports the TABLE schema rowset.

    • Name – Type the name of a data source in the left pane.
    • Command – Type an SQL query in the left pane to retrieve data from the selected data source. OleLoad parses simple SQL queries for Table Name to populate as target table name in Load scripts. If the table name is incorrect in script, manually update the Table Name text box in the Advanced Settings dialog box before launching the utility.

      Depending on the source, performance might be improved if the command limits the columns that will be returned. For example, instead of using the following command to select only the columns CustomerID and CompanyName:

      SELECT * FROM "NORTHWIND"."DBO"."CUSTOMERS"

      Instead, use:

      SELECT CustomerID, CompanyName FROM "NORTHWIND"."DBO"."CUSTOMERS"

      The Command button is only available if the data source supports SQL commands. The button is unavailable if a database is the data source.

  6. From the Select a destination list, do one of the following to select a target:
    The list contains the names of all of the available OLE DB providers and the database. After a destination is selected, you can click Connection Info to view system information about a selected destination.
    • Loading data into a database – Select a database, which opens the Teradata Connection Information dialog box. Use this dialog box to specify the information necessary to connect to the database.
    • Exporting from a database – Select an OLE DB provider, which opens the Microsoft Data Link Properties dialog box. Use this dialog box to specify the information necessary to connect to the OLE DB provider.

      After a destination is selected, you can click Connection Info to view system information about a selected destination.

      An OLE DB data provider must not be specified as both the source and the destination.
  7. In the right pane, select the columns that contain the data needed for the operation.
    The data in the selected columns will be transferred to the target system that is identified in the Select a destination box when the job is launched.
  8. Proceed with Step 2 – [Optional] Specifying Advanced Settings.