× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.