Gotcha - Teradata Views

Encountered another interesting "gotcha" again involving Teradata v13.1 and how it handles metadata for views. We encountered this issue within Cognos Framework Manager v10.1.1 when attempting to use a view created in Teradata as a query subject.

The exact Cognos error that we received was:

RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-9'.
UDQ-SQL-0107 A general exception has occurred during the operation "fetch".
[Teradata][ODBC Teradata Driver][Teradata Database] Internal error: Please do not resubmit the last request. SubCode, CrashCode:

After running a UDA trace and a Teradata ODBC driver trace and reviewing the log files we discovered a statement that was causing the error message:

HELP COLUMN "DB_NAME"."VIEW_NAME"."PK_ID_FIELD_NAME"

Running this query manually on the database gave a more detailed, but still obscure error message:

HELP Failed. 3610: Internal error: Please do not resubmit the last request. SubCode, CrashCode:

The view itself that we were debugging was extremely complex, but after some experimentation I was able to produce the following simple view definition that still caused the error.

CREATE VIEW DB_NAME.VIEW_NAME AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

Simple right? Gotcha #2 is that this error only appeared on 2 of our 3 environments, Development and UAT showed this issue, but our SystemTest environment worked without a problem.

We were able to devise a temporary workaround, because the HELP query specifically identified a problem with the PK_ID_FIELD_NAME on the CHILD_TABLE we were able to replace it by using the FK_ID_FIELD_NAME on the PARENT_TABLE which fixed the error message. However this was not a solution to the problem, because logically retrieving the primary key of a joined table in a view should NOT cause a problem.

The Solution

The exact reason for why this problem was happening on 2 out of 3 of our systems is still unknown, we suspect there is corrupt or missing column metadata that was causing the inconsistency. Nevertheless we did find a solution to the problem.

The problem was resolved by explicitly naming the view's columns in the view definition. For whatever reason, this bypassed the metadata error and allowed the view to be used in both Cognos and Teradata SQL Assistant. Below is the fixed view definition with the changes highlighted in green:

CREATE VIEW DB_NAME.VIEW_NAME (FIELD1, PK_ID_FIELD_NAME) AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

This allowed the HELP COLUMN metadata to be generated correctly for the view and fixed this issue without having to restructure the view query itself.