Installing Informatica 8.1 on my Laptop

Hi Guys,

I have downloaded informatica 8.1 from Oracle site. This software is freely available for non commercial use and you can download and use it. Its been some time since i worked on informatica was gettin rusty. So its good for revision

The post is not a detailed one. Only the issues that i faced during install.

The download that we get from the oracle edelivery site
is a 8.1 and a service pack for 8.5 upgrade so ther
are two different things in that download


1) 1st error got not enough space i have lot of space
Need to run as admin as windows xp mode as cant install on
windows 7. the 8.1 version its not able to recognize space

%USERPROFILE%\AppData\Local\Temp

I tried changing the temp location but that is not the problem
i have lot of space in temp of my c drive

The issue is you need to run 8.1 with compatibility mode for
windows server 2003 .right Click on install.exe go to properties then compatibility tab and select windows 2003 it should do it

then you are asked to create domain .. domain is the primary logical unit for management and administration within powercentre
service manager runs on it . service manager supports domain and application services

in the order of services informatica 8.6.1 service is the first service

you need to create a user in oracle called informatica to store powercentre repository meaning it will save informatica jobs in oracle
database

create user informatica identified by informatica

grant all privileges to informatica


After that informatica is not able to accept these username it gives

oracle jdbc .Error establishing connection to host and port 1521

check if the listen is up

in cmd type lsnrctl then type status

I try to manually start listener from services but its starting then again stopping in 2 seconds

I had changed my pc name got from earlier command

kapil 123 is the password for domain

For informatica services also password is kapil`123
---------------------------------------------------------------------------------------------------------

Use the error below and catalina.out and node.log in the server/tomcat/logs directory on the current machine to get more information. Select Retry to continue the installation.

STDOUT: Installing the service '"Informatica Services 8.1.1"' on node 'node01_Kapil-PC'...
Using CURRENT_DIR:      F:\Informatica_install\server\tomcat\bin
Using INFA_HOME:        F:\Informatica_install
The service '"Informatica Services 8.1.1"' has been installed.


STDERR: The filename, directory name, or volume label syntax is incorrect.
System error 1069 has occurred.

The service did not start due to a logon failure.



EXITCODE: 2
---------------------------------------------------------------------------------------

May be because my apache service was up it gave this error.so drop the informatica user from oracle as it has some repository tables
and again try the installation after disabling apache

System error 1069 has occurred

Ok finally solved .You dont need to provide any username and password as we are running as admin i dont have any user set up for my machine
if you have user set up please enter the user or continue



---------------------------- PowerCenter Domain Creation : Success PowerCenter Ping Domain : Failed Repository Service Creation : Skipped Repository Service Startup : Skipped PowerCenter Repository Creation : Skipped  The installation debug log file can be found at : F:/Informatica_install/Informatica_Installation_Server_Debug.log

-------------------------------------------------------------------------------------------

Now my admin console is not starting .So i deleted informatica manually from the folder as uninstaller was not working.then i removed
from start up by manually deleting.then need to go to window registerty just go to run and then regedit and look for informatica and delete so that you are good as new

my informatica service was not started go to control panel and services and try to start informatica service if it does not start check your path variable in environement variables it should have bin folder path where informatica is installed

after doing this my admin console is workeing

to log into admin username is admin and password is what you gave during intall kapil123


Try to create repository service from admin console .login to admin console right click on node and create repository service

before that create a licence file by right clicking on node


after you create repository service you need to create integration service

I had chagned my computer name from admin pc but in my tnsnames files it was still showing admin pc that was one issue

after that when trying to set up repository from repository manager getting error.

unable to handle request because repository content do not exists

There are 500 over tables and views present in Informatica 8.5.x Repository. All table name starts with “OPB_” and the view names start with “REP_”.

so i did not have it in my oracle user informatica which means repository did not get created though i have repository servic

then connect to repository from repository manager by giving your oracle username and password for repository
no table and all 

Join Mechanism in Oracle

Hi All,

Consider tables as files . Fact table is Big file worth 50GB and Dimension is small file worth 1GB. Now what are the mechanism that can be used to find the data that is matching Big and small file. Whenever CPU has to read data the file has to be in RAM. For now do not consider oracle specifics like PGA, SGA, Buffer cache. Just simple RAM concept

1)  Read small file worth 1 GB first in RAM and then search 50 GB file for matching row. Now 1GB file has 10 rows and only full scan is allowed so you end up reading full 50 GB table to find one row (consider 10 rows you want are at end) . So you cannot read full 50 GB in ram in one go so you read 1GB at time if row not found you clear ram and take next 1gb. So for 10 rows you did 500gb read that is BAD idea.
2) You read 50 GB table 1gb at a time compare with 1gb smaller table . 50gb fact has 1000 rows so you end up doing 1TB i/o that is BAD. This is your Nested loop. This works if you have a index on the 1gb table which allows you to pin point to correct row without doing a full table scan.and even there is a index on 50GB table and you are not selecting the entire 50GB
3) So now look at some of the efficient algorithm for finding rows
4) Sort Merge joins --- you sort both the tables. Its like a nested loop. You take on sorted output probe the second sorted ouput till you find a row that does not match. So record 1 from source1 can only find one match in source 2 no need to look further as the table is sorted and you wont find any more matches


HASH JOINS

To illustrate a hash table, assume that the database hashes hr.departments in a join of departments and employees. The join key column is department_id. The first 5 rows of departments are as follows:

SQL> select * from departments where rownum < 6;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500

The database applies the hash function to each department_id in the table, generating a hash value for each. For this illustration, the hash table has 5 slots (it could have more or less). Because n is 5, the possible hash values range from 1 to 5. The hash functions might generate the following values for the department IDs:

f(10) = 4
f(20) = 1
f(30) = 4
f(40) = 2
f(50) = 5

Note that the hash function happens to generate the same hash value of 4 for departments 10 and 30. This is known as a hash collision. In this case, the database puts the records for departments 10 and 30 in the same slot, using a linked list. Conceptually, the hash table looks as follows:

1    20,Marketing,201,1800
2    40,Human Resources,203,2400
3
4    10,Administration,200,1700 -> 30,Purchasing,114,1700
5    50,Shipping,121,1500

Hash Join: Basic Steps

A hash join of two row sources uses the following basic steps:

The database performs a full scan of the smaller data set, and then applies a hash function to the join key in each row to build a hash table in the PGA.

The database probes the second data set, using whichever access mechanism has the lowest cost.

Typically, the database performs a full scan of both the smaller and larger data set. The algorithm in pseudocode might look as follows:

For each row retrieved from the larger data set, the database does the following:

Applies the same hash function to the join column or columns to calculate the number of the relevant slot in the hash table.

For example, to probe the hash table for department ID 30, the database applies the hash function to 30, which generates the hash value 4.

Probes the hash table to determine whether rows exists in the slot.

If no rows exist, then the database processes the next row in the larger data set. If rows exist, then the database proceeds to the next step.

Checks the join column or columns for a match. If a match occurs, then the database either reports the rows or passes them to the next step in the plan, and then processes the next row in the larger data set.

If multiple rows exist in the hash table slot, the database walks through the linked list of rows, checking each one. For example, if department 30 hashes to slot 4, then the database checks each row until it finds 30.

Sql tuning Methodology

Hi All,

I have been working in Datawarehousing since past 7 years and Sql tuning is a task which is always there . It can be for ETL or It can be for reporting. Below is a methodology that i have developed over years. Its not yet fully developed but just writing it down will add more points as time progresses

Summary 1 - Best Way is to generate the retrieve actual execution plan by using dbms_xplan.display_cursor('sqlid') and then to go through each step slowly understanding the access method of table (full scan/indexes ) and Join methods. Table which comes handy in oracle is dba_ind_columsn to check column names corresponding to indexes 

Methodolody for Solving sql issues

1.     Run the sql with Autotrace on will give some general info. It will give the consistent gets and Physical reads. When we say we have to reduce logical reads we means we have to read overall less data you can do this by using index, partition any method

2.     Check in v$sql with below query that will give a lot of high level statistics about a query . ( Disk reads, CPU reads, Buffers) . Similar to TKPROF

3.     Check in v$sql_plan to check what the actual plan used for the query

4.     Check in V$active_session_history to get idea of what are the waits on and when the waits came

Results of Autotrace are Explain plan and Statistics. Important things to consider in Explain plan

1) The access mechanism used for fetching data. In datawarehouse mostly you will see full table scans
2) The order in which data is joined. Its better to always write the sql in order of the joins that will be performed in explain plan. Though now we use CBO and the order of writing join does not impact explain plan. The order is your understanding what the explain plan should be. In what order you want to join the table, factors impecting the order are the size of the table and whether its fact or dimension
3) The mechanism used for join. Example a nested loop , Fast full scan 

Below is Link to my article where i have shared some of my understanding of different joins which is very essential for figuring out whats going wrong in a explain plan. Basics always are important

Running Autotrace

Set  autotrace on

Then run the query as script

Checking in v$sql  (  Concentrate on highlighted columns)

select * from v$sql where sql_id = 'd6fr04z12yksa'

Select Sql_Id,

  (Elapsed_Time/1000000) Elapsed_Seconds,

  (Cpu_Time/1000000) Cpu_Seconds,

  (user_io_wait_time/1000000) user_io_wait_time_seconds,

  Fetches,

  executions,

  Buffer_gets,

  Disk_Reads,

  (Physical_Read_Bytes/1024)/1024 Physical_Read_Mb,

  rows_processed,

  SORTS,

  Direct_Writes,

  (Physical_write_bytes/1024)/1024 Physical_writes_MB,

  Concurrency_Wait_Time,

   optimizer_mode,

 ((Io_Interconnect_Bytes)/1024)/1024 Interconnect_Mb

From V$sql Where

sql_id = 'd6fr04z12yksa'

Actual plan for the sql from v$sql_plan. Below is for formated output

Select Plan_Table_Output From

TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d6fr04z12yksa'));

Checking Active session history to get Step by step analysis

Select Sample_Time,Sql_Id, Event,Session_State,Time_Waited,Wait_Time,Blocking_Session,Wait_Class,Current_Obj#,

Current_File#,Current_Block#,Current_Row#,

P1,P2,((Pga_Allocated)/1024/1024) Pga_Mb,(((Temp_Space_Allocated)/1024)/1024) Temp_Space_Mb From  V$active_Session_History

Where  sql_id = 'd6fr04z12yksa'