Friday, November 22, 2013

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.

Tuesday, July 3, 2012

Generate dummy data using SQL on DB2 LUW


Not sure where I got this query, but is very handy for generating sample data, try this.

-- columns SSN,FIRST_NAME,LAST_NAME,JOB_CODE,DEPT,SALARY,DOB  
WITH TEMP1 (s1,r1,r2,r3,r4) AS   (
VALUES
(0   ,RAND(2)   ,RAND()+(RAND()/1E5)   ,RAND()* RAND()   ,RAND()* RAND()* RAND())    
UNION
ALL   SELECT
s1 + 1   ,
RAND()   ,
RAND()+(RAND()/1E5)   ,
RAND()* RAND()   ,
RAND()* RAND()* RAND()  
FROM
TEMP1  
WHERE
s1 < 10 --rows   )   SELECT
SUBSTR(DIGITS(INT(r2*988+10)),
8) ||'-'||           SUBSTR(DIGITS(INT(r1*88+10)),
9) || '-' ||           TRANSLATE(SUBSTR(DIGITS(s1),
7),
'9873450126',
'0123456789'),
CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),
'aaeeiibmty',
'0123456789'),
CASE            
   WHEN INT(r4*9) > 7 THEN 'MGR'            
   WHEN INT(r4*9) > 5 THEN 'SUPR'            
   WHEN INT(r4*9) > 3 THEN 'PGMR'            
   WHEN INT(R4*9) > 1 THEN 'SEC'            
   ELSE 'WKR'          
END,
INT(r3*98+1),
DECIMAL(r4*99999,
7,
2),
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS  
FROM
TEMP1

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.