We can create a Multi Dimensional clustering table in DB2. Mostly the tables are used in the data warehousing.  If a table contain the large amount of data then we can convert the table in to the MDC Table. If we convert it then it will respond quickly.



In the above example the clustering created based on the three dimensions, c1,c3,c4.

We can create the clustering based on the combination of number of columns.



   ORGANIZE BY DIMENSIONS (c1, (c3, c4))

In the above example, two clustering was created one is on dimension c1, and another one is on c3 and c4. Combination of c3 and c4 creates a single clustering.

We are frequently using alter table queries in our daily operations. The the list of operations running behind the single alter query.

  1. Alter Table Procedure called “SYSPROC.ALTOBJ” .  This procedure called from DB2 Design Advisor.
  2. Drop all dependent objects of the table.
  3. Rename the table
  4. Create the table using the new definition
  5. Recreate the all dependent objects of the table.
  6. Transform the existing table data  into the new table.   If any of the transformations are required while moving, the transformation logics are implemented in the data moving process.  The transformations are required for the data type changes.

For easier table design and faster query performance we have some types of tables in DB2.  Choosing the right table design we can optimize the query performance.  

  1. Regular Tables.
  2. Append Mode tables
  3. Multidimensional Clustering tables.
  4. Range cluster Tables.
  5. Partitioned tables.

Regular Tables.

            This kind of table is normally used in DB2.  These kind of tables we can use indexes.

Append Mode Tables.

            This kind of tables are used when the tables will involve in more number of transactions per day.  The table support faster  insert and update operations.

Multidimensional Clustering tables.

            This kind of tables support number of cluster indexes.  We can use number of dimensions for cluster indexing.   Mostly this kind of design will be used in the Data warehousing. The larger amount of data handling will be easier when we uses this table design.

Partitioned tables.

            This tables design will be used when the number of indexes are tightly coupled with the table.

Here I share the query which used to get the list of tables from DB2.

select * from syscat.tables where type = ‘T’

Here you can change the type of the table. The type would be ‘T’, ‘S’,’V’. 

The above query will give the entire details about the table.

Which transformation should we use to normalize the COBOL and relational sources?

Normalizer Transformation. Normalizer Transformation.

Normalizer Transformation.When we drag the COBOL source in to the mapping Designer workspace,the normalizer transformation automatically appears,creating input and output ports for every column in the source.

Difference between static cache and dynamic cache?

In case of Dynamic catche when you are inserting a new row it looks at the lookup catche to see if the row existing or not,If not it inserts in the target and catche as well in case of Static catche when you are inserting a new row it checks the catche and writes to the target but not catche

If you cache the lookup table, you can choose to use a dynamic or static cache. By default, the lookup cache remains static and does not change during the session. With a dynamic cache, the Informatica Server inserts or updates rows in the cache during the session. When you cache the target table as the lookup, you can look up values in the target and insert them if they do not exist, or update them if they do.

The information you are posting should be related to java and ORACLE technology. Not political.

What are the differences between Connected and Unconnected Lookup?

Connected Lookup Unconnected Lookup
Connected lookup participates in dataflow and receives input directly from the pipeline Unconnected lookup receives input values from the result of a LKP: expression in another transformation
Connected lookup can use both dynamic and static cache Unconnected Lookup cache can NOT be dynamic
Connected lookup can return more than one column value ( output port ) Unconnected Lookup can return only one column value i.e. output port
Connected lookup caches all lookup columns Unconnected lookup caches only the lookup output ports in the lookup conditions and the return port
Supports user-defined default values (i.e. value to return when lookup conditions are not satisfied) Does not support user defined default values

What is the difference between Router and Filter?

Router Filter
Router transformation divides the incoming records into multiple groups based on some condition. Such groups can be mutually inclusive (Different groups may contain same record) Filter transformation restricts or blocks the incoming record set based on one given condition.
Router transformation itself does not block any record. If a certain record does not match any of the routing conditions, the record is routed to default group Filter transformation does not have a default group. If one record does not match filter condition, the record is blocked
Router acts like CASE.. WHEN statement in SQL (Or Switch().. Case statement in C) Filter acts like WHERE condition is SQL.

What can we do to improve the performance of Informatica Aggregator Transformation?

Aggregator performance improves dramatically if records are sorted before passing to the aggregator and “sorted input” option under aggregator properties is checked. The record set should be sorted on those columns that are used in Group By operation.

It is often a good idea to sort the record set in database level (why?) e.g. inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator

What are the different lookup cache?

Lookups can be cached or uncached (No cache). Cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built and it remains same during the session run. On the other hand, A dynamic cache is refreshed during the session run by inserting or updating the records in cache based on the incoming source data.

A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after session run is complete or not respectively

How can we update a record in target table without using Update strategy?

A target table can be updated without using ‘Update Strategy’. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and check the “Update” check-box.

Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”. Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

Deleting duplicate row using Informatica

Q1. Suppose we have Duplicate records in Source System and we want to load only the unique records in the Target System eliminating the duplicate rows. What will be the approach?
Let us assume that the source system is a Relational Database . The source table is having duplicate rows. Now to eliminate duplicate records, we can check the Distinct option of the Source Qualifier of the source table and load the target accordingly.

We can set the default value for the newly added column in the table.   After the add column name, use default as keyword following value is used to set default.

This is the syntax.

alter table TbTestTable Planning add ModDate datetime default getdate


The Resource Database is referred as RDB.   The RDB is a hidden and read only database which contains all the system databases.   The Resource Database in introduced in the SQL Server 2005.

The entire system object like sys.Objects is physically present in the Resource database.  All the system objects are physically stored in the resource database, but logically avail with the sys schema of every database on the instance.

The Resource Database doesn’t contain any user data or user meta data.

The Resource database is a hidden on so this is not listed in the SQL server management studio’s object explorer.

The resource file is stored in the name of  “mssqlsystemresource.mdf”  and “mssqlsystemresource.ldf”. The files are read only and the files cannot be moved.

The files will be stored in the following location.

“C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn”  In the location “SQLExpress” is the instance name.

The resource database id will be 32767. All the resource database id will be the same. The DBA shouldn’t move or rename the resource database.

During the time of installation the Resource and master database are stored in the same directory.   But in sql server 2008, you don’t need to keep resource and the master database are in the same location.

If you needs to backup the Resource database, You can use Xcopy for the database backup, You can’t do like Other database backups.

If you wish to apply any service pack or hot fixes, this will be much more easier to the DBA.  In SQL Server 2005 onwards the updating not made on the user databases. Roll back of the applied service pack also easier, by copying the resource file.

SELECT SERVERPROPERTY('ResourceVersion') Version,
SERVERPROPERTY('ResourceLastUpdateDateTime') UpdateDateTime

The above command is used to know about the resource file version and last updated date and time.

This statement will use inside the stored procedures.  The statement will execute for each and every statement in the stored procedure.



Set No count off

Set No count off

If the SET NOCOUNT is in “OFF” state then,

This will update the @@Rowcount values for all the updates done in sp and return the DONE_IN_PROC message to the client. This will make more network traffic.  Basically the DONE_IN_PROC message is only for the information. Inside the stored procedure this message popup to the client is mostly waste. So can we prevent this message by using the SET NOCOUNT ON.

      When SET NOCOUNT is in “OFF” For each updates the DONE_IN_PROC message will popup. This will make more network traffic. 

Set Nocount off

Set Nocount off

If the SET NOCOUNT is in ON stage

The message will not popup, this will reduce the most of the network traffic, so stored procedure’s performance will increase automatically.

Even the @@Rowcount is updated.   The @@Rowcount has the last “No of rows affected” count.

Set Nocount on

Set Nocount on

In the above stored procedure the two select statements are executed, for the both statements the @@RowCount will be get updated. But the message is not get shown at the client window. 

This “nn rows affected” message will display at the end of Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE

Sql server supports to transfer data from one table to another table. For this both table doesn’t have the same structure. We can transfer data from one table to another table even both the tables doesn’t have the same structure. This Method of Data transferring is the best way over using cursor. In General cursor needs to insert data from one table to another one by one. This will affect the performance more. Instead of cursor we can use this method for Transferring data.
For this method only we can transfer data to table which already exist.

Syntax :

Insert into  Tablename1 ( Column1, Column2, Column3 …….)  
Select Column1, Column2, Column3…………. From TableName2

Example :

Create Table TestTable (id bigint identity (1,1), Name varchar(100), Qualification nvarchar (100), Designation nvarchar(100))
Insert into TestTable (Name, Qualification, Designation ) 
Select Name, Qualification, Position from OldTablename 

Transferring set of partial rows :
If we don’t need to transfer the whole records, we needs to partial set of records then we can specify the record group in where condition.
Insert into TestTable (Name, Qualification, Designation )
Select Name, Qualification, Position from OldTablename
Where Position = ‘SoftwareEngineer’
For transferring record we can use this method than using cursor. This method of transferring will be faster.


Rajesh.Dharmakkan Photos

More Photos



April 2014
« Jul    

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 4 other followers


Get every new post delivered to your Inbox.