Informatica MDM - Helpful SQL Queries

This article provides the SQL queries which will be helpful for daily usage for monitoring jobs, analyzing data as well as developing custom components.

1. Query to get Landing and Base Object tables

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%'

2. Query to get Column names

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,

NULLABLE, DATA_DEFAULT, SYS_IND, PKEY_IND

FROM CMX_ORS.DBO.C_REPOS_COLUMN

WHERE TABLE_NAME IN (

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%')

3. Query to get jobs status, run, failure

SELECT TABLE_DISPLAY_NAME, (END_RUN_DATE - START_RUN_DATE) AS TOTAL_TIME

, START_RUN_DATE, END_RUN_DATE, STATUS_MESSAGE

, ST.JOB_STATUS_DESC,FT.OBJECT_FUNCTION_TYPE_DESC

FROM CMX_ORS.C_REPOS_JOB_CONTROL JC,

     CMX_ORS.C_REPOS_JOB_STATUS_TYPE ST,

     CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JC.RUN_STATUS = ST.JOB_STATUS_CODE

     AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

4. Query to get match rule set using Match table

select * from cmx_ors.c_repos_match_set where rowid_match_set in (
select rowid_match_Set from cmx_ors.c_repos_match_rule where ROWID_MATCH_RULE in
(select distinct ROWID_MATCH_RULE from cmx_ors.c_bo_party_mtch)
)

5. Query to get match rule set using ROWID_MATCH_RULE

SELECT * FROM CMX_ORS.C_REPOS_MATCH_SET WHERE ROWID_MATCH_SET IN (

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_RULE='SVR1.1V4T6'  

)

6. Query to get ROWID_MATCH_RULE using Match Set and Rule Number

SELECT * FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_SET IN (  

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_SET WHERE MATCH_SET_NAME='IDL'

) AND RULE_NO=1

7. Query to get Match column names using ROWID_MATCH_RULE

select MS.MATCH_SET_NAME, MR.RULE_NO, MC.MATCH_COLUMN_NAME from CMX_ORS.C_REPOS_MATCH_RULE_COMPONENT c,

 CMX_ORS.C_REPOS_MATCH_RULE MR,

 CMX_ORS.C_REPOS_MATCH_COLUMN MC,

 CMX_ORS.C_REPOS_MATCH_SET MS

where c.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE

and c.ROWID_MATCH_COLUMN = MC.ROWID_MATCH_COLUMN

and MR.ROWID_MATCH_SET = MS.ROWID_MATCH_SET

and MR.ROWID_MATCH_RULE like  '%SVR1.ABC%'

8. Query to get a count of records processed for each job

SELECT JM.CREATE_DATE, FT.OBJECT_FUNCTION_TYPE_DESC, JC.TABLE_DISPLAY_NAME, MT.METRIC_TYPE_DESC, JM.METRIC_VALUE AS COUNT

FROM CMX_ORS.C_REPOS_JOB_METRIC JM,

CMX_ORS.C_REPOS_JOB_METRIC_TYPE MT,

CMX_ORS.C_REPOS_JOB_CONTROL JC,

CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JM.METRIC_TYPE_CODE = MT.METRIC_TYPE_CODE

AND JM.ROWID_JOB = JC.ROWID_JOB

AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

9. How to check Timeline Granularity

SELECT TIMELINE_GRANULARITY FROM C_REPOS_DB_RELEASE; 

Value	Type of Granularity
0	Seconds
1	Minutes
2	Hours
3	Days
4	Months
5	Years

Informatica Business Entity Services Overview

Are you looking for an article about then Informatica business entity services? Are you interested in knowing what is the business entity services? How to identify a route record in the business entity services and how to use the security and data filters? If so, then you have reached the correct place. In this article, we are going to learn business entity services and the operations involved and more details about this business entity services.

What is the business entity service? 

A business entity service is a set of operation that runs in the Informatica MDM hub in order to create , update , delete and search the records from the base object which act as a business entity. We can develop a custom user interface which can run Java code or javascript to make the business entity service call.

What are the operations involved in the business entity service?

The operations involved in the business services are - read operation, write operation and search operation.

• By using read operation we can read a record from the business entity. 
• By using the write operation we can write a record in the database for a business entity. 
• One other hand by using search functionality we can use the searchable field to perform the search operation.

What are the business entity service Endpoints?

There are 4 types of business entity service endpoints.

1. Enterprise JavaBeans Endpoint
2. REST Endpoint 
3. REST and EJB Endpoint
4. SOAP Endpoint

How to identify a route record in the business entity service?

We can identify a route record in the business entity by 3 ways

1. By using the Rowid. It is the Rowid object column value for the Base Object record
2. By using system name and Source Key. here the system name is the name of the system from which record is coming and Source Key is PKEY_SRC_OBJECT column value
3. By using Global Business Identifier aka GBID. If GBID is compound value then we need to pass compound column values.

How to apply security and Data filters to Business Entity Service?

The user role privileges for base object and resources are get inherited in the business entity. It also inherits any data filters that you set on business entity fields.

Top 12 Interesting features of Java 10

Would you be interested in knowing what are the new interesting features in Java 10? Would you also like to know Application Class Data Sharing, Java JIT Compiler, Time based release? If so, then you reached the right place. In this article, we will understand new features in Java 10 language.

Java 10 features

Java 10 is the fastest feature release of a Java SE platform. Features contain various enhancements into many functional areas such as garbage collection and compilation as well as local variable types.

ü  Local-Variable Type Inference

ü  Application Class-Data Sharing

ü  Consolidate the JDK Forest into a Single Repository

ü  Garbage-Collector Interface

ü  Parallel Full GC for G1

ü  Thread-Local Handshakes

ü  Remove the Native-Header Generation Tool (javah)

ü  Additional Unicode Language-Tag Extensions

ü  Heap Allocation on Alternative Memory Devices

ü  Experimental Java-Based JIT Compiler

ü  Root Certificates

ü  Time-Based Release Versioning

1. Local Variable Type Inference

Java now allows var style declarations. We can declare a local variable without specifying its type. The type will be inferred from context i.e from the type of actual object created.

For eg.

var str = “Welcome to Java 10";

  

//or

  

String str = " Welcome to Java 10";

In first the statement, type of str is determined by the type of assignment which of String type.

2. Application Data-Class Sharing:

The main goal of this feature is to improve startup and footprint, extend the existing Class-Data Sharing ("CDS") feature to allow application classes to be placed in the shared archive.

Goals:

-It reduces the footprint by sharing common class metadata across different Java processes.

-Improves start-up time.

-Application Class-Data Sharing allows the built-in system class loader, the built-in platform class loader, and custom class loaders to load archived classes.

3. Consolidate the JDK Forest into a Single Repository.

 This feature is all about housekeeping. It combines the numerous repositories of the JDK forest into a single repository to simplify the development.

4. Garbage-Collector Interface.

 It introduces common Garbage Collector Interface, by using this we can improve the code isolation. It allows alternative collectors to be quickly and easily integrated. The main goal is to provide better modularity for HotSpot internal GC code.

5. Parallel Full GC for G1.

This feature of Java 10 improves G1 worst-case latencies by making the full GC parallel.

The current implementation of the full GC for G1 uses a single-threaded mark-sweep-compact algorithm.

6. Thread-Local Handshakes.

It improves performance. While the java thread is in a savepoint safe state, a handshake operation is executed for each Java Thread. While keeping the thread in a blocked state the callback is executed either by the thread itself or by the VM thread.

7. Remove the Native-Header Generation Tool (javah)

It focuses on housekeeping. This feature removes javah tool from JDK. This practicality provides the flexibility to put in writing native header files at the time that Java source code is compiled, thereby eliminating the necessity for a separate tool.

8. Additional Unicode Language-Tag Extensions.

This feature enhances java.util.Locale and related APIs to implement extra Unicode extensions of BCP 47 language tags. This JEP will implement a lot of the extensions laid out in the newest LDML specification, within the relevant JDK classes.

This feature will add support for the following additional extensions:

                                i.            cu (currency type)

                              ii.            fw (first day of the week)

                            iii.            rg (region override)

                            iv.            tz (time zone)

9. Heap Allocation on Alternative Memory Devices

This feature enhances the potential of HotSpot VM to portion the Java object heap on an alternate device, like NV-DIMM, nominative by the user.

For example, with this feature, it is possible to assign lower priority processes to use the NV-DIMM memory, and instead, only allocate the processes which have a higher priority to the DRAM in a multi-JVM environment.

10. Experimental Java-Based JIT Compiler

It enables Graal, to be used as an experimental JIT compiler on the Linux/x64 platform. Graal is basically a new JIT compiler for java, which is the basis of Ahead-of-Time (AOT) compiler.

11. Root Certificates

This feature provides root Certification Authority (CA) certificates in the JDK.

This helps to promote OpenJDK and make it more effective to community users. The aim of this feature is to reduce the difference between the OpenJDK and Oracle JDK builds.

12. Time-Based Release Versioning

Unlike the old releases, the new time-based releases won’t be delayed and features will be released every six months. There are also Long Term Releases (LTS). It is mainly for enterprise customers.

What are the differences between IDD Data View and IDD Business Entity applications

Would you like to know what are differences between Legacy IDD and Entity 360 or   Entity application? Are you also interested in knowing what are the open issues with Legacy IDD and Entity 360 application? If so, then you reached the right place. In this article, we will have a detailed discussion about the differences and features of both the applications. These differences are captured based on Informatica MDM 10.3 HF1.

Category	IDD Data View	IDD Business Entity
Customization	Use of IDD User Exits 1.     Integral part of IDD Configuration         2.    Easy to implement customization         3.    Easy to deploy as a component of IDD         4.    No separate resource configuration required, resources allocated to IDD will be used for User Exist        5.    Error handling follows MDM standard practice, no additional handling is required          6.    No additional security required as it integral part of IDD Application	No User Exit support         1.    Need to write external services (Restful or SOAP-based Web Services)        2.     Required additional efforts to build and implement and deploy these external services         3.    For scalability, high availability of external services, additional dedicated servers are required         4.    Need to apply and maintain security  as these are external services to IDD Business entity          5.    Extra error handling is required to follow MDM standard practice       6.    Extra the configuration is required to call external services          7.    Dedicated resources need to be allocated to handle user requests
Data Import template	IDD Data View provides the feature to import data. It is a very helpful tool when business would like to import bulk data in need basic. No need to create or update requires manually	Do not support Bulk import template. Need to create or update bulk volume of data manually
Unmerge functionality	It supports both Tree unmerge and linear unmerge. Note: During Tree unmerge unmerged unmerge records get separated from the group. During linear unmerge children records of unmerged record remain associated.	Supports the only Tree unmerge
Report	Easy to integrate repots in the IDD application using Jasper Reports	Jaspersoft reports work in a Home page only if it is the only component in the Home page.
Workflow	If IDD application includes workflows, we must generate the business entity schema as a requirement for Data Director to manage the workflow tasks. However, we need to migrate to business entities	The business entity the schema will be generated as part of Business Entity application publish event using the Provisioning Tool.
Both Entity and IDD Data Views- Hybrid mode	Informatica recommends that the Hybrid mode only on a temporary basis
Manual Override of matched record	Manual override  of value in the Matches view is allowed	Manual override a value in the Matching Records view is not allowed
Hierarchy View	Hierarchy relationships can be configured to show in a section to show duplicate hierarchy records.	The Hierarchy view does NOT permit the following actions:-      ·         Finding a duplicate entity.      ·         Initiating a merge.      ·         Sharing a bookmark URL
Limitations	1.       In the task inbox on the Home page, you cannot filter tasks by the creation date.      2.       When you export search results that are based on a timeline, the export process ignores the timeline and exports all data.	1.       The Cross Reference page and the Merge Preview page have pagination issues.      2.       In the search results, some rows are empty. The rows represent records that are filtered out because the user does not have permission to view the records.      3.       When a user role does not include the create and read privileges for a business entity, users with this role can still view the tasks associated with the business entity.      4.       In the History view, the timescale labels in the Options menu do not appear correctly initially.      5.       In the Hierarchy view, business entities in the Relationships tab of the history do not open in Business Entity view.      6.       In the Timeline view, you cannot open the relationship records that appear on the Relationships tab.      7.       In the Hierarchy view, in the Entity Details dialog box, when you click More Details, the dialog box closes without opening the selected business entity.      8.       In the Matching Records view, when you merge records, the system can appear unresponsive.      9.       If you delete a record and then search for the record, the ROWID of the deleted record still displays.      10.   In the History view, when you try to view event detail, an error might occur.