[HY000] ERROR: Base table/view has changed (datatype); rebuild view

Error Message

ERROR [HY000] ERROR: Base table/view ‘BLOG_LIST_TBL’ attr ‘BLOG_ID’ has changed (datatype); rebuild view ‘BLOG_LIST_VW’

What does the Error Mean?

This error means that an underlying table used by the view has changed in a way, which made the view invalid, as a result, the view must be rebuilt to reflect the new table definition.

How to Rebuild the view

In ‘Aginity for Netezza’ this process is easy, if you have a user with sufficient permissions.  The basic process is:

  • Navigate to and select the view to be rebuilt
  • Right Click, Select ‘Script’ > ‘DDL to query window’
Create DDL to Query Window
Create DDL to Query Window
  • Once the ‘Create or Replace View’ SQL has generated, click within the SQL statement (without highlighting)
  • Press Ctrl+F5 or navigate to Execute > ‘Execute as a Single batch
Execute SQL as a Single Batch
Execute SQL as a Single Batch
  • Verify the ‘Create or Replace View’ SQL executes successfully
  • Then, run a simple select against the view and that Select runs without producing the HYOOO Base Table/View Error

Netezza – [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit

While helping a customer we encountered the [SQLCODE=HY000][Native=46] ERROR, which was a new one for me. So here are a few notes to help the next unlucky soul may run into the error.

Netezza Error Reason:

  • [SQLCODE=HY008][Native=51] Operation canceled; [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit

What Does the Error Mean

  • In a nutshell, it means invalid data was submitted and could not be inserted.

What To Do

  • Basically, you need to go to the Netezza logs to see why the rows were rejected and resolve input data error, then resubmit your transactions. The logs are temporary and reused, so, you need to get to them before they are overwritten.

Where Are The Data Logs

  • In linux the logs can be found in /tmp:

For nzload Methods Logs

  • /tmp/database name.table name.nzlog
  • /tmp/database name.table name.nzbad

For External Table Load Logs

  • /tmp/external table name.log
  • /tmp/external table name.bad

Related References

Netezza – [HY000] ERROR: Please issue a groom on table

While altering a Netezza table this error was produced:

  • ERROR [HY000] ERROR: Please issue a groom on table ‘<<TableName>>’, maximum table versions exceeded.

The error was resolved by running these commands on succession:

  1. GROOM TABLE ‘<<TableName>> VERSIONS;
  2. GROOM TABLE ‘<<TableName>> PAGES ALL;

Related References

Groom Table

PureData System for Analytics, PureData System for Analytics 7.2.0, IBM Netezza Database User’s Guide, Netezza SQL command reference, groom table