Creating secondary index on hash file from a routine

[cfuller]

Hi all

I have a hash file being access using UV stage by partial key, for a multi-row lookup, that was performing very badly. I created an index on the lookup column, from the UV command line, reran the job and the performance was closer to what I expected. Now I want to create and build the index in a before stage routine, but I'm not getting any joy.

I have tried to utiles the code in the ExecTCL routine but to no avail.

Here is the code I'm using -

Code: Select all

      Call DSExecute("TCL", "CREATE.INDEX vHashFile vIndexCol", Output, SystemReturnCode)

Where vHashFile is the incoming hash file, and VinddexCol is the column to create the index on.

Any help appreciated...

[cfuller]

Did some more digging and altered the command to now be -

Code: Select all

      Command="CREATE.INDEX " : vHashFile : " " : vIndexCol
      Call DSExecute('UV', Command, Output, SystemReturnCode)

Silly mistake on my part....
(had local variable within quoted string, instead of concatinating it all together)

[ray.wurlod]

That may not be enough. If there are data in the hashed file, you will have an empty index.

Code: Select all

Command="CREATE.INDEX " : vHashFile : " " : vIndexCol 
Call DSExecute('UV', Command, Output, SystemReturnCode) 
Command="BUILD.INDEX " : vHashFile : " " : vIndexCol 
Call DSExecute('UV', Command, Output, SystemReturnCode) 

Check SystemReturnCode after each Call; a negative value indicates that a problem has occurred.

[cfuller]

Thanks Ray...

Actually I had the BUILD.INDEX further down in my routine, but seeing as how I couldn't get the CREATE.INDEX to work, the BUILD.INDEX was being bypassed due to the CREATE.INDEX failing....I supplied a code snippet, not the whole routine.

Cheers