The point of this is to document research around refactoring the informatica integration around eliminating redundancy of shared objects, like sources, targets and mappings.
This process is going to involve a LOT of interaction with pmrep, and will take a lifetime if each command is executed on it's own process. This should be done in conjunction with in process clients, or else write out listings and then create an export script and execute that.
Listing all objects:
| Code Block | ||||
|---|---|---|---|---|
| ||||
pmrep>listobjects
The option -o is required.
Usage: listobjects
-o <object_type>
[-t <object_subtype>]
[-f <folder_name>]
[-c <column_separator>]
[-r <end-of-record_indicator>]
[-l <end-of-listing_indicator>]
[-b (verbose)]
[-s < dbd_seperator>]
Failed to execute listobjects.
pmrep> |
...
Note that for a source, the source 'dbname' prefix is required (in this case, FlatFile).
Listing dependencies for a workflow gets a list of ALL objects therein - including transformations. The parent dependency direction is invalid, so only children is allowed:
| Code Block | ||||
|---|---|---|---|---|
| ||||
pmrep>listobjectdependencies -n wkf_LOAD_ITMMVMT_AUD -o workflow -p both -f EDW_POS_TICKET -b
REP_SVC_DEV EDW_POS_TICKET source FlatFile.sc_d_fct_nt_itmvmt_v20091109 1 shortcut
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_AUD 1 shortcut
REP_SVC_DEV SHARED_POS_ODS target TICKET_DETAIL_AUD 1
REP_SVC_DEV SHARED_POS_ODS target TICKET_SUBHEADER_AUD 1
REP_SVC_DEV EDW_POS_TICKET source_qualifier non-reusable m_LOAD_ITMMVMT_AUD.SQ_Shortcut_to_d_fct_nt_itmvmt_v20091109 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_DETAIL_AUD 1 shortcut
REP_SVC_DEV SHARED_POS_ODS target TICKET_AUD 1
REP_SVC_DEV EDW_POS_TICKET sessionconfig default_session_config 1
REP_SVC_DEV EDW_POS_TICKET start non-reusable wkf_LOAD_ITMMVMT_AUD.Start 1
REP_SVC_DEV EDW_POS_TICKET target sc_ITMMVMT_AUD 1 shortcut
REP_SVC_DEV EDW_POS_TICKET router non-reusable m_LOAD_ITMMVMT_AUD.RTRTRANS 1
REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_STANDARDIZE_DATE_FORMAT 1 shortcut
REP_SVC_DEV SHARED_POS_ODS sequence reusable seq_AUDIT_SK 1
REP_SVC_DEV SHARED_EDW expression reusable exp_STANDARDIZE_DATE_FORMAT 1
REP_SVC_DEV SHARED_POS_ODS target ITMMVMT_AUD 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_SUBHEADER 1
REP_SVC_DEV EDW_POS_TICKET scheduler non-reusable wkf_LOAD_ITMMVMT_AUD.Scheduler 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_DETAIL 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_TGT_ANCHOR_DETAIL 1
REP_SVC_DEV EDW_POS_TICKET mapping m_LOAD_ITMMVMT_AUD 1
REP_SVC_DEV SHARED_POS_ODS source FlatFile.d_fct_nt_itmvmt_v20091109 1
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_PREPARE_HEADER 1
REP_SVC_DEV EDW_POS_TICKET workflow wkf_LOAD_ITMMVMT_AUD 1
REP_SVC_DEV EDW_POS_TICKET sequence reusable sc_seq_AUDIT_SK 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_ADD_PARAMETERS 1
REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_SUBHEADER_AUD 1 shortcut
REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_ITMMVMT_AUD.EXP_TGT_ANCHOR_TICKET_AUD 1
REP_SVC_DEV EDW_POS_TICKET session non-reusable wkf_LOAD_ITMMVMT_AUD.s_m_LOAD_ITMMVMT_AUD 1
.
The total number of records returned: 28
listobjectdependencies completed successfully. |
Here is a more complex example. Mapplets look a bit tricky (see the lookup_procedure):
| Code Block | ||
|---|---|---|
| ||
Informatica(r) PMREP, version [9.1.0 HotFix4], build [449.0224], Windows 64-bit Copyright (c) Informatica Corporation 1994 - 2012 All Rights Reserved. This Software is protected by U.S. Patent Numbers 5,794,246; 6,014,670; 6,016,501; 6,029,178; 6,032,158; 6,035,307; 6,044,374; 6,092,086; 6,208,990; 6,339,775; 6,640,226; 6,789,096; 6,820,077; 6,823,373; 6,850,947; 6,895,471; 7,117,215; 7,162,643; 7,254,590; 7,281,001; 7,421,458; 7,496,588; 7,523,121; 7,584,422; 7,720,842; 7,721,270; and 7,774,791, international Patents and other Patents Pending. Invoked at Thu Apr 24 15:48:17 2014 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_SALES_TYPE_ID 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R2_ABS_PLU 1 REP_SVC_DEV EDW_POS_TICKET scheduler non-reusable wkf_LOAD_TICKET_DETAIL_STG.Scheduler 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_POS_SYSTEM_TYPE 1 REP_SVC_DEV EDW_POS_TICKET start non-reusable wkf_LOAD_TICKET_DETAIL_STG.Start 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_COLLECT_CONDIMENT_SEQ_NUM 1 REP_SVC_DEV EDW_POS_TICKET mapping m_LOAD_TICKET_DETAIL_STG 1 REP_SVC_DEV SHARED_POS_ODS sequence reusable seq_TICKET_DETAIL_ID_SK 1 REP_SVC_DEV EDW_POS_TICKET source_qualifier non-reusable m_LOAD_TICKET_DETAIL_STG.SQ_sc_TICKET_DETAIL_AUD 1 REP_SVC_DEV EDW_POS_TICKET output_transformation non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.OUTPUT 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_PRODUCT_SIZE 1 REP_SVC_DEV SHARED_POS_ODS target TICKET_STG 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_DEFEAT_PRODUCT_SIZE_LOOKUP_NT 1 REP_SVC_DEV EDW_POS_TICKET sequence reusable sc_seq_TICKET_DETAIL_ID_SK 1 shortcut REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_BR_IND_VALID_PLU 1 REP_SVC_DEV SHARED_EDW expression reusable exp_DATE_CONVERSION 1 REP_SVC_DEV EDW_POS_TICKET update_strategy non-reusable m_LOAD_TICKET_DETAIL_STG.upd_SUBHEADER_INDICATORS 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_BR_IND_TICKET_DETAIL_UNIQUE 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R4b_SIZED_PLU_ALIAS 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R4a_ALIAS 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_TKT_DTL_STG_ANCHOR 1 REP_SVC_DEV EDW_POS_TICKET sessionconfig default_session_config 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_LOOKUP_VALIDATIONS 1 REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_SUBHEADER_STG 1 shortcut REP_SVC_DEV SHARED_POS_ODS target TICKET_SUBHEADER_STG 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.LKP_OMC_PRODUCT_SIZE_TRANSLATION 1 REP_SVC_DEV EDW_POS_TICKET expression reusable EXP_SYNTHETIC_NUM 1 REP_SVC_DEV SHARED_EDW expression reusable exp_INT_CONVERSION 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.lkp_VW_STORE 1 REP_SVC_DEV SHARED_POS_ODS target TICKET_DETAIL_STG 1 REP_SVC_DEV EDW_POS_TICKET custom_transformation non-reusable m_LOAD_TICKET_DETAIL_STG.JTX_CALCULATE_TKT_SBHDR_FLAGS 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R3_SRI_ITM_NBR 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.EXP_SOURCE_ANCHOR 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_COLLECT_SEQ_NUM 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.LKP_PRODUCT_ALIAS_R1_BASE_PLUS_SIZE 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_DC_RULES 1 REP_SVC_DEV EDW_POS_TICKET mapplet reusable mplt_PRODUCT_LOOKUP_OR_ALIAS 1 REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET 1 REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_STG 1 shortcut REP_SVC_DEV EDW_POS_TICKET router non-reusable m_LOAD_TICKET_DETAIL_STG.RTRTRANS 1 REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET_STG 1 shortcut REP_SVC_DEV EDW_POS_TICKET filter non-reusable m_LOAD_TICKET_DETAIL_STG.flt_BR_IND_TICKET_DETAIL_UNIQUE 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.EXP_PRODUCT_DECISION 1 REP_SVC_DEV EDW_POS_TICKET input_transformation non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.INPUT 1 REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_INT_CONVERSION 1 shortcut REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET_DETAIL_AUD 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_PREP_PORTS_FOR_SEQ_NUM 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.EXP_BR_IND_TICKET_DETAIL_UNIQUE_NULL_PRIMARY_KEYS 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_COLLECT_PORTS 1 REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET 1 shortcut REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_SOURCE_ANCHOR 1 REP_SVC_DEV EDW_POS_TICKET update_strategy non-reusable m_LOAD_TICKET_DETAIL_STG.upd_UPD_TKT_STG_AGGREGATES 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_SALES_DATE 1 REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable m_LOAD_TICKET_DETAIL_STG.LKP_FIND_SUB_TICKET 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_BR_TICKET_DETAIL_VALID 1 REP_SVC_DEV EDW_POS_TICKET source EDW_TICKET.sc_TICKET_DETAIL_AUD 1 shortcut REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_CALCULATED_PORTS 1 REP_SVC_DEV EDW_POS_TICKET expression non-reusable m_LOAD_TICKET_DETAIL_STG.exp_MISC_DATA_VALIDATION 1 REP_SVC_DEV EDW_POS_TICKET expression reusable sc_exp_DATE_CONVERSION 1 shortcut REP_SVC_DEV SHARED_POS_ODS source EDW_TICKET.TICKET_STG 1 REP_SVC_DEV EDW_POS_TICKET session non-reusable wkf_LOAD_TICKET_DETAIL_STG.s_m_LOAD_TICKET_DETAIL_STG 1 REP_SVC_DEV EDW_POS_TICKET target sc_TICKET_DETAIL_STG 1 shortcut REP_SVC_DEV EDW_POS_TICKET lookup_procedure non-reusable mplt_PRODUCT_LOOKUP_OR_ALIAS.lkp_Get_NETSALES_EXCLUSION_FLAG 1 REP_SVC_DEV EDW_POS_TICKET workflow wkf_LOAD_TICKET_DETAIL_STG 1 . The total number of records returned: 64 listobjectdependencies completed successfully. Completed at Thu Apr 24 15:48:22 2014 |
This one has a shortcut to a mapplet:
| Code Block | ||
|---|---|---|
| ||
pmrep>listobjectdependencies -n wkf_LOAD_RECIPE_SCD -o workflow -p children -f SCM_ANALYTICS -b
REP_SVC_DEV SCM_ANALYTICS target sc_FF_TGT_MPLT_DIM_SCD_II_DBG 1 shortcut
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_SOFT_DELETE 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.SCM_RECIPE 1
REP_SVC_DEV SHARED_EDW_ODS output_transformation non-reusable MPLT_DIM_SCD_II.OUTPUT 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.EDW_RAW_MATERIAL_HIST 1
REP_SVC_DEV SHARED_EDW_ODS target FF_TGT_MPLT_DIM_SCD_II_DBG 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_SRC_ANCHOR_RECIPE_CURRENT_GEN 1
REP_SVC_DEV SHARED_EDW_ODS user_defined_function cleanText 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_FIRST_VERSION 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_SET_DSK_TO_0 1
REP_SVC_DEV SCM_ANALYTICS sequence reusable sc_seq_RECIPE_SK 1 shortcut
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_INPUT_ANCHOR_CONCAT 1
REP_SVC_DEV SHARED_EDW sequence reusable seq_RECIPE_SK 1
REP_SVC_DEV SCM_ANALYTICS start non-reusable wkf_LOAD_RECIPE_SCD.Start 1
REP_SVC_DEV SCM_ANALYTICS router non-reusable m_LOAD_RECIPE_SCD.RTR_SPLIT_RULES 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_SCM_RECIPE 1 shortcut
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_SRC_ANCHOR_RECIPE 1
REP_SVC_DEV SCM_ANALYTICS scheduler non-reusable wkf_LOAD_RECIPE_SCD.Scheduler 1
REP_SVC_DEV SCM_ANALYTICS workflow wkf_LOAD_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS mapplet reusable MPLT_DIM_SCD_II 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_EDW_RAW_MATERIAL_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS sessionconfig default_session_config 1
REP_SVC_DEV SCM_ANALYTICS target sc_RECIPE 1 shortcut
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_LOAD_RECIPE_SCD.SQ_sc_SCM_RECIPE 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_END_OF_LIFE 1
REP_SVC_DEV SCM_ANALYTICS joiner non-reusable m_LOAD_RECIPE_SCD.JNR_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_CATEGORIZE_RECORDS 1
REP_SVC_DEV SCM_ANALYTICS source EDW_ODS.sc_EDW_RECIPE_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_PRESQL_DELETE_WORKSPACE.SQ_sc_DUMMY_EDW_ODS 1
REP_SVC_DEV SCM_ANALYTICS session non-reusable wkf_LOAD_RECIPE_SCD.s_m_LOAD_RECIPE_SCD 1
REP_SVC_DEV SCM_ANALYTICS mapplet reusable sc_MPLT_DIM_SCD_II 1 shortcut
REP_SVC_DEV SCM_ANALYTICS mapping m_LOAD_RECIPE_SCD 1
REP_SVC_DEV SHARED_EDW_ODS input_transformation non-reusable MPLT_DIM_SCD_II.INPUT 1
REP_SVC_DEV SCM_ANALYTICS source_qualifier non-reusable m_LOAD_RECIPE_SCD.SQ_sc_EDW_RECIPE_HIST 1
REP_SVC_DEV SHARED_EDW_ODS output_transformation non-reusable MPLT_DIM_SCD_II.OUT_DBG 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_PASS_THRU 1
REP_SVC_DEV SHARED_EDW_ODS target EDW_RAW_MATERIAL_HIST 1
REP_SVC_DEV SCM_ANALYTICS mapping m_PRESQL_DELETE_WORKSPACE 1
REP_SVC_DEV SCM_ANALYTICS session reusable s_m_PRESQL_DELETE_WORKSPACE 1
REP_SVC_DEV SHARED_EDW_ODS expression non-reusable MPLT_DIM_SCD_II.EXP_DBG_ANCHOR 1
REP_SVC_DEV SHARED_EDW target RECIPE 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_ANCHOR_TGT_NEWER_VERSION 1
REP_SVC_DEV SCM_ANALYTICS target sc_EDW_RAW_MATERIAL_HIST 1 shortcut
REP_SVC_DEV SCM_ANALYTICS filter non-reusable m_PRESQL_DELETE_WORKSPACE.FLT_DROP_RECORD 1
REP_SVC_DEV SHARED_EDW_ODS source EDW_ODS.EDW_RECIPE_HIST 1
REP_SVC_DEV SCM_ANALYTICS expression non-reusable m_LOAD_RECIPE_SCD.EXP_COMPARE 1
.
The total number of records returned: 46
listobjectdependencies completed successfully. |