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
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_MAPPINGSWHERE 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_NUMBERFROM
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
CASEWHEN 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') newdateFROM 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] -outputfile>-format>-inputfile>
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.
Hi Aambarish,
ReplyDeleteThanks for your post, could you please also explain/tell the field name explanation, means, run_options means what type of data it contains like that.....
Also, can you please tell me how to find a workflow is scheduled or not?.
Also, ur query 1.5, scheduled query,
yes Bhagya, please refer to 1.4 and 1.5
ReplyDeleteThanks,
Ambarish
Hi Ambarish,
DeleteI need to know.. QUERY for Sources & Targets modfied by user and modified time. Also separately for Session,Mapping,Workflow changed time & chnaged by whom.
Hi Ambarish, Thanks for useful queries!.. However I was looking for a query which would give me details about email ids used in Session Failure/Success Notification. That is "on_success_email" and "on_failure_email" values.
ReplyDeleteThanks
Hi Ambarish,
ReplyDeleteThanks for your reply.
Below query return only default scheduler and not for 'Customized Repeat' scheduler workflows.
1.5 Check Scheduled workflows using queries
SELECT DISTINCT subject_area, workflow_name FROM rep_workflows
WHERE run_options = 8 AND end_options=2 ORDER BY 1;
It is not returning all the scheduler workflows.
Thanks in advance.
Bhagya
Hi Bhagya,
DeletePlease use below values for RUN_OPTIONS & END_OPTIONS based on your requirement.
RUN_OPTIONS
The workflow schedule type. Records the following values for each schedule type:
1 = Run on demand.
2 = Run once.
4 = Run every DELTA_VALUE seconds.
8 = Customized repeat.
16 = Run on Integration Service initialization.
18 = Run on Integration Service initialization and run once.
20 = Run on Integration Service initialization and every DELTA_VALUE seconds.
24 = Run on Integration Service initialization and customized repeat.
32 = Run continuously.
END_OPTIONS
The stop condition option for the workflow schedule type. Records the following values for each stop condition option:
0 = End on a date.
1 = End after the number of runs stored in RUN_COUNT.
2 = Run forever.
Thanks,
Ambarish
Hi Ambarish,
DeleteThanks for your reply.
My requirement is how to identify a workflow is scheduled or not?.
If a workflow is 'Un-scheduled' then how can i identify?.
The above query return the values based on the scheduler type and not return the values are currently it is scheduled or not.
If i am un-schedule a workflow then how can i identify thru queries?.
Thanks,
Bhagya
Nice information, this is will helpfull a lot, Thank for sharing, Keep do posting i like to follow this informatica online training
ReplyDeletefor the Section 14.4 query, i would like to get the PreSql & PostSql details as well. Could you please help me
ReplyDelete
ReplyDeleteNice information, this is will helpfull a lot, Thank for sharing, Keep do posting i like to follow this
informatica online training
mulesoft training
ReplyDeleteweb methods training
business analyst online training
oracle adf online training
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeleteve may bay di my gia re
vé về việt nam từ mỹ
chuyến bay từ đức về hà nội hôm nay
lịch bay từ moscow đến hà nội
giá vé máy bay từ anh về việt nam
các chuyến bay từ châu âu về việt nam
khách sạn cách ly ở nha trang
chi phi ve may bay cho chuyen gia nuoc ngoai
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website mulesoft online training
ReplyDeletebest mulesoft online training
top mulesoft online training