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 Managing access with users, groups, and table settings.
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:
- To use a character other than the pound sign (#) to delineate position values, use the SODBC_TOKEN environment variable. See Changing the position delimiter used for arrays (SODBC_TOKEN) for information.
- For arrayed fields (but not groups or struct arrays), you can instruct dbcreate to generate a single overlay column for all elements. See Generating one column for an arrayed field (SODBC_COLLAPSE).
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.
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:
- For an enum field, dbcreate generates an integer system catalog column that provides access only to the value side of the enumeration. For example, if you have an enumeration with three members—“tree” with a value of 1, “shrub” with a value of 2, and “groundcover” with a value of 3—a query that includes this column will return only 1, 2, or 3. (It will not return the member names “tree,” “shrub,” and “groundcover.”) Note that you can use the DECODE scalar function to simulate an enumeration—for example:
DECODE(plant_type, 1, 'tree', 2, 'shrub', 3, 'groundcover')
- Time columns are returned as System.TimeSpan for ADO.NET. See Time columns and ADO.NET.
- Fields with struct data type are treated as groups. See Groups and struct fields.
- Data in user fields can be manipulated by the xfODBC routines for user-defined data types. See Creating Routines for User-Defined Data Types.
For more information on data types in Repository, see Basic field information.
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.
|
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. |
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:
- If the repository specifies a member prefix for the group, the member prefix is used instead of the group name. (Member prefixes do no apply to struct fields.)
- To omit group and struct field names from column names, use the SODBC_NOGROUPNAME environment variable. See Removing group and struct names from column names (SODBC_NOGROUPNAME).
- If the group or struct field is an array, each element is mapped to a separate column and all arrayed fields are included in a read-only overlay column as described in Arrays.
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.
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:
- Field size, type, and precision
- “Excluded by ReportWriter” settings (see Including and omitting fields)
- Alternate name, if SODBC_ODBCNAME is set when the system catalog is generated (see Renaming columns for clarity (SODBC_ODBCNAME))
- Negative-allowed and range validation attributes, which are used to determine if numeric fields are signed or unsigned (see Instructing dbcreate to ignore Repository’s “Negative allowed” field (SODBC_NOUNSIGNED))
