Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

trigger freezes

9 posts in Trigger Last posting was on 2011-08-24 17:56:10.0Z
Victor Polovets Posted on 2011-08-10 14:17:09.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
Subject: trigger freezes
Date: Wed, 10 Aug 2011 17:17:09 +0300
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
NNTP-Posting-Host: 195.189.104.187
Message-ID: <4e4292cd@solutions.advantagedatabase.com>
X-Trace: 10 Aug 2011 07:16:45 -0700, 195.189.104.187
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:489
Article PK: 1136550

Hi,

I have created one AFTER UPDATE trigger
this trigger works correct when few users are connected
when a lot of users are connected (more then 15-20), this trigger freezes
without any errors and warnings
after rebooting the advantage service trigger begins to work correct again
and after some time trigger freezes again without errors

CREATE TRIGGER tranlogc_after_upd
ON TRANLOGC
AFTER
UPDATE
BEGIN
DECLARE id String;
id = (select tlc_id from __new);
execute procedure CheckShipmentStatus(id);
END;


thanks


Edgar Sherman Posted on 2011-08-10 15:59:45.0Z
Date: Wed, 10 Aug 2011 09:59:45 -0600
From: Edgar Sherman <no@email.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: trigger freezes
References: <4e4292cd@solutions.advantagedatabase.com>
In-Reply-To: <4e4292cd@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.6.193.121
Message-ID: <4e42aaeb$1@solutions.advantagedatabase.com>
X-Trace: 10 Aug 2011 08:59:39 -0700, 10.6.193.121
Lines: 40
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:490
Article PK: 1136551

What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
procedure?

When you say freezes, is it the entire server freezes and nothing else
can be done or users just can not update the tranlogc table?

Without knowing the answers, I do have a theory. When I read this, the
first thing that came to mind was that "CheckShipmentStatus" stored
procedure is a DLL / AEP. It may be throwing an error or message box,
but since it is run in-process with ADS and the message/error is never
acknowledged, it may "hang" the worker thread. As more and more users
receive the error all of the worker threads eventually hang.
Don't know if the above will help, but it is a thought.

Edgar

On 8/10/2011 8:17 AM, Victor Polovets wrote:
> Hi,
>
> I have created one AFTER UPDATE trigger
> this trigger works correct when few users are connected
> when a lot of users are connected (more then 15-20), this trigger freezes
> without any errors and warnings
> after rebooting the advantage service trigger begins to work correct again
> and after some time trigger freezes again without errors
>
> CREATE TRIGGER tranlogc_after_upd
> ON TRANLOGC
> AFTER
> UPDATE
> BEGIN
> DECLARE id String;
> id = (select tlc_id from __new);
> execute procedure CheckShipmentStatus(id);
> END;
>
>
> thanks
>
>


Victor Polovets Posted on 2011-08-15 16:45:12.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com>
Subject: Re: trigger freezes
Date: Mon, 15 Aug 2011 19:45:12 +0300
Lines: 130
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
NNTP-Posting-Host: 195.189.104.187
Message-ID: <4e494cea$2@solutions.advantagedatabase.com>
X-Trace: 15 Aug 2011 09:44:26 -0700, 195.189.104.187
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:492
Article PK: 1136552

our customer uses advantage database server 10.0 DBF tables and .net
provider for accessing
all client applications written on C#
it is the first stored procedure in dictionary
this procedure is required for denormalization
also this procedure is very big and a lot of table are involved

CREATE PROCEDURE CheckShipmentStatus
(
refid CHAR ( 7 )
)
BEGIN
DECLARE inp_set cursor as select * from __input;
DECLARE id String, id_ref String;
DECLARE lc cursor, tr cursor, sl cursor;
--CACHE PREPARE ON;
OPEN inp_set;
FETCH inp_set;
TRY
OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
TD_AMSSCAC, TD_HSEREF
FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
WHILE FETCH tr DO
IF (tr.TL_SOURCE = 'T') THEN
execute procedure CheckTTStatus(inp_set.refid);
open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'M') THEN
execute procedure CheckMBStatus(inp_set.refid);
open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'B') THEN
execute procedure CheckBBStatus(inp_set.refid);
open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'C') THEN
execute procedure CheckCEStatus(inp_set.refid);
open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'I') THEN
execute procedure CheckISFStatus(inp_set.refid);
open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSE
open sl as select '' refid, '' hse from system.iota;
ENDIF;

if fetch sl then
open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
tr.TL_SOURCE);
if fetch lc then
update shiplist set
SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
endif;
close lc;
endif;
close sl;
END WHILE;
FINALLY
CLOSE tr;
END TRY;
CLOSE inp_set;
--CACHE PREPARE DEFAULT;
END;

"Edgar Sherman" <no@email.com> wrote in message
news:4e42aaeb$1@solutions.advantagedatabase.com...
> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
> procedure?
>
> When you say freezes, is it the entire server freezes and nothing else can
> be done or users just can not update the tranlogc table?
>
> Without knowing the answers, I do have a theory. When I read this, the
> first thing that came to mind was that "CheckShipmentStatus" stored
> procedure is a DLL / AEP. It may be throwing an error or message box, but
> since it is run in-process with ADS and the message/error is never
> acknowledged, it may "hang" the worker thread. As more and more users
> receive the error all of the worker threads eventually hang.
> Don't know if the above will help, but it is a thought.
>
> Edgar
>
> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>> Hi,
>>
>> I have created one AFTER UPDATE trigger
>> this trigger works correct when few users are connected
>> when a lot of users are connected (more then 15-20), this trigger freezes
>> without any errors and warnings
>> after rebooting the advantage service trigger begins to work correct
>> again
>> and after some time trigger freezes again without errors
>>
>> CREATE TRIGGER tranlogc_after_upd
>> ON TRANLOGC
>> AFTER
>> UPDATE
>> BEGIN
>> DECLARE id String;
>> id = (select tlc_id from __new);
>> execute procedure CheckShipmentStatus(id);
>> END;
>>
>>
>> thanks
>>
>>


Victor Polovets Posted on 2011-08-15 16:45:19.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com>
Subject: Re: trigger freezes
Date: Mon, 15 Aug 2011 19:45:19 +0300
Lines: 130
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
NNTP-Posting-Host: 195.189.104.187
Message-ID: <4e494cf3@solutions.advantagedatabase.com>
X-Trace: 15 Aug 2011 09:44:35 -0700, 195.189.104.187
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:493
Article PK: 1136553

our customer uses advantage database server 10.0 DBF tables and .net
provider for accessing
all client applications written on C#
it is the first stored procedure in dictionary
this procedure is required for denormalization
also this procedure is very big and a lot of table are involved

CREATE PROCEDURE CheckShipmentStatus
(
refid CHAR ( 7 )
)
BEGIN
DECLARE inp_set cursor as select * from __input;
DECLARE id String, id_ref String;
DECLARE lc cursor, tr cursor, sl cursor;
--CACHE PREPARE ON;
OPEN inp_set;
FETCH inp_set;
TRY
OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
TD_AMSSCAC, TD_HSEREF
FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
WHILE FETCH tr DO
IF (tr.TL_SOURCE = 'T') THEN
execute procedure CheckTTStatus(inp_set.refid);
open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'M') THEN
execute procedure CheckMBStatus(inp_set.refid);
open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'B') THEN
execute procedure CheckBBStatus(inp_set.refid);
open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'C') THEN
execute procedure CheckCEStatus(inp_set.refid);
open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'I') THEN
execute procedure CheckISFStatus(inp_set.refid);
open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSE
open sl as select '' refid, '' hse from system.iota;
ENDIF;

if fetch sl then
open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
tr.TL_SOURCE);
if fetch lc then
update shiplist set
SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
endif;
close lc;
endif;
close sl;
END WHILE;
FINALLY
CLOSE tr;
END TRY;
CLOSE inp_set;
--CACHE PREPARE DEFAULT;
END;

"Edgar Sherman" <no@email.com> wrote in message
news:4e42aaeb$1@solutions.advantagedatabase.com...
> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
> procedure?
>
> When you say freezes, is it the entire server freezes and nothing else can
> be done or users just can not update the tranlogc table?
>
> Without knowing the answers, I do have a theory. When I read this, the
> first thing that came to mind was that "CheckShipmentStatus" stored
> procedure is a DLL / AEP. It may be throwing an error or message box, but
> since it is run in-process with ADS and the message/error is never
> acknowledged, it may "hang" the worker thread. As more and more users
> receive the error all of the worker threads eventually hang.
> Don't know if the above will help, but it is a thought.
>
> Edgar
>
> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>> Hi,
>>
>> I have created one AFTER UPDATE trigger
>> this trigger works correct when few users are connected
>> when a lot of users are connected (more then 15-20), this trigger freezes
>> without any errors and warnings
>> after rebooting the advantage service trigger begins to work correct
>> again
>> and after some time trigger freezes again without errors
>>
>> CREATE TRIGGER tranlogc_after_upd
>> ON TRANLOGC
>> AFTER
>> UPDATE
>> BEGIN
>> DECLARE id String;
>> id = (select tlc_id from __new);
>> execute procedure CheckShipmentStatus(id);
>> END;
>>
>>
>> thanks
>>
>>


Edgar Sherman Posted on 2011-08-18 21:26:37.0Z
Date: Thu, 18 Aug 2011 15:26:37 -0600
From: Edgar Sherman <no@email.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: trigger freezes
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com> <4e494cf3@solutions.advantagedatabase.com>
In-Reply-To: <4e494cf3@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.6.193.121
Message-ID: <4e4d836e$1@solutions.advantagedatabase.com>
X-Trace: 18 Aug 2011 14:26:06 -0700, 10.6.193.121
Lines: 149
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:494
Article PK: 1136555

Not really sure what is going on to cause a freeze.

Are your "worker threads" Current or Max used equal to the Configured
value?

If so possibly increasing this value will help (or maybe delay the
freeze).
If it delays the freeze check the queries in all of these procedures to
ensure that there are matching indexes available.
When there are not matching indexes, Advantage may create a temporary
index for better performance, unfortunately the temporary index is
thrown away at the completion of the statement. Having a couple of user
run these types of statements is ok, but if you have a large number of
users running these types of statements the "index build" for each user
may start to overwhelm the worker threads.

Edgar

On 8/15/2011 10:45 AM, Victor Polovets wrote:
> our customer uses advantage database server 10.0 DBF tables and .net
> provider for accessing
> all client applications written on C#
> it is the first stored procedure in dictionary
> this procedure is required for denormalization
> also this procedure is very big and a lot of table are involved
>
> CREATE PROCEDURE CheckShipmentStatus
> (
> refid CHAR ( 7 )
> )
> BEGIN
> DECLARE inp_set cursor as select * from __input;
> DECLARE id String, id_ref String;
> DECLARE lc cursor, tr cursor, sl cursor;
> --CACHE PREPARE ON;
> OPEN inp_set;
> FETCH inp_set;
> TRY
> OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
> TD_AMSSCAC, TD_HSEREF
> FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
> WHILE FETCH tr DO
> IF (tr.TL_SOURCE = 'T') THEN
> execute procedure CheckTTStatus(inp_set.refid);
> open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> ELSEIF (tr.TL_SOURCE = 'M') THEN
> execute procedure CheckMBStatus(inp_set.refid);
> open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> ELSEIF (tr.TL_SOURCE = 'B') THEN
> execute procedure CheckBBStatus(inp_set.refid);
> open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> ELSEIF (tr.TL_SOURCE = 'C') THEN
> execute procedure CheckCEStatus(inp_set.refid);
> open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> ELSEIF (tr.TL_SOURCE = 'I') THEN
> execute procedure CheckISFStatus(inp_set.refid);
> open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> ELSE
> open sl as select '' refid, '' hse from system.iota;
> ENDIF;
>
> if fetch sl then
> open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
> tr.TL_SOURCE);
> if fetch lc then
> update shiplist set
> SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
> SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
> SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
> SL_AMSSCAC=tr.TD_AMSSCAC;
> endif;
> close lc;
> endif;
> close sl;
> END WHILE;
> FINALLY
> CLOSE tr;
> END TRY;
> CLOSE inp_set;
> --CACHE PREPARE DEFAULT;
> END;
>
>
>
>
>
> "Edgar Sherman"<no@email.com> wrote in message
> news:4e42aaeb$1@solutions.advantagedatabase.com...
>> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
>> procedure?
>>
>> When you say freezes, is it the entire server freezes and nothing else can
>> be done or users just can not update the tranlogc table?
>>
>> Without knowing the answers, I do have a theory. When I read this, the
>> first thing that came to mind was that "CheckShipmentStatus" stored
>> procedure is a DLL / AEP. It may be throwing an error or message box, but
>> since it is run in-process with ADS and the message/error is never
>> acknowledged, it may "hang" the worker thread. As more and more users
>> receive the error all of the worker threads eventually hang.
>> Don't know if the above will help, but it is a thought.
>>
>> Edgar
>>
>> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>>> Hi,
>>>
>>> I have created one AFTER UPDATE trigger
>>> this trigger works correct when few users are connected
>>> when a lot of users are connected (more then 15-20), this trigger freezes
>>> without any errors and warnings
>>> after rebooting the advantage service trigger begins to work correct
>>> again
>>> and after some time trigger freezes again without errors
>>>
>>> CREATE TRIGGER tranlogc_after_upd
>>> ON TRANLOGC
>>> AFTER
>>> UPDATE
>>> BEGIN
>>> DECLARE id String;
>>> id = (select tlc_id from __new);
>>> execute procedure CheckShipmentStatus(id);
>>> END;
>>>
>>>
>>> thanks
>>>
>>>
>
>
>


Victor Polovets Posted on 2011-08-22 16:51:37.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com> <4e494cf3@solutions.advantagedatabase.com> <4e4d836e$1@solutions.advantagedatabase.com>
Subject: Re: trigger freezes
Date: Mon, 22 Aug 2011 19:51:37 +0300
Lines: 170
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 212.1.93.54
Message-ID: <4e5288d4@solutions.advantagedatabase.com>
X-Trace: 22 Aug 2011 09:50:28 -0700, 212.1.93.54
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:495
Article PK: 1136556

I increased the "worker threads", now it is 128
and increased other parameters such as "tables", "index files" etc..
also I checked all SQLs, indexes are taking correctly

the same problem

I suspect that the problem is in DBF format of tables
maybe the converting in ADT format will solve problem ?

also i see that ADT formt has more options
as constraints etc ...

Victor

"Edgar Sherman" <no@email.com> wrote in message
news:4e4d836e$1@solutions.advantagedatabase.com...
> Not really sure what is going on to cause a freeze.
>
> Are your "worker threads" Current or Max used equal to the Configured
> value?
>
> If so possibly increasing this value will help (or maybe delay the
> freeze).
> If it delays the freeze check the queries in all of these procedures to
> ensure that there are matching indexes available.
> When there are not matching indexes, Advantage may create a temporary
> index for better performance, unfortunately the temporary index is thrown
> away at the completion of the statement. Having a couple of user run
> these types of statements is ok, but if you have a large number of users
> running these types of statements the "index build" for each user may
> start to overwhelm the worker threads.
>
> Edgar
>
> On 8/15/2011 10:45 AM, Victor Polovets wrote:
>> our customer uses advantage database server 10.0 DBF tables and .net
>> provider for accessing
>> all client applications written on C#
>> it is the first stored procedure in dictionary
>> this procedure is required for denormalization
>> also this procedure is very big and a lot of table are involved
>>
>> CREATE PROCEDURE CheckShipmentStatus
>> (
>> refid CHAR ( 7 )
>> )
>> BEGIN
>> DECLARE inp_set cursor as select * from __input;
>> DECLARE id String, id_ref String;
>> DECLARE lc cursor, tr cursor, sl cursor;
>> --CACHE PREPARE ON;
>> OPEN inp_set;
>> FETCH inp_set;
>> TRY
>> OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
>> TD_AMSSCAC, TD_HSEREF
>> FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
>> WHILE FETCH tr DO
>> IF (tr.TL_SOURCE = 'T') THEN
>> execute procedure CheckTTStatus(inp_set.refid);
>> open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> ELSEIF (tr.TL_SOURCE = 'M') THEN
>> execute procedure CheckMBStatus(inp_set.refid);
>> open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> ELSEIF (tr.TL_SOURCE = 'B') THEN
>> execute procedure CheckBBStatus(inp_set.refid);
>> open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> ELSEIF (tr.TL_SOURCE = 'C') THEN
>> execute procedure CheckCEStatus(inp_set.refid);
>> open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> ELSEIF (tr.TL_SOURCE = 'I') THEN
>> execute procedure CheckISFStatus(inp_set.refid);
>> open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> ELSE
>> open sl as select '' refid, '' hse from system.iota;
>> ENDIF;
>>
>> if fetch sl then
>> open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
>> tr.TL_SOURCE);
>> if fetch lc then
>> update shiplist set
>> SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
>> SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
>> SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>> SL_AMSSCAC=tr.TD_AMSSCAC;
>> endif;
>> close lc;
>> endif;
>> close sl;
>> END WHILE;
>> FINALLY
>> CLOSE tr;
>> END TRY;
>> CLOSE inp_set;
>> --CACHE PREPARE DEFAULT;
>> END;
>>
>>
>>
>>
>>
>> "Edgar Sherman"<no@email.com> wrote in message
>> news:4e42aaeb$1@solutions.advantagedatabase.com...
>>> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
>>> procedure?
>>>
>>> When you say freezes, is it the entire server freezes and nothing else
>>> can
>>> be done or users just can not update the tranlogc table?
>>>
>>> Without knowing the answers, I do have a theory. When I read this, the
>>> first thing that came to mind was that "CheckShipmentStatus" stored
>>> procedure is a DLL / AEP. It may be throwing an error or message box,
>>> but
>>> since it is run in-process with ADS and the message/error is never
>>> acknowledged, it may "hang" the worker thread. As more and more users
>>> receive the error all of the worker threads eventually hang.
>>> Don't know if the above will help, but it is a thought.
>>>
>>> Edgar
>>>
>>> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>>>> Hi,
>>>>
>>>> I have created one AFTER UPDATE trigger
>>>> this trigger works correct when few users are connected
>>>> when a lot of users are connected (more then 15-20), this trigger
>>>> freezes
>>>> without any errors and warnings
>>>> after rebooting the advantage service trigger begins to work correct
>>>> again
>>>> and after some time trigger freezes again without errors
>>>>
>>>> CREATE TRIGGER tranlogc_after_upd
>>>> ON TRANLOGC
>>>> AFTER
>>>> UPDATE
>>>> BEGIN
>>>> DECLARE id String;
>>>> id = (select tlc_id from __new);
>>>> execute procedure CheckShipmentStatus(id);
>>>> END;
>>>>
>>>>
>>>> thanks
>>>>
>>>>
>>
>>
>>


Edgar Sherman Posted on 2011-08-23 21:40:15.0Z
Date: Tue, 23 Aug 2011 15:40:15 -0600
From: Edgar Sherman <no@email.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: trigger freezes
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com> <4e494cf3@solutions.advantagedatabase.com> <4e4d836e$1@solutions.advantagedatabase.com> <4e5288d4@solutions.advantagedatabase.com>
In-Reply-To: <4e5288d4@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.6.193.121
Message-ID: <4e541e11$1@solutions.advantagedatabase.com>
X-Trace: 23 Aug 2011 14:39:29 -0700, 10.6.193.121
Lines: 176
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:496
Article PK: 1136560

Are your worker threads being maxed out? In other words, is the max
used = configured?

Edgar

On 8/22/2011 10:51 AM, Victor Polovets wrote:
> I increased the "worker threads", now it is 128
> and increased other parameters such as "tables", "index files" etc..
> also I checked all SQLs, indexes are taking correctly
>
> the same problem
>
> I suspect that the problem is in DBF format of tables
> maybe the converting in ADT format will solve problem ?
>
> also i see that ADT formt has more options
> as constraints etc ...
>
> Victor
>
> "Edgar Sherman"<no@email.com> wrote in message
> news:4e4d836e$1@solutions.advantagedatabase.com...
>> Not really sure what is going on to cause a freeze.
>>
>> Are your "worker threads" Current or Max used equal to the Configured
>> value?
>>
>> If so possibly increasing this value will help (or maybe delay the
>> freeze).
>> If it delays the freeze check the queries in all of these procedures to
>> ensure that there are matching indexes available.
>> When there are not matching indexes, Advantage may create a temporary
>> index for better performance, unfortunately the temporary index is thrown
>> away at the completion of the statement. Having a couple of user run
>> these types of statements is ok, but if you have a large number of users
>> running these types of statements the "index build" for each user may
>> start to overwhelm the worker threads.
>>
>> Edgar
>>
>> On 8/15/2011 10:45 AM, Victor Polovets wrote:
>>> our customer uses advantage database server 10.0 DBF tables and .net
>>> provider for accessing
>>> all client applications written on C#
>>> it is the first stored procedure in dictionary
>>> this procedure is required for denormalization
>>> also this procedure is very big and a lot of table are involved
>>>
>>> CREATE PROCEDURE CheckShipmentStatus
>>> (
>>> refid CHAR ( 7 )
>>> )
>>> BEGIN
>>> DECLARE inp_set cursor as select * from __input;
>>> DECLARE id String, id_ref String;
>>> DECLARE lc cursor, tr cursor, sl cursor;
>>> --CACHE PREPARE ON;
>>> OPEN inp_set;
>>> FETCH inp_set;
>>> TRY
>>> OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
>>> TD_AMSSCAC, TD_HSEREF
>>> FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
>>> WHILE FETCH tr DO
>>> IF (tr.TL_SOURCE = 'T') THEN
>>> execute procedure CheckTTStatus(inp_set.refid);
>>> open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> ELSEIF (tr.TL_SOURCE = 'M') THEN
>>> execute procedure CheckMBStatus(inp_set.refid);
>>> open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> ELSEIF (tr.TL_SOURCE = 'B') THEN
>>> execute procedure CheckBBStatus(inp_set.refid);
>>> open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> ELSEIF (tr.TL_SOURCE = 'C') THEN
>>> execute procedure CheckCEStatus(inp_set.refid);
>>> open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> ELSEIF (tr.TL_SOURCE = 'I') THEN
>>> execute procedure CheckISFStatus(inp_set.refid);
>>> open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> ELSE
>>> open sl as select '' refid, '' hse from system.iota;
>>> ENDIF;
>>>
>>> if fetch sl then
>>> open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
>>> tr.TL_SOURCE);
>>> if fetch lc then
>>> update shiplist set
>>> SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
>>> SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
>>> SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>> endif;
>>> close lc;
>>> endif;
>>> close sl;
>>> END WHILE;
>>> FINALLY
>>> CLOSE tr;
>>> END TRY;
>>> CLOSE inp_set;
>>> --CACHE PREPARE DEFAULT;
>>> END;
>>>
>>>
>>>
>>>
>>>
>>> "Edgar Sherman"<no@email.com> wrote in message
>>> news:4e42aaeb$1@solutions.advantagedatabase.com...
>>>> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
>>>> procedure?
>>>>
>>>> When you say freezes, is it the entire server freezes and nothing else
>>>> can
>>>> be done or users just can not update the tranlogc table?
>>>>
>>>> Without knowing the answers, I do have a theory. When I read this, the
>>>> first thing that came to mind was that "CheckShipmentStatus" stored
>>>> procedure is a DLL / AEP. It may be throwing an error or message box,
>>>> but
>>>> since it is run in-process with ADS and the message/error is never
>>>> acknowledged, it may "hang" the worker thread. As more and more users
>>>> receive the error all of the worker threads eventually hang.
>>>> Don't know if the above will help, but it is a thought.
>>>>
>>>> Edgar
>>>>
>>>> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>>>>> Hi,
>>>>>
>>>>> I have created one AFTER UPDATE trigger
>>>>> this trigger works correct when few users are connected
>>>>> when a lot of users are connected (more then 15-20), this trigger
>>>>> freezes
>>>>> without any errors and warnings
>>>>> after rebooting the advantage service trigger begins to work correct
>>>>> again
>>>>> and after some time trigger freezes again without errors
>>>>>
>>>>> CREATE TRIGGER tranlogc_after_upd
>>>>> ON TRANLOGC
>>>>> AFTER
>>>>> UPDATE
>>>>> BEGIN
>>>>> DECLARE id String;
>>>>> id = (select tlc_id from __new);
>>>>> execute procedure CheckShipmentStatus(id);
>>>>> END;
>>>>>
>>>>>
>>>>> thanks
>>>>>
>>>>>
>>>
>>>
>>>
>
>


Victor Polovets Posted on 2011-08-24 17:56:10.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com> <4e494cf3@solutions.advantagedatabase.com> <4e4d836e$1@solutions.advantagedatabase.com> <4e5288d4@solutions.advantagedatabase.com> <4e541e11$1@solutions.advantagedatabase.com>
Subject: Re: trigger freezes
Date: Wed, 24 Aug 2011 20:56:10 +0300
Lines: 192
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 212.1.93.54
Message-ID: <4e553ae8@solutions.advantagedatabase.com>
X-Trace: 24 Aug 2011 10:54:48 -0700, 212.1.93.54
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:497
Article PK: 1136557

I used Advantage configuration utilites for changing

also one more thing
we have 17 dictionaries with the same table structure
each dictionary needs for definite office
I use triggers only in one dictionary and only on one table.
This dictionary has the largest tables

Victor

"Edgar Sherman" <no@email.com> wrote in message
news:4e541e11$1@solutions.advantagedatabase.com...
> Are your worker threads being maxed out? In other words, is the max used
> = configured?
>
> Edgar
>
> On 8/22/2011 10:51 AM, Victor Polovets wrote:
>> I increased the "worker threads", now it is 128
>> and increased other parameters such as "tables", "index files" etc..
>> also I checked all SQLs, indexes are taking correctly
>>
>> the same problem
>>
>> I suspect that the problem is in DBF format of tables
>> maybe the converting in ADT format will solve problem ?
>>
>> also i see that ADT formt has more options
>> as constraints etc ...
>>
>> Victor
>>
>> "Edgar Sherman"<no@email.com> wrote in message
>> news:4e4d836e$1@solutions.advantagedatabase.com...
>>> Not really sure what is going on to cause a freeze.
>>>
>>> Are your "worker threads" Current or Max used equal to the Configured
>>> value?
>>>
>>> If so possibly increasing this value will help (or maybe delay the
>>> freeze).
>>> If it delays the freeze check the queries in all of these procedures to
>>> ensure that there are matching indexes available.
>>> When there are not matching indexes, Advantage may create a temporary
>>> index for better performance, unfortunately the temporary index is
>>> thrown
>>> away at the completion of the statement. Having a couple of user run
>>> these types of statements is ok, but if you have a large number of users
>>> running these types of statements the "index build" for each user may
>>> start to overwhelm the worker threads.
>>>
>>> Edgar
>>>
>>> On 8/15/2011 10:45 AM, Victor Polovets wrote:
>>>> our customer uses advantage database server 10.0 DBF tables and .net
>>>> provider for accessing
>>>> all client applications written on C#
>>>> it is the first stored procedure in dictionary
>>>> this procedure is required for denormalization
>>>> also this procedure is very big and a lot of table are involved
>>>>
>>>> CREATE PROCEDURE CheckShipmentStatus
>>>> (
>>>> refid CHAR ( 7 )
>>>> )
>>>> BEGIN
>>>> DECLARE inp_set cursor as select * from __input;
>>>> DECLARE id String, id_ref String;
>>>> DECLARE lc cursor, tr cursor, sl cursor;
>>>> --CACHE PREPARE ON;
>>>> OPEN inp_set;
>>>> FETCH inp_set;
>>>> TRY
>>>> OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
>>>> TD_AMSSCAC, TD_HSEREF
>>>> FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
>>>> WHILE FETCH tr DO
>>>> IF (tr.TL_SOURCE = 'T') THEN
>>>> execute procedure CheckTTStatus(inp_set.refid);
>>>> open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> ELSEIF (tr.TL_SOURCE = 'M') THEN
>>>> execute procedure CheckMBStatus(inp_set.refid);
>>>> open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> ELSEIF (tr.TL_SOURCE = 'B') THEN
>>>> execute procedure CheckBBStatus(inp_set.refid);
>>>> open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> ELSEIF (tr.TL_SOURCE = 'C') THEN
>>>> execute procedure CheckCEStatus(inp_set.refid);
>>>> open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> ELSEIF (tr.TL_SOURCE = 'I') THEN
>>>> execute procedure CheckISFStatus(inp_set.refid);
>>>> open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> ELSE
>>>> open sl as select '' refid, '' hse from system.iota;
>>>> ENDIF;
>>>>
>>>> if fetch sl then
>>>> open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
>>>> tr.TL_SOURCE);
>>>> if fetch lc then
>>>> update shiplist set
>>>> SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
>>>> SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
>>>> SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
>>>> where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
>>>> SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
>>>> SL_AMSSCAC=tr.TD_AMSSCAC;
>>>> endif;
>>>> close lc;
>>>> endif;
>>>> close sl;
>>>> END WHILE;
>>>> FINALLY
>>>> CLOSE tr;
>>>> END TRY;
>>>> CLOSE inp_set;
>>>> --CACHE PREPARE DEFAULT;
>>>> END;
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Edgar Sherman"<no@email.com> wrote in message
>>>> news:4e42aaeb$1@solutions.advantagedatabase.com...
>>>>> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
>>>>> procedure?
>>>>>
>>>>> When you say freezes, is it the entire server freezes and nothing else
>>>>> can
>>>>> be done or users just can not update the tranlogc table?
>>>>>
>>>>> Without knowing the answers, I do have a theory. When I read this,
>>>>> the
>>>>> first thing that came to mind was that "CheckShipmentStatus" stored
>>>>> procedure is a DLL / AEP. It may be throwing an error or message box,
>>>>> but
>>>>> since it is run in-process with ADS and the message/error is never
>>>>> acknowledged, it may "hang" the worker thread. As more and more users
>>>>> receive the error all of the worker threads eventually hang.
>>>>> Don't know if the above will help, but it is a thought.
>>>>>
>>>>> Edgar
>>>>>
>>>>> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>>>>>> Hi,
>>>>>>
>>>>>> I have created one AFTER UPDATE trigger
>>>>>> this trigger works correct when few users are connected
>>>>>> when a lot of users are connected (more then 15-20), this trigger
>>>>>> freezes
>>>>>> without any errors and warnings
>>>>>> after rebooting the advantage service trigger begins to work correct
>>>>>> again
>>>>>> and after some time trigger freezes again without errors
>>>>>>
>>>>>> CREATE TRIGGER tranlogc_after_upd
>>>>>> ON TRANLOGC
>>>>>> AFTER
>>>>>> UPDATE
>>>>>> BEGIN
>>>>>> DECLARE id String;
>>>>>> id = (select tlc_id from __new);
>>>>>> execute procedure CheckShipmentStatus(id);
>>>>>> END;
>>>>>>
>>>>>>
>>>>>> thanks
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>
>>


Victor Polovets Posted on 2011-08-15 16:43:46.0Z
From: "Victor Polovets" <victor.polovets@logisticaldatasolutions.com>
Newsgroups: Advantage.Trigger
References: <4e4292cd@solutions.advantagedatabase.com> <4e42aaeb$1@solutions.advantagedatabase.com>
Subject: Re: trigger freezes
Date: Mon, 15 Aug 2011 19:43:46 +0300
Lines: 129
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090
NNTP-Posting-Host: 195.189.104.187
Message-ID: <4e494cea@solutions.advantagedatabase.com>
X-Trace: 15 Aug 2011 09:44:26 -0700, 195.189.104.187
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:491
Article PK: 1136554

our customer uses advantage database server 10.0 DBF tables and .net
provider for accessing
all client applications written on C#
it is the first stored procedure in dictionary
this procedure is required for denormalization
also this procedure is very big and a lot of table are involved

CREATE PROCEDURE CheckShipmentStatus
(
refid CHAR ( 7 )
)
BEGIN
DECLARE inp_set cursor as select * from __input;
DECLARE id String, id_ref String;
DECLARE lc cursor, tr cursor, sl cursor;
--CACHE PREPARE ON;
OPEN inp_set;
FETCH inp_set;
TRY
OPEN tr as select TL_CUST, TL_SOURCE, TD_MASTNO, TD_MSTSCAC, TD_HOUSE,
TD_AMSSCAC, TD_HSEREF
FROM TRANLOGH, TRANLOGD WHERE TL_ID=TD_ID AND TL_ID = inp_set.refid;
WHILE FETCH tr DO
IF (tr.TL_SOURCE = 'T') THEN
execute procedure CheckTTStatus(inp_set.refid);
open sl as select SL_T_REF refid, SL_T_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'M') THEN
execute procedure CheckMBStatus(inp_set.refid);
open sl as select SL_M_REF refid, SL_M_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'B') THEN
execute procedure CheckBBStatus(inp_set.refid);
open sl as select SL_B_REF refid, SL_B_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'C') THEN
execute procedure CheckCEStatus(inp_set.refid);
open sl as select SL_C_REF refid, SL_C_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSEIF (tr.TL_SOURCE = 'I') THEN
execute procedure CheckISFStatus(inp_set.refid);
open sl as select SL_I_REF refid, SL_I_HSE hse from shiplist
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
ELSE
open sl as select '' refid, '' hse from system.iota;
ENDIF;

if fetch sl then
open lc as execute procedure GetShipmentDates(sl.refid, sl.hse,
tr.TL_SOURCE);
if fetch lc then
update shiplist set
SL_INCUST=lc.SL_INCUST,SL_RCVDOC=lc.SL_RCVDOC,SL_PLINV=lc.SL_PLINV,SL_FRTRLS=lc.SL_FRTRLS,
SL_DOSENT=lc.SL_DOSENT,SL_GOODDLV=lc.SL_GOODDLV,SL_PICKUP=lc.SL_PICKUP,
SL_SHEDDLV=lc.SL_SHEDDLV,SL_CLOSE=lc.SL_CLOSE,SL_CUSRLS=lc.SL_CUSRLS,PENDEXAM=lc.PENDEXAM,OUTDELV=lc.OUTDELV,SL_RDYPKUP=lc.SL_RDYPKUP
where SL_CUST=tr.TL_CUST and SL_MASTNO=tr.TD_MASTNO and
SL_MSTSCAC=tr.TD_MSTSCAC and SL_HOUSE=tr.TD_HOUSE and
SL_AMSSCAC=tr.TD_AMSSCAC;
endif;
close lc;
endif;
close sl;
END WHILE;
FINALLY
CLOSE tr;
END TRY;
CLOSE inp_set;
--CACHE PREPARE DEFAULT;
END;

"Edgar Sherman" <no@email.com> wrote in message
news:4e42aaeb$1@solutions.advantagedatabase.com...
> What is the procedure checkshipmentstatus? Is it a DLL or SQL stored
> procedure?
>
> When you say freezes, is it the entire server freezes and nothing else can
> be done or users just can not update the tranlogc table?
>
> Without knowing the answers, I do have a theory. When I read this, the
> first thing that came to mind was that "CheckShipmentStatus" stored
> procedure is a DLL / AEP. It may be throwing an error or message box, but
> since it is run in-process with ADS and the message/error is never
> acknowledged, it may "hang" the worker thread. As more and more users
> receive the error all of the worker threads eventually hang.
> Don't know if the above will help, but it is a thought.
>
> Edgar
>
> On 8/10/2011 8:17 AM, Victor Polovets wrote:
>> Hi,
>>
>> I have created one AFTER UPDATE trigger
>> this trigger works correct when few users are connected
>> when a lot of users are connected (more then 15-20), this trigger freezes
>> without any errors and warnings
>> after rebooting the advantage service trigger begins to work correct
>> again
>> and after some time trigger freezes again without errors
>>
>> CREATE TRIGGER tranlogc_after_upd
>> ON TRANLOGC
>> AFTER
>> UPDATE
>> BEGIN
>> DECLARE id String;
>> id = (select tlc_id from __new);
>> execute procedure CheckShipmentStatus(id);
>> END;
>>
>>
>> thanks
>>
>>