Question
Rui Figueiredo · Nov 25, 2018

ERROR #5002: Cache error: <STORE>

Hi,

I'm having the following error when returning a large dataset from a REST API.

"errors":[ {
            "code":5002,
            "domain":"%ObjectErrors",
            "error":"ERROR #5002: Cache error: <STORE>%0NmGk1+5^xxxxxx",
            "id":"CacheError",
            "params":["<STORE>%0NmGk1+5^xxxxxxxxxxxxxxxxxxxx"
            ]
        }
    ],
    "summary":"ERROR #5002: Cache error: <STORE>%0NmGk1+5^xxxxxxxxxxx"

My understanding is that this error is related to the amount of memory available. 

Please see my method implementation below. The SQL query has about 50 columns and can return more than 30 000 rows.

Can someone advice me on the best implementation approach for this scenario?

I'm using Cache 2015.2.

Thank you

ClassMethod GetXXXXX(DateFrom As %Library.Date, DateTo As %Library.Date) As %Status
{    
    SET %response.ContentType="application/json"

    s DateFrom=$ZDH(DateFrom,15)
    s DateTo=$ZDH(DateTo,15)
    
    SET params = ##class(%ZEN.proxyObject).%New()
    SET params.DateFrom = $ZD(DateFrom,4)
    SET params.DateTo = $ZD(DateTo,4)
          
    &SQL(DECLARE resultCursor CURSOR FOR 
    
    SELECT Field1, Field2, ..., ..., .. 
    INTO :F1, :F2, ..., ..., ...
    FROM SQLUSER.TABLE
    WHERE ate between :DateFrom and :DateTo

    )
    
    &SQL(OPEN resultCursor)
    
    SET i=0
    SET results = ##class(%ListOfDataTypes).%New()  
    SET tmpArray = ##class(%Stream.GlobalCharacter).%New()  
    
    &sql(fetch resultCursor)
     FOR { 
        &SQL(FETCH resultCursor) 
        Quit:SQLCODE            
  
        SET obj = ##class(%ZEN.proxyObject).%New()        
        SET obj.Field1 = F1        
        SET obj.Field2 = F2        
        ...
        ...
      
          do results.Insert(obj)  
          
          SET i = i+1      
    }
   
    &SQL(CLOSE resultCursor)
    
    SET response = ##class(%ZEN.proxyObject).%New()
    SET response.Params = params
    SET response.ResultCount = i
    SET response.Results = results

    do ##class(Ens.Util.JSON).ObjectToJSONStream(response,.tmpArray)    
    do tmpArray.OutputToDevice()   
     
    Quit $$$OK
}
00
1 0 1 3,071
Log in or sign up to continue

STORE error means, your reached limit of memory per process. Since 2012.2 we have 256Mb per process by default and you can increase it up to 2Tb.  You can increase it, but I would recommend playing with different ways how to collect SQL Data, or on optimization in this SQL query. Or you can split such many rows by portions with less number of rows.