Specifying inline lengths for geospatial columns

Each geometry is stored as a large object (LOB). If the sizes of the geometries that are to be stored in a particular LOB column typically exceed the default inline length for that column, to improve performance, set the inline length for that column to a size that is typical for those geometries. However, because an increased inline length requires additional storage, do not specify an overlarge value in an attempt to cover outliers.

You specify an inline length using the INLINE LENGTH parameter of a CREATE TABLE or ALTER TABLE command. The default inline length for a column of type ST_POINT is 256. For a column of any other type, the default inline length is 4000 bytes. For a table in a 32 KB table space, you can specify an inline length of up to 32592 bytes.

To determine an appropriate value for the inline length, answer the following questions:
1. Is the geometry data of type ST_POINT?
Set the inline length to 256 bytes.
2. Is the data stored in a table with types (or subtypes) of SYSIBM.ST_GEOMETRY?
To help you to determine an appropriate value for the inline length, issue SELECT statements similar to the ones in the following examples. Then, compare the geometry sizes and determine how much of the page space should be dedicated to the inline length.
  • This statement returns the maximum and average sizes of the data stored in column geo of the table polygons1:
    db2 "with t(numpoints, length) as 
(select sysproc.st_numpoints(geo), length(geo) from polygons1 
where geo is not null and sysproc.st_numpoints(geo) > 0 ) 
select max(numpoints), max(length), avg(numpoints), avg(length), median(numpoints), median(length) from t" 
  • This statement returns the inline length that is sufficient for 80% of the data stored in column geo of the table polygons1.
    db2 "with t(numpoints, length) as 
(select sysproc.st_numpoints(geo), length(geo) from polygons1 
where geo is not null and sysproc.st_numpoints(geo) > 0 ) 
select PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY length) from t"
3. Is the data stored in a table with types (or subtypes) of db2gse.st_geometry?
To help you to determine an appropriate value for the inline length, issue SELECT statements similar to the ones in the following examples. Then, compare the size thresholds and determine how much of the page space should be dedicated to the inline length.
  • This statement returns the maximum and average sizes of the data stored in column geo of the table polygons1:
    db2 "with t(numpoints, length) as 
(select sysproc.st_numpoints(geo.points), length(geo.points) from polygons1 
where geo.points is not null and sysproc.st_numpoints(geo.points) > 0 ) 
select max(numpoints), max(length), avg(numpoints), avg(length), median(numpoints), median(length) from t" 
  • This statement returns the inline length that is sufficient for 80% of the data stored in column geo of the table polygons1.
    db2 "with t(numpoints, length) as 
(select sysproc.st_numpoints(geo.points), length(geo.points) from polygons1 
where geo.points is not null and sysproc.st_numpoints(geo.points) > 0 ) 
select PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY length) from t"
4. Is the data stored in a table with types or subtypes of nz-spatial-esri?
To help you to determine an appropriate value for the inline length, issue SELECT statements similar to the ones in the following example. This statement returns the maximum and average sizes of the data stored in column geo of the table polygons1:
db2 "with t(numpoints, length) as 
(select sysproc.st_numpoints(geo), length(geo) from polygons1 
where geo is not null and sysproc.st_numpoints(geo) > 0 ) 
select max(numpoints), max(length), avg(numpoints), avg(length), median(numpoints), median(length) from t" 
If the average length is close to the maximum length, use a value close to the maximum length as inline length. If the average length is much smaller than the maximum length, use a value close to the average length as inline length.
5. Is the data stored in a table with types or subtypes of nz-legacy-spatial?

6. Is the data stored in a shapefile?
Use the st_shape_info procedure or the db2se shape_info command to list geometry type and precision data.

If the geometry data is of type ST_POINT, set the inline length to 256 bytes. Otherwise, set the inline length to 4000 bytes.

7. Do none of the aforementioned conditions apply?
Use the following formula:
(points * 16) + 150
where points represents the expected maximum number of points for typical (that is, non-outlier) geometries.