Strange connection issue
User experienced -934 error when running an INSERT...SELECT statement from a remote database within a stored procedure on IDS 11.70.fc5/AIX 6.1, though the same SQL worked fine outside the procedure. The issue was suspected to be related to scheduler tasks killing stale connections, but disabling the scheduler did not resolve it. No clear resolution was documented in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.70.fc5
Aix 6.1
I have a stored procedure that deletes rows from a table, and then inserts
into that table from a remote database...
insert into tasks
select * from tacods@ods_lnk:tasks;
the server ods_lnk is a second instance on the same box in our development
environment, which this is... When executed in the stored procedure I get
an -934 error. If I take the exact sql and run in dbaccess outside the
stored procedure, it works correctly.
Any suggestions as to where to look?
Thanks ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
Do you have sysdbopen() procedures on the remote server?
On Wed, Jul 25, 2012 at 3:14 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> IDS 11.70.fc5
> Aix 6.1
>
> I have a stored procedure that deletes rows from a table, and then inserts
> into that table from a remote database...
>
> insert into tasks
> select * from tacods@ods_lnk:tasks;>
> the server ods_lnk is a second instance on the same box in our development
> environment, which this is... When executed in the stored procedure I get
> an -934 error. If I take the exact sql and run in dbaccess outside the
> stored procedure, it works correctly.
>
> Any suggestions as to where to look?
>
> Thanks ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3074d9ae196a1e04c5aae9fd
No I don't ... I'm wondering if this is an 11.70.fc5 issues ... this same
thing is running on my QA server .. without issue, it's 11.70.fc3
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Fernando Nunes" <domusonline@gmail.com>
To: ids@iiug.org
Date: 07/25/2012 01:36 PM
Subject: Re: Strange connection issue [27828]
Sent by: ids-bounces@iiug.org
Do you have sysdbopen() procedures on the remote server?
On Wed, Jul 25, 2012 at 3:14 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> IDS 11.70.fc5
> Aix 6.1
>
> I have a stored procedure that deletes rows from a table, and then
inserts
> into that table from a remote database...
>
> insert into tasks
> select * from tacods@ods_lnk:tasks;>
> the server ods_lnk is a second instance on the same box in our
development
> environment, which this is... When executed in the stored procedure I
get
> an -934 error. If I take the exact sql and run in dbaccess outside the
> stored procedure, it works correctly.
>
> Any suggestions as to where to look?
>
> Thanks ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3074d9ae196a1e04c5aae9fd
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Peter, Check and see if you have the scheduler task enabled that kills off stale or not used connections. Thanks Kernoal
Ok .. you are a genius .. ok, maybe that is going a little far .... Shut the entire scheduler down for a second .. and works fine .. So, is this a new feature .. or a bug ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "KERNOAL STEPHENS" <informixdba@gmail.com> To: ids@iiug.org Date: 07/25/2012 02:07 PM Subject: Re: Strange connection issue [27830] Sent by: ids-bounces@iiug.org Peter, Check and see if you have the scheduler task enabled that kills off stale or not used connections. Thanks Kernoal ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
ok .. you're not a genius .. I'm an idiot ... that didn't change anything ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "KERNOAL STEPHENS" <informixdba@gmail.com> To: ids@iiug.org Date: 07/25/2012 02:07 PM Subject: Re: Strange connection issue [27830] Sent by: ids-bounces@iiug.org Peter, Check and see if you have the scheduler task enabled that kills off stale or not used connections. Thanks Kernoal ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Haaha Funny msgs I am receiving. Hehe ------Original Message------ From: Peter_Logan@spartanstores.com Sender: ids-bounces@iiug.org To: ids@iiug.org ReplyTo: ids@iiug.org Subject: Re: Strange connection issue [27832] Sent: Jul 25, 2012 20:31 ok .. you're not a genius .. I'm an idiot ... that didn't change anything .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "KERNOAL STEPHENS" <informixdba@gmail.com> To: ids@iiug.org Date: 07/25/2012 02:07 PM Subject: Re: Strange connection issue [27830] Sent by: ids-bounces@iiug.org Peter, Check and see if you have the scheduler task enabled that kills off stale or not used connections. Thanks Kernoal ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Sent from my BlackBerry® wireless device
Peter, Stick with genius, it sounds better for both of us. Kernoal