Archives of the TeradataForum

Message Posted: Thu, 22 Dec 2005 @ 15:20:05 GMT

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

Geoffrey, Dieter, and All,

It turns out that my issue with the stored procedure 'hanging' had to do with my version of the ODBC driver. I inadvertantly loaded the 3.5 ODBC driver on my machine, even though my client utilites are TTU 7.1. This turns out to be a very bad thing to do...I still need to reload all my utilities. Anyhow, after loading the correct version of the driver (3.3.0.12) on my system, everything runs fine now.

If interested, here is the latest version of the stored procedure which looks at any view, macro, trigger, or join index and parses out all objects referenced. This SPL isn't recursive, so if a macro references a view, then the view will be returned as a referenced object (as opposed to the tables that make up the view.) In addition, I don't know of anyway to search through the requesttext of SPL statements. Is that possible? Also, I had to make my :Temp_Request_Text variable 31999 bytes versus 32000 bytes (I couldn't get around an error about max size limits if I declared this variable as 32000 bytes.)

Anyhow, this SPL does reference both dbc.table and dbc.TextTbl if a CreateText statement is greater than 32000 bytes. It is kinda slow, but it seems to be working fine. Any feedback regarding error checking, performance inprovements, etc. would be greatly appreciated.

Thanks,

Clay

     CREATE SET GLOBAL TEMPORARY 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 )
     On Commit Delete Rows;

     CREATE SET GLOBAL TEMPORARY 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 )
     On Commit Preserve Rows;

     Replace Procedure DBA_Find_All_Ref_Objects
     (
     in Database_Name Char(30),
     in Table_name Char(30)
     )
     -- This procedure requires a valid database name and object (view,
     macro,
     join index, or trigger) name to be passed as parameters
     -- Call Example = Call DBA_Find_All_ref_Objects ('Databasename',
     'ObjectName');
     -- Then you must issue the following SQL in the same Session = Select *
     >From Valid_Objects_Found;

     SPLabel:

     Begin

     Declare Temp_Request_Text Varchar(31999);
     Declare NextPeriod Integer;
     Declare FindDatabaseName Integer;
     Declare MaxValue Integer;
     Declare Counter Integer;
     Declare LoopCounter Integer;
     Declare DBName Char(30);
     Declare TblName Char(30);
     Declare PeriodorStar Integer;
     Declare OverflowLoopMaxCount Integer;
     Declare OverflowFlag Char(1);
     Declare HandleOverflowCounter Integer;
     Declare TextLength Integer;
     Declare Tempvalue Char(1);

     Set OverflowLoopMaxCount = 0;
     Set HandleOverflowCounter = 0;
     Set LoopCounter  = 0;
     Set OverFlowFlag = 'N';

     Bt;

     Select 'Y'
     Into  :OverflowFlag
     >From  DBC.tvm
     ,  DBC.TextTbl
     ,  DBC.dbase
     Where tvm.DatabaseId = dbase.DatabaseId
     and  dbase.Databasename = trim(:Database_name)
     and   tvm.TVMName  = trim(:Table_name)
     and  tvm.TVMId        = texttbl.TextID
     and  texttbl.LineNo = 1
     and  texttbl.TextType = 'R';

     Select  LineNo
     Into   :OverflowLoopMaxCount
     From
     (
     Select Max(LineNo)
     >From  DBC.tvm
       Left Outer Join
       DBC.TextTbl
        on tvm.TVMId        = texttbl.TextID
        and Texttbl.TextType = 'R'
     ,  DBC.dbase
     Where tvm.DatabaseId = dbase.DatabaseId
     and  dbase.Databasename = trim(:Database_name)
     and   tvm.TVMName  = trim(:Table_name)
     ) dt (LineNo);

     HandleOverflow:

     Loop

     If ((HandleOverFlowCounter = OverflowLoopMaxCount) or ((OverFlowFlag =
     'N'
     and HandleOverFlowCounter = 1))) Then
      Leave HandleOverflow;
     End if;

     Set HandleOverflowCounter = HandleOverflowCounter + 1;

     Select  trim(
       Case When :OverFlowFlag = 'N'
        Then trim(tvm.CreateText)
        Else  trim(texttbl.TextString)
       End
       )
     Into   :Temp_Request_Text
     >From  DBC.tvm
       Left Outer Join
       DBC.TextTbl
        on tvm.TVMId  = texttbl.TextID
        and texttbl.TextType  = 'R'
        and texttbl.LineNo  = :HandleOverflowCounter
     ,  DBC.dbase
     Where tvm.DatabaseId = dbase.DatabaseId
     and  dbase.Databasename = trim(:Database_name)
     and   tvm.TVMName  = trim(:Table_name);

     --this loop removes double quotes, all other loops replace specified
     values
     with a '*'

     Loop_Remove_DoubleQuotes:

     Loop

     If (Index(Temp_Request_Text, '"') = 0) or (Index(Temp_Request_Text, '"')
     is
     null) Then
      Leave Loop_Remove_DoubleQuotes;
     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_DoubleQuotes;

     Set TextLength = Char_Length(Temp_Request_Text);
     Set Counter  = 0;

     While TextLength >= Counter
      Do

      Set Counter = Counter + 1;
      Set TempValue = Upper(Substr(Temp_Request_Text,counter,1));

      Set Temp_Request_Text =
      (
      (case when Counter > 1 then Substr(Temp_Request_Text, 1, Counter - 1)
     else
     '' end)
      ||
      (
      Case when TempValue in
     ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R'
     ,'S','T','U','V','W','X','W','Y','Z','.','1','2','3','4','5','6','7','8'
     ,'9','0','_')
      Then TempValue
      Else '*'
      End
      )
      ||
      (Substr(Temp_Request_Text, Counter + 1, TextLength))
      );

     End While;

     --------------------------

     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;
     Set PeriodorStar  = 0;

      While FindDatabaseName + Counter < NextPeriod
      Do

      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 PeriodorStar = (
           Case when (index(Temp_Request_Text,'.') <
     index(Temp_Request_Text,'*')) and (index(Temp_Request_Text,'.')<>0)
            Then  index(Temp_Request_Text,'.')
            Else  index(Temp_Request_Text,'*')
           End
           );
      Set TblName = substr(Temp_Request_Text,1,(PeriodorStar-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
        )
      and  (trim(:DBName) || trim(:TblName)) <> (trim(:Database_Name) ||
     trim(:Table_Name));

      Set LoopCounter = LoopCounter + 1;

     End Loop FindAllObjects;

     Delete from Valid_Objects_Found;

     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;

     End Loop HandleOverflow;

     Et;

     end;

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