Configuring a database ingestion and replication task

1. 1Define basic task information, such as the task name, project location, runtime environment, and load type.
2. 2Configure the source.
3. 3Configure the target.
4. 4Configure runtime options.

Before you begin

• •Check that your organization has licenses for Database Ingestion and Replication and the DBMI packages.
• •Verify that the Secure Agent in your runtime environment is running and that you can access the Data Ingestion and Replication service.
• •Define the source and target connections.

Defining basic task information

1Start the task wizard in one of the following ways:
• - On the Home page, click the Ingest panel and select Database Ingestion and Replication Task.

• - In the navigation bar on the Explore page or the Home page, click New to open the New Asset dialog box. Then, select Data Ingestion and Replication > Database Ingestion and ReplicationTask and click Create.

Note: If your organization does not have a custom license for application, database, file, or streaming ingestion and replication, the Data Ingestion and Replication category still appears but you cannot select and configure an ingestion and replication task of the unlicensed task type or types.
The Definition page of the database ingestion and replication task wizard appears.
2Configure the following properties:

Property	Description
Name	A name for the database ingestion and replication task. Task names can contain Latin alphanumeric characters, spaces, periods (.), commas (,), underscores (_), plus signs (+), and hyphens (-). Task names cannot include other special characters. Task names are not case sensitive. Maximum length is 50 characters. Note: If you include spaces in the database ingestion and replication task name, after you deploy the task, the spaces do not appear in the corresponding job name.
Location	The project or project\folder that will contain the task definition. The default is the currently selected project or project subfolder in Explore. If a project or project subfolder is not selected, the default is the Default project.
Runtime Environment	The runtime environment in which you want to run the task. The runtime environment must be a Secure Agent group that consists of one or more Secure Agents. A Secure Agent is a lightweight program that runs tasks and enables secure communication. For database ingestion and replication tasks, the Cloud Hosted Agent is not supported and does not appear in the Runtime Environment list. Serverless runtime environments are also not supported. Tip: Click the Refresh icon to refresh the list of runtime environments.
Description	An optional description for the task. Maximum length is 4,000 characters.
Load Type	The type of load operation that the database ingestion and replication task performs. Options are: - Initial Load. Loads data read at a specific point in time from source tables to a target in a batch operation. You can perform an initial load to materialize a target to which incremental change data will be sent. - Incremental Load. Propagates source data changes to a target continuously or until the job is stopped or ends. The job replicates the changes that have occurred since the last time the job ran or from a specific start point for the first job run. - Initial and Incremental Loads. Performs an initial load of point-in-time data to the target and then automatically switches to replicating incremental data changes made to the same source tables on a continuous basis. Note: If a change record is captured during the initial unload load phase, it's withheld from apply processing until after the unload phase completes. Any insert rows captured during the unload phase are converted into a pair of delete and insert operations so that only one insert row is applied to the target in the case where the insert occurs in both the unloaded data and the captured change data.

3Click Next.

Configuring the source

1In the Connection list, select the connection for the source system. The connection type appears in parentheses after the connection name.
The connection must be predefined in Administrator for a runtime environment that your organization uses.
The list includes only the connection types that are valid for the load type selected on the Definition page. No connections are listed if you did not select a load type.
If you change the load type and the selected connection is no longer valid, a warning message is issued and the Connection field is cleared. You must select another connection that is valid for the updated load type.
Note: After you deploy the database ingestion and replication task, you cannot change the connection without first undeploying the associated job. After you change the connection, you must deploy the task again.
2In the Schema list, select the source schema that includes the source tables. If you specified a schema in the connection properties, it is selected by default but you can change it.
The list includes only the schemas that are available in the database accessed with the specified source connection.
When creating a task that has an Oracle, Microsoft SQL Server, Netezza, or PostgreSQL source, the schema name that is specified in the connection properties is displayed by default.
3If you are defining a Db2 for i source for an incremental load task, in the Journal Name field, select the name of the journal that records the changes made to the source tables.
4If you are defining a PostgreSQL source for an incremental load or combined initial and incremental load task, complete the following fields:

Field	Description
Replication Slot Name	The unique name of a PostgreSQL replication slot. A slot name can contain Latin alphanumeric characters in lowercase and the underscore (_) character. Maximum length is 63 characters. Important: Each database ingestion and replication task must use a different replication slot.
Replication Plugin	The PostgreSQL replication plug-in. Options are: - pgoutput. You can select this option only for PostgreSQL version 10 and later. - wal2json
Publication	If you selected pgoutput as the replication plug-in, specify the publication name that this plug-in uses. Note: This field is not displayed if you selected wal2json as the replication plug-in.

5If you are defining an incremental load or combined initial and incremental load task that has a Db2 for LUW, Oracle, or SQL Server source, select the capture method that you want to use under Change Data Capture Method.
1. aIn the CDC Method field, select one of the following options to indicate the method to use for capturing source changes:

Method	Supported Sources	Description
CDC Tables	SQL Server only	Read data changes directly from the SQL Server CDC tables. For SQL Server sources, this method provides the best replication performance and highest reliability of results.
Log-based	Oracle and SQL Server	Capture Inserts, Updates, Deletes, and column DDL changes in near real time by reading the database transaction logs. For Oracle sources, data changes are read from the Oracle redo logs. For SQL Server sources, data changes are read from the SQL Server transaction log and the enabled SQL Server CDC tables. Exception: For Azure SQL Database sources, data changes are read from CDC tables only.
Query-based	Db2 for LUW, Oracle, and SQL Server	Capture Inserts and Updates by using a SQL WHERE clause that points to a CDC query column. The query column is used to identify the rows that contain the changes made to the source tables since the beginning of the CDC interval. For Db2 for LUW sources in incremental load and initial and incremental load jobs, this capture method is the only available option.

2. bIf you selected the Query-based option, complete the following additional fields:
• ▪ CDC Query Column Type. The column type of the CDC query column in the source table. The only available option is Timestamp.
Note: "Timestamp" represents a column data type that combines the date and time. For Oracle, the supported data type for the query column is TIMESTAMP. For SQL Server, the supported data types for the query column are DATETIME and DATETIME2.
• ▪ CDC Query Column Name. The case-sensitive name of the CDC query column in the source table. The column must be present in the source table. Maximum length is 70 characters.
• ▪ CDC Interval. The frequency of a query-based change data capture cycle, expressed in days, hours, and minutes. You must enter a positive number in at least one of the interval fields. Otherwise, an error will be issued when you try to save the task. The default value is 5 minutes.
6Under Table Selection, use one of the following methods to select source tables:
- Select Select All to select all tables and columns in the schema for data replication. The Tables Selected field shows the count of all selected tables. You won't be able to edit the selection later under Table View.
Note: Fetching information for all of the tables might take a long time.
• - Under Rules, define rules to specify a subset of source tables to replicate. If you use rules, you'll be able to individually select or deselect tables and the columns in selected tables under Table View. You'll also be able to set an option for trimming spaces in character data. To add rules, go to 8.
7If you want to include database views as sources, select the Include Views check box to the right of the Refresh icon. This check box is available only for initial load tasks that have a Db2 for i, Db2 for LUW, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, or Teradata source.
The views will be fetched and included in the Tables Selected count and in the list of table names.
8To add a table-selection rule, first make sure that the Select All check box is cleared. Then perform the following substeps:
1. aUnder Rules, click the Add Rule (+) icon above the first table. A row is added to the table.
2. bIn the Table Rule column, select Include or Exclude to create an inclusion or exclusion rule, respectively.
3. cIn the Condition column, enter a table name or a table-name mask that includes one or more wildcards to identify the source tables to include in or exclude from table selection. Use the following guidelines:
• ▪ A mask can contain one or both of the following wildcards: an asterisk (*) wildcard to represent one or more characters and a question mark (?) wildcard to represent a single character. A wildcard can occur multiple times in a mask value and can occur anywhere in the value.
• ▪ The task wizard is case sensitive. Enter the table names or masks in the case with which the tables were defined.
• ▪ Do not include delimiters such as quotation marks or brackets, even if the source database uses them. For example, Oracle requires quotation marks around lowercase and mixed-case names to force the names to be stored in lowercase or mixed case. However, in the task wizard, you must enter the lowercase or mixed-case names without quotation marks.
• ▪ If a table name includes special characters such as a backslash (\), asterisk(*), dollar sign ($), caret (^), or question mark (?), escape each special character with a backslash (\) when you enter the rule.
4. dDefine additional rules as needed.
If you define multiple Include and Exclude rules, they'll be processed in the order in which they're listed, from top to bottom. Use the arrow icons to change the order. For an example of using multiple rules, see Example of rules for selecting source tables.
5. eWhen finished, click Apply Rules.
The Total Tables Selected and Table View counts are updated. If you click the Refresh icon, the Tables Affected count for each rule is shown.
The following image shows multiple rules defined on the Source page:

After you apply rules, if you add, delete, or change rules, you must click Apply Rules again. Click the Refresh icon to update the table counts. If you delete all rules without clicking Apply Rules, a validation error will occur at deployment, even if the Table View list still lists tables. If you switch to Select All, the rules are no longer in effect and disappear.
9To perform trim actions on character columns in the source tables selected based on rules, create column action rules in the second "Action" table under Rules.
Note: You cannot create column action rules for MongoDB sources.
1. aClick the Add Rule (+) icon above the second table.
2. bIn the Action column, select one of the following options:
• ▪ LTRIM. Trims spaces to the left of character column values.
• ▪ RTRIM. Trims spaces to the right of character column values.
• ▪ TRIM. Trims spaces to the left of and to the right of character column values.
3. cIn the Condition column, enter a column name or a column name mask that includes one or more asterisk (*) or question mark (?) wildcards. The value is matched against columns in the selected source tables to identify the columns to which the action applies.
Note: You can define multiple rules for different action types or for the same action type with different conditions. The rules are processed in the order in which they're listed, from top to bottom. Use the arrow icons to change the order.
10Under Table View, view or edit the set of selected source tables and columns.
If you selected Select All, the lists of tables and columns are view only.
If you applied rules, you can refine the set of selected tables by clicking the check box next to individual tables. Deselect any tables that you do not want to replicate, or select additional items to replicate. Click the Refresh icon to update the selected tables count.
For Oracle and SQL Server sources only, you can also individually deselect or reselect the columns in a selected source table. To view or change the columns from which data will be replicated for a selected table, click the highlighted number of columns in the Columns column. The column names and data types are displayed to the right. By default, all columns are selected for a selected source table. To deselect a column or reselect it, click the check box next to the column name. You cannot deselect a primary key column.
The following image shows selected tables and the selected columns for the first table:

Notes:
• - To search for tables or columns or both, in the drop-down list above the columns list, select Table Name, Columns, or All and then enter a search string in the Find box and click Search. You can include a single asterisk (*) wildcard at the beginning or end of the string.
• - The first time you change a check box setting for a table or column, the rules are no longer in effect. The selections under Table View take precedence. However, if you click the Add Rule (+) icon again, any tables that you deselected or selected individually are reflected as new rules in the Rules list and the rules once again take precedence. If you want to return to the Table View list, click Apply Rules again.
• - In the Table View section, the length of character columns is shown in bytes. Since the number of bytes per character differs depending on the character set encoding used by the database, for some sources, the actual column length in number of characters might be different than the number of bytes shown in the Table View.
• - If you select columns individually, the resulting set of columns is fixed and will not be updated by any schema change, regardless of schema drift settings. For example, if a source column is added or renamed, that column is silently excluded from CDC processing because it's not in the list of selected columns. However, if a selected column is dropped on the source, the schema drift Drop Column option controls how it's handled. The dropped column operation is not reflected in the list of columns until you apply rules again.
• - For combined initial and incremental load jobs, if you add to or modify a column selection, a resync operation is triggered automatically. The resync is required to ensure that the target table has the same values as the source. For incremental load jobs, which do not have a resync option, modifying a column selection results in an error. If you modify the column selection of a deployed job and then redeploy it, the source and target will no longer match.
Note: A resync operation is not triggered, and no error is reported, if you remove a selected column. Removal of a selected column is treated the same as a drop column event, triggering your drop column schema drift settings for the task.
11 If you are defining an incremental load or combined initial and incremental load task that has a Db2 for i, Db2 for z/OS, Microsoft SQL Server, Oracle, PostgreSQL, SAP HANA, or SAP HANA Cloud source and one or more of the selected source tables are not enabled for change data capture, you can generate a script for enabling CDC and then run or download the script.
Note: If you selected Query-based as the CDC method for Db2 for LUW, Oracle, or SQL Server sources, the CDC Script field is not available because it is not applicable for the query-based change capture method.
1. aIn the CDC Script field, select one of the following options:
• ▪ Enable CDC for all columns. Enables CDC for all columns in the selected source tables. This option is the only valid option for a Db2 for i, Db2 for z/OS, PostgreSQL, SAP HANA, SAP HANA Cloud, or SQL Server source.
Note: For source tables without a primary key, including any tables with unique indexes, CDC is enabled for all columns by default, regardless of which option is selected.
• ▪ Enable CDC for primary key columns. Enables CDC only for primary key columns in the selected source tables. Do not use this option for a Db2 for i, Db2 for z/OS, PostgreSQL, or SQL Server source or for any task that has a Google Big Query target.
The script enables CDC in the following ways, depending on the source type:
• ▪ For Db2 for i sources, the script enables journaling on the source tables.
• ▪ For Db2 for z/OS sources, the script sets DATA CAPTURE CHANGES for source tables and certain Db2 catalog tables needed for CDC. After DATA CAPTURE CHANGES is set for one job, all other jobs recognize that the attribute is enabled in Db2 for the catalog tables needed because the Db2 catalog tables are a set of tables shared by all users of Db2.
• ▪ For Microsoft SQL Server sources, the script runs the sys.sp_cdc_enable_db and sys.sp_cdc_enable_table stored procedures to enable CDC on the source database and tables. For RDS for SQL Server, the script runs the msdb.dbo.rds_cdc_enable_db procedure to enable CDC on the source database, and the sys.sp_cdc_enable_table script to track CDC for tables.
• ▪ For Oracle sources, the script enables supplemental logging for all or primary key columns in the selected source tables to log additional information in the redo logs.
• ▪ For PostgreSQL sources, the script sets REPLICATION IDENTITY FULL on the selected source tables to write all column values to the WAL file. The script also creates a replication slot of the type of pgoutput or wal2json. If the slot type is pgoutput, the script also creates the publication and adds tables to it.
• ▪ For SAP HANA and SAP HANA Cloud sources, the script creates the required PKLOG, PROCESSED, and _CDC shadow tables. The script also creates three triggers and a sequence for each selected source table.
2. bTo run the script, click Execute.
If you do not have a database role or privilege that allows you to run the script, click the Download icon to download the script. The script file name has the following format: cdc_script_taskname_number.txt. Then ask your database administrator to run the script.
Make sure the script runs before you run the database ingestion and replication task.
Note: If you change to the CDC Script option later and run the script again, the script first drops CDC for the original set of columns and then enables CDC for the current set of columns. For SAP HANA sources, if the PROCESSED and PKLOG tables already exist, they are omitted from the new script. If the shadow _CDC table and triggers already exist for any selected table, the SQL statements for creating those objects are commented out in the new script.
12For Microsoft SQL Server sources, complete the following fields:
• - In the Capture Filegroup field, enter the name of the filegroup to be used for the change table that is created for the capture. If you leave this field empty, the change table is located in the default filegroup of the database.
• - In the Gating Role field, enter the name of the database role that is used to gate access to change data. If you leave this field empty, the database does not use the gating role.
13To create and download a list of the source tables that match the table selection criteria, perform the following substeps:
1. aIf you used rule-based table selection, in the List Tables by Rule Type list, select the type of selection rules that you want to use. Options are:
• ▪ Include Rules Only
• ▪ Exclude Rules Only
• ▪ Include And Exclude Rules
2. bTo include columns in the list, regardless of which table selection method you used, select the Include Columns check box.
Note: This option is not available for MongoDB sources.
3. cClick the Download icon.
A downloaded list that includes columns has the following format:
status,schema_name,table_name,object_type,column_name,comment
The following table describes the information that is displayed in the downloaded list:

Field	Description
status	Indicates whether Database Ingestion and Replication excludes the source table or column from processing because it has an unsupported type. Valid values are: - E. The object is excluded from processing by an Exclude rule. - I. The object is included in processing. - X. The object is excluded from processing because it is an unsupported type of object. For example, unsupported types of objects include columns with unsupported data types and tables that include only unsupported columns. The comment field provides detail on unsupported types.
schema_name	Specifies the name of the source schema.
table_name	Specifies the name of the source table.
object_type	Specifies the type of the source object. Valid values are: - C. Column. - T. Table.
column_name	Specifies the name of the source column. This information appears only if you selected the Columns check box.
comment	Specifies the reason why a source object of an unsupported type is excluded from processing even though it matches the selection rules.

14Under Advanced, set the advanced properties that are available for your source type and load type.

Property	Source and Load Type	Description
Disable Flashback	Oracle sources - Initial loads	Select this check box to disable Database Ingestion and Replication use of Oracle Flashback when fetching data from the database. The use of Oracle Flashback requires users to be granted the EXECUTE ON DBMS_FLASHBACK privilege, which is not necessary for initial loads. This check box is selected by default for new initial load tasks. For existing initial load tasks, this check box is cleared by default, which causes Oracle Flashback to remain enabled. For tasks that have partitioning enabled, this check box is automatically selected and unavailable for editing.
Include LOBs	Oracle sources: - All load types to Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, or SQL Server targets. Incremental loads and combined loads can use either the Log-based or Query-based CDC method. However, jobs that use theLog-based CDC method do not replicate data from LONG, LONG RAW, and XML columns to the generated target columns. Db2 for LUW sources: - All load types to Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, or Snowflake targets. Incremental loads and combined loads must use the Query-based CDC method. PostgreSQL sources: - Initial loads to Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, or SQL Server targets. - Incremental loads to Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka-enabled Azure Event Hubs, Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, or SQL Server targets. - Combined initial and incremental loads to Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen 2, Microsoft AzureSynapse Analytics, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, or SQL Server targets. SQL Server sources: - Initial loads to Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, or SQL Server targets. - Incremental loads to Kafka-enabled Azure Event Hubs, Databricks, PostgreSQL, Snowflake, and SQL Server targets. Disabled if you selected the Query-based CDC method. - Combined initial and incremental loads to Databricks, PostgreSQL, Snowflake, and SQL Server targets. Disabled if you selected the Query-based CDC method.	Select this check box if the source contains the large-object (LOB) columns from which you want to replicate data to a target. LOB data types: - For Db2 for LUW: BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARCHAR FOR BIT, LONG VARGRAPHIC, and XML - For Oracle: BLOB, CLOB, NCLOB, LONG, LONG RAW, and XML - For PostgreSQL: BYTEA, TEXT, and XML plus some other potentially large types such as JSON, JSONB - For SQL Server: GEOGRAPHY, GEOMETRY, IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), VARCHAR(MAX), and XML LOB data might be truncated, primarily depending on the maximum size that the target allows. Target-side truncation points: - BLOB, BYTEA, GEOGRAPHY, GEOMETRY, IMAGE, LONG RAW, LONG VARCHAR FOR BIT, or VARBINARY(MAX) columns are truncated before being written to BINARY columns on the target. - For Amazon S3, Databricks, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Oracle, Oracle Cloud Object Storage, Microsoft Fabric OneLake, PostgreSQL, and SQL Server targets, the data is truncated to 16777216 bytes. - For Amazon Redshift targets, the data is truncated to 1024000 bytes. - For Microsoft Azure Synapse Analytics targets, the data is truncated to 1000000 bytes. - For Google BigQuery and Snowflake targets, the data is truncated to 8388608 bytes. - CLOB, DBCLOB, NCLOB, LONG, LONG VARCHAR, LONG VARGRAPHIC, TEXT, NTEXT, NVARCHAR(MAX), RAW, VARCHAR(MAX), or XML columns are truncated before being written to VARCHAR columns on the target. - For Amazon S3, Databricks, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Oracle, Microsoft Fabric OneLake, Oracle Cloud Object Storage, PostgreSQL, and Snowflake targets, the data is truncated to 16777216 bytes. - For Amazon Redshift targets, the data is truncated to 65535 bytes. - For Google BigQuery targets, the data is truncated to 8388608 bytes. - For Microsoft Azure Synapse Analytics targets, the data is truncated to 500000 bytes. - For SQL Server targets, CLOB, TEXT and VARCHAR(MAX) data is truncated to 16777216 bytes, NCLOB, NTEXT and NVARCHAR(MAX) data to 33554432 bytes, and XML data to 33554442 bytes. - For Azure Event Hubs targets, the overall record maximum size is 1 MB. If the record size exceeds 1 MB, Azure Event Hubs generates an error, and the task fails. Source-side truncation considerations: - For Db2 for LUW and Oracle sources, you can specify the custom properties dbmiSourceBlobTruncationSize and dbmiSourceClobTruncationSize on the Source page to control the number of bytes at which truncation occurs for blob and clob types of data, respectively, when you want the data truncated at a point less than the maximum size that the target allows. - For PostgreSQL sources in incremental loads and combined loads, if large-object columns contain more than 1 MB of data, the data is truncated to 1 MB.
Enable Persistent Storage	All sources except Db2 for LUW (query-based CDC), MongoDB, Oracle (query-based CDC), PostgreSQL, SAP HANA, SAP HANA Cloud, and SQL Server (query-based CDC) - Incremental loads and combined initial and incremental loads. For Db2 for LUW, Oracle, and SQL Server sources that use the query-based CDC method, this field is not displayed because persistent storage is enabled by default and cannot be changed. For MongoDB, PostgreSQL, SAP HANA, and SAP HANA Cloud change data sources, the field is not displayed because persistent storage is enabled by default and cannot be changed.	Select this check box to enable persistent storage of transaction data in a disk buffer so that the data can be consumed continually, even when the writing of data to the target is slow or delayed. Benefits of using persistent storage are faster consumption of the source transaction logs, less reliance on log archives or backups, and the ability to still access the data persisted in disk storage after restarting a database ingestion job.
Enable Partitioning	Oracle sources - Initial loads and combined initial and incremental loads SQL Server sources - Initial loads and combined initial and incremental loads	Select this check box to enable partitioning of source objects. When an object is partitioned, the database ingestion and replication job processes the records read from each partition in parallel. For Oracle sources, database ingestion and replication determines the range of partitions by using the ROWID as the partition key. Also, when you select the Enable Partitioning check box, the Disable Flashback check box is automatically selected. For SQL Server sources, partitioning is based on the primary key. Note: In combined initial and incremental loads, the partitioning of source objects occurs only in the initial load phase.
Number of Partitions	Oracle sources - Initial loads and combined initial and incremental loads SQL Server sources - Initial loads and combined initial and incremental loads	If you enable partitioning of source objects, enter the number of partitions you want to create. The default number is 5. The minimum value is 2.
Initial Start Point for Incremental Load	All sources - Incremental loads	Set this field if you want to customize the position in the source logs from which the database ingestion and replication job starts reading change records the first time it runs. Options are: - Earliest Available. The earliest available position in the database log or structure where changes are stored. - For Db2 for i, the start of the current journal. - For Db2 for LUW, this option is not available. - For Db2 for z/OS, if Earliest Available is selected, Latest Available is used. - For MongoDB, this option is not available. - For MySQL, the earliest available record in the first binlog file. - For Oracle, this option is not available. - For PostgreSQL, the earliest available record in the replication slot. - For SAP HANA and SAP HANA Cloud, the earliest available record in the PKLOG table. - For SQL Server, the earliest available record in the active transaction log. This option is not available if the CDC Method is set to Query-based. - Latest Available. The latest available position in the database log or structure. - Position. A position in the change stream from which you want the database ingestion job to start retrieving change records. The position value is a Db2 for i sequence, Db2 for z/OS LRSN, Oracle SCN, PostgreSQL LSN, SAP HANA sequence value, or SQL Server LSN. This value must be equal to or less than the current position value. An invalid value will cause the job to fail. The value of 0 is displayed by default, which results in the following start positions: - For Db2 for i, do not use the default value of 0. - For Db2 for LUW, this option is not available. - For Db2 for z/OS, the value of 0 causes the latest available point to be used. - For MongoDB and MySQL, this option is not available. - For Oracle, the value of 0 causes the latest available point to be used. This option is not available if the CDC Method is set to Query-based. - For PostgreSQL, the value of 0 causes the earliest available point to be used. - For SAP HANA, the value of 0 causes the earliest available point to be used. - For SQL Server, the value of 0 causes the earliest available point to be used. A non-zero LSN that predates the beginning of the active transaction log causes data to be read from the CDC tables instead of from the transaction log. This option is not available if the CDC Method is set to Query-based. - Specific Date and Time. A date and time, in the format MM/DD/YYYY hh:mm AM|PM, that Database Ingestion and Replication uses to determine the position in the change stream from which to start retrieving change records. Database Ingestion and Replication retrieves only the changes that were started after this date and time. If you enter a date and time earlier than the earliest date and time in the available archived logs, the job will fail. For MySQL sources, this option is not available. The default is Latest Available.
Fetch Size	MongoDB - Initial loads and incremental loads	For a MongoDB source, the number of records that a database ingestion and replication job must read at a single time from the source. Valid values are 1 to 2147483647. The default is 5000.

15Under Custom Properties, you can specify custom properties that Informatica provides to meet your special requirements. To add a property, in the Create Property fields, enter the property name and value. Then click Add Property.
Specify these properties only at the direction of Informatica Global Customer Support. Usually, these properties address unique environments or special processing needs. You can specify multiple properties, if necessary. A property name can contain only alphanumeric characters and the following special characters: periods (.), hyphens (-), and underscores (_).
Tip: To delete a property, click the Delete icon at the right end of the property row in the list.
16Click Next.

Example of rules for selecting source tables

• •Rule 1 includes all source tables in the schema.
• •Rule 2 excludes the source tables that have names beginning with "NS1".
• •Rule 3 excludes the source tables that have names beginning with "REP_".
• •Rule 4 excludes the source tables that end with "MAP_1".

Configuring the target

1In the Connection list, select a connection for the target type. The connection type appears in parentheses after the connection name.
You must have previously defined the connection in Administrator for the runtime environment.
The list includes only the connection types that are valid for the load type selected on the Definition page. No connections are listed if you did not select a load type.
If you change the load type and the selected connection is no longer valid, a warning message is issued and the Connection field is cleared. You must select another connection that is valid for the updated load type.
Note: After you deploy the database ingestion and replication task, you cannot change the connection without first undeploying the associated job. You must then deploy the task again.
2In the Target section, configure the properties that pertain to your target type.
For descriptions of these properties, see the following topics:
• - Amazon Redshift target properties
• - Amazon S3 target properties
• - Databricks target properties
• - Flat File target properties
• - Google BigQuery target properties
• - Google Cloud Storage target properties
• - Kafka target properties
• - Microsoft Azure Data Lake Storage target properties
• - Microsoft Azure Synapse Analytics target properties
• - Microsoft Fabric OneLake target properties
• - Microsoft SQL Server target properties
• - Oracle target properties
• - Oracle Cloud Object Storage target properties
• - PostgreSQL target properties
• - Snowflake Data Cloud target properties
3If you want to rename the target objects that are associated with the selected source tables, define table renaming rules.
For example, you can add a prefix such as TGT_. For more information, see Renaming tables on the target.
4If you want to override the default mappings of source data types to target data types, define data type rules.
This feature is supported only for tasks that have an Oracle (any load type) source and an SQL-based target type. For more information, see Customizing data type mappings.
5Under Custom Properties, you can specify custom properties that Informatica provides to meet your special requirements. To add a property, in the Create Property fields, enter the property name and value. Then click Add Property.
Specify these properties only at the direction of Informatica Global Customer Support. Usually, these properties address unique environments or special processing needs. You can specify multiple properties, if necessary. A property name can contain only alphanumeric characters and the following special characters: periods (.), hyphens (-), and underscores (_).
Tip: To delete a property, click the Delete icon button at the right end of the property row in the list.
6Click Next if available, or click Save.

Renaming tables on the target

1. 1Under Table Renaming Rules, in the Create Rule fields, enter a source table name or a table name mask that includes one or more wildcards. Then enter the corresponding target table name or table name mask.
2. 2Click Add Rule.
The rule appears in the rules list.

• •For the source, enter only the asterisk (*) wildcard character to specify all of the selected source tables.
• •For the target, enter PROD_* to add this prefix to the names of all target tables that match the source tables by name.

Customizing data type mappings

• •Oracle sources with Databricks, Google BigQuery, Microsoft Azure Synapse Analytics, Oracle, SQL Server, and Snowflake targets
• •SQL Server sources with Snowflake targets

1. 1Expand Data Type Rules.
2. 2In the Create Rule fields, enter a source data type and the target data type that you want to map it to.
In the Source field only, you can include the percent (%) wildcard to represent the data type precision, scale, or size, for example, NUMBER(%,4), NUMBER(8,%), or NUMBER(%). Use the wildcard to cover all source columns that have the same data type but use different precision, scale, or size values, instead of specifying each one individually. For example, enter FLOAT(%) to cover FLOAT(16), FLOAT(32), and FLOAT(84). You cannot enter the % wildcard in the target data type. A source data type that uses the % wildcard must map to a target data type that uses specific precision, scale, or size value. For example, you could map the source data type FLOAT(%) to a target data type specification such as NUMBER(38,10).
3. 3Click Add Rule.
The rule appears in the list of rules.

• •In general, binary data types cannot be mapped to character data types.
• •If you define multiple data-type rules for the same source data type with the same length or same precision and scale values, you will not be able to save the database ingestion and replication task.
• •If you define multiple data-type rules for the same source data type but use the % wildcard to represent the length or precision and scale value in one rule and a specific length or precision and scale value in the second rule, the rule that contains the specific value is processed first, before the rule with the % wildcard. For example, if you map the source data types FLOAT(84) and FLOAT(%), the FLOAT(84) rule is processed first and then the FLOAT(%) rule is processed to cover any other FLOAT source columns with different sizes.
• •If a source data type requires a length or precision and scale value, make sure that you set the required attribute by using the % wildcard or a specific value, for example, VARCHAR(%) or VARCHAR(10).
• •If you define an invalid mapping, an error message is written to the log. You can then correct the mapping error, with assistance from your DBA if necessary.
• •For Oracle sources, you must use the data types that are returned by the following query for the source object:
select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME','TABLE_OWNER_NAME') from dual;
• •Database Ingestion and Replication does not support the BYTE and CHAR semantics in data-type mappings rules.
• •If a source data type has a default precision, you must specify it in your rule. For example, you must use TIMESTAMP(6) instead of TIMESTAMP.

Amazon Redshift target properties

Amazon S3 target properties

Databricks target properties

Flat File target properties

Google BigQuery target properties

Google Cloud Storage target properties

Kafka target properties

Microsoft Azure Data Lake Storage target properties

Microsoft Azure Synapse Analytics target properties

Microsoft Fabric OneLake target properties

Microsoft SQL Server target properties

Oracle target properties

Oracle Cloud Object Storage target properties

PostgreSQL target properties

Snowflake Data Cloud target properties

Configuring schedule and runtime options

1Under Advanced, optionally edit the Number of Rows in Output File value to specify the maximum number of rows that the database ingestion and replication task writes to an output data file.
Note: Advanced options are not displayed for jobs that have an Apache Kafka target.
For incremental load operations and combined initial and incremental load operations, change data is flushed to the target either when this number of rows is reached or when the flush latency period expires and the job is not in the middle of processing a transaction. The flush latency period is the time that the job waits for more change data before flushing data to the target. The latency period is internally set to 10 seconds and cannot be changed.
Valid values are 1 through 100000000. The default value for Amazon S3, Microsoft Azure Data Lake Storage Gen2, and Oracle Cloud Infrastructure (OCI) Object Storage targets is 1000 rows. For the other targets, the default value is 100000 rows.
Note: For Microsoft Azure Synapse Analytics targets, the data is first sent to a Microsoft Azure Data Lake Storage staging file before being written to the target tables. After data is written to the target, the entire contents of the table-specific directory that includes the staging files are emptied. For Snowflake targets, the data is first stored in an internal stage area before being written to the target tables.
2For initial load jobs only, optionally clear the File Extension Based on File Type check box if you want the output data files for Flat File, Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage, Microsoft Fabric OneLake, or Oracle Cloud Object Storage targets to have the .dat extension. This check box is selected by default, which causes the output files to have file-name extensions based on their file types.
Note: For incremental load jobs with these target types, this option is not available. Database Ingestion and Replication always uses output file-name extensions based on file type.
3For database ingestion and replication incremental load tasks that have Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Microsoft Fabric OneLake, or Oracle Cloud Object Storage targets, configure the following apply cycle options:

Option	Description
Apply Cycle Interval	Specifies the amount of time that must elapse before a database ingestion and replication job ends an apply cycle. You can specify days, hours, minutes, and seconds or specify values for a subset of these time fields leaving the other fields blank. The default value is 15 minutes.
Apply Cycle Change Limit	Specifies the total number of records in all tables of a database ingestion and replication job that must be processed before the job ends an apply cycle. When this record limit is reached, the database ingestion and replication job ends the apply cycle and writes the change data to the target. The default value is 10000 records. Note: During startup, jobs might reach this limit more frequently than the apply cycle interval if they need to catch up on processing a backlog of older data.
Low Activity Flush Interval	Specifies the amount of time, in hours, minutes, or both, that must elapse during a period of no change activity on the source before a database ingestion and replication job ends an apply cycle. When this time limit is reached, the database ingestion and replication job ends the apply cycle and writes the change data to the target. If you do not specify a value for this option, a database ingestion and replication job ends apply cycles only after either the Apply Cycle Change Limit or Apply Cycle Interval limit is reached. No default value is provided.

4Under Schema Drift Options, if the detection of schema drift is supported for your source and target combination, specify the schema drift option to use for each of the supported types of DDL operations.
Schema drift options are supported for the following source - target combinations and load types:

Source	Load Type	Target
Db2 for i	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, and SQL Server
Db2 for LUW	Incremental Combined initial and incremental	Snowflake
Db2 for z/OS, except Db2 11	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, Snowflake, and SQL Server
Microsoft SQL Server	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, and SQL Server
Oracle	Incremental Combined initial and incremental	Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, Snowflake, and SQL Server
PostgreSQL	Incremental Combined initial and incremental	Incremental loads: Amazon Redshift, Amazon S3, Databricks, Google BigQuery, Google Cloud Storage, Kafka (incremental loads only), Microsoft Azure Data Lake Storage, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, Oracle, Oracle Cloud Object Storage, PostgreSQL, and Snowflake Combined initial and incremental loads: Oracle, PostgreSQL, and Snowflake

The types of supported DDL operations are:
• - Add Column
• - Modify Column
• - Drop Column
• - Rename Column
Note: The Modify Column and Rename Column options are not supported and not displayed for database ingestion jobs that have Google BigQuery targets.
The following table describes the schema drift options that you can set for a DDL operation type:

Option	Description
Ignore	Do not replicate DDL changes that occur on the source database to the target. For Amazon Redshift, Kafka, Microsoft Azure Synapse Analytics, PostgreSQL, Snowflake and SQL Server targets, this option is the default option for the Drop Column and Rename Column operation types. For Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage, and Oracle Cloud Object Storage targets that use the CSV output format, the Ignore option is disabled. For the AVRO output format, this option is enabled.
Replicate	Replicate the DDL operation to the target. For Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage, Microsoft Fabric OneLake, and Oracle Cloud Object Storage targets, this option is the default option for all operation types. For other targets, this option is the default option for the Add Column and Modify Column operation types.
Stop Job	Stop the entire database ingestion job.
Stop Table	Stop processing the source table on which the DDL change occurred. When one or more of the tables are excluded from replication because of the Stop Table schema drift option, the job state changes to Running with Warning. Important: The database ingestion and replication job cannot retrieve the data changes that occurred on the source table after the job stopped processing it. Consequently, data loss might occur on the target. To avoid data loss, you will need to resynchronize the source and target objects that the job stopped processing. Use the Resume With Options > Resync option. For more information, see Overriding schema drift options when resuming a database ingestion and replication job.

5For incremental load jobs that have an Apache Kafka target, configure the following checkpointing options:

Option	Description
Checkpoint All Rows	Indicates whether a database ingestion and replication job performs checkpoint processing for every message that is sent to the Kafka target. Note: If this check box is selected, the Checkpoint Every Commit, Checkpoint Row Count, and Checkpoint Frequency (secs) options are ignored.
Checkpoint Every Commit	Indicates whether a database ingestion and replication job performs checkpoint processing for every commit that occurs on the source.
Checkpoint Row Count	Specifies the maximum number of messages that a database ingestion and replication job sends to the target before adding a checkpoint. If you set this option to 0, a database ingestion and replication job does not perform checkpoint processing based on the number of messages. If you set this option to 1, a database ingestion and replication jobs add a checkpoint for each message.
Checkpoint Frequency (secs)	Specifies the maximum number of seconds that must elapse before a database ingestion and replication job adds a checkpoint. If you set this option to 0, a database ingestion and replication job does not perform checkpoint processing based on elapsed time.

6Under Schedule, if you want to run job instances for an initial load task based on an existing schedule instead of manually starting the job after it is deployed from one of the monitoring interfaces, select Run this task based on a schedule and then select a predefined schedule. The default option is Do not run this task based on a schedule.
This field is unavailable for incremental load and combined initial and incremental load tasks.
You can view and edit the schedule options in Administrator. If you edit the schedule, the changes will apply to all jobs that use the schedule. If you edit the schedule after deploying the task, you do not need to redeploy the task.
If the schedule criteria for running the job is met but the previous job run is still active, Database Ingestion and Replication skips the new job run.
7Under Custom Properties, you can specify custom properties that Informatica provides to meet your special requirements. To add a property, in the Create Property fields, enter the property name and value. Then click Add Property.
Specify these properties only at the direction of Informatica Global Customer Support. Usually, these properties address unique environments or special processing needs. You can specify multiple properties, if necessary. A property name can contain only alphanumeric characters and the following special characters: periods (.), hyphens (-), and underscores (_).
Tip: To delete a property, click the Delete icon button at the right end of the property row in the list.
8Click Save.