Data Model Scan fails with SQL Error: 8152, SQLState: 22001. String or binary data would be truncated

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We're migrating from an on-premise Oracle database to AWS PostgreSQL. We're having trouble with Data Model scans against the PostgreSQL database tables.

This is for our Guidewire application, which has 4 databases:

1. BC (Billing Center)
2. PC (Policy Center)
3. CM (Contact Manager)
4. CC (Claim Center)

Each database has around 2200 tables (roughly, 8800 tables in all). When running the data model scan, the scan fails with the following error: SQL Error: 8152, SQLState: 22001. String or binary data would be truncated.

We've also tried registering the tables as objects, and see the same error after registering the first block of about 200 tables. This seems to be a database issue. However, we need help identifying the table(s) causing the failure.

Environment

Release : 4.10

Cause

TDMModelingService.log shows a SQL Error: 8152, SQLState: 22011, which indicates a record value exceeds the length of a record field, thus the string or data value is truncated.

Adding the following two statements to the logback-tdm.xml provided the additional debugging to identify the root cause:

• <logger name="org.hibernate.SQL" level="DEBUG" />
• <logger name="org.hibernate.type" level="TRACE" />

Note: The CA Test Data Manager Portal service will need to be stopped and restarted for the change to take effect. It's also advised to clear the portal logs by deleting the contents of the %ProgramData%\CA\CA Test Data Manager Portal\logs directory, before restarting the service.

Resolution

In this instance, the issue was caused by unique index, which used an expression that is longer than 254 characters.

TDM expects to use the column name (which the index should force to be unique) but instead, this table is using an expression that is longer than 254 characters. To get around this error, either ignore the table, with the unique index expression, during scanning or increase the maximum length of the column "column_name" of the "gtrep_datadisc_ent_key_def" table in the gtrep repository database.

NOTE: The TDM Engineering hasn't tested increasing the length of "column_name", although they don't expect any issues.