Resending:

Hi all
I have created 4 partitions (P1, P2, P3, P4) using an access file. Now I'm using USE with MODIFY to read the partition data with a common .mas file - say MASTER.mas. I'm able to retrieve the data. However, when I try to put a filter on any of the Indexed fields (in MASTER.mas), I am getting an error - " LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:"
FYI I have not created the indexes while the data was been loaded in the respective partitions. Is there any way I can create indexes in the data in the already created partitions. Also I tried using REBUILD command to create indexes in the partitions. However, since MASTER.FOC was not found, WebFOCUS gave an error regarding the same.

Please advise!

Regards
Syed
Using WebFOCUS 7.1.4 through Citrix

It's a FOCUS database? I didn't know there were ACCESS files with FOCUS databases. Why partition the database? The message about "linked file..." usually is triggered by an invalid JOIN.

I think this refers back to a previous post Francis, where Syed was partitioning his FOCUS datafiles. This uses an access file to create the physical/logical partitions.

I thought Syed had managed to get this working okay, with using an alternate master to load data and then a REBUILD INDEX to load the index afterwards using a master with Indices declared. (Looks like I am wrong here).

Basically working this way you need to ensure that the correct master files are accessed at the right time, pointing to the correct access files. As the partitioning is not used for MODIFY, only TABLE, then it would appear that the masters are being referenced incorrectly. The REBUILD INDEX has to work for the TABLE withy an Index to work correctly. So Syed, you need to double check the load and get the REBUILD INDEX working, then use those same masters for the TABLE requests. Please check the USE statements that are in place and ensure that the correct mfd is being referenced at the right time

Thanks Alan
I was assuming that we need a FOCUS database of the same .mas file to REBUILD the indexes. The indexes are now getting created. However, the limitation to be able to rebuild only 7 indexes still persists. In my database, I need to index 12 fields.
Any idea how to index all the fields?

Regards
Syed

Let me have a check back on something here. I have done this in the past, but cannot remember how. I need some time to look back in my code as also have a file with more than 7. Can you post one of your masters, with the indices please, and I'll ge abck to you.

Sure
I will send across the master file to you. Since I am located in India, perhaps you need to wait for sometime so that I can get the actual file.
Till then I am posting a part of the file which I had also posted in one of my earlier posts:
FILE=parttest, SUFFIX=FOC, ACCESS=/usr/lpp/IWAYEPTA/ibi/apps/irs_dev/PARTTEST
SEGNAME=ROOT_SEG, SEGTYPE=S1, $
FIELD=ACCT_DT, ALIAS=ACCT_DT, FORMAT=MDY, PROPERTY=KEY, $
FIELD=YR_I, ALIAS=YR_I, FORMAT=I5, $
FIELD=MO_I, ALIAS=MO_I, FORMAT=I5, $
FIELD=WK_I, ALIAS=WK_I, FORMAT=I5, $

Similarly I have other fields also which I wanted to index - 12 in all.
Let me know if you can recollect anything. I will post the actual code tomorrow (Indian Time).

Regards
Syed

Syed,
The 7 index limitation is only if you want to add more indexes. However, when you create the file you can define more than 7 indexes in your master.

Hi Danny
That's right. We can have more than 7 indexes in the master. However, my requirement is to create indexes once the FOCUS file is created without having indexes in the data. Hence, I am looking for the option of building more than 7 indexes.
Alan - as promised, I am sending you the actual master file code as below:

FILENAME=IRS_FIN, SUFFIX=FOC , ACCESS= IRS_FIN, $
SEGMENT=SEG01, SEGTYPE=S14, $
-* FIELD=FOCLIST, ALIAS=E01, FORMAT=I5, $
FIELDNAME=DHC_CO_C, ALIAS=E01, USAGE=I6, FIELDTYPE=I, $
FIELDNAME=LOC_I, ALIAS=E02, USAGE=I6, FIELDTYPE=I, $
FIELDNAME=INV_D, ALIAS=E03, USAGE=YYMD, FIELDTYPE=I, $
FIELDNAME=INV_TYPE_C, ALIAS=E04, USAGE=A2, FIELDTYPE=I, $
FIELDNAME=TRAN_C, ALIAS=E05, USAGE=A4, FIELDTYPE=I, $
FIELDNAME=DEPT_I, ALIAS=E06, USAGE=I6, FIELDTYPE=I, $
FIELDNAME=YR_I, ALIAS=E07, USAGE=I6, FIELDTYPE=I,
TITLE='FISCAL YEAR', $
FIELDNAME=MO_I, ALIAS=E08, USAGE=I6, FIELDTYPE=I,
TITLE='ACCT MONTH', $
FIELDNAME=WK_I, ALIAS=E09, USAGE=I6, FIELDTYPE=I,
TITLE='ACCT WK', $
FIELDNAME=TRAN_PRCS_D, ALIAS=E10, USAGE=YYMD, FIELDTYPE=I, $
FIELDNAME=FA_SRC_C, ALIAS=E11, USAGE=A2, FIELDTYPE=I, $
FIELDNAME=FA_TYPE_C, ALIAS=E12, USAGE=A2, FIELDTYPE=I, $
FIELDNAME=TRAN_SUB_C, ALIAS=E13, USAGE=A2, FIELDTYPE=I, $
FIELDNAME=INTF_TRAN_C, ALIAS=E14, USAGE=A6, FIELDTYPE=I, $
FIELDNAME=FIN_ADJ_A, ALIAS=E15, USAGE=P11.2, $
FIELDNAME=MO_N, ALIAS=E16, USAGE=A9,
TITLE='ACCT MO', $
FIELDNAME=FIN_RSLT_STAT_C, ALIAS=E17, USAGE=A2, $
FIELDNAME=LASTUPDATE_TS, ALIAS=E18, USAGE=HYYMDm, $

Let me know if you come up with any solution.

Regards
Syed

Hi Syed

Firstly I do have to ask if ALL the indices are necessary and if an S14 is really required? I'd be failing if I didn't.

Danny is correct in saying that if you issue a CREATE with the indices on, then the addition of 7 indices is not an issue as space will have been allocated on the CREATE.

I have though had problems REBUILD INDEX, even if created with them on, of more than 5 at any one time.

The way I have done this on the past, actually it is still running after 7 years, is to run the REBUILD INDEX through a loop, one value at a time:

  
-SET &CNTR=0;
-REPEAT :ENDBUILD 14 TIMES
-SET &CNTR=&CNTR+1;
-SET &FIELD = DECODE &CNTR(1  DHC_CO_C
-                          2  LOC_I
-                          3  INV_D
-                          4  INV_TYPE_C
-                          5  TRAN_C
-                          6  DEPT_I
-                          7  YR_I
-                          8  MO_I
-                          9  WK_I
-                          10 TRAN_PRCS_D
-                          11 FA_SRC_C
-                          12 FA_TYPE_C
-			   13 TRAN_SUB_C
-			   14 INTF_TRAN_C);			   
? USE			   
-RUN
REBUILD
INDEX
IRS_FIN
&FIELD
-RUN
-:ENDBUILD

Ensure that the correct master, without indices is used initially for a load, and then the master with the indices on is used for the REBUILD INDEX and any subsequent TABLE request. Remember that this file will probably triple in size, so allow enough space. If you are using partitions, this will also have to be run on each partition in turn, like MODIFY, REBUILD ignores the access file.

This message has been edited. Last edited by: Alan B,

Hi Alan,
We tried using your approach, i.e. using the code mentioned in your last post. We are getting the following error when we try to run the piece of code. Another noticeable point is that, I am able to query the non-indexed focus file/partition using WHERE RECORDLIMIT EQ n. This filter, along with the other filters on any index defined in the master file, is working fine. Eg.
PRINT *
WHERE RECORDLIMIT EQ 50
WHERE LOC_I EQ 10

Please let me know as to why I could be getting this error?

(FOC720) THE NUMBER OF INDEXES ADDED AFTER FILE CREATION EXCEEDS 7:

REBUILD NOT EXECUTED

BYPASSING TO END OF COMMAND

0 DIRECTORIES IN USE ARE:

/usr/lpp/IWAYEPTA/ibi/apps/irs_dev/irs_fin_adj_smry_part1.foc AS IRS_FIN

Enter option

1. REBUILD (Optimize the database structure)

2. REORG (Alter the database structure)

3. INDEX (Build/modify the database index)

4. EXTERNAL INDEX (Build/modify an external index database)

5. CHECK (Check the database structure)

6. TIMESTAMP (Change the database timestamp)

INDEX VALUES RETRIEVED= 509008

SORT COMPLETE .. RET CODE 0

(FOC720) THE NUMBER OF INDEXES ADDED AFTER FILE CREATION EXCEEDS 7:

REBUILD NOT EXECUTED

BYPASSING TO END OF COMMAND

Query used after using your code:
Result of query:

USE

&P_PATH/&PNAME1&IRS_DOT&IRS_FOC AS IRS_FIN
END

TABLE FILE IRS_FIN
PRINT
DHC_CO_C
INV_D
TRAN_PRCS_D
LASTUPDATE_TS
WHERE LOC_I EQ 4
END

Output:


(FOC1969) WARNING: USE/ALLOCATION FOR DDNAME IRS_FIN OVERRIDES ACCESSFILE

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: MO_I

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: WK_I

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: TRAN_PRCS_D

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: FA_SRC_C

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: FA_TYPE_C

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: TRAN_SUB_C

(FOC319) WARNING. THE FIELD IS INDEXED AFTER THE FILE WAS CREATED: INTF_TRAN_C

(FOC1969) WARNING: USE/ALLOCATION FOR DDNAME IRS_FIN OVERRIDES ACCESSFILE

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: LOC_I

BYPASSING TO END OF COMMAND


Regards
Syed

Syed

Ignore running a TABLE until you can get the indices built. If the INDEX isn't there it will cause an error.

You will have to break this down into smaller pieces to see what is really going on.

Create your 4 partitions. Use a CREATE file with the MFD with the indices ON before the load, if you can, and then load using the MFD without the indices. This will then give you space for the indices and this should work with the REBUILD INDEX, as I showed, with no problems.

If you cannot issue a CREATE before the load, then this should still work, though you should see WARNINGs come through but not ERRORs. The warnings should disappear after a succesful REBUILD.

Remember that you will have to issue the REBUILD INDEX for all 4 partitons seperately, with the correct USE in place.

If this is failiing, instead of using an MFD with all 12 indices, try it with just one and see what the results are. When succesful with one INDEX, try it again with more. Instead of using the full data, use a small subset of the data.

Additionally, try the REBUILD with an MFD with the indices but without reference to the access file, see if that os getting in the way.

Summarising:
CREATE the 4 partitions, with INDEX.
LOAD the 4 partitions without INDEX.
REBUILD INDEX the 4 partitons, one INDEX at a time, using a DM loop.
Try to:
Reduce data whilst testing until you have it working cleanly.
Remove reference to access file during the LOAD and REBUILD. (May help).
Don't run a TABLE until the REBUILD runs through cleanly. It will error, that's a given.
Reduce the number of indices to ensure the code logic is working correctly.

Let me know how you are getting on. This should work, but there maybe something unseen, as yet, getting in the way.

I would recommend that if you are using Release 7.6.x that you try this with XFOCUS files.
You create the files with the SUFFIX=XFOC, and than everything is the same. If all of the MAS files have different names you might be able to REBUILD from Focus to Xfocus.

You can index the field after the data source has already been created and populated
with records, by using the REBUILD facility with the INDEX option. A total of seven
indexes may be added to the data source after the file is created using REBUILD
INDEX. After seven indexes have been added to a data source in this way, you must
use the REORG option of the REBUILD facility before adding an eighth; otherwise the
following diagnostic message is issued:
(FOC720) THE NUMBER OF INDEXES ADDED AFTER FILE CREATION EXCEEDS 7

kp

Nobody is denying what the manual states, and that the declared limit for adding an index after file creation is 7.

But, and it may be platform dependant, I regularly add 14 indices after creation with a REBUILD INDEX, rebuilding one index at a time. Afterwards the file is fine, all indices work and there are no error or warning messages.