Amazon Redshift Connectors

Back Next

Troubleshooting for Amazon Redshift V2 Connector

Using the JVM option for upsert operation

For an upsert operation, you can set the JVM option

-DuseDeleteForUpsert=true

and connect all the fields of the target table. Use the following rules when you set the JVM option:

When you set the JVM option, the Secure Agent deletes the records of the target table based on the primary key match between the source table and the target table. All the records of the source table are inserted into the target table. When you do not set the JVM option, the Secure Agent updates existing rows and inserts other rows as if marked for insert.

For mappings in advanced mode, configure the JVM option in the

Spark Session Properties

when you create a mapping task.

When you use a target table that has all the columns forming a composite key and you do not set an update column in the target, the mapping might fail with a warning. When you set the JVM option, the mapping task runs successfully.

When you use a target table that has duplicate values in an update column, the target table has lesser number rows after you run the mapping. When you do not set the JVM option, the target table does not have lesser number of rows.

When you use a source table that has duplicate values in a column and select the column as an update column on the target, an additional row is added to the target. When you do not set the JVM option, an additional row is not added to the target.

For an upsert operation, when you use a table with column names that contain special characters and do not set the JVM option, the mapping fails.

Amazon S3 bucket does not exist or the user does not have permission to access the bucket

Do not modify the time on the machine that hosts the Secure Agent. The time on the Secure Agent must be correct as per the time zone. Otherwise, the mapping fails with an exception.

NOT NULL columns with default values

Even when you do not map the NOT NULL columns that have default values in an Amazon Redshift target table, the insert, update, or upsert operation is successful and the default values for NOT NULL columns are used.

If you set the JVM option

-DRetainUnmappedNotNullColumnValidation

value to

true

in the Secure Agent properties, the operation is not successful and the default values for NOT NULL columns are not used.

How to implement the upsert operation using Amazon Redshift V2 Connector?

For information about implementing the upsert operation, see Implementing the upsert operation using Amazon Redshift V2 Connector.

How to configure AWS IAM authentication for Amazon Redshift V2 Connector?

For information about configuring AWS IAM authentication, see Configuring AWS IAM Authentication for Amazon Redshift and Amazon Redshift V2 Connectors.

How to connect to Amazon Redshift Serverless offered by Amazon Web Services (AWS) using the Amazon Redshift V2 connector?

For information about connecting to Amazon Redshift Serverless, see Using Amazon Redshift Serverless with Cloud Data Integration.

Invalid timestamp error occurs when a string data type is mapped to a time data type in a mapping

When you run a mapping enabled for

SQL ELT optimization

to write data from a string column that contains date, timestamp and time information and you want to process it with default date/time format to write to Redshift, we can make use of JVM property

-DHonorInfaDateFormat=true

for the Secure Agent.

To configure the JVM option in Administrator
, perform the following steps:

Select Administrator
> Runtime Environments
.

On the

Runtime Environments

page, select the Secure Agent machine that runs the mapping.

Click Edit
.

In the System Configuration Details
section, select Data Integration Server
as the Service and DTM as the Type
.

Edit the JVM Option system property and set the value to

-DHonorInfaDateFormat=true

Click Ok
to save the changes.

We also need to set

DateTime Format String

session property as per the input string format under advanced session properties in a mapping task.

Connection timeout error while downloading data from Amazon S3 staging to the agent machine

Network performance and reliability issues when connecting to Amazon S3 can cause slow response times, connection failures, or request errors.

These issues might be due to suboptimal configuration of client-side connection management parameters such as maximum concurrent connections, connection timeout, socket timeout, acquisition timeout, and retry settings.

To resolve these issues, configure the following properties for the Secure Agent:

-DAWSS3MaxConnectionPool. The maximum number of simultaneous HTTP connections that the Amazon S3 client can maintain. Default value is 500.

-DAWSS3ConnectionTimeOut. The timeout duration in milliseconds to establish a connection to Amazon S3. Default value is 1200000 milliseconds.

-DAWSS3SocketTimeOut. The timeout duration in milliseconds to read data from an established connection. Default value is 600000 milliseconds.

-DAWSS3AcquisitionTimeOut. The timeout duration in milliseconds to acquire a connection from the connection pool. Default value is 180000 milliseconds.

-DAWSMaxRetries. The maximum number of retry attempts for a connection.

Perform the following tasks to configure the JVM options in the Secure Agent:

Click

Administrator

Runtime Environments

On the Runtime Environments
page, select the Secure Agent.

Click Edit
.

In the System Configuration Details
section, select

Service

Data Integration Server

and

Type

DTM

Edit and configure the following JVM options:

JVM option	Value
JVMOption1	-DAWSS3MaxConnectionPool=<number of connections>
JVMOption2	-DAWSS3ConnectionTimeOut=<duration in milliseconds>
JVMOption3	-DAWSS3SocketTimeOut=<duration in milliseconds>
JVMOption4	-DAWSS3AcquisitionTimeOut=<duration in milliseconds>
JVMOption5	-DAWSMaxRetries=<number of retries>