|
Archives of the TeradataForumMessage Posted: Fri, 16 Dec 2005 @ 20:40:12 GMT
Dieter, Mike, and Donna, Thanks for the input. With your help, I have something working (which looks at a specified table and parses out all Teradata Objects (view, tables, macros) reference by it); however, the stored procedure keeps hanging (even though it is completing the work.) I can verify the work is completed by opening a seperate session and querying a set of processing tables I have the stored procedure populating as it runs. Included below is the DDL (rough draft) of the 4 processing tables I'm using (2 are only needed for trouble-shooting and the other 2 will be converted to Global temp tables when I'm done.) I've also included the SPL text and some rough comments. If anyone has the time to look at the code and isolate the bug, I would be very grateful. Thanks, Clay ************************************************************* --Just exists for trouble shooting purposes Create Table Objects_Request_Text ( RequestText varchar(31000) ) --Just Exists for trouble shooting purposes Create Table Objects_Loop_Count ( LoopCounter integer ,Counter integer ,MaxValue integer ) Unique Primary Index (LoopCounter, COunter, MaxValue); --Will be converted to a global temp CREATE SET TABLE Objects_Found ,NO FALLBACK , CHECKSUM = DEFAULT, LOG ( Databasename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, Tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC) UNIQUE PRIMARY INDEX ( Databasename ,Tablename ); --Will be converted to a global temp CREATE SET TABLE Valid_Objects_Found ,NO FALLBACK , CHECKSUM = DEFAULT, LOG ( Databasename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, Tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC) UNIQUE PRIMARY INDEX ( Databasename ,Tablename ); Replace Procedure MACRO_PARSE ( in Database_Name Char(30), in Table_name Char(30) ) SPLabel: Begin Declare Temp_Request_Text Varchar(31000); Declare NextPeriod Integer; Declare FindDatabaseName Integer; Declare MaxValue Integer; Declare Counter Integer; Declare LoopCounter Integer; Declare DBName Char(30); Declare TblName Char(30); Set LoopCounter = 0; Select trim(RequestText ) Into :Temp_Request_Text >From DBC.Tables Where Databasename = trim(:Database_name) and Tablename = trim(:Table_name); -- basically, I'm stripping out junk and replacing with a dummy value ('*')...of course, I'm having troubles stripping out ')' and '(' Loop_Remove_SemiColons: Loop If (Index(Temp_Request_Text, ';') = 0) or (Index(Temp_Request_Text, ';') is null) Then Leave Loop_Remove_SemiColons; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, ';')-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, ';')+1)); End Loop Loop_Remove_SemiColons; Loop_Remove_Spaces: Loop If (Index(Temp_Request_Text, ' ') = 0) or (Index(Temp_Request_Text, ' ') is null) Then Leave Loop_Remove_Spaces; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, ' ')-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, ' ')+1)); End Loop Loop_Remove_Spaces; Loop_Remove_Slash: Loop If (Index(Temp_Request_Text, '/') = 0) or (Index(Temp_Request_Text, '/') is null) Then Leave Loop_Remove_Slash; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, '/')-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, '/')+1)); End Loop Loop_Remove_Slash; Loop_Remove_Tab: Loop If (Index(Temp_Request_Text, (X'09')) = 0) or (Index(Temp_Request_Text, (X'09')) is null) Then Leave Loop_Remove_Tab; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, (X'09'))-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, (X'09'))+1)); End Loop Loop_Remove_Tab; Loop_Remove_Carriage_Return: Loop If (Index(Temp_Request_Text, (X'0A')) = 0) or (Index(Temp_Request_Text, (X'0A')) is null) Then Leave Loop_Remove_Carriage_Return; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, (X'0A'))-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, (X'0A'))+1)); End Loop Loop_Remove_Carriage_Return; Loop_Remove_LineFeed: Loop If (Index(Temp_Request_Text, (X'0D')) = 0) or (Index(Temp_Request_Text, (X'0D')) is null) Then Leave Loop_Remove_LineFeed; End if; Set Temp_Request_Text = (Substr(Temp_Request_Text, 1, Index (Temp_Request_Text, (X'0D'))-1)) || '*' || (Substring (Temp_Request_Text From Index(Temp_Request_Text, (X'0D'))+1)); End Loop Loop_Remove_LineFeed; -------------------------- --Now, look for '.' and insert left side (Databasename) and right side (tablename) into table Objects_Found Insert into Objects_Request_Text values (:Temp_Request_Text); BT; FindAllObjects: Loop If Index(Temp_Request_Text, '.') = 0 Then Leave FindAllObjects; End If; Set NextPeriod = index(Temp_Request_Text, '.'); Set FindDataBaseName = (Case when NextPeriod < 30 Then 0 Else (NextPeriod - 30) End); Set MaxValue = FindDatabasename; Set Counter = 0; While FindDatabaseName + Counter < NextPeriod Do -- Inserted for trouble shooting purposes Insert into Objects_Loop_Count Values (:LoopCounter, :Counter, :MaxValue); Set MaxValue = ( Case when Substr(Temp_Request_Text,FindDataBaseName+Counter,1) = '*' Then (FindDatabaseName + Counter) Else MaxValue End ); Set Counter = Counter + 1; End While; Set DBName = substr(Temp_Request_Text, (MaxValue + 1), (NextPeriod - MaxValue -1)); Set Temp_Request_Text = substring(Temp_Request_Text from (NextPeriod + 1)); Set TblName = substr(Temp_Request_Text,1,(index(Temp_Request_Text,'*')-1)); Insert into Objects_Found ( Databasename ,Tablename ) Select trim(:DBName) , trim(:TblName) Where (trim(:DBName), trim(:TblName)) not in ( Select Databasename, Tablename From Objects_Found ); Set LoopCounter = LoopCounter + 1; End Loop FindAllObjects; --Compare Objects Found Content to dbc.tables to weed out non-tables Insert into Valid_Objects_Found ( DatabaseName ,Tablename ) Select a.Databasename , a.Tablename >From dbc.tables b , Objects_Found a Where a.Databasename = b.Databasename and a.Tablename = b.Tablename; Et; --Inserted for trouble shooting purposes to prove that I am done Insert into Objects_Request_Text Values ('Done'); end; Call MACRO_PARSE ([some database],[some table]); --Assuming the above call locks up, run the following SQL in a seperate session to prove that the SPL finished its work before going off into la-la land Select * From Objects_Found; Select * From Valid_Objects_Found; Select * From Objects_Loop_Count; Select * From Objects_Request_Text;
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||