DB Store - Data360_Analyze - 3 - 3.12

Data360 Analyze Server Help

Product type
Software
Portfolio
Verify
Product family
Data360
Product
Data360 Analyze
Version
3.12
Language
English
Product name
Data360 Analyze
Title
Data360 Analyze Server Help
Copyright
2023
First publish date
2016

Stores the incoming data set as records within a relational database using ODBC, OCI (Oracle), or CLI (Teradata) interfaces.

Data is loaded from the input pin into the specified database table. Any records that produce an error on loading are placed upon the ErroredData output pin. If an optional, second output pin is present, then the data from the input pin will be placed on that output pin, as long as EnableLoadedDataOutput is set to true.

Configure the node by specifying the name of the table into which the input data is loaded in the DbTable property. Then, enter the database connection details in the mandatory DbUser, DbPassword and DbService properties, and optionally in the DbInterface property.

Properties

DbTable

Specify the name of the database table into which the input data is loaded.

A value is required for this property.

DbUser

Specify the database username to connect to the database and load the table.

A value is required for this property.

DbPassword

Specify the database user's password to connect to the database. This password is stored as clear text in the data flow and sent via clear text to the Data360 Analyze server. Therefore, it's important either to put this password into a data flow-level property or to use a user with limited privileges.

A value is required for this property.

DbService

Specify the database instance name, for example:

CustomerInfo

A value is required for this property.

DbInterface

Optionally specify the type of interface. Enter one from the following (bold text only):

  • oci - Oracle
  • cli - Teradata
  • odbc - MS Access and SQL Server

The default value is oci.

AllowSpaces

Optionally specify whether to preserve spaces in field names when mapping them to database column names. Choose false to convert spaces to underscores (_).

The default value is False.

LoadMethod

Optionally specify the method via which the input data is loaded into the database. Choose from:

  • insert - default method for loading input data into the database
  • optimized - uses direct loading, taking less time than insert. Not all triggers and default values will be applied, which could cause your data to be inconsistent in the database.

The default value is insert.

CommitFrequency

Optionally specify the number of rows that are processed before the data is committed. The larger the number, the more data that is loaded into the database before a commit is made.

In general, smaller values will require the store to run more slowly, while a larger value causes the store to run more quickly. However, if the value is too high, then the store might take even longer than with a small CommitFrequency. A high value could also cause data on the optional data output pin not to match the data that actually was stored in the database.

EnableLoadedDataOutput

Optionally specify whether to send all records that load into the database to the second output pin, which the user must create. The purpose of this pin is to provide a list of all input records that were loaded (in contrast to the list on the ErroredData pin which contains all rows that did not load).

Data output to this second pin is taken directly from the input, not from the database itself. Therefore, defaults will not appear in the output data, and changes made by triggers will not be reflected either.

The default value is False.

ParallelLoad

Optionally specify whether to inform the database loader service that it should perform the load in such a way that multiple DB Store nodes can load simultaneously. Depending on the method of loading, this can impose extra constraints on the table being loaded to and on the data being loaded.

Known constraints for optimized OCI loads:

  • Neither local nor global indexes can be maintained by the load.
  • Referential integrity and check constraints must be disabled.
  • Triggers must be disabled.

The default value is False.

IndexMaintenance

Optionally specify an option to specify how indexes on the target database table should be handled during optimized loads into the table. Choose from:

  • skipUnusable - skip unusable index maintenance
  • skipAll9i - skip all index maintenance for 9i
  • skipAll10g - skip all index maintenance for 10g

This property only applies to loads into Oracle databases when LoadMethod is set to optimized. The options provided allow for skipping all index maintenance or skipping unusable index maintenance. Skipping all index maintenance will cause all indexes to be marked unusable. The 9i and 10g values should only be used with 9i and 10g respectively. For information on the values that can be entered into this property, please refer to the Oracle documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_params.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/ociaahan.htm#i444702

DefaultDate

Optionally specify the date to use when a date is required and none is specified. The most common case where this value is used is when coercing a value from a time type into a datetime type. In this case, the date portion of the datetime is taken from this property. Choose from:

  • epoch - 1970-01-01
  • today

Alternatively, the user can enter a date in the format YYYY-MM-DD.

The default value is epoch.

ExtraFieldBehavior

Optionally specify how this node behaves if the input data has a field that is not present in the database table. Choose from:

  • log - log the extra field and continue processing
  • error - raise an error
  • ignore - ignore the extraneous field and continue processing without logging anything

The default value is log.

MissingColumnBehavior

Optionally specify how this node behaves if the input is missing a field that exists in the database. Choose from:

  • log - log the missing field and continue processing
  • error - raise an error
  • ignore - ignore the missing field and continue processing without logging anything

The default value is error.

AbortThreshold

Optionally specify the number of errors that are tolerated before this node stops processing and raises an error. In most cases, there is only one error per record. Thus, the AbortThreshold could be considered as the number of records on the ErroredData pin before this node stops processing the input.

The default value is 0.

Inputs and outputs

Inputs: in1.

Outputs: ErroredData, 1 optional.