Recently I have developed an integration module between JDEdwards (IBM DB2) database and Oracle Database using .NET Web service. Core objective of the module is to read the Payments data from JDEdwards Database and update to Oracle Database.
It was quite challenging and interesting task to work with. Being a beginner to JDEdwards Database, I had come across with some hurdles especially while reading JDEdwards Data during my implementation process. So I just would like to share this article with a hope that it could really be useful to someone who are on the same boat .
Download Integrating DB2 Universal Database for iSeries with Microsoft ADO.NET Book :
http://www.redbooks.ibm.com/redbooks/pdfs/sg246440.pdf
PC setup:Before we begin, the following software components must be installed onto your development PC:
Install available Service Packs (Fixes) from the iSeries Access for Windows Web site at:
http://www.ibm.com/servers/eserver/iseries/access/windows/
Note: The IBM DB2 UDB for iSeries .NET provider is also referred to by the namespace it defines, IBM.Data.DB2.iSeries. The two terms are used interchangeably.
IBM.Data.DB2.iSeries provider object model:
Adding an assembly reference to the provider:
- Solution Explorer (Figure 4-8), right-click References and select Add Reference
- Select IBM DB2 UDB for iSeries .NET provider and click Select.
IBM.Data.DB2.iSeries Namespace will allow applications using Microsoft's .NET framework to access iSeries databases using ADO.NET.
Imports IBM.Data.DB2.iSeries
Accessing JDEdwards Data using .NET:
Configure your JDEdwards connection parameters in Web.Config file:
You could always use Encryption and Decryption of this connection strings in Web.Config file.
-- Development Server configuration -->
<add key="JDE_IP" value="xxx.xxx.xxx.xxx" />
<add key="JDE_UserName" value="username" />
<add key="JDE_Password" value="password" />
Protected Friend Function JDEConnection()
As iDB2Connection
'Open JDE Connection
Try
Dim JDEConn
As iDB2Connection =
New iDB2Connection
Dim JDEConnString
As String =
String.Empty
Dim JDE_Ip
As String = ConfigurationSettings.AppSettings("JDE_Ip")
Dim JDE_UserName
As String = ConfigurationSettings.AppSettings("JDE_UserName")
Dim JDE_Password
As String = ConfigurationSettings.AppSettings("JDE_Password ")
JDEConnString = "DataSource=" & JDE_Ip & ";UserID=" & JDE_UserName &
";Password=" & JDE_Password & ";EnablePreFetch=false;"
JDEConn.ConnectionString = JDEConnString
JDEConn.Open()
Return JDEConn
Catch ex
As Exception
'Handle the exceptions
End Try
End Function
Using Kerberos authentication
Before opening a connection using Kerberos, you must use iSeries Navigator to define the connection and configure the connection for Kerberos. In your ConnectionString, do not specify either the
UserID or the Password property; the connection will use the configured default of Kerberos.
For more information about using Kerberos with iSeries Access for Windows, see the IBM Redbook iSeries Access for Windows V5R2 Hot Topics.
Connection pooling
The IBM.Data.DB2.iSeries .NET provider supports connection pooling. This support is on by default.
Use the Pooling property to turn connection pooling on or off.
Example 4-12 Specifying the Pooling property in the ConnectionString:
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = "DataSource=myiSeries; Pooling=false;";
Some Important Notes:
DataAdapter with the
iDB2CharBitData and
iDB2VarCharBitData data type of column results array of bytes to Dataset.
So, Alter your SQL SELECT statement to explicitly cast the data.
Example :CAST(COL1 AS VARCHAR(10) CCSID 037)
sSQL = "SELECT CAST(COL1 AS VARCHAR(10) CCSID 037) AS COL1, COL2, COL2 FROM <Table> <Where clause>"
All numeric columns in JDE always hold a negative value, so you must convert the data to a proper numeric value. Following ConvertAmount function will do the job for you.
Private Function ConvertAmount(ByVal Amount As Double) As Double
Dim dReturn As
Double = Amount
If dReturn < 0 Then
dReturn = CDbl((Amount * -1) / 100)
End If
Return dReturn
End Function
JDEdwards stores the date values in Julian date format which is a numeric value.
0 --> represents no date.
So you must convert the Julian date from to Oracle Date format (Or human readable format). Use the following function to Convert Julian Date to Normal Date format.
Private Function Julian2Date(ByVal pDate As String) As String
'Julian date in JDE database is in number format. 0 represents no date
Try
Dim sReturn As String = String.Empty
Dim jDate As Integer
Dim dtIn As Date
If Not IsDBNull(pDate.Trim) AndAlso pDate.Trim.Length > 0 Then
jDate = CInt(pDate)
'jDate = 0 means the invoice
If jDate > 0 Then
dtIn = DateSerial(CInt(1900 + jDate / 1000), 1, CInt(jDate Mod 1000))
sReturn = dtIn.ToString("yyyyMMdd")
End If
End If
Return sReturn
Catch ex As Exception
'Handle the exceptions
End Try
End Function
Public Function Date2Julian(ByVal lstrDate As String) As Long
Dim lstrDt As Date
Dim lintYear As Integer
Dim lintDays As Integer
lstrDt = System.DateTime.ParseExact(lstrDate, "yyyyMMdd", Nothing)
lintDays = lstrDt.DayOfYear
lintYear = CInt(Mid(lstrDate, 1, 4))
Date2Julian = (lintYear - 1900) * 1000 + lintDays
End Function
Injucting JDEdwards Data into a DataSet:
Public Function GetJDEPaymentInfo As DataSet
Dim _JDEConnection As iDB2Connection
Dim daJDEPayments As New iDB2DataAdapter
Dim dsJDEPayments As DataSet = Nothing 'traditional ADO.NET Dataset Object
Try
_JDEConnection = JDEConnection() 'Open JDE Connection
If Not _JDEConnection Is Nothing Then
dsJDEPayments = New DataSet("dsJDEPayments")
daJDEPayments = New iDB2DataAdapter(<SQLString>, _JDEConnection)
daJDEPayments.Fill(dsJDEPayments, "tbJDEData")
End If
Catch ex As Exception
Throw ex
Finally
If Not _JDEConnection Is Nothing AndAlso _JDEConnection.State <> onnectionState.Closed Then
_JDEConnection.Close()
_JDEConnection.Dispose()
End If
End Try
Return dsJDEPayments
End Function
