Sunday, 23 April 2017

Automated Script to Enable/Disable Foreign Key Constrain in Oracle Database

select 'ALTER TABLE HR.' || cons.TABLE_NAME || ' enable constraint ' || cons.constraint_name || ';' as, cons.owner as child_owner, cons.table_name as child_table, cons.constraint_name constaint_name, cons.constraint_type constraint_type, col.owner parent_owner, col.table_name parent_table, col.column_name column_name from dba_cons_columns col, dba_constraints cons where cons.r_owner = col.owner and cons.r_constraint_name = col.constraint_name and col.owner = 'HR

Thursday, 20 April 2017

Foreign Key Drop Statement SQL Server (Automated Script Generation of Foreign Key Constraint)

SELECT 'alter table '+OBJECT_NAME(f.parent_object_id) +' drop constraint '+ f.name AS Fk_Drop_Statement, 
   OBJECT_NAME(f.parent_object_id) AS TableName, 
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.OBJECT_ID = fc.constraint_object_id
ind of thinking we used when we created them - Albert Einstein"

Foreign Key Drop Statement SQL Server (Automated Script Generation of Foreign Key Constraint)

SELECT 'alter table '+OBJECT_NAME(f.parent_object_id) +' drop constraint '+ f.name AS Fk_Drop_Statement, 
   OBJECT_NAME(f.parent_object_id) AS TableName, 
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.OBJECT_ID = fc.constraint_object_id
ind of thinking we used when we created them - Albert Einstein"

Wednesday, 19 April 2017

​ Versioned table in Netezza

 One QC process need to obtain tables and their row counts in a database in Netezza. We use the below SQL query to do so:

"SELECT CAST(TRIM(RELNAME) AS VARCHAR(50)) TABLE_NAME,CAST(CASE WHEN RELTUPLES < 0 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES ) ELSE ((2^32) * RELREFS) + ( RELTUPLES ) END AS BIGINT) NUM_ROWS
FROM 
_T_CLASS ,
_T_OBJECT 
WHERE 
_T_OBJECT.OBJID =_T_CLASS.OID 
AND _T_OBJECT.OBJCLASS = 4905; "
  
Now oneday an issue occurs, we add a column to one table, but this query output will not include the altered table.

Solution

This issue pushes us to look into the query and the system tables. After making research, the more internally principle of Netezza is gradually discovered.
In Netezza database system, it will use ids(a number) to represent objects(tables, views, stored procedures...) internally. For tables, it will use id 4905 to represent.
   SELECT * FROM _T_OBJECT WHERE OBJCLASS = 4905; 
When a table structure is modified, for example, add/modify/delete a column, the Netezza system will internally use a different  id for the altered table, which is 4961, and the table is called versioned table now.
We could use system view to check whether there is versioned tables in the database:
  SELECT * FROM _V_SYS_TABLE_VERSION_OBJECT_DEFN;   
And Here is some explanation of versioned table:
Versioned tables come about as a result of doing an alter table. This results in multiple data stores for the table. When you go to query the table, Netezza must recombine the separate data stores back into a single entity. This action will be performed automatically and on-the-fly. But it does result in additional performance cost for using the UNION ALL view instead of having all of the data exist in a single table.Therefore, it is a best practice to reconstitute the table by using: 
 GROOM TABLE <tablename> VERSIONS;
And after the groom sql is executed, the id is set back to 4905 for the table.
  
So as a result of versioned table, it's better to modify the where statement in QC script to:
 WHERE _T_OBJECT.OBJID =_T_CLASS.OID AND _T_OBJECT.OBJCLASS IN (4905,4961)    
In this way, the query will give expected result even if there is altered table. But again it is suggested to apply the groom clause reasonably soon after table is altered.
(EnD)

Related articles

Monday, 17 April 2017

ASCII characters and Regexp_replace to replace all non printable characters







































REGEXP_REPLACE (COLUMN,'[^' || CHR (32) || '-' || CHR (127) || ']', ' '))