SELECTINDEX not found in VOC

zulfi123786 · Post by **zulfi123786** » Fri Aug 08, 2014 6:09 am

Hi,

We have a server routine which takes a date value and a key value as input and performs look up against a hashed file on the key and the input date being between Start and End dates in hashed file.

The routine is running fine since an year and all of a sudden it now throws

Code: Select all

Verb "SELECTINDEX" is not in your VOC.

Ran a check on the VOC file and below is what was found.

Code: Select all

>SELECT * FROM VOC WHERE NAME='SELECTINDEX';

0 records listed.
>SELECT COUNT(1) FROM VOC;
COUNT ( 1 )

      31135

>QUIT

Looks like the entry was wiped off.

Below is the command used in the routine

Code: Select all

SELECTINDEX 'NDC_CODE', INDEX.VALUE FROM FILE.VAR

Whats surprising is, the routine runs fine without any issues in Production but has a problem only in Development. Checked the Production VOC for the "SELECTINDEX" entry and it doesn't exist there too, still it runs fine.

Curious as to how this is possible also please help in adding the entry to VOC

Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Aug 08, 2014 3:56 pm

SELECTINDEX is a keyword in the BASIC language; it will not be in the VOC file.

What error is generated when routine containing the statement is executed?

Code: Select all

SELECTINDEX 'NDC_CODE', INDEX.VALUE FROM FILE.VAR

zulfi123786 · Post by **zulfi123786** » Fri Aug 08, 2014 11:02 pm

Hi Ray

Many thanks for the reply, the SELECTINDEX is used to create a record list and then a loop is defined to iterate over all records in the list to get the record whose start and end dates happen to contain input date in their range.

The routine doesn't throw any error but we figured out that the record list wasn't getting created and the control never flowed inside the loop due to which the routine returns null value. (Routine code pasted at the bottom)

The hashed file was scp'ed from production and not created in this environment but guess that doesn't make much of a difference.

To take a loser look I ran the selectindex command using DSExecute and it's output when displayed said the verb not in VOC.

When the below SELECTINDEX is replaced with SELECT the routine returns correct results, does it indicate an issue with index ?

Code: Select all

SELECTINDEX 'NDC_CODE', INDEX.VALUE FROM FILE.VAR

Code: Select all

SELECT FILE.VAR

Routine: Path, NdcCode and DataDate are input arguments

Code: Select all

COMMON /NDC_CODE/FILE.FLAG,FILE.VAR,HIGH.DATE

ENV = Path
NDC.CD = NdcCode
INPUT.DATE = DataDate

FILENAME=ENV:'/keyhash/NdcKeyLookup'

Ans= '0'

IF FILE.FLAG = 0 THEN
     OPENPATH FILENAME TO FILE.VAR ELSE
     Call DSLogFatal ('Cannot open file ':FILENAME, 'NdcKeyLookup')
     END
FILE.FLAG=1
END


HIGH.DATE = '0001-01-01'
INDEX.VALUE = NDC.CD


SELECTINDEX 'NDC_CODE', INDEX.VALUE FROM FILE.VAR

LOOP
WHILE READNEXT ID DO
     READ NDC.REC FROM FILE.VAR, ID ELSE
     Call DSLogFatal ('Read Failed - NDC Code Hash File Corrupted ':FILENAME, 'NdcKeyLookup')

RETURN (NDC.REC)
     END

EffDt = FIELD(ID,CHAR(251),2)

EndDt = NDC.REC<1>

IF EffDt<=INPUT.DATE and EndDt >= INPUT.DATE THEN
    Ans = NDC.REC<2>
    RETURN(Ans)
END

REPEAT
    RETURN(Ans)

ray.wurlod · Post by **ray.wurlod** » Sat Aug 09, 2014 1:59 pm

It might make a difference if you did not copy the index(es) for the hashed file. These are stored in a separate directory, called I_NdcKeyLookup.

Trying to run SELECTINDEX through DSExecute will always fail, because (as noted) SELECTINDEX is not a command (not in VOC); it is a part of the programming language.

zulfi123786 · Post by **zulfi123786** » Tue Aug 12, 2014 4:18 am

ray.wurlod wrote:It might make a difference if you did not copy the index(es) for the hashed file

Rightly pointed, there was an Index created on the Hashed file due to which the SELECTINDEX was failing.

I have created an INDEX as shown below but still the SELECTINDEX doesn't seem to work.

Code: Select all

>SETFILE /nhdb/unittest/RDM/keyhash/NdcKeyLookup_Zbkp123 myhash123

Pointer "myhash123" established in VOC file.
>LIST.DICT myhash123
DICT myhash123    04:59:40  08-12-14  Page    1

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            NdcKeyLookup_Zb 10L    S
                                                 kp123
NDC_CODE       D    0                            NDC_CODE        20L    S
EFF_DATE       D    1               D            EFF_DATE        10R    S
END_DATE       D    2               D            END_DATE        10R    S
NDC_KEY        D    3                            NDC_KEY         39L    S
@KEY           PH     NDC_CODE

@              PH     NDC_CODE
                      EFF_DATE
                      END_DATE
                      NDC_KEY
                      ID.SUP

7 records listed.
>
>CREATE.INDEX myhash123 NDC_CODE
>LIST.INDEX myhash123
Index name(s):  ALL
Alternate Key Index Summary for file myhash123
File........... myhash123
Indices........ 1 (0 A-type, 0 C-type, 1 D-type, 0 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name      Type  Build    Nulls  In DICT  S/M  Just Unique Field num/I-type
NDC_CODE         D    Required  Yes    Yes      S    L     N    0
                 NLS Collation Locale... US-ENGLISH

>SELECT COUNT(1) FROM myhash123 WHERE NDC_CODE='51432045503';
COUNT ( 1 )

          1

1 records listed.

Looks like I missed something. To take a closer look I created another short routine which returns the number of records scanned and for the above Index value "51432045503" there was no selection despite the records existence but when "SELECT" is used instead of SELECTINDEX it works fine.

Code: Select all

COMMON /NDC_CODE/FILE.FLAG,FILE.VAR,HIGH.DATE

ENV = Path
NDC.CD = NdcCode
INPUT.DATE = DataDate

FILENAME=ENV:'/keyhash/NdcKeyLookup_Zbkp123'

Ans= '0'

IF FILE.FLAG = 0 THEN
     OPENPATH FILENAME TO FILE.VAR ELSE
     Call DSLogFatal ('Cannot open file ':FILENAME, 'NdcKeyLookup')
     END
FILE.FLAG=1
END


HIGH.DATE = '0001-01-01'
INDEX.VALUE = NDC.CD


*SELECTINDEX 'NDC_CODE', '51432045503' FROM FILE.VAR

SELECT FILE.VAR


LOOP
WHILE READNEXT ID DO
     READ NDC.REC FROM FILE.VAR, ID ELSE
     Call DSLogFatal ('Read Failed - NDC Code Hash File Corrupted ':FILENAME, 'NdcKeyLookup')

RETURN (NDC.REC)
     END



Ans=Ans+1

REPEAT
    RETURN(Ans)

When the above is tested it Returns 418424 but when SELECTINDEX is uncommented and SELECT commented it returns 0.

Could you please kindly guide

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2014 4:48 am

You missed the BUILD.INDEX command to populate the index.

zulfi123786 · Post by **zulfi123786** » Tue Aug 12, 2014 6:50 am

Oh ! My Goodness

. The simplest mistakes are the toughest to find.

Many Thanks for pointing that out and it works.

Having born and brought up in the PX world, I have few dumb questions.

1.

Code: Select all

READ NDC.REC FROM FILE.VAR, ID ELSE

I guess the record from FILE.VAR (hashed file) is read into a Dynamic array NDC.REC, The ID intrigues my understanding. If the whole record is already read into NDR.REC what's special with ID

2. Does the NDC.REC contain all records for a matching key (it being a dynamic array) or it hold one record at a time looping over all records

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2014 4:34 pm

There can only be one record with the key value stored in variable ID. That's the whole point of a hashed file - the key value is processed through the hashing algorithm to return the address of the page on which the record is located (no need for indexes or table scans - very fast).
NDC.REC is a dynamic array that contains all the non-key fields of the record.