User Guide > Plans and Workflows > Source Settings
  

Source Settings

The source level properties are a subset of plan settings. You can change the settings for all the tables in the source or you can change the settings for each table in the source.
If a property is not available at the data source level, the property value at the plan level takes precedence. A lookup connection appears in the settings if there is a mapplet rule that is associated with a column in the data source.
To change settings at the source or table level, choose the Source Settings tab when you edit the plan.

Connection Properties

The following table describes connection options that you can configure at the data source level and the table level:
Connection Options
Description
Source Connection
A connection to the source database. Select a source connection from the list. For Hadoop sources, you can select a Hive or an HDFS connection.
Source Connection Directory Path
The path to the flat file source directory. Appears if flat file sources are present.
Source Filename
The name of the source file. Required if the source is a flat file. Default is <name of selected source>.dat.
Target Connection
A connection to the target database or the target file. Choose variable, relational, or flat file from the list. Select a target connection from the list. For Hadoop targets, you can select a Hive or an HDFS connection.
Note: If you enter the target connection as a variable, ensure that you define the scope of the variable as Global or Integration Service in the parameter file.
Target JDBC Connection
Appears if you select an ODBC target connection type. The connection that contains the JDBC connection string of the ODBC target database.
Required if the target connection type is ODBC and either of the following statements is true:
  • - You want to disable and enable constraints in the plan.
  • - The source connection type is ODBC and you want to generate mappings with the source data type.
Optional if the target is ODBC and you want to perform simple data movement operations.
Output Filename
The name of the target file. Required if the target is a flat file. Default is <name of selected source>.out.
File Encoding
The file encoding type. Required if the target is a flat file. If the source is a flat file, the default type is what the flat file contains.
If the source is relational, default is MS Windows Latin 1 (ANSI), superset of Latin1. You can select the type of flat file encoding that you want in the target.
File Format
The format of a flat file. Required if the target is a flat file.
You can choose one of the following options:
  • - Fixed Width. The width of the columns is fixed. You cannot specify a column delimiter.
  • - Delimited. You can limit the width of the columns. Specify a column delimiter.
Default is Delimited.
Column Delimiter
A character that separates columns of data. Required if the source is a flat file and if the file format is delimited. Default is a comma (,).
Row Separator
A character that separates rows of data. Required if the source is a flat file. Default is \012 LF (\n).
Optional Quotes
Select No Quotes, Single Quote, or Double Quotes. If you select a quote character, the Integration Service ignores delimiter characters within the quote characters. Therefore, the Integration Service uses quote characters to escape the delimiter.
For example, a source file uses a comma as a delimiter and contains the following row:
342-3849, ‘Smith, Jenna’, ‘Rockville, MD’, 6.
If you select the optional single quote character, the Integration Service ignores the commas within the quotes and reads the row as four fields.
If you do not select the optional single quote, the Integration Service reads six separate fields.
When the Integration Service reads two optional quote characters within a quoted string, it treats them as one quote character. For example, the Integration Service reads the following quoted string as
I’m going tomorrow:
2353, ‘I’’m going tomorrow’, MD
Additionally, if you select an optional quote character, the Integration Service reads a string as a quoted string if the quote character is the first character of the field.
Note: You can improve session performance if the source file does not contain quotes or escape characters.
Include Headers
Optional. You can choose to include the headers in the target flat file. Default is Yes.
Source File Name
The name of the IMS source file or VSAM source file. If the source is a Hierarchical IMS file, enter the name of the source unload file. If the source is a Hierarchical VSAM/SEQ file, enter the name of the source file. Required if the source connection is PXW_NRDB Batch.
Target File Name
The name of the IMS target file or VSAM target file. If the target is a Hierarchical IMS file, enter the name of the target unload file. If the target is a Hierarchical VSAM/SEQ file, enter the name of the target file. The target file name must be different from the source file name. Required if the target connection is PXW_NRDB Batch.
Teradata ODBC Connection
Name of the Teradata ODBC connection. Required if the target connection is Teradata PT. Select a connection from the list.
Lookup Connection
Connection to a database that contains lookup tables. Required if the plan has a mapplet rule which includes a transformation. Choose relational or flat file from the list. Select a lookup connection from the list.
Dictionary Connection
A connection to the database that contains the dictionary table. The dictionary connection option does not appear unless the project contains a rule that requires a relational dictionary.
Email Dictionary Connection
A connection to the database that contains the email dictionary table. The email dictionary connection option does not appear unless the project contains a masking rule that requires a relational email dictionary.

Test Tool Integration Properties

The following table describes the test tool integration properties that you can configure at the data source level:
Test Tool Integration Options
Description
Attach to Test Tool
Select this option to copy the flat file target results to a location in the HP ALM server integrated with TDM.
You must run the workflow from Test Data Manager. The test tool integration job does not run when you run the workflow from the PowerCenter client.
Project
Required. Browse to select the project in the HP ALM server where you want to store the flat file results.

Target Properties

The following table describes target options that you can configure at the data source level and at the source level:
Other Property Options
Description
Truncate Table
Truncates the table before loading it. Default is disabled.

Update Strategy Properties

The following table describes strategy options that you can configure at the data source level:
Update Strategy Option
Description
Treat source rows as
Indicates how the PowerCenter Integration Service treats source rows. Choose Insert or Update. If an imported PowerCenter mapplet contains a transformation that is configured to set the update strategy, the default option is Insert.
Update as
Determines whether the PowerCenter Integration Service updates or inserts rows.
Choose one of the following options:
  • - Update. The PowerCenter Integration Service updates all rows flagged for update.
  • - Insert. The PowerCenter Integration Service inserts all rows flagged for update.
  • - Update else Insert. The PowerCenter Integration Service updates rows flagged for update if they exist in the target, and inserts remaining rows marked for insert.

Error and Recovery Properties

The following table describes connection options that you can configure at the data source level:
Error and Recovery Options
Description
Commit Type
Choose a source-based or target-based commit. By default, the PowerCenter Data Integration Service performs a target-based commit.
Commit Interval
The number of rows to process before a commit. Default is 10,000.

Source and Target Properties

You can override the name of the table owner if you want to select a table from a different schema.
The following table describes source and target options that you can configure at the data source level:
Source and Target Options
Description
Imported Source Owner Name
The default name of the source owner.
Runtime Source Owner Name
The name of the table owner from which you want the source data. Specify the owner name of the source table if you need the table from a different schema. Enter the schema name in uppercase unless the schema name is case sensitive in the database. If the schema name is case sensitive in the database, enter the name as in the database.
Target Prefix Name
The name prefix of the target table. Specify the target schema name if the target is in a different schema.

Advanced Properties

The following table describes advanced options that you can override at the data source level and at the table level:
Advanced Options
Description
Datetime Format String
Date-time format defined in the session properties. You can enter seconds, milliseconds, microseconds, or nanoseconds.
  • - Seconds. MM/DD/YYYY HH24:MI:SS
  • - Milliseconds. MM/DD/YYYY HH24:MI:SS.MS
  • - Microseconds. MM/DD/YYYY HH24:MI:SS.US
  • - Nanoseconds. MM/DD/YYYY HH24:MI:SS.NS
Default is microseconds.
Target Load Type
Choose Normal or Bulk.
If you select Normal, the PowerCenter Integration Service loads targets normally. Choose Normal mode if the mapping contains an Update Strategy transformation.
Choose Bulk to improve session performance. Bulk load limits the ability to recover because no database logging occurs.
Default is Normal.
Target Schema Name
Table name prefix. Specify the target schema name if the target is in a different schema.
Source Schema Name
Table owner name. Specify the source schema name if the source is in a different schema.
Source Pre SQL
SQL query executed before the plan is run. Required if you need to perform an operation before you run the plan. Enter the complete SQL statement.
Source Post SQL
SQL query executed after the plan is run. Required if you need to perform an operation after you run the plan. Enter the complete SQL statement.
Batch Size
The number of target rows that you want to move at a time. This option is available when you select the Batch Update as Yes. Use with inplace masking.
Note: The batch size must be greater than the total number of records.
Use Oracle ROWID in Source
Determines whether to use the ROWID from Oracle sources as a column. The ROWID indicates the physical location of a row in the database. Enabled when the Oracle source and target connections are the same.
Performance Hint
Optimizes the SQL query after the plan is run. Enter a performance hint value only for Oracle databases.
.

Partitioning Properties

You must specify partitioning details if the tables in the source database contain partitions.
The following table describes partitioning options that you can configure at the table level:
Partitioning Property
Description
Enable Partitioning
Select to enable partitioning of output tables.
Partition Type
Select the type of partitioning:
  • - Pass Through. Use pass through partitioning to increase data throughput without increasing the number of partitions.
  • - Key Range. Use key range when the source data tables are partitioned by key range.
  • - Database Partitioning. Use database partitioning for Oracle or IBM DB2 sources and targets.
Number of partitions
The number of partitions in the source data. Enter the number of partitions and click Select to enter partition information for the target data.

XSD Data Source Connection Settings

You can override the plan settings and enter different source and target file directories in a plan that contains XSD data sources. The settings apply to all files in the directory. You can override the data source connection settings for individual files in the directory.
The following table describes the connection options that you can configure at the data source level and at the file level:
Connection Options
Description
Source File Directory
The path to the XML source file directory. The source file must reside on the server on which the PowerCenter Integration Service runs.
Target File Directory
The path to the XML target file directory.
Source Type
Indicates whether the source file contains the source data or whether the source file contains a list of files with the same file properties. Use to configure multiple file sources with a file list.
  • - Direct. The source file contains the source data.
  • - Indirect. The source file is an index file. The index file contains a list of file names. The PowerCenter Integration Service finds the index file and reads each listed file when it runs the session. The index file must have an empty line at the end of the file.
Default is Direct.
Source File Name
The file name or file name and path. Optionally, use the $InputFileName session parameter for the file name.
If you specify both the directory and file name in the Source File Directory field, clear this field. The PowerCenter Integration Service concatenates this field with the Source File Directory field when it runs the session. For example, if you have "C:\XMLdata\" in the Source File Directory field, then enter "filename.xml" in the Source File Name field. When the PowerCenter Integration Service begins the session, it looks for "C:\XMLdata\filename.xml".
Output File Name
The file name, or file name and path. Optionally, use the $OutputFileName session parameter for the file name.
If you specify both the directory and file name in the Output File Directory field, clear this field. The PowerCenter Integration Service concatenates this field with the Output File Directory field when it runs the session.

XSD Data Source Advanced Settings

You can override the plan settings for XSD sources in a plan that contains XSD data sources. The settings apply to all files in the directory. You can override the data source connection settings for individual files in the directory.
The following table describes the advanced options that you can configure at the file level:
Advanced Options
Description
XML date-time format
Date-time format defined in the session properties. Choose one of the following values:
  • - Local Time. The time according to the PowerCenter Integration Service server time zone.
  • - Local Time with Time Zone. The difference in hours between the PowerCenter Integration Service time zone and Greenwich Mean Time.
  • - UTC. Greenwich Mean Time.
Default is Local Time.
Treat empty content as NULL
Treat empty XML components as null. By default, the PowerCenter Integration Service does not output element tags for null values. The PowerCenter Integration Service outputs tags for empty content. Default is No.
Format Output
Format the XML target file so the XML elements and attributes indent. If you do not select Format Output, each line of the XML file starts in the same position.
Default is No.
NULL content representation
Choose how to represent null content in the target. Default is No tag.
Empty String content representation
Choose how to represent empty string content in the target. Default is Tag with empty content.
NULL attribute representation
Choose how to represent null attributes. Default is No attribute.
Empty String attribute representation
Choose how to represent empty string attributes in the target. Default is Attribute with empty string.