11 G New Features

In this article we will cover new features of 11 G

1) Case senstive Passwords : In 11G oracle password are case senstive ,but this feature can be changed by altering the parameter 

   ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

   

2) Invisible indexes :In 11G we can make index invisible .Once we make index invisible it will be ignored by Optimiser 

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

ALTER INDEX index_name INVISIBLE;

Invisible index is useful in cases where you want to see the impact of dropping the indexes.

3)Read only Tables : In 11G we can create read only tables

ALTER TABLE table_name READ ONLY;

In previous versions to make a table read only we have to restrict by giving select access.

We can again make it availaible for writing by 

ALTER TABLE table_name READ WRITE;

4) PL/SQL Function Result Cache 

PL/SQL Function result cache provides a nice way to enhance the performance of Oracle functions by storing 

the results of specific input parameter combination of function calls in SGA .

This will be particularly useful in cases where sql function is being called in SQL query or PL/SQL loop.

CREATE OR REPLACE FUNCTION test_cache (p_in IN NUMBER)

  RETURN NUMBER

  RESULT_CACHE

AS

5)DDL With the WAIT Option 

In prior 11G if you try to alter a table which is undergoing some DML operation you will get the error 

"ORA-00054: resource busy" 

ALTER SESSION SET ddl_lock_timeout=20;

DDL_LOCK_TIMEOUT parameter tells the number of seconds a DDL coomand should wait for Locks to become available.

We will discuss more features of 11 G in coming articles.

If  you want to have further details on 11 G new features please mail us at support@itnirvanas.com

Informatica Metadata Queries (Part 3)

We will continue to publish new metadata queries

Purpose :  To Find Truncate Table Option

Query : 

select task_name,'Truncate Target Table' ATTR,decode(attr_value,1,'Yes','No') Value 

 from OPB_EXTN_ATTR OEA,REP_ALL_TASKS RAT  

where OEA.SESSION_ID=rat.TASK_ID 

and attr_id=9 

Description : This query will give all sessions where Truncate Table Option is on

Purpose : This query will give count of Mapping,Instance where Sqloverride has been done

Query : WITH detail AS

(SELECT c.subject_area, c.mapping_name,

d.instance_name source_qualifier_name,

CASE WHEN a.attr_value IS NOT NULL THEN 1 ELSE 0 END as OVR_OK

FROM rep_all_mappings c,

opb_widget_inst d,

opb_widget_attr a 

WHERE c.mapping_id = d.mapping_id

AND c.mapping_version_number = d.version_number

AND d.widget_type = 3

AND d.widget_id = a.widget_id

AND a.widget_type = d.widget_type

AND a.attr_id = 1

)

SELECT subject_area, 'SQ_OVERIDE' STATUS,

COUNT (DISTINCT mapping_name) NO_OF_Mapping,

COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,

COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ

FROM detail

WHERE OVR_OK =1

GROUP BY subject_area

UNION

SELECT subject_area, 'SQ_NON_OVERIDE',

COUNT (DISTINCT mapping_name) nb_mapping,

COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,

COUNT (DISTINCT (source_qualifier_name)) nb_inst

FROM detail

WHERE OVR_OK =0 

GROUP BY subject_area

Description : This query will count of Mapping,SQ in mappings,SQ where override has been done.

Purpose : This query will SQL Override queries upto 4000 length

Query :

SELECT subj_name, mapping_name,widget_id,

replace(replace(replace (next_attr_val0||next_attr_val1,'   ',' '),chr(13),' '),chr(10),' ')  QUERY_OP_1

fROM 

(

select distinct SUB.subj_name subj_name, MAP.mapping_name mapping_name,wid.widget_id,

WID.attr_value next_attr_val0,

LEAD(WID.ATTR_VALUE,1,'') OVER ( PARTITION BY wid.WIDGET_ID,wid.MAPPING_ID ORDER BY wid.WIDGET_ID,wid.MAPPING_ID,wid.LINE_NO) NEXT_ATTR_VAL1,

WID.LINE_NO

from  opb_widget_attr WID,  opb_mapping MAP,  opb_widget_inst WIDINST,

opb_subject SUB

where WID.widget_id = WIDINST.widget_id

and WID.widget_type = WIDINST.widget_type

and WID.widget_type = 3

and WID.attr_id = 1

and WIDINST.mapping_id = MAP.mapping_id

and MAP.subject_id = SUB.subj_id

ORDER BY SUB.subj_name, MAP.mapping_name,wid.widget_id,WID.LINE_NO

)

WHERE  next_attr_val0||next_attr_val1 LIKE '%UPL%'

and line_no=1

Purpose : This query will SQL Override queries upto 4000 length.This query will work in oracle database

Please note that these queries has been tested in Oracle 10G.

If you need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.

Informatica Strange Problems

Some time we encounter strange problem while working with informatica.I would like to highlight some problem  which no way seems to be logical

Error : Informatica Debugger: Invalid session can not be debugged .Select a valid session instance to use

 

Problem : You are trying to run your mapping in debug mode by selecting valid session but you are getting below error even though your session is valid.

Solution :

1)      Copy your mapping Suppose M1 as M1_Debug

2)      Make your session as reusable

3)      Making your Initial Mapping M1 as invalid by someway

4)      Now refresh your session it will ask for new mapping then select M1_debug

5)      Revalidate your session

6)      Try to run your mapping(M1_debug) using second option use existing reusable instance for mapping

Now you should be able to run your mapping in debugger mode

Though it not a 100% correct method but it worked in my case but it is worth trying

 If you hit same issue but are not able to resolve please email to support@itnirvanas.com

 Or if you find some better solution please share with us

Error : CMN_1892 Error: Data for Lookup [] fetched from the file [] is not sorted on the condition ports

Problem : You are suddenly started getting this error though you are not using lookup with sorted ports. You also tried all the things like using sorted file etc.

Solution : For us suddenly file size increased almost 10 times and we started getting this error .We tried all the thing but it did not work.

In the end we increased Lookup Data Cache size and Lookup Index Cache size and it started working fine.

Though it not a 100% correct method but it worked in my case but it is worth trying

 If you hit same issue but are not able to resolve please email to support@itnirvanas.com

Please not both of these issues we encountered on Informatica 7.1.1

 Or if you find some better solution please share with us  

How to validate all mappings in a particular folder

In this post we will discuss how to validate all mapping in a particular folder

Step1 : Go to the Repository manager client

Step2 :

Go to option Tools->Queries

Create a new query with name  ALL_MAP_QUERY

Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:

folder = folder-name
object type = mapping

Step 3:

a)      Connect to power center repository using pmrep

pmrep connect -r RepositoryName -d DomainName -n UserId -x Passwd

b)  Create a Persistent output file using command

pmrep executequery -q  ALL_MAP_QUERY -u d:\infa8\PERS_OP_FILE

Step 4 :Use the persistent output file created in last step to validate mappings

pmrep validate -i d:\infa8\PERS_OP_FILE -u PERS_OP_FILE_LOG

Informatica Useful Tips (Part1)

Tip 1 : Ignore the SQ SQL Override conditionally
It is possible by defining a mapping parameter for the WHERE clause of the SQL Override. When you need all records from the source, define this parameter as 1=1 in theparameter file and in case you need only selected data, set the parameter accordingly.

Tip 2 : Overcome size limit for a SQL Override in a PowerCenter mapping
The SQL editor for SQL query overrides has a limit of maximum of 32,767 characters.
To overcome this we can do following
To source a SQL of more than 32,767 characters do the following:
1. Create a database view using the SQL query override.
2. Create a Source Definition based on this database view.
3. Use this new Source Definition as the source in the mapping

Tip 3. :Export an entire Informatica folder to a xml file
We can do this in 8.1.1,
1) In designer Select Tools -> Queries and create a newquery. Set the Parameter Name "Folder" equal to the Folder you want to export and then run the query.
2) In the Query Results window, choose Edit -> Select All Then select Tools -> Export to XML File andenter a file name and location. Full Folder willbe exported to an XML file.
We can also use the query tool in Repository Manager, to geteverything in the folder (mappings, sessions, workflows, etc.)

Tip 4 : Validate all mappings in a folder

We can validate all mappings in a folder in following way:
1. Go to the Repository manager client
2. Create a query with parameter name and condition to retrieve the mappings in a folder
as the following:
folder = folder-name
object type = mapping
Use the following Pmrep Execute query command to get persistent output file:

executequery -q [-u ] [-a (append)]

We can write the result to a persistent output file.If the query is successful, it returns the total number of qualifying records.We can use newly created persistent output file as a input file in the following pmrep validate command :

Pmrep validate-i }[-p [-u ] [-a (append)][-b (verbose)]

Tip 5 : If you are getting following error
CMN_1022 [
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed[Microsoft][ODBC Excel Driver]Optional feature not implemented
Database driver error...
Function Name : SetConnectOption
Database driver error...
Function Name : AutoCommitOff]
Solution :to make an entry of excel ODBC in powermart.ini file in informatica folder

If you need some more tips please mail us at support@itnirvanas.com.

Informatica Metadata Queries (Part 2)

In this post we will continue with informatica metadata queries

Purpose : To Find Tracing Level for Session
Query :
select task_name,decode (attr_value,0,'None',1,'Terse',2,'Normal',3,'Verbose Initialisation',4,'Verbose Data','') Tracing_Level
from REP_SESS_CONFIG_PARM CFG,opb_task TSK
WHERE CFG.SESSION_ID=TSK.TASK_ID
and tsk.TASK_TYPE=68
and attr_id=204 and attr_type=6
Description : This query will give tracing information along with session names.This query is helpful in identifying the session which are having particular type of Tracing level like Verbose.

Purpose : To Find name of all stored procedure being used in stored procedure transformation
Query :
select attr_value from
OPB_WIDGET_ATTR
where widget_type=6 and attr_id=1
Description : This query is helpful when you require to know name of all stored procedure being used in informatica.

Purpose : To find who saved mapping last time
Query :
SELECT substr(rpl.event_time,7,4) substr(rpl.event_time,6,1) substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11) "EventTimestamp" ,
usr.user_name "Username",
DECODE(rpl.object_type_id,21,s21.subj_name,('('rpl.object_type_id')')) "Folder",
obt.object_type_name "Type",
DECODE(rpl.object_type_id,21,map.mapping_name,('('rpl.object_type_id')')) "Object"
FROM
opb_reposit_log rpl,opb_object_type obt,
opb_subject fld,opb_mapping map,opb_users usr,opb_subject s21
WHERE obt.object_type_name = 'Mapping'
AND rpl.object_type_id = obt.object_type_id
AND rpl.object_id = map.mapping_id(+)
AND rpl.object_id = fld.subj_id(+)
AND rpl.event_uid = usr.user_id
AND map.subject_id = s21.subj_id(+)
ORDER BY(substr(rpl.event_time,7,4) substr(rpl.event_time,6,1)
substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11)) DESC
Description : This query is helpful when you want to know who saved the mapping last time .

If you need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.

Unix Interview Questions (Part1)

Following are some unix commonly asked interview questions  

Q 1  What is command to check space in Unix 

Ans : df -k 

Q 2 If a file has permission 000 then who can access the File 

Ans : System Administrator .

Q 3 What is command to kill last background Job 

Ans : kill $!

Q 4 How you will list all Hidden files 

Ans : ls -la|grep ^[.] 

Q 5 What is command to create Zero Byte File 

Ans : touch filename 

Q 6  What is difference between diff and cmp command 

Ans : cmp -It compares two files byte by byte and displays first mismatch. 

           diff -It displays all changes required to make files identical.

Q 7  What does $# stands for 

Ans : It will return the number of parameters passed as command line argument.

Q 8 How many prompts are availaible in Unix System

Ans : PS1 the default prompt 

          PS2 Multiline Prompt 

Q 9 How to kill a process forcibily 

Ans : kill -9 PID 

PID (unique identifier of process) 

Q 10 How to create a hidden file 

Ans : While creating file put . DOT in front of file name

If you need more interview questions please mail us at support@itnirvanas.com.