Starting an Access Module Load Job from a Teradata Utility - Access Module

Teradata Tools and Utilities Access Module Reference

Product
Access Module
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2425
lifecycle
previous
Product Category
Teradata Tools and Utilities

Starting an Access Module Load Job from a Teradata Utility

Access module load jobs can be initiated from the following Teradata Utilities:

  • Using FastLoad
  • Using MultiLoad
  • Using TPump
  • Using BTEQ
  • Using Teradata PT
  • Using FastLoad

    To load to Teradata Database using FastLoad

    1 Select Start >Programs >Teradata Client >FastLoad to open Teradata FastLoad from the Windows desktop.

    2 Use the BEGIN LOADING command. Because the Teradata OLE DB Access Module returns data in a format that includes indicator bits, specify the INDICATORS option in the FastLoad BEGIN LOADING command.

    3 For the AXSMOD specification, use OLEDB_AXSMOD as the DLL for the Teradata OLE DB Access Module.

    4 Use the DEFINE command to specify the fields to load.

    5 Specify the one of the following for the file parameter and operating specification:

     

    FILE Parameter

    Operating Specification

    Outcome

    "<pathname>.amj"

    'noprompt'

    Script runs if no errors exist. If errors exist, the script fails.

    "<pathname>.amj"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens so you can run the script using the access module.

    "UNTITLED"

    'noprompt'

    Script fails. Instead, specify an .amj file name instead of UNTITLED.

    "UNTITLED"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens.

    FastLoad Example

    LOGON DELL2300/taste,taste ;
    DATABASE test2 ;
    CREATE TABLE "unittestmixtablefe1_29"
          (colinteger INTEGER,
          colsmallint SMALLINT,
          Colbyteint SMALLINT);
    BEGIN LOADING "unittestmixtablefe1_29"
       ERRORFILES unittestmixtablefe1_29_errors1, unittestmixtablefe1_29_errors2
       INDICATORS;
    AXSMOD Oledb_Axsmod "noprompt";
    DEFINE colinteger (INTEGER),
           colsmallint (SMALLINT),
           colbyteint (SMALLINT)FILE=Myfile.amj;
    INSERT INTO "unittestmixtablefe1_29"(colinteger, colsmallint, and colbyteint)
       VALUES (:colinteger, :colsmallint, :colbyteint);
    END LOADING;
    LOGOFF;

    Using MultiLoad

    To load to Teradata Database using MultiLoad

    1 Select Start >Programs >Teradata Client >MultiLoad to open Teradata MultiLoad from your Windows desktop.

    2 Use the .LAYOUT command. Because the Teradata OLE DB Access Module returns data in a format that includes indicator bits, specify the INDICATORS option in the MultiLoad .LAYOUT command.

    3 For the AXSMOD specification, use OLEDB_AXSMOD as the DLL for the Teradata OLE DB Access Module.

    4 Use the IMPORT command to specify the following:

     

    FILE Parameter

    Operating Specification

    Outcome

    "<pathname>.amj"

    'noprompt'

    Script runs if no errors exist. If errors exist, the script fails.

    "<pathname>.amj"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens so you can run the script using the access module.

    "UNTITLED"

    'noprompt'

    Script fails. Instead, specify an .amj file name instead of UNTITLED.

    "UNTITLED"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens.

    MultiLoad Example

    .LOGTABLE newtest.test1_LOG;
    .LOGON perform/test,test;
    DATABASE test ;
    CREATE TABLE "test1"(col1 numeric(3,0));
    .BEGIN IMPORT MLOAD TABLES "test1" CHECKPOINT 0;
    .LAYOUT test1_layout INDICATORS;
    .FIELD col1 * numeric(3,0);
    .DML LABEL test1_label;
    INSERT INTO "test1"(col1) VALUES (:col1);
    .IMPORT INFILE "c:\oledb\test1.amj"
          AXSMOD OLEDB_AXSMOD 'noprompt'
          LAYOUT test1_layout
          APPLY test1_label;
    .END MLOAD;
    .logoff;

    Using TPump

    To load to Teradata Database using TPump

    1 Select Start >Programs >Teradata Client >TPump to open Teradata TPump from the Windows desktop.

    2 Use the .LAYOUT command. Because the Teradata OLE DB Access Module returns data in a format that includes indicator bits, specify the INDICATORS option in the TPump .LAYOUT command.

    3 For the AXSMOD specification, use OLEDB_AXSMOD as the DLL for the Teradata OLE DB Access Module.

    4 Use the TPump IMPORT command to specify the following:

     

    FILE Parameter

    Operating Specification

    Outcome

    "<pathname>.amj"

    'noprompt'

    Script runs if no errors exist. If errors exist, the script fails.

    "<pathname>.amj"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens so you can run the script using the access module.

    "UNTITLED"

    'noprompt'

    Script fails. Instead, specify an .amj file name instead of UNTITLED.

    "UNTITLED"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens.

    TPump Example

    .LOGTABLE test.precision_log;
    LOGON wuscaesc/tester,dbc;
    DATABASE test ;
    DROP TABLE "TestPrecision";
    DROP TABLE "precision_err";
    CREATE TABLE "TestPrecision"(
       munie decimal(18,4));
    .BEGIN LOAD SESSIONS 1
       ERRORTABLE "precision_err"
       NOMONITOR
       ROBUST ON;
    .LAYOUT precision_layout INDICATORS;
    .FIELD munie * decimal(18,4);
    .DML LABEL precision_label;
    INSERT INTO "TestPrecision"(munie) VALUES(:munie);
    .IMPORT INFILE "C:\WINNT\Profiles\Personal\sql_test08.amj"
       AXSMOD OLEDB_AXSMOD 'noprompt'
       LAYOUT precision_layout
       APPLY precision_label;
    .END LOAD;
    .LOGOFF;

    Using BTEQ

    To load to Teradata Database using BTEQ

    1 Select Start >Programs >Teradata Client >BTEQ to open Teradata BTEQ from the Windows desktop.

    2 Use the IMPORT command to specify the following:

     

    FILE Parameter

    Operating Specification

    Outcome

    "<pathname>.amj"

    'noprompt'

    Script runs if no errors exist. If errors exist, the script fails.

    "<pathname>.amj"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens so you can run the script using the access module.

    "UNTITLED"

    'noprompt'

    Script fails. Instead, specify an .amj file name instead of UNTITLED.

    "UNTITLED"

    '<blank>'

    The Teradata OLE DB AXSMOD dialog box opens.

    BTEQ Load Example

    .LOGON bubbater/dbc,dbc;
    database bubba ;
    drop table "Customers";
    CREATE TABLE "Customers" (CustomerID CHAR(5) NOT NULL,
          CompanyName VARCHAR(40) NOT NULL,
          ContactName VARCHAR(30),
          ContactTitle VARCHAR(30),
          Address VARCHAR(60),
          City VARCHAR(15),
          Region VARCHAR(15),
          PostalCode VARCHAR(10),
          Country VARCHAR(15),
          Phone VARCHAR(24),
          Fax VARCHAR(24));
    .IMPORT indicdata file="C:\mload.amj" AXSMOD OLEDB_AXSMOD 'noprompt';
    .REPEAT *using (CustomerID CHAR(5),
          CompanyName VARCHAR(40),
          ContactName VARCHAR(30),
          ContactTitle VARCHAR(30),
          Address VARCHAR(60),
          City VARCHAR(15),
          Region VARCHAR(15),
          PostalCode VARCHAR(10),
          Country VARCHAR(15),
          Phone VARCHAR(24),
          Fax VARCHAR(24))
       INSERT INTO "Customers"(
          CustomerID, CompanyName,
          ContactName, ContactTitle,
          Address, City,
          Region, PostalCode,
          Country, Phone,
          Fax)
     VALUES(
          :CustomerID, :CompanyName,
          :ContactName, :ContactTitle,
          :Address, :City,
          :Region, :PostalCode,
          :Country, :Phone,
          :Fax);
    .LOGOFF;
    .QUIT;

    Using Teradata PT

    To load data from Teradata Database using Teradata PT, ensure that the Teradata PT job script contains the following specifications:

  • TYPE definition is DATACONNECTOR PRODUCER
  • AccessModuleName attribute is OLEDB_AXSMOD
  • FileName attribute is set to the pathname of the .amj file
  • For information about loading data from Teradata Database using Teradata PT, see “Loading Data” in the Teradata Parallel Transporter User Guide (B035‑2445).