ERROR: Expecting page, got page -1 instead

[MariaD]

Hi folks, 

We have a new SAS server on Linux running SAS9.4M7. We're on testing phase and we facing some issue when we process big tables (more than 20MM records and 100 columns). 

When running PROC SORT or JOIN using this table, we have the following erro:

ERROR: Expecting page 91595, got page-1 instead.

ERROR: Page validation error while reading WORK.TABLE

ERROR: File WORK.TABLE.DATA is damaged. I/O processing did not complete.

We have almost 11TB of free disk space on WORK. We ran the IOTest and the results it's ok, it was validated for SAS Technical consultant. 

If, for example, we used a KEEP reading less columns (44 was the limited), the process runs ok. The weird situation it's we have other environment, Linux too but with SAS 9.4M5 and the process execute fine on that environment. We verified the ulimit values, and are the same between the environments. The MEMSIZE and SORTSIZE options are bigger in the new environment. 

Any idea?

Regards,

[SASKiwi]

Looks like you are running out of SAS user work space. Check user space quotas. Rerun the job and check space usage while it is running.

[MariaD]

Thanks @SASKiwi . How could I check the user space quotas? We didn't define any specific user quotas. In fact the process stop when rich almost 600GB of WORK space used.

[SASKiwi]

@MariaD - Your evidence suggests that there is some user-related storage limit rule is being applied. Check these anyway. I don't know what flavour of linux you are using but here is a link to the RHEL 7 doc: https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/storage_administration...

[MariaD]

Thanks @SASKiwi . I'll share the notes with server admin, tomorrow morning, and let you know. As far as I know, there is no limit (quotas) per user, but we'll check it again. 

Regards, 

[Kurt_Bremser]

I would look into any kind of workload management / quota system which may limit storage use by the user running the code.

[ballardw]

Proc Sort takes up about 3 times the size of the data set to sort as there is the original data, a temp data set that is cleaned up when the sort finishes and the final sorted set.

If the issue is ONLY with Proc Sort sometimes the TAGSORT option can reduce the amount of temporary space used. Your trial with fewer variables does indicate a likely space limitation as part of the problem.

Depending on the type of JOIN you may want to provide specific code examples. Filtering data early in a query instead of after the join generally reduces the space used (Cartesian joins such as " From a, b" may be right out with sets this big)

[MariaD]

Thanks @ballardw . Yes, we know PROC SORT or SQL JOIN without index use a lot of disk space. But the weird situation is these particularly process, created by an enduser, runs ok on our environment with 9.4M5. It's a simple LEFT JOIN between 2 SAS tables. The first one is big, not the second one. The join is for 2 columns. 

[Kurt_Bremser]

If the dataset contains lots of wide character variables and is stored with the COMPRESS option, the utility files (which are uncompressed) can easily blow up your allotted disk storage.

[MariaD]

Thanks @Kurt_Bremser . In fact, we made test with and without compress options. Because we are in testing phase, we have a lot of WORK area available, almost 11TB free. We monitored the free WORK space during executing and it never pass over 11% of usage. 

We already have a ticket opened with SAS Support but, after more than 1 week we don't have any update on it. 

[gwootton]

Could your large table(s) be corrupted? Are your old and new environments both using the large table from the same source / shared path? If the large table is a SAS dataset you could try a PROC DATASETS repair on it.

[MariaD]

Thanks @gwootton. Both tables are SAS and it created in previous steps. As test, we created 2 new big tables (totally random) and the same erro appears. 

We also tested create a big file, directly on the filesystem (without SAS) to see if any erro appears after reach some size, but the file was created without any erro. 

We also reviewed the block size for both environment and it has the same definition. 

[gwootton]

I suspect the table is not being created successfully in the prior step, and the JOIN or SORT is finding this corruption when it parses the entire data set. I might check your system log for file system errors during the creation phase. If the path where you are creating this large table is not local to the SAS session but a SAN or network share, the settings for that file system may need to be adjusted. I wonder if it's not done writing to the file system when we try to read it again with the JOIN/SORT.

[MariaD]

Hi @gwootton ,

I'm creating the test tables on WORK area. In one of my test, I created the tables first. Then opens it and navigate on each of them and everything were fine. After that test, closes the tables and I ran the JOIN process and the erro appears. 

Follows the program used for the test (it's very simple). If I run the program with 5 character columns and 130 numeric columns, it executes fine. But if I increase to 140 numeric columns the error appears. 

%LET COL=5 ; 
%LET COL_N = 135;

DATA VALIDA;
  ARRAY VAR_(&COL_N);
  ARRAY STR_(&COL) $50.;
  DO j=1 TO 30000000;
     DO I=1 TO &COL_N;
       VAR_(i) = RANUNI(0);
     END;
     DO I=1 TO &COL;
         DO n=1 TO 40;
              SUBSTR(STR_(i), n) = BYTE(INT(65 + 26 * RANUNI(0))) ;
         END;
     END;
    OUTPUT;
  END;
RUN;

DATA VALIDA2;
  ARRAY VARI_(&COL_N);
  ARRAY STRI_(&COL) $50.;
  DO j=1 TO 30000000;
     DO I=1 TO &COL_N;
       VARI_(i) = RANUNI(0);
     END;
     DO I=1 TO &COL;
         DO n=1 TO 40;
              SUBSTR(STRI_(i), n) = BYTE(INT(65 + 26 * RANUNI(0))) ;
         END;
     END;
    OUTPUT;
  END;
RUN;

PROC SQL;
  CREATE TABLE VALIDA3 AS
  SELECT a.*,
                b.VARI_1,
                b.VARI_5
   FROM VALIDA a
   LEFT JOIN VALIDA2 B ON a.VAR_1 = b.VARI_2;
QUIT;

Regards. 

[Tom]

So you do an SQL JOIN on two datasets with 30 million observations each?  

With 140 numeric variables plus another 150 bytes in character variables a 30 million observation dataset is 41 Gigabytes.

Multiple that by 30million observations from the second table and that is a million Terabytes.

Do you have unique keys to the data or not?

Much better to just use a MERGE to lookup values from a second dataset.

data want;
   merge A (in=inA) B(keep=id extra1 extra2);
   by id;
   if inA;
run;