DB2 UDB Access Requirements
The DB2 CLI interface is used by the gateway. To access DB2 UDB, the following conditions must be met:
• The correct version of DB2 UDB must be in the PATH.
• User logins must be defined to DB2 UDB.
• Permission must be granted to access each DB2 UDB database.
• Each user ID in the database must be granted the Create Table (CREATETAB) permission. CREATETAB does not explicitly grant permissions to execute stored procedures, because by default DB2 UDB users are granted IMPLICIT_SCHEMA authority and can therefore create stored procedures.
• The ingres user can optionally be made the DBA of the database.
• A DB2 CLI Data Source Name (DSN) must be defined for the database. The gateway uses the DB2 CLI DSN to access the DB2 UDB database on the local or remote system.
• If collation sequences need to be the same as for Ingres, the DB2 UDB database should be created with "CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM".
Note: During the installation of DB2 UDB, system administration (SYSADM) permissions are granted by default to any user that is a member of the administrator's group. A security recommendation is to modify the default permissions granted to users of the administrators group. For further information, see the IBM Administration Guide for DB2 UDB.
Grant User Permissions (DB2 UDB)
The following example grants permissions to a user 'INGRES' and a non-privileged user 'FRED'. It does not explicitly grant permissions to execute stored procedures, because by default DB2 UDB users are granted IMPLICIT_SCHEMA authority and can therefore create stored procedures. The example grant statements need to be executed by the database administrator of the DB2 UDB database:
To grant permissions
1. Connect as a database administrator to the DB2 UDB instance.
2. Assign the permissions as follows:
GRANT DBADM, CREATETAB, CONNECT ON DATABASE TO USER INGRES;
GRANT CREATETAB, CONNECT ON DATABASE TO USER FRED;
DB2 UDB Gateway and the Decimal Data Type
Enterprise Access expects the decimal separator to be a period (.). Inside DB2 UDB, this is not related to II_DECIMAL (you can use II_DECIMAL="," with the DB2 UDB Gateway, provided that the DBMS locale uses ".").
Note: There are db2cli.ini file settings that should change the way CLI applications like Enterprise Access behave with decimals. The setting is "patch2=15" ; however, this has no effect with Windows DB2 UDB 9.5. If in the future this is made to work, locale/language would not need to be changed.
The alternative is to change the language setting for DB2 according to the following examples.
To set up the locale settings for Windows
Under Microsoft Windows, DB2 only uses the Windows locale settings.
1. From the Control Panel, select Regional and Language Options.
• Settings on the "Standards and formats" and "Location" tabs do not influence the display format of numbers in the DB2 command line client.
• On the Languages tab, language settings are not relevant.
2. On the Advanced tab, select the language "English." (This affects the DB2 command line SQL terminal.)
3. Click OK.
4. Reboot the machine.
To configure the DSN to use the regional settings, select the Use regional settings when outputting currency, numbers, dates and times check box. See DSN Setup for Windows for additional information.
To set up the locale settings for Solaris SPARC
LC_CTYPE dictates the comma as compared to period behavior. For example:
export LC_CTYPE=de_DE.ISO8859-1
......
select ...
COL1
--------
1234,45
export LC_CTYPE=en_US
......
select ...
COL1
--------
1234.45
For additional information about II_DECIMAL, see Developing Portable Applications and the Ingres OpenSQL Reference Guide.