SQL command Leaving File open

210 pts.
Tags:
AS/400
V5R4
Our VB.net person is using OLE DB Provider IBMDA400 to connect to our ISeries (AS/400). He uses a Table adapter to do Inserts, Deletes, etc... to files on the ISeries (AS/400). For some reason the QZDASOINIT job that is used keeps the files he works with open, doesn't close them. Can he close them or can something be done without waiting for that job to time out?

Software/Hardware used:
VB.net 2008, SQL, ISeries, V5R4

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    I don't have an environment at the moment to look at the settings specific for IBMDA400, but have your developer look for a LazyClose option. Whichever way it is set -- Yes/No or On/Off or checked/unchecked -- try setting it the opposite way. Post back with results. Tom
    125,585 pointsBadges:
    report
  • DoneThat
    Sorry if this answer is too simplistic but I close everything after each task (VBA)... ' Close the AS400 connection and clean up. Set cmdAS400Command = Nothing Set conAS400System = Nothing If I had to open another workbook then... ' Close the Excel connection and clean up. Set cmExcelCommand = Nothing Set cnExcelWorkbook = Nothing With 10 - 20 regular users, AS400 does not have any files left open. Also, no noticeable delay upon reopening to do the next task. Gary
    830 pointsBadges:
    report
  • TomLiotta
    Note that the database servers may choose not to close open files in order to avoid having to do full opens and full closes for every connection. The "lazy close" attribute of a connection is one way of giving permission to only do what's called "pseudo-opens" and "pseudo-closes". Holding files open for later transactions can make significant improvement in performance. (Also note that this is perhaps more important for sites that may have hundreds or thousands of QZDASOINIT jobs with active connections at once, handling a thousand transactions or more per second.) Tom
    125,585 pointsBadges:
    report
  • BeanBaggs
    Searching online I only see the LazyClose associated with the ODBC connection not an OLE DB one. I will ask him when he gets back to look for that and what DoneThat posted. Will post results.
    210 pointsBadges:
    report
  • TomLiotta
    I only see the LazyClose associated with the ODBC connection not an OLE DB one. I also don't see one anywhere, which makes me wonder how/if an OLE DB client can have any control. But the default for ODBC should be 'Off' anyway in current releases. Do you know if you have a QAQQINI file that controls options? The OPEN_CURSOR_CLOSE_COUNT and OPEN_CURSOR_THRESHOLD options can control pseudo-opens/closes. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Sorry if this answer is too simplistic... Not simplistic at all. The obvious answer is often the best one. It's easy to forget to ask "Was the connection actually closed?" Tom
    125,585 pointsBadges:
    report
  • BeanBaggs
    We are not manually opening the Table adapter, so he figured the garbage clean up should take care of the close, but apparently not. Tried to use the .Dispose and that didn't work either, so the VB person is going to try to close the Table Adapter connection manually and see what that does.
    210 pointsBadges:
    report
  • BeanBaggs
    [...] BeanBaggs is running into some file closing operations on AS/400, but members help him figure out what’s going [...]
    0 pointsBadges:
    report
  • BeanBaggs
    It ended up being “pseudo-closes” that IBM puts on files when using SQL to help with speed. Needed to add CONFLICT(*RQSRLS) to the ALCOBJ command of those files so that it would "hard close" those files. Then our CL could ALCOBJ the files. Looks like most commands will "hard close" pseudo-closes", but ALCOBJ does not, and that is why the CONFLICT(*RQSRLS) was added to ALCOBJ.
    210 pointsBadges:
    report
  • TomLiotta
    Needed to add CONFLICT(*RQSRLS) to the ALCOBJ command of those files so that it would “hard close” those files. Then our CL could... This is a very valuable item to post back here. It will be potentially useful to many others. Thanks for remembering to come back and adding the update. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following