Configuring DB2 Database Access on iSeries

You can access DB2 databases using the DB2 DBMS connector.

The DB2 connector for iSeries can operate in two distinct modes:

Normal mode—uses the same implicit connection to the database that every iSeries job has to access database objects. In this mode no user name or password is needed in the DB2:RDBDIRE:LIBRARY|| path.
Since there is only one such implicit connection, this mode is unsuitable for parallel transactions. The component property TRANSACTION=TRUE will therefore not work in this mode.
Server mode—supports parallel transaction functionality, which requires multiple, simultaneous connections to the database, each having its own commitment control. Each connection must be explicitly established by logging on to the database. To use Server mode, you must specify a valid user profile name and password in the DB2: path, for example,
```
$DB2 = DB2:RDBDIRE:LIBRARY|User|Password
```
Server mode uses iSeries technology by that name. In this mode a separate job called QSQSRVR in subsystem QSYSWRK is started by the system for each connection to the database, each with their own commitment control.

The presence or absence of the user name determines whether the connector uses Server mode or Normal mode.

For more information about Server mode on the iSeries, refer to the IBM Knowledge Center or other IBM i documentation .

Edit the DBMS assignment file member to specify the connector and path:
```
[DRIVER_SETTINGS]
DB2 U4.0

[PATHS]
$DB2 = DB2:RDBDIRE:Library||
```

Set the connector settings as required.

[DRIVER_SETTINGS]
DB2 U4.0
USYS$DB2_PARAMS  segmented fields : off

Edit the client (or IDE) assignment file to direct the $DB2 path to the machine running the Uniface Router.
For example, use the same $DB2 settings as in the server’s assignment file (see the contents of member DBMS in file ASN) to start a Uniface Server of type ANY:
```
$DB2 = TCP:iSeries+13001|user|pass
$DEF = $DB2
```
This is enough to allow the client to access tables in the iSeries library specified in the $DB2 path of the server’s assignment file.
To start a Uniface Server in the role of a database server:
```
$DB2 = TCP:iSeries+13001|user|pass + DB2:RDBDIRE:LIBRARY||
$DEF = $DB2
```
(The RDBDIRE and LIBRARY words in this line are the same as in the DBMS’s assignment file on the server—they must be replaced by actual values.)
Note: Be careful not to delete the colons or the vertical bars in the $DB2 setting.
Note: Tables that do not exist are created on the fly, but the library itself (which must be a collection) is not—it must already exist.
If you want to use DB2 as the Uniface Repository, generate the Create table scripts (using IDE with the /gensql command line switch, see the Uniface Library). Then run the SQL scripts using the command:
```
RUNSQLSTM DFTRDBCOL(Library)
```

When two or more clients use the same DB2 server or the same ANY server to do sql ProcScript (or use the SQL workbench), their database transactions will be mixed up. This happens when they log on to the Uniface Router with the same user name, password, and UST, and it is a shared Uniface Server.

This means that one of these clients can read uncommitted changes made by the other, and commits and rollbacks will apply to both clients, because they are in a single transaction. This can be avoided by using exclusive servers or by using different user names in the paths of the client assignment files.

Note: When using a single user profile and a single UST on more than one simultaneous connection to the iSeries, we recommend that you use an exclusive database or ANY server, rather than a shared server to make changes to the database.