Specify datablock size, Percent freespace

Database
Enthusiast

Specify datablock size, Percent freespace

In teradata,CREATE TABLE can specify datablock size, percent freespace.

Can anyone site an example ?(i mean a ddl statement for a table with above attributes)
5 REPLIES
Senior Apprentice

Re: Specify datablock size, Percent freespace

Here it is, just copied straight from the manuals :-)

CREATE TABLE Employee,
DATABLOCKSIZE = 16384 BYTES,
CHECKSUM = LOW,
FREESPACE = 10 PERCENT,
(EmpNo SMALLINT FORMAT ’9(5)’
CHECK (EmpNo >= 10001 AND EmpNo <= 32001) NOT NULL,
Name VARCHAR(12) NOT NULL,
DeptNo SMALLINT FORMAT ‘999’
CHECK (DeptNo >= 100 AND DeptNo <= 900),
JobTitle VARCHAR(12),
Salary DECIMAL(8,2) FORMAT ’ZZZ,ZZ9.99’
CHECK (Salary >= 1.00 AND Salary <= 999000.00),
YrsExp BYTEINT FORMAT ’Z9’
CHECK (YrsExp >= -99 AND YrsExp <=99),
dob DATE FORMAT ’MMMbDDbYYYY’ NOT NULL,
Sex CHAR UPPERCASE NOT NULL,
Race CHAR UPPERCASE,
MStat CHAR UPPERCASE,
EdLev BYTEINT FORMAT ’Z9’
CHECK(EdLev >=0 AND EdLev <= 22) NOT NULL,
HCap BYTEINT FORMAT ’Z9’
CHECK (BCap >= -99 AND HCap <= 99)
UNIQUE PRIMARY INDEX (EmpNo),
INDEX (Name);

Dieter
Enthusiast

Re: Specify datablock size, Percent freespace

Hello,

How can I view the system default for datablock size, percent freespace? What does percent freespace signify?
What should it be its values?
Enthusiast

Re: Specify datablock size, Percent freespace

Hello,

I do not have TD Manager to use the ferret utility. Please guide if there is some command through which I come to know of the datablocksize. The default I think is 127 sectors.

Regards,
Ayush Jain
Senior Apprentice

Re: Specify datablock size, Percent freespace

You're right, 127 is the default (might be different on the appliance-style systems).

The only way to find out about the default datablocksize is the dbscontrol utility, but only DBAs have access to it.
If a table has a different blocksize then it's reported in a SHOW TABLE.

Dieter
Enthusiast

Re: Specify datablock size, Percent freespace

Thanks Dieter.
Can you please tell me what's the default for FREESPACE PERCENT? Also, my target table is being only insert loaded from another table(No Multi/Fast Load).Should I define the FREESPACE PERCENT as max(75) for the target table.