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
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]-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_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.