下载文档原格式
Informatica PowerCenter 参数和变量使用指南中国区唯一总代理神州数码（中国）有限公司2014年目录第一章参数和变量 (3)1、概述 (3)2、参数与变量存在的三种形式 (4)2.1系统级变量 (4)2.2 Mapping级参数与变量 (4)2.3 参数文件级参数与变量 (10)第二章参数文件格式 (12)1、标题格式和适用范围 (12)2、参数和变量类型 (13)3、实例 (13)第三章参数文件使用要点 (15)1、创建准则和使用注意事项 (15)2、PMCMD中使用参数文件 (17)第四章应用案例介绍 (18)1、定义M APPING的变量。
(18)2、在E XPRESSION组件中引用变量 (19)3、定义W ORKFLOW变量 (19)4、在E VENT W AIT组件中引用变量 (20)5、在S ESSION中引用变量 (21)6、在C OMMAND组件中引用变量 (22)7、参数文件定义 (22)8、S ESSION参数文件引用设置 (23)9、W ORKFLOW参数文件引用设置 (24)10、W ORKFLOW流程图 (24)第一章参数和变量1、概述参数和变量可以定义在工作流、工作集或会话中。
您可以使用WordPad 或Notepad 等文本编辑器来创建参数文件。
您在参数文件中列出参数或变量及其值。
参数文件可以包含以下类型的参数和变量：◆Integration Service变量◆Integration Service process变量◆Workflow变量◆Worklet变量◆Session参数◆Mapping参数和变量当您在工作流、工作集或会话中使用参数或变量时，PowerCenter Server将检查参数文件以确定参数或变量的开始值。
您可以使用参数文件来初始化工作流变量、工作集变量、映射参数和映射变量。
如果您不定义这些参数和变量的开始值，PowerCenter Server将在其它位置检查参数或变量的开始值。
Informatica PowerCenter 高可扩展性与高性能的企业数据集成标准确立企业数据集成标准Informatica® PowerCenter® 为具备高可扩展性与高性能的企业数据集成软件确立了标准。
PowerCenter 可使您的 IT 组织能够通过单一方式访问、转换和传输数据，无需任何手动编码。
该软件的高可扩展性能可以支持海量数据并满足企业对安全和性能的需求。
PowerCenter 可作为所有企业集成方案的数据集成基础，包括数据仓库、数据治理、数据迁移、面向服务的体系架构 (SOA)、B2B 数据交换以及主数据管理 (MDM)。
•在正确的时间提供正确信息，让业务部门拥有其所需的及时、相关和可信的数据与报告，以便制定更准确、更及时的业务决策 •经济高效地进行扩展，以满足增长的数据需求，节约硬件成本，并降低与数据停机相关的成本和风险 •让开发人员、分析师和管理员团队能够更快更好地协作，共享和重复利用工作成果，以加快项目的交付速度“在我们这一行，至关重要的是能够抢先取得竞争优势和研究项目的投资回报。
不断增长的竞争只会进一步强调这一点，让你更容易理解‘时间就是金钱’这句话的含义。
在此艰难的环境下，Informatica [PowerCenter ] 为进行日常报告助了一臂之力，从而保证我们的团队具备最优的响应度和效率。
”– Alain Afios ，数据处理主管 研发部门，AventisBBVA Compass 是一家金融机构，在世界各地拥有 748 间分行。
该银行必须有效地检测、调查和报告可疑的洗钱活动，以符合国家和国际法规、指令及最佳实践的要求。
同时，该银行也需要降低合规成本。
BBVA Compass 依赖于 PowerCenter 来集成 17 个跨企业的数据源，其中包括拥有不同格式、结构和延迟性的贷款、信用卡、账户及在线银行交易数据。
PowerCenter 提供了金融活动与客户风险的全面视图。
Informatica PowerCenter安装指南(2)目录简介： (2)目标程序： (2)安装环境： (3)假设： (3)前置任务： (3)数据库需求： (3)PowerCenter域配置存储库 (3)PowerCenter 存储库 (4)安装JRE： (4)环境变量设置： (4)安装服务器端： (5)配置服务器端： (6)创建PowerCenter存储库服务： (7)创建PowerCenter集成服务: (9)简介：PowerCenter是Informatica出品的数据中心管理工具集中的一个产品，主要用于设计和完成ETL过程，其中又包含多个子产品，如Data Profiling和Data Analyzer，其服务器端的架构如下：如上图所示，PowerCenter服务器端由被配置在同一域(PowerCenter domain)中的一组计算机组成。
此组计算机可以master－slaves的方式协作，也可以网格方式进行分布式运算？（待求证）。
一个PowerCenter domain中可以配有多个gateway nodes，但只有一个能被设为master，所有对该域的访问均由该结点路由；其余gateway可作为备份。
该服务器段采用面向服务架构，除Service Manager和PowerCenter Administrator这两个服务必须运行在master gateway机器上以外，其他服务均可部署在同一域中的其他结点上，由master gateway提供统一的访问。
对于ETL流程而言，PowerCenter Repository Service和PowerCenter Integration Service为核心服务。
只要安装和部署成功这两个服务，即可执行ETL流程，其他服务为可选服务，当购买了其他产品后才（如Analyst Service为PowerCenter Analyzer的依赖服务）需要。
Informatica PowerExchange介绍Informatica PowerExchange是一个数据访问产品系列，它使IT 组织不必开发自定义的数据访问程序，即可访问各种来源的企业数据。
由于能够访问存储在任何位置的任务关键型运营数据，并可根据需要随时随地交付这些数据，IT 组织可以最大限度地利用其有限资源并实现数据商业价值最大化。
在提取-转换-加载(ETL) 过程中，Informatica PowerExchange 还可以帮助您访问、加载和传输数据，简化小型部门数据中心和数据仓库的开发及部署，帮助它们逐步过渡到企业数据仓库。
统一访问，降低成本大多数IT 组织都需要访问分散在数据库、企业应用程序、Web 服务、社交网络和大型数据源（例如Hadoop）中的数据。
这些数据存储在各种各样的系统中，例如大型机、中型机、商业服务器、云和桌面环境。
借助Informatica PowerExchange 和其余的Informatica 平台，您可以访问各种格式复杂的数据，无须依赖不常用且高成本的各项编程技能。
访问数据就像点菜一样简单。
无需手动编程，这将显著降低数据交付成本。
通过灵活的访问管理变更您的IT 组织是在面向服务的体系结构(SOA) 中创建Web 服务，还是实施事件驱动型集成？您是否需要同步多个数据源或实时交付数据？Informatica PowerExchange 能够满足您当前和未来的数据集成需求。
“点击”应用效率意味着IT 组织无需开展广泛培训即可采用新技术。
Informatica PowerExchange 系列的所有产品将应用相同的技能。
由于每款Informatica PowerExchange 产品都能自动捕获相关元数据并与其余Informatica 数据集成平台实现共享，因此可以快速评估和开发拟定的更改并且自信地予以实现。
这将有助于您的IT 组织满足最为苛刻的数据治理策略。
Informatica PowerCenter V8功能概述产品概述 (2)简介 (2)1.1 源 (3)1.2 目标 (3)1.3 资料库 (3)1.4 Repository Server (3)1.5 PowerCenter Client (4)1.6 PowerCenter Server (4)1.7 连接 (5)1.8 数据库连接 (5)2、使用Informatica PowerCenter Administration Console (5)2.1 Administration Console 窗口 (5)3、使用Repository Manager (6)3.1 Repository Manager 窗口 (7)4、设计流程 (9)4.1 Designer 窗口 (10)5、载入数据 (11)5.1 Workflow Manager (12)5.2 Workflow Manager 窗口 (12)5.3 Workflow Monitor (13)产品概述简介PowerCenter 提供了一个环境，允许您将数据载入集中地点，例如数据集市、数据仓库或操作数据存储。
您可以从多个源提取数据，并根据您在客户端应用程序中构建的业务逻辑转换数据，然后将已转换数据载入文件和关系目标。
PowerCenter 提供了以下集成组件：✧PowerCenter资料库PowerCenter 资料库是PowerCenter 套件的核心部分。
您可以在资料库数据库中创建一组可供PowerCenter 应用程序和工具访问的元数据表。
PowerCenter Client 和Server 访问资料库以保存和检索元数据。
✧PowerCenter Repository Server能够管理从客户端应用程序到资料库的连接。
它可以从资料库数据库表中插入、更新和获取对象。
它还会保持对象的一致性。
✧PowerCenter Client使用PowerCenter Client 管理用户、定义源和目标、构建带有转换逻辑的映射和映射组件，以及创建工作流以运行映射逻辑。
informatica powercenter8.6 简易使用手册（Windows）2009年9月27日目录第1章简介 (3)1.1概念 (3)1.2作用 (3)第2章安装配置 (5)2.1下载 (5)2.2服务器安装 (6)2.3客户端安装 (17)2.4服务器配置 (22)2.5客户端介绍 (27)第1章简介1.1 概念Informatica PowerCenter 是Informatica公司开发的世界级的企业数据集成平台，也是业界领先的ETL工具。
Informatica PowerCenter使用户能够方便地从异构的已有系统和数据源中抽取数据，用来建立、部署、管理企业的数据仓库，从而帮助企业做出快速、正确的决策。
此产品为满足企业级要求而设计，可以提供企业部门的数据和电子商务数据源之间的集成，如XML，网站日志，关系型数据，主机和遗留系统等数据源。
此平台性能可以满足企业分析最严格的要求。
Informatica PowerCenter 是建立可伸缩和可扩展的Informatica数据集成平台的核心和基础。
与Informatica PowerConnect产品一起使用，Informatica PowerCenter 可以提供对广泛的应用和数据源的支持，包括对ERP系统的支持（Oracle, PeopleSoft, SAP），对CRM系统的支持（Siebel），对电子商务数据的支持（XML, MQ Series），遗留系统，及主机数据。
Informatica PowerCenter 是最突出的分析性数据集成平台。
Informatica PowerCenter 是一个可以使大的企业或组织能够按其复杂的业务信息需求，读取、转换、集成遗留系统、关系型ERP，CRM，消息信息和电子商务数据的数据集成平台。
1.2 作用数据整合引擎Informatica PowerCenter拥有一个功能强大的数据整合引擎，所有的数据抽取转换、整合、装载的功能都是在内存中执行的，不需要开发者手工编写这些过程的代码。
informatica repository table -回复主题: Informatica Repository表引言：Informatica Repository是一个关键的组件，它存储了Informatica PowerCenter工具的所有元数据信息。
Informatica是一种数据集成工具，用于在不同的应用程序、数据库和系统之间传输和转化数据。
Repository 表是Informatica Repository数据库中用于存储这些元数据信息的表格。
本文将分步回答关于Informatica Repository表的一些问题。
第一部分：Informatica Repository表的基本概述1. Informatica Repository是什么？InformaticaRepository是一个中心化的元数据存储库，用于管理和存储Informatica PowerCenter工具所需的所有元数据信息。
2. 什么是元数据？元数据是描述数据的数据。
在Informatica Repository中，元数据包含了有关源和目标的连接信息、转换逻辑和配置设置等。
3. 为什么需要Informatica Repository表？Informatica Repository表用于存储Informatica工具中的各种元数据，包括源和目标连接信息、转换逻辑和任务配置等。
这些表提供了访问和管理这些元数据的结构和方法。
第二部分：Informatica Repository表的结构和功能1. Informatica Repository表有哪些主要组成部分？Informatica Repository表大致可分为以下几个主要组成部分：- 源和目标连接信息表：用于存储与数据源和数据目标的连接相关的信息，例如数据库的连接字符串、用户名和密码等。
- 转换逻辑表：用于存储ETL转换过程中的逻辑信息，例如数据映射、过滤条件和转换规则等。
informatica工作原理Informatica是一家全球知名的数据集成和数据管理公司，其工作原理涉及到多个关键组件和技术。
本篇文章将详细介绍Informatica 的工作原理，包括其核心组件、数据处理流程、数据传输方式以及安全机制等方面。
一、核心组件Informatica的核心组件包括PowerCenter、PowerExchange和PowerDesigner等。
这些组件共同构成了Informatica的数据集成平台，提供了数据抽取、转换、清洗、加载等功能。
1.PowerCenter：作为Informatica的核心数据集成引擎，PowerCenter负责执行数据抽取、转换和加载等操作。
它支持多种数据源和目标，包括关系型数据库、非关系型数据库、文件系统等。
PowerCenter通过使用流处理引擎和批量处理引擎，实现了高效的数据处理能力。
2.PowerExchange：PowerExchange是Informatica的数据传输组件，负责在不同的数据源和目标之间进行数据传输。
它支持多种传输协议，如TCP、HTTP、JMS等，可以保证数据传输的可靠性和稳定性。
3.PowerDesigner：PowerDesigner是Informatica的数据模型设计工具，用于创建和编辑数据模型。
它提供了丰富的图形化界面，方便用户设计数据模型并生成相应的元数据。
二、数据处理流程Informatica的数据处理流程主要包括数据抽取、转换、清洗、加载和验证等步骤。
具体流程如下：1.数据抽取：从源数据存储中提取所需的数据，并将其加载到Informatica的数据仓库中。
2.数据转换：将抽取的数据进行转换和清洗，以满足目标系统的要求。
这一步骤可能包括数据类型转换、格式转换、去除重复数据等操作。
3.数据加载：将清洗后的数据加载到目标系统中，完成数据的最终传输。
4.验证：对加载的数据进行验证，确保数据的准确性和完整性。
Informatica PowerCenter 8.6.1安装配置手册2009年10月提交人：杜绍森版本号： 1.0目录安装前准备事项 (4)PowerCenter 8.6.1对系统的配置要求 (4)PowerCenter 8.1对数据库的配置要求 (6)PowerCenter 8.6.1对第三方软件的要求 (7)PowerCenter在UNIX下的安装 (9)配置UNIX用户的环境变量 (9)安装Domain和第一个Node (10)执行install.bin –i console命令 (10)指定PowerCenter服务的license key (12)定制化安装 (13)选择安装PowerCenter Services为例 (13)设定第一个Node的根路径 (14)创建新Domain (15)设定Domain的 Metadata库 (15)安装成功 (16)登陆到控制台 (18)安装后续Node (19)执行install.bin –i console命令 (19)指定PowerCenter服务的license key (21)定制化安装 (22)选择安装PowerCenter Services (22)设定新Node的根路径 (23)加入现有Domain (24)设定Domain信息 (24)设定新Node信息 (25)安装成功 (25)登陆到控制台 (27)PowerCenter在Windows下的安装 (28)选定“Server”安装选项 (28)欢迎界面 (29)为PowerCenter指定license key (30)安装前的必备条件 (30)指定PowerCenter的安装根路径 (31)选择是否启用HTTPS (31)安装需求总结 (32)安装进行过程中。
(33)选择是否创建新的Domain (33)配置存储Domain配置信息的数据库连接信息 (34)配置域信息 (34)配置Informatica Services启动用户 (35)PowerCenter Services的安装总结 (36)对应的安装日志 (36)Logon 管理控制台（Admin Console） (37)登陆后的页面 (38)创建Repository Service (39)客户端安装非常简单，这里不再敖续 (46)PowerCenter 8.1管理操作 (46)登陆到Administration Console (46)创建Repository Service.........................................................................错误！未定义书签。
Informatica所有的元数据信息均以数据库表的方式存到了元数据库中。
当然Infa本身工具提供了很多的人性化的功能，使我们在开发时可以很方便的进行操作，但人们的需求总是万变的，需要方便的取到自己需要的信息，那就需要我们对他的元数据库有很深的了解。
Informatica所有的元数据信息均以数据库表的方式存到了元数据库中。
当然Infa本身工具提供了很多的人性化的功能，使我们在开发时可以很方便的进行操作，但人们的需求总是万变的，需要方便的取到自己需要的信息，那就需要我们对他的元数据库有很深的了解。
Informatica通过表和视图给我们提供着所有的信息，在此将通过一个系列的帖子，将大部分常见的，且非常有用的表及视图介绍一下。
基于这些东西，我们即可以根据不同的需求查出自己需要的数据，也可以开发一些辅助的Infa应用程序。
....FIRST_ERROR_MSG: No errors encountered.用途：这是在查Session运行情况的最终要的表之一，可以最简便的得到Session是否运行正常及当初错时的首个错误简要信息，以及日志文件的位置OPB_SRC:INFORMATICA DESIGNER中所定义的所有源例如：SRC_ID: 12SUBJ_ID: 27FILE_NAME: AM_EQP_ASSESSSOURCE_NAME: AM_EQP_ASSESS用途：通过Subj_Id的关联，可以查出每个Folder中所有定义了的源OPB_SRC_FLD:INFORMATICA中源表的所有字段的定义例如：FLDID: 82SRC_ID: 12SRC_NAME: FLAG_ID用途：关联上表，得出该源表的所有字段，及其定义和相关属性值OPB_SRV_LOC_VARS:INFORMATICA系统服务器配置中，所有的系统变量及变量的当前值例如：VAR_ID: 13VAR_NAME: $PMRootDirVAR_VALUE: D:Program FilesInformatica PowerCenter用途：查看当前服务器的所有系统变量及其当前值OPB_SUBJECT:INFORMATICA中所有主题定义，即所有Folder的定义及相关属性例如：SUBJ_NAME: OAMSUBJ_ID: 2GROUP_ID: 3用途：Folder的ID是其他很多表的外键，作为其他表的关联，可以查看该Folder下的所有相关对象信息OPB_SWIDGET_INST:记录一个Session中所用到的Mapping引用到的所有对象及其相关属性，即细到每个转化模块一条记录例如：SESSION_ID: 11MAPPING_ID: 3INSTANCE_NAME: LKP_OTHER_CHECK11PARTITION_TYPE: 1用途：查看每个Session所引用到的所有对象，及其当前的属性值OPB_SWIDGINST_LOG:INFORMATICA运行后，所有运行了的Session中相关源及目标对象的运行日志，即运行的时间，抽取的数据成功条数等例如：TASK_INSTANCE_ID: 92PARTITION_ID: 1PARTITION_NAME: Partition #1WIDGET_NAME: SQ_SHIFT_CODEAPPLIED_ROWS: 723START_TIME: 2004-11-4 8:48:12END_TIME: 2004-11-4 8:48:31用途：这是INFORMATICA运行后，对每个对象的运行情况的最详细的日志记录，对于数据正确性的检查，性能的调优等有着很重要的参考价值OPB_SWIDG_GROUP:在INFORMATICA DESIGNER 中Union_Transformation模块上的所有Group的定义表例如：SESSION_ID: 1410GROUP_NAME: PM_GROUP1用途：该表单独记录了Union_Transformation模块上所有设置了的Group，可以通过关联查出一个Session上所有的Union Group定义OPB_TABLE_GROUP:在INFORMATICA DESIGNER 中Router Transformation模块上的所有Group的定义表.例如：OBJECT_ID: 3409ATTR_VALUE: FROM_ID = 'xx'用途：该表单独记录了Router Transformation模块上所有设置了的Group，以及Group的分组条件，可以通过关联查处出一个Mapping中Route r的所有分组设置及其分组条件OPB_TARG:在INFORMATICA DESIGNER 中所有目标表的定义OPB_TABLE_GROUP:在INFORMATICA DESIGNER 中Router Transformation模块上的所有Group的定义表.例如：OBJECT_ID: 3409ATTR_VALUE: FROM_ID = 'xx'用途：该表单独记录了Router Transformation模块上所有设置了的Group，以及Group的分组条件，可以通过关联查处出一个Mapping中Route r的所有分组设置及其分组条件OPB_TARG:在INFORMATICA DESIGNER 中所有目标表的定义例如：TARGET_ID: 3SUBJ_ID: 2TARGET_NAME: HAM_DEPT用途：该表存储了所有的目标表定义，通过关联可以查出某个Folder下所有的目标表定义OPB_TARGINDEX:在INFORMATICA中对目标表，可进行Index的定义，该表存储了所有目标表Index的定义例如：TARGET_ID: 1626INDEXNAME: IDX_AUDIT用途：查出所有在INFORMATICA中进行的Index定义，及相关目标表信息OPB_TARGINDEXFLD:INFORMATICA中目标表上进行了Index定义的相关的所有字段例如：INDEXID: 6FLDNAME: AREC_BILL_ID用途：进行关联查出在INFORMATICA中进行了Index定义的表及其字段OPB_TARG_FLD:INFORMATICA中所有目标表的字段信息例如：TARGET_ID: 131TARGET_NAME: CHECK_PROPERTY用途：查看目标表的所有字段信息，或给出字段名，查找该字段在那些目标表中出现过OPB_TASK:WORKFLOW中所有Task的记录，包括Session，Worklet，WorkFlow等例如：TASK_ID: 1717TASK_NAME: s_OAM_LOG_ARR用途：该表是Workflow关于Task的记录的主表，通过关联可以查出某个folder下所包含的所有Workflow、Worklet、Task等，以及查出一个Workflow下的所有TaskOPB_TASK_ATTR:该表记录了Task的所有属性值，每个属性一条记录例如：ATTR_ID: 2ATTR_VALUE:用途：查看相关Task的属性设置，查找系统中同一属性设置的所有TaskOPB_TASK_INST:Task实例表，与OPB_TASK表信息类似，但该表主要突出的是Workflow与Task的关系，而OPB_TASK表是Task的基表例如：WORKFLOW_ID: 9INSTANCE_NAME: s_USED_KIND用途：查找一个Workflow下的所有Task信息OPB_TASK_INST_RUN:该表记录了所有Task每次运行的日志信息，包括当前的运行起始时间，服务名等例如：INSTANCE_NAME: s_ASSET_ACCOUNTSTART_TIME: 2004-11-3 15:20:01END_TIME: 2004-11-3 15:20:08SERVER_NAME: ETL-SVR用途：该表记录了Task每次运行的日志信息，其中关于时间的信息对于性能调优有着极其重要的作用，也可以观察同一个Task，一段时间的运行效果，评估服务器的运行情况等OPB_TASK_VAL_LIST:该表记录了某些Task中的属性值，例如Command Task中的Command值例如：TASK_ID: 2990PM_VALUE: DEL “D:”VAL_NAME: DELETE用途：可以查看当前系统中设置的任务属性值，也可查看所有的Command的命令值OPB_USERS:该表记录了Rep Manager中所设置的所有用户，及其相关属性例如：USER_ID: 5USER_NAME: DEMOUSER_PASSWD: hG63"4.`USER_PRIVILEGES1: 79用途：可以查看系统中INFORMATICA所定义了的所有用户及相关属性OPB_USER_GROUPS:该表记录了Rep Manager中用户与组的关系例如：USER_ID: 2GROUP_ID: 3用途：查看一个组中存在哪些用户，或关联出每个用户到底属于哪个组。
informatica_powercenter 资料库元数据查询——Informatica PowerCenter培训系列TABLE OF CONTENTS1 Overview2 FOLDER2.1 List folder details2.2 List of shared folders2.3 List of Users and Groups having Privileges on Folders3 SOURCE3.1 List of source tables3.2 List and count of tables in each folder by db type3.3 List and count of tables overall used3.4 List of source tables used in mappings3.5 List of Sources tables using as Shortcuts4 TARGET4.1 List of Target Tables4.2 List and count of tables in each folder by db type4.3 List and count of table overall used5 TRANSFORMATION5.1 List of filer transformations5.2 List of Sequence transformations5.3 List of tables used as lookups5.4 List of transformations using sql overrides5.5 List all transformations5.6 List all Expression transformations using ‘concat’ function 5.7 List of all port details of an Expression transformations 5.8 List of all Expression transformation port links5.9 List of LKP transformation port links used in mappings6 MAPPING6.1 List mapping names6.2 List total count of mappings6.3 List last saved user for a mapping6.4 List Mapping parameters and variables6.5 List all Mappings using PARALLEL hints7 MAPPLET7.1 List Mapplets in all folders7.2 List Mapplet parameters and variables8 SESSION8.1 List session names8.2 List save session log count8.3 List stop on errors count8.4 List hardcoded paths8.5 List parameter file paths8.6 List session log names8.7 List commit intervals8.8 List total source partitions8.9 List total target partitions8.10 List DTM Buffer Size8.11 List collect performance data8.12 List Incremental Aggregation8.13 List Reinitialize aggregate cache8.14 List Enable high precision8.15 List Session retry on deadlock8.16 List write backward compatible check8.17 List over ride tracing8.18 List save session log by8.19 List load type8.20 List 'post_session_success_command' in session8.21 List of all emails with attachment8.22 List Invalid Sessions and Workflows9 TASKS9.1 List command tasks9.2 List decision tasks9.3 List Event Wait tasks10 WORKLET10.1 List worklet names10.2 List hierarchies of all workflows and its worklets11 WORKFLOW11.1 List workflow names11.2 List save workflow log count11.3 List workflow log names11.4 List write backward compatible check11.5 List fail_parent_if_task_fails objects11.6 List fail_parent_if_task_dont_run objects11.7 List is_task_enabled objects11.8 List treat_input_links_as objects11.9 List all workflows whose server is not assigned11.10 List of workflow run details12 CONNECTIONS12.1 List of cnxs using alter in env sql12.2 List of cnxs used in session levels12.3 List Lotus connection details12.4 ODBC / SQL Server Connection details12.5 List of sessions used by a connection12.6 List all Connections with User and Privileges13 REPOSITORY13.1 Repository Info13.2 List of objects which are Not Valid13.3 List of objects which are failed in last 5 days13.4 List where all a table is used13.5 List all source and target tables of mapping13.6 List comments of all object14 MISLENIOUS14.1 Query to find list of objects saved by last user15 GROUPS & USERS15.1 List User, Groups and status1OVERVIEWBelow Steps are intended for informatica development team to check if their etl code is as per ETL Standards’, developer team need to have read onl y access to informatica repository tables and Views. Please suffix your respective schema names for all your table / views names in below queries.2FOLDER2.1LIST FOLDER DETAILSSELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY1,22.2LIST OF SHARED FOLDERSSELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECTWHERE IS_SHARED <>0ORDER BY1,22.3LIST OF USERS AND GROUPS HAVING PRIVILEG ES’ ON FOLDERSSELECT subj.subj_name folder_name, user_ user_name,DECODE (obj_er_type,1,'USER',2,'GROUP')TYPE,CASE WHEN((obj_access.permissions -(obj_er_id +1))IN(8,16))THEN'READ'WHEN((obj_access.permissions -(obj_er_id +1))IN(10,20))THEN'READ & EXECUTE' WHEN((obj_access.permissions -(obj_er_id +1))IN(12,24))THEN'READ & WRITE'WHEN((obj_access.permissions -(obj_er_id +1))IN(14,28))THEN'READ, WRITE & EXECUTE' ELSE'NO PERMISSIONS'END permissionsFROM opb_object_access obj_access,opb_subject subj,opb_user_group user_groupWHERE obj_access.object_type =29AND obj_access.object_id = subj.subj_idAND obj_er_id = user_group.IDAND obj_er_type = user_group.TYPE-- and user_ not in ('Admin','READ_ONLY','Administrator','Administrators')order by1,2,33SOURCE3.1LIST OF SOURCE TABLESSELECTB.SUBJ_NAME,C.DBDNAM,D.DBTYPE_NAME,A.SOURCE_NAME AS TABLE_NAME,A.FILE_NAME SCHEMA_NAME,A.OWNERNAMEFROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEORDER BY1,2,3,4,53.2LIST AND COUNT OF TABLES IN EACH FOLDER BY DB TYPESELECTB.SUBJ_NAME,D.DBTYPE_NAME,count(*)FROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEgroup by B.SUBJ_NAME,D.DBTYPE_NAMEorder by1,2,33.3LIST AND COUNT OF TABLES OVERALL USEDSELECT SOURCE_NAME, COUNT(SOURCE_NAME)FROM REP_TBL_MAPPING GROUP BY SOURCE_NAMEORDER BY1,2ASC3.4LIST OF SOURCE TABLES USED IN MAPPINGSELECT SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING ORDER BY 1,2,33.5LIST OF SOURCE TABLES USING AS SHORTCUTSSELECT DISTINCTB.SUBJ_NAME,C.DBDNAM,D.DBTYPE_NAME,A.SOURCE_NAME AS TABLE_NAME,A.FILE_NAME SCHEMA_NAME,A.OWNERNAMEFROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEand A.SOURCE_NAME like'sc_%'ORDER BY1,2,3,4,54TARGET4.1LIST OF TARGET TABLESSELECT B.SUBJ_NAME,A.TARGET_NAME,DECODE(A.DBTYPE,0,'VSAM',1,'IMS',2,'Sybase',3,'Oracle',4,'Informix',5,'Microsoft SQL Server',6,'DB2',7,'Flat File',8,'ODBC',9,'SAP BW',10,'PeopleSoft',11,'SAP R/3',12,'XML',13,'MQSeries',14,'Siebel',15,'Teradata')as DB_TYPEFROMOPB_TARG A,OPB_SUBJECT BWHERE A.SUBJ_ID = B.SUBJ_IDORDER BY1,2,34.2LIST AND COUNT OF TABLES IN EACH FOLDER BY DB TYPE SELECT B.SUBJ_NAME,DECODE(A.DBTYPE,0,'VSAM',1,'IMS',2,'Sybase',3,'Oracle',4,'Informix',5,'Microsoft SQL Server',6,'DB2',7,'Flat File',8,'ODBC',9,'SAP BW',10,'PeopleSoft',11,'SAP R/3',12,'XML',13,'MQSeries',14,'Siebel',15,'Teradata')as DB_TYPE,count(*)FROMOPB_TARG A,OPB_SUBJECT BWHERE A.SUBJ_ID = B.SUBJ_IDGROUP BY B.SUBJ_NAME,A.DBTYPEORDER BY1,24.3LIST AND COUNT OF TABLE OVERALL USEDSELECT SOURCE_NAME, COUNT(SOURCE_NAME)FROM REP_TBL_MAPPINGGROUP BY SOURCE_NAMEORDER BY1,2ASC5TRANSFORMATION5.1LIST OF FILER TRANSFORMATIONSSELECT SUBSTR(WIDGET_NAME,1,3), COUNT(WIDGET_NAME)FROM REP_ALL_TRANSFORMSWHERE WIDGET_TYPE_NAME ='Filter'GROUP BY SUBSTR(WIDGET_NAME,1,3)5.2LIST OF SEQUENCE TRANSFORMATIONSSELECT DISTINCT SUBJECT_AREA, PARENT_WIDGET_NAME FROM REP_ALL_TRANSFORMS WHERE WIDGET_TYPE _NAME ='Sequence'ORDER BY1,25.3LIST OF TABLES USED AS LOOKUPSSELECT DISTINCTB.PARENT_SUBJECT_AREA AS FOLDER_NAME,C.ATTR_VALUE AS TABLE_NAME,A.INSTANCE_NAME AS TRANSFORMATION_NAME, A.WIDGET_TYPE_NAME AS T RANSFORMATION_TYPE,B.MAPPING_NAMEFROMREP_WIDGET_INST A INNER JOIN REP_ALL_MAPPINGS B ON A.MAPPING_ID = B.MAPPING_ID INNER JOINREP_WIDGET_ATTR C ON A.WIDGET_ID = C.WIDGET_IDWHEREC.ATTR_DESCRIPTION LIKE'Lookup source table'ORDER BY1,2,3,4,55.4LIST OF TRANSFORMATIONS USING SQL OVERRIDESSELECT DISTINCTd.subject_area AS Folder, d.mapping_name, a.widget_type_name AS Transformation_Type,a.instance_name as Transformation_Name,b.attr_name, b.attr_value,c.session_nameFROMREP_WIDGET_INST a, REP_WIDGET_ATTR b, REP_LOAD_SESSIONS c, REP_ALL_MAPPINGS dWHERE b.widget_id = a. widget_idAND b.widget_type = a. widget_typeAND b.widget_type in(3,11)AND c.mapping_id = a.mapping_idAND d.mapping_id = a.mapping_idAND b.attr_id=1AND b.attr_datatype=2and b.attr_type=3ORDER BY d.subject_area, d.mapping_name5.5LIST ALL TRANSFORMATIONSSELECT DISTINCT version_subject.subject_area "FOLDER_NAME",version_props.object_name "OBJECT_NAME" ,CASEWHEN version_props.object_type =1THEN'Source Definition'ELSE CASEWHEN version_props.object_type =2THEN'Target Definition'ELSE CASEWHEN version_props.object_type =3THEN'Source Qualifier'ELSE CASEWHEN version_props.object_type =4THEN'Update Strategy'ELSE CASEWHEN version_props.object_type =5THEN'Expression'ELSE CASEWHEN version_props.object_type =6THEN'Stored Procedure'ELSE CASEWHEN version_props.object_type =7THEN'Sequence'ELSE CASEWHEN version_props.object_type =8THEN'External Procedure'ELSE CASEWHEN version_props.object_type =9THEN'Aggregator'ELSE CASEWHEN version_props.object_type =10THEN'Filter'ELSE CASEWHEN version_props.object_type =11THEN'Lookup Procedure'ELSE CASEWHEN version_props.object_type =12THEN'Joiner'ELSE CASEWHEN version_props.object_type =13THEN'Procedure'ELSE CASEWHEN version_props.object_type =14THEN'Normalizer'ELSE CASEWHEN version_props.object_type =16THEN'Merger'ELSE CASEWHEN version_props.object_type =17THEN'Pivot'ELSE CASEWHEN version_props.object_type =18THEN'Session Obsolete'ELSE CASEWHEN version_props.object_type =19THEN'Batch'ELSE CASEWHEN version_props.object_type =20THEN'Shortcut'ELSE CASEWHEN version_props.object_type =21THEN'Mapping'ELSE CASEWHEN version_props.object_type =26THEN'Rank'ELSE CASEWHEN version_props.object_type =27THEN'Star Schema'ELSE CASEWHEN version_props.object_type =28THEN'Folder Version'ELSE CASEWHEN version_props.object_type =29THEN'Folder'ELSE CASEWHEN version_props.object_type =30THEN'Cube'ELSE CASEWHEN version_props.object_type =31THEN'Dimension'ELSE CASEWHEN version_props.object_type =32THEN'Level'ELSE CASEWHEN version_props.object_type =33THEN'Hierarchy'ELSE CASEWHEN version_props.object_type =34THEN'Fact Table'ELSE CASEWHEN version_props.object_type =35THEN'General Object'ELSE CASEWHEN version_props.object_type =36THEN'FTP Object'ELSE CASEWHEN version_props.object_type =37THEN'Oracle External Loader Object'ELSE CASEWHEN version_props.object_type =38THEN'Informix External Loader Object'ELSE CASEWHEN version_props.object_type =39THEN'Sybase IQ External Loader Object'ELSE CASEWHEN version_props.object_type =54THEN'Sybase IQ 12 External Loader Object'ELSE CASE WHEN version_props.object_type =53THEN'Tera Data External Loader Object'ELSE CASEWHEN version_props.object_type =40THEN'File Object'ELSE CASEWHEN version_props.object_type =41THEN'Server Object'ELSE CASEWHEN version_props.object_type =42THEN'Database Object'ELSE CASE WHEN version_props.object_type =43THEN'Repository'ELSE CASEWHEN version_props.object_type =44THEN'Mapplet'ELSE CASE WHEN version_props.object_type =45THEN'Application Source Qualifier' ELSE CASE WHEN version_props.object_type =46THEN'Input Transformation'ELSE CASE WHEN version_props.object_type =47THEN'Output Transformation'ELSE CASE WHEN version_props.object_type =50THEN'Advanced External Procedure'ELSE CASE WHEN version_props.object_type =48THEN'Business Component Framework'ELSE CASE WHEN version_props.object_type =49THEN'Business Component'ELSE CASE WHEN version_props.object_type =51THEN'SAP Structure'ELSE CASEWHEN version_props.object_type =52THEN'SAP Function'ELSE CASEWHEN version_props.object_type =15THEN'Router'ELSE CASE WHEN version_props.object_type =55THEN'XML Source Qualifier'ELSE CASE WHEN version_props.object_type =56THEN'MQ Source Qualifier'ELSE CASE WHEN version_props.object_type =57THEN'MQ Connection Object'ELSE CASE WHEN version_props.object_type =58THEN'Command'ELSE CASEWHEN version_props.object_type =59THEN'Decision'ELSE CASEWHEN version_props.object_type =60THEN'Event Wait'ELSE CASEWHEN version_props.object_type =61THEN'Event Raise'ELSE CASEWHEN version_props.object_type =62THEN'Start'ELSE CASEWHEN version_props.object_type =63THEN'Abort'ELSE CASE WHEN version_props.object_type =64THEN'Stop'ELSE CASEWHEN version_props.object_type =65THEN'Email'ELSE CASE WHEN version_props.object_type =66THEN'Timer'ELSE CASE WHEN version_props.object_type =67THEN'Assignment'ELSE CASEWHEN version_props.object_type =68THEN'Session'ELSE CASE WHEN version_props.object_type =69THEN'Scheduler'ELSE CASEWHEN version_props.object_type =70THEN'Worklet'ELSE CASEWHEN version_props.object_type =71THEN'Workflow'ELSE CASEWHEN version_props.object_type =72THEN'SessionConfig'ELSE CASEWHEN version_props.object_type =73THEN'Relational'ELSE CASEWHEN version_props.object_type =74THEN'Application'ELSE CASEWHEN version_props.object_type =75THEN'FTP'ELSE CASEWHEN version_props.object_type =76THEN'External Loader'ELSE CASE WHEN version_props.object_type =77THEN'Queue'ELSE CASE WHEN version_props.object_type =78THEN'Reader'ELSE CASE WHEN version_props.object_type =79THEN'Writer'ELSE CASE WHEN version_props.object_type =80THEN'Sorter'ELSE CASE WHEN version_props.object_type =81THEN'Vendor'ELSE CASE WHEN version_props.object_type =84THEN'App Multi-Group Source Qualifier'ELSE CASE WHEN version_props.object_type =91THEN'Control'ELSE CASE WHEN version_props.object_type =92THEN'Transaction Control'ELSE CASEWHEN version_props.object_type =97THEN'Custom Transformation'ELSE CASEWHEN version_props.object_type =93THEN'Query'ELSE CASEWHEN version_props.object_type =94THEN'Deployment Group'ELSE CASEWHEN version_props.object_type =95THEN'Label'ELSE CASEWHEN version_props.object_type =96THEN'Deployed Deployment Group'ELSE CASEWHEN version_props.object_type =98THEN'Server Grid'ELSE CASEWHEN version_props.object_type =99THEN'Profiling Ruleset'ELSE CASEWHEN version_props.object_type =100THEN'Template Extension'ELSE CASEWHEN version_props.object_type =101THEN'Global Profile Resource'ELSE CASEWHEN version_props.object_type =102THEN'Web Services Hub'ELSE CASEWHEN version_props.object_type =103THEN'Lookup Extension'ELSE CASEWHEN version_props.object_type =105THEN'Service Level'ELSE CASEWHEN version_props.object_type =106THEN'User Defined Function'ELSE'Shortcut'END END END END END END END END END END EN D END END END END END END END END END ENDEND END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END E ND END END END END END END END END END END END END END END END END END END END END END END END END END END ENDEND"OBJECT_TYPE"FROM rep_users version_users,rep_version_props version_props,rep_reposit_info version_reposit_info,rep_subject version_subjectWHERE(version_er_id = version_er_idAND version_props.object_id <> version_reposit_info.repository_idAND version_props.subject_id = version_subject.subject_id)ORDER BY3,15.6LIST ALL EXPRESSION TRANSFORMATIONS USIN G ‘CONCAT’ FUNCTIONSELECT DISTINCT REP_ALL_MAPPINGS.SUBJECT_AREA, REP_ALL_MAPPINGS.MAPPING_NAME,REP_ WIDGET_INST.WIDGET_TYPE_NAME AS TRANSFORMATION_TYPE,REP_WIDGET_INST.INSTANCE_NA ME AS TRANSFORMATION_NAME,REP_WIDGET_FIELD.FIELD_NAME AS PORT_NAME,CASEWHEN REP_WIDGET_FIELD.PORTTYPE =1THEN'I'WHEN REP_WIDGET_FIELD.PORTTYPE =2THEN'O'WHEN REP_WIDGET_FIELD.PORTTYPE =3THEN'IO'WHEN REP_WIDGET_FIELD.PORTTYPE =32THEN'V'END AS PORT_TYPE,REP_WIDGET_FIELD.EXPRESSIONFROM REP_WIDGET_INST, REP_WIDGET_FIELD, REP_ALL_MAPPINGSWHERE REP_WIDGET_INST.WIDGET_ID = REP_WIDGET_FIELD.WIDGET_IDAND REP_WIDGET_INST.MAPPING_ID = REP_ALL_MAPPINGS.MAPPING_IDAND REP_WIDGET_INST.WIDGET_TYPE =5AND REP_WIDGET_FIELD.EXPRESSION LIKE'%CONCAT%'ORDER BY15.7LIST OF ALL PORT DETAILS OF AN EXPRESSION TRANSFORMATIONSSELECT S.SUBJ_NAME, W.WIDGET_NAME, F.FIELD_ID,F.FIELD_NAME,E.VERSION_NUMBER, E.EXPRESSION FROM OPB_WIDGET W,OPB_SUBJECT S,OPB_WIDGET_FIELD F,OPB_WIDGET_EXPR R,OPB_EXPRESSION EWHERE W.SUBJECT_ID=S.SUBJ_ID AND W.WIDGET_ID=F.WIDGET_IDAND W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_IDAND W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_IDAND W.VERSION_NUMBER = F.VERSION_NUMBERAND F.VERSION_NUMBER = R.VERSION_NUMBERAND R.VERSION_NUMBER = E.VERSION_NUMBERAND W.IS_VISIBLE =1AND W.WIDGET_NAME LIKE'EXP_%'ORDER BY1,2,35.8LIST OF ALL EXPRESSION TRANSFORMATION PORT LINKSSELECT DISTINCT S.SUBJ_NAME, WF.INSTANCE_NAME ||'.'|| F.FIELD_NAMEFROM_NAME, F.FIELD_ORDER AS EXP_PORT_ORDER,WT.INSTANCE_NAME ||'.'|| T.FIELD_NAME TO_NAME,T.FIELD_ORDERFROM OPB_WIDGET Z,OPB_WIDGET_INST WF,OPB_WIDGET_INST WT,OPB_WIDGET_FIELD F,OPB_WIDGET_FIELD T,OPB_WIDGET_DEP D,OPB_SUBJECT SWHERE Z.SUBJECT_ID = S.SUBJ_IDAND Z.IS_VISIBLE =1AND Z.WIDGET_ID = F.WIDGET_IDAND Z.WIDGET_ID = WF.WIDGET_IDAND Z.RU_VERSION_NUMBER = WF.VERSION_NUMBERAND WF.REF_VERSION_NUMBER = F.VERSION_NUMBERAND WF.VERSION_NUMBER = D.VERSION_NUMBERAND WF.MAPPING_ID = D.MAPPING_IDAND WF.INSTANCE_ID = D.FROM_INSTANCE_IDAND F.FIELD_ID = D.FROM_FIELD_IDAND D.TO_INSTANCE_ID = WT.INSTANCE_IDAND D.TO_FIELD_ID = T.FIELD_IDAND D.MAPPING_ID = WT.MAPPING_IDAND D.VERSION_NUMBER = WT.VERSION_NUMBERAND WT.WIDGET_ID = T.WIDGET_IDAND WT.REF_VERSION_NUMBER = T.VERSION_NUMBER--AND Z.WIDGET_NAME LIKE 'EXP_%'AND S.SUBJ_NAME =:FOLDER_NAMEAND WF.INSTANCE_NAME =:EXP_NAMEORDER BY1,2,35.9LIST OF LKP TRANSFORMATION PORT LINKS USED IN ALL MAPPINGSSELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_MAPPING.MAPPING_NAME,OPB_WIDGET_FIELD.FIELD_NAME FIELD_NAME,OPB_EXPRESSION.EXPRESSION EXPRESSIONFROM OPB_WIDGET_EXPR,OPB_EXPRESSION,OPB_WIDGET_FIELD,REP_FLD_DATAT YPE,OPB_WIDGET,OPB_SUBJECT,OPB_WIDGET_INST,OPB_MAPPINGWHEREOPB_WIDGET_FIELD.WIDGET_ID =OPB_WIDGET.WIDGET_IDAND OPB_WIDGET.SUBJECT_ID =OPB_SUBJECT.SUBJ_IDAND OPB_WIDGET_INST.WIDGET_ID =OPB_WIDGET.WIDGET_IDAND OPB_MAPPING.MAPPING_ID =OPB_WIDGET_INST.MAPPING_IDAND OPB_WIDGET_FIELD.VERSION_NUMBER =OPB_WIDGET.VERSION_NUMBERAND OPB_WIDGET.IS_VISIBLE =1AND OPB_WIDGET_FIELD.WIDGET_ID=OPB_WIDGET_EXPR.WIDGET_IDAND OPB_WIDGET_FIELD.FIELD_ID=OPB_WIDGET_EXPR.OUTPUT_FIELD_IDAND OPB_WIDGET_EXPR.WIDGET_ID=OPB_EXPRESSION.WIDGET_IDAND OPB_WIDGET_EXPR.EXPR_ID=OPB_EXPRESSION.EXPR_IDAND OPB_EXPRESSION.LINE_NO =1AND OPB_WIDGET_EXPR.VERSION_NUMBER =OPB_EXPRESSION.VERSION_NUMBER AND OPB_WIDGET_EXPR.VERSION_NUMBER =OPB_WIDGET_FIELD.VERSION_NUMBER --AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'--AND OPB_MAPPING.MAPPING_NAME = 'MAPPING_NAME'AND UPPER(EXPRESSION)LIKE'%LKP_ACCT_B%'ORDER BY1,26MAPPING6.1LIST MAPPING NAMESSELECT SUBJECT_AREA, PARENT_MAPPING_NAMEFROM REP_ALL_MAPPINGSORDER BY1,26.2LIST TOTAL COUNT OF MAPPINGSSELECT SUBJECT_AREA, COUNT(PARENT_MAPPING_NAME)AS TOTAL_MAPPINGSFROM REP_ALL_MAPPINGSGROUP BY SUBJECT_AREAORDER BY1,26.3LIST LAST SAVED USER FOR A MAPPINGSELECT REP_SUBJECT.SUBJECT_AREA "FOLDER",REP_VERSION_PROPS.OBJECT_NAME"MAPPING",REP_ ER_NAME,REP_VERSION_ST_SAVEDFROM REP_USERS,REP_VERSION_PROPS,REP_SUBJECTWHERE REP_ER_ID=REP_VERSION_ER_IDAND REP_VERSION_PROPS.OBJECT_TYPE IN(21)--AND REP_SUBJECT.SUBJECT_AREA ='FOLDER_NAME'AND REP_SUBJECT.SUBJECT_ID = REP_VERSION_PROPS.SUBJECT_IDORDER BY1,2,3,46.4LIST MAPPING PARAMETERS AND VARIABLESselect distinct rep_reposit_info.repository_name, rep_all_mappings.subject_areaas folder_name, rep_all_mappings.mapping_name as object_name,case when opb_map_parmvar.pv_flag =2then 'Mapping Parameter' elsecase when opb_map_parmvar.pv_flag =3then 'Mapping Variable' endend as parameter_type,opb_map_parmvar.pv_name as parameter_name,opb_map_parmvar.pv_default as parameter_value,opb_map_parmvar.pv_desc as descriptionfrom rep_all_mappings,opb_map_parmvar, rep_reposit_infowhere rep_all_mappings.mapping_id =opb_map_parmvar.mapping_id6.5LIST ALL THE MAPPINGS USING PARALLEL HINTSSELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE,1,60) ATTR_VALUEFROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M WHERE A.WIDGET_ID = W.WIDGET_IDAND W.IS_VISIBLE =1AND A.VERSION_NUMBER = W.VERSION_NUMBERAND A.WIDGET_TYPE IN(2,3,11)--Limit to Src/Tgt/Lkp TransformationsAND W.WIDGET_ID = I.WIDGET_IDAND W.VERSION_NUMBER = I.VERSION_NUMBERAND I.MAPPING_ID = M.MAPPING_IDAND I.VERSION_NUMBER = M.VERSION_NUMBERAND W.SUBJECT_ID = S.SUBJ_IDAND UPPER(A.ATTR_VALUE)LIKE'%PARALLEL%'7MAPPLET7.1LIST MAPPLETS IN ALL FOLDERSselect subject_area,mapplet_name from rep_all_mappletsorder by 1,27.2LIST MAPPLET PARAMETERS AND VARIABLESselect distinct rep_reposit_info.repository_name, rep_all_mapplets.subject_areaas folder_name, rep_all_mapplets.mapplet_name as object_name,case when opb_map_parmvar.pv_flag =2then'Mapplet Parameter' elsecase when opb_map_parmvar.pv_flag =3then'Mapplet Variable' endend as parameter_type,opb_map_parmvar.pv_name as parameter_name,opb_map_parmvar.pv_default as parameter_value,opb_map_parmvar.pv_desc as descriptionfrom rep_all_mapplets, rep_widget_inst,opb_mapping,opb_map_parmvar, rep_reposit_info where rep_all_mapplets.mapplet_id=opb_mapping.mapping_idand rep_widget_inst.widget_id=opb_mapping.ref_widget_idand opb_mapping.mapping_id=opb_map_parmvar.mapping_idand rep_widget_inst.widget_type=448SESSION8.1LIST SESSION NAMESSELECT SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME FROM REP_ALL_TASKSWHERE TASK_TYPE IN(68)--AND SUBJECT_AREA= 'ABC'ORDER BY1,2,38.2LIST SAVE SESSION LOG COUNTselect distinct cc.subject_area,cc.task_name as session_name,bb.attr_value as Savesessionlog from(select a.session_id,min(a.config_id)as config_id,a.attr_id from rep_sess_config_parm a where a.attr_id ='103' group by a.session_id,a.attr_id) aa,(select session_id,config_id,attr_value from rep_sess_config_parmwhere attr_id ='103') bb,(select subject_area,task_name,task_id from rep_all_tasks ) ccwhere aa.session_id = bb.session_idand aa.config_id=bb.config_idand bb.session_id = cc.task_idand bb.attr_value not in(8,4)order by1,2,38.3LIST STOP ON ERRORS COUNTSELECT DISTINCTA.SUBJECT_AREA,A.TASK_NAME AS SESSION_NAME,B.ATTR_VALUE AS STOPONERRORSFROMREP_ALL_TASKS A ,REP_SESS_CONFIG_PARM BA.TASK_ID =B.SESSION_IDAND TASK_TYPE_NAME ='Session'AND B.ATTR_ID ='202'--AND B.ATTR_VALUE NOT IN (1) --AND A.SUBJECT_AREA in ('ABC') ORDER BY1,28.4LIST HARD CODED PATHSSELECT DISTINCTA.SUBJECT_AREA,A.TASK_NAME AS SESSION_NAME,B.FILE_NAME,DIR_NAMEFROMREP_ALL_TASKS A ,OPB_SESS_FILE_VALS BWHEREA.TASK_TYPE_NAME ='Session'AND A.TASK_ID = B.SESSION_ID--AND A.SUBJECT_AREA IN ('ABC')ORDER BY1,28.5LIST PARAMETER FILE PATHSSELECT DISTINCTB.SUBJECT_AREA,B.TASK_NAME AS SES_WF_NAME,A.ATTR_VALUE AS PRM_FILE_PATHFROMOPB_TASK_ATTR A,REP_ALL_TASKS BWHEREA.ATTR_ID IN(1,4)AND A.TASK_ID = B.TASK_IDAND A.ATTR_VALUE LIKE'%.prm%'ORDER BY1,2ASC8.6List session log namesSELECT DISTINCTA.SUBJECT_AREA,A.WORKFLOW_NAME,A.SESSION_NAME,A.SESSION_INSTANCE_NAME,SUBSTR(A.SESSION_LOG_FILE,25,300)AS EXISTING_SESSLOGNAME FROMREP_SESS_LOG ASUBSTR(A.SESSION_LOG_FILE,25,300)!= CONCAT(LOWER(A.SESSION_INSTANCE_NAME),'.log') ORDER BY1,2,38.7LIST COMMIT INTERVALSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS COMMITINTERVELFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(14)AND A.ATTR_VALUE <>10000AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.8LIST TOTAL SOURCE PARTITIONSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS TOTAL_SOURCE_PARTITIONSFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(12)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.9LIST TOTAL TARGET PARTITIONSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS TOTAL_TARGET_PARTITIONSFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(11)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.10List DTM Buffer SizeSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS DTM_BUFFER_SIZEFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(101)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.11LIST COLLECT PERFORMANCE DATASELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS COLLECT_PERFORMANCE_DATA FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(102)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.12List Incremental AggregationSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS INCREMENTAL_AGGREGATION FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(103)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.13List Reinitialize aggregate cacheSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS REINITIALIZE_AGGREGATE CACHE FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(104)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.14LIST ENABLE HIGH PRECISIONSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS ENABLE_HIGH_PRECISIONFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(105)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.15LIST SESSION RETRY ON DEADLOCKSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS SESSION_ RETRYON_DEADLOCK FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(106)。
Informatica PowerCenter V7.1.2功能特性介绍杨晓东姜炜2005年05月1. S EQUENCE G ENERATOR组件 32. 共享的S EQUENCE G ENERATOR组件 43. 读取VSAM(C OBOL)源文件 54. 利用动态L OOKUP和自定义主键实现目标表的无主键更新 65. 读取E XCEL文件76. 笛卡儿乘积87. M APPING局部变量(LOCAL V ARIABLE) 98. 条件L OOKUP和自定义代理主键109. 调用外部过程(E XTERNAL P ROCEDURE) 1110. XML文件作为数据源和目标1211. U NION组件：合并同结构数据集1312. T RANSACTION组件：自定义事务1413. 读取DBF格式文件1514. 同数据源的自关联1615. 列/行转换1716. 行/列转换+条件汇总功能1817. 增量汇总1918. M APPING的参数/变量和S ESSION的参数 2019. 读文件列表(F ILE L IST) 2520. M APPING D EBUGGER功能2621. W ORKFLOW流程控制2722. 约束性装载2923. S ESSION分区(P ARTITION) 3024. E XTERNAL L OADER3125. 断点续传(S ESSION R ECOVERY) 3326. P RE SQL和P OST SQL调用数据库SQL以及存储过程 3427. M APPING D ESIGNER中提供多种缓慢变化维模板 3528. 对象的导出\导入\知识库的备份和恢复等操作 3629. S ESSION LOG和W ORKFLOW LOG的个数控制 3730. 悬挂(S USPENDING)W ORKFLOW3831. 跟S ESSION性能相关的参数设置3932. MX V IEWS使用4133. PMCMD命令4334. PMREP命令4535. MX FOR D ATA M ODELS561.Sequence Generator组件(参见例子：SequenceGenerator)Sequence Generator组件能产生不断增加的数值，从而可作为代理主键使用。
Informatica元数据库解析（一）Informatica元数据库解析（一）作者: informatica(whyu) @ 03/29 2006, 07:50Informatica所有的元数据信息均以数据库表的方式存到了元数据库中。
当然Infa本身工具提供了很多的人性化的功能，使我们在开发时可以很方便的进行操作，但人们的需求总是万变的，需要方便的取到自己需要的信息，那就需要我们对他的元数据库有很深的了解。
Informatica通过表和视图给我们提供着所有的信息，在此将通过一个系列的帖子，将大部分常见的，且非常有用的表及视图介绍一下。
基于这些东西，我们即可以根据不同的需求查出自己需要的数据，也可以开发一些辅助的Infa应用程序。
/////////////////////////////////////////////////////////////////////////////OPB_ATTR :INFORMATICA (Designer,Workflow等)设计时及服务器设置的所有属性项的名称，当前值及该属性项的简要说明Informatica所有的元数据信息均以数据库表的方式存到了元数据库中。
当然Infa本身工具提供了很多的人性化的功能，使我们在开发时可以很方便的进行操作，但人们的需求总是万变的，需要方便的取到自己需要的信息，那就需要我们对他的元数据库有很深的了解。
Informatica通过表和视图给我们提供着所有的信息，在此将通过一个系列的帖子，将大部分常见的，且非常有用的表及视图介绍一下。
基于这些东西，我们即可以根据不同的需求查出自己需要的数据，也可以开发一些辅助的Infa应用程序。
/////////////////////////////////////////////////////////////////////////////OPB_ATTR :INFORMATICA (Designer,Workflow等)设计时及服务器设置的所有属性项的名称，当前值及该属性项的简要说明例如：ATTR_NAME: Tracing LevelATTR_VALUE: 2ATTR_COMMENT: Amount of detail in the session log用途：可以通过该表快速查看到设计或设置时碰到的一些属性项的用途与说明OPB_ATTR_CATEGORY:INFORMATICA各属性项的分类及说明例如：CATEGORY_NAME: Files and DirectoriesDESCRIPTION: Attributes related to file names and directory locations用途：查看上表所提的属性项的几种分类及说明OPB_CFG_ATTR:WORKFLOW MANAGER中的各个Folder下的Session Configuration的配置数据，每个配置对应表中一组Config_Id相同的数据，一组配置数据共23条例如：ATTR_ID: 221ATTR_VALUE: $PMBadFileDir用途：查看所有的Session Configuration的配置项及值，并方便的进行各个不同Folder间的配置异同比较OPB_CNX:WORKFLOW MANAGER中关于源、目标数据库连接的定义，包括Relational Connection,Queue Connection,Loader Connection等例如：OBJECT_NAME: Orace_SourceUSER_NAME: oralUSER_PASSWORD: `?53S{$+*$*[X]CONNECT_STRING: Oratest用途：查看在WorkFlow Manager中进行配置的所有连接及其配置数据OPB_CNX_ATTR:上表所记录的所有数据库连接的一些相关属性值，一种属性值一条数据。
informatica_powercenter 资料库元数据查询——Informatica PowerCenter培训系列TABLE OF CONTENTS1 Overview2 FOLDER2.1 List folder details2.2 List of shared folders2.3 List of Users and Groups having Privileges on Folders3 SOURCE3.1 List of source tables3.2 List and count of tables in each folder by db type3.3 List and count of tables overall used3.4 List of source tables used in mappings3.5 List of Sources tables using as Shortcuts4 TARGET4.1 List of Target Tables4.2 List and count of tables in each folder by db type4.3 List and count of table overall used5 TRANSFORMATION5.1 List of filer transformations5.2 List of Sequence transformations5.3 List of tables used as lookups5.4 List of transformations using sql overrides5.5 List all transformations5.6 List all Expression transformations using ‘concat’ function 5.7 List of all port details of an Expression transformations 5.8 List of all Expression transformation port links5.9 List of LKP transformation port links used in mappings6 MAPPING6.1 List mapping names6.2 List total count of mappings6.3 List last saved user for a mapping6.4 List Mapping parameters and variables6.5 List all Mappings using PARALLEL hints7 MAPPLET7.1 List Mapplets in all folders7.2 List Mapplet parameters and variables8 SESSION8.1 List session names8.2 List save session log count8.3 List stop on errors count8.4 List hardcoded paths8.5 List parameter file paths8.6 List session log names8.7 List commit intervals8.8 List total source partitions8.9 List total target partitions8.10 List DTM Buffer Size8.11 List collect performance data8.12 List Incremental Aggregation8.13 List Reinitialize aggregate cache8.14 List Enable high precision8.15 List Session retry on deadlock8.16 List write backward compatible check8.17 List over ride tracing8.18 List save session log by8.19 List load type8.20 List 'post_session_success_command' in session8.21 List of all emails with attachment8.22 List Invalid Sessions and Workflows9 TASKS9.1 List command tasks9.2 List decision tasks9.3 List Event Wait tasks10 WORKLET10.1 List worklet names10.2 List hierarchies of all workflows and its worklets11 WORKFLOW11.1 List workflow names11.2 List save workflow log count11.3 List workflow log names11.4 List write backward compatible check11.5 List fail_parent_if_task_fails objects11.6 List fail_parent_if_task_dont_run objects11.7 List is_task_enabled objects11.8 List treat_input_links_as objects11.9 List all workflows whose server is not assigned11.10 List of workflow run details12 CONNECTIONS12.1 List of cnxs using alter in env sql12.2 List of cnxs used in session levels12.3 List Lotus connection details12.4 ODBC / SQL Server Connection details12.5 List of sessions used by a connection12.6 List all Connections with User and Privileges13 REPOSITORY13.1 Repository Info13.2 List of objects which are Not Valid13.3 List of objects which are failed in last 5 days13.4 List where all a table is used13.5 List all source and target tables of mapping13.6 List comments of all object14 MISLENIOUS14.1 Query to find list of objects saved by last user15 GROUPS & USERS15.1 List User, Groups and status1OVERVIEWBelow Steps are intended for informatica development team to check if their etl code is as per ETL Standards’, developer team need to have read onl y access to informatica repository tables and Views. Please suffix your respective schema names for all your table / views names in below queries.2FOLDER2.1LIST FOLDER DETAILSSELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY1,22.2LIST OF SHARED FOLDERSSELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECTWHERE IS_SHARED <>0ORDER BY1,22.3LIST OF USERS AND GROUPS HAVING PRIVILEG ES’ ON FOLDERSSELECT subj.subj_name folder_name, user_ user_name,DECODE (obj_er_type,1,'USER',2,'GROUP')TYPE,CASE WHEN((obj_access.permissions -(obj_er_id +1))IN(8,16))THEN'READ'WHEN((obj_access.permissions -(obj_er_id +1))IN(10,20))THEN'READ & EXECUTE' WHEN((obj_access.permissions -(obj_er_id +1))IN(12,24))THEN'READ & WRITE'WHEN((obj_access.permissions -(obj_er_id +1))IN(14,28))THEN'READ, WRITE & EXECUTE' ELSE'NO PERMISSIONS'END permissionsFROM opb_object_access obj_access,opb_subject subj,opb_user_group user_groupWHERE obj_access.object_type =29AND obj_access.object_id = subj.subj_idAND obj_er_id = user_group.IDAND obj_er_type = user_group.TYPE-- and user_ not in ('Admin','READ_ONLY','Administrator','Administrators')order by1,2,33SOURCE3.1LIST OF SOURCE TABLESSELECTB.SUBJ_NAME,C.DBDNAM,D.DBTYPE_NAME,A.SOURCE_NAME AS TABLE_NAME,A.FILE_NAME SCHEMA_NAME,A.OWNERNAMEFROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEORDER BY1,2,3,4,53.2LIST AND COUNT OF TABLES IN EACH FOLDER BY DB TYPESELECTB.SUBJ_NAME,D.DBTYPE_NAME,count(*)FROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEgroup by B.SUBJ_NAME,D.DBTYPE_NAMEorder by1,2,33.3LIST AND COUNT OF TABLES OVERALL USEDSELECT SOURCE_NAME, COUNT(SOURCE_NAME)FROM REP_TBL_MAPPING GROUP BY SOURCE_NAMEORDER BY1,2ASC3.4LIST OF SOURCE TABLES USED IN MAPPINGSELECT SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING ORDER BY 1,2,33.5LIST OF SOURCE TABLES USING AS SHORTCUTSSELECT DISTINCTB.SUBJ_NAME,C.DBDNAM,D.DBTYPE_NAME,A.SOURCE_NAME AS TABLE_NAME,A.FILE_NAME SCHEMA_NAME,A.OWNERNAMEFROMOPB_SRC A,OPB_SUBJECT B,OPB_DBD C,OPB_MMD_DBTYPE DWHERE A.SUBJ_ID = B.SUBJ_IDAND A.DBDID = C.DBDIDAND C.DBTYPE = D.DBTYPE_ID--AND A.SOURCE_NAME <> A.FILE_NAMEand A.SOURCE_NAME like'sc_%'ORDER BY1,2,3,4,54TARGET4.1LIST OF TARGET TABLESSELECT B.SUBJ_NAME,A.TARGET_NAME,DECODE(A.DBTYPE,0,'VSAM',1,'IMS',2,'Sybase',3,'Oracle',4,'Informix',5,'Microsoft SQL Server',6,'DB2',7,'Flat File',8,'ODBC',9,'SAP BW',10,'PeopleSoft',11,'SAP R/3',12,'XML',13,'MQSeries',14,'Siebel',15,'Teradata')as DB_TYPEFROMOPB_TARG A,OPB_SUBJECT BWHERE A.SUBJ_ID = B.SUBJ_IDORDER BY1,2,34.2LIST AND COUNT OF TABLES IN EACH FOLDER BY DB TYPE SELECT B.SUBJ_NAME,DECODE(A.DBTYPE,0,'VSAM',1,'IMS',2,'Sybase',3,'Oracle',4,'Informix',5,'Microsoft SQL Server',6,'DB2',7,'Flat File',8,'ODBC',9,'SAP BW',10,'PeopleSoft',11,'SAP R/3',12,'XML',13,'MQSeries',14,'Siebel',15,'Teradata')as DB_TYPE,count(*)FROMOPB_TARG A,OPB_SUBJECT BWHERE A.SUBJ_ID = B.SUBJ_IDGROUP BY B.SUBJ_NAME,A.DBTYPEORDER BY1,24.3LIST AND COUNT OF TABLE OVERALL USEDSELECT SOURCE_NAME, COUNT(SOURCE_NAME)FROM REP_TBL_MAPPINGGROUP BY SOURCE_NAMEORDER BY1,2ASC5TRANSFORMATION5.1LIST OF FILER TRANSFORMATIONSSELECT SUBSTR(WIDGET_NAME,1,3), COUNT(WIDGET_NAME)FROM REP_ALL_TRANSFORMSWHERE WIDGET_TYPE_NAME ='Filter'GROUP BY SUBSTR(WIDGET_NAME,1,3)5.2LIST OF SEQUENCE TRANSFORMATIONSSELECT DISTINCT SUBJECT_AREA, PARENT_WIDGET_NAME FROM REP_ALL_TRANSFORMS WHERE WIDGET_TYPE _NAME ='Sequence'ORDER BY1,25.3LIST OF TABLES USED AS LOOKUPSSELECT DISTINCTB.PARENT_SUBJECT_AREA AS FOLDER_NAME,C.ATTR_VALUE AS TABLE_NAME,A.INSTANCE_NAME AS TRANSFORMATION_NAME, A.WIDGET_TYPE_NAME AS T RANSFORMATION_TYPE,B.MAPPING_NAMEFROMREP_WIDGET_INST A INNER JOIN REP_ALL_MAPPINGS B ON A.MAPPING_ID = B.MAPPING_ID INNER JOINREP_WIDGET_ATTR C ON A.WIDGET_ID = C.WIDGET_IDWHEREC.ATTR_DESCRIPTION LIKE'Lookup source table'ORDER BY1,2,3,4,55.4LIST OF TRANSFORMATIONS USING SQL OVERRIDESSELECT DISTINCTd.subject_area AS Folder, d.mapping_name, a.widget_type_name AS Transformation_Type,a.instance_name as Transformation_Name,b.attr_name, b.attr_value,c.session_nameFROMREP_WIDGET_INST a, REP_WIDGET_ATTR b, REP_LOAD_SESSIONS c, REP_ALL_MAPPINGS dWHERE b.widget_id = a. widget_idAND b.widget_type = a. widget_typeAND b.widget_type in(3,11)AND c.mapping_id = a.mapping_idAND d.mapping_id = a.mapping_idAND b.attr_id=1AND b.attr_datatype=2and b.attr_type=3ORDER BY d.subject_area, d.mapping_name5.5LIST ALL TRANSFORMATIONSSELECT DISTINCT version_subject.subject_area "FOLDER_NAME",version_props.object_name "OBJECT_NAME" ,CASEWHEN version_props.object_type =1THEN'Source Definition'ELSE CASEWHEN version_props.object_type =2THEN'Target Definition'ELSE CASEWHEN version_props.object_type =3THEN'Source Qualifier'ELSE CASEWHEN version_props.object_type =4THEN'Update Strategy'ELSE CASEWHEN version_props.object_type =5THEN'Expression'ELSE CASEWHEN version_props.object_type =6THEN'Stored Procedure'ELSE CASEWHEN version_props.object_type =7THEN'Sequence'ELSE CASEWHEN version_props.object_type =8THEN'External Procedure'ELSE CASEWHEN version_props.object_type =9THEN'Aggregator'ELSE CASEWHEN version_props.object_type =10THEN'Filter'ELSE CASEWHEN version_props.object_type =11THEN'Lookup Procedure'ELSE CASEWHEN version_props.object_type =12THEN'Joiner'ELSE CASEWHEN version_props.object_type =13THEN'Procedure'ELSE CASEWHEN version_props.object_type =14THEN'Normalizer'ELSE CASEWHEN version_props.object_type =16THEN'Merger'ELSE CASEWHEN version_props.object_type =17THEN'Pivot'ELSE CASEWHEN version_props.object_type =18THEN'Session Obsolete'ELSE CASEWHEN version_props.object_type =19THEN'Batch'ELSE CASEWHEN version_props.object_type =20THEN'Shortcut'ELSE CASEWHEN version_props.object_type =21THEN'Mapping'ELSE CASEWHEN version_props.object_type =26THEN'Rank'ELSE CASEWHEN version_props.object_type =27THEN'Star Schema'ELSE CASEWHEN version_props.object_type =28THEN'Folder Version'ELSE CASEWHEN version_props.object_type =29THEN'Folder'ELSE CASEWHEN version_props.object_type =30THEN'Cube'ELSE CASEWHEN version_props.object_type =31THEN'Dimension'ELSE CASEWHEN version_props.object_type =32THEN'Level'ELSE CASEWHEN version_props.object_type =33THEN'Hierarchy'ELSE CASEWHEN version_props.object_type =34THEN'Fact Table'ELSE CASEWHEN version_props.object_type =35THEN'General Object'ELSE CASEWHEN version_props.object_type =36THEN'FTP Object'ELSE CASEWHEN version_props.object_type =37THEN'Oracle External Loader Object'ELSE CASEWHEN version_props.object_type =38THEN'Informix External Loader Object'ELSE CASEWHEN version_props.object_type =39THEN'Sybase IQ External Loader Object'ELSE CASEWHEN version_props.object_type =54THEN'Sybase IQ 12 External Loader Object'ELSE CASE WHEN version_props.object_type =53THEN'Tera Data External Loader Object'ELSE CASEWHEN version_props.object_type =40THEN'File Object'ELSE CASEWHEN version_props.object_type =41THEN'Server Object'ELSE CASEWHEN version_props.object_type =42THEN'Database Object'ELSE CASE WHEN version_props.object_type =43THEN'Repository'ELSE CASEWHEN version_props.object_type =44THEN'Mapplet'ELSE CASE WHEN version_props.object_type =45THEN'Application Source Qualifier' ELSE CASE WHEN version_props.object_type =46THEN'Input Transformation'ELSE CASE WHEN version_props.object_type =47THEN'Output Transformation'ELSE CASE WHEN version_props.object_type =50THEN'Advanced External Procedure'ELSE CASE WHEN version_props.object_type =48THEN'Business Component Framework'ELSE CASE WHEN version_props.object_type =49THEN'Business Component'ELSE CASE WHEN version_props.object_type =51THEN'SAP Structure'ELSE CASEWHEN version_props.object_type =52THEN'SAP Function'ELSE CASEWHEN version_props.object_type =15THEN'Router'ELSE CASE WHEN version_props.object_type =55THEN'XML Source Qualifier'ELSE CASE WHEN version_props.object_type =56THEN'MQ Source Qualifier'ELSE CASE WHEN version_props.object_type =57THEN'MQ Connection Object'ELSE CASE WHEN version_props.object_type =58THEN'Command'ELSE CASEWHEN version_props.object_type =59THEN'Decision'ELSE CASEWHEN version_props.object_type =60THEN'Event Wait'ELSE CASEWHEN version_props.object_type =61THEN'Event Raise'ELSE CASEWHEN version_props.object_type =62THEN'Start'ELSE CASEWHEN version_props.object_type =63THEN'Abort'ELSE CASE WHEN version_props.object_type =64THEN'Stop'ELSE CASEWHEN version_props.object_type =65THEN'Email'ELSE CASE WHEN version_props.object_type =66THEN'Timer'ELSE CASE WHEN version_props.object_type =67THEN'Assignment'ELSE CASEWHEN version_props.object_type =68THEN'Session'ELSE CASE WHEN version_props.object_type =69THEN'Scheduler'ELSE CASEWHEN version_props.object_type =70THEN'Worklet'ELSE CASEWHEN version_props.object_type =71THEN'Workflow'ELSE CASEWHEN version_props.object_type =72THEN'SessionConfig'ELSE CASEWHEN version_props.object_type =73THEN'Relational'ELSE CASEWHEN version_props.object_type =74THEN'Application'ELSE CASEWHEN version_props.object_type =75THEN'FTP'ELSE CASEWHEN version_props.object_type =76THEN'External Loader'ELSE CASE WHEN version_props.object_type =77THEN'Queue'ELSE CASE WHEN version_props.object_type =78THEN'Reader'ELSE CASE WHEN version_props.object_type =79THEN'Writer'ELSE CASE WHEN version_props.object_type =80THEN'Sorter'ELSE CASE WHEN version_props.object_type =81THEN'Vendor'ELSE CASE WHEN version_props.object_type =84THEN'App Multi-Group Source Qualifier'ELSE CASE WHEN version_props.object_type =91THEN'Control'ELSE CASE WHEN version_props.object_type =92THEN'Transaction Control'ELSE CASEWHEN version_props.object_type =97THEN'Custom Transformation'ELSE CASEWHEN version_props.object_type =93THEN'Query'ELSE CASEWHEN version_props.object_type =94THEN'Deployment Group'ELSE CASEWHEN version_props.object_type =95THEN'Label'ELSE CASEWHEN version_props.object_type =96THEN'Deployed Deployment Group'ELSE CASEWHEN version_props.object_type =98THEN'Server Grid'ELSE CASEWHEN version_props.object_type =99THEN'Profiling Ruleset'ELSE CASEWHEN version_props.object_type =100THEN'Template Extension'ELSE CASEWHEN version_props.object_type =101THEN'Global Profile Resource'ELSE CASEWHEN version_props.object_type =102THEN'Web Services Hub'ELSE CASEWHEN version_props.object_type =103THEN'Lookup Extension'ELSE CASEWHEN version_props.object_type =105THEN'Service Level'ELSE CASEWHEN version_props.object_type =106THEN'User Defined Function'ELSE'Shortcut'END END END END END END END END END END EN D END END END END END END END END END ENDEND END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END END E ND END END END END END END END END END END END END END END END END END END END END END END END END END END ENDEND"OBJECT_TYPE"FROM rep_users version_users,rep_version_props version_props,rep_reposit_info version_reposit_info,rep_subject version_subjectWHERE(version_er_id = version_er_idAND version_props.object_id <> version_reposit_info.repository_idAND version_props.subject_id = version_subject.subject_id)ORDER BY3,15.6LIST ALL EXPRESSION TRANSFORMATIONS USIN G ‘CONCAT’ FUNCTIONSELECT DISTINCT REP_ALL_MAPPINGS.SUBJECT_AREA, REP_ALL_MAPPINGS.MAPPING_NAME,REP_ WIDGET_INST.WIDGET_TYPE_NAME AS TRANSFORMATION_TYPE,REP_WIDGET_INST.INSTANCE_NA ME AS TRANSFORMATION_NAME,REP_WIDGET_FIELD.FIELD_NAME AS PORT_NAME,CASEWHEN REP_WIDGET_FIELD.PORTTYPE =1THEN'I'WHEN REP_WIDGET_FIELD.PORTTYPE =2THEN'O'WHEN REP_WIDGET_FIELD.PORTTYPE =3THEN'IO'WHEN REP_WIDGET_FIELD.PORTTYPE =32THEN'V'END AS PORT_TYPE,REP_WIDGET_FIELD.EXPRESSIONFROM REP_WIDGET_INST, REP_WIDGET_FIELD, REP_ALL_MAPPINGSWHERE REP_WIDGET_INST.WIDGET_ID = REP_WIDGET_FIELD.WIDGET_IDAND REP_WIDGET_INST.MAPPING_ID = REP_ALL_MAPPINGS.MAPPING_IDAND REP_WIDGET_INST.WIDGET_TYPE =5AND REP_WIDGET_FIELD.EXPRESSION LIKE'%CONCAT%'ORDER BY15.7LIST OF ALL PORT DETAILS OF AN EXPRESSION TRANSFORMATIONSSELECT S.SUBJ_NAME, W.WIDGET_NAME, F.FIELD_ID,F.FIELD_NAME,E.VERSION_NUMBER, E.EXPRESSION FROM OPB_WIDGET W,OPB_SUBJECT S,OPB_WIDGET_FIELD F,OPB_WIDGET_EXPR R,OPB_EXPRESSION EWHERE W.SUBJECT_ID=S.SUBJ_ID AND W.WIDGET_ID=F.WIDGET_IDAND W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_IDAND W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_IDAND W.VERSION_NUMBER = F.VERSION_NUMBERAND F.VERSION_NUMBER = R.VERSION_NUMBERAND R.VERSION_NUMBER = E.VERSION_NUMBERAND W.IS_VISIBLE =1AND W.WIDGET_NAME LIKE'EXP_%'ORDER BY1,2,35.8LIST OF ALL EXPRESSION TRANSFORMATION PORT LINKSSELECT DISTINCT S.SUBJ_NAME, WF.INSTANCE_NAME ||'.'|| F.FIELD_NAMEFROM_NAME, F.FIELD_ORDER AS EXP_PORT_ORDER,WT.INSTANCE_NAME ||'.'|| T.FIELD_NAME TO_NAME,T.FIELD_ORDERFROM OPB_WIDGET Z,OPB_WIDGET_INST WF,OPB_WIDGET_INST WT,OPB_WIDGET_FIELD F,OPB_WIDGET_FIELD T,OPB_WIDGET_DEP D,OPB_SUBJECT SWHERE Z.SUBJECT_ID = S.SUBJ_IDAND Z.IS_VISIBLE =1AND Z.WIDGET_ID = F.WIDGET_IDAND Z.WIDGET_ID = WF.WIDGET_IDAND Z.RU_VERSION_NUMBER = WF.VERSION_NUMBERAND WF.REF_VERSION_NUMBER = F.VERSION_NUMBERAND WF.VERSION_NUMBER = D.VERSION_NUMBERAND WF.MAPPING_ID = D.MAPPING_IDAND WF.INSTANCE_ID = D.FROM_INSTANCE_IDAND F.FIELD_ID = D.FROM_FIELD_IDAND D.TO_INSTANCE_ID = WT.INSTANCE_IDAND D.TO_FIELD_ID = T.FIELD_IDAND D.MAPPING_ID = WT.MAPPING_IDAND D.VERSION_NUMBER = WT.VERSION_NUMBERAND WT.WIDGET_ID = T.WIDGET_IDAND WT.REF_VERSION_NUMBER = T.VERSION_NUMBER--AND Z.WIDGET_NAME LIKE 'EXP_%'AND S.SUBJ_NAME =:FOLDER_NAMEAND WF.INSTANCE_NAME =:EXP_NAMEORDER BY1,2,35.9LIST OF LKP TRANSFORMATION PORT LINKS USED IN ALL MAPPINGSSELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_MAPPING.MAPPING_NAME,OPB_WIDGET_FIELD.FIELD_NAME FIELD_NAME,OPB_EXPRESSION.EXPRESSION EXPRESSIONFROM OPB_WIDGET_EXPR,OPB_EXPRESSION,OPB_WIDGET_FIELD,REP_FLD_DATAT YPE,OPB_WIDGET,OPB_SUBJECT,OPB_WIDGET_INST,OPB_MAPPINGWHEREOPB_WIDGET_FIELD.WIDGET_ID =OPB_WIDGET.WIDGET_IDAND OPB_WIDGET.SUBJECT_ID =OPB_SUBJECT.SUBJ_IDAND OPB_WIDGET_INST.WIDGET_ID =OPB_WIDGET.WIDGET_IDAND OPB_MAPPING.MAPPING_ID =OPB_WIDGET_INST.MAPPING_IDAND OPB_WIDGET_FIELD.VERSION_NUMBER =OPB_WIDGET.VERSION_NUMBERAND OPB_WIDGET.IS_VISIBLE =1AND OPB_WIDGET_FIELD.WIDGET_ID=OPB_WIDGET_EXPR.WIDGET_IDAND OPB_WIDGET_FIELD.FIELD_ID=OPB_WIDGET_EXPR.OUTPUT_FIELD_IDAND OPB_WIDGET_EXPR.WIDGET_ID=OPB_EXPRESSION.WIDGET_IDAND OPB_WIDGET_EXPR.EXPR_ID=OPB_EXPRESSION.EXPR_IDAND OPB_EXPRESSION.LINE_NO =1AND OPB_WIDGET_EXPR.VERSION_NUMBER =OPB_EXPRESSION.VERSION_NUMBER AND OPB_WIDGET_EXPR.VERSION_NUMBER =OPB_WIDGET_FIELD.VERSION_NUMBER --AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'--AND OPB_MAPPING.MAPPING_NAME = 'MAPPING_NAME'AND UPPER(EXPRESSION)LIKE'%LKP_ACCT_B%'ORDER BY1,26MAPPING6.1LIST MAPPING NAMESSELECT SUBJECT_AREA, PARENT_MAPPING_NAMEFROM REP_ALL_MAPPINGSORDER BY1,26.2LIST TOTAL COUNT OF MAPPINGSSELECT SUBJECT_AREA, COUNT(PARENT_MAPPING_NAME)AS TOTAL_MAPPINGSFROM REP_ALL_MAPPINGSGROUP BY SUBJECT_AREAORDER BY1,26.3LIST LAST SAVED USER FOR A MAPPINGSELECT REP_SUBJECT.SUBJECT_AREA "FOLDER",REP_VERSION_PROPS.OBJECT_NAME"MAPPING",REP_ ER_NAME,REP_VERSION_ST_SAVEDFROM REP_USERS,REP_VERSION_PROPS,REP_SUBJECTWHERE REP_ER_ID=REP_VERSION_ER_IDAND REP_VERSION_PROPS.OBJECT_TYPE IN(21)--AND REP_SUBJECT.SUBJECT_AREA ='FOLDER_NAME'AND REP_SUBJECT.SUBJECT_ID = REP_VERSION_PROPS.SUBJECT_IDORDER BY1,2,3,46.4LIST MAPPING PARAMETERS AND VARIABLESselect distinct rep_reposit_info.repository_name, rep_all_mappings.subject_areaas folder_name, rep_all_mappings.mapping_name as object_name,case when opb_map_parmvar.pv_flag =2then 'Mapping Parameter' elsecase when opb_map_parmvar.pv_flag =3then 'Mapping Variable' endend as parameter_type,opb_map_parmvar.pv_name as parameter_name,opb_map_parmvar.pv_default as parameter_value,opb_map_parmvar.pv_desc as descriptionfrom rep_all_mappings,opb_map_parmvar, rep_reposit_infowhere rep_all_mappings.mapping_id =opb_map_parmvar.mapping_id6.5LIST ALL THE MAPPINGS USING PARALLEL HINTSSELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE,1,60) ATTR_VALUEFROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M WHERE A.WIDGET_ID = W.WIDGET_IDAND W.IS_VISIBLE =1AND A.VERSION_NUMBER = W.VERSION_NUMBERAND A.WIDGET_TYPE IN(2,3,11)--Limit to Src/Tgt/Lkp TransformationsAND W.WIDGET_ID = I.WIDGET_IDAND W.VERSION_NUMBER = I.VERSION_NUMBERAND I.MAPPING_ID = M.MAPPING_IDAND I.VERSION_NUMBER = M.VERSION_NUMBERAND W.SUBJECT_ID = S.SUBJ_IDAND UPPER(A.ATTR_VALUE)LIKE'%PARALLEL%'7MAPPLET7.1LIST MAPPLETS IN ALL FOLDERSselect subject_area,mapplet_name from rep_all_mappletsorder by 1,27.2LIST MAPPLET PARAMETERS AND VARIABLESselect distinct rep_reposit_info.repository_name, rep_all_mapplets.subject_areaas folder_name, rep_all_mapplets.mapplet_name as object_name,case when opb_map_parmvar.pv_flag =2then'Mapplet Parameter' elsecase when opb_map_parmvar.pv_flag =3then'Mapplet Variable' endend as parameter_type,opb_map_parmvar.pv_name as parameter_name,opb_map_parmvar.pv_default as parameter_value,opb_map_parmvar.pv_desc as descriptionfrom rep_all_mapplets, rep_widget_inst,opb_mapping,opb_map_parmvar, rep_reposit_info where rep_all_mapplets.mapplet_id=opb_mapping.mapping_idand rep_widget_inst.widget_id=opb_mapping.ref_widget_idand opb_mapping.mapping_id=opb_map_parmvar.mapping_idand rep_widget_inst.widget_type=448SESSION8.1LIST SESSION NAMESSELECT SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME FROM REP_ALL_TASKSWHERE TASK_TYPE IN(68)--AND SUBJECT_AREA= 'ABC'ORDER BY1,2,38.2LIST SAVE SESSION LOG COUNTselect distinct cc.subject_area,cc.task_name as session_name,bb.attr_value as Savesessionlog from(select a.session_id,min(a.config_id)as config_id,a.attr_id from rep_sess_config_parm a where a.attr_id ='103' group by a.session_id,a.attr_id) aa,(select session_id,config_id,attr_value from rep_sess_config_parmwhere attr_id ='103') bb,(select subject_area,task_name,task_id from rep_all_tasks ) ccwhere aa.session_id = bb.session_idand aa.config_id=bb.config_idand bb.session_id = cc.task_idand bb.attr_value not in(8,4)order by1,2,38.3LIST STOP ON ERRORS COUNTSELECT DISTINCTA.SUBJECT_AREA,A.TASK_NAME AS SESSION_NAME,B.ATTR_VALUE AS STOPONERRORSFROMREP_ALL_TASKS A ,REP_SESS_CONFIG_PARM BA.TASK_ID =B.SESSION_IDAND TASK_TYPE_NAME ='Session'AND B.ATTR_ID ='202'--AND B.ATTR_VALUE NOT IN (1) --AND A.SUBJECT_AREA in ('ABC') ORDER BY1,28.4LIST HARD CODED PATHSSELECT DISTINCTA.SUBJECT_AREA,A.TASK_NAME AS SESSION_NAME,B.FILE_NAME,DIR_NAMEFROMREP_ALL_TASKS A ,OPB_SESS_FILE_VALS BWHEREA.TASK_TYPE_NAME ='Session'AND A.TASK_ID = B.SESSION_ID--AND A.SUBJECT_AREA IN ('ABC')ORDER BY1,28.5LIST PARAMETER FILE PATHSSELECT DISTINCTB.SUBJECT_AREA,B.TASK_NAME AS SES_WF_NAME,A.ATTR_VALUE AS PRM_FILE_PATHFROMOPB_TASK_ATTR A,REP_ALL_TASKS BWHEREA.ATTR_ID IN(1,4)AND A.TASK_ID = B.TASK_IDAND A.ATTR_VALUE LIKE'%.prm%'ORDER BY1,2ASC8.6List session log namesSELECT DISTINCTA.SUBJECT_AREA,A.WORKFLOW_NAME,A.SESSION_NAME,A.SESSION_INSTANCE_NAME,SUBSTR(A.SESSION_LOG_FILE,25,300)AS EXISTING_SESSLOGNAME FROMREP_SESS_LOG ASUBSTR(A.SESSION_LOG_FILE,25,300)!= CONCAT(LOWER(A.SESSION_INSTANCE_NAME),'.log') ORDER BY1,2,38.7LIST COMMIT INTERVALSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS COMMITINTERVELFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(14)AND A.ATTR_VALUE <>10000AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.8LIST TOTAL SOURCE PARTITIONSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS TOTAL_SOURCE_PARTITIONSFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(12)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.9LIST TOTAL TARGET PARTITIONSSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS TOTAL_TARGET_PARTITIONSFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(11)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.10List DTM Buffer SizeSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS DTM_BUFFER_SIZEFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(101)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.11LIST COLLECT PERFORMANCE DATASELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS COLLECT_PERFORMANCE_DATA FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(102)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.12List Incremental AggregationSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS INCREMENTAL_AGGREGATION FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(103)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.13List Reinitialize aggregate cacheSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS REINITIALIZE_AGGREGATE CACHE FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(104)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.14LIST ENABLE HIGH PRECISIONSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS ENABLE_HIGH_PRECISIONFROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(105)AND A.TASK_ID = B.TASK_IDAND TASK_TYPE_NAME IN('Session')ORDER BY1,2ASC8.15LIST SESSION RETRY ON DEADLOCKSELECTB.SUBJECT_AREA,B.TASK_NAME AS SESS_NAME,A.ATTR_VALUE AS SESSION_ RETRYON_DEADLOCK FROMOPB_TASK_ATTR A ,REP_ALL_TASKS BWHEREA.ATTR_ID IN(106)。
