CODEWITHSUNDEEP
netezza
Kapish Kumar
Kapish Kumar

Reputation: 143

Netezza: ERROR: 65536 : Record size limit exceeded

Can someone please explain below behavior 

KAP.ADMIN(ADMIN)=> create table char1 ( a char(64000),b char(1516));

CREATE TABLE

KAP.ADMIN(ADMIN)=> create table char2 ( a char(64000),b char(1517));

ERROR: 65536 : Record size limit exceeded

KAP.ADMIN(ADMIN)=> insert into char1 select * from char1;

ERROR: 65540 : Record size limit exceeded => why this error during insert if create table does not throw any error for same table as shown above.

KAP.ADMIN(ADMIN)=> \d char1
                      Table "CHAR1"

Attribute |       Type       | Modifier | Default Value

-----------+------------------+----------+---------------

A         | CHARACTER(64000) |          |

B         | CHARACTER(1516)  |          |

Distributed on hash: "A"

./nz_ddl_table KAP char1

Creating table:  "CHAR1"

CREATE TABLE  CHAR1
(
     A        character(64000),
     B        character(1516)
)
DISTRIBUTE ON (A)
;

/*
       Number of columns  2

    (Variable) Data Size  4 - 65520
            Row Overhead  28
  ======================  =============

  Total Row Size (bytes)  32 - 65548

*/

I would like to know the calculation of row size in above case. I checked the netezza db user guide, but not able to understand its calculation in above example.

Upvotes: 0

Views: 9614

Answers (2)

skoolbus
skoolbus

Reputation: 21

I think this link does a good job of explaining the over head of Netezza / PDA Datatypes: 

For every row of every table, there is a 24-byte fixed overhead of the rowid, createxid, and deletexid. If you have any nullable columns, a null vector is required and it is N/8 bytes where N is the number of columns in the record. 
The system rounds up the size of 
this header to a multiple of 4 bytes.
In addition, the system adds a record header of 4 bytes if any of the following is true:

Column of type VARCHAR
Column of type CHAR where the length is greater than 16 (stored internally as VARCHAR)
Column of type NCHAR
Column of type NVARCHAR

Using UTF-8 encoding, each Unicode code point can require 1 - 4 bytes of storage. A 10-character string requires 10 bytes of storage if it is ASCII and up to 20 bytes if it is Latin, or as many as 40 bytes if it is Kanji.

The only time a record does not contain a header is if all the columns are defined as NOT NULL, there are no character data types larger than 16 bytes, and no variable character data types.

https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_data_types_calculate_row_size.html

Upvotes: 2

mehtat_90
mehtat_90

Reputation: 628

First create a temp table based on one row of data.

create temp  table tmptable as
select *
from Table
limit 1

Then check the used bytes of the temp table. That should be the size per row.

select used_bytes
from _v_sys_object_storage_size a inner join
_v_table b
on a.tblid = b.objid
and b.tablename = 'tmptable'

Netezza has some Limitations: 1)Maximum number of characters in a char/varchar field: 64,000 2)Maximum row size: 65,535 bytes

Beyond 65 k bytes is impossible for a record length in NZ. Though NZ box offers huge space, it would be really good idea to move with accurate space forecasting rather radomly spacing. Now in your requirement does all the attributes would mandatorily require a char(64000) or can be compacted with real-time data analysis. If further compacting can be done, then revisit on the attribute length . Also during such requirements, never go with insert into char1 select * ....... statements because this will allow system to choose preferred datatypes and that will be of higher sizing ends which might not be necessary.

Upvotes: 1

Related Questions