Re: Embeded SQL ODP problem

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

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