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


3 comments:

  1. Thank you !!!

    -- how-to export all the workflows from an Informatica folder
    for wf in `pmrep listobjects -o workflow -f FOLDER_NAME | sed '1,8d;/successfully/,$d' | sed -e 's/workflow //'` ; do pmrep objectexport -n $wf -f FOLDER_NAME -o workflow -r -u $wf.xml ;done ;

    ReplyDelete
  2. Thank you for this useful article , we are looking to replace all objects and save session config and sequence generator normalizer.

    I read some commands and got that if we say object type all it will replace and resuse all ,meanwhile we can save sequence generator by putting it in folder map values.

    any idea how to save session config also.

    Thanks
    Rahul

    ReplyDelete
  3. Hi all, Thanks for this useful artical.

    WE want to change the column lenth in 150 mappings for the same column so is there any script we can use to get this fast?

    thanks

    ReplyDelete