Example: Java program
This example program is written in the Java™ programming language.
By using the code examples, you agree to the terms of the Code license and disclaimer information.
5738PW1 V5R4M0 000000 SEU SOURCE LISTING 00/00/00 17:12:35 PAGE 1 SOURCE FILE . . . . . . . DRDA/QLBLSRC MEMBER . . . . . . . . . DDBPT6CB SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 /******************************************************************************/ /* PROGRAM NAME: SampJava */ /* */ /* DESCRIPTIVE NAME: Sample java application using DRDA */ /* */ /* FUNCTION: This module processes the PART_STOCK table and */ /* for each part below the ROP (REORDER POINT) */ /* creates a supply order. */ /* */ /* LOCAL TABLES: PART_STOCK */ /* */ /* REMOTE TABLES: PART_ORDER, PART_ORDLN, SHIPMENTLN */ /* */ /* COMPILE OPTIONS: */ /* javac SampJava.java */ /* */ /* INVOKED BY: */ /* java SampJava lcldbname rmtdbname */ /******************************************************************************/ import java.sql.*; public class SampJava { private static String JDBCDriver = "com.ibm.db2.jcc.DB2Driver"; private static String part_table = " ";/* part number in table part_stock */ private static long line_count = 0;/* total number of order lines */ private static long eoq_table = 0;/* reorder quantity , tbl part_stock */ private static long quant_table = 0;/* quantity in stock, tbl part_stock */ private static long rop_table = 0;/* reorder point , tbl part_stock */ private static int contl = 0; /* continuation line, tbl order_line */ private static short next_num = 0;/* next order nbr,table part_order */ /****************************************************************************/ /* Method For Reseting Environment */ /****************************************************************************/ private static void resetTables(Connection rmtConn) throws SQLException { Statement stmt1 = rmtConn.createStatement(); /* Clean up for rerunability in test environment */ stmt1.executeUpdate("DELETE FROM DRDA.PART_ORDLN WHERE ORDER_NUM IN " + " (SELECT ORDER_NUM FROM DRDA.PART_ORDER " + " WHERE ORDER_TYPE = 'R')"); stmt1.executeUpdate("DELETE FROM DRDA.PART_ORDER WHERE ORDER_TYPE = 'R'"); stmt1.close(); rmtConn.commit(); } /* function delete_for_rerun */5738PW1 V5R4M0 000000 SEU SOURCE LISTING 00/00/00 17:12:35 PAGE 2 SOURCE FILE . . . . . . . DRDA/QLBLSRC MEMBER . . . . . . . . . DDBPT6CB SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 /****************************************************************************/ /* Method For Calculating Order Quantity */ /****************************************************************************/ private static void calculateOrderQuantity(Connection lclConn, Connection rmtConn, String loc) throws SQLException { PreparedStatement prpStmt1; PreparedStatement prpStmt2; ResultSet rsltSet1; ResultSet rsltSet2; short ord_table = 0; /* order nbr. , tbl order_line */ short orl_table = 0; /* order line , tbl order_line */ prpStmt1 = lclConn.prepareStatement("SELECT PART_NUM, PART_QUANT, PART_ROP, PART_EOQ " + " FROM DRDA.PART_STOCK WHERE PART_ROP > PART_QUANT AND " + " PART_NUM > ? ORDER BY PART_NUM"); prpStmt1.setString(1,part_table); rsltSet1 = prpStmt1.executeQuery(); if (rsltSet1.next() == false) { System.out.println("--------------------------------"); System.out.println("NUMBER OF LINES CREATED = " + line_count); System.out.println("--------------------------------"); System.out.println("***** END OF PROGRAM *********"); rop_table = 0; /* no (more) orders to process */ } else { /* available qty = Stock qty + qty in order - qty received */ part_table = rsltSet1.getString(1); quant_table = rsltSet1.getLong(2); rop_table = rsltSet1.getLong(3); eoq_table = rsltSet1.getLong(4); long qty_rec = 0; prpStmt2 = rmtConn.prepareStatement("SELECT A.ORDER_NUM, ORDER_LINE, QUANT_REQ " + " FROM DRDA.PART_ORDLN A, DRDA.PART_ORDER B " + " WHERE PART_NUM = ? AND LINE_STAT <> 'C' AND " + " A.ORDER_NUM = B.ORDER_NUM AND ORDER_TYPE = 'R'"); prpStmt2.setString(1,part_table); rsltSet2 = prpStmt2.executeQuery(); while (rsltSet2.next()) { ord_table = rsltSet2.getShort(1); orl_table = rsltSet2.getShort(2); long qty_table = rsltSet2.getLong(3); qty_rec = qty_rec + qty_table; } rsltSet2.close();5738PW1 V5R4M0 000000 SEU SOURCE LISTING 00/00/00 17:12:35 PAGE 3 SOURCE FILE . . . . . . . DRDA/QLBLSRC MEMBER . . . . . . . . . DDBPT6CB SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 prpStmt2 = rmtConn.prepareStatement("SELECT SUM(QUANT_RECV) FROM DRDA.SHIPMENTLN " + " WHERE ORDER_LOC = ? AND ORDER_NUM = ? AND " + " ORDER_LINE = ?"); prpStmt2.setString(1,loc); prpStmt2.setShort(2,ord_table); prpStmt2.setShort(3,orl_table); rsltSet2 = prpStmt2.executeQuery(); rsltSet2.next(); long qty_table = rsltSet2.getLong(1); qty_rec = qty_rec + qty_table; rsltSet2.close(); prpStmt2.close(); } rsltSet1.close(); prpStmt1.close(); } /* end of calculate_order_quantity */ /****************************************************************************/ /* Method For Processing Orders */ /****************************************************************************/ private static void processOrder(Connection rmtConn, String loc) throws SQLException { PreparedStatement prpStmt1; ResultSet rsltSet1; /* insert order and order_line in remote database */ if (contl == 0) { prpStmt1 = rmtConn.prepareStatement("SELECT (MAX(ORDER_NUM) + 1) FROM DRDA.PART_ORDER"); rsltSet1 = prpStmt1.executeQuery(); rsltSet1.next(); next_num = rsltSet1.getShort(1); rsltSet1.close(); prpStmt1 = rmtConn.prepareStatement("INSERT INTO DRDA.PART_ORDER (ORDER_NUM, ORIGIN_LOC, ORDER_TYPE, ORDER_STAT, CREAT_TIME) " + " VALUES (?, ?, 'R', 'O', CURRENT TIMESTAMP)"); prpStmt1.setShort(1,next_num); prpStmt1.setString(2,loc); prpStmt1.executeUpdate(); System.out.println("***** ROP PROCESSING *********"); System.out.println("ORDER NUMBER = " + next_num); System.out.println("--------------------------------"); System.out.println(" LINE PART QTY "); System.out.println(" NBR NBR REQUESTED"); System.out.println("--------------------------------"); contl = contl + 1; } /* if contl == 0 */5738PW1 V5R4M0 000000 SEU SOURCE LISTING 00/00/00 17:12:35 PAGE 4 SOURCE FILE . . . . . . . DRDA/QLBLSRC MEMBER . . . . . . . . . DDBPT6CB SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 prpStmt1 = rmtConn.prepareStatement("INSERT INTO DRDA.PART_ORDLN (ORDER_NUM, ORDER_LINE, PART_NUM, QUANT_REQ, LINE_STAT) " + " VALUES (?, ?, ?, ?, 'O')"); prpStmt1.setShort(1,next_num); prpStmt1.setInt(2,contl); prpStmt1.setString(3,part_table); prpStmt1.setLong(4,eoq_table); prpStmt1.executeUpdate(); line_count = line_count + 1; System.out.println(" " + line_count + " " + part_table + " " + eoq_table + ""); contl = contl + 1; prpStmt1.close(); } /* end of function processOrder */ /****************************************************************************/ /* Method For Displaying Errors */ /****************************************************************************/ private static void errorFunction(SQLException e, Connection lclConn, Connection rmtConn) { System.out.println("************************"); System.out.println("* SQL ERROR *"); System.out.println("************************"); System.out.println("SQLCODE = " + e.getErrorCode()); System.out.println("SQLSTATE = " + e.getSQLState()); System.out.println("**********************"); try { lclConn.rollback(); rmtConn.rollback(); } catch (SQLException uowErr) { } } /* end of function errorFunction */5738PW1 V5R4M0 000000 SEU SOURCE LISTING 00/00/00 17:12:35 PAGE 5 SOURCE FILE . . . . . . . DRDA/QLBLSRC MEMBER . . . . . . . . . DDBPT6CB SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0 /****************************************************************************/ /* Mainline */ /****************************************************************************/ public static void main(String[] args) { String User = "myuser"; String Password = "mypwd"; String lclUrl = null; String rmtUrl = null; String loc = "SQLA"; /* dealer's database name */ Connection lclConn = null; Connection rmtConn = null; try { Class.forName(JDBCDriver).newInstance(); } catch (Exception e) { System.out.println("Error: Failed to load DB2 driver."); System.exit(1); } try { lclUrl = "jdbc:db2:" + args[0]; lclConn = DriverManager.getConnection(lclUrl, User, Password); rmtUrl = "jdbc:db2:" + args[1]; rmtConn = DriverManager.getConnection(rmtUrl, User, Password); } catch (Exception e) { System.out.println("Error: Failed to get database connections."); System.exit(1); } try { /* Initialization */ resetTables(rmtConn); /* Main Work */ do { calculateOrderQuantity(lclConn, rmtConn, loc); if (rop_table > quant_table) { processOrder(rmtConn, loc); quant_table = 0; } } while (rop_table != 0); /* End Work */ lclConn.commit(); rmtConn.commit(); } catch (SQLException e) { e.printStackTrace(); errorFunction(e, lclConn, rmtConn); System.exit(1); } } }
Parent topic:
Examples: Application programming