Issues with Database Renaming in Netezza

I was trying to use rename database as part of solution, but it has limitations, specifically if you have stored procedures or views built that reference a database is that you are intending to rename..here is a test that demonstarates the issue.

nzsql -c "create database SAMDB_01"
nzsql -c "create table test as select * from TEST_DB..test_dimension limit 5" -d SAMDB_01
nzsql -c "create or replace view v_test as select * from test" -d SAMDB_02
nzsql -c '\dtv' -d SAMDB_01
nzsql -c "alter database SAMDB_01 rename to SAMDB_02"
nzsql -c '\dtv' -d SAMDB_02
nzsql -c "select * from v_test limit 5" -d SAMDB_02
 ** ERROR:  Database name 'SAMDB_01' has changed (name); rebuild view 'V_TEST'
nzsql -c '\d v_test' -d SAMDB_02|grep -P  "definition:"
View definition: SELECT SAMDB_01.ADMIN.TEST.LOCATION_ID, SAMDB_01.ADMIN.TEST.COMPANY_ID, SAMDB_01.ADMIN.TEST.CURRENT_REGION_ID, SAMDB_01.ADMIN.TEST.HISTORICAL_REGION_ID, SAMDB_01.ADMIN.TEST.CURRENT_DISTRICT_ID, SAMDB_01.ADMIN.TEST.HISTORICAL_DISTRICT_ID, SAMDB_01.ADMIN.TEST."LOCATION", SAMDB_01.ADMIN.TEST.LOCATION_TYPE, SAMDB_01.ADMIN.TEST.IA_PROCESS, SAMDB_01.ADMIN.TEST.IA_AGGREGATE, SAMDB_01.ADMIN.TEST.SHORT_NAME, SAMDB_01.ADMIN.TEST.LONG_NAME, SAMDB_01.ADMIN.TEST.RELOCATED_NUMBER, SAMDB_01.ADMIN.TEST.CURRENT_ROW, SAMDB_01.ADMIN.TEST.BEGIN_DATE_ID, SAMDB_01.ADMIN.TEST.END_DATE_ID, SAMDB_01.ADMIN.TEST.STATE_POSTAL_CODE, SAMDB_01.ADMIN.TEST.LOCATION_TYPE_ID, SAMDB_01.ADMIN.TEST.LOCATION_SUBTYPE_ID, SAMDB_01.ADMIN.TEST.OPEN_DATE_ID, SAMDB_01.ADMIN.TEST.CLOSE_DATE_ID, SAMDB_01.ADMIN.TEST.LOAD_TIMESTAMP FROM SAMDB_01.ADMIN.TEST;

nzsql -c "create or replace view v_test as select * from test" -d SAMDB_02
nzsql -c "select * from v_test limit 5" -d SAMDB_02
nzsql -c "drop database SAMDB_02" -d system

Basically I am creating a new database named "SAMDB_01", I creaate a view "v_test" which references a table within this "SAMDB_01" database.

Then I rename "SAMDB_01" database to "SAMDB_02". Now my view "v_test" is not working, I receive an error (highlighted below). If you take look at the view definition as of now in the renamed database the code show that the old database name is embedded in the definition and thats the problem.

Unfortunately there is no easy command like "rebuild view" or "recompile view" like in Oracle. Rebuild basically means..you have to recreate/replace it using "CREATE OR REPLACE VIEW" statement. So you better save the ddl for views, stored procedures and functions etc. If you have not saved your ddl then you might have to write a script that will retrieve all the ddl from the renamed database and replaces the old database name with new name in our case we need to replace "SAMDB_01" with "SAMDB_02". In my case I had access to Aginity Workbench for Netezza so I just used it to script it and replace the old database name with new one.

I have seen similar issue when we rename tables and columns.