Connect to IBM DB2 Server (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to IBM DB2 Server.

Supported Versions

The FireDAC native driver supports IBM DB2 Enterprise, Workgroup, Express, or AS/400 editions version 8 and later. We recommend v 8.2 at least. You can also work with other IBM database products using FireDAC ODBC or DBX Bridge drivers.

Client Software

Windows Client Software

FireDAC requires the "IBM DATA SERVER DRIVER for ODBC", "IBM DB2 ODBC DRIVER", or "IBM DB2 DRIVER FOR ODBC" x86 or x64 ODBC driver to be installed on the workstation. These can be downloaded from the following locations:

After downloading and unpacking, run "<client>\bin\db2oreg1 -i" to install the ODBC driver. Additionally, download and install the fix packs.

If the DB2 ODBC driver has not been installed properly, an exception is raised when you try to connect: 

[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

DB2 AS400

FireDAC requires the "iSERIES ACCESS ODBC DRIVER" driver to connect to DB2 AS400.

Linux Client Software

FireDAC requires the unixODBC 64-bit ODBC driver manager library and the IBM DB2 64-bit ODBC driver.

To install the IBM DB2 64-bit ODBC driver on Linux, download it and run the following commands to install it: 

tar -xavf ibm_data_server_driver_for_odbc_cli_linuxx64_v*.tar.*
cd odbc_cli
sudo mkdir -p /opt/ibm/clidriver
sudo cp -r clidriver/* /opt/ibm/clidriver
echo [IBM DB2 ODBC DRIVER] > db2.driver.template
echo Description=IBM DB2 ODBC Driver >> db2.driver.template
echo Driver=/opt/ibm/clidriver/lib/libdb2o.so >> db2.driver.template
echo DontDLClose=1 >> db2.driver.template
echo Threading=1 >> db2.driver.template
odbcinst -i -d -f db2.driver.template

Driver Linkage

To link the driver:

Connection Definition Parameters

There are two methods of specifying the DB2 connection attributes. If the DB2 client software has the DB2 connection aliases configured, an application should use the Alias parameter. If no aliases are configured, then you should use the Protocol, Server, Port, and Database parameters. Also specify User_Name and Password (see Defining Connection (FireDAC) for details).

DriverID=DB2

Parameter Description Example value
Alias Connection alias. MyDB2Srv
Server Host name, if Alias is not specified. 127.0.0.1
Port Port value, if Alias is not specified. 5000
Database Database name, if Alias is not specified. ADDEMO
Protocol Protocol name, if Alias is not specified. TCPIP
User_Name The DB2 user name. db2admin
Password The DB user password. Note that the passwords with both '{' and '}' are not supported. master
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection (0 specifies an infinite wait). 30
StringFormat

Defines how to represent String values:

  • Choose -- represent as ftString / ftWideString / ftMemo / ftWideMemo, depending on the declared data type name (default).
  • Unicode -- always represent as ftWideString / ftWideMemo.
 Unicode
ExtendedMetadata

Controls the extended description of the query result sets:

  • True -- FireDAC describes a result set to obtain all the possible column attributes - is read-only, is auto incrementing, column origin, and so on. Setting this option to True slightly slows down a dataset opening.
  • False -- FireDAC uses the restricted information about the query columns (default).
 True
ODBCAdvanced Allows you to specify any other additional ODBC connection parameter value. The default value is "IGNOREWARNINGS=1".
TxSupported Specifies if the DB2 database supports SQL transactions or not.
  • Yes. The DB2 database supports transactions. This is the default value.
  • No. The DB2 database does not support transactions.
    Note: Set this parameter to No when the StartTransaction, Commit or Rollback method calls lead to errors. For example:
    • When a transaction includes a DB2 database table created with the NOT LOGGED INITIALLY option.
    • When a AS400 DB2 database has the transaction logging disabled.
 Yes
MetaDefSchema Default schema name. The design time code excludes the schema name from the object name if it is equal to MetaDefSchema. db2admin

Use Cases

  • Connect to DB2 using the existing database alias:
DriverID=DB2
Alias=addemo
User_Name=db2admin
Password=mypwd
MetaDefSchema=db2admin
  • Connect to DB using full connection information:
DriverID=DB2
Server=127.0.0.1
Database=addemo
Port=50000
Protocol=TCPIP
User_Name=db2admin
Password=mypwd
MetaDefSchema=db2admin
  • Connect to DB2 AS400
To connect to DB2 AS400, take the following steps:
  1. Drop a TFDConnection and a TFDPhysDB2DriverLink component on the TForm .
  2. Set the TFDPhysDB2DriverLink.ODBCDriver property to 'iSeries Access ODBC Driver'.
  3. Double click on TFDConnection and set Driver ID to DB2.
  4. Press the Wizard button.
  5. Fill the connection parameters using the ODBC driver dialog.
  6. Press the OK button to save parameters.

See Also

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Athens/e/index.php?title=Connect_to_IBM_DB2_Server_(FireDAC)&oldid=263006"