Hi All,
Recently we upgraded webfocus server version 767 to 7611 after that we are facing problem in one report, Please find below for the error message.
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
-HTMLFORM VIN_PSR_RECEIPTS_HEADER
(FOC36225) UNABLE TO OPEN FILE VINPSR SPECIFIED BY WEBFOCUS TABLE

Could you please let me know what could be the issue.

Thanks,
Raghu

This message has been edited. Last edited by: Kerry,

From past posts in this forum regarding the 'Invalid root page' error, possible causes are:

• use of an index name longer than 12 characters with ON TABLE HOLD FORMAT FOCUS INDEX
  
• JOINs between fields that are not the same length or have different formats
  
• JOINs between fields that have the same length but one is fixed length and the other variable length
  

Unfortuntely, even if the cause is one of the above, this does not explain why the report worked in 767 but now fails in 7611. Have changes been made to the report code since the upgrade?

Hi Dan,
We haven't made any changes for the code .

Thanks,
Raghu

I am assuming this is a FOCUS file. Try doing a REBUILD on the file making sure to back it up first. If that doesn't fix it, then possibly the file was corrupted somehow. Do you have a backup? Do you still have 7.6.7?

quote:

this is a FOCUS file. Try doing a REBUILD on

Hi Ginny,
we haven't upgrade our local server ...In the local server is working fine ,but in the other environments(which are upgraded to 7.6.11) is not working.

Thanks,
Raghu

Is it possible the environment where 7.6.11 is running on a different version of Windows? 64 bit perhaps? Which may be causing a problem reading a FOCUS DB, if it is a FOCUS DB?

Did you regenerate the meta-data or just copy it from one environment to the other? I would regenerate...

Hi,
You may want to rebuild the FOCUS DBs manually using a combination of TABLE, TABLEF and TABLE through a physical view. For example, in an ABC structure, one over the other, you can use TABLE FILE X.C, dump the data into a HOLD file and load a fresh copy of the DB. This would use the "UP" pointers instead of the page and down pointers.
I would use this technique in the 7.6.7 version, if still available, and transport the data to the 7.6.11 version.

If this does not fix the problem, then you must go into debug mode and start eliminating features until the problem is gone. The last eliminated feature may be the culpret.
Hope this helps!
Nick

Have we determined that this is a FOCUS DB (type .FOC)? In the program that is having a problem are you reading the file by an index? (TABLE FILE VIN_PSR_RECEIPTS_HEADER.FIELDNAME) If it is try removing the index from the table command. Otherwise, please post your master and your code.

Hi ,
We are not using FOCUS DB.
We identified there is some join problem so we tried to put indexing but still we are getting invalid root page error.
please find below for code.
TABLE FILE CURRENCY_EXCHANGE_HOLD
PRINT CURRENCY_CODE
YEAR
EXCHANGE_RATE
ON TABLE HOLD AS CURR_EXCH FORMAT FOCUS INDEX YEAR
END
SQL
SELECT MIN(RD.COMGRP_GROUP_CODE) AS COMGRP_GROUP_CODE,
MIN(RD.COMGRP_DESC) AS COMGRP_DESC,
MIN(RD.COMMODITY_CODE) AS COMMODITY_CODE,
MIN(RD.COMMODITY_NAME) AS COMMODITY_NAME,
MIN(RD.REGION_CODE) AS REGION_CODE,
MIN(RD.REGION_NAME) AS REGION_NAME,
MIN(RD.PRODUCTLINE_CODE) AS PRODUCTLINE_CODE,
MIN(RD.PRODUCTLINE_DESC) AS PRODUCTLINE_DESC,
RD.PLANT_CODE,
MIN(RD.PLANT_NAME) AS PLANT_NAME,
RD.MST_CODE,
MIN(RD.SUPPLIER_NAME) AS SUPPLIER_NAME,
RD.RLDC_PART_NUMBER,
MIN(RD.PART_DESC) AS PART_DESC,
SUM(CEH1.EXCHANGE_RATE * RD.SPND * (1/CEH2.EXCHANGE_RATE)) AS SPND,
SUM(RD.QTY) AS QTY
FROM RECEIPTS_DTL RD,
CURR_EXCH CEH1,
CURR_EXCH CEH2
WHERE
RD.YR = CEH1.YEAR
AND
CEH1.CURRENCY_CODE = 'USD'
AND RD.YR = CEH2.YEAR
AND RD.RLDC_CUR_CODE = CEH2.CURRENCY_CODE
GROUP BY RD.MST_CODE,
RD.RLDC_PART_NUMBER,
RD.PLANT_CODE;
TABLE ON TABLE HOLD AS HLD_PSR
END
TABLE FILE HLD_PSR
PRINT *
END
-EXIT
0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0
SEGMENTS: INPUT = 1 UPDATED = 0 DELETED = 0
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
Invalid root page
Invalid root page
Invalid root page
Invalid root page

We have n't put indexing on EXCHANGE_RATE,CURRENCY_CODE columns.
because it is not supporting more than 12 characters.

Thanks,
Raghu

I would try to eliminate the double join to your FOCUS hold file by using a multi-verb request to calculate the exchange rate factor for your SPND conversion. You can use a DEFINE to shorten the CURRENCY_CODE field name so you can INDEX the field.

DEFINE FILE CURRENCY_EXCHANGE_HOLD
 USD_RATE/D??.???? = IF CURRENCY_CODE EQ 'USD' THEN EXCHANGE_RATE ELSE 0 ;
 CUR_CODE/A3       = CURRENCY_CODE ;
END
-*
TABLE FILE CURRENCY_EXCHANGE_HOLD
 SUM MAX.USD_RATE NOPRINT
 BY YEAR
 PRINT EXCHANGE_RATE NOPRINT
 COMPUTE FACTOR/D??.???? = C1 * ( 1 / EXCHANGE_RATE );
 BY YEAR
 BY CUR_CODE
 ON TABLE HOLD AS CURR_EXCH FORMAT FOCUS INDEX YEAR CUR_CODE
END
-*
SQL
SELECT MIN(RD.COMGRP_GROUP_CODE) AS COMGRP_GROUP_CODE,
       MIN(RD.COMGRP_DESC) AS COMGRP_DESC,
       MIN(RD.COMMODITY_CODE) AS COMMODITY_CODE,
       MIN(RD.COMMODITY_NAME) AS COMMODITY_NAME,
       MIN(RD.REGION_CODE) AS REGION_CODE,
       MIN(RD.REGION_NAME) AS REGION_NAME,
       MIN(RD.PRODUCTLINE_CODE) AS PRODUCTLINE_CODE,
       MIN(RD.PRODUCTLINE_DESC) AS PRODUCTLINE_DESC,
       RD.PLANT_CODE,
       MIN(RD.PLANT_NAME) AS PLANT_NAME,
       RD.MST_CODE,
       MIN(RD.SUPPLIER_NAME) AS SUPPLIER_NAME,
       RD.RLDC_PART_NUMBER,
       MIN(RD.PART_DESC) AS PART_DESC,
       SUM(RD.SPND * CEH.FACTOR) AS SPND,
       SUM(RD.QTY) AS QTY
 FROM RECEIPTS_DTL RD,
      CURR_EXCH CEH,
 WHERE RD.YR = CEH.YEAR
   AND RD.RLDC_CUR_CODE = CEH.CUR_CODE
 GROUP BY RD.MST_CODE,
       RD.RLDC_PART_NUMBER,
       RD.PLANT_CODE;
TABLE ON TABLE HOLD AS HLD_PSR
END
-*
TABLE FILE HLD_PSR
 PRINT *
END

Hi,
Still I am getting the same error.

Thanks,
Raghu

Check that RECEIPTS_DTL.YR and CURR_EXCH.YEAR have the same format and length, and that RECEIPTS_DTL.CUR_CODE and CURR_EXCH.CUR_CODE do, also. You might also turn on the SQL trace facility to see how the WebFOCUS translator is handling the request. The trace might provide a clue as to where the problem lies. As a last resort, I think you have two options:

1. Hold your temporary file, CURR_EXCH, as a database table (ON TABLE HOLD AS CURR_EXCH FORMAT SQLORA/SQLMSS/SQLODBC/DB2/etc.) so it can be joined directly to table RECEIPTS_DTL as a relational table.
2. Split the data extract from the join - because the join to CURR_EXCH isn't necessary for record selection. Bring back the necessary data from the relational database and hold it. Then JOIN or MATCH the two hold files to produce your final output.