To my knowledge you are correct on the reader. The single connection is letting the app run continuously where the system was not affected as adversely before due to the jobs ending and restarting interrupting the process.
A few things to check for next: 1) The ODBC job should not be taking over that bad unless for some reason it is running at priority 20 or lower (on 400 lower number is better, just like golf). The prestart job entries for the server side job should be normally be 30 or so. 2) After making the connection do a call to a CL program to start debugging the AS/400 server job. Your SQL command would be something likee Call libraryname.STRDEBUG (assuming using ANSI naming conventions) And the 400 CL program would be PGM STRDBG UPDPROD(*YES) /* omit the UPDPROD parm if the DB's library is attr TEST */ ENDPGM This places a great deal additional information in the job log on the 400 that can be used to determine the reason(s) why the query is causing so much overhead on the system. 3) The where clause is definitely processed on the 400, but make sure your selection criteria are supported by an index(s) if working against a very large table. 4) There is a object created on the 400 called an SQL Package which keeps access plans for the queries. If there has been changes to the tables or the method which you are accessing the table from earlier tests, the package could be used to create some wrong assumptions. You can delete the package and it will be recreated using current assumptions on the file which can have a big effect if it now decides to do an indexed lookup versus a full table scan. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Castelot Sent: Friday, May 03, 2002 1:56 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net I was under the impression that the DataReader is only for reading and that there is no way to control cursors, etc. -----Original Message----- From: Steve Miller [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 2:34 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net It would be my assumption that being that the DataReader is maintaining state it may be attempting to use a client side cursor. Steve Miller G. A. Sullivan -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Castelot Sent: Friday, May 03, 2002 1:24 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net Ok. This is the deal. Using a single connection and the DataReader I brought the entire production data base to its knees simply by doing a select statement to see if an item already exists on the database. I had to abort the process after an hour when management screamed because of performance. (And this is only a subset of the necessary processing.) Making new connections doesn't seem to bring the 400 to its knees, just takes for ever and a day. The kicker here is that an old, rather poorly written VB6 application using odbc drivers is out performing the .Net version. -----Original Message----- From: Tom Archer [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 7:04 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net On start, yes, but then you no longer need to reopen the connection. Personally, I prefer the lower-level coding of APPC because I like to have that level of control. You'll also note that the faster ODBC drivers (such as Showcase) are implemented using APPC for this reason. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Heizer Sent: Thursday, May 02, 2002 6:57 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net APPC incurs the same overhead on start up as a opening a connection do to the need to start a new job on the 400 to service the requests. My preference has bee to just hold an open connection over writing the routines to handle the APPC communication. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Tom Archer Sent: Thursday, May 02, 2002 5:37 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net >>> 1) I assume you are not creating a new connection each time you access the 400. 400 connections are very costly to create. If for some reason you must create a new connection all the time, verify that 400 is configured to reuse the prestart jobs handling the connection. <<< This would be one of the main benefits of writing your own APPC conversations - to have this level of control. -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Heizer Sent: Thursday, May 02, 2002 6:26 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] Accessing AS400 data through ado.net Some thoughts after many years of squeezing what ever I could 1) I assume you are not creating a new connection each time you access the 400. 400 connections are very costly to create. If for some reason you must create a new connection all the time, verify that 400 is configured to reuse the prestart jobs handling the connection. 2) Use programs written in one of the ILE languages on the 400 and call them just as you would a SQL stored procedure. 3) Inserts are always slow, if you only will be using the 400 for this application, use the SQL extensions for blocking the inserts. Good luck -----Original Message----- From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of Christopher Castelot Sent: Thursday, May 02, 2002 3:30 PM To: [EMAIL PROTECTED] Subject: [DOTNET] Accessing AS400 data through ado.net Hi all, I work in an environment which has most inventory and distribution data on an AS400. The company also has an Oracle 8i database for client server apps and web based business. For all intents and purposes the Oracle database is a mirror of the 400. However, all data is entered on to the 400 and oracle is kept in sync through a series of updates. My project is to start loading product data for new sources directly to the AS400 (what has been a primarily manual process). I started out writing the application in .Net and would like to finish it that way. Here's the problem: 1) Using Client Access 5.1, Ado.Net, and the oledb provider IBMDA400 directly hitting the 400 takes nearly 3 hours to process 28,000 records. Unacceptably long. This same scenario in terms of processing except hitting the Oracle database takes 14 minutes to process the same 28,000 records. (MSDAORA as the provider.) If I use an Oracle Gateway (MSDAORA as the provider) the same processing takes 30 minutes. I would think that going through a gateway would be slower than going direct. The question is how can I optimize performance going directly to the 400? Although it is interesting that Oracle takes significantly less time it is also not an option. The gateway is only for selects, not inserts. And, most importantly, this project is dead on arrival if I can't get that processing time down to a reasonable time. Here's what I am doing? connectionString "Provider=IBMDA400.DataSource.1;Data Source=prod;User ID=*****;Password=******" cmdText "SELECT title AS result FROM tdmsdta01.dmm005 WHERE itnbr = '009335021X '" try { conn = NewConnection(); OleDbCommand cmd = new OleDbCommand( sSql, conn); conn.Open(); dr = cmd.ExecuteReader(); if (dr.Read()) { objReturn = dr.GetValue(0); } } catch Any hints would be greatly appreciated! Chris You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
