Thursday, June 25, 2015

encrypted password in Parameter File

  • On Windows, From command prompt go to folder bin of Server
     CD C:\Informatica\PowerCenter8.6.0\server\bin

  • Type following command where infotgt is password of target database
          pmpasswd infotgt -e CRYPT_DATA

  • Use the encrypted password in Parameter File
     [Dinesh.WF:wf_EMP_DEPT_SAL.ST:s_EMP_DEPT_SAL]
     $Param_tgt=rhDGQbmanvR2xgbbbfLw6A==

Friday, June 12, 2015

Informatica repository to search for column name exist in which mapping

select TO_OBJECT_FIELD_NAME,MAPPING_NAME from dbo.REP_MAPPING_CONN_PORTS
where TO_OBJECT_TYPE=2
AND SUBJECT_AREA='folder name'
and TO_OBJECT_FIELD_NAME ='column name'



SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name= 'column name'
ORDER BY schema_name, table_name;

Thursday, January 23, 2014

MS SQL useful statement

1. To find out how much space occupied by each table and index

Column names are database name, current date, row count, data size, index size.

SELECT DB_NAME() AS DatabaseName
, object_name(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.[rows] AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id

2. The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait

  • sp_helpdb BizTalkDTADb

    ALTER DATABASE BiztalkDTADb
    SET RECOVERY SIMPLE;
    GO

    DBCC SHRINKFILE (BiztalkDTADb_log, 1);
    GO
    sp_helpdb BizTalkDTADb
    GO
    ALTER DATABASE BiztalkDTADb
    SET RECOVERY FULL
    GO
    Why on Earth are you posting a reply to a thread that started in 2006, and which has not been touched for over two years? On top of that you solution is a bad one for several reasons.
    1) You suggest shrinking the log file to 1 MB, which unlikely to be correct. What is correct is difficult to tell, because it depends on the system in question. For a developer database 50 MB can do. For a production database that itself is 1TB, a log file of 100 GB is not unreasonable.
    2) You suggest flipping the recovery model from full to simple and back again. This breaks the log chain, and you will not be able to do point-in-time recovery from this point and on. There are two options here:
    a) You don't care about point-in-recovery. (For instance, because it is a development database on your workstation). In this case keep the database in simple recovery.
    b) Immediately take a full backup, so that you can meet the RPO (Recovery Point Objective) in your SLA (Service Level Agreement.)
    And overall, shrinking database files is a very exceptional thing which you should only do when you know that the file has grown to a size that will not achieve again. This is particularly true for log files, since growing a log file takes time.

Thursday, September 26, 2013

ERROR: "The mapping contains blocking transformations which may cause the mapping to hang while executing." when validating a PowerCenter mapping with multiple Joiner transformations
Problem Description
When validating a PowerCenter mapping with multiple joiners, the mapping is invalidated and the following message appears:
The mapping contains blocking transformations [Joiner_Name] which may cause the mapping to hang while executing.
What is the main Joiner Validation Rule?
Cause
This message indicates that one or more Joiner transformations may be blocked based on the master/detail relationship set up in the mapping.
Solution
The basic rule for this validation is that the active reader should not be blocked.

Example


S1 Source1
S2 Source2
D Detail
M Master
J1 Joiner1
J2 Joiner2
J3 Joiner3
In this example the validation for the first Joiner takes the master thread as a first reader (S2), so S2 is the active reader, and this reader will read data first.
When it attempts to start reading the data (as it is the master thread for J1), J2 is requesting to block (as it is a Detail thread in J2) which will cause the session to hang.

Example

If the master and detail reader is switched for the second Joiner (J2) as in the following example:
  
The first reader (S2 - evaluated based on the first Joiner master) will not be blocked because both threads which are linked to that particular reader are expecting data (requesting to read data).
Once that reader (S2) is finished reading the data, it will start the second reader (S1).
For the second reader, even though we have detail and master threads connecting to S1, both threads are expecting data, so this reading source will not be blocked. 

Wednesday, July 3, 2013

Expression Editor Issue After Using Dual Monitors

Expression Editor Issue After Using Dual Monitors


SQL Editor

When attempting to edit the SQL Override in the Source Qualifier (or Lookup) the window for the SQL editor is cropped and does not display the entire window.

Cause

This behavior is seen when using the Designer client tool on a machine running in dual display mode with the Extend the desktop to this monitor option enabled.

Solution

To resolve this do the following:

Close Designer and run regedit .

Go to the following key:

HKEY_CURRENT_USER\Software\Informatica\PowerMa rt Client Tools\\Designer\Opt ions\Global\Editor\SQL

Where PowerCenter_Client_Version is the PowerCenter client version (8.1, 8.1.1, 8.5, 8.5.1, 8.6, etc).

Change the values of the following to "0":

Expression Editor Position

Expression Editor Splitter Position

SQL Editor Position

SQL Editor Splitter Position


More Information


To avoid editor display problems when using dual display mode, do the following:

Open the Designer and move it to the larger monitor.

Open the Expression transformation.

Do not drag the Edit Transformations window to another position, leave it in the default position.

Open and work in the Expression editor.

If the Edit Transformations window is moved to another position, then the expression editor display will not display properly.

Notes:

This problem is not resolved when the machine is set back to single display mode.

The only sure way to avoid this issue is to use one monitor, the client tools are guaranteed to work properly with this configuration.

Monday, October 15, 2012

Dig Into Informatica PowerCenter Repository

How to find all the mappings and sessions which have PARALLEL hints in the SQL Override


SELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,
SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M
WHERE A.WIDGET_ID = W.WIDGET_ID
AND W.IS_VISIBLE = 1
AND A.VERSION_NUMBER = W.VERSION_NUMBER
AND A.WIDGET_TYPE IN(2, 3, 11) --Limit to Src/Tgt/Lkp Transformations
AND W.WIDGET_ID = I.WIDGET_ID
AND W.VERSION_NUMBER = I.VERSION_NUMBER
AND I.MAPPING_ID = M.MAPPING_ID
AND I.VERSION_NUMBER = M.VERSION_NUMBER
AND W.SUBJECT_ID = S.SUBJ_ID
AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
UNION
SELECT S.SUBJ_NAME, W.TASK_NAME
'.'
TI.INSTANCE_NAME TASK_NAME, 'SQL Override' WIDGET_NAME,
A.SESS_WIDG_INST_ID, TI.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM OPB_SWIDGET_ATTR A, OPB_TASK_INST TI, OPB_TASK W, OPB_SUBJECT S
WHERE A.SESSION_ID = TI.TASK_ID
AND A.VERSION_NUMBER = TI.VERSION_NUMBER
AND TI.WORKFLOW_ID = W.TASK_ID
AND TI.VERSION_NUMBER = W.VERSION_NUMBER
AND W.IS_VISIBLE = 1
AND W.SUBJECT_ID = S.SUBJ_ID
AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
ORDER BY 1, 2, 3, 4;


How to turn on Write Backward Compatible Session Log File for all session tasks in a folder

UPDATE OPB_TASK_ATTR A SET A.ATTR_VALUE=1
WHERE A.ATTR_ID=17 -- WBCSLF
-- 102 Write performance data to repository
-- 108 Collect performance data
-- 105 Enable high precision
AND EXISTS (
SELECT 0 FROM OPB_TASK T, OPB_SUBJECT S
WHERE T.TASK_ID = A.TASK_ID
AND T.VERSION_NUMBER = A.VERSION_NUMBER
AND T.IS_VISIBLE = 1
AND T.SUBJECT_ID = S.SUBJ_ID
AND S.SUBJ_NAME LIKE '%Sample%'
)
-- AND A.INSTANCE_ID = 0 -- Reusable Session Only ;



What are today's long-running tasks

select -- the SRC_ROWS may look big if joiner is used
T.SUBJECT_AREA, T.INSTANCE_NAME, TRUNC(AVG(END_TIME-START_TIME)*24, 2) RUN_HOUR,
MIN(T.START_TIME) START_TIME,
SUM(L.SRC_SUCCESS_ROWS) SRC_ROWS, SUM(L.TARG_SUCCESS_ROWS) TGT_ROWS
from REP_TASK_INST_RUN T, OPB_SESS_TASK_LOG L
where T.run_err_code=0 and (T.END_TIME-T.START_TIME)>= 1/24
and T.START_TIME >= TRUNC(SYSDATE)-2/24
and T.INSTANCE_ID = L.INSTANCE_ID
GROUP BY T.SUBJECT_AREA, T.INSTANCE_NAME
Order By RUN_HOUR desc;



Inside the Expression Transformation

select S.SUBJ_NAME, W.WIDGET_NAME, F.WIDGET_ID, F.FIELD_NAME, F.FIELD_ID, E.EXPR_ID, E.VERSION_NUMBER, E.EXPRESSION from OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_FIELD F, OPB_WIDGET_EXPR R, OPB_EXPRESSION E
where W.SUBJECT_ID=S.SUBJ_ID and W.WIDGET_ID=F.WIDGET_ID
and W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_ID
and W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_ID
and W.VERSION_NUMBER = F.VERSION_NUMBER
and F.VERSION_NUMBER = R.VERSION_NUMBER
and R.VERSION_NUMBER = E.VERSION_NUMBER
and W.IS_VISIBLE = 1
and w.WIDGET_NAME like 'EXP_SAMPLE%'
order by S.SUBJ_ID, W.WIDGET_ID, F.FIELD_ID;



Which session populates the specific target table

select SUBJECT_AREA, SESSION_NAME,TARGET_NAME,
MAX(SUCC_ROWS) AS ROWS#, TYPE_ID, COUNT(1) SAMPLE#
from (
select SUBJECT_AREA, SESSION_INSTANCE_NAME SESSION_NAME,
TYPE_ID, SUCCESSFUL_AFFECTED_ROWS SUCC_ROWS,
TABLE_NAME TARGET_NAME
from PM_REPO.REP_SESS_TBL_LOG
WHERE TYPE_ID in (2) -- Target Only
and END_TIME >= TRUNC(SYSDATE-40)
and SUCCESSFUL_AFFECTED_ROWS > 0
and TABLE_NAME like :TGT_NAME
'%' ESCAPE '\'
)
Group By SUBJECT_AREA, SESSION_NAME, TARGET_NAME, TYPE_ID
Order By SUBJECT_AREA, TARGET_NAME, SESSION_NAME ;



How to avoid re-generating & re-transporting ABAP program after slightly changing the mapping

CR 29233 and 63134 have been opened to have fix this problem.

-- Find the current LAST_SAVED and UTC_LAST_SAVED

select m.Mapping_ID, m.VERSION_NUMBER, m.Last_Saved, m.UTC_Last_Saved,
v.User_ID, v.LAST_SAVED, v.UTC_LAST_SAVED, v.COMMENTS,
p.PROGRAM_NAME, p.INSTALL_TIME, p.VERSION_NUMBER ABAP_VERSION_NUM
from opb_mapping m, OPB_VERSION_PROPS v, opb_program_info p
where m.MAPPING_ID = v.OBJECT_ID
and v.OBJECT_TYPE = 21
and m.SUBJECT_ID = v.SUBJECT_ID
and m.VERSION_NUMBER = v.VERSION_NUMBER
and m.MAPPING_ID = p.MAPPING_ID(+)
and m.SUBJECT_ID = p.SUBJECT_ID(+)
and m.VERSION_NUMBER = p.VERSION_NUMBER(+)
and m.MAPPING_NAME = :MAP_NAME o
order by m.version_number;



Then modify the LAST_SAVED, UTC_LAST_SAVED in OPB_VERSION_PROPS and OPB_MAPPING first; then modify the VERSION_NUMBER in OPB_PROGRAM_INFO if necessary.


--The timsstamp below is just a sample



update OPB_VERSION_PROPS v set last_saved='12/05/2008 09:10:11', UTC_LAST_SAVED=1228363499
where v.OBJECT_ID = :MAP_ID and
version_number = :VER_NUM and
object_type = 21;



update opb_mapping m set last_saved='12/05/2008 09:10:11', UTC_LAST_SAVED=1228363499
where MAPPING_ID = :MAP_ID and version_number = :VER_NUM;


update opb_program_info set VERSION_NUMBER = :VER_NUM
where mapping_id = :MAP_ID and subject_id = :SUB_ID;



Link from EXP_FROM.PORT_NAME to ???

select S.SUBJ_NAME, WF.INSTANCE_NAME
'.'
F.FIELD_NAME
FROM_NAME, F.WIDGET_ID, F.FIELD_ORDER, F.FIELD_ID,
WT.INSTANCE_NAME
'.'
T.FIELD_NAME TO_NAME, T.WIDGET_ID, T.FIELD_ORDER, T.FIELD_ID
from OPB_WIDGET Z, OPB_WIDGET_INST WF, OPB_WIDGET_INST WT,
OPB_WIDGET_FIELD F, OPB_WIDGET_FIELD T, OPB_WIDGET_DEP D, OPB_SUBJECT S
where Z.SUBJECT_ID = S.SUBJ_ID
and Z.IS_VISIBLE = 1
and Z.WIDGET_ID = F.WIDGET_ID
and Z.WIDGET_ID = WF.WIDGET_ID
and Z.RU_VERSION_NUMBER = WF.VERSION_NUMBER
and WF.REF_VERSION_NUMBER = F.VERSION_NUMBER
and WF.VERSION_NUMBER = D.VERSION_NUMBER
and WF.MAPPING_ID = D.MAPPING_ID
and WF.INSTANCE_ID = D.FROM_INSTANCE_ID
and F.FIELD_ID = D.FROM_FIELD_ID
and D.TO_INSTANCE_ID = WT.INSTANCE_ID
and D.TO_FIELD_ID = T.FIELD_ID
and D.MAPPING_ID = WT.MAPPING_ID
and D.VERSION_NUMBER = WT.VERSION_NUMBER
and WT.WIDGET_ID = T.WIDGET_ID
and WT.REF_VERSION_NUMBER = T.VERSION_NUMBER
and Z.WIDGET_NAME like 'EXP_FROM%'
and F.FIELD_NAME like 'PORT_NAME%'
order by F.FIELD_ID;



How the connection values is set at session level

select x.WORKFLOW_ID , t.TASK_ID, t.TASK_NAME, b.INSTANCE_ID, b.INSTANCE_NAME,
b.SESS_WIDG_INST_ID, x.CONNECTION_NUM, x.CONNECTION_TYPE, x.CONNECTION_ID,
x.CONNECTION_VALUE, c.OBJECT_NAME
from opb_session s, opb_task t, OPB_SWIDGET_INST b, OPB_SESS_CNX_VALS x, opb_cnx c
where c.OBJECT_ID(+) = x.CONNECTION_ID and s.MAPPING_ID = b.MAPPING_ID and
s.SESSION_ID = b.SESSION_ID and s.SESSION_ID = t.TASK_ID and
s.SESSION_ID = x.SESSION_ID and b.SESS_WIDG_INST_ID = x.SESS_WIDG_INST_ID and
t.SUBJECT_ID = :SUBJ_ID and b.INSTANCE_NAME like :WIDGET_NAME -- Source/Target
and t.TASK_NAME like :SESS_NAME
order by t.task_name, b.SESS_WIDG_INST_ID;



Find Transformation Attribute Override at Session Level

select f.SUBJ_NAME Folder, t.task_name SESSION_NAME, i.INSTANCE_NAME,
o.OBJECT_TYPE_NAME, a.*
from opb_swidget_attr a, OPB_SWIDGET_INST i,
OPB_WIDGET w, OPB_OBJECT_TYPE o,
opb_task t, opb_subject f
where a.attr_value in (:VALUE1, :VALUE2, :VALUE3)
and i.SESSION_ID = a.SESSION_ID
and i.SESS_WIDG_INST_ID = a.SESS_WIDG_INST_ID
and i.WIDGET_ID = w.WIDGET_ID
and i.WIDGET_TYPE = w.WIDGET_TYPE
and i.WIDGET_TYPE = o.OBJECT_TYPE_ID
and i.SESSION_ID= t.task_id
and t.SUBJECT_ID= f.subj_id;



List all the Transformations in a given Mapping

Including the transformations within the mapplet



WITH M AS (
select M.SUBJECT_ID, M.MAPPING_ID
from OPB_MAPPING M, OPB_SUBJECT S
where S.SUBJ_ID = M.SUBJECT_ID
and S.SUBJ_NAME LIKE :Folder_Name
and M.MAPPING_NAME like :Mapping_Name
)



select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME NOT IN ('Mapplet')
and W.MAPPING_ID = M.MAPPING_ID
union
select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME NOT IN ('Mapplet', 'Output Transformation', 'Input Transformation')
and W.MAPPING_ID in (
select X.MAPPING_ID
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M, OPB_MAPPING X
where W.MAPPING_ID = M.MAPPING_ID
and O.OBJECT_TYPE_ID = W.WIDGET_TYPE
and O.OBJECT_TYPE_NAME = 'Mapplet'
and X.REF_WIDGET_ID = W.WIDGET_ID
)
order by 1,2 ;

Thursday, September 6, 2012

SQL Server : When you restore a database backup to another server, you may experience a problem with orphaned users.

SYMPTOMS When you restore a database backup to another server, you may experience a problem with orphaned users. The Troubleshooting Orphaned Users' topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.
Troubleshooting Orphaned Users When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it. A. Showing a report of the current user to login mappings
The following example produces a report of the users in the current database and their security identifiers (SIDs).
Copy
EXEC sp_change_users_login 'Report';
B. Mapping a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped
USE AdventureWorks2012;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
Use Northwind
go
sp_change_users_login 'update_one', (restore users id)'test',(current database user id) 'test'
sp_change_users_login :
@Action
@UserNamePattern
@LoginName
@Password

Wednesday, August 29, 2012

Your session has expired due to inactivity. You will be redirected to the login page where you can enter your username and password" on Internet Explorer while logging into Administration Console

Cause
This issue occurs due to underscores in the hostname.
Solution
To resolve this issue, do any of the following:
•Use the physical IP address.
•Use a different browser, like Mozilla Firefox, Chrome, or Safari.
•Make DNS entry changes on the server side.

Row error occured while fetching data from database.

I am using 8.6 version and In our sql override there are couple of columns which are more than 4000 i.e string 4000 , we are taking a substruing and sending them as 1000 in sql override but still the data is getting rejected at source level. when i was going through the informatica potral i came across this infor will this help in resolving this issue... RR_4053 : Row error occurred while fetching data from database. This error occurs when no column value is greater than 4000 characters in this case. Cause This occurs when Informatica Integration Service is fetching an array.

Cause: This occurs when Informatica Integration Service is fetching an array.
Solution : Steps
1. In Administration Console , click on Integration Service
2. Expand Custom Properties
3. Add a property called OptimizeODBCRead with value No
4. Bounce Integration Service
5. With this flag the Integration Service uses the single row fetch instead of doing array fetch.

Saturday, March 10, 2012

Informatica PowerCenter Migration Checklist 7/28/2003

__ 1. Have all naming standards been used?

__ 2. Have the mappings/workflows been documented? Including:
- descriptions and text within the PowerCenter mappings and workflows;
- restart instructions; and
- special handling instructions.

__ 3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?

__ 4. Have DDL scripts moved to production? Are there additional objects/DDL to move to production for source or lookup tables?

__ 5. Optional, but critical: Have the business users reviewed the test data and approved the results?

__ 6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?

__ 7. Have reasonable error checks been coded for in the error handling routines?

__ 8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.

__ 9. Has the fail parent option been checked?

__ 10. Have all test settings such as “collect performance data” been disabled?

__ 11. Are there sequence generators that need the current values adjusted? What should the current value be?

__ 12. Have any reusable objects been used? Have they been documented?

__ 13. Have post-session emails been set up and are the proper email addresses used?
(Indicate whether a failure is critical or not.)

__ 14. Have relational connections been reviewed and are they correct?

__ 15. Have mapping and session parameters and variables been reviewed, values set?

__ 16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?

__ 17. Have the session failure variables been reviewed?