[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sapdb-general
Subject:    AW: CAT_CACHE_SUPPLY in Recursive function -> Lock wainting
From:       "Zabach, Elke" <elke.zabach () sap ! com>
Date:       2004-06-28 14:37:17
Message-ID: 235EE6AC8AE9264CBF763FC6749E270105F6AB () dewdfe22 ! wdf ! sap ! corp
[Download RAW message or body]

Matteo Gattoni wrote:
> 
> HI,
> the problem 'General error;-915 POS(375) No more
> memory:CAT_CACHE_SUPPLY.' is due to the recursive call of the function.
> Deleting it from the body of the function, in this way
> 
> DROP FUNCTION FIX_TREE_D
> //
> CREATE FUNCTION FIX_TREE_D( father fixed(9,0), child fixed, prefix CHAR)
> RETURNS VARCHAR
> AS
> VAR C_TREE_PATH_D VARCHAR(1000); Appo fixed(9,0); treeN VARCHAR(1000);
> BEGIN
> IF prefix = '' OR prefix is null THEN
> set C_TREE_PATH_D = '/' || father || '/' || child || '/'
> ELSE
> set C_TREE_PATH_D = prefix || child || '/';
> Appo = child;
> /* Mi occupo dei figli */
> UPDATE PSF.ACTIVITY_RELATIONSHIP
> set C_TREE_PATH = ''
> WHERE E_ACT_K_ACTIVITY1 = :Appo;
> RETURN C_TREE_PATH_D;
> END;
> //
> 
> I'm able to compile the function.
> 
> But the function, however, doesn't do what it has to. Calling it in this
> way,
> 
> UPDATE PSF.ACTIVITY_RELATIONSHIP set C_TREE_PATH =
> FIX_TREE_D(E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2, '')
> WHERE E_ACT_K_ACTIVITY1 = 7 AND E_ACT_K_ACTIVITY2 = 8
> 
> the system seems to be waiting endesly for something; I think that the
> first update locks the table, so that the update in the function isn't
> able to act properly and waits for the unlocking by the first one.

As no two tasks/threads/processes/transactions handle the user-called update and the \
function-called update, but both are handled within one task, there is no chance to \
have a lock-wait within the update itself. But could it be that you created the \
table/function or inserted within one task/ SQLStudio and did the update within \
another one? That is the only chance I see to wait for a lock.
Or didn't you wait for a lock, but had a log full-problem exactly at that time?

(Second part of answer see below)

> 
> The table used is created in the following way
> 
> CREATE TABLE ACTIVITY_RELATIONSHIP
> (E_ACT_K_ACTIVITY1 NUMBER(9,0) NOT NULL
> ,E_ACT_K_ACTIVITY2 NUMBER(9,0) NOT NULL
> ,E_REL_K_RELATIONSHIP_TYPE NUMBER(9,0)
> ,D_START DATE NOT NULL
> ,O_MODIFY DATE NOT NULL
> ,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
> ,C_NOTE VARCHAR2(250)
> ,D_END DATE
> ,C_TREE_PATH VARCHAR2 (4000)
> )TABLESPACE TBS_PSF_DATI
> 
> and contains the following records.
> 
> INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
> E_REL_K_RELATIONSHIP_TYPE,
> D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END )
> VALUES (
> 2, 7, 1, TO_Date( '06/08/2004 12:43:36 PM', 'MM/DD/YYYY HH:MI:SS AM'),
> TO_Date( '06/08/2004 12:43:36 PM', 'MM/DD/YYYY HH:MI:SS AM')
> , '1', NULL, '/1/2/7/', NULL)
> //
> INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
> E_REL_K_RELATIONSHIP_TYPE,
> D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END )
> VALUES (
> 7, 8, 1, TO_Date( '06/08/2004 12:44:32 PM', 'MM/DD/YYYY HH:MI:SS AM'),
> TO_Date( '06/08/2004 12:44:32 PM', 'MM/DD/YYYY HH:MI:SS AM')
> , '1', NULL, '/1/2/7/8/', NULL)
> //
> INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
> E_REL_K_RELATIONSHIP_TYPE,
> D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END )
> VALUES (
> 1, 2, 1, TO_Date( '06/08/2004 12:44:49 PM', 'MM/DD/YYYY HH:MI:SS AM'),
> TO_Date( '06/08/2004 12:44:49 PM', 'MM/DD/YYYY HH:MI:SS AM')
> , '1', NULL, '/1/2/', NULL)
> //
> INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
> E_REL_K_RELATIONSHIP_TYPE,
> D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END )
> VALUES (
> 7, 4, 1, TO_Date( '06/08/2004 12:45:07 PM', 'MM/DD/YYYY HH:MI:SS AM'),
> TO_Date( '06/08/2004 12:45:07 PM', 'MM/DD/YYYY HH:MI:SS AM')
> , '1', NULL, '/1/2/7/4/', NULL)
> //
> INSERT INTO ACTIVITY_RELATIONSHIP ( E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2,
> E_REL_K_RELATIONSHIP_TYPE,
> D_START, O_MODIFY, E_OPERATOR_K_OPERATOR, C_NOTE, C_TREE_PATH, D_END )
> VALUES (
> 5, 6, 1, TO_Date( '06/11/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
> TO_Date( '06/11/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , '1', NULL, '/5/6/', NULL)
> //
> 
> My hypothesis fail making a simplify example of the algorithm.
> 
> The table used is
> DROP TABLE PIPPOZ
> //
> CREATE TABLE PIPPOZ
> (Prova NUMBER(9,0),
> valo NUMBER(9,0))
> //
> 
> containing the following two elements
> INSERT INTO PIPPOZ (Prova,valo) values (1,1)
> //
> INSERT INTO PIPPOZ (Prova,valo) values (2,2)
> //
> 
> I create the function
> 
> drop function PIPPOS
> //
> create function PIPPOS(zuppa NUMBER) RETURNS NUMBER
> AS
> VAR zuppaI NUMBER(9,0);
> BEGIN
> zuppaI = zuppa;
> UPDATE PPORTAL.PIPPOZ set valo = :zuppaI+1 where Prova = 2;
> RETURN zuppaI;
> END;
> 
> and call it in this way
> UPDATE PIPPOZ SET VALO = PIPPOS(4) WHERE Prova = 1
> 
> In this example everything goes as excepted.
> 
> What's wrong with the first function (FIX_TREE_D)? Why does it seem that
> the locking is different between the first and the example?
> 
> However, I need to call recursevly the function; so the problem with
> Cat_cache_supply (reported in the previous email here following attached)
> still remains.
> At the same way, I still have problem fecthing dynamic cursors (as
> reported in the other mail here attached). Having more information about
> that could solve everything for me.
> 
> Thank in advance.
> Bye,
> Matteo
> 
> 
> ----- Original Message -----
> From: "Matteo Gattoni" <matteo.gattoni@icteam.it>
> To: <maxdb@lists.mysql.com>
> Sent: Tuesday, June 22, 2004 5:17 PM
> Subject: CAT_CACHE_SUPPLY in Recursive function
> 
> 
> HI,
> I'm still trying to fetch datas in a recursive function. Waiting for
> your responses to my latest email (that follows), I've tried changing the
> structure of my algorithm.
> Now I use a function that is called in an update statement like:
> 
> UPDATE PSF.ACTIVITY_RELATIONSHIP set C_TREE_PATH =
> FIX_TREE_D(E_ACT_K_ACTIVITY1, E_ACT_K_ACTIVITY2, '')
> WHERE E_ACT_K_ACTIVITY1 = 7 AND E_ACT_K_ACTIVITY2 = 8
> 
> The function is declared as follow:
> 
> DROP FUNCTION FIX_TREE_D
> //
> CREATE FUNCTION FIX_TREE_D( father fixed, child fixed, prefix CHAR)
> RETURNS VARCHAR
> AS
> VAR C_TREE_PATH_D VARCHAR(1000); Appo fixed(9,0); treeN VARCHAR(1000);
> BEGIN
> IF prefix = '' OR prefix is null THEN
> set C_TREE_PATH_D = '/' || father || '/' || child || '/'
> ELSE
> set C_TREE_PATH_D = prefix || child || '/';
> /* Mi occupo dei figli */
> UPDATE PSF.ACTIVITY_RELATIONSHIP
> set C_TREE_PATH = FIX_TREE_D(:Appo, E_ACT_K_ACTIVITY2, ltrim(rtrim
> (:C_TREE_PATH_D)))
> WHERE E_ACT_K_ACTIVITY1 = :Appo;
> RETURN C_TREE_PATH_D;
> END;
> //
> 
> At compiling time, I receive the following error: General error;-915
> POS(375) No more memory:CAT_CACHE_SUPPLY.



We could reproduce the problem now. The bug will be fixed asap.

Elke
SAP Labs Berlin 




> I read that I ought to change the database parameter CAT_CACHE_SUPPLY so
> that (MAXUSERTASKS +1)*_CAT_CACHE_MINSIZE <= CAT_CACHE_SUPPLY. So I did,
> but nothing changes.
> 
> What's wrong?
> 
> Is possible to create recursive function accessing tables directly or
> using cursors?
> Or recursive functions are not allowed to?
> 
> Thanks,
> Matteo
> 
> 
> ----- Original Message -----
> From: "Matteo Gattoni" <matteo.gattoni@icteam.it>
> To: <maxdb@lists.mysql.com>
> Sent: Monday, June 21, 2004 7:23 PM
> Subject: Fetch from Dynamic cursor
> 
> 
> Hi,
> I'm using Dynamic cursor declared in the following way:
> 
> stmt = 'DECLARE Children' || child || ' CURSOR FOR SELECT
> E_ACT_K_ACTIVITY2 from PSF.ACTIVITY_RELATIONSHIP  '
> > > '  WHERE E_ACT_K_ACTIVITY1 = ' || child;
> EXECUTE stmt;
> 
> When I try to fetch it using
> 
> stmt = 'FETCH Childrens' || child || ' INTO :n_child';
> EXECUTE stmt;
> 
> the variable n_child is always null; even if the select isn't empty.
> 
> I've tried to declare the cursor in a different way:
> 
> $CURSOR = 'Children' || child;
> DECLARE :$CURSOR CURSOR FOR SELECT E_ACT_K_ACTIVITY2 from
> PSF.ACTIVITY_RELATIONSHIP
> WHERE E_ACT_K_ACTIVITY1 = :child;
> 
> and using FETCH :$CURSOR INTO :n_child;
> But I'm not able to compile the procedure because MaxDb says General
> error;-5006 POS(1223) Missing identifier.
> 
> Finally, I've tried like that:
> 
> $CURSOR = 'Children' || child;
> stmt = 'DECLARE ' || :$CURSOR || ' CURSOR FOR SELECT E_ACT_K_ACTIVITY2
> from PSF.ACTIVITY_RELATIONSHIP
> WHERE E_ACT_K_ACTIVITY1 = :child';
> EXECUTE stmt;
> 
> But it says General error;-7045 POS(1084) Parameter spec not allowed
> during compilation.
> 
> How could I create cursors with different name depending on a variable?
> And how could I fetch them?
> 
> I need this purpose because I have a recursive procedure that every time
> create a cursor, using different name I hope that it wouldn't rewrite the
> old istance.
> 
> Thanks in advance.
> Bye,
> Matteo

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=sapdb-general@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]