Setting catalog generation options

This topic includes the following sections:

 

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 Synergy/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 Synergy/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.

Note

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 Synergy/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:

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.

Note

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:

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:

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:

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.)

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:

Data type

Column value

If “Null allowed” is yes,a
value read as…

If “Null allowed” is no,b
value read as…

Alpha

Spaces

Spaces (filled to max length)

Spaces (filled to max length)

Null

Null

Spaces

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

a. I.e., if SQL DescribeCol returns NullablePtr=SQL_NULLABLE=1 for the column.

b. I.e., if SQL DescribeCol returns NullablePtr=SQL_NO_NULLS=0 for the column.