Author Topic: Please explain Error Message  (Read 239 times)

DonQuixote

  • Rock Star
  • *****
  • Posts: 187
  • To Dream the Impossible Dream...
Please explain Error Message
« on: June 18, 2018, 04:58:21 pm »
I created a work file.   WRK.port
I read the ORDER.HISTORY.LINE file and add "H*" before the ID and write to this work file.
Many records write to the work file and then it aborts with the following errors.

'new block's offset is over the limit
error in write_record for file 'WRK.227C'
over the limit error in U_add_record for file 'WRK.227'
insertion failed error in add_to_group for file 'WRK.227C',
key 'H*001007G2464!5', number = 790
insertion failed error in U_apprend_strtuple for file 'WRK.227C'
key 'H*001007G2464!5', number = 790
Fatal error: WRITE error

What does it mean and how can I correct it.
I thought it was the size as these work files are created using modulo  101,1
So I forced to to create using modulo 3701,8
No change... same errors.

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 223
  • Tom Pellitieri - Toledo, Ohio
Re: Please explain Error Message
« Reply #1 on: June 19, 2018, 05:49:44 am »
Check the physical size of the file at the O/S level.  Linux/Unix/AIX has a file size limit of around 2Gb.  If the file gets too big, you need to make it dynamic so the data can be split into multiple parts.

Generally, any file over 1.5Gb should be reviewed to purge old records or made dynamic instead.

Given that you are creating an historic copy of ORDER.HISTORY.LINE, I would recommend you use

CREATE.FILE WRK.port 3701,16 DYNAMIC KEYDATA

If no one is accessing the file, you may reformat your existing file using

memresize WRK.port 3701,16 MEMORY 64000 DYNAMIC KEYDATA

The MEMORY keyword allocates 64Mb to use during the resize.  The default is 8Mb, which is extremely slow.  You could easily bump that to 256000 if you don't have others on the system.

There are two options for determining when to split the file.  KEYONLY uses just the size of the record keys, while KEYDATA uses the size of both the keys and data.  Given that you are using ORDER.HISTORY.LINE, I recommend KEYDATA.

Hope this helps.

DonQuixote

  • Rock Star
  • *****
  • Posts: 187
  • To Dream the Impossible Dream...
Re: Please explain Error Message
« Reply #2 on: June 19, 2018, 08:38:04 am »
Thank you. That's exactly the answer. Thank you.

DonQuixote

  • Rock Star
  • *****
  • Posts: 187
  • To Dream the Impossible Dream...
Re: Please explain Error Message
« Reply #3 on: June 20, 2018, 10:29:01 am »
My original problem is to blend the ORDER.LINE and ORDER.HISTORY.LINE files for a report.
The report is done using a basic program; so if the ID is not in one file then I read the other.
Instead of creating a work file as originally planned, I'm now thinking to work around that issue.
I could select the IDs I need from one file and then the other.  I can merge the two list using.
merge.list 1 UNION 2

It worked except for one thing; the sort.
I need to sort the blended lists by the order number and by the line number.
Remember the size of this select list is huge.
Any ideas?

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 223
  • Tom Pellitieri - Toledo, Ohio
Re: Please explain Error Message
« Reply #4 on: June 20, 2018, 01:28:06 pm »
If you're doing this from BASIC, you might only need to select ORDER.HISTORY.  The corresponding Line Keys are in Invoice order in Attribute 200.  Here's the BASIC framework.

Code: [Select]
CMD = "SELECT ORDER.HISTORY WITH ":(criteria):" BY ":(sort options)
EXECUTE CMD
LOOP WHILE READNEXT ID DO
   READ HREC FROM F.ORDER.HISTORY,HID THEN
      IX = DCOUNT(HREC<200>,@VM)
      FOR I = 1 TO IX
         READ LREC FROM F.ORDER.HISTORY.LINE,HREC<200,I> THEN
            (decide if you need the line or not, and print what you need)
         END
      NEXT I
   END
REPEAT

You could use /RW and a work file as well.  Rather than copying ALL of the data to a new file, I suggest that you create a file with just the record keys from both files, with no data.  You can then use dictionary items to pull the data you need from the appropriate file.  Since ORDER.HISTORY.LINE uses a two-part key (ORD.KEY!LINE), you don't have to worry about having the same key in both files. 

Since you might have multiple users running the report at the same time, I would suggest pre-pending @PORT (or PORT in BASIC) to the key when you write it to the file.  Header IDs would be port!orderkey and Line IDs would be port!orderkey!linenumber.

I do this for some tracking I need to do for our Sales reports.  I have to get Freight Charges from ORDER.HISTORY, and other Sales information from ORDER.HISTORY.LINE.  I have my own file with the appropriate keys only, and set up the few derived fields I need (e.g., Fiscal Period from ORDER.HISTORY, Ship Quantity/Ext. Price from ORDER.HISTORY.LINE).

For example, you can set up these derived fields in SB+:

PORT - A3:  <0>"G0!1"
ORD.KEY - A15:  <0>"G1!1"
BASE.LN - N3:  <0>"G2!1"
SORT.LN - N3:  (IF(BASE.LN="",0,BASE.LN))

You can also use SB+ F(file,key) derivations to get whatever fields you need from either file.  For example:

SHP.QTY - N6:  (IF(BASE.LN="",0,F("ORDER.HISTORY.LINE",KEY)<41>)
PERIOD - A5:  (F("ORDER.HISTORY",ORD.KEY)<72>)

Another alternative would be to run the report strictly from ORDER.HISTORY.LINE, and get the information from the header only for the first record of each order.  You would have to track when your order number changes to get the header information out.

Yet another alternative would be to use /RW from ORDER.HISTORY, and use a Process After Read paragraph to read the lines and load appropriate MVs into an @WORK slot.

Needless to say, there are a lot of ways to attack this.  Not knowing exactly what you need from both files makes it hard to give you better advice.