Archives of the TeradataForum

Message Posted: Fri, 16 Dec 2005 @ 20:40:12 GMT

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

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;

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