Aginity For Netezza – How to Generate DDL

Aginity, Aginity for Netezza, Netezza, PureData, DDL, SQL
Aginity

How to Generate Netezza Object DDL

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

The basic process is:

  • In the object browser, navigate to the Database
  • select the Object (e.g. table, view, stored procedure)
  • Right Click, Select ‘Script’ > ‘DDL to query window’
  • The Object DDL will appear in the query window
Create DDL to Query Window
Create DDL to Query Window

Related References

 

Netezza / PureData – How to rebuild a Netezza view in Aginity

How To Generate View or table DDL in Aginity For Netezza PureData
How To Generate View or table DDL in Aginity For Netezza

 

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view.  Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view.  The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

  • Navigate to the Database and view you wish to rebuild
  • Select the view and right click
  • Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

  • This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

  • This I usually do by choosing the ‘Execute as a single batch’ option.  Make sure the code executes successfully.

Verify the view

  • To verify the simply execute a select statement and make it executes without errors and/or warning.

Related References

 

[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