Informatica Metadata Queries - 1 DOMAIN & REPOSITORY

1.1         Check Master Domain Election of Node (on Multi Node Setup)

Select * from ISP_MASTER_ELECT_LOCK

1.2         Check Master Selection of Host, Node and Port Details (Domain tables)

Select * from ISP_MASTER_ELECTION  

1.3         Repository Information

SELECT PCSF_DOMAIN AS DOMAIN, REPOSITORY_NAME, DB_USER FROM OPB_REPOSIT_INFO

1.4         List Service Names, Grid, License Name and Service Type (Domain tables)

SELECT

PSD_DISCRIMINATOR AS SERVICE_TYPE,

POS_NAME AS SERVICE_NAME,

POD_GRIDNAME AS GRIDNAME,

POD_LICENSENAME AS LICENSENAME,

POD_SERVICETYPE AS SERVICETYPE,

POD_SERVICEVERSION AS SERVICEVERSION

FROM PO_DOMAINOBJ

ORDER BY 1,2

  

1.5         List of “Not Valid” objects in a Repository

SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,    WORKLET_OR_SESSION,IS_VALID

FROM

(SELECT DISTINCT         

OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,

OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,

DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,

OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,

DECODE (OPB_TASK_INST.IS_VALID,1,'VALID','NOT VALID') AS IS_VALID

FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO

WHERE OPB_TASK_INST.TASK_TYPE != 62

AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID

AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER

AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

AND OPB_TASK.UTC_CHECKIN <> 0  )

WHERE IS_VALID = 'NOT VALID'

ORDER BY 2,3

1.6         Check Reposit DB User and Type

SELECT

REPOSITORY_ID,

REPOSITORY_NAME,

DATABASE_USER,

DECODE (REPOSITORY_TYPE,1,'GLOBAL',2,'STANDALONE',3,'LOCAL') REPOSITORY_TYPE

FROM

REP_REPOSIT_INFO

order by 2

1.7         List where all a table is used

Sometimes you want to know if certain tables are listed in sql overrides of Source Qualifier or Lookup transformation. This helps you identifying dependencies. The query below will list folder, attribute type and sql override as output.

SELECT DISTINCT REP_ALL_MAPPINGS.SUBJECT_AREA, REP_ALL_MAPPINGS.MAPPING_NAME,REP_WIDGET_ATTR.ATTR_NAME, REP_WIDGET_ATTR.ATTR_VALUE

FROM REP_WIDGET_ATTR, REP_WIDGET_INST, REP_ALL_MAPPINGS

WHERE REP_WIDGET_ATTR.WIDGET_ID = REP_WIDGET_INST.WIDGET_ID

AND REP_WIDGET_INST.MAPPING_ID = REP_ALL_MAPPINGS.MAPPING_ID

AND REP_WIDGET_ATTR.WIDGET_TYPE IN (3,11)

AND REP_WIDGET_ATTR.ATTR_ID = 1

AND REP_WIDGET_ATTR.ATTR_VALUE LIKE '%' || REPLACE('TABLE_NAME', '_', '/_') || '%' ESCAPE '/'

ORDER BY 1,2,3

1.8         List of all Jobs which are failed in last 5 days

SELECT Subject_Area AS Folder,

Session_Name,

Last_Error AS Error_Message,

DECODE (Run_Status_Code,3,'Failed',4,'Stopped',5,'Aborted') AS Status,

Actual_Start AS Start_Time,

Session_TimeStamp

FROM rep_sess_log

WHERE run_status_code != 1

AND TRUNC(Actual_Start) BETWEEN TRUNC(SYSDATE -5) AND TRUNC(SYSDATE)

order by 1,2

1.9         List All Integration Service Names available in Repository

SELECT Domain_Name,Server_Name as IntegrationService_Name FROM OPB_SERVER_INFO

order by 2

1.10      List comments of all objects

SELECT

B.SUBJECT_AREA AS FOLDER_NAME, A.OBJECT_NAME,A.COMMENTS,  A.VERSION_NUMBER

FROM

REP_VERSION_PROPS A, REP_SUBJECT B

WHERE B.SUBJECT_ID = A.SUBJECT_ID

AND A.COMMENTS IS NOT NULL

ORDER BY 1,2

1.11      List of Database used in Repository

The database definition view provides a list of all database definitions in the repository. A database definition includes the source database names, flat file or RDBMS, and the folder where the database definition resides.

select subject_area,database_name,def_source from REP_DATABASE_DEFS

order by 1,2,3

1.12      Query to check timestamp of objects last saved by user

SELECT

CASE

WHEN opb_version_props.object_type = 1 THEN 'Source Definition'                                 ELSE CASE

WHEN opb_version_props.object_type = 2 THEN 'Target Definition'                                   ELSE CASE

WHEN opb_version_props.object_type = 3 THEN 'Source Qualifier'                                    ELSE CASE

WHEN opb_version_props.object_type = 4 THEN 'Update Strategy'                                    ELSE CASE

WHEN opb_version_props.object_type = 5 THEN 'Expression'                                            ELSE CASE

WHEN opb_version_props.object_type = 6 THEN 'Stored Procedure'                                ELSE CASE

WHEN opb_version_props.object_type = 7 THEN 'Sequence'                           ELSE CASE

WHEN opb_version_props.object_type = 8 THEN 'External Procedure'                              ELSE CASE

WHEN opb_version_props.object_type = 9 THEN 'Aggregator'                                           ELSE CASE

WHEN opb_version_props.object_type = 10 THEN 'Filter'                                                                          ELSE CASE

WHEN opb_version_props.object_type = 11 THEN 'Lookup Procedure'                            ELSE CASE

WHEN opb_version_props.object_type = 12 THEN 'Joiner'                                                          ELSE CASE

WHEN opb_version_props.object_type = 13 THEN 'Procedure'                                                ELSE CASE

WHEN opb_version_props.object_type = 14 THEN 'Normalizer'                                               ELSE CASE

WHEN opb_version_props.object_type = 16 THEN 'Merger'                                                        ELSE CASE

WHEN opb_version_props.object_type = 17 THEN 'Pivot'                                                             ELSE CASE

WHEN opb_version_props.object_type = 18 THEN 'Session Obsolete'                                ELSE CASE

WHEN opb_version_props.object_type = 19 THEN 'Batch'                                                           ELSE CASE

WHEN opb_version_props.object_type = 20 THEN 'Shortcut'                             ELSE CASE

WHEN opb_version_props.object_type = 21 THEN 'Mapping'                             ELSE CASE

WHEN opb_version_props.object_type = 26 THEN 'Rank'                                                              ELSE CASE

WHEN opb_version_props.object_type = 27 THEN 'Star Schema'                                            ELSE CASE

WHEN opb_version_props.object_type = 28 THEN 'Folder Version'                                       ELSE CASE

WHEN opb_version_props.object_type = 29 THEN 'Folder'                                                          ELSE CASE

WHEN opb_version_props.object_type = 30 THEN 'Cube'                                                             ELSE CASE

WHEN opb_version_props.object_type = 31 THEN 'Dimension'                                                ELSE CASE

WHEN opb_version_props.object_type = 32 THEN 'Level'                                                            ELSE CASE

WHEN opb_version_props.object_type = 33 THEN 'Hierarchy'                                                  ELSE CASE

WHEN opb_version_props.object_type = 34 THEN 'Fact Table'                                                 ELSE CASE

WHEN opb_version_props.object_type = 35 THEN 'General Object'                                    ELSE CASE

WHEN opb_version_props.object_type = 36 THEN 'FTP Object'                                                ELSE CASE

WHEN opb_version_props.object_type = 37 THEN 'Oracle External Loader Object'  ELSE CASE

WHEN opb_version_props.object_type = 38 THEN 'Informix External Loader Object'                       ELSE CASE

WHEN opb_version_props.object_type = 39 THEN 'Sybase IQ External Loader Object'                    ELSE CASE

WHEN opb_version_props.object_type = 54 THEN 'Sybase IQ 12 External Loader Object'            ELSE CASE

WHEN opb_version_props.object_type = 53 THEN 'Tera Data External Loader Object'                    ELSE CASE

WHEN opb_version_props.object_type = 40 THEN 'File Object'                                               ELSE CASE

WHEN opb_version_props.object_type = 41 THEN 'Server Object'                                        ELSE CASE

WHEN opb_version_props.object_type = 42 THEN 'Database Object'                                  ELSE CASE

WHEN opb_version_props.object_type = 43 THEN 'Repository'                                                ELSE CASE

WHEN opb_version_props.object_type = 44 THEN 'Mapplet'                             ELSE CASE

WHEN opb_version_props.object_type = 45 THEN 'Application Source Qualifier'       ELSE CASE

WHEN opb_version_props.object_type = 46 THEN 'Input Transformation'                                               ELSE CASE

WHEN opb_version_props.object_type = 47 THEN 'Output Transformation'                                           ELSE CASE

WHEN opb_version_props.object_type = 50 THEN 'Advanced External Procedure'   ELSE CASE

WHEN opb_version_props.object_type = 48 THEN 'Business Component Framework'                    ELSE CASE

WHEN opb_version_props.object_type = 49 THEN 'Business Component'                                               ELSE CASE

WHEN opb_version_props.object_type = 51 THEN 'SAP Structure'                                       ELSE CASE

WHEN opb_version_props.object_type = 52 THEN 'SAP Function'                                        ELSE CASE

WHEN opb_version_props.object_type = 15 THEN 'Router'                                                        ELSE CASE

WHEN opb_version_props.object_type = 55 THEN 'XML Source Qualifier'                                               ELSE CASE

WHEN opb_version_props.object_type = 56 THEN 'MQ Source Qualifier'                                                 ELSE CASE

WHEN opb_version_props.object_type = 57 THEN 'MQ Connection Object'                                          ELSE CASE

WHEN opb_version_props.object_type = 58 THEN 'Command'                                                ELSE CASE

WHEN opb_version_props.object_type = 59 THEN 'Decision'                            ELSE CASE

WHEN opb_version_props.object_type = 60 THEN 'Event Wait'                                               ELSE CASE

WHEN opb_version_props.object_type = 61 THEN 'Event Raise'                                              ELSE CASE

WHEN opb_version_props.object_type = 62 THEN 'Start'                                                             ELSE CASE

WHEN opb_version_props.object_type = 63 THEN 'Abort'                                                           ELSE CASE

WHEN opb_version_props.object_type = 64 THEN 'Stop'                                                             ELSE CASE

WHEN opb_version_props.object_type = 65 THEN 'Email'                                                           ELSE CASE

WHEN opb_version_props.object_type = 66 THEN 'Timer'                                                          ELSE CASE

WHEN opb_version_props.object_type = 67 THEN 'Assignment'                                             ELSE CASE

WHEN opb_version_props.object_type = 68 THEN 'Session'                                                       ELSE CASE

WHEN opb_version_props.object_type = 69 THEN 'Scheduler'                                                ELSE CASE

WHEN opb_version_props.object_type = 70 THEN 'Worklet'                             ELSE CASE

WHEN opb_version_props.object_type = 71 THEN 'Workflow'                                                 ELSE CASE

WHEN opb_version_props.object_type = 72 THEN 'SessionConfig'                                        ELSE CASE

WHEN opb_version_props.object_type = 73 THEN 'Relational'                                                ELSE CASE

WHEN opb_version_props.object_type = 74 THEN 'Application'                                             ELSE CASE

WHEN opb_version_props.object_type = 75 THEN 'FTP'                                                                ELSE CASE

WHEN opb_version_props.object_type = 76 THEN 'External Loader'                                   ELSE CASE

WHEN opb_version_props.object_type = 77 THEN 'Queue'                                                        ELSE CASE

WHEN opb_version_props.object_type = 78 THEN 'Reader'                                                       ELSE CASE

WHEN opb_version_props.object_type = 79 THEN 'Writer'                                                        ELSE CASE

WHEN opb_version_props.object_type = 80 THEN 'Sorter'                                                         ELSE CASE

WHEN opb_version_props.object_type = 81 THEN 'Vendor'                                                       ELSE CASE

WHEN opb_version_props.object_type = 84 THEN 'App Multi-Group Source Qualifier'                  ELSE CASE

WHEN opb_version_props.object_type = 91 THEN 'Control'                                                      ELSE CASE

WHEN opb_version_props.object_type = 92 THEN 'Transaction Control'                          ELSE CASE

WHEN opb_version_props.object_type = 97 THEN 'Custom Transformation'                                          ELSE CASE

WHEN opb_version_props.object_type = 93 THEN 'Query'                                                          ELSE CASE

WHEN opb_version_props.object_type = 94 THEN 'Deployment Group'                           ELSE CASE

WHEN opb_version_props.object_type = 95 THEN 'Label'                                                           ELSE CASE

WHEN opb_version_props.object_type = 96 THEN 'Deployed Deployment Group'   ELSE CASE

WHEN opb_version_props.object_type = 98 THEN 'Server Grid'                                             ELSE CASE

WHEN opb_version_props.object_type = 99 THEN 'Profiling Ruleset'                                 ELSE CASE

WHEN opb_version_props.object_type = 100 THEN 'Template Extension'                                               ELSE CASE

WHEN opb_version_props.object_type = 101 THEN 'Global Profile Resource'                                      ELSE CASE

WHEN opb_version_props.object_type = 102 THEN 'Web Services Hub'                          ELSE CASE

WHEN opb_version_props.object_type = 103 THEN 'Lookup Extension'                            ELSE CASE

WHEN opb_version_props.object_type = 105 THEN 'Service Level'                                      ELSE CASE

WHEN opb_version_props.object_type = 106 THEN 'User Defined Function' ELSE 'Shortcut'

   END          END               END               END               END               END               END               END               END               END               END               END               END               END               END                        END               END               END               END               END              END               END               END               END               END               END               END               END               END                        END               END               END               END               END               END               END               END               END               END               END               END               END               END                        END               END               END               END               END               END               END               END               END               END               END               END               END               END                        END               END               END               END               END               END               END               END               END               END               END               END               END               END                        END               END               END               END               END               END               END               END               END               END               END               END               END               END                        END               END               END               END               END               END               END

END "OBJECT_TYPE",object_name,

REP_USERS.USER_NAME,

SUBSTR(opb_version_props.LAST_SAVED,1,10) LAST_SAVED

FROM REP_USERS,OPB_VERSION_PROPS,REP_SUBJECT

WHERE REP_USERS.USER_ID=opb_version_props.USER_ID

AND REP_SUBJECT.SUBJECT_ID = opb_version_props.SUBJECT_ID

and REP_USERS.status = 0

and  object_type = 1

ORDER BY 1,2,3,4

1.13      List the name of the object, type, date and last saved

SELECT c.subj_name, a.object_name, b.object_type_name,

         TO_DATE (a.last_saved, 'mm/dd/yyyyHH24:mi:ss') newdate

    FROM opb_version_props a, opb_object_type b, opb_subject c

   WHERE a.object_type = b.object_type_id

     AND a.subject_id = c.subj_id

     AND TO_DATE (a.last_saved, 'mm/dd/yyyyHH24:mi:ss') >

                       TO_DATE ('04/22/2012 00:00:00', 'mm/dd/yyyyHH24:mi:ss')

ORDER BY newdate DESC;

1.14      List Folder,wf,sess,maping,src and trg

SELECT

F.SUBJ_NAME AS FOLDER_NAME,

WF.TASK_NAME AS WORKFLOW_NAME,

SE.INSTANCE_NAME AS SESSION_NAME,

M.MAPPING_NAME,

SRC.INSTANCE_NAME AS SOURCE_NAME,

TGT.INSTANCE_NAME AS TARGET_NAME

FROM

OPB_SUBJECT F,

OPB_TASK WF,

(SELECT

WORKFLOW_ID,

INSTANCE_ID,

TASK_ID,

TASK_TYPE,

INSTANCE_NAME,

MAX(VERSION_NUMBER)

FROM OPB_TASK_INST SESS

WHERE

SESS.TASK_TYPE=68

GROUP BY

WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,

(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,

OPB_MAPPING M,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) FROM OPB_WIDGET_INST GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) FROM OPB_WIDGET_INST GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT

WHERE WF.IS_VISIBLE = 1

AND WF.SUBJECT_ID = F.SUBJ_ID

AND SE.WORKFLOW_ID = WF.TASK_ID

AND WF.TASK_TYPE = 71

AND SE.TASK_ID = S.SESSION_ID

AND S.MAPPING_ID = M.MAPPING_ID

AND M.IS_VISIBLE = 1

AND SRC.MAPPING_ID=M.MAPPING_ID

AND SRC.WIDGET_TYPE=1

AND TGT.MAPPING_ID=M.MAPPING_ID

AND TGT.WIDGET_TYPE=2

AND F.SUBJ_NAME = 'ABC'

1.15      How to Remove Node

Go to the path: 

/opt/infa/svc-ide/Informatica/9.1.0/isp/bin

Execute the command:

./infacmd.sh removeNode

1.16      How to convert binary log to text format

Please use the below command if you need to convert binary session/workflow.. log file to text/XML format for your analysis.

(Since most of the jobs not having write backward compatible option enabled the logs are generated in binary format by default)

Syntax:

Infacmd.sh ConvertLogFile <-inputfile in=""> input_file_name [<-format fm=""> format_TEXT_XML] [<-outputfile lo=""> output_file_name]

Example:

/opt/infa/svc-infa/Informatica/9.1.0/server/bin

infacmd.sh Convertlogfile -in /opt/infa/svc-infa/Informatica/9.1.0/server/infa_shared/Logs/s_m_STG_SAP_PRPS_NA_1_60.log.6.bin -fm text -lo /opt/infa/svc-infa/Informatica/9.1.0/server/infa_shared/Logs/s_m_STG_SAP_PRPS_NA_1_60.log

1.17      PMSTACK

Go to /server/bin directory

2. Run the following command on the core file.

    /tools/debugtools/pmstack -c /server/bin/

1.18      Purging & Archiving DX Events in DX Repository

Proposed Solution:

1. When you run the archive script - based on the condition provided in the purge.xml file - the action takes place.

·   If you have choose both archive and purge =true and  =true - the data is backed up and purged 

·   if you choose - archive and no purge =true and  =false- the data is backed up

·   if you choose-no archive and purge =false and  =true - the data is purged completely from the DX repository.

2. The purge event will not be stored. It is deleting the data completely from the DX repository.
Only if you archive - the data is backed up.

3. 

Archive Rule One
(ALL)
c:\temp\archive\all

10d 0h 0m
true
true
false


When you run the archive script with the above xml - then all teh events which are older than 10 days will be archived (backedup) in the location - c:\temp\archive\all - as a zip file and then those events will be purged from database.



System Events
(SYSTEM)
c:\temp\archive\system
10d 00h 00m
false
true
true

When you run the archive script with the above xml - then all the events which are older than 10 days will be purged from database as you have set the  = false
 

Additional Info: Since you are using PC 9.6.1 - you can use ILM archive method to archive the events - this is faster than script.