Saturday, May 26, 2012

WORKING WITH PMREP COMMANDS



            1          Working with pmrep commands       
1.1       Connect to Repository           
1.2       ListObjects     
1.2.1    Workflow
1.2.2    Source
1.2.3    Target
1.2.4    Mapplet         
1.2.5    Transformation          
1.2.6    Worklet          
1.2.7    Stored Procedures     
1.2.8    Reusable Sessions      
1.2.9    All Folders      
1.2.10  List User Connections
1.2.11  Listing Tables by Session (Listtablesbysess)   
1.2.12  Listing Dependencies 
1.3       Export Object 
1.3.1    Source
1.3.2    Target
1.3.3    Transformation          
1.3.4    Task    
1.3.5    Mapplet         
1.3.6    Mapping         
1.3.7    Session           
1.3.8    Worklet          
1.3.9    Workflow       
1.4       Find Checkouts           
1.5       Connections   
1.5.1    CreateConnection      
1.5.2    UpdateConnection     
1.5.3    DeleteConnection      
1.6       Validate          
1.7       Modify Folder Owner and Permission           
1.8       Execution of a Query 
1.9       Object Import
1.10     Starting a session in worklet using pmrep commands          
  

1   Working with pmrep commands

        1.1   Connect to Repository
 pmrep connect -r <REPO NAME> -d <DOMAIN NAME> -n <USER> -x <PWD>
      
     1.2   ListObjects 
     
     1.2.1  Workflow

          pmrep listobjects -o workflow -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import.txt
     1.2.2  Source

          pmrep listobjects -o Source -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import.txt

     1.2.3  Target

          pmrep listobjects -o Target -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import.txt
  
     1.2.4   Mapplet

          pmrep listobjects -o Mapplet -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import_mpplt.txt

     1.2.5  Transformation

          pmrep listobjects -o Transformation -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import_mpplt.txt
  
     1.2.6 Worklet

           pmrep listobjects -o Worklet -f <FOLDER> | sed '1,8d;/successfully/,$d' > /wf_list_import_wrklt.txt
  
     1.2.7  Stored Procedures

 pmrep listobjects -o transformation -t stored_procedure -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import_stproc.txt
  
     1.2.8  Reusable Sessions

              pmrep listobjects -o session -f <FOLDER> | sed '1,8d;/successfully/,$d' > wf_list_import_sess.txt

     1.2.9  All Folders

               pmrep listobjects -o folder > listfolders.txt;
  
     1.2.10  List User Connections

               pmrep listuserconnections sed '1,8d;/successfully/,$d' > wf_list_cnxs.txt

  
      1.2.11  Listing Tables by Session (Listtablesbysess)

               pmrep listtablesbysess -f INFA -s <SESSION NAME> -t target
               pmrep listtablesbysess -f INFA -s <SESSION NAME> -t source
  
      1.2.12 Listing Dependencies

               pmrep ListObjectDependencies -n <SESSION NAME> -o session -f <FOLDER> -p both

  
      1.3   Export Object

      1.3.1 Source

             pmrep objectexport -n <DB.SourceName> -o Source -f <Folder Name> -m -s -b -r –u <xml file name>;
  
    1.3.2  Target

              pmrep objectexport -n <TargetName> -o Target -f <Folder Name> -m -s -b -r –u <xml file name>;

    1.3.3  Transformation

              pmrep objectexport -n  <LKP Name> -o Transformation -t lookup_procedure -f <folder> -m -s -b -r -u LKP_LOAN_PARTY.xml;
  
    1.3.4  Task

               pmrep objectexport -n <Task Name>  -o Task -t Command -f <FOLDER>-m -s -b -r -u tsk_icr_dly_delete_persistent_files.xml;

    1.3.5 Mapplet

             pmrep objectexport -n <Mapplet name> -o Mapplet -f <Folder Name> -m -s -b -r –u <xml file name>; 

    1.3.6 Mapping
     
             pmrep objectexport -n <Mapping name> -o Mapping -f <Folder Name> -m -s -b -r –u <xml file name>;

     1.3.7  Session

             pmrep objectexport -n <Session name> -o Session -f <Folder Name> -m -s -b -r –u <xml file name>;

    1.3.8   Worklet

               pmrep objectexport -n <Worklet name> -o Worklet -f <Folder Name> -m -s -b -r –u <xml file name>;

   1.3.9  Workflow

             pmrep objectexport -n <Workflow name> -o Workflow -f <Folder Name> -m -s -b -r –u <xml file name>;



LOG:

12/19/2011 22:17:18
Analyzing Object Dependencies...
Exporting selected objects in repository SIT1_REPO ...
Exporting selected objects in folder COMMON ...
        Fetching Source Definition [DISB_DIM_BKP] ...
        Exporting Source Definition [DISB_DIM_BKP] ...
        Exporting Target Definition [DISB_DIM] ...
Exporting selected objects in folder <FOLDER>...
        Fetching Mapping [m_dim_arch_wly] ...
        Exporting Mapping [m_dim_arch_wly] ...
        Fetching Source Defination [sc_DISB_DIM_BKP] ...
        Exporting Source Defination [sc_DISB_DIM_BKP] ...
        Fetching Target Defination [sc_DISB_DIM] ...
        Exporting Target Defination [sc_DISB_DIM] ...
Export is completed.
Exported 5 object(s) - 0 Error(s), - 0 Warning(s)
objectexport completed successfully.

  
 1.4  Find Checkouts


        To find checkout objects of all folders and all users

        pmrep findcheckout -u all users > /infa01/etldata/checkout.txt;

Find checkout objects of only <ABC> Folder

         pmrep findcheckout -f ABC -u all users > /infa01/etldata/checkout.txt;

   1.5   Connections

   1.5.1    CreateConnection


createconnection
-s <connection_type>
-n <connection_name>
-u <user_name>
[-p <password> |

-P <password_environment_variable>]
[-c <connect string> (required for Oracle, Informix, DB2, and ODBC)]
-l <code_page>
[-r <rollback_segment> (valid for Oracle connection only)]
[-e <connection_environment_SQL>]
[-f <transaction_environment_SQL>]
[-z <packet_size> (valid for Sybase ASE and MS SQL Server connection)]
[-b <database_name> (valid for Sybase ASE, Teradata and MS SQL Server connection)]
[-v <server_name> (valid for Sybase ASE and MS SQL Server connection)]
[-d <domain name> (valid for MS SQL Server connection only)]
[-t (enable_trusted_connection, valid for MS SQL Server connection only)]
[-a <data_source_name> (valid for Teradata connection only)]
[-x (enable advanced security, lets users give Read, Write and Execute
permissions only for themselves.)]
[-k <connection_attributes> (attributes have the format
name=value;name=value; and so on)]


    pmrep createconnection -s Oracle -n TEST1 -u abcd -p abcdef -c o20T -l US-ASCII

       1.5.2 UpdateConnection



Updates the user name, password, connect string, and attributes for a database connection.

updateconnection
-t <connection_subtype >
-d <connection_name>
[-u <new_user_name>]
 [-p <new_password>|-
P <new_password_environment_variable>]
[-c <new_connection_string>]
[-a <attribute_name> -v <new_attribute_value>]
[ -s <connection type application, relational, ftp, loader or queue > ]
[-l <code page>]

     pmrep updateconnection -t <DB>  -d <Connection Name>  -u <USER>  -p <PWD> -c <Connection String>

pmrep updateconnection -t Oracle -d TEST -u def -p abcd -c O20K

  
     1.5.3  DeleteConnection
deleteconnection
-n <connection_name>
[-f (force delete)]
[-s <connection type application, relational, ftp, loader or queue>]
       pmrep deleteconnection -n TEST -f -s Relational

       1.6  Validate


             pmrep validate -n <Mapping Name>  -o mapping -f <Folder> -s -k -m test
 

        1.7  Modify Folder Owner and Permission


               pmrep modifyfolder -n <Folder> -o Admin -p 777

       1.8  Execution of a Query

               Execute query using pmrep command

                pmrep executequery -q Folder_Backup -t shared -u /infa01/etldata/Folderquerylist.txt

      1.9  Object Import


pmrep objectimport -i wf_check_dly.xml -c importtestctlfile.xml -l /infa01/etldata/temp/import.log

pmrep objectimport -i /infa01/etldata/temp/wf_check_dly.xml -c /infa01/etldata/temp/importtestctlfile.xml -l /infa01/etldata/temp/import.log

Place your export xml file and cntrl file in temp location

Please find control file below importtestctlfile.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE IMPORTPARAMS SYSTEM "/ifx01/informatica/pc86/server/bin/impcntl.dtd">
<IMPORTPARAMS>
<FOLDERMAP
  SOURCEFOLDERNAME="IFX"
  SOURCEREPOSITORYNAME="SRC_DEV_REPO"
  TARGETFOLDERNAME="IFX"
  TARGETREPOSITORYNAME="TGT_SIT1_REPO"/>
<TYPEFILTER TYPENAME="SOURCE"/>
<TYPEFILTER TYPENAME="TARGET"/>
<TYPEFILTER TYPENAME="MAPPLET"/>
<TYPEFILTER TYPENAME="MAPPING"/>
<TYPEFILTER TYPENAME="TRANSFORMATION"/>
<TYPEFILTER TYPENAME="CONFIG"/>
<TYPEFILTER TYPENAME="TASK"/>
<TYPEFILTER TYPENAME="SESSION"/>
<TYPEFILTER TYPENAME="SCHEDULER"/>
<TYPEFILTER TYPENAME="WORKFLOW"/>
<TYPEFILTER TYPENAME="SCHEDULER"/>
<TYPEFILTER TYPENAME="WORKLET"/>
<RESOLVECONFLICT>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="WORKFLOW"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="WORKLET"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="SESSION"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="MAPPING"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="MAPPLET"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Source definition"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Target definition"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Expression"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Filter"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Aggregator"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Rank"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Normalizer"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Router"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Sequence"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Sorter"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="update strategy"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Custom Transformation"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Lookup Procedure"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Transaction control"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Stored Procedure"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="External Procedure"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Joiner"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="SessionConfig"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Email"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Command"/>
<TYPEOBJECT RESOLUTION="REPLACE" OBJECTTYPENAME="Scheduler"/>
</RESOLVECONFLICT>
</IMPORTPARAMS>

 1.10  Starting a session in worklet using pmrep commands


pmcmd starttask -sv <ServerName> -d domain_name -u Administrator -p pwd -f <FOLDER>-w wf_load_dly s_dly_credit_fact


Friday, May 25, 2012

INFORMATICA REPOSITORY QUERIES - PART V



INFORMATICA REPOSITORY QUERIES - PART I
INFORMATICA REPOSITORY QUERIES - PART II
INFORMATICA REPOSITORY QUERIES - PART III
INFORMATICA REPOSITORY QUERIES - PART IV
 

16        SCHEDULER


 16.1           Check Scheduled workflows using pmcmd command
 

Command Prompt:

pmcmd getservicedetails -sv etltst222_iserv -d domain_ etltst222-u Administrator -p Administrator –scheduled



16.2           Check Scheduled workflows using queries



Query:

 SELECT DISTINCT subject_area, workflow_name FROM rep_workflows WHERE run_options = 8 AND end_options =2 ORDER BY 1;

 
select
SUBJECT_AREA,
WORKFLOW_NAME,
SCHEDULER_NAME,
START_TIME,
END_TIME,
Case when RUN_OPTIONS = 1 then 'Run on demand'
        when RUN_OPTIONS = 2 then 'Run once'
        when RUN_OPTIONS = 4 then 'Run every DELTA_VALUE seconds'
       when RUN_OPTIONS = 8 then  'Customized repeat'
       when RUN_OPTIONS = 16 then 'Run on IS int'
       when RUN_OPTIONS = 18 then 'Run on IS int and run once'
    when RUN_OPTIONS = 20 then 'Run on IS int and every DELTA_VALUE seconds'
    when RUN_OPTIONS = 24 then 'Run on IS int and customized repeat'
    when RUN_OPTIONS = 32 then 'Run continuously'
        else 'Dummy'
end as RUN_OPTIONS,SERVER_NAME
from rep_workflows
where
--SUBJECT_AREA = 'EABC_2_02'
and RUN_OPTIONS <> 1
order by 2,3


17        INTEGRATION SERVICES


 
17.1        Count of workflows running on each Integration Service

 select server_name,count(1) from(
select subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1
group by server_name
--order by 1,2,3


--  AS (SELECT Server_name, Workflow_name, start_time FROM OPB_WFLOW_RUN)
  SELECT (SELECT repository_name FROM OPB_REPOSIT_INFO) AS REPO,
  TRUNC (START_TIME),
         A.SERVER_NAME,
         COUNT (*)
    FROM OPB_WFLOW_RUN A
   WHERE TRUNC (START_TIME) = (  SELECT MAX (TRUNC (AA.START_TIME))
                                   FROM OPB_WFLOW_RUN AA
                                  WHERE AA.workflow_name = A.workflow_name
                               GROUP BY A.workflow_name)
GROUP BY SERVER_NAME,TRUNC (START_TIME)
--ORDER BY 1, 2, 3
 
 
17.2        List of workflows running on each Integration Service


select * from(
select subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1
order by 1,2,3

 

17.3        how to mass update connection


Incase if we have to update permission for multiple connections please use the below pmrep command, Adjust the parameters as needed and make it as a script under $INFA_HOME/server/bin.

 

AssignPermission -o [-t ] -n {-u | -g } [-s ] -p
 

Example:

pmrep connect -r Repository_EDW -d Domain_EDW -n abcd-s LDAP -x xxxx

pmrep assignPermission -o Connection -t Relational -n ADM_Src_d -g Informatica_grade -p RWX  (use updatepermission for modifying the currently assigned permission)