7. OpenSQL Features : DBMS Extensions : With Clause Syntax
 
Share this page                  
With Clause Syntax
The WITH clause can contain options intended for different Enterprise Access or EDBC products. Enterprise Access, EDBC, and Ingres options can be specified in a WITH clause.
The WITH clause has the following syntax:
WITH [db_id_]option_name [= option_value]
        {, [db_id_]option_name [= option_value]}
db_id_
Specifies the server class of the Enterprise Access or EDBC product for which the option is specified. The trailing underscore is required, and the option_name parameter must be appended with no intervening space.
If this parameter is specified, only the specified Enterprise Access or EDBC product will process the option. If this parameter is omitted, all Enterprise Access, EDBC, or database management systems will attempt to process the option. Enterprise Access and EDBC will ignore options they cannot process. Valid values are:
DB2_ DB2 or DB2 UDB
IMS_ IMS
RDB_ Rdb/VMS
RMS_ RMS
SQL_ All Ingres relational or SQL‑based Enterprise Access or EDBC products
option_name
Specifies the name of the option. If this is an Enterprise Access‑specific or EDBC-specific option, option_name must be preceded by the db_id. For details about the product‑specific options, see your Enterprise Access or EDBC product guide.
option_value
Specifies the value of the option (if required). This value can be specified using a quoted or unquoted character string, numeric literal, or variable.
All values must be specified using simple data formats, such as integers, numerics, names, or strings. If a complex value is required, it must be encoded in a quoted string.
To specify a list of values, use a comma‑separated list enclosed in parentheses. For example:
with myoption=(value1, value2, value3)
If an option is specified using a string variable, (for example with :stringvar,) and no value is to be provided, the variable must contain the string “NULL.” Enterprise Access or EDBC will ignore the option.
WITH Clause Examples
1. Connect to a DB2 subsystem DB2T and set the default database for table creation to mydb.
connect 'mvs1::db2t/db2' with db2_ct_option = 
'mydb';
EDBC for DB2 receiving the preceding connect request will issue the DB2 statements required to connect to the DB2T DB2 subsystem with the indicated default database.
2. Create a database table and specify DBMS‑specific extensions for DB2.
create table newtab (col1 integer, col2 integer not null) 
with db2_ct_option = 'audit all', alb_type = private;