Using the UBS File Bridge Server

Overview

The UBS File Bridge Server enables SQL access to data stored in files outside of a relational database system. Supported operating systems are Linux / UNIX, Windows, and z/OS.

The following types of files are supported:

Operating System	File type	Access
Linux / UNIX, Windows	Regular files	read / write
z/OS	VSAM KSDS, ESDS, RRDS	read / write
z/OS	Unix System Services files	read / write
z/OS	Sequential data sets or PDS members	read only

Operating System

File type

Access

Linux / UNIX, Windows

Regular files

read / write

z/OS

VSAM KSDS, ESDS, RRDS

read / write

z/OS

Unix System Services files

read / write

z/OS

Sequential data sets or PDS members

read only

To access VSAM KSDS, RRDS or ESDS data sets under z/OS, the data set contents must be described by a COBOL or PL/I copybook. For all other types of files, the contents must be in CSV format and can only contain plain text data.

General usage

The UBS File Bridge Server consists of two components:

A server process that must run on the operating system where the files to be accessed are located.
A JDBC type 4 driver that can be used by Java applications.

The server process maintains a directory where it stores information about the files that is ready to access and how the contents of these files are mapped to virtual tables and columns.

The directory contents are retained when the File Bridge server is stopped and restarted. It can be accessed by client applications using normal SELECT statements, which enables them to retrieve information about all files that the server can access.

Internally, a physical file is represented by a tablespace object and the structure of the file’s contents is represented by a table object. For VSAM KSDS, RRDS and ESDS data sets under z/OS, one tablespace (data set) can contain data from multiple tables. For all other types of files, one tablespace can only contain one table.

Information about new files can be added to the directory by using DDL statements.

CREATE TABLESPACE is used to introduce a new file to the server. In the CREATE TABLESPACE statement, you specify the file name, the type of file, and optional attributes that depend on the type of file.
CREATE TABLE is used to declare a virtual table in an existing tablespace. The CREATE TABLE statement contains a list of column declarations that are used to interpret the contents of each row (record) of the file and map it to virtual table columns.

When a tablespace associated with a CSV file is created, if the CSV file does not yet exist, then the File Bridge server will create the file the first time that the table in that tablespace is accessed. If necessary, all parent directories are also created. Note that the creation of the CSV file may fail if the File Bridge server does not have the authorization to create files or directories at the specified location.

You use DML statements to access the data inside the files. Prepared statements with parameter markers are supported.

SELECT statements retrieve information from the files. The data is returned in the form of a standard JDBC result set.
INSERT statements are used to add new rows to a file.
UPDATE statements modify existing rows.
DELETE statements remove existing rows.

It is not possible to run INSERT, UPDATE and DELETE statements against tables whose tablespace refers to a sequential data set or a PDS member on a z/OS server. Only SELECT statements are allowed for these tables.

It is possible to use WHERE conditions in SELECT, UPDATE and DELETE statements.

You can use DROP statements to remove information about virtual tables and tablespaces from the File Bridge Server directory.

When dropping tables, only the description in the directory is removed. The physical file itself is not deleted or modified in any way.

When dropping tablespaces and the associated file is a CSV file, the UBS File Bridge server can optionally delete the CSV file. You must explicitly specify the keyword PURGE in the DROP TABLESPACE statement in order to have the UBS File Bridge Server delete the associated CSV file. In addition, the File Bridge server must have the authorization to delete the file.

When executing DROP TABLESPACE with the PURGE option, only the CSV file itself is deleted. If the file did not exist when the tablespace was created, and the File Bridge server had to create one or more parent directories in order to create the CSV file at the specified location, these parent directories are not deleted.

A full description of all supported SQL statements can be found in section "File Bridge Statements".

Executing SQL statements

After starting the UBS File Bridge Server, any Java program capable of executing SQL statements can connect and work with CSV files and VSAM data sets using the File Bridge JDBC client driver JAR file.

XDM itself uses the File Bridge JDBC client driver JAR file to execute DML statements. However, it does not provide facilities for executing arbitrary SQL statements. Therefore, in order to create the directory entries that represent CSV files and VSAM data sets and the virtual tables that correspond to them, you need to use a generic Java-based JDBC client. One example for such a client is SQuirreL, which can be downloaded from http://www.squirrelsql.org/ .

To use the File Bridge JDBC client driver JAR file with SQuirreL:

Install and start SQuirreL.
In the menu bar, click Drivers → New Driver…. Enter the following information into the fields:

Name

UBS File Bridge

Example URL

jdbc:ubs://<server>:<port>;<encrypted>;

Website URL

https://www.ubs-hainer.com
Click the tab Extra Class Path, then click Add. Locate and select the file jdbcserver-driver-bundled-jar-with-dependencies.jar
Under Class Name, select de.ubs.jdbcserver.driver.UBSDriver.
Click OK.

You can now use the driver to establish a connection to the File Bridge Server:

In the menu bar, click Aliases → New Alias…. Enter the following information into the fields. In this example, it is assumed that the File Bridge server is running on 192.168.50.1 and uses port 5000 and that there is a user account with the name admin and the password pass123word. Please replace those values with the correct IP address, port, and credentials for your installation. If the File Bridge Server uses SSL/TLS encryption you must add encrypted=true to the URL. Please make sure that the certificate is available in the Java key store:

Name

UBS File Bridge

Driver

Select UBS File Bridge

URL

jdbc:ubs://192.168.50.1:5000;encrypted=true;

Name

admin

Password

pass123word
Click OK. The new alias will now show up in the list of aliases, which can be accessed in the left side bar.
Right-click the new alias and click Connect…. A dialog window opens. Click Connect.
After connecting, the main part of the screen will now display your session window. Click the tab SQL. You can now enter and execute arbitrary SQL statements.

Considerations for accessing CSV files

On all supported platforms, you can access data in CSV files as if they were tables in a relational database system.

Retrieving data from CSV files

You can use SELECT statements to retrieve data from CSV files. It is possible to specify a WHERE condition in order to only retrieve those rows that match the condition.

Since CSV files lack indexes, SELECT statements with WHERE conditions cause the File Bridge Server to iterate over all rows in the CSV file. If you know that only a limited number of rows match the condition (for example, when referring to a column that is known to be unique), you can improve query performance by adding a FETCH FIRST clause to the SELECT statement. When a FETCH FIRST clause is provided, the server will stop searching for rows matching the WHERE condition when the specified number of rows has been retrieved.

Rows are returned in the order in which they physically appear in the CSV file. It is not possible to use an ORDER BY clause in a SELECT statement that retrieves data from CSV files.

Modifying existing CSV files

The UBS File Bridge Server allows you to modify existing CSV files by executing INSERT, UPDATE or DELETE statements.

INSERT statements always append the new data to the end of the file.
Changes resulting from UPDATE and DELETE statements are cached in a memory buffer and materialized when the buffer capacity is exceeded, or when the statement is closed.

The process of materializing changes consists of creating a copy of the CSV file with the changes applied, then renaming the files so that the new copy takes the place of the original file, and finally deleting the original file.

Concurrent access to the CSV file by other processes may cause the materialization to fail. It is recommended to ensure that for the duration of any session that modifies a CSV file, the UBS File Bridge Server has exclusive access to the file.

The UBS File Bridge Server does not enforce the uniqueness of any set of columns in a table that is associated with a CSV file.

Encoding of CSV files

In general, it is not possible to automatically detect the encoding of a plain text file. Therefore, you must specify the encoding of a file in the CREATE TABLESPACE statement that refers to that file.

On Linux / UNIX and Windows, typical encoding names are:

UTF-8
UTF-16LE
ISO8859-15
Windows-1252

On z/OS, you can specify the encoding as IBMxxxx, where xxxx is a numeric value of at least three digits (left-padded with zeroes, if necessary) that identifies the CCSID. Typical encoding names are:

IBM037 (US-English EBCDIC)
IBM1047 (Latin 1 / Open Systems EBCDIC)
IBM1141 (Austria, Germany EBCDIC with Euro)
IBM1144 (Italy EBCDIC with Euro)
IBM1147 (France EBCDIC with Euro)
IBM819 (ISO 8859-1 Latin 1 / Open Systems ASCII)
IBM1208 (UTF-8)
IBM1200 (UTF-16BE)

By default, the UBS File Bridge Server will replace any byte sequence in a CSV file that is not valid in the specified encoding with a substitution character. This substitution character is the symbol at Unicode code point U+FFFD. By specifying the additional keyword STRICT in the ENCODING clause of the CREATE TABLESPACE statement, you can have the server issue an SQL error instead whenever an invalid byte sequence is encountered.

You should always determine the encoding that a CSV file uses and make sure to specify it correctly in the CREATE TABLESPACE statement. If you process a CSV file for which an incorrect encoding was specified, characters (typically characters with diacritics or typographical characters) can become corrupted. This is particularly important when using an 8-Bit encoding such as ISO8859-15, Windows-1252 or IBM1047. An 8-bit encoding maps every possible byte value to a character. Therefore, the UBS File Bridge Server is not able to detect if the specified encoding is incorrect.

If you create a tablespace without an ENCODING specification, the system default encoding is assumed.

Line endings in CSV files

The UBS File Bridge Server automatically detects whether UNIX-style line endings (X'0A') or Windows-style line endings (X'0D0A') are used in a CSV file and it uses the detected line ending for all inserted or updated records. When working with a CSV file that is initially empty, the default line ending of the operating system on which the UBS File Bridge Server runs is used.

Values in quotation marks

The CREATE TABLESPACE statement allows you to specify a QUOTE option that determines which fields are enclosed in quotation marks. This option has the following possible values:

Value	Description
ALL	All fields are enclosed in quotation marks
STRINGS	All string fields (CHAR, VARCHAR) are enclosed in quotation marks. This is the default.
MINIMAL	String fields (CHAR, VARCHAR) are enclosed in quotation marks if the column delimiter character is part of the string value
NONE	No fields are enclosed in quotation marks

Value

Description

ALL

All fields are enclosed in quotation marks

STRINGS

All string fields (CHAR, VARCHAR) are enclosed in quotation marks. This is the default.

MINIMAL

String fields (CHAR, VARCHAR) are enclosed in quotation marks if the column delimiter character is part of the string value

NONE

No fields are enclosed in quotation marks

Double quotes (") are used by default for the quotation marks. You can use the CHARDEL option to specify a different character.

When the quotation mark character appears inside a string, it must be doubled. for example, if the quotation mark character was set to the apostrophe ('), then the string 12 o’clock must be written as '12 o''clock' inside the CSV file.

Strings in quotation marks may contain the line ending character. In this case, the line ending is considered to be part of the string and not the end of the record. As a consequence, one logical record can span multiple physical lines inside the CSV file.

When reading data from CSV files, the UBS File Bridge Server tolerates quotes around values even if they should not exist based on the QUOTE parameter. For example, when reading an integer value that is enclosed in quotation marks, the quotation marks will be removed and the integer value will be correctly interpreted, even if QUOTE ALL was not specified.

When writing new records to a CSV file or updating an existing record in a CSV file, the UBS File Bridge Server will honor the specified QUOTE parameter. Therefore, you should make sure that the QUOTE parameter is set correctly in order to avoid inconsistent usage of quotation characters inside the CSV file.

Boolean values in CSV files

In CREATE TABLE statements, you can declare columns with the data type BOOLEAN. These columns can only contain the values true or false, and the NULL value if allowed by the column specification.

By default, true is represented by 1 and false is represented by 0. If you use other representations of boolean values in your CSV file (for example, Y and N), you can use the BOOLVALUES parameter of the CREATE TABLESPACE statement to specify the desired text representation.

When reading boolean values from CSV files, the column value is compared with the specified text representation of true in a case-insensitive manner. The field is interpreted as true if the strings match, otherwise the field is false.

When writing boolean values to CSV files, the UBS File Bridge Server always uses the exact text representation that was specified in the BOOLVALUES option.

NULL values in CSV files

In CREATE TABLE statements, you can specify whether the columns of the table are allowed to contain NULL values or not.

When a column is allowed to contain NULL values, the UBS File Bridge Server interprets multiple consecutive column delimiter characters as the field in between being NULL, except if the column is a string column and no quotation marks are used (in which case the field is interpreted as containing an empty string). If the column cannot contain NULL values, a default value that depends on the column type will be used instead.

The type dependent default values are:

Data type	Default value
String data types (CHAR, VARCHAR)	(empty string)
Numeric data types (SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL)	0
DATE	0001-01-01
TIME	00:00:00
TIMESTAMP	0001-01-01-00.00.00.000
BOOLEAN	FALSE

Data type

Default value

String data types (CHAR, VARCHAR)

(empty string)

Numeric data types (SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL)

DATE

0001-01-01

TIME

00:00:00

TIMESTAMP

0001-01-01-00.00.00.000

BOOLEAN

FALSE

When string fields are enclosed in quotation marks, a field consisting of two consecutive quotation marks will be interpreted as an empty (not NULL) string. Only two consecutive column delimiters without quotation marks in between will be interpreted as a NULL value.

Example 1

Assume a table with the four columns that have the data types INTEGER, VARCHAR, INTEGER, and VARCHAR.

Consider the following records in a CSV file that uses the comma as column delimiter, double quotes as string delimiter, and only string fields are enclosed in quotes:

2641,"ABC",29,"UVW"
2642,,,
,"",,""
2643,,,"XYZ"

If the table columns may contain NULL values, the record would be interpreted as follows:

Column 1 Column 2 Column 3 Column 4

2641

ABC

UVW

2642

NULL

(empty string)

NULL

(empty string)

2643

NULL

XYZ

If the table columns may not contain NULL values, the record would be interpreted as follows:

Column 1	Column 2	Column 3	Column 4
2641	`ABC`	29	`UVW`
2642	(empty string)	0	(empty string)
0	(empty string)	0	(empty string)
2643	(empty string)	0	`XYZ`

Example 2

Assume a table with the four columns that have the data types INTEGER, VARCHAR, INTEGER, and VARCHAR.

Given the following record in a CSV file that uses the comma as column delimiter, and fields are never enclosed in any type of quotes:

2642,ABC,29,UVW
,,,
2643,,,XYZ

If the table columns may contain NULL values, the record would be interpreted as follows:

Column 1 Column 2 Column 3 Column 4

2642

ABC

UVW

NULL

(empty string)

NULL

(empty string)

2643

(empty string)

NULL

XYZ

If the table columns may not contain NULL values, the record would be interpreted as follows:

Column 1	Column 2	Column 3	Column 4
2642	`ABC`	29	`UVW`
0	(empty string)	0	(empty string)
2643	(empty string)	0	`XYZ`

Date, time and timestamp values in CSV files

CSV files can contain text representations of date, time and timestamp values. When creating a table in a tablespace that is associated with a CSV file, you can specify how to interpret these values by providing a pattern for the table’s DATE, TIME and TIMESTAMP columns by adding a PATTERN clause to the column definition in the CREATE TABLE statement.

Patterns are sequences of characters in which certain letters identify the components of date, time and timestamp values. These letters can be repeated to determine the exact representation. Other characters, such as blanks or punctuation marks, are expected to appear in the text representation as they are.

The syntax of the patterns used by the UBS File Bridge Server is identical to those used by the Java class SimpleDateFormat. Please refer to the official Java documentation for a comprehensive list of all available pattern letters.

The most commonly used date and time components are as follows:

Letter Component Notes

Letter	Component	Notes
y	Year	Use `yy` for two digits, `yyyy` for four digits
M	Month in year (1 - 12)	Use `M` for one or two digits, `MM` for two digits, `MMMM` for 3-character abbreviation, `MMMM` for full name of month
d	Day in month (1 - 31)	Use `d` for one or two digits, `dd` for two digits
D	Day in year (1 - 366)
H	Hour in day (0 - 23)	Use `H` for one or two digits, `HH` for two digits
h	Hour in am/pm (1 - 12)
a	am/pm marker
m	Minute in hour (0 - 59)
s	Second in minute (0 - 59)
S	Millisecond

Year

Use yy for two digits, yyyy for four digits

Month in year (1 - 12)

Use M for one or two digits, MM for two digits, MMMM for 3-character abbreviation, MMMM for full name of month

Day in month (1 - 31)

Use d for one or two digits, dd for two digits

Day in year (1 - 366)

Hour in day (0 - 23)

Use H for one or two digits, HH for two digits

Hour in am/pm (1 - 12)

am/pm marker

Minute in hour (0 - 59)

Second in minute (0 - 59)

Millisecond

The following default values are used if no explicit pattern is specified:

Data type Default pattern

Data type	Default pattern
DATE	`yyyy-MM-dd`
TIME	`HH:mm:ss`
TIMESTAMP	`yyyy-MM-dd-HH:mm:ss.SSS`

DATE

yyyy-MM-dd

TIME

HH:mm:ss

TIMESTAMP

yyyy-MM-dd-HH:mm:ss.SSS

Examples

Description Sample value Pattern to use

Description	Sample value	Pattern to use
European date format	31.12.2019	`dd.MM.yyyy`
U.S. date format	12/31/2019	`yyyy/MM/dd`
Ordinal date format, no separator	2019365	`yyyyDDD`
ISO date format with 3-letter month	2019-DEC-31	`yyyy-MMM-dd`
U.S. time format (without seconds)	4:15 pm	`hh:mm a`

European date format

31.12.2019

dd.MM.yyyy

U.S. date format

12/31/2019

yyyy/MM/dd

Ordinal date format, no separator

2019365

yyyyDDD

ISO date format with 3-letter month

2019-DEC-31

yyyy-MMM-dd

U.S. time format (without seconds)

4:15 pm

hh:mm a

CSV files with binary data

Do not try to access CSV files that contain binary data with the UBS File Bridge server. Data corruption can occur if you modify the data in such a file.

Unprintable characters such as line feed, carriage return or tab stop characters are not considered binary data in this context. For example, you can specify that a CSV file uses tab stops as column delimiters by specifying COLDEL X'09' in the CREATE TABLESPACE statement.

The COLDEL option expects the delimiter character to be specified in UTF-8 encoding. Therefore, X'09' identifies the tab stop character even if the CSV file itself uses an encoding where the tab stop has a different code point.

Considerations for accessing VSAM data sets

When the UBS File Bridge Server is running on a z/OS system, you can access VSAM data sets (KSDS, RRDS and ESDS) as if they were tables in a relational database system.

VSAM data sets that use extended format and extended addressability are supported.

It is possible to create more than one table in a tablespace that is associated with a VSAM data set. All tables in such a tablespace must have one or more common columns. The contents of these columns determine to which table a record belongs. You can use a CREATE TABLE statement with a WHEN condition to assign records in the same physical VSAM data set to multiple logical tables in the tablespace.

Retrieving data from VSAM data sets

You can use SELECT statements to retrieve data from VSAM data sets. It is possible to specify a WHERE condition in order to only retrieve those rows that match the condition. When accessing VSAM KSDS, the UBS File Bridge Server automatically determines if it is possible to use index access in order to increase the query performance.

In addition, depending on the options that were specified in the CREATE TABLESPACE statement, the UBS File Bridge Server can invoke DFSORT in the background in order to quickly find records that match the specified WHERE condition. If it is not possible to use DFSORT, all records are scanned sequentially for matches instead.

You can use an ORDER BY clause in a SELECT statement that retrieves data from a VSAM data set in order to influence the order in which rows are returned. DFSORT is used to sort records in the specified order before they are returned.

If no ORDER BY clause is specified, records are returned in the following order:

VSAM type	Order
KSDS	Ascending key order
RRDS	Relative entry number
ESDS	Relative byte address

VSAM type

Order

KSDS

Ascending key order

RRDS

Relative entry number

ESDS

Relative byte address

Modifying existing VSAM data sets

The UBS File Bridge Server allows you to modify existing VSAM data sets by executing INSERT, UPDATE or DELETE statements.

When inserting or updating records into a VSAM KSDS, the uniqueness of keys is enforced by VSAM. Attempting to insert a record with a key that already exists or to change the key of an existing record to a duplicate value results in SQL error -803. For RRDS and ESDS, VSAM does not enforce uniqueness for any set of columns.

Directory tables

The UBS File Bridge Server maintains a set of tables called the directory. The schema name for these tables is SYSUBS. You can access the information in these tables using SELECT statements. It is not possible to modify the information in these tables using INSERT, UDPATE or DELETE statements. Instead, you use DDL statements such as CREATE, ALTER and DROP to add, modify or remove information.

SYSUBS.SYSCOLUMNS table

The SYSUBS.SYSCOLUMNS table contains one row for each column of every table.

Column name Data type Description

Column name	Data type	Description
TABLE_SCHEMA	VARCHAR(128)	Schema of the table that contains the column.
TABLE_NAME	VARCHAR(128)	Name of the table that contains the column.
NAME	VARCHAR(128)	Name of the column.
POSITION	INTEGER	Location of the column inside the table’s column list.
DATA_TYPE	INTEGER	The internal ID of the column’s data type.
TYPE_NAME	VARCHAR(128)	The name of the column’s data type.
NULLABLE	CHAR(1)	Whether the column can contain `NULL` values. Contains either `Y` or `N`.
LENGTH	INTEGER	Length attribute of the column.
SCALE	INTEGER	If the column type is DECIMAL, the scale of the column. If the column type is TIMESTAMP, the number of fractional second digits. Otherwise 0.
KEY_SEQ	INTEGER	The position of the column within the list of key columns of the table. 0 if the column is not part of the table’s key.
PATH	VARCHAR(1024)	If the column belongs to a table in a VSAM tablespace, the path within the associated COBOL or PL/I data structure that identifies the field storing the column value. Otherwise `NULL`.
PATTERN	VARCHAR(64)	If the column type is DATE, TIME or TIMESTAMP, the format string that is used to read and write text representations of the value. Otherwise `NULL`.

TABLE_SCHEMA

VARCHAR(128)

Schema of the table that contains the column.

TABLE_NAME

VARCHAR(128)

Name of the table that contains the column.

NAME

VARCHAR(128)

Name of the column.

POSITION

INTEGER

Location of the column inside the table’s column list.

DATA_TYPE

INTEGER

The internal ID of the column’s data type.

TYPE_NAME

VARCHAR(128)

The name of the column’s data type.

NULLABLE

CHAR(1)

Whether the column can contain NULL values. Contains either Y or N.

LENGTH

INTEGER

Length attribute of the column.

SCALE

INTEGER

If the column type is DECIMAL, the scale of the column. If the column type is TIMESTAMP, the number of fractional second digits. Otherwise 0.

KEY_SEQ

INTEGER

The position of the column within the list of key columns of the table. 0 if the column is not part of the table’s key.

PATH

VARCHAR(1024)

If the column belongs to a table in a VSAM tablespace, the path within the associated COBOL or PL/I data structure that identifies the field storing the column value. Otherwise NULL.

PATTERN

VARCHAR(64)

If the column type is DATE, TIME or TIMESTAMP, the format string that is used to read and write text representations of the value. Otherwise NULL.

SYSUBS.SYSDUMMY1 table

The SYSUBS.SYSDUMMY1 table contains one row. It can be used as a table reference when the actual contents of the table do not matter.

Column name Data type Description

Column name	Data type	Description
DUMMY1	CHAR(1)	Always contains `Y`.

DUMMY1

CHAR(1)

Always contains Y.

SYSUBS.SYSSCHEMAS table

The SYSUBS.SYSSCHEMAS table contains one row for each schema that exists.

Column name	Data type	Description
NAME	VARCHAR(128)	The name of the schema.

Column name

Data type

Description

NAME

VARCHAR(128)

The name of the schema.

SYSUBS.SYSTABAUTH table

The SYSUBS.SYSTABAUTH table stores information about the privileges that users have on tables.

Column name Data type Description

Column name	Data type	Description
GRANTOR	VARCHAR(128)	User ID of the user who granted the privilege.
GRANTEE	VARCHAR(128)	User ID of the user who holds the privilege.
TABLE_SCHEMA	VARCHAR(128)	Schema of the table.
TABLE_NAME	VARCHAR(128)	Name of the table.
SELECT_AUTH	CHAR(1)	Whether the grantee can select rows from the table (either `Y` or `N`).
UPDATE_AUTH	CHAR(1)	Whether the grantee can update rows in the table (either `Y` or `N`).
INSERT_AUTH	CHAR(1)	Whether the grantee can insert rows into the table (either `Y` or `N`).
DELETE_AUTH	CHAR(1)	Whether the grantee can delete rows from the table (either `Y` or `N`).
GRANTEDTS	TIMESTAMP	Time when the privilege was granted.

GRANTOR

VARCHAR(128)

User ID of the user who granted the privilege.

GRANTEE

VARCHAR(128)

User ID of the user who holds the privilege.

TABLE_SCHEMA

VARCHAR(128)

Schema of the table.

TABLE_NAME

VARCHAR(128)

Name of the table.

SELECT_AUTH

CHAR(1)

Whether the grantee can select rows from the table (either Y or N).

UPDATE_AUTH

CHAR(1)

Whether the grantee can update rows in the table (either Y or N).

INSERT_AUTH

CHAR(1)

Whether the grantee can insert rows into the table (either Y or N).

DELETE_AUTH

CHAR(1)

Whether the grantee can delete rows from the table (either Y or N).

GRANTEDTS

TIMESTAMP

Time when the privilege was granted.

SYSUBS.SYSTABLES table

The SYSUBS.SYSTABLES table contains one row for each table.

Column name Data type Description

Column name	Data type	Description
TABLESPACE	VARCHAR(128)	The name of the tablespace that contains the table.
SCHEMA	VARCHAR(128)	The schema of the table.
NAME	VARCHAR(128)	The name of the table.
CREATEDTS	TIMESTAMP	Time when the table was originally created.
ALTEREDTS	TIMESTAMP	Time when the table was last altered.
CREATOR	VARCHAR(128)	User ID of the user who created the table.
WHEN_CONDITION	VARCHAR(1024)	If the table is in a VSAM tablespace, the condition that identifies whether or not a record from the VSAM data set belongs to this table. `NULL` if all records of the VSAM data set belong to this table. For all other tablespace types, this column is `NULL`.

TABLESPACE

VARCHAR(128)

The name of the tablespace that contains the table.

SCHEMA

VARCHAR(128)

The schema of the table.

NAME

VARCHAR(128)

The name of the table.

CREATEDTS

TIMESTAMP

Time when the table was originally created.

ALTEREDTS

TIMESTAMP

Time when the table was last altered.

CREATOR

VARCHAR(128)

User ID of the user who created the table.

WHEN_CONDITION

VARCHAR(1024)

If the table is in a VSAM tablespace, the condition that identifies whether or not a record from the VSAM data set belongs to this table. NULL if all records of the VSAM data set belong to this table. For all other tablespace types, this column is NULL.

SYSUBS.SYSTABLESPACE table

The SYSUBS.SYSTABLESPACE table contains one row for each tablespace.

Column name Data type Description

Column name	Data type	Description
NAME	VARCHAR(128)	The name of the tablespace.
CREATEDTS	TIMESTAMP	Time when the tablespace was originally created.
ALTEREDTS	TIMESTAMP	Time when the tablespace was last altered.
CREATOR	VARCHAR(128)	User ID of the user who created the tablespace.
FILE	VARCHAR(1024)	The name of the file or data set that is associated with the tablespace. For VSAM tablespaces, this is the name of the VSAM data set that contains the data (not the name of the data set that contains the COBOL or PL/I copy book).
ENCODING	VARCHAR(128)	The encoding of the tablespace (for example, `UTF-8` or `ISO8859-15`)
DFSORT	CHAR(1)	For VSAM tablespaces, whether DFSORT acceleration may be used when evaluating WHERE conditions or ORDER BY criteria. Can be `Y` or `N`. For all other tablespace types, this column is `NULL`.
DFSORT_THRESHOLD	INTEGER	For VSAM tablespaces, the minimum number of rows in the tablespace before DFSORT acceleration is used. For all other tablespace types, this column is `NULL`.
DFSORT_OPTIONS	VARCHAR(1024)	For VSAM tablespaces, additional options that are passed to the DFSORT program when DFSORT acceleration is used. For all other tablespace types, this column is NULL.
TYPE	VARCHAR(8)	The tablespace type. Can be `VSAM` or `CSV`.
READONLY	CHAR(1)	Whether the tablespace only allows read access. Can be `Y` or `N`.
COLDEL	CHAR(1)	If the tablespace is associated with a CSV file, the character used as column delimiter. For all other tablespace types, this column is `NULL`.
CHARDEL	CHAR(1)	If the tablespace is associated with a CSV file, the character used to enclose string fields. For all other tablespace types, this column is `NULL`.
DECPT	CHAR(1)	If the tablespace is associated with a CSV file, the character used as decimal delimiter. For all other tablespace types, this column is `NULL`.
CACHESIZE	INTEGER	If the tablespace is associated with a CSV file, the size of the cache (in bytes) that stores information about modified and deleted rows. For all other tablespace types, this column is `NULL`.
BOOLEANTRUE	VARCHAR(32)	If the tablespace is associated with a CSV file, the text representation of the boolean value true. For all other tablespace types, this column is `NULL`.
BOOLEANFALSE	VARCHAR(32)	If the tablespace is associated with a CSV file, the text representation of the boolean value false. For all other tablespace types, this column is `NULL`.
QUOTEMODE	VARCHAR(8)	If the tablespace is associated with a CSV file, which fields are enclosed in quotation characters. Can be `ALL`, `STRINGS`, `MINIMAL`, or `NONE`. For all other tablespace types, this column is `NULL`.
STRICTENCODING	CHAR(1)	If the tablespace is associated with a CSV file, specifies whether an SQL error is issued when a byte sequence is encountered that is not valid in the encoding of the tablespace. Can be `Y` or `N`. When set to `N`, any invalid byte sequence is replaced with a substitution character and no SQL error occurs. For all other tablespace types, this column is `NULL`.
HEADER	CHAR(1)	If the tablespace is associated with a CSV file, specifies whether the first line of the associated CSV file is to be interpreted as a header. Can be `Y` or `N`. When set to `Y`, the first line is treated as a header line. When set to `N`, the first line is treated as normal data. For all other tablespace types, this column is `NULL`.

NAME

VARCHAR(128)

The name of the tablespace.

CREATEDTS

TIMESTAMP

Time when the tablespace was originally created.

ALTEREDTS

TIMESTAMP

Time when the tablespace was last altered.

CREATOR

VARCHAR(128)

User ID of the user who created the tablespace.

FILE

VARCHAR(1024)

The name of the file or data set that is associated with the tablespace. For VSAM tablespaces, this is the name of the VSAM data set that contains the data (not the name of the data set that contains the COBOL or PL/I copy book).

ENCODING

VARCHAR(128)

The encoding of the tablespace (for example, UTF-8 or ISO8859-15)

DFSORT

CHAR(1)

For VSAM tablespaces, whether DFSORT acceleration may be used when evaluating WHERE conditions or ORDER BY criteria. Can be Y or N. For all other tablespace types, this column is NULL.

DFSORT_THRESHOLD

INTEGER

For VSAM tablespaces, the minimum number of rows in the tablespace before DFSORT acceleration is used. For all other tablespace types, this column is NULL.

DFSORT_OPTIONS

VARCHAR(1024)

For VSAM tablespaces, additional options that are passed to the DFSORT program when DFSORT acceleration is used. For all other tablespace types, this column is NULL.

TYPE

VARCHAR(8)

The tablespace type. Can be VSAM or CSV.

READONLY

CHAR(1)

Whether the tablespace only allows read access. Can be Y or N.

COLDEL

CHAR(1)

If the tablespace is associated with a CSV file, the character used as column delimiter. For all other tablespace types, this column is NULL.

CHARDEL

CHAR(1)

If the tablespace is associated with a CSV file, the character used to enclose string fields. For all other tablespace types, this column is NULL.

DECPT

CHAR(1)

If the tablespace is associated with a CSV file, the character used as decimal delimiter. For all other tablespace types, this column is NULL.

CACHESIZE

INTEGER

If the tablespace is associated with a CSV file, the size of the cache (in bytes) that stores information about modified and deleted rows. For all other tablespace types, this column is NULL.

BOOLEANTRUE

VARCHAR(32)

If the tablespace is associated with a CSV file, the text representation of the boolean value true. For all other tablespace types, this column is NULL.

BOOLEANFALSE

VARCHAR(32)

If the tablespace is associated with a CSV file, the text representation of the boolean value false. For all other tablespace types, this column is NULL.

QUOTEMODE

VARCHAR(8)

If the tablespace is associated with a CSV file, which fields are enclosed in quotation characters. Can be ALL, STRINGS, MINIMAL, or NONE. For all other tablespace types, this column is NULL.

STRICTENCODING

CHAR(1)

If the tablespace is associated with a CSV file, specifies whether an SQL error is issued when a byte sequence is encountered that is not valid in the encoding of the tablespace. Can be Y or N. When set to N, any invalid byte sequence is replaced with a substitution character and no SQL error occurs. For all other tablespace types, this column is NULL.

HEADER

CHAR(1)

If the tablespace is associated with a CSV file, specifies whether the first line of the associated CSV file is to be interpreted as a header. Can be Y or N. When set to Y, the first line is treated as a header line. When set to N, the first line is treated as normal data. For all other tablespace types, this column is NULL.

SYSUBS.SYSTABLESPACELAYOUT table

The SYSUBS.SYSTABLESPACELAYOUT table contains one row for each tablespace.

Column name	Data type	Description
TABLESPACE	VARCHAR(128)	The name of the tablespace.
CONTENT	XML	For internal use only. An XML representation of the internal structure of the tablespace.
TABLES	XML	For internal use only. An XML representation of all tables that are located in the tablespace.

Column name

Data type

Description

TABLESPACE

VARCHAR(128)

The name of the tablespace.

CONTENT

XML

For internal use only. An XML representation of the internal structure of the tablespace.

TABLES

XML

For internal use only. An XML representation of all tables that are located in the tablespace.

SYSUBS.SYSUSERAUTH table

The SYSUBS.SYSUSERAUTH table stores information about system privileges that users have.

Column name Data type Description

Column name	Data type	Description
GRANTOR	VARCHAR(128)	User ID of the user who granted the privilege.
GRANTEE	VARCHAR(128)	User ID of the user who holds the privilege.
DROP_TS_AUTH	CHAR(1)	Whether the grantee can drop existing tablespaces (either `Y` or `N`).
DROP_TB_AUTH	CHAR(1)	Whether the grantee can drop existing tables (either `Y` or `N`).
CREATE_TS_AUTH	CHAR(1)	Whether the grantee can create new tablespaces (either `Y` or `N`).
CREATE_TB_AUTH	CHAR(1)	Whether the grantee can create new tables (either `Y` or `N`).
SYSADM	CHAR(1)	Whether the grantee is a system administrator (either `Y` or `N`).
GRANTEDTS	TIMESTAMP	Time when the privilege was granted.

GRANTOR

VARCHAR(128)

User ID of the user who granted the privilege.

GRANTEE

VARCHAR(128)

User ID of the user who holds the privilege.

DROP_TS_AUTH

CHAR(1)

Whether the grantee can drop existing tablespaces (either Y or N).

DROP_TB_AUTH

CHAR(1)

Whether the grantee can drop existing tables (either Y or N).

CREATE_TS_AUTH

CHAR(1)

Whether the grantee can create new tablespaces (either Y or N).

CREATE_TB_AUTH

CHAR(1)

Whether the grantee can create new tables (either Y or N).

SYSADM

CHAR(1)

Whether the grantee is a system administrator (either Y or N).

GRANTEDTS

TIMESTAMP

Time when the privilege was granted.

Examples

Example 1

Assume that a file /jdbcserver/data/dept_manager.csv exists on a Linux / UNIX or Windows server. The file has the following contents:

110022,"d001",01/01/1985,10/01/1991
110039,"d001",10/01/1991,01/01/9999
110085,"d002",01/01/1985,12/17/1989
110114,"d002",12/17/1989,01/01/9999
110183,"d003",01/01/1985,03/21/1992
110228,"d003",03/21/1992,01/01/9999
110303,"d004",01/01/1985,09/09/1988
110344,"d004",09/09/1988,08/02/1992
110386,"d004",08/02/1992,08/30/1996
110420,"d004",08/30/1996,01/01/9999
110511,"d005",01/01/1985,04/25/1992
110567,"d005",04/25/1992,01/01/9999
110725,"d006",01/01/1985,05/06/1989
110765,"d006",05/06/1989,09/12/1991
110800,"d006",09/12/1991,06/28/1994
110854,"d006",06/28/1994,01/01/9999
111035,"d007",01/01/1985,03/07/1991
111133,"d007",03/07/1991,01/01/9999
111400,"d008",01/01/1985,04/08/1991
111534,"d008",04/08/1991,01/01/9999
111692,"d009",01/01/1985,10/17/1988
111784,"d009",10/17/1988,09/08/1992
111877,"d009",09/08/1992,01/03/1996
111939,"d009",01/03/1996,01/01/9999

This file is a CSV file in which each row has four columns. Column 1 is an integer column. Column 2 is a string column. Columns 3 and 4 are date columns. The values in string columns are enclosed in double quotes. Columns are separated with a comma.

To access this file through the File Bridge Server, perform the following steps:

Declare a tablespace that refers to the file using the following statement:
```
CREATE TABLESPACE DEPTMAN
FILE '/jdbcserver/data/dept_manager.csv' ENCODING 'ISO8859-15'
FORMAT CSV
COLDEL ','
CHARDEL '"'
QUOTE STRINGS
HEADER NO
```
This statement creates a tablespace called DEPTMAN. A tablespace is an entry in the File Bridge directory. It lets the server know that the file /data/dept_manager.csv is a CSV file encoded in ISO8859-15 (also known as Latin-9). The parameters COLDEL and CHARDEL specify the column delimiter and string delimiter, respectively. QUOTE STRINGS indicates that only values in string columns are enclosed by the character specified as CHARDEL. Since the first line of the file contains actual data and not column names, HEADER NO is specified.
Declare a table inside that tablespace:
```
CREATE TABLE PRODUCTION.DEPT_MANAGER (
    "EMP_NO"      INTEGER,
    "DEPT_NO"     CHAR(4),
    "FROM_DATE"   DATE PATTERN 'MM/dd/yyyy',
    "TO_DATE"     DATE PATTERN 'MM/dd/yyyy'
) IN DEPTMAN
```
This statement tells the server how to interpret each individual row in the file that is associated with the tablespace DEPTMAN. The schema and name of the table can be chosen freely. The column list has four entries, therefore the server expects each row in the file to consist of four columns. Rows that have any other number of columns cause errors when accessing the file (except for empty rows, which are always ignored).

You can now access the data in the CSV file as if it were a table in a relational database system. For example, to retrieve all data in the CSV file as a JDBC result set, use the following statement:

SELECT * FROM PRODUCTION.DEPT_MANAGER

To retrieve only those rows where the column DEPT_NO contains the value d007, use the following statement:

SELECT * FROM PRODUCTION.DEPT_MANAGER WHERE DEPT_NO = 'd007'

Example 2

Assume that a VSAM KSDS with the name PROD.INVNTORY.ITEMS exists on a z/OS system that is described by the following COBOL copy book:

01 entry.
  05  no          pic S9(5).
  05  description pic X(30).
  05  price       pic 9(5)V(2).
  05  binary-data pic X(30).

The copy book is stored in the sequential data set PROD.INVNTORY.ITEMS.CB.

To access this file through the File Bridge Server, perform the following steps:

Declare a tablespace that refers to the file using the following statement:
```
CREATE TABLESPACE ITEMS
FILE 'PROD.INVNTORY.ITEMS' ENCODING 'CP1047'
DESCRIPTION IN FILE 'PROD.INVNTORY.ITEMS.CB'
LANGUAGE COBOL
```
This statement creates a tablespace called ITEMS. It lets the server know that the data set PROD.INVNTORY.ITEMS is a VSAM KSDS encoded in EBCDIC CCSID 1047. Information about the format of each row is stored in the COBOL copy book under PROD.INVNTORY.ITEMS.CB.
Declare a table inside that tablespace:
```
CREATE TABLE PRODUCTION.ITEMS (
  "NO"             INTEGER       USES 'entry'.'no',
  "DESCRIPTION"    CHAR(30)      USES 'entry'.'description',
  "PRICE"          DECIMAL(5, 2) USES 'entry'.'price',
  "BINDAT"         BINARY(30)    USES 'entry'.'binary-data'
) IN ITEMS
```
This statement tells the server how to map the fields of the COBOL copy book to virtual table columns. The schema and name of the table can be chosen freely. You can now access the data in the VSAM KSDS as if it were a table in a relational database system.