Re: Embeded SQL ODP problem

["Schadd D Gray" <list@xxxxxxxxxxxxx>]

Had to call IBM on this.  It is a "feature" of the optimizer.  The only way
I could solve this was to change the SQL statement to a prepared statement.
SQL then closes the ODP after use.  I will take a hit on the prepare (vs.
static), but it is better than 300 ODP's!!!

Thanks for everyone's suggestions,
Schadd Gray
Damon Technologies, Inc.
www.damontech.com

----- Original Message ----- 
From: "Schadd D Gray" <list@xxxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Tuesday, December 16, 2003 1:48 PM
Subject: Re: Embeded SQL ODP problem


> I have ran it in debug.  The optimizer just says SQL 7914 "ODP not
deleted".
> It does not say why.
>
> Here is a snapshot of the joblog:
>
> 1 rows fetched from cursor ALLCUST.
> 1 rows fetched from cursor ALLCUST.
> 1 rows fetched from cursor ALLCUST.
> Row not found for ALLCUST.
> ODP not deleted.
> Cursor ALLCUST closed.
>
> Thanks,
> Schadd Gray
> Damon Technologies, Inc.
> www.damontech.com
>
> ----- Original Message ----- 
> From: "Haas, Matt" <Matt.Haas@xxxxxxxxxxx>
> To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
> Sent: Tuesday, December 16, 2003 1:22 PM
> Subject: RE: Embeded SQL ODP problem
>
>
> > Have you tried running the job in debug mode to get more information
from
> the query optimizer? It may tell you why it's not deleting the ODP.
> >
> > Matt
> >
> > -----Original Message-----
> > From: Schadd D Gray [mailto:list@xxxxxxxxxxxxx]
> > Sent: Tuesday, December 16, 2003 2:09 PM
> > To: RPG programming on the AS400 / iSeries
> > Subject: Re: Embeded SQL ODP problem
> >
> >
> > It is a named activation group (QILE).  I did not want to encure the
> > activation group startup and shutdown, as it would hinder web
performance.
> > >From what I have read, I thought CLOSQLCSR(*ENDMOD) meant that the
cursor
> > and all ODP's are deleted when the program ends.  I guess my question
> should
> > have been, why is this not the case here?
> >
> > Thanks,
> > Schadd Gray
> > Damon Technologies, Inc.
> > www.damontech.com
> >
> > ----- Original Message ----- 
> > From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
> > To: "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
> > Sent: Tuesday, December 16, 2003 12:51 PM
> > Subject: RE: Embeded SQL ODP problem
> >
> >
> > > Schadd,
> > >
> > > I'll bet your program is running in a named activation group that has
> not
> > > yet been reclaimed.  You'll need to RCLACTGRP to close out the
existing
> > copy
> > > of your program.
> > >
> > > One easy fix (probably not the best way) is to recompile the program
to
> > > ACTGRP(*NEW), which should ensure that the activation group is
destroyed
> > > whenever the program ends.  Note that there is some overhead
associated
> > with
> > > creating a new activation group, but I wouldn't expect it to be a
> problem
> > in
> > > most cases.
> > >
> > > Eric DeLong
> > > Sally Beauty Company
> > > MIS-Project Manager (BSG)
> > > 940-898-7863 or ext. 1863
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Schadd D Gray [mailto:list@xxxxxxxxxxxxx]
> > > Sent: Tuesday, December 16, 2003 12:38 PM
> > > To: RPG programming on the AS400 / iSeries
> > > Subject: Embeded SQL ODP problem
> > >
> > >
> > > I have a SQLRPGLE with a select statement in it.  I have compiled this
> > > program with CLOSQLCSR(*ENDMOD).  However, after the program finishes
I
> > look
> > > at the Open Data Paths for my job and the files in the SELECT
statement
> > are
> > > still open.  I close the cursor prior to exiting the program.
> > >
> > > I put the program in debug and my joblog shows the message SQL7914
"ODP
> > not
> > > deleted".
> > >
> > > The main place this is causing me issue is in JDBC calls to these
> SQLRPGLE
> > > programs, which are registered as stored procedures.  The system jobs
> for
> > > JDBC end up having 300+ ODP's.
> > >
> > > Is there a way to ensure the ODP's are deleted?
> > >
> > > By the way, this is a hard coded SELECT statement (Not a prepared
> > > statement).
> > >
> > > OS - V5R2
> > >
> > > Thanks,
> > > Schadd Gray
> > > Damon Technologies, Inc.
> > > www.damontech.com
> > > _______________________________________________
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/rpg400-l.
> > >
> > >
> > > _______________________________________________
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/rpg400-l.
> > >
> > >
> >
> > _______________________________________________
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> > _______________________________________________
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>