SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-15
Determining the Number of Records per Block
as a department number or zip code in an employee table. This reference is made
through the WHERE clause in a DELETE, SELECT, or UPDATE statement.
A user performing update operations can update or delete rows and can lengthen or
shorten values in varying-length columns. If the logical length of the record varies,
however, the physical space consumed in a relative table is always the same.
Moreover, all blocks allocated for a relative table are always full, even if the table
includes zero-length records.
This example creates a relative table. RECLENGTH is specified as 100 to allow space
for adding columns later.
>> CREATE TABLE \SYS1.$VOL3.CODS.HCODES
+> (CODENUM PIC 9(4) NO DEFAULT NOT NULL,
+> ORGANIZATION PIC X(10) DEFAULT SYSTEM,
+> USAGES_CODE NUMERIC (6) DEFAULT SYSTEM,
+> DESCRIPTION PIC X(45) NO DEFAULT NOT NULL)
+> EXTENT (10,10)
+> BLOCKSIZE 2048
+> RECLENGTH 100
+> AUDIT
+> CATALOG \SYS1.$VOL3.ADMIN
+> ORGANIZATION RELATIVE;
--- SQL operation complete.
Determining the Number of Records per Block
The BLOCKSIZE attribute in the CREATE TABLE statement lets you specify the block
size for SQL tables. The maximum block size of 4096 is recommended. Choice of
block size can affect the performance of your database. For additional performance
information, see Specifying Block Sizes for Files on page 14-24.
Key-Sequenced Tables
The maximum record size for a key-sequenced table is the block size less 32 bytes for
block header information. In addition, each record in a block requires two bytes to store
the record’s offset location from the block header. Thus, for the maximum block size of
4096, the maximum usable record size is 4062 bytes if you store one record per block.
To determine the number of records that can be guaranteed to fit in each block, use
this formula, in which N is the number of records, B is the block size, and R is the
record length:
N = (B - 32) / (R + 2)
Thus, if your record length is 202 bytes and the block size is 4096, you can compute
the number of records per block as follows:
N = (4096 - 32) / (202 + 2) = 19
For a key-sequenced table, the number of bytes allocated for a row equals the number
of bytes in the row when the row is inserted into the file. After a row has been inserted,
its length can be changed by updates that change values of varying-length columns.