Thursday, 17 August 2017

Query to Generate alter statement to disable and enable foreign key constraints in SQL Server


SELECT 
distinct
OBJECT_NAME(f.parent_object_id) Reference_TableName,
'Alter table '+OBJECT_NAME(f.parent_object_id)+' nocheck constraint '+ f.name ALter_Statement_Disable,
'Delete from '+OBJECT_NAME (f.referenced_object_id) Delete_Statment,
'Alter table '+OBJECT_NAME(f.parent_object_id)+' check constraint '+ f.name ALter_Statement_Enable
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'dashboard'

Sunday, 30 July 2017

Overriding Context Environments and Variables

1. Passing Context Environment :-

We can create different context group in the Talend Job and set the values of the context variables according to these groups. e.g. we can create a context group for Dev (Development environment) , Prod (Production environment) and provide different values to context variables based on the environment.

context_demo_run.bat --context=Prod

For using context group Dev defined in the Job:

H:\demo\context_demo> context_demo_run.bat --context=Dev

If we do not provide the context group then Job will automatically use the default context group defined in the Job.

For using default context group defined in the Job:

H:\demo\context_demo> context_demo_run.bat

2. Overriding Context Variables - We can also override the values of the individual context variables/parameters through command line. We can pass the argument --context_param followed by a name of the parameter defined in the Job and its value to override any of the parameters in the context. Use the following command to override the value of context parameter or variable.

H:\demo\context_demo> context_demo_run.bat --context_param <param-name>=<param-value>

For example: To override the value of context parameter DB_NAME to localhost

H:\demo\context_demo> context_demo_run.bat --context_param DB_NAME=localhost

Thursday, 18 May 2017

Export Model Repository Mappings using infacmd oie command

Export Command Without Control File: 

​./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true

Export Command With Control File: 
./infacmd.sh oie ExportObjects -dn infav10_domain -un Admin -pd "@@@@@@" -sdn Native -pn Integration -rs mrs_irpo -fp /tmp/Mappings.xml -ow true -cp /tmp/ec_mapping.xml

Control File Samples

Control File :    All_Mappings In the Project Without considering Sub Folders

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="false" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>

Control File :    All_Mappings In the Project With consider Sub Folders contents

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="true" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>


Control File :  Export Particular Mapping 

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder recursive="false" >
<objectList type="Mapping">
<object name="m_PassThrough_Mapping"/>
</objectList>
</folder>
</folders>
</exportParams>​

Control File : Export Particular Folder Mappings in a Project ​

​<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<exportParams
<folders>
<folder path="/SourceAdapter" recursive="false" >
<objectList type="Mapping" />
</folder>
</folders>
</exportParams>​

​ 

Wednesday, 17 May 2017

Operation not allowed for reason code “7” on table in DB2

This issue generally occurs when we altered/modified already existing table in IBM DB2.

We have to execute below Query when we get this error.

CALL sysproc.admin_cmd('REORG TABLE TABLE_NAME');

Below are the situations when we can ReOrg the table in DB2.

  • A high volume of insert, update, and delete activity on tables accessed by queries. If many rows are inserted, there may not be enough free space to keep them in the clustered sequence. If many rows are deleted, the table will still have the space allocated and a REORG can free the unnecessary space.
  • Significant changes in the performance of queries that use an index with a high cluster ratio. Some applications access groups of rows using the clustered index and may not perform well if the table becomes unclustered.
  • Executing RUNSTATS to refresh statistical information does not improve performance.
  •    In some cases, a RUNSTATS Utility can collect the current statistics and resolve performance problems. If a table has become unclustered or if it contains a large amount of free space, the REORG Utility may be needed to improve access efficiency.
  • The REORGCHK command indicates a need to reorganize your table.
  • If Row compression is being implemented for a table, the REORG Utility can be used to build the compression dictionary and compress the data rows.
Note:  alter table "APPS"."DEPARTMENTS" alter column DEPARTMENT_ID set not null; 

 


Wednesday, 10 May 2017

Creation of Informatica connections Using Infacmd

When a connection is created from command line, multiple options need to provide in -o option:


infacmd.sh createconnection
[ICMD_10023] Missing required command option: [<-DomainName|-dn>]
Usage:
<-DomainName|-dn> domain_name
<-UserName|-un> user_name
<-Password|-pd> password
[<-SecurityDomain|-sdn> security_domain]
[<-ResilienceTimeout|-re> timeout_period_in_seconds]
<-ConnectionName|-cn> connection_name
<-ConnectionId|-cid> connection_id
<-ConnectionType|-ct> connection_type
[<-ConnectionUserName|-cun> connection_user_name]
[<-ConnectionPassword|-cpd> connection_password]
<-Options|-o> options, separated by space in the form of name=value. Use single quote to escape any equal sign or space in the value.
Defines a connection and the connection options.




​./infacmd.sh createconnection -dn Domain_orcl -un Administrator -pd Lucid2016 -cn SQLServer -cid SQLServer -ct ODBC -cun ssasuser -cpd Lucid@123 -o "DataAccessConnectString=DW_SQLServer CodePage=UTF-8 EnableConnectionPool=true EnableQuotes=true QuoteChar=3 ODBCProvider='Microsoft SQL Server'"​

Monday, 8 May 2017

NLS_LANG Variable in Linux

Setting the NLS_LANG environment variable on the client to match that of the Oracle Database (found in v$nls_parameters system view) is NOT NECESSARILY the correct configuration.  NLS_LANG is used to tell the Oracle Client what character set the client's (or Informatica Server's) OS is using, so that appropriate code conversion can take place (or be skipped, when code pages match between Oracle client and server).  Please see Oracle Technical Note 158577.1 - "NLS_LANG Explained" for a more elaborate explanation as well as troubleshooting tips.  Please contact Oracle Support for configuration suggestions, as needed.

  • The NLS_LANG variable must to be in uppercase on UNIX.

  • If there is any mistake in the value setting of this variable (such as a spelling mistake), you may encounter the ORA-12705 error.
    For a list of valid languages, territories and character sets, the relevant National Language Support Guide for your version should be consulted.

  • Completely unsetting the NLS_LANG will use the default value AMERICAN_AMERICA.US7ASCII.
    This may fix the ORA-12705 error. However it may give unexpected results, as you are only dealing correctly with characters that can be represented completely with 7-bits. When you use a special characters (usually stored using the 8th bit), this will result in conversion problems.

  • On Windows, if there are several Oracle_HOMEs on the machine, ensure you have set the correct ORACLE_HOME
    Once you have determined the correct ORACLE_HOME , open the registry and verify the NLS_LANG entry in the special path.

  • On Windows, if you set NLS_LANG as an environment variable, this will take precedence over the value set in the Registry. 
    In most cases this is not preferable, so you may unset this variable.

  • On UNIX, depending on the shell, you will have to export the environment variable to make it visible to the sub-processes.

 

Oracle SQL Developer is the only "known-good" client side tool which can be used to view characters correctly and works independently of client-side NLS_LANG settings. 

Exporting a table that contains a CLOB column

​Problem Statement:​ 
What is the recommended way for exporting a table that contains a CLOB column to a SQL script? 
The standard feature of the SQL Developer only exports all columns that are not CLOBs. 


Solution:

Let us assume table named "Data" contains a clob type column. In order to get the insert statements for the content of the table ,
trigger the below query

SQL> select /*insert*/* from Data;


Note: Above sql should be executed in script mode .



Once the query executed , you can see the insert queries in the Script Output Area.

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) || ']', ' '))