Dictionary-IV Formats and Links

You must have a Format and Link defined to access a MySQL table.

Format Definition

Although no changes are required to existing formats in order to use the client capabilities of the environment, you should be aware of the following information.

Note

The data element names must not be MySQL reserved words. For more information see the appropriate MySQL documentation.

File Type

When Thoroughbred creates an MySQL table, data types are translated as follows:

Alphanumeric: VARCHAR or CHAR
Text field:    TEXT
Numeric:       DECIMAL
Date:          DATETIME

The VARCHAR and CHAR data types only support characters above hex 20 to be included in the data. Any character below hex 20 is converted to a space.

The CHAR type will be used when the Basic PRM CREATWDBATTR is configured and the data
element is set as a fixed length field either mandatory or optional (Entry Type 1 or 3).

When records are written to this table, any field of the CHAR type will be assigned a value of at least one space. This will ensure that the column is not null. For more information see PRM CREATWDBATTR in the IPLINPUT topic.

Entry Type

All mandatory fields (Entry Type 2 and 3) will be set as NOT NULL fields in the MySQL tables when the Basic PRM CREATWDBATTR is set.

Data Elements – Naming

MySQL only allows alphanumeric characters, the currency symbol ($) and an underscore ( _ ) in column names. The first character of the column must start with a letter. Any deviation from this convention results in table creation failure.

The tsmysql program translates the hyphen ( - ) that is acceptable in Thoroughbred Dictionary-IV Formats to an underscore ( _ ).

The use of MySQL reserved words also results in table creation failure. For more information see your appropriate MySQL reference material.

Data Elements – Multiple Occurrences

MySQL does not support multiple occurrences for a single data element. For more information, see Multiple Occurrences in the Dictionary-IV Developer Guide.

The system creates separate columns in the MySQL table from a multiple occurrence field. The following is the naming convention for multiple occurrences.

data-element-name_seq#

seq#

is the range from one to the number of defined occurrences.

Binary Numeric Fields

Fields defined as binary in the Format are converted to decimal values and stored in MySQL DECIMAL fields. The type of binary field determines the length of the field.

The following table displays the formulas for calculating the field width in the MySQL table.

Formulas for Calculating Field Widths

In all cases: if the value contains a decimal, add one to the field width.

SQL Dates

The system automatically translates dates stored in either of the SQL formats into the standard SQL DATETIME storage format as you update records.

Link Definition

The Link definition contains the configuration parameters necessary to describe a server table to Dictionary-IV. To access the MySQL tables, you must enter the following information in the Link definition:

• File Type: M
• Server ID
• Table Name

Below is a sample Link definition screen:

Server ID The server ID is a unique identifier that references the host via the definition in the SERVER.MAP file. It will be validated against the list of server devices configured in the IPLINPUT file. Although no ID will be disallowed, a warning will be displayed.

Server Table Name
The Table Name is the name used to create a table on the server.

Text File A Text File value is required if the Format contains Text fields. In the example above, the Text Field data will be maintained in a MySQL table named CUSTOMER_TXT. If the Text File name had a .TXT extension, the MySQL table name would be converted to use _TXT.

Both the table and text file names must conform to MySQL table naming conventions. For more information see the appropriate MySQL document to review the restrictions.

Links must be defined as a file type M (MSORT). While the physical file is not stored as an MSORT file, it will be processed as one in Dictionary-IV for sorts and text field storage. For more information see Using Sorts and Text Fields for MySQL Data.

For Dictionary-IV to detect that a Link references a server-based table, File Type, Server ID, and Table Name must all contain valid information. If they do not a local file will be used, referenced by the name entered in the Data File field.

The system supports file suffixes. The file suffix character ( @ ) must be placed in the Table Name field for server files, rather than the Data File field. Where text fields are configured and a file suffix is used, the text file name must include the suffix character. For more information refer to the Dictionary-IV User Guide.

Sort definition occurs in the same way for server Links as for local Links and should be saved and updated with F8 processing. Even though no physical rebuild takes place, this will properly update the Link header. For more information see the Dictionary-IV Developer Guide.

If sort definitions exist in the Link and the table is recreated, the indexes will be defined automatically on the server as part of the create process.

Under the current Dictionary-IV methodology, first build a Link header and create the table. Modify the Link header to define sorts. The next time the table is created, the indexes for all sorts will be defined on the server.

Once the Link header is configured, multiple and single record maintenance, CONNECTs, reports, queries, and recompiled scripts can access server tables. For more information about modifying Basic programs to support access to server tables see Using Basic.

While the data and sort file names may be populated during the Link header creation, neither is referenced during server access. However, the data file name is used to generate the text file name for text field storage. Therefore, be sure to either enter the data file name for the automatic generation of a text file name or manually enter the field.

From the TS DataServer for MySQL Reference, version 8.9.1. Open this page in the PDF