Wednesday, September 7, 2011

How will you rename a table ?

I have created new table say testTable

create table testTable
(
    id number
);

Renamed that to RenameTestTable

rename testTable to RenamedTestTable

When u try to select data from original table, it gives error as table does not exists.

select *
from TestTable

When you select data from renamed table, command gets succeeded.

select *
from RenamedTestTable

Renaming works even if table has the data

drop table RenamedTestTable

create table testTable
(
    id number
);


insert into testTable values(1);

rename testTable to RenamedTestTable


select *
from RenamedTestTable

Now the question is, what happen if table has

1. Index defined on it
2. Table has synonym defined in another schema
3. Table is getting used in Function or Procedure
4. Table is defined in view query.

We will test these scenarios one by one.

1. If table has an index defined on it.....

drop table RenamedTestTable;
create table testTable
(
    id number
);
create unique index IdxTestTable
on testTable(id) ;
select *
from user_ind_columns
where index_name = upper('IdxTestTable');
insert into testTable values(1);
rename testTable to RenamedTestTable;
select *
from user_ind_columns
where index_name = upper('IdxTestTable');
select *
from RenamedTestTable;

So, if index is defined on a table and you are renaming that particular table, nothing will happen to index. At metedata view of index, table name entry  will get change simply.


2. If table has a synonym defined ...

I logged in as sys user/schema and created synonym as below

select *
from hr.testTable

create synonym testTable for hr.testTable

select *
from testTable

select *
from all_synonyms
where synonym_name = upper('testTable')

When I re-named a table at hr schema, and fired select statement at sys; got an error as "ORA-00980: synonym translation is no longer valid".

As synonym is created in sys schema, there is no changed in synonym entry.


3. If table is getting used in a procedure/function ....

I created table and procedure as below

Table Creation :
drop table RenamedTestTable

create table testTable
(
    id number
);

create unique index IdxTestTable
on testTable(id) ;

Procedure Creation :
create procedure TableTest
as

    vCount number;

begin

    select count(*) into vCount
    from testTable;
   
    dbms_output.put_line('Record Count Is ==> ' || vCount);

end;

Note: if ur using toad, just select whole code and press F5, procedure will get compile
Also, note here, procedure and table names are same. It allows because both are different db objects. I might not able to create table or proedure with same name

Running a procedure:
begin

TableTest;

end;

make your dbms_output on at table. If you are using sql *, then type "setserveroutput on".

 You will get 0 as a result.

select *
from all_procedures
where  owner = 'HR'

If you check that proc at all_procedures, amazingly proc name is null and object name it displays as proc name. Not sure how.

select *
from all_objects
where owner = 'HR'
and object_name like 'T%'
If you check that in all_objects, it shows correctly - object type as table different and object type as procedure different. and status of table and procedure is valid.

Now I will rename the table.
rename testTable to RenamedTestTable

fired the below query

select *
from all_objects
where owner = 'HR'
and object_name like 'T%'

Tables vanished from result set and procedures status became InValid.

Just try to run the proc...
begin

TableTest;

end;

Note: to run the proc at toad, just select the above code and do Ctr+Enter

Got an error as below
ORA-06550: line 3, column 1:
PLS-00905: object HR.TABLETEST is invalid
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

Here, object tabletest is reffered as procedure and not a table, because tabletest table object is no longer exists.


4. If table is getting used in a view ...

drop table RenamedTestTable

create table testTable
(
    id number
);


create unique index IdxTestTable
on testTable(id)

create view testTableView
as
select * from testTable

select *
from all_views
where view_name = upper('testTableView')


select *
from all_objects
where object_name = upper('testTableView')

View as an object is with valid status.

Now, I will rename the table

rename testTable to RenamedTestTable

Now, view became invalid
select *
from all_objects
where object_name = upper('testTableView')

I will try to select from view now
select *
from testTableView;
It gave error as [ORA-04063: view "HR.TESTTABLEVIEW" has errors]
at 1 comment:

Sunday, September 4, 2011

Partition Table

Range Partitioning

Partition table can not be created on Express Edition, please install 10g.

Fortunately I had got it from staksale, installed new 10g database as XE10g, I already had XE for express edition.
------------------------------------------------------------------------
C:\Documents and Settings\Administrator>tnsping XE10g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-SEP-2
011 08:06:38

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mandar)(
PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE10g)))
OK (70 msec)

C:\Documents and Settings\Administrator>
------------------------------------------------------------------------

First partition table

CREATE TABLE Partition_employees
(employee_id  VARCHAR2(30),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
email    VARCHAR2(30),
phone_number VARCHAR2(30),
hire_date date,
job_id VARCHAR2(30),
salary VARCHAR2(30),
commission_pct VARCHAR2(30),
manager_id VARCHAR2(30),
department_id VARCHAR2(30))
PARTITION BY RANGE(hire_date)
(
PARTITION hire_1987 VALUES LESS THAN(TO_DATE('01/01/1987','DD/MM/YYYY')),
PARTITION hire_1988 VALUES LESS THAN(TO_DATE('01/01/1988','DD/MM/YYYY')),
PARTITION hire_1989 VALUES LESS THAN(TO_DATE('01/01/1989','DD/MM/YYYY')),
PARTITION hire_1990 VALUES LESS THAN(TO_DATE('01/01/1990','DD/MM/YYYY')),
PARTITION hire_1991 VALUES LESS THAN(TO_DATE('01/01/1991','DD/MM/YYYY')),
PARTITION hire_1992 VALUES LESS THAN(TO_DATE('01/01/1992','DD/MM/YYYY')),
PARTITION hire_1993 VALUES LESS THAN(TO_DATE('01/01/1993','DD/MM/YYYY')),
PARTITION hire_1994 VALUES LESS THAN(TO_DATE('01/01/1994','DD/MM/YYYY')),
PARTITION hire_1995 VALUES LESS THAN(TO_DATE('01/01/1995','DD/MM/YYYY')),
PARTITION hire_1996 VALUES LESS THAN(TO_DATE('01/01/1996','DD/MM/YYYY')),
PARTITION hire_1997 VALUES LESS THAN(TO_DATE('01/01/1997','DD/MM/YYYY')),
PARTITION hire_1998 VALUES LESS THAN(TO_DATE('01/01/1998','DD/MM/YYYY')),
PARTITION hire_1999 VALUES LESS THAN(TO_DATE('01/01/1999','DD/MM/YYYY')),
PARTITION hire_2000 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY'))
);

I was trying to fire below statement

insert /*+ append */   into  Partition_employees
select *
from employees

Since, employees table having values for year 2000 and I have not defined partition for year 2000 (partition is exists for year < 2000 but not for 2000), my insert statement was failing giving error as

ORA-14400: inserted partition key does not map to any partition

Re-created table with one more partition - below is the script

CREATE TABLE Partition_employees3
(employee_id  VARCHAR2(30),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
email    VARCHAR2(30),
phone_number VARCHAR2(30),
hire_date date,
job_id VARCHAR2(30),
salary VARCHAR2(30),
commission_pct VARCHAR2(30),
manager_id VARCHAR2(30),
department_id VARCHAR2(30))
PARTITION BY RANGE(hire_date)
(
PARTITION hire_1987 VALUES LESS THAN(TO_DATE('01/01/1987','DD/MM/YYYY')),
PARTITION hire_1988 VALUES LESS THAN(TO_DATE('01/01/1988','DD/MM/YYYY')),
PARTITION hire_1989 VALUES LESS THAN(TO_DATE('01/01/1989','DD/MM/YYYY')),
PARTITION hire_1990 VALUES LESS THAN(TO_DATE('01/01/1990','DD/MM/YYYY')),
PARTITION hire_1991 VALUES LESS THAN(TO_DATE('01/01/1991','DD/MM/YYYY')),
PARTITION hire_1992 VALUES LESS THAN(TO_DATE('01/01/1992','DD/MM/YYYY')),
PARTITION hire_1993 VALUES LESS THAN(TO_DATE('01/01/1993','DD/MM/YYYY')),
PARTITION hire_1994 VALUES LESS THAN(TO_DATE('01/01/1994','DD/MM/YYYY')),
PARTITION hire_1995 VALUES LESS THAN(TO_DATE('01/01/1995','DD/MM/YYYY')),
PARTITION hire_1996 VALUES LESS THAN(TO_DATE('01/01/1996','DD/MM/YYYY')),
PARTITION hire_1997 VALUES LESS THAN(TO_DATE('01/01/1997','DD/MM/YYYY')),
PARTITION hire_1998 VALUES LESS THAN(TO_DATE('01/01/1998','DD/MM/YYYY')),
PARTITION hire_1999 VALUES LESS THAN(TO_DATE('01/01/1999','DD/MM/YYYY')),
PARTITION hire_2000 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION hire_2001 VALUES LESS THAN(TO_DATE('01/01/2001','DD/MM/YYYY'))--newly addd
--PARTITION hire_max VALUES LESS THAN(maxvalue )
);


You can add required partition or add maxvalue partition, as mentioned above.

insert /*+ append */   into  Partition_employees
select *
from employees

Now the 107 rows got inserted successfully. I have not commited the transaction and fired below select statement

got an error. Not sure why. It says can not read object after modifying it in parallel.

I committed the transaction and now select went fine.








Checking Explain Plan:

select *
from Partition_employees
where hire_date = '31-Jan-2001'

Here, it says that it has accessed data from partition#15.



What if we replace = with between clause ?

Check below query

select *
from Partition_employees
where hire_date between '1-Jan-1999' and '31-Jan-2001'

Here, it has access partition #14 and #15. Which is quite obvious and logical.









Selecting data from Particular Partition

select *
from Partition_employees partition(hire_1988)

If you check its explain plan then it has accessed 2nd partition.

What if I fire a query based on date range instead of partition; Ultimately it will access same 2nd partition but only with based on data range.

select *
from Partition_employees
where hire_date between '1-Jan-1987' and '31-Dec-1987'

Both queries result and explain plan will be same.

1. Question arises is, which one to use in case you want all the values of that range or partition.
2. Second question is, what will be the performance if I use hash / value partition instead of range partition.

Need to check...

==================================================

Local Index Creation -

I fired below statement to create a local index on our partitioned table. Logically local index will point to each partition which are available on base table.

Here, I am trying to create an index only on first 3 partitions instead of all. When I fired a statement, oracle cried with giving below error--

CREATE INDEX LocalPartitionIndex
on Partition_employees (hire_date)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989);

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table



When I specified all the partitions at index level, it got succeeded

CREATE INDEX LocalPartitionIndex
on Partition_employees (hire_date)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989,
PARTITION hire_1990,
PARTITION hire_1991,
PARTITION hire_1992,
PARTITION hire_1993,
PARTITION hire_1994,
PARTITION hire_1995,
PARTITION hire_1996,
PARTITION hire_1997,
PARTITION hire_1998,
PARTITION hire_1999,
PARTITION hire_2000,
partition hire_max);


Even this will also works


CREATE INDEX LocalPartitionIndex
on Partition_employees(hire_date) local

Below, Local index got created even on non-partitioned column
CREATE INDEX LocalPartitionIndex11
on Partition_employees_2 (employee_id)
LOCAL
(PARTITION hire_1987,
PARTITION hire_1988,
PARTITION hire_1989,
PARTITION hire_1990,
PARTITION hire_1991,
PARTITION hire_1992,
PARTITION hire_1993,
PARTITION hire_1994,
PARTITION hire_1995,
PARTITION hire_1996,
PARTITION hire_1997,
PARTITION hire_1998,
PARTITION hire_1999,
PARTITION hire_2000,
partition hire_max);

CREATE INDEX LocalPartitionIndex4
on Partition_employees3 (employee_id) local


Global Index Creation


CREATE INDEX GlobalPartitionIndex ON Partition_employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(500),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));

CREATE INDEX GlobalPartitionIndex1_2 ON Partition_employees3(hire_date)
GLOBAL PARTITION BY RANGE(hire_date)
(PARTITION p1 VALUES LESS THAN('1-Jan-2000'),
 PARTITION p2 VALUES LESS THAN(MAXVALUE));

You can define global index on partitioned or non-partitioned column both.

DATA DICTIONARY VIEW

SELECT *
FROM ALL_PART_TABLES -- PARTITION TABLE NAMES
where table_name = upper('Partition_employees')
This will give all information, like
1. Type of partition
2. SubPartition
3. Number of partition
4. partition status
5. table name

---------------------------------------
SELECT *
FROM LOADER_PART_INFO

SELECT *
FROM ALL_PARTIAL_DROP_TABS

---------------------------------------
SELECT *
FROM ALL_PART_INDEXES
where table_name = upper('Partition_employees')
This will give us information like
1. Types of indexes on partition table
2. Type of partition
3. sub partitions
4. index partition count (Here its 15 and 2, please see above scripts. We have
            defined 2 partitions on global and for local it will be same as table partitions)
5. locality of partition
6. alignment
---------------------------------------
SELECT *
FROM ALL_PART_KEY_COLUMNS


It will show all partition objects (index and table in this case) and column on which its partition is defined.


---------------------------------------
SELECT *
FROM ALL_TAB_PARTITIONS

SELECT *
FROM ALL_IND_PARTITIONS

These both tables shows table and index partition information respectively as
1. index or table name
2. partition name
3. sub partition count
4. high_value (i.e. criteria)
5. status
6. tablespace name
7. logging

There are some doubts like can we define loggig or tablespace for a particular partition separately ?
at 4 comments:

Sorted Input @ Aggregator

What is the use of sorted input ?

When we do not specify sorted input at Aggregator, it generates the data and index cash based on the input values. It sorts data which is present at cash internally and use those sorted values for aggregation.

When we specify the sorted input, it assumes that data which is coming is already sorted and it do not require to sort it again. When data is not in sorted order, mapping gets fail.


1. First we selected group by port as ID and selected sorted input option.

The input data is like below

ID    NAME    GES_UPDATE_DATE
1    mandar    1/8/2009 4:25:14 AM
2    sudhir    1/8/2009 4:25:26 AM
3    ashish    1/8/2009 4:25:34 AM
4    null    1/8/2009 4:33:40 AM
5    null    1/8/2009 4:33:52 AM
5    null    1/8/2009 4:34:02 AM
1    null    1/8/2009 4:37:12 AM
6    null    1/8/2009 4:38:10 AM



Here, ges_update_date is in sorted order but not the id column values. So when we select id as a group by port and sorted input option, mapping gets fail giving below error

Severity    Timestamp    Node    Thread    Message Code    Message
ERROR    1/7/2009 4:50:05 AM    node01_mandar    TRANSF_1_2_1    TE_7073    Aggregate Error: Expecting keys to be ascending

Now, if you change the group by port to GES_UPDATE_DATE and use option sorted input, mapping will get complete successfully.


at No comments:

Number of sorted input @ Source Qualifier Transformation

You get an option "Number of Sorted Inputs"  at source qualifier tranformation.

Here the value is 3 [default its 0]. At source qualifier there are only 3 columns and now if you run the session, below is the query got generated
Severity    Timestamp    Node    Thread    Message Code    Message
INFO    1/7/2009 4:26:08 AM    node01_mandar    READER_1_1_1    RR_4010    SQ instance [SQ_RESOURCE_DIM] SQL Query [SELECT RESOURCE_DIM.ID, RESOURCE_DIM.NAME, RESOURCE_DIM.GES_UPDATE_DATE FROM RESOURCE_DIM ORDER BY RESOURCE_DIM.ID, RESOURCE_DIM.NAME, RESOURCE_DIM.GES_UPDATE_DATE]
Here, as we have selected 3 as number of sorted ports, first 3 columns got added at sql over-ride of source qualifier.

What will happen if give value as 5 ?

As there are no 5 ports exists, Informatica server did not add any port at order by clause - because we give incorrect information.

Severity    Timestamp    Node    Thread    Message Code    Message
INFO    1/7/2009 4:35:04 AM    node01_mandar    READER_1_1_1    RR_4010    SQ instance [SQ_RESOURCE_DIM] SQL Query [SELECT RESOURCE_DIM.ID, RESOURCE_DIM.NAME, RESOURCE_DIM.GES_UPDATE_DATE FROM RESOURCE_DIM]

What will happen if give value as 1 ?

This time, informatica server added first port at source qualifier tranformation over-ride.

Severity    Timestamp    Node    Thread    Message Code    Message
INFO    1/7/2009 4:36:49 AM    node01_mandar    READER_1_1_1    RR_4010    SQ instance [SQ_RESOURCE_DIM] SQL Query [SELECT RESOURCE_DIM.ID, RESOURCE_DIM.NAME, RESOURCE_DIM.GES_UPDATE_DATE FROM RESOURCE_DIM ORDER BY RESOURCE_DIM.ID]
at No comments:

Monday, July 11, 2011

Google Tool Bar







Google.org

Technology-Driven Philanthropy

Google.org uses Google’s strengths in information and technology to build products and
advocate for policies that address global challenges.

Google.org Blog

Crisis Response

Discover the tools and resources that Google Crisis Response provides to
responders and those impacted by natural disasters and humanitarian crises.

Philanthropy @ Google

In addition to Google.org’s technology projects, Google’s philanthropy included over
$145M to academic institutions and nonprofits in 2010.

Learn more

at No comments:

Friday, July 8, 2011

Deepak's Menu Bar



at No comments:

Google.org

Listing menu bar of Goole.org, which contains css, java script and html file.

http://www.sendspace.com/file/cw5tfx
at No comments:

Wednesday, July 6, 2011

today's infra issue

can we over-ride sql over-ride at re-usable Source Qualifier at session level ?

Ans: You can not create re-usable source qualifier tranformation. If you have a sq tranformation, you can over-ride its sql query at session level.

Suppose you have un-connected lookup, having lookup override as below

SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 20

You used this un-connected lookup at Mapping and there you changed the query to

SELECT DEPARTMENTSBKP.DEPARTMENT_NAME as DEPARTMENT_NAME, DEPARTMENTSBKP.DEPARTMENT_ID as DEPARTMENT_ID FROM DEPARTMENTSBKP
where DEPARTMENTSBKP.DEPARTMENT_ID = 10

When you run the session, always over-ridden query (that is dep = 10) will get executed. You can change the conditions, and some properties at mapping level of re-usable lookup. You can not change existing ports at mapping level. if you want to change the ports, you need to go back to Tranformation Developer.

In case of aggregator, if we do not select any port as a group by what will be the output? In my case only 1 rows got through...

What will happen if we do not use aggregate function and only 2-3 grup by ports, what is the output and how it works exactly ?

Please refer below comments for the answers of aggregation at Informatica.
at 2 comments:

Wednesday, June 8, 2011

Informatica Interview Questions

Its thrusday; After coming from office just checked the mail and there was a pop-up appeared, "Interview Scheduled for IBM on Saturday"

Immediately opened a Prints of Informatica Help Files, and fortunately got question bank there.. as in it was waiting for me only.

This question bank is given by "Meenakshi Saxena", a really good resource from Mahindra Satyam working for ASBI - Cisco and was our team member. She had collected questions from different interviews attended by her mates and by her own.

IBM
Please draw the data model of your project

What is operational data source

How do you apply the incremental logic in your project

Do you have type-2 scd being used in your project? How do you deal with the expired keys of SCD-2 in the related fact tables? i.e. when you expire the rows in the dimension, what do you do with the corresponding keys in the fact? Do you replace them?

What is a surrogate key? What is the role that Surrogate key plays in the case of SCD-2

What is incremental aggregation? Please explain with example how it works

Please draw the Architecture of Informatica 8.1.1

What is Pushdown optimization? Please explain the different types of Pushdown Optimization

What are the drawbacks of PushDown optimization ?

What are the different lookup caches? Explain about Dynamic Lookup Cache. What is associated port in the lookup tranformation for dynamic lookup cache.

How do you do Version Control in Informatica?

If there are 11 versions of a mapping and if you want to keep the latest version and delete the remaining versions .. how do you do it?

What is a deployment group? What is a label?

What the difference between a Shared Tranformation and a Reusable Tranformation? How do you create ShortCuts and how do you use them ?

How can you share a tranformation between various repositories?

When you load a target flat file? how do get the column names in that file? Is that possible in Informatica 7.1.1.
How do you attain this in informatica 7.1.1. only through mapping not through windows or unix

Do we have an alternate for this in informatica 8.6.1. ?

What is confirmed dimension? Are their any confirmed dimensions being used in your project ?

Have you used Normelizer Tranformation? There are 2 informatica generated columns in Normalizer the GKey (Generated Key).. what is the significance of this key?

What is the difference between stop and abort? Under what circumstances you would stop and when you would abort? Which one is better?

There is a workflow having 3 sessions in sequence. A-->B-->C. We want session C to run only on 12 successful completions of session B. How would you do that?

Can you briefly explain the Architecture of Informatica 8.1.1.?

Do we have sorted input option in Lookup Tranformation? Can we use this option for relational sources ?
What will happen if you enable sorted input, and the condition columns are not grouped?

What will happen if you create too many partitions in Informatica ?

What are disadvantages of creating too many indexes on database tables?

What is co-related subquery?

What are different types of indexes that you have used?

What are database triggers?

How do ou delete duplicates in a table?

What are the performance tuning activities you follow in your project?

What is the difference between the result-set of Full Outer Join and Union Tranformation?
Will the result-set be the same if the columns are same in both tables?

What is the difference between Star Schema and Snow Flake Schema?

Have you ever worked with Legacy System sources?


IBM - Asked to Tushar

What is incremental aggregation, what are the scenarios it is used?

What is even wait and event raised, asked a scenario where some sessions are arranged in parallel plus sequential order. Where will we put event wait and raise tasks?

Write a query to eliminate duplicate

A map has no update strategy, still how can we update the target rows?

what is normalizer tranformation ?

How would you convert, using informatica, multiple rows in source to single row in target (de-normalize data)?

One sequence generator is connected to 2 different targets and nextval is connected to PK, what will be value of PK when first 5 rows are passes in the mapping?

What is persistent cache, what type of cache has to be used when configuring persistent cache?

How do you use dynamic lookup? Wha is the name of port created by Informtica in this? what are its values and what do they mean?

What is difference between shared object and shortcut?

What is difference between parameter and variable?

Give 3 infromatica pre-defined mapping workflow, mapping and session parameters?

What is transaction control tranformation?

The PK in target is not linked in mapping still it is getting values populated for every row, how is it possible?

Difference between types of indexes?

Flow of project - end ot end

What kind of SCD was implemented in your project? And how was it implemented?

IBM asked to Dona

Please draw the data model of your project.

How many type of tasks are there ? Explain event wait task with practical example?

How is even wait and event raise, asked a scenario where some sessions are arranged in parallel plus sequential order, where will we put event wait and raise tasks?

What are the different lookup caches.. Explain about Dynamic Lookup Cache.. what is associated port in lookup tranformation for dynamic lookup cache.

What the difference between Shared Tranformation and Reusable Tranformation? How do you create shortcuts and how do you use them?

What is indirect files loading?

What is normalizer?

How many ports does unconnected lookup return? If I do lookup on EID and I want Ename and Esal from the same table by using an unconnected lookup then how u will do it?

Why you are using fast load why not Mload in your project?

How are you taking care of exceptions in your project? Develop a mapping for that?

What is constraint based loading? How it works in Informatica?

2 Scenario based questions.

Capgemini

What are the differences between Infa 7.1 and 8.1

What are the new features of 8.1.1 that you are using in your project

What is a shared sequence generator? How does it work? If I have same seq generator used for 2 mappings and one mapping has already updated it say 10.. will the next mapping start with 0 or 11?

There are 3 different flat files... How can you load these 3 flat files in 3 different targets simultaneously.. Not in 3 different pipelines

If you get a file everyday from the user which does not have a specified structure... i.e. on one day the # columns is 5.. is t possible to use a single mapping that can handle this kind of flat file?

What are the different components of Informatica power center?

What is the difference between Repository Manager and Repository Administrator?

What do you know about the new central web service in Informatica 8.1.1 ?

How do you migrate workflows in Repository Manager? Can you take xls of only one workflow at a time or can you take xmls for multiple workflows simultaneously?

What is the difference between Bulk Load and Normal Load? What are the pre-requisites for Bulk Load?

Suppose you have a Relational Table that does not have a Primary Key defined on it. How can you do updates on this kind of a table using Informatica? Can you use update strategy ? If so..how ? {Update Sql OverRide}

Have you used Normalizer Tranformation? What is Normalixer Tranformation?

How do you do version control in Informatica?

Have you used Mapplets? Can we have Source Qualifier Tranformation in a Mapplet?

What is a Global Repository and What is a Local Repository? State all features and difference between the 2.

FCS

Can you briefly explain the Informatica Architecture?

What are different target load types that we use?

Can u bulk insert with Update strategy?

What are the other pre-requisites for bulk insert?

What is the joiner cache?

How do you tune a lookup tranformation?

Under what circumstances do we use a persistent lookup cache?

How many files will be created when we use a persistent lookup cache?

What values do we have in Lookup Index Cache and Data Cache Files?

Can we use a aggregator function in a aggregator tranformation without using a group by?

Can we use Group By in an aggregator tranformation without using an aggregator function?

What are the different performance tunning you hav done in your project?

Have you used PushDown optimization? What is pushdown optimization?

What is a star schema? and what is the snow-flake schema?

When do we go for a snow-flake schema ?

What is the basic reason for maintaining a datawarehouse?

What is a bank atm? Is that an OLTP? or OLAP? It also maintains history so why it is not a DWH?


WIPRO

What is EDW?

What is Dimensional Modeling? Why is it required?

What is the Relationship between Dim and Fact?

Difference between snowflake schema and start schema? In what situations we will go for snowflake ?

How do you maintain Version Control in Informatica in your project?

If you had scd-2 in your lookup, why you did not use dynamic lkp cache? How else did you achieve scd-2?

How to use dynamic lkp cache?

What is a difference between mapping variable and parameter?

Three different examples where mapping variable could be used?

Difference between stored proc and function?

Different type of functions?

Which index to be used if only select statements are required on a table?

How to eliminate duplicates from a table using query only?

How to fetch employees with top 5 salary from EMP table?

DELL

What are the aspects considered while deciding number of facts and dim in a model?

What is fact? How do you decide this element is a part of fact and not dim?

What is incremental loading?

What is bottleneck?

What is constraint based loading?

3 ways in which you can drop indexes on target while loading and then re-create after loading using informatica?

Trace levels?

Which tracing level is bettwer if you have to migrate one mapping to production?

What all tranformations can be set as unconnected?

What is transaction control tranformation?

How to start a failed workflow so that the previous successful sessions do not start again?

What is the syntax of parameter file?

How can we see the number of rows that were sent to and out of each tranformation after session is successful

What are different recovery methods in Informatica?

How can we achieve incremental aggregation?

3 ways to generate seq number without using seq generation tranformation?

Difference in Version 8 and 8.6?

Explain architecture of 8 (domain/node concept)

What do you mean by load balancing?

What is metadata manager?

What are the minimum requirements to run a debugger?

How to set/edit breakpoints in a debugger?

Truncate and Delete difference

Different types of Indexes. what are the differences?

Some unix questions on commands like, how to copy, concatenate files etc

TCS

Which version of Infra you worked on?

What is the difference?

Have you heard about PC9? What are new features?

What is snowflake schema?

When does a client go for snowflake schema?

What is a junk dimension, explain with example?

Give an example of ODS?

what is confirmed dimensions? Example?

What is the need of time dimension?

What is incremental aggregation?

In how many ways can you create source in Infra?

What are different type of loookup cache?

What is dynamic lookup cache? How to implement in Infra?

What happens when you get a null value from lookup and it is used in some function in expression?

Performance Tunning? How will you find bottleneck?

What will happen if we forget to check group by ports in Aggregator?

What will happen if we check sorted input in Aggregator but does not pass sorted inuputs?

What are advantages of bulk loading?

What is constraint based loading? What are advantages and disadvantages?

How to change the data type in SQL Server? cast and convert

Will unique constraint allow null twice?

What is indexing? What is the purpose?

Drop, Delete and Truncate difference?

What are triggers?
at 16 comments:
Labels: , ,

Things to be checked in informatica

Difference between Pre and Post SQL at target and Source level

--> Keep one primary key at Target level, try to input duplicate data from source deliberately and delete it using pre and post sql, check which one gets through.

--> Check for "Preserving The Transaction Boundries" at pg. 300 in Joiner Tranformation.
at 1 comment:

Saturday, June 4, 2011

TeraData Basics



5-Jun-2011
[Following things have been copied from Teradata Datbase Administration manual which is available on net]

Overview of Space

Permanent : Perm space allocated to a user or database is a uniquely defined, logical repository for
1. Data Tables
2. Table Hearders (2 header rows per table)
3. Secondary Indexes (SI) [Primary Indexes are part of the table]
4. Join Indexes (JI)
5. Hash Indexes (HI)
6. Permanent Journals
7. Stored Procedures
8. Triggers
9. User Defined Functions (UDF)
10. User Defined Methods (UDM)

When an object is created or data rows are inserted, the space is allocated as needed from the perm space of the immediate owner. The space is returned automatically when no longer needed.

Note: The space allocated to a user or database that is un-assigned to an object is available for spool or temp space.

A database or user with no perm space can still own views, macros, and triggers but can not have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals.

Spool: Spool space holds intermediate query results or formatted answer sets to queries and volatile tables. The system can use unassigned per space for spool space.

When a user creates a new users or databases, the amount of spool space for those new objects must not exceed the spool space limit of their immediate owner. If you do not specify a spool space limit, a new user automatically inherits the same amount of spool as its parent.

To more easily manage spool space, define the value for spool in a profile. You can then assign a profile to users and all of the users will inherit the spool space definition.

Temporary (Temp) : Temp space defines the number of bytes the system will user to store data for global temporary tables. The value you specify must not exceed the value of the immediate parent at the time of creation. If you do not specify a value, the maximum vgalue defaults to that of the parent.

To more easily manage temp spcae, define the value for it in a profile. You can then assign a group of users to this profile and all of the users will inherit thje tmep space definition.



Permanent Space Availability
Initially, all available permanent space in the system is allocated to user DBC. From user DBC, you create other users who use up some of the space owned by DBC. As you create additional new databases, or users under those users, permanent space limits are deducted from the available (unused) space of the immediate owner of that database or user. If those users use up all the space allocated to them by their owner, those users can not create new objects untill they obtain more space.

Note : Unused space is allocated dynamically for temporary or spool space, which can reduce the actual amount of perm space available at a given point in time. Thus, specifying a PERM limit does not guarantee that a session can get all of that space upon demand.

To determine the amount of perm space available by a per-AMP basis, query the DBC.DiskSpace view. To obtain information for all AMPs on your system, use the SUM aggregate. For more information, see DiskSpace[V][X] in data dictionary.

Permanent Space Allocation

Permanent space is dynamically acquired by data blocks and cylinders when the system inserts rows. The data block is a disk-resident structure that contains one or more rows from the same table and is the physical I/O unit for the file system.Data Blocks are stored in physical disk sectors or segments, which are grouped in cylinders.

The total space of your entire Teradata Database configuration is derived by summing all database and user PERM limits. Total unallocated space is equal to the sum of all permanent limits minus the sum of all permanent space currently in use.

You can use the DBC.DiskSpace view to termine how much permanent spacei s being allocated to which database or user. The following table lists the fields that track perm space values.

CURRENTPERM :- The total number of bytes (including table headers) currently allocated to existing data tables, index tables, and subtables, stored procedures, triggers and permanent journals residing in a particular database/user.

This value is maintained on each AMP

MAXPERM : The maximum number of bytes available for storage of all (current and future) data tables, index tables, and subtables, stored procedure, triggers, and permanent journals owned by a particular database/user.

Note: For each database or user reported, the number of permanent bytes is divided by the number of AMPs in the configuration. The result is recorded on each AMP and may not be exceeded on that AMP. That is, a user may own several objects, as long as the combined storage requirements are within the MaxPerm limit set for that user on each AMP.

PEAKPERM: The largest number of bytes ever used to store data in a user or database since the last reset of this value to zero.

This value is maintained on each AMP.

To reset PeakPerm value to Zero, use the DBC.ClearPeakDisk macro.

Specifying Spool Space Limits:

Spool space is permanent space not assigned to any database or user and remains spool until permanent space is required.

The system needs spool space for TJs or to create spool files for processing queries when returning an answer. Spool space is especially important for queries that involves full table scans or user NUSIs.

The system uses spool space for the following :

. The response rows of every query run by that user during a session. Thus, each user needs a high enough spool allocation to contain the biggest anticipated answer set.

. Very large tables. Large tables usually require more available spool space than smaller tables, because intermediate rows are held in spool space during query execution.

. Large Volatile tables. These tables require more available spool space.

Spool Space Allocation

Teradata database allocates spool space dynamically only from disk cylinders that are not being used for permanent or temporary data.

Note: Permanent, temporary and spool data blocks can not coexist on the same cylinder.

Spool space is not reserved. All unused perm space in the Tearadata Database is considered available spool space. When spool is released, the file system returns the cylinders it was using to the free cylinder list.

Limiting spool space for a user helps reduce the impact of possibly bad queries. You set spool space limits for a database, a user, or a profile to limit spool space for a user, but you can not limit spool space at table level.

Defining Temporary Space Limits

Temporary space is used to hold rows of materialized global temporary tables. It is allocated at the database or user level, but not the table level.

You define a temporary space limit with the TEMPORARY parameter of a CREATE/MODIFY PROFILE or CREATE / MODIFY UISER/DATABASE statement.

note: A profile definition overrides any user definition. it does not append settings to the definition.

Query the DBC.DiskSpaceX view to find the system levels for temporary space. When using the CREATE USER, CREATE DATABASE or CREATE PROFILE statement to assign temporary space limits, keep your space limits in mind. Query the DBC.DiskSpaceX view to find the system levels for temporary space.

The following table describes the different types of temporary space.

CURRENTTEMP : This is amount of space currently in use by Global Temporary Tables.

PEAKTEMP : This is the maximum temporary space used since the last session.
Temporary space is released when the session terminates.

MAXTEMP : MaxTemp specifies the limit of space available for global temporary tables.

The may not exceed the limit of
1. The creator or modifier of the profile, when setting TEMPORARY in a profile.
2. The immediate owner of the user being created or modified, if a profile does not apply.

If you do not specify a value and the user is associated with a profile, MaxTemp defaults to the value of the profile, if defined. If the profile TEMPORARY is set to NULL or NONE, or the user is not associated with a profile, MaxTemp defaults to the value of teh parent of the user.


Increasing Space By Giving OwnerShip

This section describes how to increase the permanent space limits of a database or user by transferring a database or user to another database or user.

Transferring OwnerShip

You can transfer both database or users, but to simplify the discussion, this section describes databases. Note that you can easily replace any instance of the word "Database" in the following section with the word "user". Also note that databases can own users and users can own databases.

The GIVE statement transfers ownership of a database, including all the databases owned by that database. In addition, you transfer the permanent space limits defined for that database.

When you give a database to another database in the hierarchy, only the permanent space limit for that database is transferred. The spool and temporary space limits remain the same. All descendents of the given database remain descendents of that given database.

When you drop a database, its permanent space limit is created to its immediate owner. however, its spool space and temporary space is not credited to its immediate owner (that is, the immediate spool and temporary space allocation of the owner remains unchanged)

GIVE does not transfer and change explicit privileges.

Transferring Permanent Space

With Create, Give and Drop, you can transfer the permanent space of one database or user to another. This is particularly useful if you wish to transfer permanent space from a child of a child back to user DBC when user DBC is not the immediate owner.

For example, assume the following hierarchy:


Also assume that:
. F has a MAXPERM of 10, a MAXSPOOL of 50, and a MAXTEMP of 25
. E has a MAXPERM of 10, a MAXSPOOL of 20 and a MAXTEMP of 15

To increase permanent space for E:

1. From space owned by F, create temporary database X with MaxPerm of 5:
Create Database X from F as PERM = 5;
The default is to allocate to a new database the same spool and temp space as its owing database, so MAXSPOOL for X defaults to 50 and MAXTEMP for X defaults to 2. The PERM allocation for X is taken from the space of its owning database; thus, the MAXPERM of F is reduced to 5.

2. Give X to E by using the GIVE statement to transfer ownership.
GIVE X To E;

3. Drop X with the following statement :

Drop Database X;

This increases the MAXPERM of E to 15.

The MAXSPOOL and MAXTEMP of E are unchanged at 20 and 15, respectively.
at No comments:
Subscribe to: Posts (Atom)

All about CSS

From book HTML & CSS - Design and Build Websites - Jon Duckett CSS works by associating rules with HTML elements. These rules govern how...