I am trying to use BTREE.EXTRACT to get more than 64k worth of keys and have been unable to. I have AREV 3.12. I created the following bit of code to test it out.

 Open 'DICT.PAYABLE' to dict Then

    search=PAYEE_CODE' :@VM: 'B*2' :@FM

    file  =PAYABLE'

    keys  ='

    opt   =S'

    opt=1

    opt='

    flag  ='

    Call BTREE.EXTRACT(search, file, dict, keys, opt, flag)

    length  =LEN(keys)

    num_keys=COUNT(keys, @VM) + (keys # '')

    DEBUG

 End Else

    mesg=|Unable to open the DICT.PAYABLE table.|'

    Call MSG(mesg)

 End

When I get to the debug num_keys is set to 12507, even though I have 12512 records that meet the selection criteria, flag is set to zero and opt is unchanged.

Does BTREE.EXTRACT not work for more than 64k worth of keys or is there something wrong with my test routine?

According to the user's manual…

"To search large indexes, you must indicate that you are willing to accept more than 64K of keys. BTREE.EXTRACT will then return to you a 'set handle', which you can use later to query for further keys."

Basically, the fifth value passed to BTREE.EXTRACT is the OPTION flag.

OPTION is for your base options, either null or 'S' for suppress.

OPTION indicates if you want to accept more than 64K of keys.

OPTION gets passed as null the first time, and the 'set handle' mentioned above gets returned here.

So, you can place your BTREE.EXTRACT call in a loop, with your exit condition based on the flag value, which is the final value passed to the function.

Just remember to initialize OPTIONS outside of your loop, or you will start a new query every time.

Matt Sorrell

Mat,

If you read the bit of code I put in my message I did set option as described in the manual … field 2 was set to 1 and field 3 was null.

When BTREE.EXTRACT returned the option variable was uncahnged, that is field 3 was stiil null !!! It is, according to the manual supposed to return the set handle if there are more than 64k worth of keys. The variable flag is also to be set to ] 0 if more than 64k worth of keys exist but this was set to zero indicating that the BTREE.EXTRACT was successfull and all the keys available were returned, even though I know more than 64k worth of keys existed. I even rebuilt the indexes on my table before I ran my test routine to ensure the indexes were up to date.

I am still looking for answers???

Michael,

Have you tried this with a different index, one that does not have your compressed key structure in there. There could be something in the key structure itself which is telling the system to bypass the recursive call.

akaplan@sprezzatura.com

Sprezzatura, Inc.

I have not tried it with a different index.

I am not sure what you mean by "compressed key structure"?

Please explain …

Thanks,

Michael

Didn't you mention in an earlier thread you had some sort of binary index, which keys containing chars from char 1 up through 230-something?

akaplan@sprezzatura.com

Sprezzatura, Inc.

No … it is an index on a standard varchar field.

Sorry, I was thinking of someone else.

Try passing in a D in options and bring back the debug information.

akaplan@sprezzatura.com

Sprezzatura, Inc.

I tried the "D" option and came up with the following;

The "Query Spec" contained the correct info.

The "Query Plan" contained the correct info.

The Select statement was correct and returned the correct number of keys (12512).

It then displayed the message "Performing SI operation"

I then checked the values of my variables.

STATS() was set to zero.

flag was set to zero

option was unchanged (ie option=D :@FM: 1 :@FM)

num_keys=12507

It seems to be working correctly EXCEPT that it does not set flag or return the set handle in the third parameter of option, as the documentation says it should, when there is more than 64k worth of keys.

Thanks,

Michael

View this thread on the forum...