INFORMATICA REPOSITORY QUERIES – PART IV


INFORMATICA REPOSITORY QUERIES – PART I

INFORMATICA REPOSITORY QUERIES – PART II

14                         
REPOSITORY

14.1          
Repository
Info

SELECT DOMAIN_NAME,
REPOSITORY_NAME,PCSF_DOMAIN AS DOMAIN, DB_USER FROM OPB_REPOSIT_INFO

 

14.2          
List
of objects which are Not Valid

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

14.3          
List
of objects 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

14.4          
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
 

14.5          
List
all source and target tables of mapping

 

SELECT
DISTINCT SUBJECT_AREA,SOURCE_NAME,TARGET_NAME,MAPPING_NAME FROM REP_TBL_MAPPING

ORDER
BY 1,2,3,4

 

14.6          
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

 

14.7          
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

 

 

15  
MISLENIOUS

 

15.1          
Query
to find list 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

 

 

 

15.2          
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;

 

15.3          
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’

 

 

 

 

15.4          
List
Locks of objects in reposit

 

 

SELECT SUBJ_NAME FOLDER,

      
CNX_ID,

      
DECODE (LOCK_TYPE,

              
1, ‘NodeInUseLock’,

              
2, ‘NodeWriteIntentLock’,

              
4, ‘ExecuteLock’,

              
5, ‘SharedLock’,

              
6, ‘ExclusiveLock’,

              
7, ‘SubTreeSharedLoc’,

              
8, ‘SubTreeExclusiveLock’,

              
9, ‘SubTreeISharedLock’,

              
10, ‘SubTreeIExclusiveLock’,

              
11, ‘SubTreeWriteIntentLock’,

              
12, ‘SubTreeIWriteIntentLock’)

         
LOCK_TYPE

  FROM OPB_OBJECT_LOCKS L, OPB_SUBJECT S

WHERE L.SUBJECT_ID = SUBJ_ID

and lock_type = 4

and SUBJ_NAME  = ‘EBI_EnterpriseDataWarehouseCoreCRMDashboard_1_41’

 

 

 

 

 

 

15.5          
How
to Remove Node

 

Go to the
path: 

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

Execute the
command:

./infacmd.sh
removeNode

 

15.6          
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_60.log.6.bin
-fm text -lo /opt/infa/svc-infa/Informatica/9.1.0/server/infa_shared/Logs/s_m_STG_60.log

 

15.7          
PMSTACK

Go
to /server/bin directory

 
2.
Run the following command on the core file.

 
  
 /tools/debugtools/pmstack -c
/server/bin/
About
Legal
Trending Topics
  • Facebook
  • Instagram
  • LinkedIn
  • X Twitter
  • YouTube
© 2024 ETL Tech Blog. All rights reserved.