Informatica_功能特性介绍

Informatica主要功能特性介绍1.Pre-Session和Post-Session调用数据库SQL以及存储过程。

2.列/行转换：一行记录转换为多条记录，使用Normalizer组件(见：Normalizer)。

3.行/列转换+条件汇总功能：多条记录合并为一条记录，使用Aggregator组件(结合其条件汇总功能) (见：ReversibleNormalizer)。

关于条件汇总的两种写法，注意它们的不同，如下：1) sum(iif(COMMITMONTH=$$v_yearmonth,PREMIUM,0))2) sum(PREMIUM, COMMITMONTH=$$v_yearmonth)4.增量汇总：将上次汇总的结果保存在Informatica系统目录下，以后对于增量的数据再做汇总的结果跟上次汇总的结果合并，大大提高汇总效率。

要结合Aggregator组件和Session的增量汇总属性(见：IncrementalAggregation)。

最新汇总数据的集合文件，会在Informatica Server的Cache子目录下生成，文件名如：PMAGG493_4.idx，PMAGG493_4.dat。

5.Mapping的参数(Parameter)和变量(Variable)：参数和变量的值可以在参数文件中指定；变量的值可以在Session运行结束后动态更改(用法举例：动态生成抽取源表的Where语句，来实现对增量数据的抽取(见：ParameterAndVariable)。

可以使用的Variable函数：SetCountVariable SetMaxVariable SetMinVariable SetVariable。

参数文件的格式举例：[tdbu.s_m_ParameterAndVariable]$$PID=12$$VID=8$InputFile1=sales.txt$DBConnection_target=sales$PMSessionLogFile=D:/session logs/firstrun.txt6.读文件列表(FileList)：针对多个结构相同，但是文件名不同的文件数据源，采用该方式可以大大提高ETL设计的灵活性，减少不必要的重复性操作。

(见：Filelist)。

注意：1)上图中当“Source filetype”选择Indirect的方式时，“Source filename”选项必须对应Filelist文件。

2)Fililist文件中包括那些结构相同的多个文件的全路径。

3)如果只抽取1个文本文件(比如：F1.tx)，就可以采用下面的设计：7.如何处理Session log和Workflow log?针对每个Session和Workflow都会对应一个log，每次运行都会自动覆盖上次的log文件。

如果想保留多个版本的log，可按照入下的两种模式：1)设置log保留的个数：2)使用时间戳保留每一个log文件：8.跟Session性能相关的几个参数设置：mit Interval: 缺省10000该参数越大，对目标数据库提交的次数越少，装载的性能越高，但是要考虑目标数据库回滚断的大小。

2.DTM buffer size:缺省12000000Informatica数据处理进程占用的内存，针对大数据量操作，该参数可调大；该值最大建议值是128M，最小建议值6M，根据情况建议调整到1-4倍左右。

3.Default buffer block size:缺省64000Informatica处理数据Buffer的size，该值越大，一个Buffer可装载的数据条数越多。

建议一个buffer block至少要可以存储20条记录。

该值最大建议值是128M，最小建议值4M，根据情况建议调整到1-4倍左右。

9.对象的导出，导入；Folder的版本控制；知识库的备份和恢复操作。

10.Debugger功能：在Designer中，提供可视化调试功能，可添加断点，验证函数，修改调试数据等。

11.多种缓慢变化维和Mapping Wizard模板：Designer中有Mapping Wizard，提供了各种功能的缓慢变化维模板，有极高的参考价值。

提供了3大类缓慢变化维模板：12.pmcmd命令：可以对Workflow调度做控制；停止Informatica ETL Server等，具体使用格式如下。

Usage: help [command]Usage: versionUsage: pingserverUsage: getserverpropertiesUsage: shutdownserver <-complete|-stop|-abort>Usage: getserverdetails [-all|-running|-scheduled]Usage: getrunningsessionsdetailsUsage: startworkflow [<-folder|-f> folder][<-startfrom> taskInstancePath [<-recovery>]][<-paramfile> paramfile] [-wait|-nowait] workflow举例：pmcmd startworkflow -s scottyxd:4001 -u Administrator -p Administrator -f tdbu wf_ods_employee Usage: stopworkflow [<-folder|-f> folder] [-wait|-nowait] workflowUsage: abortworkflow [<-folder|-f> folder] [-wait|-nowait] workflowUsage: waitworkflow [<-folder|-f> folder] workflowUsage: resumeworkflow [<-folder|-f> folder] [-wait|-nowait] [<-recovery>]workflowUsage: scheduleworkflow [<-folder|-f> folder] workflowUsage: unscheduleworkflow [<-folder|-f> folder] workflowUsage: getworkflowdetails [<-folder|-f> folder] workflowUsage: starttask [<-folder|-f> folder] <-workflow|-w> workflow[<-paramfile> paramfile] [-wait|-nowait] [<-recovery>]taskInstancePathUsage: stoptask [<-folder|-f> folder] <-workflow|-w> workflow [-wait|-nowait]taskInstancePathUsage: aborttask [<-folder|-f> folder] <-workflow|-w> workflow [-wait|-nowait]taskInstancePathUsage: waittask [<-folder|-f> folder] <-workflow|-w> workflow taskInstancePath Usage: resumeworklet [<-folder|-f> folder] <-workflow|-w> workflow[-wait|-nowait] [<-recovery>] taskInstancePathUsage: gettaskdetails [<-folder|-f> folder] <-workflow|-w> workflowtaskInstancePathUsage: getsessionstatistics [<-folder|-f> folder] <-workflow|-w> workflowtaskInstancePathUsage: connect <-serveraddr|-s> [host:]portno<<-user|-u> username|<-uservar|-uv> userEnvVar><<-password|-p> password|<-passwordvar|-pv> passwordEnvVar> Usage: disconnectUsage: setwaitUsage: setnowaitUsage: unsetfolderUsage: setfolder folderUsage: showsettingsUsage: exit13.pmrep命令：提过对元数据操作的命令，包括知识库用户，组，密码管理；权限管理；数据库连接串管理等。

run-f <script file name>[-o <output file name>][-e <echo commands>][-s <stop at first error>]connect-r <repository name>-n <repository user name>[-x <repository password> |-X <repository password environment variable>]-h <repserver host name>-o <repserver port number>backup-o <output file name>-f (overwrite existing output file)-d <description>[-b (skip workflow/session logs)][-j (skip deploy group history)][-q (skip MX data)]updatestatisticsupdateemailaddr-d <folder name>-s <session name>-u <success email address>-f <failure email address>updatesrvvar-s <server name>-v <variable name>-u <new value>updatetargprefix-f <folder name>-s [<qualifying path>.]<session name> [-t <target name>]-p <prefix name>-nupdatesrcprefix-f <folder name>-s [<qualifying path>.]<session name> [-t <source name>]-p <prefix name>-nupdateseqgenvals-f <folder name>[-m <mapping name>]-t <sequence generator name>[-s <start value>][-e <end value>][-i <increment by>][-c <current value>]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>]listtablesbysess-f <folder name>-s [<qualifying path>.]<session name> -t <source or target>listalluserslistallgroupscreateuser-u <repository user name>[{-p <password>-c <password again>} |-P <password environment variable>] [-d <description>][-g <group name>][-i <contact info> ] }registeruser-u <repository user name>-l <external login>[-d <description>][-g <group name>][-i <contact info> ] }rmuser-u <repository user name> changepasswd[{-p <new password>-c <password again>} |-P <new password environment variable>] edituser-u <repository user name>[{-p <new password>-c <password again>} |-P <new password environment variable>] [ -l <new login> ][ -d <new description> ][ -e <enabled: yes|no> ][ -i <contact info> ]addusertogroup-u <repository user name>-g <group name>rmuserfromgroup-u <repository user name>-g <group name>creategroup-g <group name>[-d <description>]rmgroup-g <group name>listallprivilegesaddprivilege-p <privilege>{ [-u <repository user name>][-g <group name>] }rmprivilege-p <privilege>{ [-u <repository user name>][-g <group name>] }createconnection-s <relational connection subtype>-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 (Environment SQL)][-z (Packet Size, valid for Sybase and MS SQL Server connection)][-b (Database Name, valid for Sybase, Teradata and MS SQL Server connection)] [-v (Server Name, valid for Sybase and MS SQL Server connection)][-d (Domain Name, valid for MS SQL Server connection only)][-t (1 for Trusted Connection, valid for MS SQL Server connection only)][-a (Data Source Name, valid for Teradata connection only)] switchconnection-o <old connection name>-n <new connection name>deleteconnection-n <relational connection name>[-f (force delete)]showconnectioninfoupdateconnection-t <database type>-d <database connection name>-u <new user name>[-p <new database password> |-P <new database password environment variable>] -c <new database connection string> updateserver-v <server name>[-h <new host name>][-k <new servername>][-o <new port number>][-t <new timeout value>][-p <new protocol name>][-l <new codepage name>]deleteserver-v <server name>addserver-v <server name>-h <new host name>[-o <new port number>][-t <new timeout value>][-p <new protocol name>][-l <new codepage name>]createFolder-n <folder name>[-d <folder description>][-o <owner name>][-g <group name>][-s <shared folder>][-p <permissions>]deleteFolder-n <folder name>modifyFolder-n <folder name>[-d <folder description>][-o <owner name>][-g <group name>][-s <shared folder>][-p <permissions>][-r <new name>]truncatelog-t <all | <endtime> (MM/DD/YYYY HH24:MI:SS)> [-f <folder name>][-w <workflow name>]createlabel-a <label name>[-c <comments>]deletelabel-a <label name>[-f <force delete>]applylabel-a <label name>{ [-n <object name>-o <object type>-t <object subtype>][-v <version number][-f <folder name>] }{ [-i <persistent input file>] }[-d <dependency object types>][-p <dependency direction (children, parents, or both)>] [-s <include pk-fk dependency>][-g <across repositories>][-m <move label>][-c <comments>]createdeploymentgroup-p <deployment group name>[-t <deployment group type (static or dynamic)>]{ [-q <query name>][-u <query type (shared or personal)>] }[-c <comments>]deletedeploymentgroup-p <deployment group name>[-f <force delete>]cleardeploymentgroup-p <deployment group name>[-f <force clear>]addtodeploymentgroup-p <deployment group name>{ [-n <object name>-o <object type>-t <object subtype>][-v <version number][-f <folder name>] }{ [-i <persistent input file>] }[-d <dependency types (all, non-reusable or none)>] findcheckout[-o <object type>][-f <folder name>][-u <all users>][-c <column separator][-r <end-of-record separator>][-l <end-of-listing indicator>][-b <verbose>]checkin-o <object type>[-t <object subtype>]-n <object name>-f <folder name>-c <comments>undocheckout-o <object type>[-t <object subtype>]-n <object name>-f <folder name>executequery-q <query name>[-t <query type (shared or personal)>][-u <output persistent file name>][-a <append>][-c <column separator][-r <end-of-record separator>][-l <end-of-listing indicator>][-b <verbose>]listobjectdependencies{ [-n <object name>-o <object type>-t <object subtype>][-v <version number][-f <folder name>] }{ [-i <persistent input file>] }[-d <dependency object types>]-p <dependency direction (children, parents, or both)> [-s <include pk-fk dependency>][-g <across repositories>][-u <output persistent file name>][-a <append>][-c <column separator][-r <end-of-record separator>][-l <end-of-listing indicator>][-b <verbose>]deployfolder-f <folder name>-c <control file name>-r <target repository name>[-n <target repository user name>[-x <target repository password> |-X <target repository password environment variable>] -h <target repserver host name>-o <target repserver port number>][-l <log file name>]deploydeploymentgroup-p <deployment group name>-c <control file name>-r <target repository name>[-n <target repository user name>[-x <target repository password> |-X <target repository password environment variable>] -h <target repserver host name>-o <target repserver port number>][-l <log file name>]objectimport-i <input xml file name>-c <control file name>[-l <log file name>]objectexport{ [-n <object name>-o <object type>-t <object subtype>][-v <version number][-f <folder name>] }{ [-i <persistent input file>] }[-m <export pk-fk dependency>][-s <export objects referred by shortcut>][-b <export non-reusable dependents>][-r <export reusable dependents>]-u <xml output file name>[-l <log file name>]validate{ [-n <object name>-o <object type (mapplet, mapping, session, worklet, workflow)>[-v <version number][-f <folder name>] }{ [-i <persistent input file>] }[-s <save upon valid>]{ [-k <check in upon valid>-m <check in comments>] }[-p <output option types (valid, saved, skipped, save_failed, invalid_before, invalid_after, or all)>] [-u <output persistent file name>][-a <append>][-c <column separator][-r <end-of-record separator>][-l <end-of-listing indicator>][-b <verbose>]addrepository-h <repserver host name>-o <repserver port number>[-a <repserver password> |-A <repserver password environment variable>]-r <repository name>-t <database type>-u <database user name>[-p <database password> |-P <database password environment variable>][-m (Trusted Connection, valid for Microsoft SQL Server only)]-c <database connect string>[-d <code page name>][-e <DB2 tablespace name>]stoprepository[-a <repserver password> |-A <repserver password environment variable>][-h <hostname>-o <port number>-r <repository name>]enablerepository-h <hostname>-o <port number>[-a <repserver password> |-A <repserver password environment variable>]-r <repository name>disablerepository-h <hostname>-o <port number>[-a <repserver password> |-A <repserver password environment variable>]-r <repository name>register-r <repository name>-n <repository user name>[-x <repository password> |-X <repository password environment variable>][-a <GDR repserver password>-A <GDR repserver password environment variable>][-h <LDR host name> (only if LDR is managed by a different RepServer)-o <LDR port number> (only if LDR is managed by a different RepServer)] unregister-r <repository name>-n <repository user name>[-x <repository password> |-X <repository password environment variable>][-a <GDR repserver password>-A <GDR repserver password environment variable>][-h <LDR host name> (only if LDR is managed by a different RepServer)-o <LDR port number> (only if LDR is managed by a different RepServer)]notify-h <hostname>-o <port number>[-a <repserver password> |-A <repserver password environment variable>]-r <repository name>-t <notify | broadcast> (message type)-m <message>helphelp [command].Print help. If command is specified print help for command, else print help for all commands cleanup14.Workflow流程控制：在Wofkflow Manager里面，结合Workflow 变量、Event、Schedule以及其他组件，可以实现非常复杂的流程控制(见：WorkflowControl)。