Thursday, June 10, 2021

Query to create Repository User in Oracle


--Create  a new tablespace:

DROP TABLESPACE infa_repo_dev_ts INCLUDING CONTENTS;

CREATE TABLESPACE infa_repo_dev_ts DATAFILE SIZE 50 M AUTOEXTEND ON NEXT 10 M MAXSIZE UNLIMITED FORCE LOGGING ONLINE FLASHBACK OFF;


--Create the infa_repo_dev database user:

DROP USER infa_repo_dev CASCADE;

CREATE USER infa_repo_dev IDENTIFIED BY DB105you DEFAULT TABLESPACE infa_repo_dev_ts TEMPORARY TABLESPACE temp QUOTA 0 ON USERS QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON infa_repo_dev_ts;


--Grant necessary privileges and roles to your infa_repo_dev database user:

GRANT CONNECT, RESOURCE TO infa_repo_dev;

GRANT CREATE TABLE TO infa_repo_dev;

GRANT CREATE VIEW TO infa_repo_dev;

GRANT CREATE SEQUENCE TO infa_repo_dev;

GRANT CREATE SESSION TO infa_repo_dev;

GRANT CREATE SYNONYM TO infa_repo_dev;

Query to create Domain User in Oracle


DROP TABLESPACE domain_dev_ts INCLUDING CONTENTS;

CREATE TABLESPACE domain_dev_ts DATAFILE SIZE 50 M AUTOEXTEND ON NEXT 10 M MAXSIZE UNLIMITED FORCE LOGGING ONLINE FLASHBACK OFF;


DROP USER domain_dev CASCADE;

CREATE USER domain_dev IDENTIFIED BY DB105you DEFAULT TABLESPACE domain_dev_ts TEMPORARY TABLESPACE temp QUOTA 0 ON USERS QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON domain_dev_ts;


GRANT CONNECT, RESOURCE TO domain_dev;

GRANT CREATE TABLE TO domain_dev;

GRANT CREATE VIEW TO domain_dev;

GRANT CREATE SEQUENCE TO domain_dev;

GRANT CREATE SESSION TO domain_dev;

GRANT CREATE SYNONYM TO domain_dev;

Wednesday, June 9, 2021

Informatica Powercenter 10.5 Upgrade Paths

 


Product

Versions

PowerCenter

You can upgrade to version 10.5 from the following versions including any hotfix and service pack:

·         10.1.1

·         10.2

·         10.4

·         10.4.1

Informatica Data Quality

You can upgrade to version 10.5 from the following versions including any hotfix and service pack:

·         10.1.1

·         10.2

·         10.4

·         10.4.1

Data Engineering Integration

Data Engineering Quality

You can upgrade to version 10.5 from the following versions including any hotfix and service pack:

·         10.1.1

·         10.2

·         10.2.1

·         10.2.2

·         10.4

·         10.4.1

If Data Engineering is in the same domain as any other product supported by the Informatica installer, verify that all products are upgraded to the same supported upgrade version before you upgrade to 10.5.

Data Engineering Streaming

You can upgrade to version 10.5 from the following versions including any hotfix and service pack:

·         10.2.2

·         10.4

·         10.4.1

Data Privacy Management

You can upgrade to version 10.5 from the following versions including any service packs:

·         10.4

·         10.4.1

If Data Engineering, Enterprise Data Catalog, 

Data Privacy Management

and are in the same domain of a version earlier than 10.4, upgrade them all to version 10.4 before you upgrade to 10.5.

Enterprise Data Catalog

You can upgrade to version 10.5 from the following versions including any service packs:

·         10.4

·         10.4.1

If Enterprise Data Catalog and Data Engineering are in the same domain of a version earlier than 10.4, upgrade them both to version 10.4 or 10.4.1 before you upgrade to 10.5.

Enterprise Data Preparation

You can upgrade to version 10.5 from the following versions including any service packs:

·         10.4

·         10.4.1

If Data Engineering, Enterprise Data Catalog, and Enterprise Data Preparation are in the same domain of a version earlier than 10.4, upgrade them all to version 10.4 or 10.4.1 before you upgrade to 10.5.

Test Data Management

You can upgrade to version 10.5 from the following versions:

·         10.2.0 Hotfix 2

·         10.4 and all service packs

·         10.4.1 and all service packs


https://docs.informatica.com/data-integration/powercenter/10-5/upgrading-from-version-10-2--10-5-/upgrade-overview/upgrade-paths.html



Tuesday, May 25, 2021

Linux User Creation With SSHKeypairs on AWS

 In order create user account linux server with ssh keypair for login server with putty tool.

Follow the below steps:

Step 1: Login to Linux server where you want to create user account.

Step 2: create user accouunt by using below command

    useradd -m -s /bin/bash <user name>

Step 3: To check whether user account created

cat /etc/passwd

Step 4: Switch into created user

su - <user name>

Step 5: create .ssh directory under user's home folder

/home/<user name>/  mkdir .ssh

Step 6: change the .ssh directory permission and navigate to that directory

  chmod 700 .ssh

cd .ssh

step 7: under the user's home folder in .ssh directory.  need to generate keypair 

ssh-keygen

step 8: It will generate two files

id_rsa    ----> this private key of user

id_rsa.pub ----> this is the public key user

step 9: create a file called authorized_keys and copy the content of from id_rsa to authorized_keys file

       cp id_rsa.pub authorized_keys

step 10: change the permission of authorized_keys

chmod 600 authorized_keys

step 11: copy the id_rsa file content to vdc server and open and notepate paste the content and save the file with pem extentio "filename.pem"

step 12: open puttygen tool and load the pem file and save as the private key.  it will prompting to save your file.  now your pem file is converted into ppk file

step 13: try to login with this ppk file for specified linux server

Informatica Upgrade Planner on AWS

 



Tuesday, January 5, 2021

Query to check owner of each table in Redshift

 

Query to check owner of each table in Redshift

SELECT n.nspname AS schema_name,

       pg_get_userbyid(c.relowner) AS table_owner,

       c.relname AS table_name,

       CASE

         WHEN c.relkind = 'v' THEN 'view'

         ELSE 'table'

       END AS table_type,

       d.description AS table_description

FROM pg_class AS c

  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

  LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

  LEFT JOIN pg_description AS d

         ON (d.objoid = c.oid

        AND d.objsubid = 0)

WHERE c.relkind IN ('r','v')

ORDER BY n.nspname,

         c.relname


List Metadata Exchange Connections

 



SELECT REPOSITORY_ID,

       REPOSITORY_NAME,

       REPO_TYPE_NAME,

       IMW_REPOSITORY.DELETED_FLAG,

       EFF_FROM_DT,

       EFF_TO_DT

FROM IMW_REPOSITORY,

     IMW_REPO_TYPE

WHERE IMW_REPOSITORY.REPO_TYPE_UID = IMW_REPO_TYPE.REPO_TYPE_UID

AND   IMW_REPOSITORY.DELETED_FLAG = 'N'

AND   REPOSITORY_ID <>('IMM_MASTER_REPOSITORY')

ORDER BY REPOSITORY_UID

Data Quality Elements (DQ Mapplets within PowerCenter)


SELECT OPB_SUBJECT.SUBJ_NAME AS PROJ,

       OPB_MAPPING.MAPPING_NAME AS MAPPING,

       OPB_WIDGET_INST.INSTANCE_NAME AS TRANSFORMATION,

       OPB_WIDGET.LAST_SAVED AS LAST_SAVED

FROM OPB_WIDGET_INST

  INNER JOIN OPB_MAPPING ON OPB_WIDGET_INST.MAPPING_ID = OPB_MAPPING.MAPPING_ID

  INNER JOIN OPB_SUBJECT ON OPB_MAPPING.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

  INNER JOIN OPB_WIDGET ON OPB_WIDGET_INST.WIDGET_ID = OPB_WIDGET.WIDGET_ID

WHERE OPB_WIDGET.TEMPLATE_ID IN (SELECT PLG_ID FROM OPB_MMD_CATALOG WHERE PLG_DESC LIKE '%Data Quality%');





List Data Quality Mappings


Execute the following query in your Model Repositories (“MRS Repository”) Schema.


 SELECT PRD_ATTR_VALUE,

       PRR_TYPENAME

FROM PR_RESOURCE,

     PR_ATTRIBUTE

WHERE PR_RESOURCE.PRR_ID = PR_ATTRIBUTE.PRA_RESOURCE

AND   PR_ATTRIBUTE.PRD_ATTR_NAME = 'REPO.SERVICE_PROP.UTC_CREATION_TIME_ATTR'

AND   PR_RESOURCE.PRR_TYPENAME LIKE '%TokenStandardiserTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%AssociationTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%AvTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%CaseConverterTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ClassifierTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ConsolidationTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%DecisionTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%GroupTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%LabelerTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%MatcherTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%MergeTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ParserTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%StringDistanceTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%WeightBasedAnalyserTxImpl'