The IBM DB2 for i.NET provider (named IBM.Data.DB2.iSeries) allows Windows applications to use the Microsoft.NET Framework to access DB2 for i databases. What versions of.NET Framework are supported by the.NET Provider? The 5.4 version of the.NET Provider targets the Microsoft 1.0 / 1.1.NET Framework.
Active2 years, 6 months ago
![]()
I need to connect to remote DB2 from local SQL Server 2008 R2. I don't want to use linked servers. So I am searching for download and trying to install IBM DB2 driver, as Microsoft ODBC driver for DB2 comes with Host Integration Server is expensive.
putvande
13.3k33 gold badges2525 silver badges4747 bronze badges
ChowChow
3 Answers
You can download DB2 clients for your DB2 version from here: http://www-01.ibm.com/support/docview.wss?uid=swg27007053
Client software is free.
mustacciomustaccio
15.1k99 gold badges4141 silver badges4444 bronze badges
The below link has a step by step guide that worked well for me
CidCid
1,18811 gold badge1414 silver badges3535 bronze badges
Both answers are correct, but only have the links to were the answers are.
for the 32 bit version open your odbc data sources 32 bit otherwise open the odbc data sources 64 bit.
To open odbc data sources in windows versions 7 upwards
4,61299 gold badges2626 silver badges6060 bronze badges
Not the answer you're looking for? Browse other questions tagged sql-serverdb2odbcdriver or ask your own question.
This chapter describes how to work with IBM DB2 for iSeries in Oracle Data Integrator.
This chapter includes the following sections:
14.1 Introduction
Oracle Data Integrator (ODI) seamlessly integrates data in IBM DB2 for iSeries. Oracle Data Integrator features are designed to work best with IBM DB2 for iSeries, including reverse-engineering, changed data capture, data integrity check, and integration interfaces.
14.1.1 Concepts
The IBM DB2 for iSeries concepts map the Oracle Data Integrator concepts as follows: An IBM DB2 for iSeries server corresponds to a data server in Oracle Data Integrator. Within this server, a collection or schema maps to an Oracle Data Integrator physical schema. A set of related objects within one schema corresponds to a data model, and each table, view or synonym will appear as an ODI datastore, with its attributes, columns and constraints.
Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to IBM DB2 for iSeries.
14.1.2 Knowledge Modules
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 14-1 for handling IBM DB2 for iSeries data. In addition to these specific IBM DB2 for iSeries Knowledge Modules, it is also possible to use the generic SQL KMs with IBM DB2 for iSeries. See Chapter 4, 'Generic SQL' for more information.
Table 14-1 IBM DB2 for iSeries Knowledge Modules
14.2 Installation and Configuration
Make sure you have read the information in this section before you start working with the IBM DB2 for iSeries technology:
14.2.1 System Requirements and Certifications
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html .
14.2.2 Technology Specific Requirements
Some of the Knowledge Modules for IBM DB2 for iSeries use specific features of this database. The following restrictions apply when using these Knowledge Modules.
See the IBM DB2 for iSeries documentation for additional information on these topics.
Using System commands
This section describes the requirements that must be met before using iSeries specific commands in the knowledge modules for IBM DB2 for iSeries:
Using CDC with Journals
This section describes the requirements that must be met before using the Journal-based Change Data Capture with IBM DB2 for iSeries:
14.2.3 Connectivity Requirements
This section lists the requirements for connecting to an IBM DB2 for iSeries system.
JDBC Driver
Oracle Data Integrator is installed with a default IBM DB2 Datadirect Driver. This drivers directly uses the TCP/IP network layer and requires no other installed component or configuration. You can alternatively use the drivers provided by IBM, such as the Native Driver when installing the agent on iSeries.
14.3 Setting up the Topology
Setting up the Topology consists of:
14.3.1 Creating a DB2/400 Data Server
An IBM DB2/400 data server corresponds to an iSeries server connected with a specific user account. This user will have access to several databases in this server, corresponding to the physical schemas in Oracle Data Integrator created under the data server.
14.3.1.1 Creation of the Data Server
Create a data server for the IBM DB2/400 technology using the standard procedure, as described in 'Creating a Data Server' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining an IBM DB2/400 data server:
14.3.2 Creating a DB2/400 Physical Schema
Create an IBM DB2/400 physical schema using the standard procedure, as described in 'Creating a Physical Schema' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
The work schema and data schema in this physical schema correspond each to a schema (collection or library). The work schema should point to a temporary schema and the data schema should point to the schema hosting the data to integrate.
Create for this physical schema a logical schema using the standard procedure, as described in 'Creating a Logical Schema' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
14.4 Setting Up an Integration Project
Setting up a project using the IBM DB2 for iSeries database follows the standard procedure. See 'Creating an Integration Project' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
It is recommended to import the following knowledge modules into your project for getting started with IBM DB2 for iSeries:
14.5 Creating and Reverse-Engineering an IBM DB2/400 Model
This section contains the following topics:
14.5.1 Create an IBM DB2/400 Model
Create an IBM DB2/400 Model using the standard procedure, as described in 'Creating a Model' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
14.5.2 Reverse-engineer an IBM DB2/400 Model
IBM DB2 for iSeries supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering, which uses a RKM to retrieve the metadata.
In most of the cases, consider using the standard JDBC reverse engineering for starting.
Consider switching to customized reverse-engineering for retrieving more metadata. IBM DB2 for iSeries customized reverse-engineering retrieves the physical files, database tables, database views, columns, foreign keys and primary and alternate keys.
Standard Reverse-Engineering
To perform a Standard Reverse-Engineering on IBM DB2 for iSeries use the usual procedure, as described in 'Reverse-engineering a Model' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Customized Reverse-Engineering
To perform a Customized Reverse-Engineering on IBM DB2 for iSeries with a RKM, use the usual procedure, as described in 'Reverse-engineering a Model' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to the IBM DB2/400 technology:
In the Reverse tab of the IBM DB2/400 Model, select the
KM: RKM DB2 400.<project name> .
14.6 Setting up Changed Data Capture
Oracle Data Integrator handles Changed Data Capture on iSeries with two methods:
14.6.1 Setting up Trigger-Based CDC
This method support Simple Journalizing and Consistent Set Journalizing. The IBM DB2 for iSeries JKMs use triggers to capture data changes on the source tables.
Oracle Data Integrator provides the Knowledge Modules listed in Table 14-2 for journalizing IBM DB2 for iSeries tables using triggers.
See Chapter 'Working with Changed Data Capture' of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for details on how to set up journalizing and how to use captured changes.
Table 14-2 IBM DB2 for iSeries Journalizing Knowledge Modules
14.6.2 Setting up Log-Based CDC
This method is set up with the JKM DB2/400 Journal Simple and used by the LKM DB2/400 Journal to SQL. It uses also an RPG program to retrieve the journal content.
14.6.2.1 How does it work?
A iSeries transaction journal contains the entire history of the data changes for a given period. It is handled by the iSeries system for tables that are journaled. A journaled table is either a table from a collection, or a table for which a journal receiver and a journal have been created and journaling started.
Reading the transaction journal is performed by the a journal retriever CDCRTVJRN RPG program provided with Oracle Data Integrator. This program loads on demand the tables of the Oracle Data Integrator CDC infrastructure (J$ tables) with the contents from the transaction journal. Adobe after effects cs6 crack.
This program can be either scheduled on the iSeries system or called by the KMs through a stored procedure also called CDCRTVJRN. This stored procedure is automatically created by the JKM DB2/400 Journal Simple and invoked by the LKM DB2/400 Journal to SQL when data extraction is needed.
14.6.2.2 CDCRTVJRN Program Details
This program connects to the native iSeries journal for a given table, and captures changed data information into the Oracle Data Integrator Journal (J$).
The program works as follows:
This program accepts the parameters listed in Table 14-3.
Table 14-3 CDCRTVJRN Program Parameters
Download Ibm Iseries Odbc Driver14.6.2.3 Installing the CDC Components on iSeries
There are two major components installed on the iSeries system to enable native journal reading:
Note:
The program must be set up in a library defined in the Topology as the default work library for this iSeries data server. In the examples below, this library is called ODILIB .
Installing the CDCRTVJRN Program
To install the CDCRTVJRN program:
The CDCRTVJRN Stored Procedure
This procedure is used to call the CDCRTVJRN program. It is automatically created by the JKM DB2/400 Journal Simple KM when journalizing is started. Journalizing startup is described in the Change Data Capture topic.
The syntax for the stored procedure is provided below for reference:
Note:
The stored procedure and the program are installed in a library defined in the Topology as the default work library for this iSeries data server
14.6.2.4 Using the CDC with the Native Journals
Once the program is installed and the CDC is setup, using the native journals consists in using the LKM DB2/400 Journal to SQL to extract journalized data from the iSeries system. The retrieval process is triggered if the RETRIEVE_JOURNAL_ENTRIES option is set to
true for the LKM.
14.6.2.5 Problems While Reading Journals
This section list the possibly issues when using this changed data capture method.
CDCRTVJRN Program Limits
The following limits exist for the CDCRTVJRN program:
Troubleshooting the CDCRTVJRN Program
The journal reading process can be put in trace mode:
The reading process logs are stored in a spool file which can be reviewed using the WRKSPLF command.
You can also review the raw contents of the iSeries journal using the DSPJRN command.
14.7 Setting up Data Quality
Oracle Data Integrator provides the generic CKM SQL for checking data integrity against constraints defined in DB2/400. See 'Set up Flow Control and Post-Integration Control' in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for details.
See Chapter 4, 'Generic SQL' for more information.
14.8 Designing an Interface
You can use IBM DB2 for iSeries as a source, staging area or a target of an integration interface.
The KM choice for an interface or a check determines the abilities and performance of this interface or check. The recommendations in this section help in the selection of the KM for different situations concerning an IBM DB2 for iSeries data server.
14.8.1 Loading Data from and to IBM DB2 for iSeries
IBM DB2 for iSeries can be used as a source, target or staging area of an interface. The LKM choice in the Interface Flow tab to load data between IBM DB2 for iSeries and another type of data server is essential for the performance of an interface.
14.8.1.1 Loading Data from IBM DB2 for iSeries
Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from IBM DB2 for iSeries to a target or staging area database. These optimized IBM DB2 for iSeries KMs are listed in Table 14-4.
In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved to load data from IBM DB2 for iSeries to a target or staging area database.
Table 14-4 KMs for loading data from IBM DB2 for iSeries
14.8.1.2 Loading Data to IBM DB2 for iSeries
Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into an IBM DB2 for iSeries database. These optimized IBM DB2 for iSeries KMs are listed in Table 14-5.
In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.
Table 14-5 KMs for loading data to IBM DB2 for iSeries
14.8.2 Integrating Data in IBM DB2 for iSeries
Oracle Data Integrator provides Knowledge Modules that implement optimized data integration strategies for IBM DB2 for iSeries. These optimized IBM DB2 for iSeries KMs are listed in Table 14-6. I
In addition to these KMs, you can also use the Generic SQL KMs.
The IKM choice in the Interface Flow tab determines the performances and possibilities for integrating.
Table 14-6 KMs for integrating data to IBM DB2 for iSeries
Using Slowly Changing Dimensions
For using slowly changing dimensions, make sure to set the Slowly Changing Dimension value for each column of the target datastore. This value is used by the IKM DB2 400 Slowly Changing Dimension to identify the Surrogate Key, Natural Key, Overwrite or Insert Column, Current Record Flag and Start/End Timestamps columns.
14.9 Specific Considerations with DB2 for iSeries
This section provides specific considerations when using Oracle Data Integrator in an iSeries environment.
14.9.1 Installing the Run-Time Agent on iSeries
The Oracle Data Integrator Standalone Agent can be installed on iSeries.
See the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator for more information.
14.9.2 Alternative Connectivity Methods for iSeries
It is preferable to use the built-in IBM DB2 Datadirect driver in most cases. This driver directly use the TCP/IP network layer and require no other components installed on the client machine. Other methods exist to connect DB2 on iSeries.
14.9.2.1 Using Client Access
It is also possible to connect through ODBC with the IBM Client Access component installed on the machine. This method does not have very good performance and does not support the reverse engineering and some other features. It is therefore not recommended.
14.9.2.2 Using the IBM JT/400 and Native Drivers
This driver appears as a
jt400.zip file you must copy into your Oracle Data Integrator installation drivers directory.
To connect DB2 for iSeries with a Java application installed on the iSeries machine, IBM recommends that you use the JT/400 Native driver (
jt400native.jar ) instead of the JT/400 driver (jt400.jar ). The Native driver provides optimized access to the DB2 system, but works only from the iSeries machine.
To support seamlessly both drivers with one connection, Oracle Data Integrator has a built-in Driver Wrapper for AS/400. This wrapper connects through the Native driver if possible, otherwise it uses the JT/400 driver. It is recommended that you use this wrapper if running agents installed on AS/400 systems.
To configure a data server with the driver wrapper:
14.10 Troubleshooting
This section provides information on how to troubleshoot problems that you might encounter when using Oracle Knowledge Modules. It contains the following topics:
14.10.1 Troubleshooting Error messages
Errors in Oracle Data Integrator appear often in the following way:
the
java.sql.SQLExceptioncode simply indicates that a query was made to the database through the JDBC driver, which has returned an error. This error is frequently a database or driver error, and must be interpreted in this direction.
Only the part of text in bold must first be taken in account. It must be searched in the DB2 or iSeries documentation. If its contains sometimes an error code specific to your system, with which the error can be immediately identified.
If such an error is identified in the execution log, it is necessary to analyze the SQL code send to the database to find the source of the error. The code is displayed in the description tab of the erroneous task.
14.10.2 Common Problems and Solutions
This section describes common problems and solutions.
Ibm Db2 Iseries Client Download 201714.10.2.1 Connection ErrorsIbm Iseries Download
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |