|
[DB2] |
DB2 Support |
OPEN (chan[,fileopt1])"[DB2]database[;table][;fileopt2]"
Where:
|
[DB2] |
File tag clause to inform PxPlus that it will be opening a DB2 database (not a PxPlus data file). | |||||||||||
|
chan |
Channel or logical file number to open. | |||||||||||
|
database |
Name of the DB2 database to which to connect. | |||||||||||
|
fileopt1 |
File options. Supported options include:
| |||||||||||
|
fileopt2 |
OPT=string$ |
Open parameters (see [DB2] OPT= Parameters). | ||||||||||
|
table |
Name of the table to open. If the table name is not supplied, either SQL statements sent to the database must be created by the application (see Using SQL Directly Within PxPlus) or table and/or column information must be retrieved (see Retrieving DB2/ODB Table and Column Information). | |||||||||||
The [DB2] tag is used as a prefix in an OPEN statement to denote that PxPlus is to route all file I/O requests to an external DB2 database server. Once you open a channel for [DB2] use, you can use it just like any other channel (i.e. for file I/O). It remains open until you close it. Use TCB(198) to check if DB2 is supported on a platform.
These OPEN parameters can be used for connecting via DB2. This list also indicates which parameters are supported for use in the INI file ([DB2] section):
|
ACCESS= |
Determines type of file access required (READ or WRITE). Default is ACCESS=WRITE. (INI Supported) | ||||||||
|
AUTOCOMMIT= |
Determines auto commit functionality of the database driver (either ON or OFF). (INI Supported) It is applicable only if the driver supports transactions. | ||||||||
|
CONCURRENCY= |
Determines the type of concurrent access control/locking to be used. (INI Supported)
| ||||||||
|
COMPLETE= |
Determines the response to incomplete information by the following values:
| ||||||||
|
CONNECT= |
Specifies a connection string surrounded by a delimiter character, enabling use of a "DSN-less" connection. Connection strings are driver specific. Consult the driver's reference for supported connection string values. Under UNIX/Linux, this parameter requires COMPLETE=0. If a connection string is supplied, the value of the database name is ignored. If the database name is not null, then the value is used only for determining if a connection should be shared. open (1,iol=*,opt="CONNECT='DSN=nomads'")"[DB2]foo;Customer" The table opened on channel 2 will share the connection because of foo. If the keywords USER= or PSWD= are supplied on the OPEN, then the values of these attributes will be appended to the connection string. | ||||||||
|
CURSORCLOSE= |
Determines whether all cursors are closed on a transaction commit. Options are YES (default) or NO. | ||||||||
|
CURSOR_TYPE= |
Defines the type of cursor that is to be used.
| ||||||||
|
CURSOR_USE= |
Defines the type of cursor to be used. (INI Supported)
| ||||||||
|
Date format mask applying to all date fields in table. (INI Supported) This can be a combination of Y M D with any other characters. Example: To convert dates to 4-character year, month and day: DATEFMT=YYYYMMDD Other characters are inserted as is. DATEFMT=YY/MM/DD with a date of March 1, 2004 would be returned as 04/03/01. Two packed century formats are also supported: • The first format, AA, maps A to 2000. The example of March 1, 2004 with DATEFMT=AAMMDD would be returned as A40301. | |||||||||
|
DB= or QUALIFIER= |
Qualifies the specific database that you wish to use when using a driver to service multiple databases.(INI Supported) | ||||||||
|
DEBUGIT= |
String to append to SQL statement along with program name and line number for debugging purposes. (INI Supported) This must indicate the comment character(s) appropriate to the database. Example: "--" is the comment identifier for Microsoft SQL Server; anything after "--" is ignored by SQL Server when compiling the SQL statement. | ||||||||
|
EXEC_SPRNO= |
Name of stored procedure used to emulate the RNO( ) function. You must create a stored procedure for each table and key for which an RNO is needed. Format is spRNOtablename_keynumber. | ||||||||
|
EXTROPT= |
Controls the format of the SELECT statement used to process an EXTRACT. (INI Supported) By default, PxPlus generates a SELECT* FROM table FOR UPDATE WHERE... When EXTROPT=text, then text is substituted in place of FOR UPDATE. In addition, if the first character of text is $, then the remaining characters of text are placed at the end of the SELECT statement rather than after the filename. This allows for different variations of SQL to be supported. | ||||||||
|
IND= |
Identifies a column that contains a sequential number starting at 0. This is used to emulate an indexed file. | ||||||||
|
ISOLATION= |
Controls the isolation that this connection will have relative to other processes on the same database. In particular, it controls Dirty reads (reading data that may be rolled back), Non-Repeatable reads (reading data after being changed by other transactions), and Phantom reads (reading data newly added to file). (INI Supported) UNCOMMITED (D, R, P possible) | ||||||||
|
KEY= |
Identifies fields that make up the key(s). For named keys, enter *NAME:keyname. OPEN(chan)"[ODB]dsn;table;KEY =field,field,*NAME:keyname" Use the :D option to indicate that the key segment is to be sorted in descending order. KEY=KeyFld1,KeyFld2:D,KeyFld3 | ||||||||
|
KEYDATA= |
Identifies a column that represents the key. This is used to emulate an external key where the data is not duplicated in the data. | ||||||||
|
KEYSET_SIZE= |
Size of the Keyset for use with the cursor. (INI Supported) | ||||||||
|
Reformats the contents of a date column to and from the Sage MAS 90 date format. | |||||||||
|
MAS90SET |
Sets flags for Sage MAS 90 emulation, such as turning on the MAS90DATE conversion. | ||||||||
|
MAXROWS= |
Maximum number of rows/records returned. (INI Supported) | ||||||||
|
NONUMADJ= |
Set to 1, Y or y to suppress +3 adjustment for defined length of numerics. (INI Supported) | ||||||||
|
NONULLS= |
Inserts zero-length strings rather than nulls into the target database and does not generate WHERE clauses checking for IS NULL or IS NOT NULL. (INI Supported) Set to 1, Y or y to enable or 0, N or n to disable. If the application does not work correctly when moving from Version 5 or lower, then set NONULLS=P to indicate that keys are handled the same as pre-Version 6. | ||||||||
|
NOSTRIP |
Keeps trailing spaces. (Default) | ||||||||
|
NULLPADKEY |
Forces keys to be padded to full length with the null character, $00$. When used in an INI file, set NULLPADKEY=1, Y or y. | ||||||||
|
POSUPDATE= |
Determines use of SqlSetPos functions. (INI Supported) Use one of the following:
| ||||||||
|
PREPARE= |
Set to 1, Y or y to use prepared statements. (INI Supported) Prepared statements are pre-compiled SQL that may improve performance. | ||||||||
|
PSWD= |
Specifies password. (INI Supported, but NOT SECURE. Anyone with access to the INI will be able to read this password.) | ||||||||
|
REC= |
Provides the column names, type and size. This is typically done to improve performance. If this information is not provided, then PxPlus must query the database for this information. | ||||||||
|
RECDATA= |
Identifies a column to return as the full record. This can be used for variant records that use complex rules to identify the record type. | ||||||||
|
ROWSET_SIZE= |
Size of the Rowset used by the cursor. (INI Supported) | ||||||||
|
SHARED |
Sets all tables to share a single connection to the Oracle database. (Default) | ||||||||
|
STDDATE |
Overrides the above formatting on individual columns. | ||||||||
|
STRIP |
Removes trailing spaces from fields. | ||||||||
|
TEXTMAX= |
Overrides maximum size for text fields. (Default is 8192 bytes.) (INI Supported) | ||||||||
|
TIMEOUT= |
Defines the timeout value for any SQL operation (time before error 0 returned). (INI Supported) | ||||||||
|
TOP= |
Specifies use of the TOP clause in SELECT statements (limits the number of rows to return in a result set). (INI Supported) If TOP=n is non-zero, then the KEF( ) / KEL( ) functions issue a SELECT TOP1…SQL statement, which improves system performance. If TOP=n > 0, then PxPlus issues SELECT TOP n to reduce the data transferred. TOP=-1 indicates the driver supports SELECT TOP, but normal reading should not use it. Default is 0 (TOP not supported). | ||||||||
|
TSQL= |
Defines a SQL statement that is used to control what data the logical file returns. | ||||||||
|
TYP= |
Sets identifier for different variant records. See ODB/OCI Variant Record Processing. | ||||||||
|
UNIQUE |
Sets new opens to be on a unique connection to the database. (INI Supported) When used in an INI file, set UNIQUE=1, Y or y. UNIQUE=0, N or n indicates a shared connection. | ||||||||
|
USER= |
Specifies login name. (INI Supported) | ||||||||
|
Set to 1, Y or y to validate the data. This option can be included in the respective INI section by database type. Date validation for database connections will only be included if the database OPEN specifies a date format using the DATEFMT= or MAS90DATE option. For additional data validation information, see IOLIST directive. (The VALIDATE option was added in PxPlus 2017.) |
To open a connection to a DB2 table using the column names to generate the IOList:
open (14,iol=*)"[DB2]DB2instance,DB2table"
Use of Global String Variables as Option Values
In database connection options, you can provide the value following the = (equals sign) in global string variables. When opening the connection to the database, the system will check to see if the value following the = (equals sign) starts with a % and ends with a $ and is the name of a valid global string variable. If so, the value is replaced with the contents of that global variable.
For example, the option USER=%UserName$ would be dynamically changed by the EXE on the OPEN to replace the text %UserName$ with whatever is in that global variable.
(Support to allow the use of global variables for defining database Link/Prefix files was added in PxPlus 2023 Update 1.)
READ Read Data from File
READ RECORD Read Record from File
SELECT Select/Query From ... Where
WRITE Add/Update Data in File
WRITE RECORD Write Record
OPEN Open a File for Processing
IOLIST Specify Variable List
[ADO] Microsoft SQL Server Interface
[MYSQL] Open MySql Native Database Link
[OCI] Connect to Oracle Server
[ODB] Open Database
Database Export Utility
When accessing an external database with a raw (no table specified) connection, it is possible to find out what the table, columns and indices are by using either a READ or READ RECORD and a KEY=string$ value:
Where:
|
string$ |
Can be one of the following:
|
Example:
open (chan)"[DB2]Database;;"
read (chan,key="?")iol=TableIOList