Oracle Tables

The following describes how to process Oracle tables from the TS Environment.

Accessing Oracle Tables

The following describes how to create Oracle tables and then access these tables from the TS Environment. For more information specific to Script-IV and Dictionary-IV see Script-IV/Dictionary-IV Tables.

Using Basic

Existing server files are accessed using the following syntax:

OPEN(CH,OPT="LINK")"link-name"

"LINK"

specifies that the file to be opened on this channel is found in the system dictionary in the link-name record.

"link-name"

specifies a valid link-name containing the necessary server references.

New server files may be created from the Basic environment as follows:

OPEN(CH,OPT="LINK|CREATE")"link-name"

"CREATE"

is a parameter that causes Basic to generate the server commands necessary to build the file. The Oracle table definition is based on the format listed in the Link Definition. If the table already exists on the Oracle server, the CREATE is ignored and the table opened.

A special OPEN syntax is used for access to text field data. This syntax is used internally by the Dictionary-IV system for accessing text field data. For more information see the Text Fields for Oracle Data section later in this manual.

OPEN(CH,OPT="LINK|TEXT")"link-name"

"TEXT"

specifies that the text file from the link is to be opened.

If multiple SQL servers are configured, existing files on different servers can be accessed using the following syntax:

OPEN(CH,OPT="LINK|SID=’server-id’")"link-name"

"SID="

is a parameter that causes Basic to access the specified server instead of the server ID defined in the link definition.

"server-id"

specifies the id of one of the configured SQL servers.

Server files with names different from the names defined in a link may be accessed using the following syntax:

OPEN(CH,OPT="LINK|TABLE-NAME=’table-name’|TEXT-FILE=’text-table-name’")"link-
name"

"TABLE-NAME="

is a parameter that causes Basic to use the specified table name instead of the one defined in the link definition.

"table-name"

specifies the table name.

"TEXT-FILE="

is a parameter that causes Basic to use the specified text field table name instead of the one defined in the link definition.

"text-table-name"

specifies the text field table name.

The following code fragments show two ways of using the new OPEN syntax to access files. The examples assume a Link definition with information in the Server ID and Table Name fields and a file type of M.

If no server information is present, the OPEN(OPT=“LINK”) will access the local file referenced by the Data File name. In version 8.3.0 a Link opened this way expected the Format to contain field separators and the data file to be a fixed field/fixed length record. In this version the Format and data are required to match. In addition the OPT=“LINK” supports local files with or without field separators.

Example 1

OPEN/READ server file into IOLIST (variables)

10       IOLIST CUST_CODE$,CUST_NAME$,ADDRESS$

100      CH=UNT;
         OPEN(CH,OPT="LINK") "UTCUST"
         READ(CH,KEY="0001")IOL=10

This example assumes a Format containing field separators.

Example 2

OPEN/READ server file into FORMAT

10       CH=UNT;
         OPEN(CH,OPT="LINK") "UTCUST";
         FORMAT INCLUDE #UTCUST;
         READ(CH,KEY="0001")#UTCUST

This example can be used on any format with or without field separators.

Example 3

OPEN/READ server file into record variable

10       CH=UNT;
         OPEN(CH,OPT="LINK") "UTCUST";
         READRECORD(CH,KEY="CUST0001) REC$

This example shows access using a Format without field separators. The data in REC$ could be used to populate the format data area.

Under certain circumstances an error 167 may be returned when writing to a Link opened with OPT=“LINK”. When employing a Format directly, or a Format that contains field separators, the system assigns that data to the data element name.

If this data is invalid for the particular data element name (invalid value for Yes/No fields, blank mandatory fields, or invalid data in a date field) an error 167 occurs. Printing the DNE system variable at the instance of the error will display the data element name for which the assignment failed.

OPT=“LINK” and Local Files

The OPT=“LINK” parameter now allows you to use it with local files. The same application code that accesses server tables can be used to access local files. Once you establish a Link to reference a server table, clear the Server ID and you can use this Link to reference a local file.

This is applicable to 3GL code. For Script-IV, changes to the Link header require you to recompile your script.

ERASE Server tables are deleted from within the Basic environment by specifying the Link name and OPT=“LINK”.

ERASE"UTCUST",OPT="LINK"

This deletes the table from the server referenced in the Link UTCUST. It also deletes the text field table if any text fields were defined. Specifying an OPT=“” erases local files.

INITFILE Server tables may be cleared of all data by using the INITFILE directive. This directive is useful for clearing a table without the extra step of performing an erase.

INITFILE "UTCUST",OPT="LINK"

This clears the table on the server referenced in the Link UTCUST. It also clears the text field table if any fields are defined. Specifying an OPT=“” clears local files.

RENAME
The rename function allows a table name to be changed on the server from the client process.

RENAME "UTCUST","UTTEMP",OPT="LINK"

This renames the table referenced in Link UTCUST to the table UTTEMP. No changes are made to the original Link. Specifying an OPT=“” performs the rename on local files.

FID Function When the FID is executed on a channel where a Link referencing a server table is opened, the resulting string contains (in addition to the standard FID information) the following:

Byte 1

Value is "S". Indicates that this is a channel opened to a link referencing a server table.

Byte 2 - 3 The two character Server ID from the Link.

The bytes that normally contain the file name now contain the Link name.

TCB Function Error 150 has been added to the Basic error list to reflect errors resulting from server operations. To return the specific server error, an argument has been added to the TCB( ) function. The TCB(20) now returns the server error in the same way that the TCB(3) returns the operating system error. The value returned for the TCB(20) can be interpreted by referring to the server error guide.

DUMP Directive
Several of the DUMP options have been changed to be aware of server access.

DUMP CHANNELS now display the link name when a link has been OPENed with OPT=“LINK”.

DUMP IPLDEVS will specify that devices configured as a server will return “Access using server xx.”

XFD Function
The following describes the current values returned for server tables:

Option = 0
Bytes	Description
1-2	Unused
3-10	N/A
11-35	Unused
36-38	N/A
38-41	N/A
42-47	Unused
48	“N”
49-53	Unused
54-59	N/A
60-65	N/A
66-71	N/A
72-85	Unused
86+	N/A

Option = 1
As documented in the Thoroughbred Basic Reference Manual.

Option = 3
Bytes	Description
1	$05$
2	$83$
3	Number of sorts (binary)
4	Current sort number (binary)
5-12	"NNY" (bytes 8-12 Unused)
13-14	N/A
15-16	COBOL only
17-42	Unused
45+	Sort definition parameters (For more information see the Thoroughbred Basic Reference Manual.)

Option = 4
As documented in the Thoroughbred Basic Reference Manual.

Option = 6
Bytes	Description
1	$06$
2	$00$
3-6	$00000000$
7-8	Bytes per record (binary)
9+	Link name

Option = 10

Returns -1

FST Function
This function is not supported for server tables.

IND Function The value returned by the IND function is the ORACLE ROWID. Unlike local files this value does not start at 0 for the first record and continue incrementally. That is, reading the table sequentially by index number will produce unexpected results. However, reading specific records by a determined index value is supported.

Note

The string following OPT= can be a variable in any of the commands where it is specified. The value does not need to be hard-coded into the program.

Using Sorts

Sorts, also known as indexes, are handled in substantially the same manner for server tables as they are for local files. Created in the Link definition, they can be used for access in any operation that currently supports sorts. As with MSORT, if the Link references an existing file, the link definition can be updated to include sorts defined in the file without rebuilding the file. The differences occur in the way that sorting is applied to the server table.

Indexes do not need to physically exist in the server environment to use Link sorts anywhere in Dictionary-IV. As long as the sort definition is established in the Link definition, Dictionary-IV can use it to access the data file. Basic will expand the sort statement defined in the Link into the appropriate field names, rather than passing only the sort number.

Oracle sort statements will refer to these field (column) names, not index names, when defining an ORDER BY statement. So, if the index does not exist, Oracle performs the sorting operation in-line. However, if an Oracle index exists for the columns in the ORDER BY statement, the index is used to access the data file. However, the Indexes will be used only if one of the columns has been defined with a NOT NULL constraint. For more information see the Performance Tuning section later in this manual.

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 the sorts. Erase and recreate the table. The indexes for all sorts are now defined on the server.

Oracle does not support sorts on substrings of fields.

The sorts (indexes) are created using the following naming convention:

“I_” + table-name + “ _ “ + Dictionary-IV sort number

Example:

For sort number 2 on a Link with a table-name defined as TSISERVER, the index would be created as “I_TSISERVER_2”.

If there are sorts defined in the Link that refer to fields that contain blank data, Oracle places these records at the end of an ascending sort and at the beginning of a descending sort. To modify this behavior see Null Processing.

Adding and deleting sorts is not supported from within the Basic client (ADDSORT/REMSORT). These functions are reserved for the database administrator.

Locking Records

The locking facilities available with Oracle provide the developer with resources to ensure data consistency. Many of the features designed for these goals occur automatically. For more information refer to the Oracle Concepts Manual.

This section explains how these locking mechanisms correlate to Thoroughbred directives and how to relate locking that occurs in the Thoroughbred application to that, which occurs in an Oracle application.

The following table summarizes the relationship of Thoroughbred directives to Oracle SQL statements and the level of locking associated with each.

SQL Statements/Thoroughbred Directives

* Valid as long as no conflicting records are involved.

For more information about the WRITE statement see the Generated SQL Code section later in this manual.

The Lock Modes Permitted column indicates what type of lock may be placed on the file (table) or record (row) while the particular directive is in effect. For example, if a record is EXTRACTed in Thoroughbred, exclusive (X) locks by another client or Oracle task are not allowed and produce an error. All other lock types are valid as long as a different row is locked.

Since the query-type functions (READ, FIND, etc.) do not generate a lock, it will not fail under any locking mode the table or record may currently have. This means that READing locked records will not require PRM READONLY to access tables or rows locked by either an Oracle task or another Thoroughbred client process.

Null Processing

By default, null key values will be returned to the Thoroughbred Basic application following all other records for ascending sorts and before all records for descending sorts. This is an Oracle convention and cannot be changed. However, two options exist to alter this behavior. Both methods involve the modification of the null data so that the sorting mechanism treats the null key value as non-null data. This is more consistent with a Thoroughbred Basic application where null keys appear at the top of an ascending sort and at the bottom of a descending sort.

In the first method, the null data is logically changed. This is accomplished by using the ORACLE NVL function in the ORDER BY clause of the SELECT statement. Each column listed in the ORDER BY clause is passed through this function which will map null values to the value supplied to the function. In this case, the value is a single space. This value will then be passed to the sorting process so that these records will sort at the appropriate end of the result set. While this method makes no modification to the physical data, it may cause sorts to perform poorly for composite indexes. To activate this method, specify the PRM ORA_NVLNULLS in the IPLINPUT file.

Note

For single column indexes that allow null values, performance may not be an issue since this index would not be utilized.

In the second method, the data is changed physically. Whenever a null character field is written or updated to an Oracle table, it will be changed to a single blank. All subsequent accesses with indexes will return the data with the blank fields sorted appropriately. This method will allow the index to be used for queries so that the result set is returned efficiently, but the data is modified. It will be important for other applications to maintain the space in the data. To activate this method, specify the PRM ORA_DONTWRITENULLS in the IPLINPUT file.

As an alternative to the above methods, the table could be created so that columns used as indexes can never contain null values. This will happen if the field is defined in Dictionary-IV as a fixed length type, either optional or mandatory (Entry Type 1 or 3). This attribute will ensure that the data for that column will not be null.

For access to existing tables, all of the options are available, however, care must be taken if new data is written to a table when the ORA_DONTWRITENULLS parameter is specified. This will create a situation where only the new records with null keys appear in the proper sorted order.

CMASK

The Thoroughbred Basic client will always output data to Oracle tables as if CMASK is set to “.=.”. Oracle will accept this regardless of its setting for the numeric character masking. However, when reading data, no assumptions are made and the masking must match on both the client and server systems.

Processing Tables from Oracle

To access existing Oracle tables you must build a Format and Link with the appropriate table information to be derived from the Oracle system.

The following Oracle constructs have no Dictionary-IV equivalent:

• LONG data type
• VARCHAR2 fields greater than 255 bytes in length
• NUMBER fields exceeding Thoroughbred’s maximum of 14 significant digits cannot be supported. The maximum length of this column type in Oracle is 40 where 38 are significant. The concepts of negative scale and scale greater than precision are not supported.
• Column names exceeding 20 characters in length.

You have the ability to access tables containing the above characteristics for reading and updating. By defining a format that contains a partial list of the fields defined in the table, data can be read and updated without affecting the fields not included in the format.

Numeric columns that Thoroughbred will support are defined as a precision of 5 and scale of 2 with a length of 7.2 for numeric types 0 and 2. It must be defined with a length of 6.2 for type 1. Oracle does not include the minus ( - ) sign in column length.

Text Fields for Oracle Data

Dictionary-IV treats server tables internally as MSORT for I/O processing. Therefore, text field data associated with servers must be stored in a separate file as is the current convention with MSORT type files. This file, referenced as the text file in the Link header, must be stored on the server with the main table to maintain data integrity, to provide access to all clients, and for backing up.

If the Format defines one or more text fields then a separate table for the text data is automatically generated when the table for the record data is created. The record data is stored in the table referenced by the Table Name field and the text field data is stored in a table referenced by the Text File field. If present, a dot ( . ) in the file name is converted to an underscore
( _ ).

The definition of the text table is derived from the record data and a number of preset fields that are used internally by Dictionary-IV. This does not appear as a Dictionary-IV Format but exists as an Oracle table. A logical Format called IDSV1 has been created to aid in the handling of this information. It is used by the system and is not associated with any specific Link.

The layout is as follows:

KEY_FIELD      VARCHAR2 (len=length of primary key + 1 for text id)
CREATE_DATE    DATE
CHANGE_DATE    DATE
TEXT_WIDTH     NUMBER(3)
TEXT_FIELD     LONG

KEY_FIELD Contains the key to the record data plus the text id. This is similar to the current key structure of text field data, except there is no need for a sequence counter since the entire text field is stored in one record. This is possible because the LONG column type has a maximum storage capacity of 2 gigabytes.

CREATE_DATE
Specifies the creation date.

CHANGE_DATE
Specifies the date of the last change.

TEXT_WIDTH
Used by Dictionary-IV for compatibility to local MSORT processing.

TEXT_FIELD Because this text data has been made available as an Oracle table, the data is comprised of purely printable characters (except line terminating $0A$). Therefore, any color or attribute information is stripped prior to storage in the table. Graphic characters are converted to a standard ASCII character representation. Changes made from an Oracle application are available to the Thoroughbred application.

All file level access (create, erase, and clear) performed on the record table is also executed on the text table. However, all record level access requires a distinct OPEN of the text table. This is consistent with the current MSORT text field operation.

During I/O processing a $FF$ is implied in the first byte of the key. This byte does not appear in the data but is required for programming consistency. The byte is required for all key access and is returned in all key functions (KEY, FKY, LKY, and PKY).

The Text field data is automatically maintained when accessed via Dictionary-IV. No changes are required to CONNECTs, Scripts, Reports, or Queries that currently use Text fields.

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