Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2003 @ 08:17:04 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Stored Procedure that Loops
 
From:   Dieter Noeth

Figge, Jason wrote:

  If you used the .RUN approach from within BTEQ and the script you execute contains subsequent .RUN statements, then you'll eventuall run out of stack space and/or memory.  



I know NCR added a warning to the BTEQ manual some time ago:

"If a run file contains a RUN command to run itself, BTEQ generates an out-of-memory message after several iterations. Always avoid such a loop."

But did you really ever had that kind of problem? "Several" seems to be a very large number ;-)

I usually show a recursive approach, adding the warning from the manual...


Dieter


/*** file recursion1.btq ***/
.logon ...;

CREATE VOLATILE TABLE vt_hierarchy, NO FALLBACK, NO LOG
(DatabaseName char(30) not null primary key,
  OwnerName    char(30),
  Level        integer,
  rnk          integer,
  Sort         varchar(1024)
) ON COMMIT PRESERVE ROWS
;

INSERT INTO vt_hierarchy
   SELECT DatabaseName, OwnerName, 0, RANK(DatabaseName ASC), ''
   FROM dbc.Databases
   GROUP BY OwnerName
;

.RUN FILE = recursion2.btq;

/*** file recursion 2.btq ***/
UPDATE vt_hierarchy
FROM
  (SELECT
     child.DatabaseName
    ,parent.Level
    ,parent.Sort || (child.rnk (format '999')) AS NewSort
   FROM
       vt_hierarchy parent
     JOIN
       vt_hierarchy child
     ON parent.DatabaseName = child.OwnerName
   WHERE parent.Level = (SEL MAX(Level) FROM vt_hierarchy)
   AND child.DatabaseName <> 'dbc'
  ) AS tmp
SET Level = tmp.Level + 1
    ,Sort = tmp.NewSort
WHERE vt_hierarchy.DatabaseName = tmp.DatabaseName
;

.IF ACTIVITYCOUNT > 0 THEN .RUN FILE = Recursion2.btq;

SELECT SUBSTRING('              ' FROM 1 FOR Level * 2) || DatabaseName
AS Hierarchy
FROM vt_hierarchy ORDER BY Sort
;

DROP TABLE vt_hierarchy;

/*** As BTEQ does not return to the last file, everything following the .RUN is done only once  ***/


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023