IBM Support

Data Truncation With Access Client Solutions Data Transfer

Troubleshooting


Problem

The Access Client Solutions data transfer upload function does not truncate data in the manner that IBM i Access for Windows does. Two new options allow either or both character and numeric truncation.

Symptom

By default character data that is too large for a IBM i field fails with error message "MSGDB0036 -Server returned SQL error (Data truncation)" or "Value too large (ColumnName)".

Resolving The Problem

The Access Client Solutions data transfer to IBM i handles character truncation differently than previous clients did.  In IBM i Access Client Solutions version 1.1.5.0 and earlier, the client reports an error due to the truncation and stops. Depending upon whether the upload is into a data or source file the error varies. When you upload to a data file with an FDFX file, the error is "MSGDB0036 - Server returned SQL error (Data truncation)". When you upload to a source file, the error is "Value too large (<ColumnName>)."

Starting with version 1.1.6.0, the IBM i Access Client Solutions data transfer was enhanced to allow for the truncation of character or overflow of numeric data, but these features are not enabled by default.

To enable the character truncation feature, you modify the AcsConfig.properties file by adding the following:

# com.ibm.iaccess.dataxfer.jdbc.AllowCharacterTruncation
# false (default) = Character strings larger than the column length will cause an exception and a message 'Data in Row x, Column y is too long for the IBM i field (fieldname)'
# true = This setting allows data transfer to truncate character data
com.ibm.iaccess.dataxfer.jdbc.AllowCharacterTruncation=true

Attempting to upload a number that is larger or longer in the PC file than the size of the numeric field on the IBM i fails with the following message:

Data in Row x, Column x is too long for the IBM i field (FieldName). MSGDB0036 - Server returned SQL error (Data type mismatch).

To enable the numeric truncation and overflow feature, modify the AcsConfig.properties file by adding the following:

# com.ibm.iaccess.dataxfer.jdbc.AllowNumericOverflow
# false (default) = numeric overflow/underflow results in an exception on upload and warning with the largest/smallest value for the field used on download
# true = numeric overflow/underflow results in the largest/smallest value for the field used with no exceptions or warnings on upload or download
com.ibm.iaccess.dataxfer.jdbc.AllowNumericOverflow=true

You can also suppress truncation warning messages in the transfer request by clicking the "Properties" button in the request, selecting the "Display" tab, and  clearing the checkbox on "Display warnings during data transfer".

The default location of the AcsConfig.properties file is in the same location as the acsbundle.jar file.  Click the About item in the IBM i Access Client Solutions Help menu to display the location of the acsbundle.jar file.

Related Information

Access Client Solutions home page

[{"Type":"MASTER","Line of Business":{"code":"LOB66","label":"Technology Lifecycle Services"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SSRQKY","label":"IBM i Access Client Solutions"},"ARM Category":[{"code":"a8m0z0000000CHZAA2","label":"Data Access"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
17 November 2024

UID

nas8N1021406

Page Feedback