If you have access to any of the Oracle clients then you most likely have
the JDBC drivers already.
If not, You can download them from
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
You place the JDBC driver in your classpath on the iSeries, You can create
a java pgm to retreive data from the Oracle
tables, you can either call the java pgm from a rpg pgm or insert the data
into a db2 table.
Below is a simple java pgm that retrieves data from a oracle database and
inserts the data into a db2 table.
hth
// Get invoice detail for selected customer from last billing run from
Oracle database
// and insert into work table in DB2 database to update EDI data with.
import java.sql.*;
import com.ibm.as400.access.*;
class get_inv_dtl
{
public static void main (String[] args) throws SQLException
{
String OracleConnStr = " ";
while (OracleConnStr.equals(" ")) {
OracleConnStr = Console_Input.readFromConsole(
"Enter Oracle connection string of system to connect
with\n"+
"Enter a period to use default, which is:
host.domain.com:1521:oracle_instance\n");
}
if (OracleConnStr.equals(".")) {
OracleConnStr = "host.domain.com:1521:oracle_instance";
}
System.out.println(" ");
String OracleUserId = " ";
while (OracleUserId.equals(" ")) {
OracleUserId = Console_Input.readFromConsole(
"Enter ORACLE user id to connect with\n");
}
System.out.println(" ");
String OraclePassword = " ";
while (OraclePassword.equals(" ")) {
OraclePassword = Console_Input.readFromConsole(
"Enter ORACLE password to connect with\n");
}
System.out.println(" ");
String DB2SysName = " ";
while (DB2SysName.equals(" ")) {
DB2SysName = Console_Input.readFromConsole(
"Enter DB2 system name to connect with\n");
}
System.out.println(" ");
String DB2UserId = " ";
while (DB2UserId.equals(" ")) {
DB2UserId = Console_Input.readFromConsole(
"Enter DB2 user id to connect with\n");
}
System.out.println(" ");
String DB2Password = " ";
while (DB2Password.equals(" ")) {
DB2Password = Console_Input.readFromConsole(
"Enter DB2 password to connect with\n");
}
System.out.println(" ");
String DB2Schema = " ";
while (DB2Schema.equals(" ")) {
DB2Schema = Console_Input.readFromConsole(
"Enter DB2 schema name where table to insert invoice data
is\n");
}
System.out.println(" ");
String DB2Table = " ";
while (DB2Table.equals(" ")) {
DB2Table = Console_Input.readFromConsole(
"Enter DB2 table name to insert invoice data into\n");
}
System.out.println(" ");
String StrLicId = " ";
while (StrLicId.equals(" ")) {
StrLicId = Console_Input.readFromConsole(
"Enter customer Id to process( i.e. 99880 or 99884 )\n");
}
System.out.println(" ");
String StrWeekNo = " ";
while (StrWeekNo.equals(" ")) {
StrWeekNo = Console_Input.readFromConsole(
"Enter week to process(i.e. 200601 = xxxxyy where xxxx is
year and yy is month)\n");
}
System.out.println(" ");
int LicId = Integer.parseInt( StrLicId );
int WeekNo = Integer.parseInt( StrWeekNo );
System.out.println(" ");
System.out.println("Oracle System being accessed: "+ OracleConnStr);
System.out.println(" ");
System.out.println("DB2 System being accessed: "+ DB2SysName);
System.out.println("DB2 Schema name: "+ DB2Schema);
System.out.println("DB2 Table name: "+ DB2Table);
System.out.println(" ");
System.out.println("customer being processed: "+ StrLicId);
System.out.println("Week being processed: "+ StrWeekNo);
System.out.println(" ");
System.out.println("Start time is: " + new
Timestamp(System.currentTimeMillis()));
// Start elapsed time tracking for process
ElapsedTime timeRecorder = new ElapsedTime();
timeRecorder.setStartTime();
//Make a connection to the Oracle server
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection OracleConnection = DriverManager.getConnection
("jdbc:oracle:thin:@"+ OracleConnStr,OracleUserId,OraclePassword);
Statement OracleSelect = OracleConnection.createStatement();
ResultSet Oraclerset = OracleSelect.executeQuery(
"Select inv.cust_no, inv.inv_detail_id, cbg.cust_bill_id,"+
" inv.inv_batch_id, inv.inv_no, nvl(inv.rep_id_1,0),
nvl(inv.rep_id_2,0),"+
" inv.currency_id, inv.terms_id, inv.credit_flag,
inv.inv_date,"+
" inv.qty, inv.inv_amt, inv.tax_amt, inv.misc_fee_amt,"+
" inv.prod_tot_amt, inv.del_flag, nvl(inv.ref_info,' '),
inv.inv_type"+
" FROM tango.ar_inv_dtl inv, cust cus, cust_bill cbg"+
" where inv.cust_rpt_no in( Select cust_rpt_no"+
" from tango.billing_run_history"+
" where cust_no ="+ LicId +
" and bill_freq = 'WEEKLY'"+
" and status = 70"+
" and to_char(end_date,'YYYYWW') ="+
WeekNo +")"+
" and inv.cust_no ="+ CustNo +
" and cus.cust_bill_id = cbg.cust_bill_id "+
"Order by inv.cust_no, inv.cust_rpt_no, cbg.cust_bill_id,
cus.cust_id_as400");
// define / create connection to iSeries database
Connection DB2connection=null;
DriverManager.registerDriver (new AS400JDBCDriver());
DB2connection = DriverManager.getConnection
("jdbc:as400://"+ DB2SysName + "/" + DB2Schema +";user="+DB2UserId
+";password="+DB2Password +
";driver=native");
// Insert statement for DB2 database table insert
DatabaseMetaData dmd = DB2connection.getMetaData();
PreparedStatement DB2insert = DB2connection.prepareStatement
("insert into "+ DB2Schema + "." + DB2Table +
"( AILicId, AIAiId, AICbgId, AICusId, AIShCusId, AIRptId, AIBatch,
AIInvId, AIArPid1, AIArPid2,"+
"AICurId, AITrmId, AICredit, AIInvDat, AIQtyTot, AIcAmt, AIcTax,
AIFeeAmt, AIPrdAmt, AIDelFlag,"+
"AIRef, AIInvType )"+
" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
//System.out.println(" ");
//System.out.println ("Invoice data retrieved from Oracle database and
inserted into DB2");
//System.out.println(" ");
int rowCount = 0;
// Loop through the recordset we got from Oracle and write it to
iSeries database table
while (Oraclerset.next())
{
//Print data for the recordset retrieved from Oracle
/*
System.out.println(
Oraclerset.getBigDecimal(1) + "," + Oraclerset.getBigDecimal(2) +
"," + Oraclerset.getBigDecimal(3) + "," +
Oraclerset.getBigDecimal(4) + "," + Oraclerset.getBigDecimal(5) +
"," + Oraclerset.getBigDecimal(6) + "," +
Oraclerset.getBigDecimal(7) + "," + Oraclerset.getBigDecimal(8) +
"," + Oraclerset.getBigDecimal(9) + "," +
Oraclerset.getBigDecimal(10) + "," + Oraclerset.getBigDecimal(11) +
"," + Oraclerset.getBigDecimal(12) + "," +
"'" + Oraclerset.getString(13) + "'" + "," + "'" +
Oraclerset.getString(14) + "'" + "," +
Oraclerset.getBigDecimal(15) + "," + Oraclerset.getBigDecimal(16) +
"," + Oraclerset.getBigDecimal(17) + "," +
Oraclerset.getBigDecimal(18) + "," + Oraclerset.getBigDecimal(19) +
"," + Oraclerset.getBigDecimal(20) + "," +
"'" + Oraclerset.getString(21) + "'" + "," + "'" +
Oraclerset.getString(22) + "'");
*/
// prepare parameters for insert statement to insert row into DB2
database table
DB2insert.setBigDecimal(1,Oraclerset.getBigDecimal(1));
DB2insert.setBigDecimal(2,Oraclerset.getBigDecimal(2));
DB2insert.setBigDecimal(3,Oraclerset.getBigDecimal(3));
DB2insert.setBigDecimal(4,Oraclerset.getBigDecimal(4));
DB2insert.setBigDecimal(5,Oraclerset.getBigDecimal(5));
DB2insert.setBigDecimal(6,Oraclerset.getBigDecimal(6));
DB2insert.setBigDecimal(7,Oraclerset.getBigDecimal(7));
DB2insert.setBigDecimal(8,Oraclerset.getBigDecimal(8));
DB2insert.setBigDecimal(9,Oraclerset.getBigDecimal(9));
DB2insert.setBigDecimal(10,Oraclerset.getBigDecimal(10));
DB2insert.setBigDecimal(11,Oraclerset.getBigDecimal(11));
DB2insert.setBigDecimal(12,Oraclerset.getBigDecimal(12));
DB2insert.setString(13,Oraclerset.getString(13));
DB2insert.setDate(14,Oraclerset.getDate(14));
DB2insert.setBigDecimal(15,Oraclerset.getBigDecimal(15));
DB2insert.setBigDecimal(16,Oraclerset.getBigDecimal(16));
DB2insert.setBigDecimal(17,Oraclerset.getBigDecimal(17));
DB2insert.setBigDecimal(18,Oraclerset.getBigDecimal(18));
DB2insert.setBigDecimal(19,Oraclerset.getBigDecimal(19));
DB2insert.setBigDecimal(20,Oraclerset.getBigDecimal(20));
DB2insert.setString(21,Oraclerset.getString(21));
DB2insert.setString(22,Oraclerset.getString(22));
DB2insert.executeUpdate(); //write to iSeries database
rowCount++;
}
OracleSelect.close();
OracleConnection.close();
DB2connection.close() ;
DB2insert.close();
System.out.println(" ");
java.util.GregorianCalendar CurTimeStamp = new
java.util.GregorianCalendar();
System.out.println("End time is: " + new
Timestamp(System.currentTimeMillis()));
System.out.println(" ");
System.out.println("Count of rows retrived from Oracle / inserted into
DB2: " + rowCount);
System.out.println(" ");
// Stop and display elasped time recorder
timeRecorder.setEndTime();
System.out.println(timeRecorder);
}
}
There's always the JDBC type 4 drivers for Oracle.... I think there are
commercial products using this approach.
Eric
-----Original Message-----
From: Michael Ryan
Sent: Monday, April 02, 2007 10:25 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQLRPGLE to remote oracle db
I don't think that will work. Unless the Oracle to DB2 thing is
involved, that program is accessing a DB2 database. I think it's
Oracle as in "JD Edwards (that's owned by Oracle) running on an i5".
On 4/2/07, Lansink, David <david.lansink@xxxxxxxxxxxx> wrote:
All
I would like to be able to read from a remote oracle data base in an
sqlrpgle program. I came across this sample program in the archive but
no explanation of the parms used in the program
Is anyone using sqlrpgle to access a remote data base and if so please
send me an example with an explanation.
midrange.com
