Netezza Error – Record Size Exceeds Internal Limit of 65535 Bytes

I have a column PARAM that has JSON data that looks like this:

JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information","SessionId":"ab04f2c4-ddge-4555-c792-01d9518e7148"}

I would like to have multiple columns (Name, StepName, StepData, SessionID) with their data in them.

SELECT substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21) as Name, substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) as StepName FROM Table

That query won't work exactly right since I have changed the names but I think you get the picture. When I run the query for either of these columns I get the data I am looking for. But when they are run together as I have above, I get the error:

ERROR [HY000] ERROR: Record size 120000 exceeds internal limit of 65535 bytes

It feels like this means that I shouldn't assign the text of PARAM as the column name but that isn't what I am trying to do. Any suggestions or clarifications needed?

Best Answer

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT 
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name, 
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) 
  as varchar (100)) as StepName 
FROM Table