ODI FAQ

去年DW项目实施时写的一些常见问题，主要针对ODI 10g版本，内容较陈旧，仅供参考


===一个变量可以在多个Scenario中使用而不产生冲突吗?===
可以

Recommendations For Working With ODI Variables In Startscen And OdiStartScen Calls ID 423737.1
https://support.oracle.com/CSP/main/article?cmd=show&id=423737.1&type=NOT

If the same Variable is used in multiple simultaneously running Scenarios, it must have the 'Action' parameter set to 'Not Persistent' to avoid Variable value collision from concurrent Scenarios.

===如何处理目录下的所有文件===

ODI Series – Processing all files in a directory: http://john-goodwin.blogspot.com/2009/06/odi-series-processing-all-files-in.html
How to refresh ODI variables from file – Part 1 – Just one value: http://odiexperts.com/?p=243
How to refresh ODI variables from file – Part 2 – Getting all lines, once at time: http://odiexperts.com/?p=273
Getting one or several unknown files from a directory: http://odiexperts.com/?p=1426

===如何删除ODI日志===
How To Avoid Saturating The ODI Log Tables ID 423934.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423934.1
Session And Scenario Execution Log Tables Used By ODI ID 424663.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424663.1
Programming ODI To Automatically Purge The Operator Journal ID 423839.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423839.1
How To Manually Delete A Scenario And Its Related Reports In The ODI Repository With SQL ID 424661.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424661.1
ODI snippets: Purge Log and shrink space: http://www.business-intelligence-quotient.com/?p=539

===ODI User Function怎么用?===
ODI User Functions: A Case Study: http://blogs.oracle.com/dataintegration/2009/09/odi_user_functions_a_case_stud.html
http://john-goodwin.blogspot.com/2009/06/odi-series-quick-look-at-user-functions.html

===如何并行执行过程===
Parallel Processing in ODI: http://blogs.oracle.com/dataintegration/2009/11/parallel_processing_in_odi.html
How To Run ODI Processes In Parallel And Using OdiWaitForChildSession ID 443858.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=443858.1
Asynchronous -Parallel Execution: http://odiexperts.com/?p=1180

===ODI的学习资源有哪些===
Oracle Data Integrator: http://www.oracle.com/technology/products/oracle-data-integrator/index.html
Oracle Data Integrator Release 10.1.3.6 Documentation Library: http://download.oracle.com/docs/cd/E15985_01/index.htm
Oracle Data Integrator Downloads: http://www.oracle.com/technology/software/products/odi/index.html
Oracle Data Integrator应用指南: http://www.docin.com/p-32319219.html
Oracle Data Integrator/Sunopsis, Releases and Patches ID 456313.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=456313.1
My Oracle Support searching keyword odi: http://support.oracle.com/
Data Integration Forum: http://forums.oracle.com/forums/forum.jspa?forumID=374
Developing a Knowledge Module in Oracle Data Integrator: http://www.oracle.com/technology/pub/articles/bethke-odi.html
ODI Resources for Beginners: Getting Started with Oracle Data Integrator: http://www.business-intelligence-quotient.com/?p=379
Oracle Data Integration Resource Kit. Included is the State of the Data Integration Market White Paper, in addition to additional key Data Integration resources.: http://www.oracle.com/webapps/dialogue/dlgpage.jsp
Oracle by Example Series: Oracle Data Integrator: http://www.oracle.com/technology/obe/fusion_middleware/odi/index.html
http://www.oracle.com/technology/obe/fusion_middleware/ODI/index.html
Data Integration and Management: http://blogs.oracle.com/dataintegration/
ODI EXPERTS: http://odiexperts.com/
More to life than this...: http://john-goodwin.blogspot.com/
BI-Quotient: http://www.business-intelligence-quotient.com/
Rittman Mead: http://www.rittmanmead.com
Business Intelligence – Oracle: http://oraclebizint.wordpress.com/category/oracle-data-integrator/

Oracle Czech BI/DW Blog: http://bidwcz.blogspot.com/
Technology & Other Worldly Issues: http://askankit.blogspot.com/

https://dbinfo.no.sohu.com:4443/apex/p?n=8247611144080585
https://dbinfo.no.sohu.com:4443/apex/p?n=8247701710096829

Note: The repository structure changes slightly between major versions and in particular from 11g onwards.
In this case, it is recommended to use the SDK instead of querying the repository tables directly.

===如何对数据源UNION操作===
Is It Possible To Use A UNION Operation Of Two Datastores As A Source For An ODI Integration Interface? ID 423731.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423731.1
KM FOR UNION BASED ON IKM SQL CNTRL APPEND: http://odiexperts.com/?p=1164

===ODI授权===
Q:

开发想修改md_xxx中dm_agent，想定义为缓慢变化维，我用security manager通过profile给他授予了Object，Model Folder,Model,Sub-Model,Datastore 的所有权限，然后把md_xxx拖到了该用户下instances目录下，指定repository为wk_dev。增加这些权限后，开发说还是报错：com.sunopsis.core.SecurityAccessException: You are not authorized to Edit the Object:SNP_JOIN : DM_AGENT_FK_CHANNEL / 6001
难道我还得把相应的datastore也拖到用户下? ODI授个权怎么就这么麻烦呢? 这种操作到底需要授予哪些权限?

A:

we don't know

===如何用动态文件名===
Using A Dynamically Specified File Name Or Directory Name With ODI ID 423635.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423635.1
Using Parameters in ODI: The Dynamic File Name Example: http://blogs.oracle.com/dataintegration/2009/04/using_parameters_in_odi_the_dy_1.html

===Operator中如何显示出变量值?===
Find the Value of the Variable in Operator ?: http://odiexperts.com/?p=855
ODI Variables and the Operator Module: http://www.business-intelligence-quotient.com/?p=762

One Comment on “ODI Variables and the Operator Module”

  1.
    #1 Craig Stewart said at 3:18 pm on March 17th, 2010:

    Uli
    Another great article – another snippet to follow-up. If the variable has been passed into the execution as a parameter, then if you use odiRef.getSession(”SESS_PARAMS” ) it will printout the values of the parameters. To make this readable in the execution, I usually put in a procedure with a Java Beanshell step, with the code:
    /* */
    As it is in /* */ comments, it is ignored, and does not look like an error/kludge, but prints out neatly in the log.
    Shame there is no similar thing for the variables set dynamically!
    Craig

Using Jython Scripting Features In ODI FAQ ID 566475.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=566475.1#aref78

10. Displaying ODI Variables Values In ODI Operator Log

Is there any technique whereby it is possible, in the ODI Operator Log, to display the values of ODI Variables which have been initialized from runtime parameters when launching Scenarios?
Such a technique would allow the recording of Variable values during runtime and would be useful for execution audit operations.

Two alternative solutions are possible:

In the case of OdiStartScen (SnpsStartScen) tool, it is possible to specify the name of the Session with the "-SESSION_NAME" parameter. In this case, pass the value of the ODI Variable to this parameter for display in ODI Journal.

A Jython script step such as the following, in a Knowledge Module, will allow the display of the ODI Variable value in the Execution tab of the step :
a = 'Table = '+ '#YOURVARIABLE'
raise(a)


Such a Jython script will require the "Ignore errors" checkbox to be checked in the Knowledge Module step. Do not forget that such an Integration Interface must be called from an ODI Package including steps which Declare and Refresh the ODI Variable.

Variable values cannot be displayed in this case because the Agent records data in ODI Operator Log before the ODI process that substitutes Variables by their values, and before sending the values to the technology be be directly used or bound.

===如何重新生成scenario===
ODI: Automating deployment of scenarios to production in Oracle Data Integrator: http://www.business-intelligence-quotient.com/?p=340
What Will Each Generation Mode (Replace, Re-generate, Creation) Of ODI Project "Generate All Scenarios" Option Do? ID 555709.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=555709.1
Generate Multiple Scenarios: http://odiexperts.com/?p=456
In ODI What Is The Difference Between Generation And Regeneration Of A Scenario ID 733103.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=733103.1

How To Ensure That The Regeneration Of An ODI Scenario Is Successful? ID 804583.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=804583.1

===生成场景报错ORA-12899===
Q:

生成场景时报如下错误
java.sql.SQLException: ORA-12899: value too large for column "W_DEV"."SNP_SCEN_STEP"."VAR_VALUE" (actual: 265, maximum: 250)
如何解决？

A:

变量赋值不能超过250字符。可以将一个大的变量分成多个小的变量，最后再拼到一起。

===用OdiFileWait报错===
Q:

程序中使用OdiFileWait检查文件是否存在，文件可以被ls看到，目录权限drwxr-xr-x，文件权限-rw-r--r--，但是OdiFileWait总报错说No File Found.

A:

# TIMEOUT时间太短，应该设置长一些，比如10000ms
# 文件名PATTERN中不能带目录名，比如#v_dirname/#v_filename，只能是纯文件名
# 改用别的方式检查文件是否存在，比如OdiOSCommand, sh -c "test -f #v_dirname/#v_filename"

===SQLLDR导数据的字段顺序出错===
Q:

用LKM File to Oracle (SQLLDR)导数据，报错字段格式不对。检查日志和生成的控制文件，发现控制文件中字段的顺序变了，跟文件中不一致

A:

因为在Interface配置的Target Datastore有些字段execute on source,有些execute on staging。KM生成的控制文件中，字段顺序自动发生了变化，execute on source的字段在前，execute on staging的在后。所以相关字段统一使用execute on source或staging...，顺序才不会变。

===IKM插入数据报错 ORA-01031: insufficient privileges===
Q:

IKM SQL Control Append在Insert new rows这步报错

1031 : 42000 : java.sql.SQLException: ORA-01031: insufficient privileges

java.sql.SQLException: ORA-01031: insufficient privileges

执行的语句是

insert /*+ append */ into  ods.ODS_ORACLE_TABXXX
(
….
from  ODI_WORK.I$_ODS_ORACLE_TABXXX

直接用sqlplus以odi work用户在ods数据库上执行同样语句，报错

    ,ods.seq_ods_oracle_tabxxx_id.nextval,
          *
ERROR at line 88:
ORA-01031: insufficient privileges

A:

ods库上的odi work用户没有相应权限，需要手工授予

grant select any sequence to ODI_WORK;

===如何实现反连接?===
Q:

ODI中如何实现not in, not exists
比如:

select * from t1 where not exists (select null from t2 where t2.c1=t1.c1)

A:

How To Set Up Negative Join Operations In An ODI Integration Interface ? ID 742144.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=742144.1
Using Oracle Outer Join Operations Such As LIKE, IN, NOT IN, IS NULL ...In ODI Integration Interfaces. ID 424034.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424034.1

比如

select * from t1 where not exists (select null from t2 where t2.c1=t1.c1)

用left outer join实现:

Interface->Diagram->Sources

* t1 left outer join t2
** 注意不能选中left outer join复选框, 必须手工写(+)号
**Implementation填t1.c1=t2.c1(+), 设置为execute on staging
* t2增加filter, Implementation填t2.c1 is null, 设置为execute on staging

===刷新变量出错No value to affect to this variable for DefDate===
Q:

Package中刷新变量，报错Error: No value to affect to this variable for DefDate
意思是说select刷新语句没有返回结果。
然而数据库中运行select语句可以查到记录，有返回结果。

Package是按照Best practice，先声明变量，再刷新的，使用上应该没有错。

刷新语句是:

select to_char(t1_id) from admin_odi.t1
where name='#v_t1_name'

* 将where子句改成where name='p_city_day'，也报同样错。
* 如果注释掉where子句，改成select to_char(p1_id) from admin_odi.t1，则刷新成功，取得第一条记录的结果。

A:

* 原因不明。
* 删掉Diagram中变量声明和刷新的步骤，重新定义这2步，即可。

===调用场景参数中带双引号的转义===
Q:

用如下命令调用一个场景失败，报Unmatching quotes错误

OdiStartScen "-SCEN_NAME=T1_INVOKER" "-SCEN_VERSION=001" "-TEST_XIEWEN.v_t1_name=p_city_day" "-TEST_XIEWEN.v_scen_name=P_CITY_DAY" "-TEST_XIEWEN.v_scen_params=\"-ODS_FROM_ONLINE_CITY.v_start_date=#TEST_XIEWEN.v_start_date\" \"-ODS_FROM_ONLINE_CITY.v_end_date=#TEST_XIEWEN.v_end_date\" \"-ODS_FROM_ONLINE_CITY.v_date_format=#TEST_XIEWEN.v_date_format\""

应如何转义双引号？

A:

现在用的方法是，先用一段特殊字符代表双引号，在被调用的场景中再将特殊字符替换回双引号

调用场景时，特殊字符\\u0022代表双引号:

OdiStartScen "-SCEN_NAME=T1_INVOKER" "-SCEN_VERSION=001" "-TEST_XIEWEN.v_t1_name=p_city_day" "-TEST_XIEWEN.v_scen_name=P_CITY_DAY" "-TEST_XIEWEN.v_scen_params=\\u0022-ODS_FROM_ONLINE_CITY.v_start_date=#TEST_XIEWEN.v_start_date\\u0022 \\u0022-ODS_FROM_ONLINE_CITY.v_end_date=#TEST_XIEWEN.v_end_date\\u0022 \\u0022-ODS_FROM_ONLINE_CITY.v_date_format=#TEST_XIEWEN.v_h1\\u0022"

场景中再替换回双引号：

select replace(q'{#v_scen_params}','\\u0022','"') from dual

===改resource name后interface取的还是旧表名===
Q:

为了测试用，将一个表data store的resource name改成另一个临时建的新表名，点refresh number of rows显示行数也和新表名相符, 然而运行interface，取到的还是旧表数据。为什么？

A:

* LKM Oracle to Oracle (DBLINK)_corp使用了odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]获得源表表名
** TABLE_NAME表示逻辑表名,RES_NAME才表示物理表名
** TABLE_NAME Logical name of the source datastore
** RES_NAME Physical access name of the resource.......
* 所以修改resource name不管用，必须修改data store的名字。

===Historize变量没取到最新值===
Q:

有一个Historize变量v_etl_sysdate每天都刷新为当天的日期，通过变量的历史可以看到。

package查看这个变量，得到的不是最后一次刷新的值(2010-07-16)，是历史记录中的第一个值(2010-04-09)。

SQL> select * from SNP_VAR_DATA where var_name='S2ODS.v_etl_sysdate' order by FIRST_DATE;

VAR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
CONTEXT_CODE                                          I_VAL VAR_D
---------------------------------------------------------------------- ---------- -------------------
VAR_V
------------------------------------------------------------------------------------------------------------------------------------------------------
  VAR_N FIRST_DATE      FIRST_I_USER LAST_DATE      LAST_I_USER I_TXT_VAR_T
---------- ------------------- ------------ ------------------- ----------- -----------
S2ODS.v_etl_sysdate
EMPDATAB                                              6591
2010-04-09
      2010-04-09 00:29:59        2010-04-09 00:29:59

S2ODS.v_etl_sysdate
EMPDATAB                                            21382
2010-04-10
      2010-04-10 00:29:59        2010-04-10 00:29:59

...
...
S2ODS.v_etl_sysdate
EMPDATAB                                            1141971
2010-07-15
      2010-07-15 01:00:00        2010-07-15 01:00:00

S2ODS.v_etl_sysdate
EMPDATAB                                            1142420
2010-07-16
      2010-07-16 01:00:00        2010-07-16 01:00:00


98 rows selected.

SQL>

A:

* 直接执行package能取到最新值。
* 执行scenario, 如果生成scenario时，选择了v_etl_sysdate做参数，则以后运行scenario该参数默认值都是历史第一个值。
* 生成scenario不选择该变量做参数，执行也可得到最新值。

===ODI 11gR1===
document: http://download.oracle.com/docs/cd/E14571_01/odi.htm
download: http://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=11571971

===如有error/discard记录，LKM SQLLDR导入失败===
Q:

如果有error或discard记录，LKM SQLLDR导入失败

A:

sqlloader not load file in oracle when get invalid rows.: http://forums.oracle.com/forums/thread.jspa?threadID=1043579

This is a bug 8560194 with ODI LKM File to Oracle (SQLLDR) and it has not been resolved yet.
SQLLDR if successful returns 0
SQLLDR if unsuccessful returns 1
SQLLDR if successful but even 1 record erred out to .bad or .dsc file returns 2

So, for ODI anything that is not 0 is an error.

To resolve this bug, you will have to customize this KM and change the step "Invoke SQLLDR" to selectively handle each error condition.

Duplicate the KM.
Goto the step Call sqlldr. and change the technology to Jython.

Replace the existing code with the following:

import os
retVal = os.system(r'sqlldr control=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].ctl", "", "")%> log=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> userid=<%=snpRef.getInfo("DEST_USER_NAME")%>/<%=snpRef.getInfo("DEST_PASS")%>@<%=snpRef.getInfo("DEST_DSERV_NAME")%> > <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].out", "", "")%>')

if retVal == 1 or retVal > 2:
  raise 'SQLLDR failed. Please check the <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> for details '


Hope that helps

===显示Session运行状态有问题===
Q:

Operator日志显示某Session总是Running状态，在后台查询已无相应数据库会话

A:

该会话可能是从本机No agent发起的，当会话运行中，退出了客户端(Designer,Operator)。客户端退出，会话也随之终止，但会话状态不会更新到ODI系统表。

===过程中使用绑定变量，报错Missing parameter===
Q:

Procedure中一条语句使用到了bind variable:

begin
...
UPDATE <%=odiRef.getOption("TABLE_NAME")%>
  SET STATUS=<%=odiRef.getOption("T1_STATUS")%>
  WHERE T1_ID=<%=odiRef.getOption("T1_ID")%>;
...
end;

P1_ID赋值为":ADMIN_ODI.v_t1_ID"，开头的冒号表示使用绑定变量

执行时出错:

com.sunopsis.sql.SnpsMissingParametersException: Missing parameter
  at com.sunopsis.sql.SnpsQuery.completeHostVariable(SnpsQuery.java)
  at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
...

日志显示，最后生成的语句是:

begin
...
UPDATE #ADMIN_ODI.v_admin_odi_t1
  SET STATUS=2
  WHERE T1_ID=:ADMIN_ODI.v_t1_ID;
...
end;

A:

ODI把UPDATE语句最后的分号当成变量名的一部分，所以找不到变量，就报错了。

分号换行写，或前面加空格与变量名分开，即可。

===OdiOSCommand双连字符问题===
Q:

* OdiOSCommand调用命令sh -c "rsync -q --dry-run ...."
* 从Operator操作日志观看生成的命令，double hyphen及其后字符都没有了
* 将双连字符改成\u002d\u002d，生成命令没错，执行报错"命令返回:2"
* 检查出错原因，好像还是因为双连字符后被截断了，导致命令不完整:

sh: -c: line 0: unexpected EOF while looking for matching `"'
sh: -c: line 1: syntax error: unexpected end of file

A:

OdiOutFile先将执行的命令写到一个脚本内，然后OdiOSCommand调用此脚本

===SNP_SESSION.SESS_STATUS字段代表什么含义?===
* D = Done
* W = Waiting
* E = Error
* R = Running
* M = Warning
* Q = Queued

===SQLLDR LKM如何实现只选择几列导入===
Q:

* 一个文件有A,B,C 3列，数据库表中有B,C 2列
* interface mapping只映射了B,C列, km生成的CTL文件内也只有这2列, 于是字段不匹配，sqlldr导入失败

A:

对所有没有mapping的字段，建filter, 填字段名 is not null or 字段名 is null

===Scheduler Agent和Work Repository是一一对应的吗===
Q:

odiparam.sh有个参数ODI_SECU_WORK_REP，表示agent连接哪个工作资料库

10g中，Scheduler Agent在启动时连接该工作资料库，获取scheduler定时任务

11g，情况好像有所不同，启动agent后，日志显示:

2010-08-24 20:08:38.177 NOTIFICATION ODI-1128 Agent pa_emods_dev is starting. Application Server: STANDALONE. Agent Version: 11.1.1.3.0 - 23/06/2010
. Port: 21911. JMX Port: 22911
2010-08-24 20:09:01.667 NOTIFICATION ODI-1136 Starting Schedulers on Agent pa_ods_dev
2010-08-24 20:09:01.853 NOTIFICATION ODI-1111 Agent pa_emods_dev started. Agent version: 11.1.1.3.0 - 23/06/2010. Port: 21911. JMX Port: 22911.
2010-08-24 20:09:01.907 NOTIFICATION ODI-1137 Scheduler started for work repository REPWPROD on Agent pa_ods_dev
2010-08-24 20:09:02.012 NOTIFICATION ODI-1137 Scheduler started for work repository REPWDEV on Agent pa_ods_dev

貌似连接了所有工作资料库，一个agent就管理了所有scheduler任务

odiparam.sh对该变量的注释也有所变化：

# The following work repository name variable is required by startcmd (when the specified
# command needs to connects to the work repository), startscen, and restartsession scripts.
# The agent startup and agentstop programs do not need this variable.
#
#  ODI_SECU_WORK_REP
#    Name of the Work Repository to connect. This Work Repository must
#    be attached to the master repository.

A:

测试了下，好像是这样
* 10g scheduler agent从odiparam.sh指定的work repository取出context和logical agent等于当前physical agent的任务
* 11g agent从所有的work repositories取出context和logical agent等于当前physical agent的任务

7.2.2 Agent Startup and Shutdown Cycle: http://download.oracle.com/docs/cd/E14571_01/core.1111/e10106/odi.htm#ASHIA4972

When the Oracle Data Integrator agent starts, it first reads its configuration, which includes master repository connection information. Then the agent connects to each of the work repositories attached to this master repository and removes stale sessions. Stale sessions are sessions that are incorrectly indicated in the work repository as running on this given agent. Stale sessions may result from an agent being stopped without being able to stop these sessions gracefully. As the agent restarts, it identifies the stale sessions and moves them to an error state.

From that point, the agent can retrieve and compute the schedules available for it in each work repository. Once this phase is complete, the agent waits for incoming sessions requests to start their processing, and is also able to start sessions based on its schedules.

===如何使用子查询===
Using subqueries in Oracle Data Integrator (ODI) interfaces for complex data integration requirements: http://www.business-intelligence-quotient.com/?p=621
Subselect, derived tables, and subqueries in ODI 11G: http://www.business-intelligence-quotient.com/?p=1045

===生成场景报错An object will be in an unidentified state===
Q:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=FAQ&id=471564.1#aref17

2. "An object will be in an unidentified state...." Message Is Randomly Displayed
The following message is displayed randomly:
"An object will be in an unidentified state (it is in the state Not found in the database and Update is currently being performed on it) " for example, when refreshing an Oracle Data Integrator Variable, running a Scenario...

Such a problem has been attributed to the ODI Log related tablespaces being full.

Workarounds are the following:

Increase the size of the appropriate tablespaces containing the ODI Work Repositories.
Purge the Log tables in ODI Log (Operator).

A:

* 删除日志

* 关了重新打开，有时也能好

===MySQL建temporary indexes的问题===
Q:

LKM建临时索引使用如下语句:

Create <%=odiRef.getTemporaryIndex ("INDEX_TYPE_CLAUSE") %> Index <%=odiRef.getObjectName(odiRef.getTemporaryIndex ("IDX_NAME"),"W") %> on <%=odiRef.getTemporaryIndex ("COLL_NAME")%>
<%=odiRef.getTemporaryIndexColList( "(", " [CX_COL_NAME] ", ", ", ")" )%>

生成的SQL语句是:

create ... index aaa.ix$_bbbb on .....;

索引名之前带上了数据库名

MySQL不支持这样写，应去掉数据库名:aaa.

所以将KM写为

Create <%=odiRef.getTemporaryIndex ("INDEX_TYPE_CLAUSE") %> Index ", "W").substring(odiRef.getCatalogName("W").length()+1)?> on <%=odiRef.getTemporaryIndex ("COLL_NAME")%>
<%=odiRef.getTemporaryIndexColList( "(", " [CX_COL_NAME] ", ", ", ")" )%>

A:

good

===OdiSqlUnload 报错内存不够===
Q:

使用OdiSqlUnload导出数据，大约28万条记录，报OutOfMemoryError

java.lang.OutOfMemoryError: Java heap space
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1585)
  at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1409)
  at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2886)
  at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:476)
  at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2581)
  at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1757)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2171)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
  at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
  at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:602)
  at com.sunopsis.dwg.tools.SqlUnload.actionExecute(SqlUnload.java:327)
  at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:273)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3185)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:1414)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2785)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
  at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
  at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1818)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:559)
  at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:481)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1040)
  at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
  at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
  at java.lang.Thread.run(Thread.java:619)

A:

增加odi agent java虚拟机内存

"java heap OutOfMemory" Error When Loading A Large Amount Of Data From Mysql With ODI ID 737309.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=737309.1

This issue is due to a MySQL JDBC Driver issue.

When selecting a large amount of data, the MySQL JDBC Driver loads the entire ResultSet returned by the select to the JVM's memory.

This seems to be the case for any version of MySQL JDBC Driver.

* http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-implementation-notes.html

ResultSet

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
        java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

* 经试验,odiunload 参数fetchsize改小，比如改成10，能成功


===索引===
Q:

建索引

A:

create index w_dev.i_SNP_SESSION_psn on w_dev.SNP_SESSION(PARENT_SESS_NO) online;
create index w_prod.i_SNP_SESSION_psn on w_prod.SNP_SESSION(PARENT_SESS_NO) online;
...

create index w_dev.i_SNP_OBJ_STATE_IO on w_dev.SNP_OBJ_STATE(I_INSTANCE,I_OBJECT) online;
create index w_prod.i_SNP_OBJ_STATE_IO on w_prod.SNP_OBJ_STATE(I_INSTANCE,I_OBJECT) online;
...

create index w_prod.i_SNP_SESSION_an_ss on w_prod.SNP_SESSION(AGENT_NAME,SESS_STATUS) online;
create index w_dev.i_SNP_SESSION_an_ss on w_dev.SNP_SESSION(AGENT_NAME,SESS_STATUS) online;

===导入报错 ORA-00001: AK_SNP_GRP_STATE===
Q:

导入一个项目时报错：

java.sql.SQLException: ORA-00001: unique constraint (xxxx.AK_SNP_GRP_STATE) was happend
...

改用utf-8格式导出导入，导入报错

... integrity constraint (xxxxx.FK_TXT) violated - Parent Key are not found.
...

A:

因有人以中文系统创建了项目，项目中带有中文字符，如Markers是中文名称，如：优先级、进度、笑脸

将中文标识改为英文后，导出导入成功


===ODI Sendmail tool 发中文显示乱码===
Q:

用ODI sendmail tool发中文邮件，显示为乱码

A:

标题和内容编码不对，默认用了操作系统的语言设置

方法1：ODI Agent环境变量LANG设置GBK，LANG=zh_CN.gbk，然后重启agent

方法2：自己编写jython程序，支持编码，参考Unprintable And UTF-8 Characters When Sending Emails With The ODI  SendMail Tool ?: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=423953.1。未能实现

===ODI Agent不断重启===
Q:

按问题[#ODI Sendmail tool 发中文显示乱码]所述，设置LANG=zh_CN.gbk后，odi agent反复重启

A:

经检查，是/etc/init.d/odiagentctl condstart重启了进程

LANG改成zh_CN后，ping日志内容变成中文，脚本无法识别中文，因此误判，导致重启

只要将

grep -q -- "Invalid parameter:-SECURITY_DRIVER"

改成

grep -q -- ":-SECURITY_DRIVER"

即可








--fin--