System catalog generation issues

Keep the following in mind as you prepare to create a system catalog from your repository.

Access levels

When generated, all tables have an initial read‑only access level set at 100. For information on access levels, see Setting security levels.

Arrays

Because the current SQL API does not support arrays, each element in an arrayed field is mapped to a separate column and given a name that consists of the array name, the element’s position in the array, and pound signs (#) to delineate position values. For example, a [2,2] array with the name myarray will be mapped as the following columns: myarray#1#1, myarray#1#2, myarray#2#1, and myarray#2#2. These are the names you use to access data in myarray—for example:

SELECT myarray#2#2 FROM mytable WHERE myarray#1#1 = 100

This is also true of groups and struct fields that are arrays, except that for these, dbcreate also generates a read‑only overlay field that includes all the fields in the array. For example, if a repository has a group or struct field named myarray that’s a [2,2] array with a single field, myfield, the group or struct field will be mapped to the following columns: myarray (then overlay field), myarray#1#1myfield, myarray#1#2myfield, myarray#2#1myfield, and myarray#2#2myfield.

Note the following:

AutoSeq, AutoTime, and CTIMESTAMP fields

AutoSeq, AutoTime, and CTIMESTAMP fields are 8‑byte read‑only ISAM key fields that are automatically populated with values by Synergy DBMS. AutoSeq is a generated number that is guaranteed to be unique within an ISAM file. AutoTime is a timestamp that records the last date and time that a record was modified. And CTIMESTAMP is a timestamp that records the date and time that a record was created. See Keys in ISAM files for more information.

Data types

The dbcreate utility generates system catalog columns with the following SQL types:

Repository data type

SQL type

Alpha

SQL_VARCHAR

AutoSeq

SQL_BIGINT

AutoTime

SQL_TIMESTAMP

Binary

SQL_BINARY

Boolean

SQL_BIT

CTIMESTAMP

SQL_TIMESTAMP

Date

SQL_TYPE_DATE

Decimal

d1 and d2

SQL_TINYINT

d3 and d4

SQL_SMALLINT

d5 through d9

SQL_INTEGER

d10 and higher (except d16.6)

SQL_DECIMAL

d16.6

SQL_FLOAT

Enum

SQL_INTEGER

Integer

i1

SQL_TINYINT

i2

SQL_SMALLINT

i4

SQL_INTEGER

i8

SQL_BIGINT

Time (HHMM or HHMMSS)

SQL_TYPE_TIME

User

Alpha

SQL_VARCHAR

Binary

SQL_BINARY

Date with HHMM or HHMMSS in User data field

SQL_TYPE_TIME

Date with YYYYMMDDHHMISS or YYYYMMDDHHMISSUUUUUU

SQL_TIMESTAMP

Date with any other value in the User data field

SQL_TYPE_DATE

Numeric

SQL_DECIMAL

Note the following:

DECODE(plant_type, 1, 'tree', 2, 'shrub', 3, 'groundcover')

For more information on data types in Repository, see Basic field information.

Date and time fields

When a system catalog is generated, date and time columns are generated as described below. For information on how date and time data is returned from a database and how dates and times must be specified in SQL statements, see Setting runtime data access options.

SQL time columns are generated from repository fields with the Time type or with one of the following formats specified in the Repository “User data” field (e.g., ^CLASS^=HHMM), where HH is the hour (in 24‑hour format), MM is the minutes, and SS is the seconds:

HHMM

HHMMSS

SQL date columns are generated from AutoTime and CTIMESTAMP fields and repository fields with one of the following formats specified in the Repository Class field or User data field:

1. YYMMDD

9. MMDDYYYY

17. JJJYY\

2. YYYYMMDD

10. MMDDYY

18. JJJYYYY

3. YYJJJ

11. DDMonYY

19. JJJJJJ

4. YYPP

12. DDMonYYYY

20. PPYY

5. YYYYPP

13. MonDDYY

21. PPYYYY

6. YYYYJJJ

14. MonDDYYYY

22. YYYYMMDDHHMISS

7. DDMMYY

15. YYMonDD

23. YYYYMMDDHHMISSUUUUUU

8. DDMMYYYY

16. YYYYMonDD

 

Where

YY

is the last two digits of the year.

YYYY

is the year, including the century.

MM

is the one‑ or two‑digit month.

Mon

is the three‑letter abbreviation for the month (e.g., Jan, Feb, Mar).

DD

is the one‑ or two‑digit day of the month.

HH

is the hour.

MI

is the minute

SS

is the second

UUUUUU

is the microsecond

PP

is the period.

JJJ

is the Julian day count from the first of the year.

JJJJJJ

is the Julian day count from SYNBASEDATE or the default base date, which is 1752‑09‑14 (i.e., 14 September 1752). (See Setting the base date for Julian day conversions.)

Date formats 1 through 6 can be specified in Repository by selecting the corresponding date format in the Class field. Date formats 7 through 23 can be selected by defining the field as “User” in the Type field in Repository and by including the following in the “User data” field (where date_format is one of the above formats):

^CLASS^=date_format

See Basic field information for more information.

Note

When generating a system catalog, a date field that doesn’t include a century (a YY date) is formatted as a date with a rolling century (an RR date). This enables the xfODBC driver to display the date correctly. See Converting dates returned without centuries for information on how xfODBC converts RR dates as it accesses a database.

Groups and struct fields

By default, if a field is part of a group or struct field in the repository, the group name or the struct name is added to the beginning of the field name to create the name for the column in the system catalog. For example, the field myfield in the group mygroup becomes mygroupmyfield in the system catalog; if myfield is part of a struct field named mystruct, it becomes mystructmyfield in the system catalog. You can use these names to access data in the group or struct field. For example:

SELECT mystructmyfield FROM mytable

Note the following:

Open filename field (S/DE Repository)

For greater flexibility, use environment variables in the Open filename field of Repository file definitions to specify the location of your data files. These environment variables are stored in the system catalog and must also be set in the system in which the database is installed, generally in the environment setup file or the connect file. For information, see Using an environment variable in the Open filename field.

You can also use the USR_DD_FILNAM routine in the xfODBC Database Administrator (DBA) program to customize the Open filename field when you generate a system catalog. For more information, see Using USR_DD_FILNAM to change replaceable characters.

Overlay fields

xfODBC supports overlay fields.

Relations

Relations established between tables, as defined in Repository, are supported in xfODBC. Use of relations is application dependent.

Spaces, zeros, and null values

For information on how xfODBC interprets spaced, zeros, and null values, and for information on how to prevent fields from being updated with null values (and other values that xfODBC considers null), see Preventing null updates and interpreting spaces, zeros, and null values.

Structures and table names

The dbcreate utility generates a caution if you attempt to generate a system catalog from a structure assigned to more than one file definition unless you use the ODBC table name option in Repository. If you use this option to assign an ODBC table name to file/structure combinations, dbcreate will use the ODBC table names, rather than the structure names, in the generated system catalog. See Assigning structures to filess for information. (Note that this is not related to the SODBC_ODBCNAME environment variable, which enables you to use the field name specified in the Repository Alternate name field attribute.)

Temporary files

By default, when dbcreate generates a system catalog, it includes tables that describe temporary files (files for which the Repository Temporary flag is set). To omit tables that describe temporary files, set the SODBC_TMPOPT environment variable as described in Excluding tables attached to temporary files (SODBC_TMPOPT).

User‑defined data types

For information on creating routines that manipulate data in user fields, see Creating Routines for User-Defined Data Types.

Other field attributes

The dbcreate utility and DBA also use the following repository settings as column attributes in the system catalog: