|
My issue was trying to end odbc jobs where developers run an sql statement then leave their pc and go home and the result is files being open and nightly batch jobs abending because of it. Here's the IBM doc/info on this one, in case anyone's interested. Support line told me the open files show up on the additional calls of the SQL statement because of pseudo closed cursors. Pseudo closed cursors are used for performance reasons. IBM's not aware of a way you can completely get rid of pseudo closed cursors. You can set some options in the QAQQINI file, but we are not sure these options will totally eliminate the pseudo closed cursors. I will send you a Knowledgebase document on pseudo closed cursors. Also, the issue can't b/resolved using ACCPTHSIZ being *MAX1TB instead of *MAX4GB although this size could affect performance. This document and many others can be found by selecting the "Technical databases" link at the iSeries Technical Support website at the following URL: http://www-03.ibm.com/servers/eserver/support/iseries/index.html While you are there, check out other exciting offerings such as iPTF and Recommended Fixes. We show you the quickest way to keep your iSeries systems current on fixes. IBM Software Technical Document Document Number: 18874457 ____________________________________________________________ Functional Area: Host Servers SubFunctional Area: Database Server SubSubFunctional Area: General ____________________________________________________________ Product: 400-12 HOST SUPPORT (5769SS1HS); OS/400 BASE (5722SS100) Release: V4R3M0; V4R4M0; V4R5M0; V5R1M0; V5R2M0; V5R3M0; V5R4M0 Classification: IBM Internal Use Keywords: JDBC, ODBC, SQL, SQLCLI ____________________________________________________________ Document Title:Pseudo Closed Cursor FAQ Document Description: What Is a Pseudo Closed Cursor? Pseudo closed cursors are a key part of a performance optimization feature of IBM® DB2®/400 SQL. When an application closes a cursor, DB2/400 normally closes the cursor and closes the file (deleting the open data path - ODP). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. DB2/400 can then reuse the cursor as needed without the overhead of a full open of the file. DB2/400 can also decide to hard close the pseudo closed cursor when needed. When Is a Cursor Pseudo Closed? This varies depending on PTF level. In general, the first time a dynamic SQL statement is prepared, run, and closed, the cursor is hard closed, the ODP is deleted, and the file is closed. However, DB2/400 keeps track of the SQL statement and can detect if it is run a second time. DB2/400 tracks the number of times the statement is used against the number of times the program is invoked. These statistics are kept in the SQL statement area in the SQL package, Prepared Statement Area, or Program Associated Space. If the same statement is run a second time, the cursor is pseudo closed and cached (it is left in the Open Cursor List but a bit is set showing the cursor as pseudo closed). Further running of the same SQL statement can then use the existing ODP (assuming, of course, that it is reusable). Note that R420 APAR SA76930 enabled support for forcing cursors to pseudo close (rather than hard close) on the first execution. In R520 IBM® OS/400®, statements executed out of a SQL package may be pseudo-closed after the first execution. How Many Pseudo Closed Cursors Are Cached? Can This Be controlled? The number of pseudo closed cursors that are held is set by IBM and can be changed at any time. Internally, pseudo closed cursor are tracked differently depending on how they are named. Cursors that reuse an existing cursor's name are treated differently than cursors that have a unique name. The current default setting for uniquely named cursors is no limit. The limit can be controlled in R450 operating system and later by the OPEN_CURSOR_THRESHOLD QAQQINI file option. For additional information, refer to the Rochester Support Center knowledgebase document 16262423, Use of QAQQINI File. To link to document 1626423 immediately, click here (Document link: Database 'Rochester Support Line KnowledgeBase', View '1.0 DBManagement View\All', Document 'Use of QAQQINI File'). The limit is enforced only during a full open. Therefore, some applications may exceed the limit. If the application reuses the same cursor name (close cursor, drop statement, allocate a new statement which uses the previous cursor name), the pseudo closed cursor is treated differently. DB2/400 must rename the cursor, cache the new name, and match a later execute of the same statement with the renamed cursor. At this time, the limit to these pseudo closed cursors is 150. This limit is controlled through the data area QSQCSRT. However, this is not documented for customer use. The limit is enforced on a prepare. Therefore, it is possible for jobs to exceed this limit. For example, statements executed from an extended dynamic packages are not prepared. In situations like this, the 150 cursor limit can be exceeded. What Happens When the Limit Is Reached? By default, all cursors are hard closed and the entire list is cleared when the limit is reached (this is subject to change). The cursor close behavior can be adjusted using the OPEN_CURSOR_CLOSE_COUNT QAQQINI file option. This option affects only the uniquely named cursor list. The list of 150 renamed ("dummy") cursors is always cleared when the limit is reached. For additional information, refer to Rochester Support Center knowledgebase document 16262423, Use of QAQQINI File. To link to document 1626423 immediately, click here (Document link: Database 'Rochester Support Line KnowledgeBase', View '1.0 DBManagement View\All', Document 'Use of QAQQINI File'). What Are the Impacts of Pseudo Closed Cursors? Increase use of storage: Each pseudo closed cursor represents a significant amount of storage used for the associated resources. A typical value is 1 Meg per cursor. The number of pseudo closed cursors can be reduced (see above) to decrease storage per job. Shared file locks even when all cursors are closed: As stated above, a pseudo closed cursor does not hold any record locks; however, it will continue to hold a shared lock on the file. In theory, the shared lock left on the file should have a minimal effect on other applications. Some operating system commands and all SQL statements that require an exclusive lock (DLTF, DROP TABLE, CLRPFM, and so on) will cause a pseudo closed cursor to be hard closed so that the lock is released. Each command that wants to force closing of pseudo closed cursors causes an event to be signaled if a lock conflict occurs. This event causes a program to run that hard-closes any pseudo closed cursors that hold the object. For this to function correctly, the maximum file wait time (WAITFILE) must be set to a value other than *IMMED. In general, a value of 1 second or greater is sufficient (actual time depends on system performance). Because hard closing of pseudo closed cursor is dependent on the implementation of each command, the behavior can vary between commands, operating system releases, and even PTFs. For example, the command ALCOBJ does not force pseudo closed cursors to hard close in R430 or later. It will return the message CPF3156 stating that the object cannot be allocated. Most IBM RPG/400® and COBOL programs using native (non-SQL) database file functions that require an exclusive lock also fail if pseudo closed cursors exist. How Do I Force Pseudo Closed Cursors to Hard Close? In R450 and later, you can force closing of the pseudo closed cursors of other jobs by specifying *RQSRLS for the Lock conflict action parameter of the ALCOBJ command. This option is new to R450 of OS/400. The syntax is: ALCOBJ OBJ((QCUSTCDT *FILE *EXCL)) CONFLICT(*RQSRLS) Note: This command does not close pseudo closed cursors scoped to the current job. So, for example, if you run a command that requires the file be hard closed (in addition to an exclusive lock) and the current job has pseudo closed cursors, then the *RQSRLS option will not work. You must use one of the techniques described below to hard close the cursors. The CHGPFCST command is one example of a command that requires a hard closed file. To close all pseudo closed cursors in the current job the application can: o Disconnect from the database server. o Execute the statements DISCONNECT ALL; CONNECT RESET to essentially disconnect and reconnect to the local database. Note: Both techniques have adverse effects on performance and should be avoided if at all possible. If a lock is still held after trying the ALCOBJ *RQSRLS, the lock is most likely held by an open cursor. Verify that the application actually issued the close cursor command and committed any open transaction. If using Client Access ODBC or IBM Toolbox for Java JDBC, verify that the lazy close option is disabled. How Can I Track Pseudo Closed Cursor Reuse? The database monitor utility, DBMON, can be used. The QQUCNT field on the open (1000 format) identifies a unique ODP (full open) while QQRCNT is incremented each time the ODP is used (a pseudo open). QQC11 of the 3010 records shows if the ODP is reusable. In R440, the SQL Performance Monitor feature of Operations Navigator can be used to easily check for statement reuse, reusable ODPs, number of full opens, and number of pseudo opens (reuse of pseudo closed cursor). Run the statement summary Basic Statement query from the Detailed Results tab. Starting debug on a job (STRDBG command) will add additional debug messages to the job's joblog. These messages can also be used to identify cursor reuse. What are Common Reasons for Not Reusing a Pseudo Closed Cursor? The query must be implemented with a reusable ODP. All attributes of the cursor must be the same (isolation level, ability to be updated, cursor type and so on). The SQL Statement text must match exactly (including white space and the value of any literals). Anything that might cause an access plan rebuild can also prevent reuse. Reuse of pseudo closed cursors is highly dependent on application design and, to a lesser extent, operating system PTF levels and Client PTF levels (ODBC, JDBC, and so on). Ideally, the database will always match a later execution of the same SQL statement to an existing pseudo closed cursor if one is available. This behavior is not documented nor guaranteed (problems are not accepted as defect). For best reuse applications need to follow these guidelines: o Use connection pooling. o Use parameterized SQL Statements. o Use a statement cache. -----Original Message----- From: ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx To: midrange-l@xxxxxxxxxxxx Sent: Mon, 12 Mar 2007 11:54 AM Subject: RE: ODBC/QZDASOINIT Messages Let us know what IBM finds. It's simply not possible to retrieve data out of a file without opening it. Elvis Celebrating 10-Years of SQL Performance Excellence -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of fbocch2595@xxxxxxx Sent: Monday, March 12, 2007 10:38 AM To: midrange-l@xxxxxxxxxxxx Subject: Re: ODBC/QZDASOINIT Messages Hi Elvis, but when an sql is completed it should not keep files open. Here's info on WINSQL, http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp I think it's time to start running traces and send them to IBM. Frank -----Original Message----- From: ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx To: midrange-l@xxxxxxxxxxxx Sent: Fri, 9 Mar 2007 4:30 PM Subject: RE: ODBC/QZDASOINIT Messages Well... I don't know what WINSQL is, but is it possible it caches the result set if you run the same exact SQL statement twice, and doesn't even hit the iSeries 2nd time around? Celebrating 10-Years of SQL Performance Excellence -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of fbocch2595@xxxxxxx Sent: Friday, March 09, 2007 1:44 PM To: midrange-l@xxxxxxxxxxxx Subject: Re: ODBC/QZDASOINIT Messages No, the same job but no open files. It's the only odbc job running on the iSeries. -----Original Message----- From: ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx To: midrange-l@xxxxxxxxxxxx Sent: Fri, 9 Mar 2007 2:24 PM Subject: RE: ODBC/QZDASOINIT Messages You got a different ODBC job 2nd time around? Celebrating 10-Years of SQL Performance Excellence -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of fbocch2595@xxxxxxx Sent: Friday, March 09, 2007 12:57 PM To: midrange-l@xxxxxxxxxxxx Subject: Re: ODBC/QZDASOINIT Messages Another question on these jobs. When I run the select statement (using winsql) and I get the results on my screen I wrkjob qzdasoinit and I see open files (via option 14 from wrkjob). Then, when I run the very same select statement again I don't see any open files. I'm waiting for IBM to get back to me on this but does anyone know why the job would display files then when the same statement is run again, no open files? Thanks, Frank -----Original Message----- From: gary.monnier@xxxxxxxxxxxxx To: midrange-l@xxxxxxxxxxxx Sent: Fri, 2 Mar 2007 3:16 PM Subject: RE: ODBC/QZDASOINIT Messages Frank, I can't say my example will satisfy what you want to do. Maybe you want to make your version of USR9805 be an inquiry message. It depends upon what you want to do. If all you want to do is force a message to be answered you can use the following. MONMSG(CPF000 MCH000 SQL000) EXEC(DO) RCVMSG MSGTYPE(*DIAG) MSGDTA(&MSGDTA) MSGID(&MSGID) + MSGF(&MSGF) MSGFLIB(&MSGFLIB) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) IF (&MSGID *NE ' ') THEN(DO) SNDUSRMSG MSGID(USR9805) MSGF(USERMSGLIB/USERMSGF) + VALUES(C) DFT(C) + MSGDTA(&JOBNAME) MSGTYPE(*INQ) TOUSR(*QSYSOPR) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) RETURN ENDDO RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) + MSGF(&MSGF) MSGFLIB(&MSGFLIB) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) IF (&MSGID *NE ' ') THEN(DO) SNDUSRMSG MSGID(USR9805) MSGF(USERMSGLIB/USERMSGF) + VALUES(C) DFT(C) + MSGDTA(&JOBNAME) MSGTYPE(*INQ) TOUSR(*QSYSOPR) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) RETURN ENDDO RETURN ENDDO -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of fbocch2595@xxxxxxx Sent: Friday, March 02, 2007 12:02 PM To: midrange-l@xxxxxxxxxxxx Subject: Re: ODBC/QZDASOINIT Messages So, just this clp and that's it? If that's all I'm gonna give it a try. I could run it right b4 our backup. Thanks -----Original Message----- From: gary.monnier@xxxxxxxxxxxxx To: midrange-l@xxxxxxxxxxxx Sent: Fri, 2 Mar 2007 2:23 PM Subject: RE: ODBC/QZDASOINIT Messages Frank, You can also change TESTCL to monitor for messages and resend them to a message queue of your choice. Something like this. Message ID USR9805 could state "ODBC error occurred in job 123456/QUSER/QZDASOINIT. You may have to end this job manually." CALLPRC A MONMSG(CPF000 MCH000 SQL000) EXEC(DO) RCVMSG MSGTYPE(*DIAG) MSGDTA(&MSGDTA) MSGID(&MSGID) + MSGF(&MSGF) MSGFLIB(&MSGFLIB) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) IF (&MSGID *NE ' ') THEN(DO) SNDPGMMSG MSGID(USR9805) MSGF(USERMSGLIB/USERMSGF) + MSGDTA(&JOBNAME) TOUSR(QSYSOPR) MSGTYPE(*INFO) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) + MSGF(&MSGF) MSGFLIB(&MSGFLIB) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) IF (&MSGID *NE ' ') THEN(DO) SNDPGMMSG MSGID(USR9805) MSGF(USERMSGLIB/USERMSGF) + MSGDTA(&JOBNAME) TOUSR(QSYSOPR) MSGTYPE(*INFO) MONMSG MSGID(CPF0000 MCH0000) EXEC(RETURN) ENDDO ________________________________________________________________________ AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
