RE: Very Slow Run Time on AS/400 for Java Program

[Joyce Louie <JLouie@xxxxxxxxxxxxxxx>]

Our ISeries is a 620-2179 and has 1.536 GB of memory.

CRTJVAPGM is mysterious to me because after it says it created the class/jar
files (which I confirm with a DSPJVAPGM but can't find the object with the
machine instructions it generates), I assume the RUNJVA somehow finds this
machine code that links back to my class/jar files?

I do not have the jt400 or jt400Native jar files in my classpath anymore (I
was using them when I tried the Toolbox JDBC driver though before I realized
I should be using the native driver from the Developer Kit).  Now I am just
using the db2_classes.jar file from the native JDBC driver.

I thought I'd insert the code I'm using in case there is something
inefficient in my logic that I can't see (I'm new to Java).  By the way, the
number of records I'm retrieving is about 50,000 currently but grows a bit
each day (it's an inventory file).

Thanks for much for any help you can give!


//
// This class retrieves inventory information from "ILI" on the AS/400 and
writes it to
// "BPCSF_ILI_SNAPSHOT" on the SQL Server.
//

import java.sql.*;                                      //get all Java sql
classes
public class Inventory                                  //define controlling
class
{
public static void main(String args[])                  //define main method
{
String as400Library = args[0];                          //declare AS/400
library input parm
int[] results;                                          //declare array for
batch update
try
{
//Load the As/400 JDBC driver
DriverManager.registerDriver
    (new com.ibm.db2.jdbc.app.DB2Driver());

//Connect to the AS/400
Connection ca = DriverManager.getConnection
    ("jdbc:db2:*local; block size=570", "TRANSFER", "PROGRAM");

//Load the Sql Server JDBC driver
DriverManager.registerDriver
    (new com.microsoft.jdbc.sqlserver.SQLServerDriver());

//Connect to the Sql Server
Connection cs = DriverManager.getConnection
    ("jdbc:microsoft:sqlserver://hae-bra-sql01.btree.haemo.us:1433",
"invinq", "qnivni");

//Set up SQL statement to select fields from the Location Inventory file on
the AS/400
//Available inventory = (LOPB - LISSU + LADJU + LRCT - LIALOC)
PreparedStatement pstmt_select = ca.prepareStatement
    ("SELECT LWHS, LLOC, LPROD, LLOT, (LOPB - LISSU + LADJU + LRCT -
LIALOC), LOPB, LISSU, LADJU, LRCT, LIALOC FROM " + as400Library + ".ILI");

//Set up SQL statement to clear the table on the Sql Server
PreparedStatement pstmt_clear = cs.prepareStatement
    ("DELETE FROM " + as400Library + "_ILI_SNAPSHOT");

//Set up SQL statement to insert the retrieved fields into a table on the
Sql Server
PreparedStatement pstmt_insert = cs.prepareStatement
    ("INSERT INTO " + as400Library + "_ILI_SNAPSHOT (WAREHOUSE, LOCATION,
ITEM, LOT, AVAILABLE, OPENINGBALANCE, ISSUES, ADJUSTMENTS, RECEIPTS,
ALLOCATIONS) VALUES (?,?,?,?,?,?,?,?,?,?)");

//Clear the current data in the Sql Server table
pstmt_clear.executeUpdate();

//Execute the SQL Select statement
ResultSet rs = pstmt_select.executeQuery();              //run the SQL query
select

//Loop thru the selected AS/400 inventory records and write them to the Sql
Server table
while (rs.next())
{
     pstmt_insert.setString(1,rs.getString(1));         //warehouse
     pstmt_insert.setString(2,rs.getString(2));         //location
     pstmt_insert.setString(3,rs.getString(3));         //item number
     pstmt_insert.setString(4,rs.getString(4));         //lot number
     pstmt_insert.setDouble(5,rs.getDouble(5));         //available
     pstmt_insert.setDouble(6,rs.getDouble(6));         //opening balance
     pstmt_insert.setDouble(7,rs.getDouble(7));         //issues
     pstmt_insert.setDouble(8,rs.getDouble(8));         //adjustments
     pstmt_insert.setDouble(9,rs.getDouble(9));         //receipts
     pstmt_insert.setDouble(10,rs.getDouble(10));       //allocated to
orders
//     pstmt_insert.executeUpdate();                          //run the
insert
     pstmt_insert.addBatch();                                  //batch the
insert
}
results = pstmt_insert.executeBatch();                 //execute the insert
with batch

//Close the pstmt's
pstmt_select.close();
pstmt_clear.close();
pstmt_insert.close();

//Close the connections
ca.close();
cs.close();

}                                                         //end try

//Print a message if any Sql errors were found
catch(SQLException se)
{
System.err.println("SQL Exception: " + se);
}
}                                                                      //end
main method
}                                                          //end Inventory
class


-----Original Message-----
From: Dave Wall [mailto:dawall@us.ibm.com]
Sent: Thursday, May 23, 2002 10:58 AM
To: java400-l@midrange.com
Subject: Re: Very Slow Run Time on AS/400 for Java Program



How big is your iSeries (both processor and memory)?  Java uses a lot of
memory so maybe your box is memory constrained.  You seem to be doing the
right things.  CRTJVAPGM makes a big difference on v4r5 machines but you
already did that.

Also, not that it will help performance, but you mentioned jt400.jar.  If
you are using the native JDBC driver you don't need that jar in your
classpath (unless you are using some other Toolbox class).

David Wall
Toolbox for Java
iSeries ODBC Driver for Linux




                      Joyce Louie
                      <JLouie@Haemoneti        To:
"'java400-l@midrange.com'" <java400-l@midrange.com>
                      cs.com>                  cc:
                      Sent by:                 Subject:  Very Slow Run Time
on AS/400 for Java Program
                      java400-l-admin@m
                      idrange.com


                      05/22/2002 12:36
                      PM
                      Please respond to
                      java400-l





Hi,

I put my question out on JGuru but got no replies so hopefully you can help
me out here.  I wrote a Java program to Select records (by certain fields)
from an AS/400 file, Delete from an Sql Server table, and then Insert the
selected records to this Sql Server table.
My environment is:  OS/400 V4R5, JDK 1.3, Native JDBC driver (from AS/400
Developer Kit for Java).  I am using the latest versions of the db2_classes
(AS/400), and msbase, msutil and mssqlserver (MS Sql Server) jar files in
my
classpath.  I used QSH javac to create the class for my Java program.  I
then did a CRTJVAPGM at optimization=40 for this class, as well as the 3 MS
Sql Server jar files (I got "unable to create" error when I tried the
CRTJVAPGM on the AS/400 jar file).  I am using blocked fetch (set the block
size to 570, which is a multiple of the total length of my fields), and
batch update (using addBatch and executeBatch instead of executeUpdate in
my
results loop) - both of these coding logic were ones that I found in a JDBC
performance tips document.
The problem is that  the Java program runs VERY slowly on the AS/400
(either
from QSH with java, or with RUNJVA) and uses up alot of CPU resources.  It
runs well over 10 times longer than if I ran it from the PC DOC
environment.
Any suggestions on what could be causing such poor performance when running
the Java program on the AS/400?  I have scanned many Java/400 forums and
websites but am just about out of ideas on what to try next.  My Java
program is virtually useless the way it runs now on the AS/400.  Please
help!
Thanks alot!



_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.






_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.