Setting catalog generation options
This topic includes the following sections:
- Including and omitting fields
- Omitting keys
- Instructing dbcreate to ignore Repository’s “Negative allowed” field (SODBC_NOUNSIGNED)
- Renaming columns for clarity (SODBC_ODBCNAME)
- Generating one column for an arrayed field (SODBC_COLLAPSE)
- Changing the position delimiter used for arrays (SODBC_TOKEN)
- Removing group and struct names from column names (SODBC_NOGROUPNAME)
- Excluding tables attached to temporary files (SODBC_TMPOPT)
- Specifying a conversion setup file (SODBC_CNVFIL)
- Using decimal information in the repository format string (SODBC_USEFORMAT)
- Preventing null updates and interpreting spaces, zeros, and null values
In addition to the options you set at the command line for dbcreate or in the Generate System Catalog window of DBA, you can specify some system catalog generation options by setting environment variables and S/DE Repository options. You are not required to set these, but if you use any of the environment variables documented here, you must set them in the environment. We recommend creating a batch file, shell script, or DCL command file, setting the environment variables in this file, and then running the file before using dbcreate or the DBA program.
For information on system catalog generation settings for dbcreate or DBA, see dbcreate utility and Using DBA to generate a system catalog with level-based users. See Setting runtime data access options for information on options that affect the way xfODBC behaves as it accesses data, including more settings that determine how the xfODBC driver interprets data.
In the following sections, discussions of dbcreate apply to the use of dbcreate from the command line and the use of dbcreate that happens behind the scenes when you use the DBA program to generate a system catalog.
Including and omitting fields
If the S/DE Repository option “Excluded by ReportWriter” is checked for a field, dbcreate will not include the field in the system catalog, so the field will not be available to ODBC-enabled applications. To include the field in the system catalog, clear this option in Repository. See Defining a new field for more information.
If you want all fields to be included in the system catalog, regardless of their “Excluded by ReportWriter” settings, set the SODBC_CNVOPT environment variable to 1 in the environment. For client/server configurations, set it where you run dbcreate.
If a field is used as a structure tag or key segment, or if it overlays a field used as a key segment, the field is automatically included in the system catalog regardless of the ReportWriter exclusion flag or the SODBC_CNVOPT setting. |
Omitting keys
By default all keys defined in the repository are used to define indexes in the system catalog. To omit a key from the system catalog, set the S/DE Repository option “Excluded by ODBC” for the key definition. (See Defining keys for more information.) Note that this option does not affect the inclusion of fields in the system catalog (even fields specified in the key definition), just the key. Every field that is a key or key segment is included in the system catalog. See Tags and optimization for recommendations that utilize this option.
Instructing dbcreate to ignore Repository’s “Negative allowed” field (SODBC_NOUNSIGNED)
The dbcreate utility and the xfODBC driver distinguish between signed and unsigned numeric fields. When dbcreate generates a system catalog, it checks the “Negative allowed” repository setting to determine if the resulting column will be signed or unsigned:
- If “Negative allowed” is No, the resulting column will be unsigned.
- If “Negative allowed” is Only, OrZero, or Yes, the resulting column will be signed unless a range that includes only positive values is assigned to the field, in which case the column will be unsigned.
Prior to Connectivity Series version 8.3, dbcreate ignored the “Negative allowed” setting and set all fields to signed unless they had validation ranges that were limited to positive values (in which case the resulting columns were unsigned). To revert to this behavior, set the SODBC_NOUNSIGNED environment variable in the environment to any value before you generate the system catalog. For client/server configurations, set it where you run dbcreate.
Renaming columns for clarity (SODBC_ODBCNAME)
Repository field names that are short and cryptic may not make good column names. As an alternative, you can use Alternate name field values (specified in the repository) as column names by setting the SODBC_ODBCNAME environment variable. When SODBC_ODBCNAME is set, xfODBC uses a field’s Alternate name value if it’s set; otherwise it uses the field’s name.
To use the values in the Repository Alternate name field as column names, set SODBC_ODBCNAME to 1 in the environment. For client/server configurations, set it where you run dbcreate.
Note that this is not related to the Repository ODBC table name option, which enables you to assign ODBC table names to file/structure combinations.
Generating one column for an arrayed field (SODBC_COLLAPSE)
By default, each element in an arrayed field is mapped to a separate column in the system catalog (see Arrays). You can, however, use the SODBC_COLLAPSE environment variable to instruct dbcreate to map all elements of an arrayed field to a single system catalog column if the number of elements in the array is greater than or equal to the limit you specify—that is, the number you set SODBC_COLLAPSE to. You should use SODBC_COLLAPSE if a system catalog table will have more than 254 columns because some ODBC-enabled applications do not permit tables with more than 254 columns. (Note that SODBC_COLLAPSE does not affect group arrays, which cannot be collapsed.)
For example, if you set SODBC_COLLAPSE to 10 and your repository has a structure with three arrayed fields—one with 6 elements, one with 8 elements, and one with 10 elements—the corresponding table in the system catalog will have 15 columns: six for the first array, eight for the second array, and one for the third array (because it reached the limit set by SODBC_COLLAPSE).
Set SODBC_COLLAPSE in the environment; for client/server configurations, set it in the environment where you run dbcreate.
Changing the position delimiter used for arrays (SODBC_TOKEN)
When you generate a system catalog for a repository that has an arrayed field, each element in the array is mapped as a separate system catalog column with a name that consists of the array name, the element’s position in the array, and pound signs (#) to delineate position values. (See Arrays) For example, a [2,2] arrayed field named myarray will be mapped to the following: myarray#1#1, myarray#1#2, myarray#2#1, myarray#2#2.
You can change the character used to delineate position values by setting SODBC_TOKEN to the character you want to use. Be sure to set it to a valid SQL identifier value for your ODBC applications. Set SODBC_TOKEN in the environment; for client/server configurations, set it where you run dbcreate. For example, if you set SODBC_TOKEN=_, the myarray field described above would result in the following system catalog columns: myarray_1_1, myarray_1_2, myarray_2_1, myarray_2_2.
Removing group and struct names from column names (SODBC_NOGROUPNAME)
By default, if a field is part of a group or struct field in the repository, the group or struct name is added to the field name to create the column name for the system catalog. (See Groups and struct fields for information.) To omit group and struct names from column names, set SODBC_NOGROUPNAME to any value before you generate the system catalog. But do this only if you are certain the resulting column names will be unique. Set SODBC_NOGROUPNAME in the environment; for client/server configurations, set it where you run dbcreate.
If a group or struct field is an array, dbcreate generates a column for each element in the array (in addition to an overlay column), using the naming convention documented in Arrays. However, if SODBC_NOGROUPNAME is set, instead of using the group or struct name as part of the names for these columns, the names will start with “GR”. For example, if a repository has a group or struct field named myarray, that is a [2,2] array with a single field, myfield, the group or struct field will be mapped to the following columns: myarray (the overlay field), GR#1#1MYFIELD, GR#1#2MYFIELD, GR#2#1MYFIELD, and GR#2#2MYFIELD. |
Excluding tables attached to temporary files (SODBC_TMPOPT)
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 exclude tables attached to temporary files from the system catalog, set SODBC_TMPOPT to 1. Set SODBC_TMPOPT in the environment; for client/server configurations, set it where you run dbcreate.
Specifying a conversion setup file (SODBC_CNVFIL)
Conversion setup files are used when you regenerate a system catalog. They enable you to make changes to system catalog settings — changes to the paths and filenames for data files, access levels for tables, and so forth. (See Generating and editing a conversion setup file.) To use a conversion setup file, you must generate one and then specify it before or as you use dbcreate or DBA to regenerate the system catalog.
To specify a conversion setup file as you use dbcreate or DBA, use the -i command line option for dbcreate, or use the Conversion setup field in the Generate System Catalog window of DBA. For information on dbcreate command line options, see dbcreate utility. For information on the Conversion setup field, see Using DBA to generate a system catalog with level-based users.
To specify the file before you use DBA or dbcreate, set the SODBC_CNVFIL environment variable to the path and filename of the conversion setup file. If SODBC_CNVFIL is set, DBA and dbcreate automatically use the conversion setup file whenever you regenerate the system catalog. You won’t need to set a command line option, and the Generate System Catalog window of DBA will automatically specify the conversion setup file. In addition, if SODBC_CNVFIL is set, tables you delete in DBA will also be marked for deletion in the conversion setup file.
Note the following:
- The SODBC_CNVFIL environment variable should not be set until the conversion setup file has been created.
- If the SODBC_CNVFIL environment variable is set, it must be set in the environment.
- For client/server configurations, set SODBC_CNVFIL where you run dbcreate, and put the conversion setup file where it can be accessed by dbcreate.
- If you use the conversion setup file command line option (-i) for dbcreate without specifying a filename, the conversion setup file is not
While in DBA, changes you make to tables automatically and immediately update both the system catalog and the conversion setup file specified by the SODBC_CNVFIL environment variable. Be careful to use SODBC_CNVFIL to specify the exact conversion setup file for the system catalog you are modifying before you open DBA.
Using decimal information in the repository format string (SODBC_USEFORMAT)
If your repository has a field that’s not an implied decimal, but has a format string with a decimal point, you can instruct dbcreate and DBA’s Generate option to use the decimal information in the format string to create an implied decimal column in the system catalog. To do this, set the SODBC_USEFORMAT environment variable to 1 before you generate the system catalog. For example, if SODBC_USEFORMAT is set to 1 and your repository has a d5 field with an XXX.XX format string, the field will appear as a d5.2 column in the system catalog.
Set SODBC_USEFORMAT in the environment. For client/server configurations, set it where you run dbcreate.
Preventing null updates and interpreting spaces, zeros, and null values
xfODBC uses the “Null allowed” setting for a column to determine the following:
- Whether an alpha, decimal, date, or time column can be updated with a null value or some other value that xfODBC considers equivalent to null (spaces for alpha fields and alpha dates, and zeros for decimal and time fields).
- How nulls and spaces in some columns are interpreted. See How spaces, null values, and zeros are interpreted when read from a database.
If “Null allowed” is set to no for a column, the column must be included in every INSERT statement for the table.
To see what this setting is for a column, use SQLDescribeCol to get the NullablePtr setting:
- If NullablePtr=SQL_NULLABLE=1, nulls are allowed for the column (i.e., “Null allowed” is set to yes).
- If NullablePtr=SQL_NO_NULLS=0, nulls are not allowed (i.e., “Null allowed” is set to no).
You can also use the DBA program to view the “Null allowed” setting for the column (see Viewing information about a column).
Setting “Null allowed” for a column
To set this property for a column, set the “Null allowed” Repository option for the field before generating the system catalog. (See Validation information.)
- If the “Null allowed” Repository option is set to Yes or No for a repository field, dbcreate uses this setting for the system catalog column it generates for that field. For alpha, date, decimal, and time fields, you can set this option to Yes, No, or Default. For other fields, it can be set only to No or Default.
- If the “Null allowed” Repository option is set to Default for a repository field, the system catalog column generated for that field will be set to allow nulls unless it is a Boolean, binary, or integer field, or a non-date field that is part of the definition for the first key for the table. (The SODBC_NONULL environment variable changes this behavior, but this environment variable is deprecated.)
We recommend that you set every repository field used in a key definition to preclude nulls (i.e., set the Repository “Null allowed” option for the field to No), except those fields that must actually be able to accept null values. This is particularly important if you access your data in a .NET environment.
The “Null allowed” setting is yes for columns that are part of a table added with CREATE TABLE unless you use NOT NULL in the CREATE TABLE statement. See CREATE TABLE.
How spaces, null values, and zeros are interpreted when read from a database
When reading from a database, the xfODBC driver interprets spaces and null values differently for some columns depending on how “Null allowed” is set. Note the following:
- xfODBC interprets zero-length strings as nulls.
- xfODBC interprets invalid data as null, unless you set the convert_error option (see Specifying handling of invalid dates).
- The behavior for user types is the same as their base types. For example, see the Alpha row in the table below for information on how the “Null allowed” property affects user-defined alpha fields.
Data type |
Column value |
If “Null allowed” is yes,a |
If “Null allowed” is no,b |
---|---|---|---|
Alpha |
Spaces |
Null |
Spaces |
Null |
Spaces (filled to max length) |
Spaces (filled to max length) |
|
Binary |
Zero |
Zero |
Zero |
Boolean |
Zero |
False |
False |
Decimal |
Spaces |
Null |
Zero |
Zero |
Zero |
Zero |
|
Null |
Null |
Zero |
|
Date |
Spaces |
Null |
Null |
Zero |
Null |
Null |
|
Null |
Null |
1-1-0001 |
|
Integer |
Zero |
Zero |
Zero |
Time |
Spaces |
Null |
Null |
Zero |
Null |
Null |
|
Null |
Null |
00:01 |