TDM Publish fails with error: SQL state [S0001]; error code [4506]; Column names in each view or function must be unique

Products

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

Issue/Introduction

While running a TDM generator to publish data to a DB2 database, the publish job fails with the following error:

SQL state [S0001]; error code [4506]; Column names in each view or function must be unique. Column name 'PV_0' in view or function 'PV_809_8388329' is specified more than once.

The trace stack for the error shows the problem occurred while TDM was trying to create the view in the database, and every variable ID shows to be PV_0.

Environment

TDM Portal 4.11.89.0

Cause

When the view variables are created using a '0' value, that typically means something has exceeded an upper limit, and cannot be properly converted.
Looking at the gtrep_table_col_def table, confirmed that problem is seen because the Column IDs (tcd_id) values that are generated, when increments are made by the 'SEQ_TCD' sequence, has exceeded the upper limit of the INTEGER data type (2147483647).

Resolution

This has been resolved in TDM Portal 4.11.1005.0, which now uses the BIGINT data type (9223372036854775807) to increases the length of the value allowed. 

Additional Information

The latest TDM Patches are available for download from the Test Data Manager (TDM) Support Patches page.

SQL queries used for troubleshooting:

• To find the maximum value of the Column ID generated by the 'SEQ_TCD' sequence:
  SELECT MAX(tcd_id) FROM gtrep_table_col_def;
  
  
• To check the increment value of the sequence:
  SELECT * FROM sys.sequences WHERE name = 'SEQ_TCD';
  
  
• To reset the increment of the sequence, so it increments at a smaller value: 
  ALTER SEQUENCE seq_tcd INCREMENT BY 1