15.10 - Job Files - Access Module

Teradata Tools and Utilities Access Module Reference

prodname
Access Module
vrm_release
15.10
category
Programming Reference
featnum
B035-2425-035K

Job Files

An access module job is a set of parameters defining OLE DB data source information to the Teradata OLE DB Access Module. Access module jobs include the following parameters:

  • Data source details for both of the data sources supplying and receiving data
  • Table name for the data source supplying data
  • List of columns for the data source supplying data
  • The Teradata OLE DB Access Module uses .amj files that include the name of the file containing the information used by the access module when the job was executed; for example, filename.amj. An .amj file that loads a table from Oracle might include such information as the specific OLE DB provider to be used, the name of the Oracle database server containing the table to be loaded, the user name and password to log on that server, and the name of the table to be loaded.

    Note: The Teradata OLE DB Access Module does not use the convention of using the name of the data source as the pathname for an operation.

    Use the filename.amj name for the following:

  • FILE = filename specification in the IMPORT command in a BTEQ load job script
  • FILE = filename specification in the EXPORT command in a BTEQ export job script
  • OUTFILE fileid specification in the .EXPORT command in a FastExport job script
  • FILE = filename specification in the DEFINE command in a FastLoad job script
  • INFILE filename specification in the IMPORT command in a MultiLoad or TPump job script
  • Value of the FileName attribute specification in the DATACONNECTOR operator definition in a Teradata PT job script
  • File Format

    The Teradata OLE DB Access Module creates .amj files, which are saved in an extensible markup language (XML) based text format. You can use Teradata OleLoad to view, edit, and save access module job files.

    Note: Access module job files adhere to version 1.0 of the XML specification. Binary format files created by earlier versions can be opened by the Teradata OLE DB Access Module; however, XML‑based .amj files cannot be opened with the versions earlier than 02.02.00.

    Some text is altered in an access module job file when the text contains illegal characters or characters that already have assigned meaning in XML. Altered text is identical to the unaltered text for all characters except for the following:

  • Less‑than symbol (<)
  • Greater‑than symbol (>)
  • Equal sign (=)
  • Ampersand (&)
  • All other characters for which the UTF‑16 encoding of the character is not in the range [ 0x0020 – 0xd7ff ] or in the range [ 0xe000 – 0xfffd ]
  • Altered characters are replaced with an equal sign (=) followed by four hexidecimal digits representing the UTF‑16 encoded value of the character. For example, an ampersand is replaced by =0026 because the UTF‑16 encoding for an AMPERSAND is 0x0026.

    Example

    The following example, which explains the contents of an .amj file, assumes an understanding of the XML specification and the format is subject to change without notice.

    Files begin with an XML declaration specifying the XML version and character encoding used and may have comments interspersed as permitted by the XML specification; these comments are discarded when the file is processed.

    Following the XML declaration is a processing instruction containing the version number of the first version of the Teradata OLE DB Access Module that handled the .amj file:

    OLE_DB_AXSMOD_<FirstCompatibleVersion>

    After this processing instruction is the root element, named OLE_DB_AXSMOD_Jobs, which has one child element, named Job, which has four child elements: Source, Destination, CharacterEncoding, and might contain CheckpointInterval, LargeDecimalSupport, RowsPerFetch, BufferSize, and EnableScrollBackwards.

  • Source – The Source element can contain:
  • One child element named DataSourceParseName
  • A DataSourceParseName element contains an altered parse name uniquely identifying the selected OLE DB provider.
  • One child element named DataSourceProperties
  • A DataSourceProperties element contains the values of all properties needed to initialize the provider, including provider‑specific properties. It contains one child PropertySet element for each property set supported by the provider.
  • Each PropertySet element contains one child PropertySetId element followed by one child Property element for each supported property in that property set.
  • A PropertySetId element contains identification of a property set. For some property sets, the Teradata OLE DB Access Module associates a symbolic name for the property set; for example, DBPROPSET_DBINIT. For these property sets, the identification is the altered symbolic name. For other property sets (such as, many provider‑specific property sets) the identification is an altered textual representation of the GUID identifying the property set; for example, {c200e360‑38c5‑11ce‑ae62‑08002b2b79ef}.
  • Each Property element contains one PropertyId element followed by one PropertyType element followed by one PropertyValue element.
  • A PropertyId element contains the identification of a property (within a property set). For some properties, the Teradata OLE DB Access Module associates a symbolic name for the property; for example, DBPROP_INIT_TIMEOUT. For these properties, the identification is the altered symbolic name. For other properties (such as, many provider‑specific properties), the identification is the altered textual hexadecimal representation of the integer identifying the property; for example, 0x43.
  • A property's value may be included in one of two ways. The first method is used when the property value can be converted to a string (VT_BSTR) and then back to the original value using the features supplied by the COleVariant class (which is part of the Microsoft Foundation Classes (MFC)). Otherwise, the second method is used.

    When the first method is used, a PropertyType element contains the data type associated with the property and a PropertyValue element contains the altered textual representation of the value for the property. For some PropertyType elements, the Teradata OLE DB Access Module knows a symbolic name for the data type; for example, VT_I4. For these types, PropertyType contains the altered symbolic name. For other data types, PropertyType contains the altered textual hexadecimal representation of the type number; for example, 0x43.

    When the second method is used, PropertyType contains “ARCHIVED” and the PropertyValue element contains the altered byte pattern obtained in an archive by dumping the property value to an archive (managed by an object of the MFC CArchive class) using the COleVariant insertion (<<) operator.

    Note: To learn more about OLE DB defined properties, see the OLE DB Programmer's Reference at http://msdn.microsoft.com/library/. For documentation of provider‑specific properties, consult the documentation or supplier of the relevant OLE DB provider.

  • One child element named TableSelection or one child element named TableName or one child element named TableCommand.
  • The TableSelection element is present when a source table is selected from the tree control. It contains one child element named Catalog, followed by one child element named Schema, followed by one child element named Name.
  • The Catalog element contains the altered name of the catalog of the table selected by the user. The Schema element contains the altered name of the schema of the table selected by the user. The Name element contains the altered name of the table selected by the user.
  • When a source table is specified by name using the edit control, the Source element has a child element named TableName containing the altered table name.

    The TableCommand element is present when a source table has been specified by command using the edit control, for example, a SQL SELECT... command.

    Note: The precise manner in which the OLE DB provider uses catalog names, schema names, table names, and commands is specific to the provider. Consult the documentation or supplier of the relevant OLE DB provider for details relating to a particular provider.

  • One child element named LocationOfLogTables and one child element named OtherDatabase:
  • The contents of the LocationOfLogTables depends on which option you select in the Location of log tables frame on the Teradata OleLoad – Advanced Settings dialog box. If you select the User's default database option, then the LocationOfLogTables element is zero. This option is enabled in the export job operation. If you select the Source database option, then the LocationOfLogTables element is 1. If you select the Other database option, then the LocationOfLogTables element is 2.
  • Note: The OtherDatabase element contains the altered string from the Other database box in the Teradata OleLoad – Advanced Settings dialog box.

  • One child element named Columns:
  • The Columns element contains one child element named Column for each column in the selected source table. Each Column element contains one empty child element named Selected, if the column is selected. If the column is not selected, the Selected child element is not present. Each Column element also contains one child element named SourceName, followed by one child element named DestinationName, followed by one child element named TypeName.
  • A SourceName element contains the altered name of the source column. This is the name that appears in the Src. Column Name column of the list of available columns in OleLoad. When you click Teradata Database from the Select a source list, this name is derived from the source Teradata Database by issuing a HELP COLUMN command and removing trailing SPACE (“ ”) characters from the returned Column Name value. When you select an OLE DB provider from the Select a source list, this name is the name returned in the pressmen field of the DBCOLUMNINFO structure by the IColumnsInfo::GetColumnInfo() method when applied to the source table.
  • A DestinationName element contains the altered destination column name. This is the name that appears in the Dest. Column Name column of the list of available columns in OleLoad. Often this name is the same as the source column name, but it need not be the same. You can change the name using OleLoad.
  • A TypeName element contains the altered Teradata data type name for the column. This is the name that appearing in the Data Type column of the list of available columns.
  • Destination – The Destination element contains one child element named TableName, and may contain one child element named DataSourceParseName, one child element named DataSourceProperties, one child element named ReferentialIntegrityIsChecked, and one child element named IndexUpdatesAreRequired.
  • A TableName element that is a child of the Destination element contains the altered name of the destination table. This is the name in the Table name box in the Edit the table name frame of the Teradata OleLoad – Advanced Settings dialog box.
  • If you select the Referential integrity check check box in the Bulk Loading Options frame of the Teradata OleLoad – Advanced Settings dialog box, the ReferentialIntegrityIsChecked element is present. This element sets the DBPROPVAL_BO_REFINTEGRITY bit in the DBPROP_ROW_BULKOPS property in the DBPROPSET_ROWSET property set used to create the rowset for the destination table. The ReferentialIntegrityIsChecked element serves as a hint to the destination provider that referential integrity constraints do not need to be checked or enforced for changes made through the rowset. This is effective only during exports to a provider that supports DBPROPVAL_BO_REFINTEGRITY.

    If you select the Index updates required check box in the Bulk Loading Options frame of the Teradata OleLoad – Advanced Settings dialog box, the IndexUpdatesAreRequired element is present. This element sets the DBPROPVAL_BO_NOINDEXUPDATE bit in the DBPROP_ROW_BULKOPS property in the DBPROPSET_ROWSET property set used to create the rowset for the destination table. The IndexUpdatesAreRequired element serves as a hint to the destination provider that the provider is not required to update indexes based on inserts or changes to the rowset. Any indexes need to be recreated following changes made through the rowset.

  • Character Encoding – The CharacterEncoding element contains the altered character set name for this job.
  • CheckpointInterval – The CheckpointInterval element contains the altered string from the Checkpoint Interval box in the Teradata OleLoad – Advanced Settings dialog box when you enter any integral value.
  • LargeDecimalSupport – The LargeDecimalSupport element contains Supported and NotSupported. If Supported, the access module can return DECIMAL values greater than 18 digits; otherwise the maximum returned DECIMAL values is 18 or less.
  • RowsPerFetch – The RowsPerFetch element, if present, contains the value specified in the Advanced Settings dialog box.
  • BufferSize – The BufferSize element, if present, contains the value specified in the Advanced Settings dialog box.
  • EnableScrollBackwards – The EnableScrollBackwards element, if present, contains the value specified in the Advanced Settings dialog box.