File Bridge Server SQL Statements

The following document describes the valid SQL statements for the file bridge and the syntax of these statements. These statements can be executed using an SQL client that is connected to a server instance using the JDBC interface.

General information

Identifiers

Identifiers, such as table names or column names, can be specified with or without enclosing quotation marks ("). Quotation marks are mandatory if the identifier contains spaces, or if the identifier is equal to a reserved keyword. If the identifier itself contains quotation marks, then the enclosing quotation marks are mandatory and each quotation mark inside the identifier must be doubled.

Examples of valid identifiers:

ORDERS
LAST_NAME
"TABLE"
"DATE OF BIRTH"
"Contents of field ""COL1"""

String literals

String literals must always be specified with enclosing apostrophes. If the string literal itself contains apostrophes, then each apostrophe inside the string literal must be doubled.

Examples of valid string literals:

'Smith'
'John Doe'
'Patrick O''Brien'

Numeric literals

Numeric values must be specified as a sequence of digits from 0-9. Negative numbers must be prefixed by a minus symbol (-). Positive numbers can optionally be prefixed by a plus symbol (+). For fractional numbers, the decimal delimiter is always the period symbol (.). Fractional numbers between 0 and 1 can be specified with or without the leading zero, i.e. the values 0.5 and .5 are equivalent.

Hexadecimal literals

Hexadecimal literals must be specified in the format X’nn…', where n represents a character between 0-9 or A-F. The values A-F can be specified as uppercase or lowercase characters. The number of characters inside quotation marks must be an even number. At least two characters must be specified.

ALTER TABLE

The ALTER TABLE statement changes the description of a table on the current server.

Authorization required

SYSADM

Syntax

>>──ALTER TABLE──table─name─────────────────────────────────────────────>

>────┬──ALTER──┬──────────┬──column─name──┤set─data─type─spec├──┬──────><
     │         └──COLUMN──┘                                     │
     └──SET──┤when─condition├───────────────────────────────────┘

set-data-type-spec

>────SET DATA TYPE──┤type─spec├─────────────────────────────────────────>

Description

table-name: Specifies the table to be altered in the form <schema>.<name>.
ALTER COLUMN: Specifies that an existing column of the table should be changed. The column-name identifies the column that should be changed. The set-data-type-spec clause specifies the new data type that is assigned to the respective column. For details refer to type-spec in the CREATE TABLE statement.
SET when-condition: Specifies which records of the underlying file are assigned to this table. Only records that match the specified criteria are treated as records of the respective table. For the syntax of the when-condition refer to the when-condition in the CREATE TABLE statement.

CREATE TABLE

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its table space, or a condition that is used to assign specific records of the underlying file to this table.

Authorization required

SYSADM, or
CREATETB

Syntax

                                  ┌──,─────────────────────────┐
                                  V                            │
>>───CREATE TABLE──table─name──(────┬──┤column─definition├──┬──┴──)─────>
                                    └──┤group├──────────────┘

>────IN──┬──tablespace─name───┬─────────────────────────────────────────>
         └──FILE file─name────┘

>─────┬────────────────────┬───────────────────────────────────────────><
      └──┤when─condition├──┘

column-definition

>>───column─name──┤type─spec├──┬────────────┬───────────────────────────>
                               └──NOT NULL──┘

>──┬────────────────────────────────────────────────────────────────┬───>
   └──┬──USE───┬──┬─────────'item'────────┬────┬─────────────────┬──┘
      └──USES──┘  └────REMAINING_BYTES────┘    └──IF expression──┘

type-spec

>────┬──────────────────────────────────────────────┬────────────────────>
     ├──CHAR─────(──length──)───────────────────────┤
     ├──VARCHAR──(──length──)───────────────────────┤
     ├──INTEGER─────────────────────────────────────┤
     ├──SMALLINT────────────────────────────────────┤
     ├──BIGINT──────────────────────────────────────┤
     ├──FLOAT───────────────────────────────────────┤
     ├──DOUBLE──────────────────────────────────────┤
     ├──BINARY───(──length──)───────────────────────┤
     ├──DECIMAL──(──precision──┬─────────────┬──)───┤
     │                         └──,── scale──┘      │
     ├──DATE───────┬───────────────────────────┬────┤
     │             └──PATTERN──pattern─string──┘    │
     ├──TIME───────┬───────────────────────────┬────┤
     │             └──PATTERN──pattern─string──┘    │
     └──TIMESTAMP──┬───────────────────────────┬────┘
                   └──PATTERN──pattern─string──┘

group

>>───GROUP──┬────────┬──group-name──┬──USE───┬──'item'──────────────────>
            └──NAME──┘              └──USES──┘

        ┌──,───────────────────────────┐
        V                              │
>────(─────┬──┤column─definition├──┬───┴─)─────────────────────────────>
           └──┤group├──────────────┘

when-condition

>>───WHEN──┤disjunction├──────────────────────────────────────────────>

Description

table-name: Specifies the identifier for the table to be created in the form <schema>.<name>.
column-definition: Defines a column in the table.
column-name: Specifies the name of the column.
type-spec: Specifies the column’s data type. A length must be specified for character data types. The length specifies the maximum number characters for a cell of the column. For decimal data types the precision must be specified, where as the scale is optional. The precision specifies the total number of significant digits and the scale specifies the number of digits to the right of the decimal point.
NOT NULL: Specifies that the column can not contain null values.
USE items: Specifies a path within the underlying data structure to identify the fields that store the data for this column. For example this could be a field in a COBOL record.

The items in the path must be enclosed in apostrophes and separated by a dot. For example: 'a'.'b'.'c'
REMAINING_BYTES: Use all bytes in the current record from the current position till the end of record for the given column. The columns data type must be defined as BINARY(*).
IF expression: Specifies an expression that is evaluated for each record read from the underlying sequential or VSAM file. The expression equates to true if the respective column value is present in that record. If the expression evaluates to false the DBMS system returns a null value for this column cell.

This expression is typically used for multiple occurrences of a field within a record, such as with the COBOL OCCURS DEPENDING clause.
GROUP: Defines a set of columns to reflect the hierarchy of a data structure in a programming language such as COBOL.

group-name specifies the name of the group.
USE items: Specifies a path within the underlying data structure to identify the group.
IN tablespace-name: Identifies the tablespace in which the table is to be created. If not all records of the underlying sequential or VSAM file should be assigned to this table a condition must be specified in form of a when-condition. If you create multiple tables in a tablespace a when-condition must be specified for each table of the tablespace, otherwise an error is issued. The DBMS engine uses this condition to determine which records belong to each table. The condition must be exclusive (i.e. each record can only belong to one table).
FILE file-name: Specifies a sequential or VSAM file that contains the data for the table. If the file-name is specified, the DBMS engine will create an implicit tablespace for this table.
WHEN: Specifies a condition that controls whether or not a particular record of the underlying file belongs to this table. This condition is used if the underlying file contains different record types and the table covers only one specific type. In such a case the condition must match only these records of the specific record type. For details on the disjunction syntax refer to the definition in the where-clause of the SELECT statement.

CREATE TABLESPACE

The CREATE TABLESPACE statement defines a tablespace to the file bridge server. A table space represents a sequential file or VSAM file on a z/OS system, or a regular file on a Linux / UNIX or Windows system.

Authorization required

SYSADM, or
CREATETS

Syntax

>>───CREATE──┬────────────┬──TABLESPACE──tablespace─name─────────────────>
             └──READONLY──┘

>────FILE──file─name──┬───────────────────────────────────────┬──────────>
                      └──ENCODING──encoding─name──┬────────┬──┘
                                                  └─STRICT─┘

>────┬──┤vsam─file─information├─────────────┬─────────────────────────><
     └──FORMAT CSV──┤csv─file─information├──┘

vsam-file-information

>────DESCRIPTION IN FILE──description─file───────────────────────────────>

>────LANGUAGE──┬─COBOL─┬─────────────────────────────────────┬──┬────────>
               │       └──(──REPLACING──text1──BY──text2──)──┘  │
               └─┬──PL/I──┬─────────────────────────────────────┘
                 └──PL/1──┘

>──┬───────────────────────────────────────────────────────────────────┬─><
   └──USE DFSORT ACCESS─┬────────────────────────┬──┬────────────────┬─┘
                        └──MIN THRESHOLD number──┘  └──(──options──)─┘

csv-file-information

   ┌──────────────────────────────────────────────────────────────┐
   v                                                              │
>────┬──COLDEL─────────column─delimiter────────────────────────┬──┴───────><
     ├──CHARDEL────────string─delimiter────────────────────────┤
     ├──DECPT──────────decimal─delimiter───────────────────────┤
     ├──BOOLVALUES─────(──true─value──,──false─value──)────────┤
     ├──HEADER─────────┬─YES─┬─────────────────────────────────┤
     │                 └─NO──┘                                 │
     ├──QUOTE──────────┬──ALL──────┬───────────────────────────┤
     │                 ├──STRINGS──┤                           │
     │                 ├──MINIMAL──┤                           │
     │                 └──NONE─────┘                           │
     └──CACHESIZE──number──┬──────┬────────────────────────────┘
                           ├──KB──┤
                           └──MB──┘

Description

READONLY: Specifies that the tablespace is declared as read-only. As a result, only SELECT statements can be executed against the tables in the tablespace. Attempting to execute an INSERT, UPDATE or DELETE statement against any of the tables in the tablespace results in an SQL error.
tablespace-name: Specifies the identifier for the tablespace to be created. The tablespace name must start with a letter (A-Z) and must not include special characters. The length of the name must be from 1 to 8 characters.
FILE file-name: Specifies the underlying sequential or VSAM file. The file name is that of the data set that is accessed each time a data operation is performed on the tablespace.
ENCODING encoding-name: Specifies the string encoding in the associated file. You may either specify the encoding name, or a numeric CCSID in the format IBMx where x is the CCSID. Note that x must have at least three digits. CCSIDs that have less than three digits must be left-padded with zeroes. Typical values for encoding-name are: UTF-8, UTF-16LE, ISO8859-15, Windows-1252, IBM037, IBM1047. If this parameter is not specified, the system default encoding is assumed.
STRICT: Specifies that an SQL error is issued if, while processing the underlying data file, bytes are encountered that cannot be interpreted using the specified encoding. If STRICT is not specified, bytes that cannot be interpreted will be replaced with the Unicode substitution character.

Options for VSAM data sets on z/OS

The following options are only valid if the specified file name refers to a VSAM data set that is located on a z/OS server.

DESCRIPTION IN FILE description-file

Specifies the name of a data set that contains the record description. The description file can be a sequential file or a member of a partitioned data set. The record description can be a COBOL or PL/1 data structure. The record description is necessary in order to read and update records in the underlying file.

LANGUAGE

Specifies the programming language that is used in the description file.

COBOL: Specifies that the description file is a COBOL copybook. The REPLACING clause can be used to replace parts of the source code, such as placeholders like '$'. The REPLACING clause replaces all occurrences of text-1 with text-2.
PL/I │ PL/1: Specifies that the description file contains a PL/I structure declaration.

USE DFSORT ACCESS

specifies that DFSORT is to be used to evaluate expressions when reading the underlying file. Such expressions are specified when accessing the file using the SELECT, UPDATE, or DELETE statements.

If the clause is not specified the DBMS engine will evaluate all expressions of a where-condition itself. This is an efficiency consideration. Since DFSORT is optimized to evaluate expressions the use of this clause is recommended. However, the file bridge server might be running on a system where DFSORT is not available, in which case this clause should not be specified.

MIN THRESHOLD number: Specifies a minimum threshold for the number of records which must be contained in the underlying file in order for the DBMS engine to use DFSORT access. If the actual number of records is less then the threshold the DBMS engine will access the file without DFSORT. The number should be positive. A value of -1 has the same effect as if no threshold is specified.
options: can be used to specify DFSORT options. Please refer to the DFSORT user guide for a list of valid options.

Options for CSV files

The following options are only valid if the specified file name refers to a file that contains CSV data (comma-separated values). The file may be a non-VSAM data set that is located on a z/OS server, a Unix System Services file on a z/OS server, or a normal file that is located on a Unix, Linux, or Windows server.

A tablespace that is backed by a CSV file can only contain one table.

On z/OS, sequential data sets and members with CSV data can only be accessed in read-only mode, regardless of whether or not the READONLY keyword was specified in the CREATE TABLESPACE statement.

FORMAT CSV: Specifies that the file that is associated with this tablespace is a CSV (comma-separated values) file.
COLDEL: Specifies the character that is used to separate the columns in each record. The default value is the comma (,). You can specify the desired column delimiter character as a single character constant, or as a hexadecimal constant.
CHARDEL: Specifies the character that is used to enclose the column values. The default value is the double quote character ("). You can specify the desired enclosing character either as a single character constant, or as a hexadecimal constant. If a field contains the string delimiter, then it must appear twice (e.g. "This field contains the "" delimiter").
DECPT: Specifies the decimal delimiter. The default value is the period character (.). You can specify the desired decimal delimiter either as a single character constant, or as a hexadecimal constant.

When specifying hexadecimal constants for either of the parameters COLDEL, CHARDEL or DECPT, the format must be X’nn', where n is a hexadecimal digit (0-9 or A-F), and must identify a Unicode code point, regardless of the actual encoding of the tablespace.

HEADER

Specifies whether the first line in the CSV file contains a header. Possible values are:

YES: Specifies that the first line in the associated CSV file does not contain actual data, but is to be treated as a header. This is the default.
NO: Specifies that the first line in the associated CSV file contains actual data and is to be treated identically to all the other lines in the CSV file.

BOOLVALUES (true-value, false-value)

Specifies how boolean values are represented in the associated CSV file. The values should be specified as character strings enclosed in single quotes (e.g. 'YES', 'NO'). The default for true-value is '1' and the default for false-value is '0'.

QUOTE

Specifies which fields are quoted using the specified string delimiter CHARDEL.

Possible values are:

ALL: Specifies that all fields are enclosed in string delimiters.
STRINGS: Specifies that all CHAR and VARCHAR fields are enclosed in string delimiters. This is the default.
MINIMAL: Specifies that CHAR and VARCHAR fields are only enclosed in string delimiters if they contain the column delimiter character.
NONE: Specifies that no fields are enclosed in string delimiters.

CACHESIZE

Specifies the amount of memory that is used to buffer delete and update operations that are performed against the CSV file. Larger cache sizes increase the performance of delete and update operations, but require more memory when modifying existing CSV files. The cache size is specified in bytes. You can use the KB or MB modifier to specify the size in kilobytes or megabytes respectively.

Example

The following example creates a tablespace that is backed by a CSV file. The file is encoded in UTF-8. All fields, regardless of the data type, are enclosed in apostrophes.

  CREATE TABLESPACE TS1
    FILE 'C:\Data\Customers.csv'
    ENCODING 'UTF-8' STRICT
    FORMAT CSV
    CHARDEL '"'
    BOOLVALUES ('1', '0')
    QUOTE ALL

DELETE

The DELETE statement deletes rows from a table and consequently also from the underlying data file.

Authorization required

SYSADM, or
DELETE

Syntax

>>───DELETE FROM table─name──┬──────────────────┬─────────────────────><
                             └──┤where─clause├──┘

Description

table-name: Specifies the table from which rows are to be deleted in the form <schema>.<name>.
where-clause: specifies a condition for deleting a subset of rows. Only the rows that match the condition are deleted. For more details on the where-clause refer to where-clause in the SELECT statement.

Limitations

The DELETE statement cannot be used if the underlying file is a sequential data set under z/OS. This is a limitation of the z/OS operating system. Only VSAM files provide the functionality to delete specific records.

DROP TABLE

The DROP TABLE statement drops an existing table.

Authorization required

SYSADM, or
DROPTB

Syntax

>>───DROP TABLE────table─name──────────────────────────────────────────><

Description

table-name: Specifies the table to be dropped in the form <schema>.<name>.

The underlying sequential or VSAM file is not affected by the DROP TABLE command.

DROP TABLESPACE

Drops an existing tablespace. All tables within the tablespace will also be dropped.

If the tablespace is associated with a CSV file, the CSV file will not be deleted unless the keyword PURGE is specified. If the associated tablespace is a VSAM tablespace, the associated data set is never deleted.

Authorization required

SYSADM, or
DROPTS

Syntax

>>───DROP TABLESPACE──┬──tablespace─name──┬──┬─────────┬───────────────><
                      └──file─name ───────┘  └──PURGE──┘

Description

tablespace-name: Specifies the tablespace to be dropped.
file-name: Specifies the underlying file containing the data for the tablespace being dropped. Note that the file itself is not deleted.
PURGE: Specifies that the underlying CSV file is to be deleted. This option can only be specified for CSV tablespaces.

GRANT TABLE

The GRANT TABLE statement controls the access to table data.

Authorization required

SYSADM

Syntax

           ┌─,───────────────────┐
           V                     │
>>──GRANT───┬──ALL PRIVILEGES──┬─┴─ON table─name──TO──┬──user─name──┬──><
            ├──SELECT──────────┤                      └──PUBLIC─────┘
            ├──UPDATE──────────┤
            ├──DELETE──────────┤
            └──INSERT──────────┘

Description

ALL PRIVILEGES: Includes all privileges that can be granted on a table. In particular this includes select, update, insert, and delete privileges.
SELECT: Specifies that the user is allowed to select data from the table.
UPDATE: Specifies that the user is allowed to update data in the table.
DELETE: Specifies that the user is allowed to delete one or more rows of the table.
INSERT: Specifies that the user is allowed to insert new rows into the table.
ON table-name: Specifies the table on which the privilege is granted in the form <schema>.<name>.
TO user-name: Specifies the user name to which the privilege is granted.
TO PUBLIC: grants the specified privileges to all users. This includes users who are added at a later stage.

Examples

The following example demonstrates how to assign all privileges on table UBSH.EMP to user HUGO:
```
  GRANT ALL PRIVILEGES
     ON UBSH.EMP
     TO HUGO
```
The following example shows a GRANT statement that assigns the SELECT and INSERT privilege to user DAN on table UBSH.PROJ
```
  GRANT SELECT, INSERT
     ON UBSH.PROJ
     TO DAN
```

GRANT SYSTEM

The GRANT statement grants system privileges to the specified users.

Authorization required

SYSADM

Syntax

            ┌─,───────────────┐
            V                 │
>>───GRANT─────┬──SYSADM────┬─┴─TO──┬──user─name──┬────────────────────><
               ├──CREATETS──┤       └──PUBLIC─────┘
               ├──CREATETB──┤
               ├──DROPTS────┤
               └──DROPTB────┘

Description

SYSADM: Specifies that the user has system administrator privileges on the current server. This includes the CREATETS, CREATETB, DROPTS and DROPTB privileges. Additionally the user has the table privileges INSERT, UPDATE, DELETE and SELECT. A system administrator is also allowed to grant privileges to other users.
CREATETS: Specifies that the user is allowed to create new tablespaces.
CREATETB: Specifies that the user is allowed to create new tables.
DROPTS: Specifies that the user is allowed to drop tablespaces.
DROPTB: Specifies that the user is allowed to drop tables.
TO user-name: Specifies the user name that receives the specified system privileges.
TO PUBLIC: Grants the specified system privileges to all users. This includes users who are added at a later stage.

Examples

The following example shows a GRANT statement that grants system administrator privileges to the user HUGO. After the execution of this statement the user HUGO will be allowed to perform all valid actions.
```
  GRANT SYSADM
     TO HUGO
```
The following statement grants the permission to create new tablespaces and tables to the user DAN.
```
  GRANT CREATETB, CREATETS
     TO DAN
```

INSERT

The INSERT statement inserts a new row into a specified table.

Authorization required

SYSADM, or
INSERT

Syntax

>>──INSERT INTO table─name────┬────────────────────────────┬────────────>
                              │     ┌─,──────────────┐     │
                              │     V                │     │
                              └──(─────column─name───┴──)──┘
              ┌─,──────┐
              V        │
>──VALUES──(────┤sum├──┴──)────────────────────────────────────────────><

Description

table-name: Specifies the table into which a new row should be inserted in the form <schema>.<name>.
column-name: Specifies a comma separated list of columns for which values will be provided by the VALUES clause. Each entry of this list corresponds to the respective entry of the VALUES list. The number of provided entries must match with the number of entries of the VALUES list. If a column is omitted the DBMS will insert a null value for that column.
sum: This is an expression which provides a value for a cell in the row to be inserted into the table. If a column list has been specified, then a value for each specified column must be provided. If the column-name list has been omitted, then a valid value must be specified for every column in the table, and the order of the values should correspond to the order in the column definition (see CREATE TABLE statement). Refer to the syntax definition and the associated sum description in the SELECT statement for more details.

REVOKE TABLE

The REVOKE TABLE statement revokes privileges on tables.

Authorization required

SYSADM

Syntax

           ┌─,───────────────────┐
           V                     │
>>──REVOKE──┬──ALL PRIVILEGES──┬─┴─ON table─name──FROM─┬──user─name──┬─><
            ├──SELECT ─────────┤                       └──PUBLIC─────┘
            ├──UPDATE ─────────┤
            ├──DELETE ─────────┤
            └──INSERT ─────────┘

Description

ALL PRIVILEGES: Specifies that all privileges will be revoked for the specified user on the specified table.
SELECT: Revokes the privilege to select data from the table.
UPDATE: Revokes the privilege to update rows in the table.
DELETE: Revokes the privilege to delete rows from the table.
INSERT: Revokes the privilege to insert new rows into the table.
table-name: Specifies the table for which the privilege should be removed in the form <schema>.<name>.
user-name: Specifies the user for whom the privilege is to be revoked.
PUBLIC: Revokes the specified privileges for PUBLIC access on the specified table.

Example

Remove the permission SELECT for user DAN on table UBSH.EMP:

  REVOKE SELECT
     ON TABLE UBSH.EMP
     FROM DAN

REVOKE SYSTEM

The REVOKE SYSTEM statement revokes system privileges.

Authorization required

SYSADM

Syntax

             ┌─,───────────────┐
             V                 │
>>───REVOKE─────┬──SYSADM────┬─┴─FROM──┬──user─name──┬─────────────────><
                ├──CREATETS──┤         └──PUBLIC─────┘
                ├──CREATETB──┤
                ├──DROPTS────┤
                └──DROPTB────┘

Description

SYSADM: Revokes the SYSADM privilege.
CREATETS: Revokes the privilege to create new tablespaces.
CREATETB: Revokes the privilege to create new tables.
DROPTS: Revokes the privilege to drop tablespaces.
DROPTB: Revokes the privilege to drop tables.
user-name: Revokes the privilege for the specified user.
PUBLIC: Revokes the privilege for public access.

Examples

The following example demonstrates how to remove the SYSADM privilege from user HUGO:

  REVOKE SYSADM
     FROM HUGO

SELECT

The SELECT statement selects the specified rows and columns of a table.

Authorization required

SYSADM, or
SELECT

Syntax

             ┌──ALL───────┐
>>───SELECT──┼────────────┼──┬──*─────────────────────────┬─────────────>
             └──DISTINCT──┘  │                            │
                             │  ┌─,────────────────────┐  │
                             │  V                      │  │
                             └─────┤column─reference├──┴──┘

>────FROM──table─name──┬──────────────────┬─────────────────────────────>
                       └──┤where─clause├──┘

>────┬─────────────────────┬─────┬─────────────────────┬────────────────>
     └──┤group─by─clause├──┘     └──┤order─by─clause├──┘

>────┬────────────────────────┬────────────────────────────────────────><
     └──┤fetch─first─clause├──┘

column─reference

>───┤sum├──┬─────────┬─────────────────────────────────────────────────><
           └──label──┘

sum

>────┤product├──┬────────────────────┬─────────────────────────────────><
                └──┬── + ──┬──┤sum├──┘
                   └── ─ ──┘

product

>────┤atom├──┬─────────────────────────┬───────────────────────────────><
             └──┬── * ──┬───┤product├──┘
                └── / ──┘

atom

>───┬──┬───────┬──┬──(──┤sum├──)──┬───┬────────────────────────────────><
    │  └── ─ ──┘  └─────column────┘   │
    ├── ? ────────────────────────────┤
    ├──┤constant├─────────────────────┤
    ├──┤case─statement├───────────────┤
    └──┤column─function├──────────────┘

constant

>───┬──NULL───────────────┬────────────────────────────────────────────><
    ├──'string─literal'───┤
    ├──X'hex─literal'─────┤
    ├──B'binary─literal'──┤
    └──number─────────────┘

case-statement

                                ┌──,─────────────────────────┐
                                V                            │
>───CASE──┬──┤column─reference├────WHEN──┤sum├──THEN──┤sum├──┴──┬───────>
          │   ┌─,─────────────────────────────────┐             │
          │   V                                   │             │
          └─────WHEN──┤disjunction├──THEN──┤sum├──┴─────────────┘

>────┬─────────────────┬──END──────────────────────────────────────────><
     └──ELSE──┤sum├────┘

where-clause

>───WHERE──┤disjunction├───────────────────────────────────────────────><

group-by-clause

              ┌─,──────────────┐
              V                │
>───GROUP BY─────┤identifier├──┴───────────────────────────────────────><

order-by-clause

              ┌─,──────────────┐
              V                │  ┌──ASC───┐
>───ORDER BY─────┤identifier├──┴──┼────────┼───────────────────────────><
                                  └──DESC──┘

fetch-first-clause

>───FETCH FIRST ──┬────────────┬──┬── ROW ONLY ───┬────────────────────><
                  └──┤number├──┘  └── ROWS ONLY ──┘

disjunction

>───┤conjunction├──┬───────────────────────────┬───────────────────────><
                   └──(──OR──┤disjunction├──)──┘

conjunction

>───┤bool├──┬────────────────────────────┬─────────────────────────────><
            └──(──AND──┤conjunction├──)──┘

bool

>───┬──NOT──┤bool├──────────┬──────────────────────────────────────────><
    ├──┤equation├───────────┤
    └──(──┤disjunction├──)──┘

equation

>───┤sum├──┬──┤operator├───┤sum├───────────────────┬───────────────────><
           ├──IS──┬───────┬──NULL──────────────────┤
           │      └──NOT──┘                        │
           │                   ┌─,───────────┐     │
           │                   V             │     │
           ├──┬───────┬──IN──(───┤constant├──┴──)──┤
           │  └──NOT──┘                            │
           └──┬───────┬──LIKE──'string─literal'────┘
              └──NOT──┘

operator

>>──┬── = ───┬─────────────────────────────────────────────────────────><
    ├── <> ──┤
    ├── != ──┤
    ├── >= ──┤
    ├── <= ──┤
    ├── > ───┤
    └── < ───┘

Description

ALL: Specifies that all rows from the table should be retrieved. This is the default.
DISTINCT: Specifies that duplicate rows should be removed from the result set before it is returned.

DISTINCT cannot be used in SELECT statements that refer to tables in a CSV tablespace.

column-reference

Names the columns, or specifies the expressions that should be returned by the SELECT statement.

sum: Defines an expression which resolves to a value which can be inserted into a single cell in a table.
label: Specifies the name of the returned column. The label can be used if the returned column should be named differently. If an expression has been specified you can name the column as well.

If the label is not specified the File Bridge Server will return the column name, or if an expression has been specified it will generated an according column name for the result set of the SELECT statement.

table-name

Specifies the table from which the data should be selected in the form <schema>.<name>.

WHERE

Specifies that only rows matching the given condition should be returned.

ORDER BY

Specifies that the rows of the result set should be sorted according to the specified criteria.

ORDER BY is ignored in SELECT statements that refer to tables in a CSV tablespace.

fetch-first-clause: Limits the number of rows that are returned in the result set. If number is not specified, the default is 1. The keywords ROW and ROWS are synonymous. Using a fetch-first-clause can improve the performance when selecting rows based on a unique column, particularly when the table is located in a CSV tablespace.

Example

The following example selects all records where the TYPE is E and the NAME of the employee starts with Bob. The SELECT statement is expected on table UBSH.EMP and returns only the columns NAME and ADDRESS. For the LASTNAME column we only select the first character using the SUBSTR function.

  SELECT
     NAME,
     ADDRESS,
     SUBSTR(LASTNAME, 1, 1)
  FROM UBSH.EMP
  WHERE
     TYPE = 'E' AND
     NAME LIKE 'Bob%'

UPDATE

The UPDATE statement updates the values of specified columns in rows of a table.

Authorization required

The user needs one of the following privileges to execute this statement:

SYSADM
UPDATE

Syntax

                             ┌─,──────────────────┐
                             V                    │
>>───UPDATE table─name──SET─────column = ──┤sum├──┴─────────────────────>

>───┬──────────────────┬───────────────────────────────────────────────><
    └──┤where─clause├──┘

Description

table-name: Specifies the table in which rows should be updated in the form <schema>.<name>.
column: Names the column that should be updated.
sum: Specifies the new value for the column. If multiple columns should be updated the terms column = sum must be coma separated. Refer to the syntax definition and the associated sum description in the SELECT statement for more details.
where-clause: Specifies a WHERE clause to update only specific rows. Only the rows that match the where-clause criteria will be updated. For more details refer to the where-clause definition in the SELECT statement.

Limitations

The UPDATE statement has the following limitations. The UPDATE statement cannot be used if:

The underlying file of the table/tablespace is a sequential data set in z/OS. The UPDATE statement cannot update records at any position within a such a data set. This is a limitation of the z/OS operating system. For z/OS only VSAM files provide the necessary functionality to update specific rows.
The UPDATE statement cannot be used if the tablespace containing the table has been defined with the READONLY option.

Examples

The following example updates all rows that have a TYPE of E. It doubles the current value of the SIZE column of the table UBSH.EMP. After this statement is executed all rows in the underlying file of the table that have a TYPE of E will have been updated.

 UPDATE UBSH.EMP
     SET SIZE = SIZE * 2
     WHERE TYPE = 'E'

Built-in functions

The following chapter describes the available column functions that are supported by the File Bridge Server. The column functions can be used as described in the syntax rule column-reference.

BITAND

>>──BITAND──(──┤expression1├──,──┤expression2├──)──────────────────────><

Performs a bit-wise AND operation. The input arguments must be integer values or expressions that resolve to integer values. The two’s complement representation is used for the calculation.

BITNOT

>>──BITNOT──(──┤expression├──)─────────────────────────────────────────><

Performs a bit-wise NOT operation. The input argument must be an integer value or an expressions that resolves to an integer values. The two’s complement representation is used for the calculation.

BITOR

>>──BITOR──(──┤expression1├──,──┤expression2├──)───────────────────────><

Performs a bit-wise OR operation. The input arguments must be integer values or expressions that resolve to integer values. The two’s complement representation is used for the calculation.

BITXOR

>>──BITXOR──(──┤expression1├──,──┤expression2├──)──────────────────────><

Performs a bit-wise exclusive OR operation. The input arguments must be integer values or expressions that resolve to integer values. The two’s complement representation is used for the calculation.

CONCAT

>>──CONCAT──(──┤expression1├──,──┤expression2├──)──────────────────────><

Combines two string arguments, resulting in a string that consists of the first string, followed by the second string.

GETVARIABLE

>>──GETVARIABLE─(─┤string-constant├──┬───────────────────┬───────────)─><
                                     └─,─┤default-value├─┘

Returns the value of a server variable. The first input argument is the name of the variable and must be a string constant. The second argument is optional and may be a string constant, an SQL expression, or the null value.

When no server variable with the specified name exists, the value of the second argument is returned. If no second argument is specified, SQL error -171 is issued instead.

The following server variables are available:

Variable name Description

Variable name	Description
`SERVER_BUILD_DATE`	The build date of the File Bridge server in the format `yyyyMMdd`.
`SERVER_DEFAULT_ENCODING`	The name of the default string encoding that the File Bridge server uses.
`SERVER_WORKING_DIRECTORY`	The File Bridge server’s working directory. When creating a tablespace that is associated with a file name that is not an absolute path, the path is considered to be relative to this directory. Under Linux / Unix or z/OS Unix System Services, an absolute path is a path that starts with a forward slash character. Under Windows, an absolute path is a path that starts with a drive letter. The variable name `SERVER_WORKING_DIRECTORY` can be abbreviated as `SERVER_WORKING_DIR`.
`SERVER_OPERATING_SYSTEM`	The name, version and architecture of the operating system under which the File Bridge server is running. The variable name `SERVER_OPERATING_SYSTEM` can be abbreviated as `SERVER_OS`.

SERVER_BUILD_DATE

The build date of the File Bridge server in the format yyyyMMdd.

SERVER_DEFAULT_ENCODING

The name of the default string encoding that the File Bridge server uses.

SERVER_WORKING_DIRECTORY

The File Bridge server’s working directory. When creating a tablespace that is associated with a file name that is not an absolute path, the path is considered to be relative to this directory. Under Linux / Unix or z/OS Unix System Services, an absolute path is a path that starts with a forward slash character. Under Windows, an absolute path is a path that starts with a drive letter. The variable name SERVER_WORKING_DIRECTORY can be abbreviated as SERVER_WORKING_DIR.

SERVER_OPERATING_SYSTEM

The name, version and architecture of the operating system under which the File Bridge server is running. The variable name SERVER_OPERATING_SYSTEM can be abbreviated as SERVER_OS.

HEX

>>───HEX──(──┤expression├──)───────────────────────────────────────────><

Returns a hexadecimal representation of a value. The input argument is interpreted as a string value. If the input argument is a number, its UTF-8 representation is used. The result is a string of hexadecimal digits.

LENGTH

>>───LENGTH──(──┤expression├──)────────────────────────────────────────><

Returns the length of a value. The input argument is interpreted as a string value. If the input argument is a number, its UTF-8 representation is used. The result is the number of characters in the string.

Depending on the encoding of the associated file or data set, the number of characters in a string may be less than the number of bytes required to store the string.

LOWER

>>───LOWER──(──┤string─expression├──)──────────────────────────────────><

Returns a string in which all the characters have been converted to lowercase.

MAX

             ┌─,────────────────────┐
             V                      │
>>───MAX──(─────┤column─reference├──┴──)───────────────────────────────><

Returns the maximum value in a set of values.

The MAX function is a scalar function, not an aggregate function.

MIN

             ┌─,────────────────────┐
             V                      │
>>───MIN──(─────┤column─reference├──┴──)───────────────────────────────><

Returns the minimum value in a set of values.

The MIN function is a scalar function, not an aggregate function.

STRIP

>>──STRIP─(─┤expression├──┬────────────────────────────────────────┬─)─><
                          └─,─┤location├──┬─────────────────────┬──┘
                                          └─,─┤string─literal├──┘

The STRIP function returns a copy of the input string argument in which all spaces (or another specified character) have been removed from the beginning and/or end.

location may be L, T, or B. When omitted, B is assumed. Specifying L strips leading characters. Specifying T strips trailing characters. Specifying B strips both leading and trailing characters.

string-literal specifies which character to strip. If omitted, spaces are stripped.

SUBSTR

>>──SUBSTR──(──┤string─expression├──,──┤start├──┬─────────────┬──)─────><
                                                └─,─┤length├──┘

Returns a substring of a string. The value for start must be between 1 and the length of the string. length indicates the length of the resulting substring. If length is not specified, it is implicitly set to LENGTH(string-expression) - start + 1.

TO_CHAR

>>───TO_CHAR──(──┤expression├──┬──────────────────────┬──)─────────────><
                               └──,──┤format─string├──┘

Returns a string representation for a value of any data type. For DATE, TIME and TIMESTAMP values, an optional format string can be specified. If omitted, the format string defaults to the following values:

Data type Default format string

Data type	Default format string
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

The syntax of the format string corresponds to the syntax of the Java class DateFormat.

For all other data types, a format string must not be specified.

TO_DATE

>>───TO_DATE──(──┤expression├──┬──────────────────────┬──)─────────────><
                               └──,──┤format─string├──┘

Returns a date value that is obtained by parsing the specified string expression using the provided format string. The format string is optional. If omitted, it defaults to yyyy-MM-dd.

TO_TIME

>>───TO_TIME──(──┤expression├──┬──────────────────────┬──)─────────────><
                               └──,──┤format─string├──┘

Returns a time value that is obtained by parsing the specified string expression using the provided format string. The format string is optional. If omitted, it defaults to HH:mm:ss.

TO_TIMESTAMP

>>───TO_DATE──(──┤expression├──┬──────────────────────┬──)─────────────><
                               └──,──┤format─string├──┘

Returns a timestamp value that is obtained by parsing the specified string expression using the provided format string. The format string is optional. If omitted, it defaults to yyyy-MM-dd-HH:mm:ss.SSS.

UPPER

>>───UPPER──(──┤string─expression├──)──────────────────────────────────><

Returns a string in which all the characters have been converted to uppercase.

File Bridge JDBC URL Parameter

The JDBC server supports parameters in the JDBC URL to control the behavior of the connection between client and server.

Transport mechanism

There are different mechanisms for serializing the data when transported between client and server:

SIMPLE: The data is send as it is. This mechanism uses the most bandwidth.
COMPRESS: The data is compressed before sending. Using less bandwidth, but needs more processing. This is the default setting.
SERIAL: A experimental custom serialization. Uses less bandwidth and is faster then the other two mechanisms.

The transport mechanism can be set in the JDBC URL as parameter transport.

Example:

jdbc:ubs://server:5000;transport=SERIAL