We're trying out a job that uses a Change Capture stage to synchronize data to a table in our Netezza database. Everything works as expected until we try to write the results back to the table. We are using the Netezza Connector with write mode as "Action column." The action column is defined in the job as char(1), not null. It is derived/modified from the tinyint change_code that is generated by the CC stage. The job aborts while creating the temporary Netezza table with an error saying, "Failed to execute SQL statement: CREATE TABLE NZCC_TT_... Record size limit exceeded." Examining the DDL it issued, it is including the action column in the temp table as "CAST ( NULL AS CHAR(64000) ) AS change_code"
Is there a way to control the size it uses for this column in the temp table? If it was not using 64000 bytes, I suspect the job would work fine.
Netezza Action column issue
Moderators: chulett, rschirm, roy
The strangest thing... we modified the original job and tried a few different options (including using a Switch Stage to redirect the deletes and insert/updates to different Netezza connectors), and now the configuration that was causing the original issue is working. I'm not sure which change fixed it, but it seems fine now.
We also spent a week at the IBM Insight conference, so that may have helped too :D
We also spent a week at the IBM Insight conference, so that may have helped too :D
It seems I spoke too soon with my last comment. We had gotten the job to work on a test table with fewer columns than the original one. However, when we tried a test table that had all of the columns, the same issue occurred.
We were able to work with our support reps to determine the issue was being caused by a modify stage preceding the Netezza connector. This was being used to convert the tinyint change_code output from the Change Capture stage to the char(1) action value expected by the Netezza connector. Even though the output for the modify stage had the column defined as char(1), it was outputting an unbounded string. This caused the Netezza connector to use the maximum allowable string length, 64000.
We changed the modify spec from to
This caused the output schema of the modify stage to use a 1 character change_code, instead of 64000, and resolved the issue.
We were able to work with our support reps to determine the issue was being caused by a modify stage preceding the Netezza connector. This was being used to convert the tinyint change_code output from the Change Capture stage to the char(1) action value expected by the Netezza connector. Even though the output for the modify stage had the column defined as char(1), it was outputting an unbounded string. This caused the Netezza connector to use the maximum allowable string length, 64000.
We changed the modify spec from
Code: Select all
change_code:string = lookup_string_from_int16...Code: Select all
change_code:string[1] = lookup_string_from_int16...This caused the output schema of the modify stage to use a 1 character change_code, instead of 64000, and resolved the issue.
