In the continuing journey to track down impacted objects and to determine when the code in a database was last changed or added, here is another quick SQL, which can be used in Aginity Workbench for Netezza to retrieve a list of when Store Procedures were last updated or were created.
SQL List of When A Stored Procedure was Last Changed or Created
select t.database — Database
, t.OWNER — Object Owner
, t.PROCEDURE — Procedure Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime
from _V_OBJECT o
, _v_procedure t
o.objid = t.objid
and t.DATABASE = ‘<<Database Name>>‘
order by o.objmodified Desc, o.objcreated Desc;
occasionally, there is a need to quickly drop a list of tables and you don’t always want to write or generate each command individually in Aginity. So, here is a quick example of how you can use a ‘Select’ SQL statement to generate a list of drop commands for you. Now, this approach assumes there is a common naming convention, so, you may need to adapt it to your needs.
An outline of the Drop Multiple Tables Process
Here is a quick summary of the steps to generate the drop statements from _V_Table:
Build required Netezza SQL select; paying particular attention to the where clause criteria to exclude any unnecessary tables.
Execute the SQL statement
Copy from Aginity Results Tab without headers
Past into new Aginity Query window
validate that only the tables are in the list — No extras
Click with the SQL Drop command list and Execute as a single batch
Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).
Foreign Key (FK) Guidelines
A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
Avoid using distribution keys as foreign keys
Foreign Key field should not be nullable
Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
Apply standard naming conventions to constraint name:
Please note that foreign key constraints are not enforced in Netezza
Steps to add a Foreign Key
The process for adding foreign keys involves just a few steps:
Verify guidelines above
Alter table add constraint SQL command
Run statistics, which is optional, but strongly recommended
Basic Foreign Key SQL Command Structure
Here is the basic syntax for adding Foreign key:
ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>
ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>
FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;
Example Foreign Key SQL Command
This is a simple one field example of the foreign key (FK)
ALTER TABLE Blog.job_stage_fact
ADD CONSTRAINT job_stage_fact_host_dim_fk1
FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints
A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.
Facts About Foreign Keys
Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
Foreign keys are not enforced by all RDBMS
The concept of referential integrity is derived from foreign key theory
Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys
The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.
Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records. No Action does not mean to suppress the foreign-key constraint.
The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.
The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record
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.