Unusable Index Issue

kkumardatastage · Post by **kkumardatastage** » Mon Nov 16, 2009 2:00 pm

Hi

I have a job that loads data into a table [Source(dataset)---->target(oracle stage)].

The data loads correctly into the table but the index becomes corrupt and has a status of 'Unusable'. I can correct this problem by manually rebuilding the indices through a sql client but shouldn't have to.

The target write method is Load and write mode is Append. I have tried it with the Index Mode = Rebuild as well as Maintainence but still have the same problem.

Is this an issue with DS or could it be an Oracle permission issue? DS is using the same credentials that is used to manually rebuild the indices through a sql client.

Thanks
k-v

theone · Post by **theone** » Mon Nov 16, 2009 2:41 pm

Enter Environmental variable APT_ORACLE_LOAD_OPTIONS as (DIRECT=FALSE,PARALLEL=FALSE) and do not provide any index options at load. That should work.

kkumardatastage · Post by **kkumardatastage** » Tue Nov 17, 2009 9:27 am

Hi

thanks for your replay, please can you tell me where i need to give the Environmental variable APT_ORACLE_LOAD_OPTIONS as (DIRECT=FALSE,PARALLEL=FALSE), do i need to go into adm--> properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and do i need to set the values as DIRECT=FALSE,PARALLEL=FALSE is that the wright thing to do. please can u help me in issue.

Thanks
k

theone · Post by **theone** » Tue Nov 17, 2009 10:01 am

In Job properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and set the values as (DIRECT=FALSE,PARALLEL=FALSE)

chulett · Post by **chulett** » Tue Nov 17, 2009 10:37 am

You need to understand that what that will get you is a conventional load, strictly transactional inserts rather than any kind of 'bulk' operation, which is why it will "work" with indexes and constraints in place.

kkumardatastage · Post by **kkumardatastage** » Tue Nov 17, 2009 4:13 pm

Hi

Thanks for your replay i used the following steps to get the Indexs in my job
In Job properties-->environmental variable--->Operator Specific---> APT_ORACLE_LOAD_OPTIONS(right now the Value box is empty) and set the values as (DIRECT=FALSE,PARALLEL=FALSE)

But the job aborted and i got the following error in log:

Ora_Out,0: The call to sqlldr failed; the return code = 256;
please see the loader logfile: /opt/IBM/InformationServer/Server/Scratch/ora.14708.478299.0.log for details.

Ora_Table_Out,0: Log file contents:

Ora_Table_Out,0: The runLocally() of the operator failed.

Following is the log for details.

SQL*Loader: Release 10.2.0.4.0 - Production on Tue Nov 17 16:48:36 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 1.
Expecting keyword LOAD, found "DIRECT".
DIRECT=FALSE,PARALLEL=FALSE

Thanks
k

chulett · Post by **chulett** » Tue Nov 17, 2009 4:23 pm

Set the value as 'OPTIONS(DIRECT=FALSE,PARALLEL=FALSE)'

kkumardatastage · Post by **kkumardatastage** » Mon Nov 23, 2009 10:10 am

Thanks for your help.

i solved this issue using Job param as APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=FALSE)

Magesh_bala · Post by **Magesh_bala** » Fri Dec 11, 2009 5:43 am

I have got the same error, But after enabling the options its working fine. But now Job takes almost 24 hrs to complete early it takes only 8 hrs. Can any one tell me the right options to use?

chulett · Post by **chulett** » Fri Dec 11, 2009 8:14 am

Define 'right'. You turned a parallel bulk load into regular sql inserts, so of course it takes longer now. What problems were you having before you made this change?

Magesh_bala · Post by **Magesh_bala** » Mon Dec 14, 2009 12:59 am

I have enabled the below options
OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)

Please let me know the Right options to use? Your help will be highly appreciated.

Magesh_bala · Post by **Magesh_bala** » Mon Dec 14, 2009 1:16 am

I was trying to load the huge data from my source system into the Oracle table, It got aborted due to the Table index problem "Unusable" status. So they have enabled the below option
"OPTIONS(DIRECT=FALSE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)"

Please let me know what is the right option to choose, I need to reduce the PROD run timings?

chulett · Post by **chulett** » Mon Dec 14, 2009 7:15 am

As long as you specify DIRECT=FALSE you are doing a 'conventional' load, which means transactional SQL inserts. To 'reduce the run timings' you'd need to get the direct load working and that means handling indexes.