Simply Start sharing

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

This error may occur if the Java transformation does not find the JAR file. Follow the user guide from resources tab. Please try the below steps: Please make sure that the JAR file path should have a jar file name like “C:\Informatica\9.5.1\server\infa_shared\Temp\ concat.jar ”. For compile time: Under settings of Java Transformation, the first classpath should be an absolute path on the server and second should be the absolute path of the client machine. Make sure that file exists in both the locations. However, classpath of the server is optional. For run time: In session properties > Properties tab > Java Classpath > Here, provide the absolute path from server machine. If you still face the same problem, provide the path only from client machine twice.

In the PowerCenter Administration Console, set the following custom properties for the Integration Service Process JVMOption1 = -Xdebug JVMOption2 = -Xrunjdwp:transport=dt_socket,address=8300,server=y,suspend=n Complete the following steps to debug the java code of Java transformations in an Eclipse environment: In Eclipse, create a package named com.informatica.powercenter.server.jtx In Eclipse, create a class named JTXPartitionDriverImplGen In the PowerCenter Designer, copy the source code of the Java transformation you want to debug. To view the source code, go to the Java Code tab of the Java transformation and then select the full code option. Paste the source code into Eclipse. In Eclipse, create a Remote Java Application. To create a remote java application, select the class you created, click debug, right-click on Remote Java Application, and then choose New. Configure the host and port. Note: Configure the port number to be the same as the address parameter you defined in...

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;

1. To know the SQL overrides in mapping Source Qualifier transformation or Lookup Overrides or Pre SQL or post SQL etc Below is the query that takes the workflow name & Folder name as input and gives you all the SQL overrides wherever they are in the workflow. select folder, wf_name,     sess_name, mapping_name,     transformation_name, attr_name,     line_no, sql_value     from (select f.subj_name folder,     wf.task_name wf_name,     sess.instance_name sess_name,     m.mapping_name mapping_name,     w_inst.instance_name transformation_name,     attr.line_no, attr.attr_value sql_value,     attr_type.attr_name attr_name,     row_number() over (partition by wf.task_name,     sess.instance_name,     m.mapping_name,     w_inst.instance_name,     attr.line_no, ...

OPB_SUBJECT - PowerCenter folders table OPB_MAPPING - Mappings table OPB_TASK - Tasks table like sessions, workflow etc TASK_TYPE for session is 68 and that of the workflow is 71. OPB_SESSION - Session & Mapping linkage table OPB_TASK_ATT R - Task attributes tables OPB_WIDGET - Transformations table Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc. OPB_WIDGET_FIEL D - Transformation ports table Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information. OPB_WIDGET_ATTR - Transformation properties table Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means. OPB_EXPRESSION - Expressions table Usa...

–Get list of all workflows that have run since date                                                      select distinct REP_WORKFLOWS.SUBJECT_AREA, REP_WORKFLOWS.WORKFLOW_NAME from dbo.REP_WORKFLOWS join dbo.REP_WFLOW_RUN on REP_WORKFLOWS.WORKFLOW_ID = REP_WFLOW_RUN.WORKFLOW_ID where REP_WFLOW_RUN.END_TIME > ‘date’ order by REP_WORKFLOWS.SUBJECT_AREA asc, REP_WORKFLOWS.WORKFLOW_NAME asc;  –Get list of on demand workflows that have run since date                                            select distinct REP_WORKFLOWS.SUBJECT_AREA, REP_WORKFLOWS.WORKFLOW_NAME, REP_WFLOW_RUN.USER_NAME from dbo.REP...

Informatica Repository management select * from V_PC8X_CONNECTION where CONNECTION_NAME IN (select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME');   If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query. Above query will give details like Connection name, Host name, database type, user name, Connection string and more. Informatica Repository Queries to get connection names, command tasks and event wait file names  Query to get folder name, workflow name, session names and all the connections used in sessions. SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME, T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME, C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME, V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE, C.SESS_EXTN_OBJECT_TYPE FROM REP_TASK_INST T, REP_SESS_WIDGET_CNXS...

-- Assign permissions to relational connections for individual groups/owners SELECT 'assignpermission -o Connection -t Relational -n ',cnx.object_name, USR_GRP_PERM FROM opb_cnx cnx JOIN ( select ' -g "Informatica Administrators" -s SONIC_LDAP -p rwx' USR_GRP_PERM from dual union select ' -g "Informatica Developers" -s SONIC_LDAP -p rx' USR_GRP_PERM  from dual union select ' -g "Informatica Operators" -s SONIC_LDAP -p rx' USR_GRP_PERM  from dual union select ' -g Administrators -p rwx' USR_GRP_PERM  from dual union select ' -g "Automated Runners" -p rx' USR_GRP_PERM  from dual union select ' -u Administrator -p rwx' USR_GRP_PERM  from dual union select ' -g Everyone -p ""' USR_GRP_PERM  from dual union select ' -g Developers -p ""' USR_GRP_PERM  from dual) USR_GRP on 1=1 ORDER BY cnx.object_name,USR_GRP_PERM -- Change owner of relational connections SELEC...

Dynamically generate parameter files I've seen this in many places, but just want to tell you the way I have been doing it for few years now. I will take the context in a bit more detailed manner to make every one understand it. 1. PowerCenter objects – Introduction: • A repository is the highest physical entity of a project in PowerCenter. • A folder is a logical entity in a PowerCenter project. For example, Customer_Data is a folder. • A workflow is synonymous to a set of programs in any other programming language. • A mapping is a single program unit that holds the logical mapping between source and target with required transformations. A mapping will just say a source table by name EMP exists with some structure. A target flat file by name EMP_FF exists with some structure. The mapping doesn’t say in which schema this EMP table exists and in which physical location this EMP_FF table going to be stored. • A session is the physical representation of the ...

Informatica PowerCenter Repository tables I am sure every PowerCenter developer either has an intention or necessity to know about the Informatica metadata tables and where information is stored etc. For the starters, all the objects that we create in Informatica PowerCenter - let them be sources, targets, mappings, workflows, sessions, expressions, be it anything related to PowerCenter, will get stored in a set of database tables (call them as metadata tables or OPB tables or repository tables). * I want to know all the sessions in my folder that are calling some shell script/command in the Post-Session command task. * I want to know how many mappings have transformations that contain "STOCK_CODE" defined as a port. * I want to know all unused ports in my repository of 100 folders. In repositories where you have many number of sessions or workflows or mappings, it gets difficult to achieve this with the help of Informatica PowerCenter client tool...

Below is the query to get relation connection details in Informatica. select * from V_PC8X_CONNECTION where CONNECTION_NAME IN (select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME');   If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query. Above query will give details like Connection name, Host name, database type, user name, Connection string and more. select * from V_PC8X_CONNECTION where CONNECTION_NAME IN (select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME');    If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query. Above query will give details like Connection name, Host name, database type, user name, Connection string and more. Informatica Repository Queries to get connection names, command tasks and event wait file names  Query to ge...

Repository tables Expression query In a mapping with multiple expression transformations and multiple unconnected lookups, it is difficult for you to identify the expressions calling these unconnected lookups. This SQL will give you what you are looking for. select w.instance_name,        g.expression from opb_widget_expr f,      opb_expression g,      opb_widget_inst w,      opb_mapping m where f.expr_id = g.expr_id and f.widget_id = w.widget_id and w.widget_type = 5 and w.mapping_id = m.mapping_id and m.mapping_name = <mapping_name>