Oracle Direct Path Load Problem

siauchun84 · Post by **siauchun84** » Thu Oct 23, 2008 12:43 am

I have 1 database loading job.
Input of the job is a datastage which has been remove duplicated base on the DB primary key.
Output stage is the Oracle DB stage. In loading, I use the direct path loading by using $APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=TRUE,PARALLEL=FALSE,SKIP_INDEX_MAINTENANCE=TRUE,DATE_CACHE=0).

Unfortunately, this job will auto abort after running around 6 minutes. The total records able to load around 2 millions of data.

Anyone have any idea how to overcome this?

Please help.

chulett · Post by **chulett** » Thu Oct 23, 2008 12:49 am

So... do we have to guess why the job aborted?

siauchun84 · Post by **siauchun84** » Thu Oct 23, 2008 1:06 am

chulett wrote:So... do we have to guess why the job aborted? ...

It would be better if you have any idea why it keep on abort within 6 minutes.

ArndW · Post by **ArndW** » Thu Oct 23, 2008 5:14 am

Craig - I think we do need to guess, since siauchun84 won't tell what the error message is.

My guess: column 3, record 54243 has a "h" character, but the column is defined as integer.

chulett · Post by **chulett** » Thu Oct 23, 2008 8:12 am

I almost added my own guess in the first post but thought the OP would get the hint without it and post the error(s) the job generates.

siauchun84 - how in the heck can anyone help you if you keep the details of your problem a secret?

John Smith · Post by **John Smith** » Thu Oct 23, 2008 7:39 pm

Please post the error message. Load failures could be due to many reasons and unless there is more information it's not possible to help you.

siauchun84 · Post by **siauchun84** » Thu Oct 23, 2008 9:11 pm

Thank you for all the respond.
I am quite new to the Datastage and really have no idea on it.

The following is the log file that I have exported out from the job:

DataStage Report - Detail Log for job: pxLoadCUST_PERS
Produced on: 10/22/2008 11:59:48 AM
Items: 1 - 40
Sorted on: Date Sorter

Item #: 1
Event ID: 0
Timestamp:: 2008-10-22 11:43:47
Type: Reset
User Name: username
Message:: Log cleared by user

Item #: 2
Event ID: 1
Timestamp:: 2008-10-22 11:46:08
Type: Control
User Name: username
Message:: Starting Job pxLoadCUST_PERS.
$APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=TRUE,PARALLEL=FALSE,SKIP_INDEX_MAINTENANCE=TRUE,DATE_CACHE=0)
DatasetFolder = E:\Devdata\Datasets\
DBPass = dbpass
DBUser = dbuser
DBServer = dbserver
DSFile = INSERTDB.ds
UpdDSFile = UPDATEDB.ds
TableName = CUST_PERS_DEV
DSJobController = seqMECUSTPERS

Item #: 3
Event ID: 2
Timestamp:: 2008-10-22 11:46:08
Type: Info
User Name: username
Message:: Attached Message Handlers:
Project message handler: WARNING_HANDLER

Item #: 4
Event ID: 3
Timestamp:: 2008-10-22 11:46:09
Type: Info
User Name: username
Message:: Environment variable settings:
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APT_BUFFER_DISK_WRITE_INCREMENT=4194304
APT_BUFFER_MAXIMUM_MEMORY=8388608
APT_COMPILEOPT=-W/TP -W/EHa -DAPT_USE_ANSI_IOSTREAMS -c -W/Zc:wchar_t-
APT_COMPILER=cxx
APT_CONFIG_FILE=D:\IBM\InformationServer\Server\Configurations\2node_try.apt
APT_DISABLE_COMBINATION=1
APT_DUMP_SCORE=1
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_LINKER=cxx
APT_LINKOPT=-s -W/dll -W/base:0x50000000 -W/Zc:wchar_t-
APT_MONITOR_MINTIME=10
APT_MONITOR_SIZE=1000000
APT_MONITOR_TIME=5
APT_OPERATOR_REGISTRY_PATH=D:\IBM\InformationServer\Server\Projects\DEVPROJ\buildop
APT_ORACLE_LOAD_OPTIONS=OPTIONS(DIRECT=TRUE,PARALLEL=FALSE,SKIP_INDEX_MAINTENANCE=TRUE,DATE_CACHE=0)
APT_ORCHHOME=D:/IBM/InformationServer/Server/PXEngine
APT_STRING_PADCHAR=
APT_USE_CRLF=1
ArchiveFolder=F:\DW-DEV-SRC-BACKUP\
BELL=^G
CLASSPATH=.;D:\IBM\SQLLIB\java\db2java.zip;D:\IBM\SQLLIB\java\db2jcc.jar;
D:\IBM\SQLLIB\java\sqlj.zip;
D:\IBM\SQLLIB\java\db2jcc_license_cu.jar;
D:\IBM\SQLLIB\bin;
D:\IBM\SQLLIB\java\common.jar
COMPUTERNAME=computer
ClusterLog=C:\WINDOWS\Cluster\cluster.log
ComSpec=C:\WINDOWS\system32\cmd.exe
CommonProgramFiles=C:\Program Files\Common Files
DB2INSTANCE=DB2
DBPass=LDI@99V@=9;L0OD
DBServer=dbserver
DBUser=dbuser
DISPLAY=:0.0
DSIPC_OPEN_TIMEOUT=3600
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
DS_TDM_PIPE_OPEN_TIMEOUT=259200
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_USERNO=-13336
DatasetFolder=E:\Devdata\Datasets\
Delay=20
ETLDeltaSrcFolder=F:\ETL-DEV-SRC-DELTA\
ETLDeltaTgtFolder=F:\ETL-DEV-TRG-DELTA\
ETLLookupFolder=F:\ETL-DEV-LOOKUP\
ETLMthlySrcFolder=F:\ETL-DEV-SRC-CONV\
ETLMthlyTgtFolder=F:\ETL-DEV-TRG-CONV\
FLAVOR=-1
FP_NO_HOST_CHECK=NO
HashFolder=E:\Devdata\HashFiles\
INCLUDE=D:\IBM\SQLLIB\INCLUDE;D:\IBM\SQLLIB\LIB;D:\Program Files\Microsoft Platform SDK\Include
LIB=D:\Program Files\Microsoft Platform SDK\Lib;
D:\IBM\SQLLIB\LIB
MAN_CHM_INDEX=C:/PROGRA~1/MKSTOO~1/etc/chm/tkutil.idx;
C:/PROGRA~1/MKSTOO~1/etc/chm/tkapi.idx;
C:/PROGRA~1/MKSTOO~1/etc/chm/tcltk.idx;
C:/PROGRA~1/MKSTOO~1/etc/chm/tkcurses.idx
MAN_HTM_PATHS=C:/PROGRA~1/MKSTOO~1/etc/htm/perl;
C:/PROGRA~1/MKSTOO~1/etc/htm/perl/pod;
C:/PROGRA~1/MKSTOO~1/etc/htm/perl/ext;
C:/PROGRA~1/MKSTOO~1/etc/htm/perl/lib
MAN_TXT_INDEX=C:/PROGRA~1/MKSTOO~1/etc/tkutil.idx;
C:/PROGRA~1/MKSTOO~1/etc/tkapi.idx;
C:/PROGRA~1/MKSTOO~1/etc/tcltk.idx;
C:/PROGRA~1/MKSTOO~1/etc/tkcurses.idx
MaxLoopWait=5
NUMBER_OF_PROCESSORS=16
NUTCROOT=C:\PROGRA~1\MKSTOO~1
OS=Windows_NT
OSH_STDOUT_MSG=1
PATH=D:\IBM\InformationServer\Server\PXEngine\bin;
D:\IBM\InformationServer\Server\PXEngine\osh_wrappers;
D:\IBM\InformationServer\Server\PXEngine\user_osh_wrappers;
D:\IBM\InformationServer\Server\PXEngine\user_osh_wrappers;
D:\IBM\InformationServer\Server\DSComponents\bin;
D:\IBM\InformationServer\Server\DSParallel;
D:\IBM\InformationServer\Server\Projects\DEVPROJ\RT_BP1696.O;
D:\IBM\InformationServer\Server\DSEngine\bin;
D:\IBM\InformationServer\Server\Projects\DEVPROJ\buildop;
D:\IBM\InformationServer\Server\Projects\DEVPROJ\wrapped;
D:\oracle\ora92\bin;C:\PROGRA~1\MKSTOO~1\mksnt;
C:\PROGRA~1\MKSTOO~1\bin;
C:\PROGRA~1\MKSTOO~1\bin\X11;
C:\PROGRA~1\MKSTOO~1\mksnt;
D:\IBM\InformationServer\ASBNode\lib\cpp;
D:\IBM\InformationServer\ASBNode\apps\proxy\cpp\vc60\MT_dll\bin;
C:\Program Files\Oracle\jre\1.3.1\bin;
C:\Program Files\Oracle\jre\1.1.8\bin;
C:\WINDOWS\system32;
C:\WINDOWS;
C:\WINDOWS\System32\Wbem;
C:\Program Files\IBM\Director\bin;
C:\Program Files\Common Files\IBM\ICC\cimom\bin;
D:\IBM\SQLLIB\BIN;D:\IBM\SQLLIB\FUNCTION;
D:\IBM\SQLLIB\SAMPLES\REPL;
D:\IBM\SQLLIB\BIN
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.sh;.ksh;.csh;.sed;.awk;.pl
PIDTOK=11576
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 15 Model 6 Stepping 8, GenuineIntel
PROCESSOR_LEVEL=15
PROCESSOR_REVISION=0608
PWD=D:\IBM\InformationServer\Server\Projects\DEVPROJ
PX_DBCONNECTHOME=D:/IBM/InformationServer/Server/DSComponents
ProgramFiles=C:\Program Files
ROOTDIR=C:/PROGRA~1/MKSTOO~1
RejectFolder=E:\Devdata\Rejects\
SHELL=C:/PROGRA~1/MKSTOO~1/mksnt/sh.exe
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=E:\TEMP
TERM=console
TERMCAP=C:\PROGRA~1\MKSTOO~1\etc\termcap
TERMINFO=C:\PROGRA~1\MKSTOO~1\usr\lib\terminfo
TMP=E:\TEMP
TMPDIR=E:\dstemp
UNIVERSE_CONTROLLING_TERM=1
UNIVERSE_PARENT_PROCESS=13964
USER=username
USERPROFILE=C:\Documents and Settings\Default User
VS80COMNTOOLS=D:\Program Files\Microsoft Visual Studio 8\Common7\Tools\
WHO=DEVPROJ
WorkFolder=E:\Devdata\WorkFiles\
windir=C:\WINDOWS

Item #: 5
Event ID: 4
Timestamp:: 2008-10-22 11:46:09
Type: Info
User Name: username
Message:: Parallel job initiated

Item #: 6
Event ID: 5
Timestamp:: 2008-10-22 11:46:09
Type: Info
User Name: username
Message:: OSH script
# OSH / orchestrate script for Job pxLoadCUST_PERS compiled at 11:26:27 22 OCT 2008
#################################################################
#### STAGE: CUST_PERS
## Operator
orawrite
## Operator options
-dboptions '{user=[&"DBUser"],password=[&"DBPass"]}'
-table '[&"DBServer"].[&"TableName"]'
-server '[&"DBServer"]'
-mode append
## General options
[ident('CUST_PERS'); jobmon_ident('CUST_PERS'); seq]
## Inputs
0< [] 'CUST_PERS_LOAD:CustPers.v'
;
#################################################################
#### STAGE: CUST_PERS_LOAD
## Operator
copy
## General options
[ident('CUST_PERS_LOAD')]
## Inputs
0< [ds] '[&"DatasetFolder"][&"DSFile"]'
## Outputs
0> [modify (
PER_HLD_ORG_CD:not_nullable string[max=3]=PER_HLD_ORG_CD;
PER_CUST_KEY:not_nullable int32=PER_CUST_KEY;
PER_DRV_LIC_NO:nullable string[max=8]=PER_DRV_LIC_NO;
PER_DRV_LIC_EXP_DT:not_nullable timestamp=PER_DRV_LIC_EXP_DT;
PER_DRV_LIC_STATE:nullable string[max=2]=PER_DRV_LIC_STATE;
PER_DRV_LIC_CNTRY:nullable string[max=3]=PER_DRV_LIC_CNTRY;
PER_PASSPORT_NO:nullable string[max=15]=PER_PASSPORT_NO;
PER_PASSPORT_EXPDT:not_nullable timestamp=PER_PASSPORT_EXPDT;
PER_PASSPORT_STATE:nullable string[max=2]=PER_PASSPORT_STATE;
PER_PASSPORT_CNTRY:nullable string[max=3]=PER_PASSPORT_CNTRY;
PER_NATIONAL_ID:nullable string[max=15]=PER_NATIONAL_ID;
PER_CUST_DOB:not_nullable timestamp=PER_CUST_DOB;
PER_SEX_CD:nullable string[max=1]=PER_SEX_CD;
PER_HOUSE_VALUE:nullable decimal[18,0]=PER_HOUSE_VALUE;
PER_HOUSE_VALUE_DT:not_nullable timestamp=PER_HOUSE_VALUE_DT;
PER_EMP_DT:not_nullable timestamp=PER_EMP_DT;
PER_EMP_NM:nullable string[max=60]=PER_EMP_NM;
PER_EMP_NM_MDT:not_nullable timestamp=PER_EMP_NM_MDT;
PER_EMP_ADDR1:nullable string[max=40]=PER_EMP_ADDR1;
PER_EMP_ADDR2:nullable string[max=40]=PER_EMP_ADDR2;
PER_EMP_ADDR3:nullable string[max=40]=PER_EMP_ADDR3;
PER_EMP_ADDR_MDT:not_nullable timestamp=PER_EMP_ADDR_MDT;
PER_EMP_PHN_CNTRY:nullable int32=PER_EMP_PHN_CNTRY;
PER_EMP_PHN_AREACD:nullable int32=PER_EMP_PHN_AREACD;
PER_EMP_PHONE_NO:nullable int32=PER_EMP_PHONE_NO;
PER_EMP_PHONE_EXT:nullable int32=PER_EMP_PHONE_EXT;
PER_HOUSEMAKER_IND:nullable string[max=1]=PER_HOUSEMAKER_IND;
PER_STUDENT_IND:nullable string[max=1]=PER_STUDENT_IND;
PER_RETIRED_IND:nullable string[max=1]=PER_RETIRED_IND;
PER_UNEMPLOYED_IND:nullable string[max=1]=PER_UNEMPLOYED_IND;
PER_RENT_OWN_IND:nullable string[max=2]=PER_RENT_OWN_IND;
PER_RENT_IND_MDT:not_nullable timestamp=PER_RENT_IND_MDT;
PER_EMPCUS_F_P_IND:nullable string[max=1]=PER_EMPCUS_F_P_IND;
PER_EMPCUS_F_P_MDT:not_nullable timestamp=PER_EMPCUS_F_P_MDT;
PER_EMPCUS_T_P_IND:nullable string[max=1]=PER_EMPCUS_T_P_IND;
PER_EMPCUS_T_P_MDT:not_nullable timestamp=PER_EMPCUS_T_P_MDT;
PER_MARIT_STAT_CD:nullable string[max=1]=PER_MARIT_STAT_CD;
PER_INC_LEVEL_TOT:nullable decimal[18,0]=PER_INC_LEVEL_TOT;
PER_INC_EFF_DT:not_nullable timestamp=PER_INC_EFF_DT;
PER_CUST_OCCUP_CD:nullable string[max=4]=PER_CUST_OCCUP_CD;
PER_EDUC_LEVEL_CD:nullable string[max=2]=PER_EDUC_LEVEL_CD;
PER_EDUC_MAINT_DT:not_nullable timestamp=PER_EDUC_MAINT_DT;
PER_ETHNIC_IND_CD:nullable string[max=1]=PER_ETHNIC_IND_CD;
PER_CUST_DOD:not_nullable timestamp=PER_CUST_DOD;
PER_DOD_NOTIF_CD:nullable string[max=3]=PER_DOD_NOTIF_CD;
PER_EMP_REL_CUS_CD:nullable string[max=1]=PER_EMP_REL_CUS_CD;
PER_LANG_PRF_CD:nullable string[max=3]=PER_LANG_PRF_CD;
PER_LANGPRF_SRC_CD:nullable string[max=3]=PER_LANGPRF_SRC_CD;
PER_HEAR_IMPR_IND:nullable string[max=1]=PER_HEAR_IMPR_IND;
PER_SGHT_IMPR_IND:nullable string[max=1]=PER_SGHT_IMPR_IND;
PER_OTH_HDCAP_DESC:nullable string[max=40]=PER_OTH_HDCAP_DESC;
PER_NO_MINOR_DEP:nullable string[max=2]=PER_NO_MINOR_DEP;
PER_NO_OTHER_DEP:nullable string[max=2]=PER_NO_OTHER_DEP;
PER_TOT_NO_DEP_MDT:not_nullable timestamp=PER_TOT_NO_DEP_MDT;
PER_NET_WORTH:nullable decimal[18,0]=PER_NET_WORTH;
PER_NET_WORTH_DT:not_nullable timestamp=PER_NET_WORTH_DT;
PER_NET_WORTH_SRC:nullable string[max=8]=PER_NET_WORTH_SRC;
PER_EMAIL:nullable string[max=40]=PER_EMAIL;
PER_CUS_GREETING:nullable string[max=15]=PER_CUS_GREETING;
PER_CUS_LAST_NM:nullable string[max=20]=PER_CUS_LAST_NM;
PER_CUS_FIRST_NM:nullable string[max=30]=PER_CUS_FIRST_NM;
PER_CUS_MDLE_INIT:nullable string[max=1]=PER_CUS_MDLE_INIT;
PER_CUS_SUFFIX:nullable string[max=4]=PER_CUS_SUFFIX;
PER_CUS_CAREER_ID:nullable string[max=10]=PER_CUS_CAREER_ID;
PER_CUS_INITIALS:nullable string[max=4]=PER_CUS_INITIALS;
PER_INC_CATEGORY:nullable string[max=1]=PER_INC_CATEGORY;
PER_EFFECTIVE_DT:not_nullable timestamp=PER_EFFECTIVE_DT;
PER_EXPIRATION_DT:not_nullable timestamp=PER_EXPIRATION_DT;
PER_GREEN_CARD_NUM:nullable string[max=10]=PER_GREEN_CARD_NUM;
PER_GRN_CRD_EXP_DT:not_nullable timestamp=PER_GRN_CRD_EXP_DT;
PER_ANCESTOR_ORIGN:nullable string[max=18]=PER_ANCESTOR_ORIGN;
PER_BIRTHPLACE:nullable string[max=40]=PER_BIRTHPLACE;
PER_MIS_PERSON_IND:nullable int32=PER_MIS_PERSON_IND;
PER_CANT_GUARA_IND:nullable int32=PER_CANT_GUARA_IND;
PER_AU_LICEN_PLATE:nullable string[max=7]=PER_AU_LICEN_PLATE;
PER_OCCUPATE_POSIT:nullable int32=PER_OCCUPATE_POSIT;
PER_TOT_HHLD_MEMBR:nullable int32=PER_TOT_HHLD_MEMBR;
PER_RELIGIOUS_CODE:nullable string[max=10]=PER_RELIGIOUS_CODE;
PER_ASSOCIATION:nullable string[max=30]=PER_ASSOCIATION;
PER_HOBBY:nullable string[max=30]=PER_HOBBY;
PER_FAMFIN_DEC_MKR:nullable string[max=10]=PER_FAMFIN_DEC_MKR;
PER_TERMINATION_DT:not_nullable timestamp=PER_TERMINATION_DT;
PER_2ND_LANG:nullable string[max=3]=PER_2ND_LANG;
PER_3RD_LANG:nullable string[max=3]=PER_3RD_LANG;
PER_INCOME_RNG_IND:nullable string[max=1]=PER_INCOME_RNG_IND;
PER_REV_EMPLOYER:nullable string[max=60]=PER_REV_EMPLOYER;
PER_EMP_LENGTH:nullable string[max=2]=PER_EMP_LENGTH;
PER_SPOUSE_NAME:nullable string[max=150]=PER_SPOUSE_NAME;
PER_SPOUSE_NEW_ID:nullable string[max=20]=PER_SPOUSE_NEW_ID;
PER_SPOUSE_ID_CODE:nullable string[max=1]=PER_SPOUSE_ID_CODE;
PER_SPOUSE_OLD_ID:nullable string[max=20]=PER_SPOUSE_OLD_ID;
PER_LST_MAINT_DT:not_nullable timestamp=PER_LST_MAINT_DT;
PER_WEIGHT:nullable int32=PER_WEIGHT;
PER_HEIGHT:nullable int32=PER_HEIGHT;
PER_SMOKER_IND:nullable string[max=1]=PER_SMOKER_IND;
PER_STATUS_CD:nullable string[max=2]=PER_STATUS_CD;
keep
PER_HLD_ORG_CD,PER_CUST_KEY,PER_DRV_LIC_NO,PER_DRV_LIC_EXP_DT,
PER_DRV_LIC_STATE,PER_DRV_LIC_CNTRY,PER_PASSPORT_NO,PER_PASSPORT_EXPDT,
PER_PASSPORT_STATE,PER_PASSPORT_CNTRY,PER_NATIONAL_ID,PER_CUST_DOB,
PER_SEX_CD,PER_HOUSE_VALUE,PER_HOUSE_VALUE_DT,PER_EMP_DT,
PER_EMP_NM,PER_EMP_NM_MDT,PER_EMP_ADDR1,PER_EMP_ADDR2,
PER_EMP_ADDR3,PER_EMP_ADDR_MDT,PER_EMP_PHN_CNTRY,PER_EMP_PHN_AREACD,
PER_EMP_PHONE_NO,PER_EMP_PHONE_EXT,PER_HOUSEMAKER_IND,PER_STUDENT_IND,
PER_RETIRED_IND,PER_UNEMPLOYED_IND,PER_RENT_OWN_IND,PER_RENT_IND_MDT,
PER_EMPCUS_F_P_IND,PER_EMPCUS_F_P_MDT,PER_EMPCUS_T_P_IND,PER_EMPCUS_T_P_MDT,
PER_MARIT_STAT_CD,PER_INC_LEVEL_TOT,PER_INC_EFF_DT,PER_CUST_OCCUP_CD,
PER_EDUC_LEVEL_CD,PER_EDUC_MAINT_DT,PER_ETHNIC_IND_CD,PER_CUST_DOD,
PER_DOD_NOTIF_CD,PER_EMP_REL_CUS_CD,PER_LANG_PRF_CD,PER_LANGPRF_SRC_CD,
PER_HEAR_IMPR_IND,PER_SGHT_IMPR_IND,PER_OTH_HDCAP_DESC,PER_NO_MINOR_DEP,
PER_NO_OTHER_DEP,PER_TOT_NO_DEP_MDT,PER_NET_WORTH,PER_NET_WORTH_DT,
PER_NET_WORTH_SRC,PER_EMAIL,PER_CUS_GREETING,PER_CUS_LAST_NM,
PER_CUS_FIRST_NM,PER_CUS_MDLE_INIT,PER_CUS_SUFFIX,PER_CUS_CAREER_ID,
PER_CUS_INITIALS,PER_INC_CATEGORY,PER_EFFECTIVE_DT,PER_EXPIRATION_DT,
PER_GREEN_CARD_NUM,PER_GRN_CRD_EXP_DT,PER_ANCESTOR_ORIGN,PER_BIRTHPLACE,
PER_MIS_PERSON_IND,PER_CANT_GUARA_IND,PER_AU_LICEN_PLATE,PER_OCCUPATE_POSIT,
PER_TOT_HHLD_MEMBR,PER_RELIGIOUS_CODE,PER_ASSOCIATION,PER_HOBBY,
PER_FAMFIN_DEC_MKR,PER_TERMINATION_DT,PER_2ND_LANG,PER_3RD_LANG,
PER_INCOME_RNG_IND,PER_REV_EMPLOYER,PER_EMP_LENGTH,PER_SPOUSE_NAME,
PER_SPOUSE_NEW_ID,PER_SPOUSE_ID_CODE,PER_SPOUSE_OLD_ID,PER_LST_MAINT_DT,
PER_WEIGHT,PER_HEIGHT,PER_SMOKER_IND,PER_STATUS_CD;
)] 'CUST_PERS_LOAD:CustPers.v'
;
# End of OSH code

Item #: 7
Event ID: 6
Timestamp:: 2008-10-22 11:46:11
Type: Info
User Name: username
Message:: main_program: IBM WebSphere DataStage Enterprise Edition 8.0.1.4665
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved

Item #: 8
Event ID: 7
Timestamp:: 2008-10-22 11:46:11
Type: Info
User Name: username
Message:: main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded

Item #: 9
Event ID: 8
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 10
Event ID: 9
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 11
Event ID: 10
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 12
Event ID: 11
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 13
Event ID: 12
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 14
Event ID: 13
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 15
Event ID: 14
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 16
Event ID: 15
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 17
Event ID: 16
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 18
Event ID: 17
Timestamp:: 2008-10-22 11:46:16
Type: Warning
User Name: username
Message:: main_program: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 19
Event ID: 18
Timestamp:: 2008-10-22 11:46:16
Type: Info
User Name: username
Message:: main_program: APT configuration file: D:/IBM/InformationServer/Server/Configurations/2node_try.apt
{
node "node1"
{
fastname "computer"
pools ""
resource disk "E:\node1" {pools ""}
resource scratchdisk "E:\scratch1" {pools ""}
}
node "node2"
{
fastname "computer"
pools ""
resource disk "E:\node2" {pools ""}
resource scratchdisk "E:\scratch2" {pools ""}
}
}

Item #: 20
Event ID: 19
Timestamp:: 2008-10-22 11:46:16
Type: Info
User Name: username
Message:: main_program: This step has 3 datasets:
ds0: {E:/Devdata/Datasets/INSERTDB.ds
[pp] eSame=>eCollectAny
op0[2p] (parallel CUST_PERS_LOAD)}
ds1: {op0[2p] (parallel CUST_PERS_LOAD)
[pp] >>eCollectAny
op1[1p] (sequential APT_DBExportOperator in CUST_PERS)}
ds2: {op1[1p] (sequential APT_DBExportOperator in CUST_PERS)
->eCollectAny
op2[1p] (sequential APT_OraWriteSubOperator in CUST_PERS)}
It has 3 operators:
op0[2p] {(parallel CUST_PERS_LOAD)
on nodes (
node1[op0,p0]
node2[op0,p1]
)}
op1[1p] {(sequential APT_DBExportOperator in CUST_PERS)
on nodes (
node1[op1,p0]
)}
op2[1p] {(sequential APT_OraWriteSubOperator in CUST_PERS)
on nodes (
node1[op2,p0]
)}
It runs 4 processes on 2 nodes.

Item #: 21
Event ID: 20
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS: When checking operator: A sequential operator cannot preserve the partitioning
of the parallel data set on input port 0.

Item #: 22
Event ID: 21
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 23
Event ID: 22
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 24
Event ID: 23
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 25
Event ID: 24
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 26
Event ID: 25
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 27
Event ID: 26
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 28
Event ID: 27
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 29
Event ID: 28
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 30
Event ID: 29
Timestamp:: 2008-10-22 11:46:17
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 31
Event ID: 30
Timestamp:: 2008-10-22 11:51:51
Type: Warning
User Name: username
Message:: CUST_PERS,0: Invalid character(s) ([x0080]) found converting Unicode string (code point(s): [x0000][x0000][x0000][x0080]) to codepage windows-1252, substituting.

Item #: 32
Event ID: 31
Timestamp:: 2008-10-22 11:51:51
Type: Fatal
User Name: username
Message:: CUST_PERS,0: The call to sqlldr failed; the return code = 7;
please see the loader logfile: E:\scratch1/ora.9620.286000.0.log for details.

Item #: 33
Event ID: 32
Timestamp:: 2008-10-22 11:51:51
Type: Fatal
User Name: username
Message:: CUST_PERS,0: Log file contents:

Item #: 34
Event ID: 33
Timestamp:: 2008-10-22 11:51:51
Type: Fatal
User Name: username
Message:: CUST_PERS,0: The runLocally() of the operator failed.

Item #: 35
Event ID: 34
Timestamp:: 2008-10-22 11:51:51
Type: Info
User Name: username
Message:: CUST_PERS,0: Input 0 consumed 1 records.

Item #: 36
Event ID: 35
Timestamp:: 2008-10-22 11:51:57
Type: Fatal
User Name: username
Message:: CUST_PERS,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

Item #: 37
Event ID: 36
Timestamp:: 2008-10-22 11:51:57
Type: Fatal
User Name: username
Message:: main_program: Step execution finished with status = FAILED.

Item #: 38
Event ID: 37
Timestamp:: 2008-10-22 11:51:57
Type: Info
User Name: username
Message:: main_program: Startup time, 0:13; production run time, 5:34.

Item #: 39
Event ID: 38
Timestamp:: 2008-10-22 11:51:57
Type: Control
User Name: username
Message:: Job pxLoadCUST_PERS aborted.

Item #: 40
Event ID: 39
Timestamp:: 2008-10-22 11:51:57
Type: RunJob
User Name: username
Message:: (seqMECUSTPERS) <- pxLoadCUST_PERS: Job under control finished.

End of report.

And the following is the Ora Log

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Oct 22 11:46:18 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: ora.9620.286000.0.ctl
Data File: \\.\pipe\ora.9620.286000.fifo.0
File processing option string: "FIX 1309"
Bad File: ora.9620.286000.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table dbserver.CUST_PERS_DEV, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PER_HLD_ORG_CD 1:3 3 CHARACTER
PER_CUST_KEY 4:7 4 INTEGER
PER_DRV_LIC_NO 8:15 8 CHARACTER
NULL if 8:15 = BLANKS
PER_DRV_LIC_EXP_DT 16:34 19 DATE YYYY-MM-DD HH24:MI:SS
PER_DRV_LIC_STATE 35:36 2 CHARACTER
NULL if 35:36 = BLANKS
PER_DRV_LIC_CNTRY 37:39 3 CHARACTER
NULL if 37:39 = BLANKS
PER_PASSPORT_NO 40:54 15 CHARACTER
NULL if 40:54 = BLANKS
PER_PASSPORT_EXPDT 55:73 19 DATE YYYY-MM-DD HH24:MI:SS
PER_PASSPORT_STATE 74:75 2 CHARACTER
NULL if 74:75 = BLANKS
PER_PASSPORT_CNTRY 76:78 3 CHARACTER
NULL if 76:78 = BLANKS
PER_NATIONAL_ID 79:93 15 CHARACTER
NULL if 79:93 = BLANKS
PER_CUST_DOB 94:112 19 DATE YYYY-MM-DD HH24:MI:SS
PER_SEX_CD 113:113 1 CHARACTER
NULL if 113:113 = BLANKS
PER_HOUSE_VALUE 114:123 10 PACKED DECIMAL (18, 0)
NULL if 114:123 = 0X00000000000000000000(character '')
PER_HOUSE_VALUE_DT 124:142 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMP_DT 143:161 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMP_NM 162:221 60 CHARACTER
NULL if 162:221 = BLANKS
PER_EMP_NM_MDT 222:240 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMP_ADDR1 241:280 40 CHARACTER
NULL if 241:280 = BLANKS
PER_EMP_ADDR2 281:320 40 CHARACTER
NULL if 281:320 = BLANKS
PER_EMP_ADDR3 321:360 40 CHARACTER
NULL if 321:360 = BLANKS
PER_EMP_ADDR_MDT 361:379 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMP_PHN_CNTRY 380:383 4 INTEGER
NULL if 380:383 = 0X00000080(character '')
PER_EMP_PHN_AREACD 384:387 4 INTEGER
NULL if 384:387 = 0X00000080(character '')
PER_EMP_PHONE_NO 388:391 4 INTEGER
NULL if 388:391 = 0X00000080(character '')
PER_EMP_PHONE_EXT 392:395 4 INTEGER
NULL if 392:395 = 0X00000080(character '')
PER_HOUSEMAKER_IND 396:396 1 CHARACTER
NULL if 396:396 = BLANKS
PER_STUDENT_IND 397:397 1 CHARACTER
NULL if 397:397 = BLANKS
PER_RETIRED_IND 398:398 1 CHARACTER
NULL if 398:398 = BLANKS
PER_UNEMPLOYED_IND 399:399 1 CHARACTER
NULL if 399:399 = BLANKS
PER_RENT_OWN_IND 400:401 2 CHARACTER
NULL if 400:401 = BLANKS
PER_RENT_IND_MDT 402:420 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMPCUS_F_P_IND 421:421 1 CHARACTER
NULL if 421:421 = BLANKS
PER_EMPCUS_F_P_MDT 422:440 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EMPCUS_T_P_IND 441:441 1 CHARACTER
NULL if 441:441 = BLANKS
PER_EMPCUS_T_P_MDT 442:460 19 DATE YYYY-MM-DD HH24:MI:SS
PER_MARIT_STAT_CD 461:461 1 CHARACTER
NULL if 461:461 = BLANKS
PER_INC_LEVEL_TOT 462:471 10 PACKED DECIMAL (18, 0)
NULL if 462:471 = 0X00000000000000000000(character '')
PER_INC_EFF_DT 472:490 19 DATE YYYY-MM-DD HH24:MI:SS
PER_CUST_OCCUP_CD 491:494 4 CHARACTER
NULL if 491:494 = BLANKS
PER_EDUC_LEVEL_CD 495:496 2 CHARACTER
NULL if 495:496 = BLANKS
PER_EDUC_MAINT_DT 497:515 19 DATE YYYY-MM-DD HH24:MI:SS
PER_ETHNIC_IND_CD 516:516 1 CHARACTER
NULL if 516:516 = BLANKS
PER_CUST_DOD 517:535 19 DATE YYYY-MM-DD HH24:MI:SS
PER_DOD_NOTIF_CD 536:538 3 CHARACTER
NULL if 536:538 = BLANKS
PER_EMP_REL_CUS_CD 539:539 1 CHARACTER
NULL if 539:539 = BLANKS
PER_LANG_PRF_CD 540:542 3 CHARACTER
NULL if 540:542 = BLANKS
PER_LANGPRF_SRC_CD 543:545 3 CHARACTER
NULL if 543:545 = BLANKS
PER_HEAR_IMPR_IND 546:546 1 CHARACTER
NULL if 546:546 = BLANKS
PER_SGHT_IMPR_IND 547:547 1 CHARACTER
NULL if 547:547 = BLANKS
PER_OTH_HDCAP_DESC 548:587 40 CHARACTER
NULL if 548:587 = BLANKS
PER_NO_MINOR_DEP 588:589 2 CHARACTER
NULL if 588:589 = BLANKS
PER_NO_OTHER_DEP 590:591 2 CHARACTER
NULL if 590:591 = BLANKS
PER_TOT_NO_DEP_MDT 592:610 19 DATE YYYY-MM-DD HH24:MI:SS
PER_NET_WORTH 611:620 10 PACKED DECIMAL (18, 0)
NULL if 611:620 = 0X00000000000000000000(character '')
PER_NET_WORTH_DT 621:639 19 DATE YYYY-MM-DD HH24:MI:SS
PER_NET_WORTH_SRC 640:647 8 CHARACTER
NULL if 640:647 = BLANKS
PER_EMAIL 648:687 40 CHARACTER
NULL if 648:687 = BLANKS
PER_CUS_GREETING 688:702 15 CHARACTER
NULL if 688:702 = BLANKS
PER_CUS_LAST_NM 703:722 20 CHARACTER
NULL if 703:722 = BLANKS
PER_CUS_FIRST_NM 723:752 30 CHARACTER
NULL if 723:752 = BLANKS
PER_CUS_MDLE_INIT 753:753 1 CHARACTER
NULL if 753:753 = BLANKS
PER_CUS_SUFFIX 754:757 4 CHARACTER
NULL if 754:757 = BLANKS
PER_CUS_CAREER_ID 758:767 10 CHARACTER
NULL if 758:767 = BLANKS
PER_CUS_INITIALS 768:771 4 CHARACTER
NULL if 768:771 = BLANKS
PER_INC_CATEGORY 772:772 1 CHARACTER
NULL if 772:772 = BLANKS
PER_EFFECTIVE_DT 773:791 19 DATE YYYY-MM-DD HH24:MI:SS
PER_EXPIRATION_DT 792:810 19 DATE YYYY-MM-DD HH24:MI:SS
PER_GREEN_CARD_NUM 811:820 10 CHARACTER
NULL if 811:820 = BLANKS
PER_GRN_CRD_EXP_DT 821:839 19 DATE YYYY-MM-DD HH24:MI:SS
PER_ANCESTOR_ORIGN 840:857 18 CHARACTER
NULL if 840:857 = BLANKS
PER_BIRTHPLACE 858:897 40 CHARACTER
NULL if 858:897 = BLANKS
PER_MIS_PERSON_IND 898:901 4 INTEGER
NULL if 898:901 = 0X00000080(character '')
PER_CANT_GUARA_IND 902:905 4 INTEGER
NULL if 902:905 = 0X00000080(character '')
PER_AU_LICEN_PLATE 906:912 7 CHARACTER
NULL if 906:912 = BLANKS
PER_OCCUPATE_POSIT 913:916 4 INTEGER
NULL if 913:916 = 0X00000080(character '')
PER_TOT_HHLD_MEMBR 917:920 4 INTEGER
NULL if 917:920 = 0X00000080(character '')
PER_RELIGIOUS_CODE 921:930 10 CHARACTER
NULL if 921:930 = BLANKS
PER_ASSOCIATION 931:960 30 CHARACTER
NULL if 931:960 = BLANKS
PER_HOBBY 961:990 30 CHARACTER
NULL if 961:990 = BLANKS
PER_FAMFIN_DEC_MKR 991:1000 10 CHARACTER
NULL if 991:1000 = BLANKS
PER_TERMINATION_DT 1001:1019 19 DATE YYYY-MM-DD HH24:MI:SS
PER_2ND_LANG 1020:1022 3 CHARACTER
NULL if 1020:1022 = BLANKS
PER_3RD_LANG 1023:1025 3 CHARACTER
NULL if 1023:1025 = BLANKS
PER_INCOME_RNG_IND 1026:1026 1 CHARACTER
NULL if 1026:1026 = BLANKS
PER_REV_EMPLOYER 1027:1086 60 CHARACTER
NULL if 1027:1086 = BLANKS
PER_EMP_LENGTH 1087:1088 2 CHARACTER
NULL if 1087:1088 = BLANKS
PER_SPOUSE_NAME 1089:1238 150 CHARACTER
NULL if 1089:1238 = BLANKS
PER_SPOUSE_NEW_ID 1239:1258 20 CHARACTER
NULL if 1239:1258 = BLANKS
PER_SPOUSE_ID_CODE 1259:1259 1 CHARACTER
NULL if 1259:1259 = BLANKS
PER_SPOUSE_OLD_ID 1260:1279 20 CHARACTER
NULL if 1260:1279 = BLANKS
PER_LST_MAINT_DT 1280:1298 19 DATE YYYY-MM-DD HH24:MI:SS
PER_WEIGHT 1299:1302 4 INTEGER
NULL if 1299:1302 = 0X00000080(character '')
PER_HEIGHT 1303:1306 4 INTEGER
NULL if 1303:1306 = 0X00000080(character '')
PER_SMOKER_IND 1307:1307 1 CHARACTER
NULL if 1307:1307 = BLANKS
PER_STATUS_CD 1308:1309 2 CHARACTER
NULL if 1308:1309 = BLANKS

The following index(es) on table dbserver.CUST_PERS_DEV were processed:
index dbserver.PK_PER_DEV was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested

Table dbserver.CUST_PERS_DEV:
2162039 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2162039
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 2700
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Oct 22 11:46:18 2008
Run ended on Wed Oct 22 11:51:53 2008

Elapsed time was: 00:05:34.89
CPU time was: 00:02:43.90

Please advice.

John Smith · Post by **John Smith** » Thu Oct 23, 2008 11:57 pm

What is inthe sqlldr log file ?

please see the loader logfile: E:\scratch1/ora.9620.286000.0.log for details.

Are there any specific SQLLDR errors logged? Your answer may lie in the log file.

siauchun84 · Post by **siauchun84** » Fri Oct 24, 2008 12:44 am

Dear John Smith, the second quote is the details for the E:\scratch1/ora.9620.286000.0.log file that i copied out. Please advice.

chulett · Post by **chulett** » Fri Oct 24, 2008 7:14 am

Your sqlldr log shows that all records were processed successfully. However, since you enabled 'SKIP_INDEX_MAINTENANCE' during the direct path load, your Primary Key on the table was left in an unusable status. That issue from the call to sqlldr is what generated the fatal errors in your log.

You also have Unicode conversion warnings.

siauchun84 · Post by **siauchun84** » Sat Oct 25, 2008 7:49 am

chulett wrote:Your sqlldr log shows that all records were processed successfully. However, since you enabled 'SKIP_INDEX_MAINTENANCE' during the direct path load, your Primary Key on the table was left in an unusab ...

chulett, I have 7 millions of data but it only loaded 2 millions then aborted.

For your information, I have tried disable the "SKIP_INDEX_MAINTENANCE" before and it run slower and 1 millions data loaded then aborted. As i know, the job always aborted after run about 6 minutes. Please advice.

chulett · Post by **chulett** » Sat Oct 25, 2008 8:04 am

I don't see any evidence that you had more records to load than what it processed.

Table dbserver.CUST_PERS_DEV:
2162039 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2162039
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 2700
Total stream buffers loaded by SQL*Loader load thread: 0

If you want to talk about your "slower" load, post that log as well.

John Smith · Post by **John Smith** » Sun Oct 26, 2008 11:45 pm

Hi,

If you swap the Oracle stage and replace that with a sequential file stage (or even a Copy stage), can you confirm that you get 7 million records as output?
Your sqlldr log shows that there are only 2 million records.The error you get is just an index maintenance error which might explain why your job abort.

J S

siauchun84 · Post by **siauchun84** » Mon Oct 27, 2008 8:01 pm

John Smith wrote:Hi,

If you swap the Oracle stage and replace that with a sequential file stage (or even a Copy stage), can you confirm that you get 7 million records as output?
Your sqlldr log shows that there are only 2 million records.The error you get is just an index maintenance error which might explain why your job abort.

J S

Ya, I have swapped it to a sequential files and it shows me 7 million of records inside. Thats my main problem, the sqlldr log shows all ok but the job will automaticly aborted when it has run about 5 minutes 45 seconds using the Load method.
I have also tried to change the load to Upsert method and increase the arraysize to 50000 and it manage to inserted 7 millions of data in 1 hour.
The main concern is, why when I use the load method, it not allow the job to run more than 6 minutes while the upsert method is applicable? Anyone have any idea on it?

John Smith · Post by **John Smith** » Mon Oct 27, 2008 11:56 pm

Is there any data in the bad file "Bad File: ora.9620.286000.0.log.bad "
did your upsert loaded ALL the records or is there a discrepancy in the records loaded? can you provide exact input and exact count of records loaded ?