Oracle Load option - Encountering a error

DSDexter · Post by **DSDexter** » Mon Jun 23, 2008 4:32 am

Hi all,

I have a job which has source as sequential file and target as Oracle table.

The job is pure data loading with some validations. The target table on Oracle has two indexes, Due to which my inserts are very very slow.

There are about 1.5 millions records in the seq. file. The insert is causing the job to run for almost 32 min

. So i am using the LOAD option to load the target tables, with the following sub properties.

Code: Select all


Disable Constraints - True
Index Mode - Rebuild
 Add COMPUTE STATISTICS clause - True
 Add NOLOGGING clause - True.

On running the job I am getting the following error

Code: Select all

main_program: Fatal Error: Invalid character   encountered in table <table_name>

There is no .log file created when the job is executed. What could possibly be going wrong?

Any inputs, will be appreciated.

ArndW · Post by **ArndW** » Mon Jun 23, 2008 5:46 am

My first guess is that you have an invalid character in your table name. Can you cut-and-paste the name to this thread?

DSDexter · Post by **DSDexter** » Mon Jun 23, 2008 7:26 am

ArndW wrote:My first guess is that you have an invalid character in your table name. Can you cut-and-paste the name to this thread? ...

Andrw the table name is "evdf_aft_cpr_raw".

Do you find anything wrong in this name

ArndW · Post by **ArndW** » Mon Jun 23, 2008 7:40 am

Apart from being cryptic, there seems to be nothing wrong with the name. I am not at a DataStage PC right now, but recall that you can set the load stage to keep the control files and scripts that it writes, could you set that to "true" and use a binary text editor to ensure that there are no excess non-printable characters in the table name?

DSDexter · Post by **DSDexter** » Mon Jun 23, 2008 8:50 am

Andrw,

Actually there are before each row trigger being added (We were not aware of this

).

I suspect that this migt be causing the load issues

hello105 · Post by **hello105** » Mon Jun 23, 2008 11:17 pm

Hi,maybe this can help you:

Oracle direct path load compatibility
It is not possible to perform an Oracle direct path load using an Oracle 10.2 client to an Oracle 9 server. Starting with Oracle 9i, the client version must be the same as or earlier than the server version. If you upgrade from an earlier version of WebSphere DataStage and have jobs that use the Oracle Enterprise stage or the Oracle OCI Load plug-in stage, these jobs might not work correctly for a direct path load unless the Oracle client and server version requirements are met. An alternative is not to use the direct path load feature. For the Oracle Enterprise stage, configure the APT_ORACLE_LOAD_OPTIONS environment variable, for example:
APT_ORACLE_LOAD_OPTIONS= 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)'

DSDexter wrote:Hi all,

I have a job which has source as sequential file and target as Oracle table.

The job is pure data loading with some validations. The target table on Oracle has two indexes, Due to which my inserts are very very slow.

There are about 1.5 millions records in the seq. file. The insert is causing the job to run for almost 32 min . So i am using the LOAD option to load the target tables, with the following sub properties.
Code: Select all
Disable Constraints - True
Index Mode - Rebuild
 Add COMPUTE STATISTICS clause - True
 Add NOLOGGING clause - True.
On running the job I am getting the following error
Code: Select all
main_program: Fatal Error: Invalid character   encountered in table <table_name>
There is no .log file created when the job is executed. What could possibly be going wrong?

Any inputs, will be appreciated.

DSDexter · Post by **DSDexter** » Tue Jun 24, 2008 1:56 am

Hi hello105,

Thanks for sharing that information. It did help. :D

I had not set the APT_ORACLE_LOAD_OPTIONS in my job, So the orcestrate was setting the two options to TRUE by default. by changing the options as you said in your post the job ran fine.

DSDexter · Post by **DSDexter** » Wed Jun 25, 2008 1:28 am

Hello Everyone,

I was able to load the DB with load option. I will shorthly mark the topic resolved :D , but was just curios to know something.

Can we set the row commit interval when using load option? Beacause I can see a hell lot of information messages being written to the logs. A piece of this information messge is as shown below.

Code: Select all


ORA_Raw_Tab,0: Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1792
Commit point reached - logical record count 1856
Commit point reached - logical record count 1920
Commit point reached - logical record count 1984
Commit point reached - logical record count 2048
Commit point reached - logical record count 2112
Commit point reached - logical record count 2176
Commit point reached - logical record count 2240
Commit point reached - logical record count 2304
Commit point reached - logical record count 2368
Commit point reached - logical record count 2432
Commit point reached - logical record count 2496
Commit point reached - logical record count 2560
Commit point reached - logical record count 2624
Commit point reached - logical record count 2688
Commit point reached - logical record count 2752
Commit point reached - logical record count 2816
Commit point reached - logical record count 2880
Commit point reached - logical record count 2944
Commit point reached - logical record count 3008
Commit point reached - logical record count 3072
Commit point reached - logical record count 3136
Commit point reached - logical record count 3200
Commit point reached - logical record count 3264
Commit point reached - logical record count 3328
Commit point reached - logical record count 3392
Commit point reached - logical record count 3456
Commit point reached - logical record count 3520
Commit point reached - logical record count 3560
Commit point reached - logical record count 3624
Commit point reached - logical record count 3688
Commit point reached - logical record count 3752
Commit point reached - logical record count 3816
Commit point reached - logical record count 3880
Commit point reached - logical record count 3944
Commit point reached - logical record count 4008
Commit point reached - logical record count 4072
Commit point reached - logical record count 4136
Commit point reached - logical record count 4200
Commit point reached - logical record count 4264
Commit point reached - logical record count 4328
Commit point reached - logical record count 4392
Commit point reached - logical record count 4456
Commit point reached - logical record count 4520
Commit point reached - logical record count 4584
Commit point reached - logical record count 4648
Commit point reached - logical record count 4712
Commit point reached - logical record count 4776
Commit point reached - logical record count 4840
Commit point reached - logical record count 4904
Commit point reached - logical record count 4968
Commit point reached - logical record count 5032
Commit point reached - logical record count 5096
Commit point reached - logical record count 5160
Commit point reached - logical record count 5224
Commit point reached - logical record count 5288
Commit point reached - logical record count 5352
Commit point reached - logical record count 5416
Commit point reached - logical record count 5480
Commit point reached - logical record count 5544
Commit point reached - logical record count 5608
Commit point reached - logical record count 5672
Commit point reached - logical record count 5736
Commit point reached - logical record count 5800
Commit point reached - logical record count 5864
Commit point reached - logical record count 5928
Commit point reached - logical record count 5992
Commit point reached - logical record count 6056
Commit point reached - logical record count 6120
Commit point reached - logical record count 6184
Commit point reached - logical record count 6248
Commit point reached - logical record count 6312
Commit point reached - logical record count 6376
Commit point reached - logical record count 6435
Commit point reached - logical record count 6499
Commit point reached - logical record count 6563
Commit point reached - logical record count 6627

Although I set the environment variable $APT_ORAUPSERT_COMMIT_ROW_INTERVAL = 100000

Still I can see the commits being occured more frequently. Is it not possible to set the row commit interval when using load option?

munch9 · Post by **munch9** » Wed Jun 25, 2008 10:03 am

If you want to change the commit frequency when using the load option you should set
APT_ORACLE_LOAD_OPTIONS= 'OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,ROWS=nnnnnn)'

Setting DIRECT=TRUE would also (normaly) give much better performance although I have never tried this on a table with triggers so do not know how this would behave.

Depending on your situation you could also specify the NONRECOVERABLE option if you do use DIRECT=TRUE

DSXchange

Oracle Load option - Encountering a error

Oracle Load option - Encountering a error

Re: Oracle Load option - Encountering a error