Sybase Data Packing

The table describes the mapping of Uniface packing codes to Sybase storage formats.

Mappings Between Uniface Packing Codes and Sybase Storage Formats

Uniface

Sybase

A1-A32

identity

B1-B4

bit

C1-Cn 1

 cahr

C*

varchar

D, D1-D11

datetime

E, E1-E7

datetime

E9

smalldatetime

F

float

F4

real

F8

float

I1

tinyint

I2

smallint

I3-I4

int

I8

float or bigint 2

M1-M4

money

M6

smallmoney

N1-N32

numeric or decimal

(N)C1-32

numeric or decimal

O1-O32

numeric or decimal

P1-P16

numeric or decimal

Q1-Q16

numeric or decimal

R1-Rn 1

binary

R*

varbinary

SC1-SC*

text

SR1-SR*

image

SU1-SU*

text

SW1-SW*

unitext

T, T 1- T3

datetime

U1-U255

char

VC1-VCn 3, VC*

varchar

VR1-VRn 1, VR*

varbinary

VU1-VUn 1,VU*

varchar

VW1-VWn

univarchar

VW*

varbinary

W1-VWn

unichar

W*

varbinary

  1. n must not exceed the row limit. To set the row limit, use the row limit connector option.
  2. Bigint is used only if the mapping connector option is set to 1
  3. Ensure that the database page size is large enough to support the maximum field length used. This can be 8000 characters.

Data Packing with disable numerics

When the disable numerics connector option is enabled, the numerics and identity storage formats are not supported. The table describes how Uniface packing codes are mapped when numerics are disabled:

Uniface Packing Code to Sybase Storage Formats (disable numerics)
Uniface

Sybase with disable numerics
A1-A32 Not supported
N1-N2 TINYINT
N3-N4 SMALLINT
N5-N9 INT
N10-N32 FLOAT
P1 TINYINT
P2 SMALLINT
P3-P5 INT
P6-P8 FLOAT
Q1 TINYINT
Q2 SMALLINT
Q3-Q5 INT
Q6-Q8 FLOAT

All other Uniface packing codes are unchanged when numerics are disabled.

Overflow Tables

The following Uniface packing codes require an overflow table when a large amount of data is inserted into the field:

  • C*
  • VC*
  • U*
  • VU*
  • R*
  • VR*

Entities with these types of fields, where * is the literal asterisk, cannot be created ‘on the fly’ and they require referential integrity controls.

Sybase Storage Formats

Explanation of Sybase storage formats
Storage formats Description
numeric The Numeric data type is available for exact numeric values. Numeric fields can have a precision of 1-38 digits, and a scale of 0-38 digits. Currently, support is provided for Numerics up to a precision of 32 digits.
decimal Decimal is another name for the Numeric data type.
identity Identity fields provide unique sequential number generation. Each table can have at most one Identity field. Identity fields use the Numeric storage format with the limitation that Identity fields must have a scale of zero, and must be mandatory (non-null). These fields must have values which are greater than or equal to zero.

If the identity insert connector option is not set, the Sybase DBMS controls the value of the Identity field. The connector does not update this field, it only inserts a value into the Identity field if the identity insert connector option is set.
bit Use the Bit storage format for fields which store true or false values. Bit fields are equivalent to the Uniface Boolean data type. They are called ‘bit’ fields because their storage size is one bit containing the values 0 or 1. It is impossible to index a bit field, so do not use these fields as key fields. Bit fields cannot be null.
char Char fields are ordinary character fields for textual information, being up to 255 characters in length.

Accessing Char fields in Sybase is usually faster than accessing Varchar fields, although more storage space is used if a field is less than the specified length. Be warned, however, that Char fields which allow null are treated like Varchars if they are null.

Arithmetic with Char fields is impossible without first converting the data into a numerical format, even if the field contains only numeric data.

tinyint Tinyint is a numerical storage format for integers between zero and 255. Negative numbers, fractions and decimals are not allowed. All arithmetic functions are possible with Tinyint data. Storage size of Tinyint fields is one byte. Uniface uses this storage format slightly differently to Sybase, storing signed values from -128 through 127. This distinction is only important if you access your data with a tool other than Uniface.
smallint Smallint is an integer field for whole numbers from -32,768 through 32,767. Negative numbers are therefore allowed. Fractions and decimal places are not allowed. All arithmetic functions are possible. Storage size is two bytes.
int Int is a storage format for integers from - 2,147,483,647 through 2,147,483,647 inclusive. Storage size is four bytes. No decimal places or fractions are allowed, but negative numbers are permitted and all arithmetic functions are possible.
bigint Bigint a fixed-width, 8-byte data type for signed integers between -263 and +(263 - 1). It can hold whole numbers between -9223372036854775808 and 9223372036854775807 (signed), or from 0 to 18446744073709551615 (unsigned).
float Float is a storage format for floating point numbers. Positive and negative numbers are allowed, as are numbers with exponential components (ideal therefore for scientific notation). Storage size is eight bytes.
real This is a four-byte version of the Float storage format.
datetime Datetime fields are eight-byte Date and Time fields, comprising a four-byte time integer and a four-byte date integer. The date integer contains the number of days before or after 01-JAN-1900. The time integer contains the time of day expressed in three-hundredths of a second. Linear date and time values are all mapped to Datetime fields.

With Sybase, Uniface will always store any date or any time in a Datetime format. If no time is available with a date record, 00:00:00 will be added by default. If time is recorded without date information, Uniface automatically adds the date that Sybase understands as the base date (that is, 01-JAN-1900). Arithmetic functions with Datetime are possible.

Beware of handling Uniface time fields (data type T) with other tools (for example, ISQL), because Uniface maps T fields to Datetime in Sybase and adds the base date of 01-JAN-1900. Other tools probably do not do this (for example, ISQL does not), and this can cause time values to change inexplicably and make them unintelligible to Uniface.

Uniface handles time to a precision of 1/100 second, whereas Sybase handles time to a precision of 1/300 second. You will experience problems with time values if Datetime fields are filled by non-Uniface tools to a greater precision than Uniface does and you want to retrieve on those fields with an ‘exact’ match. For this reason, you should not define any primary key, foreign key or index fields as Datetime.

smalldatetime Smalldatetime fields have less precision than Datetime fields. Storage size is four bytes: one small integer for the number of days after January 1, 1900, and one small integer for the number of minutes since midnight. Dates range from January 1, 1900 through June 6, 2079, with accuracy to the minute.
money The Money storage format should be specified for any decimal currency values that you need to enter, unless you prefer to use Smallmoney. All arithmetic operations are possible except modulo. Entries should not use any commas or currency notation.
smallmoney Smallmoney fields can contain values from -214,748.3648 through 214,748.3647 (with an accuracy of one ten-thousandth of a unit). All arithmetic operations are possible except modulo.
binary Binary storage format fields should be used for fixed-length binary data, up to a maximum of 255 positions (approximately 2000 bits).

Accessing Binary fields in Sybase is usually faster than accessing Varbinary fields, although more storage space is used if a field is less than the specified length. Be warned, however, that Binary fields which allow null are treated like Varbinaries if they are null.

varchar Varchar should be used when a variable-length field is required, up to a maximum length of 8000 positions. Varchar fields, like Char fields, can be used for any combination of characters, numbers and symbols.

Choose this storage format when the expected length of your data can vary considerably: in a name field for example, when the same field will be used to hold the values JJB Inc. and International Sub-Oceanic Refuse Engineering Corporation. Storage size depends on the contents: if the field is empty, no space is used.

varbinary Varbinary fields are for variable-length binary data. As with Binary fields, this storage format is ideal for the storage of programming data, or pictures. The difference lies in the expected length of your data. Specify the Varbinary storage format if it is likely that various lengths of data storage will be needed in the same field. Storage size of Varbinary fields is equal to the contents: if the field is empty, no space is used. Sybase truncates binary data stored as Varbinary, trailing zeros are removed.
text Text fields have the same characteristics as Char fields, but can be of unlimited length (up to the limits imposed upon you by the system). The Uniface limit is 64 kilobytes.

Caution: Storing as little as one character in this way causes Sybase to make a 2-kilobytes storage area. Null Text does not require a 2-kilobytes storage area.

image Image fields have the same characteristics as Varbinary fields, but can be of unlimited length (up to the limits imposed upon you by the system).

It is not possible to specify Image fields in retrieve profiles, or to search for specific data within the overall contents of the field..

nchar Nchar is similar to Char except that Nchar is designed for national character support. In particular, Nchar data types are designed for multi-byte character support. Nchar can be used interchangeably with Char.

Nchar is supported in the Load Database Definitions utility, but the SYB connector cannot create fields of this type.

nvarchar Nvarchar is similar to Varchar except that Nvarchar is designed for national character support. In particular, Nvarchar data types are designed for multi-byte character support. Nvarchar can be used interchangeably with Varchar.

Nvarchar is supported in the Load Database Definitions utility, but the SYB connector cannot create fields of this type.

unichar Unichar is similar to Char but stores data in UTF-16 Unicode.
univarchar Unichar is similar to Varchar but stores data in UTF-16 Unicode.
unibinary Unitext is similar to Binary but stores data in UTF-16 Unicode.
unitext Unitext is similar to Text but stores data in UTF-16 Unifacode.

Related Topics