AS400 ODBC Connection Problem

[simian101]

I have a vb.net 2003 program that makes an ODBC connection to an AS400 then it performs an insert query to put a single record in a table.

In testing it works perfectly. But in production from time to time it hangs up. I believe it may have something to do with the state of the AS400. When it hangs up I believe that the AS400 has really high CPU usage. Probably at or near 100%.

As you can imagine this is difficult to test. I was just wondering if there is anything I can do from VB side to further validate the connection?

If there really is no connection, then it fails in 30 seconds. However, in this high cpu state I believe it is making connection but the 400 won't process the query, becuase of this it may persist for 30 minutes or more. In some cases it has eventually gone thru. Most of the time it is assumed it is hung up and the program is manually ended. Is there way to regulate the timing of the connection?

Thanks for any ideas?

Simi

 

[RiverGuy]

The best course of action of course is to work with the AS400 system administrator to see what is going on with the AS400. You should also be able to set the .CommandTimeout property of your ODBC command object.

 

[Skittle]

It took me a great deal of effort to get my client server systems working with an ODBC connection to the AS400.
AS400 is a pig to work with using Microsoft technologies.

One problem I found is that if you are writing to a file on the AS400 via ODBC, make sure the file you are writing to on the AS400 is cleared down after each session.

If there are more than 50 records in it, I find I get timeouts when I try to write or read via ODBC. I write to the AS400 into a staging file and then run a stored procedure from VB to move the data from the input file to the true destination file on the AS400 and clear down the staging file for the next session. You have to use a unique identity for the write to the staging file and the SQL clear down afterwards.

Dazed and confused

 

[Sorwen]

All I can say on the subject is every admin I've worked with that was in charge of an AS400 had a conniption fit if it was near 100% CPU usage for an extended amount of time. Generally speaking you then have something doing what it shouldn't or they are not prioritizing daily processes right.

-I hate Microsoft!
-Forever and always forward.

 

[Skittle]

If the file you are accessing is large, the connection will put all its efort into retrieveing the recordset before you do the write ( I think ). Since connections atomatically go into a subsystem wih a priority the same as an interactive session, it will hammer the system. You can change the subsystem settings of course.



Dazed and confused