商云方的技术博客 (沪ICP备14046774号-1)

1. 根据文档rms-1205-og1.pdf 中 RETL Extraction Programs 章节，测试RETL程序；(RETL的说明文档请参考：retl-12011-pg.pdf)

2. 根据rms-1205-og1.pdf 中 RETL Extraction Programs 章节，选取了两个程序(一个不带参数，一个带参数)进行测试；
2.1 执行RETL及RMS的环境变量：
su oracle
source /home/rfx/retl.env
source /home/oracle/rmsbatchenv.env

2.2 测试RETL程序：crncycdex.ksh
2.2.1 查找crncycdex.ksh所在目录：
find /d01/app/retail/rms -name crncycdex.ksh
结果：
/d01/app/retail/rms/db/retlforRDW/rfx/src/crncycdex.ksh
查找crncycddm.schema所在目录：
find /d01/app/retail/rms -name crncycddm.schema
结果：
/d01/app/retail/rms/db/retlforRDW/rfx/schemas/crncycddm.schema

2.2.2 第一次在Liunx下执行：
cd /d01/app/retail/rms/db/retlforRDW/rfx/src
./crncycdex.ksh
时报找不到对应目录及文件，经测试需要做以下操作：
a) 修改MMHOME环境变量：
MMHOME=/d01/app/retail/rms/db/retlforRDW
export MMHOME
b) 建立目录：cd $MMHOME
mkdir log
mkdir error
mkdir data
cd $MMHOME/rfx
mkdir etc
c) 把$MMHOME/config 下的所有文件拷贝到$MMHOME/rfx/etc目录下面

2.2.3 重新在Linux下执行crncycdex.ksh RETL 程序(无参数，具体程序见附录1)：
cd /d01/app/retail/rms/db/retlforRDW/rfx/src
./crncycdex.ksh
　　　　　此次执行没有报任何错误，根据文档内容，检查在$MMHOME/data目录下是否产生crncycddm.txt文件：
find /d01/app/retail -name crncycddm.txt
结果为空，即没有产生相应的txt文件。

查看目录$MMHOME/log下产生的日志文件，显示如下：
crncycdex 15:40:24: Program started …
crncycdex 15:40:26: ERROR: Program failed – check /d01/app/retail/rms/db/retlforRDW/error/crncycdex.19960101

根据以上提示，接着查看目录$MMHOME/error下产生的错误日志文件crncycdex.19960101，显示如下：
—————————–
crncycdex 15:40:24: Program started …
—————————–
FATAL – retl 12.0.1 build 1142 (1.4.2_04-b05 :: Sun Microsystems Inc. :: i386 :: Linux :: 2.6.9-42.0.0.0.1.ELsmp)

FATAL – E111: Exception in [export:1] setting property “schemafile”.
E301: Error reading schema file (/d01/app/retail/rms/db/retlforRDW/rfx/schema/crncycddm.schema)!

根据错误提示，好像是schema文件有问题，但查看了此crncycddm.schema文件(见附件2)没有什么问题，所以不知道问题出在哪里？

2.2.4 在Linux下执行ivrcpildex.ksh RETL程序(有参数)：
cd /d01/app/retail/rms/db/retlforRDW/rfx/src
./ivrcpildex.ksh /d01/app/retail/rms/db/retlforRDW/log/ivrcpilddm.txt
产生的错误跟上面例子类似。

附录1 crncycdex.ksh：
#!/bin/ksh

########################################################
# Copyright (c) 2001, Retek Inc. All rights reserved.
# $Workfile: crncycdex.ksh $
# $Revision: $
# $Modtime: $
########################################################

########################################################
# Script extracts currencies dimension information from#
# RMS currencies table #
########################################################

export PROGRAM_NAME=”crncycdex”

. ${MMHOME}/rfx/etc/dwi_config.env
. ${LIB_DIR}/dwi_lib.ksh

message “Program started …”

${RETL_EXE} ${RETL_OPTIONS} << EOF

${DBREAD}

EOF

checkerror -e $? -m “Program failed – check ${ERR_FILE}”

# Remove the status file
rm ${STATUS_FILE}

message “Program completed successfully”

附录2 crncycddm.schema：

附录2 crncycddm.schema：

Hi，提供以下信息参考：

========
一、以前在安装RMS的时候，在RMS部分版本安装文档中,需要执行：Update RETL fro RDW
当时因为还没有安装RDW，所以这些动作没有执行，现在需要你补充执行一下。
——————————————–
1) /d01/app/retail/rms/12.0.2/rms_12.0.2_docs/Installation Guide/rms-1202-ig.pdf page12
Update RETL for RDW
Note: These steps only needs to be done if you are using RDW.
1. Change directories to STAGING_AREA/retl4RDW/rfx/
2. Copy all the files from this directory to retl install
cp -R * INSTALL_DIR/retl/rfx

备注：
$STAGING_AREA=/d01/app/retail/rms/12.0.2/dbserverunix
$INSTALL_DIR=/d01/app/retail/rms/db

———————————————
2) /d01/app/retail/rms/12.0.3/rms_12.0.3_docs/Installation Guide/rms-1203-ig.pdf page16

Update RETL for RDW
Note: These steps only need to be done if you are using RDW.
Note: Refer to Appendix A & Appendix B for further information on RETL setup
Note: Some clean up of the retl for RDW directories are needed. These instructions should solve these issues.
1. Create a new directory INSTALL_DIR/retlforRDW/rfx/etc
2. Change directories to INSTALL_DIR/retlforRDW/config
3. Copy all files from INSTALL_DIR/retlforRDW/config to INSTALL_DIR/retlforRDW/rfx/etc
cp -R * INSTALL_DIR/retl/rfx/etc
4. Change directories to INSTALL_DIR/retlforRDW/rfx
5. Rename the schemas directory to schema
mv schemas schema
6. Change directories to STAGING_AREA/retlforRDW/
7. Copy all the files from this directory to retl install
cp -R * INSTALL_DIR/retlforRDW
备注：
$STAGING_AREA=/d01/app/retail/rms/12.0.3/dbserverunix
$INSTALL_DIR=/d01/app/retail/rms/db
——————————————–
3) /d01/app/retail/rms/12.0.4/rms_12.0.4_docs/Installation Guide/rms-1204-ig.pdf page18
Update RETL for RDW
Note: These steps only need to be done if you are using RDW.
Note: Refer to Appendix A & Appendix B for further information on RETL setup
1. Change directories to STAGING_AREA/retlforRDW/install.
2. Copy all the files from this directory to INSTALL_DIR/retlforRDW/install
cp -R * INSTALL_DIR/retlforRDW/install
备注：
$STAGING_AREA=/d01/app/retail/rms/12.0.4/dbserverunix
$INSTALL_DIR=/d01/app/retail/rms/db
———————————————
4) /d01/app/retail/rms/12.0.5/rms_12.0.5_docs/Installation Guide/rms-1205-ig.pdf page18
Update RETL for RDW
Note: These steps only need to be done if you are using RDW.
Note: Refer to Appendix A and Appendix B for further information on RETL setup
1. Change directories to STAGING_AREA/retl/rfx/etc.
2. Copy all the files from this directory to INSTALL_DIR/retlforRDW/rfx/etc.
cp -R * INSTALL_DIR/retlforRDW/rfx/etc
3. Change directories to STAGING_AREA/retl/rfx/lib.
4. Copy all the files from this directory to INSTALL_DIR/retlforRDW/rfx/lib.
cp -R * INSTALL_DIR/retlforRDW/rfx/lib
5. Change directories to STAGING_AREA/retl/rfx/schema.
6. Copy all the files from this directory to INSTALL_DIR/retlforRDW/rfx/schema.
cp -R * INSTALL_DIR/retlforRDW/rfx/schema
7. Change directories to STAGING_AREA/retl/rfx/src.
8. Copy all the files from this directory to INSTALL_DIR/retlforRDW/rfx/src.
cp -R * INSTALL_DIR/retlforRDW/rfx/src
备注：
$STAGING_AREA=/d01/app/retail/rms/12.0.5/dbserverunix
$INSTALL_DIR=/d01/app/retail/rms/db

===============================================
注意在：rms-1203-ig.pdf 中提到：
5. Rename the schemas directory to schema
mv schemas schema

是否schemas 与 schema目录名上有差异 导致找不到schema文件？

现完成ivrcpildex.ksh的测试，并在 $MMHOME/data 目录下面产生从数据库获得的数据文件：crncycddm.txt；

测试过程中产生的几个错误及解决方案如下：

报错一：
—————————–
crncycdex 15:40:24: Program started …
—————————–
FATAL – retl 12.0.1 build 1142 (1.4.2_04-b05 :: Sun Microsystems Inc. :: i386 :: Linux :: 2.6.9-42.0.0.0.1.ELsmp)
FATAL – E111: Exception in [export:1] setting property “schemafile”.
E301: Error reading schema file (/d01/app/retail/rms/db/retlforRDW/rfx/schema/crncycddm.schema)!
解决方案：修改 $MMHOME/rfx 目录下 schemas 为 schema
cd /d01/app/retail/rms/db/retlforRDW/rfx
mv schemas schema

报错二：
—————————–
crncycdex 11:28:34: Program started …
—————————–
Starting the threads
FATAL – retl 12.0.1 build 1142 (1.4.2_04-b05 :: Sun Microsystems Inc. :: i386 :: Linux :: 2.6.9-42.0.0.0.1.ELsmp)
FATAL – E135: Exception in operator [oraread:4]
E415: Unable to obtain connection to database.  (connectionstring=”jdbc:oracle:thin:@mspdev38:1525:cs0110g”, userid=”rms12dev”)
— SQLException caught —
java.sql.SQLException: Io exception: The Network Adapter could not establish the connectionMessage:   Io exception: The Network Adapter could not establish the connectionXOPEN SQL State:  null
Database Vendor Driver/Source Error Code: 17002
解决方案：修改 $MMHOME/rfx/etc目录下的 dwi_config.env 文件
find $MMHOME -type f -print | xargs grep “mspdev38″
结果：/d01/app/retail/rms/db/retlforRDW/config/dwi_config.env:export ORACLE_HOST=”mspdev38″
      /d01/app/retail/rms/db/retlforRDW/rfx/etc/dwi_config.env:export ORACLE_HOST=”mspdev38″
cd $MMHOME/rfx/etc
vi dwi_config
修改dwi_config.env文件中的 ORACLE_HOST=”syfr12″、ORACLE_PORT=”1521″、DBNAME=”RETL”

报错三：
—————————–
crncycdex 12:03:36: Program started …
—————————–
Starting the threads
FATAL – retl 12.0.1 build 1142 (1.4.2_04-b05 :: Sun Microsystems Inc. :: i386 :: Linux :: 2.6.9-42.0.0.0.1.ELsmp)
FATAL – E135: Exception in operator [oraread:4]
E419: SQL Exception
— SQLException caught —
Query: SELECT CURRENCY_CODE CRNCY_CDE_IDNT,
CURRENCY_DESC CRNCY_CDE_DESC
FROM rmssedev121.CURRENCIES
java.sql.SQLException: ORA-00942: table or view does not exist
Message:   ORA-00942: table or view does not exist
XOPEN SQL State:  42000
Database Vendor Driver/Source Error Code: 942
解决方案：修改 $MMHOME/rfx/etc目录下的 dwi_config.env 文件
cd $MMHOME/rfx/etc
vi dwi_config
修改dwi_config.env文件中的 RMS_OWNER=”RMS12DEV”

注意：以上测试RMS的RETL时，MMHOME环境变量修改为：
MMHOME=/d01/app/retail/rms/db/retlforRDW
export MMHOME