Informatica MDM - Hub Store, Import Meta data and Operation Reference store set-up

Ready to set up Informatica MDM? Let's dive into creating the Hub Store (CMX_SYSTEM), importing metadata, and installing the Operational Reference Store (CMX_ORS).

Environment Details:

Operating System:- windows
Database Server**:- MS SQL Server 
App Server:- Jboss
Informatica:- Multi-Domain MDM 10.4 HF2

**Note:- Below steps will be similar to Oracle database as well.

Please modify the below attributes high-lighted according to your environment set-up.

Steps for Hub Store(CMX_SYSTEM) Installation:-

F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin>sip_ant.bat create_system
--------------------------------------------------------------

Current folder : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\
Current script : sip_ant.bat
Parameters : create_system
Sipeian Home : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\
Java Home : C:\Program Files\Java\jre1.8.0_202
Classpath : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\hashing\bcprov-jdk15on-1.54.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xml-apis.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ojdbc7.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xercesImpl.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\log4j-1.2.16.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\siperian-common.jar;C:\Program Files\Java\jre1.8.0_202\lib\rt.jar;C:\Program Files\Java\jre1.8.0_202\lib\i18n.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant-apache-log4j.jar;

extract_target_build_number:
     [echo] extracting build number...
     [unzip] Expanding: F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\lib\ProductVersion.jar into F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin
     [delete] Deleting directory F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\resources 
	 [echo] Target build number is 10.4.2.0.0.0.10684905 
	 
defenvironment: 
     [echo] Define the target database type  
	 [propertyprompt] Enter database type (ORACLE,MSSQL,DB2) [] : MSSQL

create_system: 
    [echo] 
	[echo] ===============================================
    [echo] CREATE_MASTER start: 2021 November 19 08:39:43 

mssqldb.info:
    [propertyprompt]Enter Master database name: [cmx_system] :cmx_system
	[propertyprompt]Enter DB hostname: [localhost] : <Provide hostname>
	[propertyprompt]Enter DB port number: [1433] :1433 
	
	db.create.sys.info:
	[propertyprompt]Enter CMX_SYSTEM Database path. ["C:\MSSQLDATA"] : "R:\USERDB\DATA1\MSSQLDATA" </Provide>
    [propertyprompt]Enter CMX_SYSTEM DB collation name. [Latin1_General_CI_AS] :

create_system:

 [propertyprompt]Enter Master DB user password [] : <Setup cmx_system  password>
 [propertyprompt]Enter DBA username [sa] : <Provide user having DBA privelages>
 [propertyprompt]Enter DBA password [] :<Provide DBA password>

[echo] DB short path:  R:\USERDB\DATA1\MSSQLDATA
[RunSQLCMD] ------------------------------------------------------
[RunSQLCMD] Script Directory : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\resources\database\mssql
[RunSQLCMD] --------------------------------------------------------
[RunSQLCMD]  Server : <host name>
[RunSQLCMD]  Port : 1433
[RunSQLCMD]  Database : cmx_system
[RunSQLCMD]  User : <User with DBA privelages>
[RunSQLCMD]  Number of files : 1
[RunSQLCMD] --------------------------------------------------------
[RunSQLCMD] [1] Running create_cmx_db.sql ...
[RunSQLCMD] Changed database context to 'master'.
[RunSQLCMD] START TO DROP DB
[RunSQLCMD] Changed database context to 'master'.
[RunSQLCMD] Changed database context to 'master'.
[RunSQLCMD] Changed database context to 'master'.
[RunSQLCMD] MSSQL DB cmx_system READ_COMMITTED_SNAPSHOT ON
[RunSQLCMD] Changed database context to 'cmx_system'.
[RunSQLCMD] Changed database context to 'master'.
     [echo] CREATE_MASTER  end: 2021 November 19 08:41:35

BUILD SUCCESSFUL

Total time: 1 minute 52 seconds

SCRIPT FINISHED

F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin>


Steps for Operational Reference store(CMX_ORS) Installation:-

F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin>sip_ant.bat create_ors

--------------------------------------------------------------------

Current folder : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\
Current script : sip_ant.bat
Parameters : create_ors
Sipeian Home : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\
Java Home : C:\Program Files\Java\jre1.8.0_202

Classpath : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\hashing\bcprov-jdk15on-1.54.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant.jar;F:Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xml-apis.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ojdbc7.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xercesImpl.jar;F:Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\log4j-1.2.16.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\siperian-common.jar;C:\Program Files\Java\jre1.8.0_202\lib\rt.jar;C:\Program Files\Java\jre1.8.0_202\lib\i18n.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant-apache-log4j.jar;

extract_target_build_number:

[echo] extracting build number...

[unzip] Expanding: F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\lib\ProductVersion.jar into F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin

[delete] Deleting directory F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\resources
[echo] Target build number is 10.4.2.0.0.0.10684905

defenvironment:

[echo] Define the target database type

[propertyprompt] Enter database type (ORACLE,MSSQL,DB2) [] :MSSQL

create_ors:
     [echo]
     [echo] =======================================================
     [echo] CREATE_ORS  start: 2021 November 19 08:46:07

db.ors.info:

[propertyprompt] Enter the Operational Reference Store DB hostname. [localhost] : <host name>
[propertyprompt] Enter the Operational Reference Store DB port number. [1433] :1433
[propertyprompt] Enter the Operational Reference Store database name. [cmx_ors] : cmx_ors

db.create.ors.info:

[propertyprompt] Enter CMX_ORS Database path. ["C:\MSSQLDATA"] :"R:\USERDB\DATA1\MSSQLDATA"
[propertyprompt] Enter CMX_ORS DB collation name. [Latin1_General_CI_AS] :

create_ors:

[propertyprompt] Enter the Operational Reference Store DB user password. [] :<set-up cmx_ors password here>
[propertyprompt] Enter the DBA username. [sa] :<Provide user with DBA privilages>
[propertyprompt] Enter the DBA password. [] :<Password for above user>
[echo] DB short path:  R:\USERDB\DATA1\MSSQLDATA
[RunSQLCMD] -----------------------------------------------------------
[RunSQLCMD] Script Directory : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\resources\database\mssql
[RunSQLCMD] -----------------------------------------------------------
[RunSQLCMD]  Server : host name
[RunSQLCMD]  Port : 1433
[RunSQLCMD]  Database : cmx_ors
[RunSQLCMD]  User : DB User
[RunSQLCMD]  Number of files : 1
[RunSQLCMD] ---------------------------------------------------------
[RunSQLCMD] [1] Running create_cmx_db.sql ...
[RunSQLCMD] Changed database context to 'master'.
[RunSQLCMD] NO SUCH DB FOUND
[RunSQLCMD] Changed database context to 'masteImport the Metadata into the MDM Hub Master Databaser'.
[RunSQLCMD] MSSQL DB supplier_hub READ_COMMITTED_SNAPSHOT ON
[RunSQLCMD] Changed database context to 'supplier_hub'.
[RunSQLCMD] Changed database context to 'master'.

  [echo] CREATE_ORS  end: 2021 November 19 08:47:54

BUILD SUCCESSFUL

Total time: 1 minute 48 seconds

SCRIPT FINISHED

Import the Metadata into the CMX_SYSTEM (MDM Hub Master Database):

F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin>sip_ant.bat import_system
------------------------------------------------------------------

Current folder : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\
Current script : sip_ant.bat
Parameters : import_system
Sipeian Home : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\
Java Home : C:\Program Files\Java\jre1.8.0_202

Classpath : F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\hashing\bcprov-jdk15on-1.54.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xml-apis.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ojdbc7.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xercesImpl.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\log4j-1.2.16.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\siperian-common.jar;C:\Program Files\Java\jre1.8.0_202\lib\rt.jar;C:\Program Files\Java\jre1.8.0_202\lib\i18n.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant-apache-log4j.jar;

------------------------------------------------------------------

F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin>"C:\Program Files\Java\jre1.8.0_202\bin\java"  -classpath "F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\hashing\bcprov-jdk15on-1.54.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xml-apis.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ojdbc7.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\xercesImpl.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\log4j-1.2.16.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\siperian-common.jar;C:\Program Files\Java\jre1.8.0_202\lib\rt.jar;C:\Program Files\Java\jre1.8.0_202\lib\i18n.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant-apache-log4j.jar;;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ant-launcher.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\commons-validator-1.4.0.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\ojdbc7.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\mssql-jdbc-7.2.2.jre8.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\db2jcc.jar;F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\..\\lib\db2jcc_license_cu.jar" org.apache.tools.ant.Main import_system

Buildfile: F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\build.xml
[echo] sip.home = F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database

extract_target_build_number:

[echo] extracting build number...
[unzip] Expanding: F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\lib\ProductVersion.jar into F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin
[delete] Deleting directory F:\Informatica_MDM_10.4_HF2_DATABASE_FILES\database\bin\resources
[echo] Target build number is 10.4.2.0.0.0.10684905

defenvironment:

[echo] Define the target database type
[propertyprompt] Enter database type (ORACLE,MSSQL,DB2) [] :MSSQL

import_system:

     [echo]
     [echo] ==========================================================
     [echo] IMPORT_MASTER  start: 2021 November 19 08:51:42

mssqldb.info:

[propertyprompt] Enter Master database name: [cmx_system] :<Press ENTER here>
[propertyprompt] Enter DB hostname: [localhost] :<Provide DB server name>
[propertyprompt] Enter DB port number: [1433] :

import_system:

[propertyprompt] Connect URL [jdbc:sqlserver://DEV-WIVMDB01:1433] :
[localizeEcho] MDM master database name: "cmx_system"
[propertyprompt] Enter Master DB user password [] :<Provide cmx_system password>
[propertyprompt] Enter locale name [en_US] :

     [echo] Connecting to jdbc:sqlserver://DEV-WIVMDB01:1433
     [java] 08:53:59,458  INFO ImportUtil:67 - start
     [java] 08:54:05,073 ERROR Deala:184 - auto commit is on: true
     [java] 08:54:05,389  INFO Mapping:285 - Loading mapping descriptors from jar:file:/F:/Informatica_MDM_10.4_HF2_DATABASE_FILES/database/lib/siperian-server.jar!/com/siperian/mrm/jdbcmd/dbadapter/mapping.xml
         .
	 .
	 .
	 .
	 .
	 .
	 .
     [java] sql statement - select PROCESS_DESC from C_REPOS_PROCESS
     [echo] IMPORT_MASTER  end: 2021 November 19 08:55:53

BUILD SUCCESSFUL

Total time: 4 minutes 11 seconds

SCRIPT FINISHED

SHARE:
by at

Informatica MDM - Truncate or Delete Base Object table Data

Curious about how to remove or purge data from the Base Object table in Informatica MDM using SIF and Shell script? Here's a quick guide! First, use the SIF API to identify and mark records for deletion. Then, run a Shell script to execute the purge process based on your specifications. Want to dive deeper? Let’s explore the steps together!


The Service Integration Framework (SIF) provides the "CleanTable" API, which allows for the deletion or truncation of the entire Base Object in the Operation Reference Store (ORS). In the following section, we will explore the process of accomplishing this task using a shell script

Sample Shell Script:
#!/bin/bash
###########################################################
# Author: Prasad J
# Date Created: 10/14/2021
#
# Purpose: - clean the BO table 
###########################################################
# Cleane Bo Table - C_BO_PRTY 
curl -m 60 --header "Content-Type: text/xml;charset=UTF-8" --header "SOAPAction:urn:siperian.api/Soap/cleanTable" --data "@Bo_prty.xml" --url http://hostname:8080/cmx/services/SifService
Modify the attributes according to your environment setup.

@Bo_prty.xml file:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:siperian.api">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:cleanTable>
         <!--Optional:-->
	   <urn:username>admin</urn:username>
         <urn:password>
		<urn:password>admin</urn:password>
            <urn:encrypted>false</urn:encrypted>
         </urn:password>
         <!--Optional:-->
         <urn:securityPayload/>
         <!--Optional:-->
         <urn:orsId>CMX_ORS</urn:orsId>
         <!--Optional:-->
         <urn:interactionId/>
         <!--Optional:-->
         <urn:asynchronousOptions>
            <urn:isAsynchronous>false</urn:isAsynchronous>
            <!--Optional:-->
            <urn:jmsReplyTo/>
            <!--Optional:-->
            <urn:jmsCorrelationId>?</urn:jmsCorrelationId>
         </urn:asynchronousOptions>
         <urn:siperianObjectUid>BASE_OBJECT.C_BO_PRTY</urn:siperianObjectUid>
         <!--Optional:-->
         <urn:cleanStaging>false</urn:cleanStaging>
         <!--Optional:-->
         <urn:useTruncate>true</urn:useTruncate>
      </urn:cleanTable>
   </soapenv:Body>
</soapenv:Envelope>
cleanStaging:
True - Removes the rows in the staging tables and the dependent tables
False - Retains the rows in the staging tables and the dependent tables

useTruncate:
True -  Uses the TRUNCATE statement, which functions faster, to remove the records
False - Uses the DELETE statement, which functions slower, to remove the records



SHARE:
by at
Subscribe to: Posts (Atom)
© DataTransformations | All rights reserved.
Blogger Template Created by pipdig