All Forums Database
Sandeepyadav 52 posts Joined 09/13
30 Sep 2014
DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Hi,
 
I am try to find the views that don't have lock on tables in it. But when i try to find defination of these view from dbc.tables, view defination is not complete in requesttext field , its getting truncated.
 
Can anybody help . How can i find full defination from dbc.tables.
 
Thanks in Advanc
Sandeep.

Thanks, -Sandeep.
Raja_KT 1246 posts Joined 07/09
30 Sep 2014

Even DBC.TableText  or DBC.tvm  ?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Boopathi15 11 posts Joined 12/13
30 Sep 2014

Use dbc tables to extract the table list and prepare the list of "show view" statements. 
Set the sql assistant to export data and also check the option of "export all the resultset to single file". 
Run the statements at once and all the definitions are exported to single file. 

Sandeepyadav 52 posts Joined 09/13
30 Sep 2014

Hi Raja,
Thanks for your comment,
I am able to find it in dbc.tabletext view that is on dbc.texttbl. 
can you please tell about the texttype column values C,R in dbc.texttbl and what these values are refer to ?
 
Thanks- Sandeep.
 
 

Thanks, -Sandeep.

Raja_KT 1246 posts Joined 07/09
30 Sep 2014

If I am not wrong:  CreateTxtOverFlow = 'C' or RequestTxtOverFlow = 'R' . You can check these three tables
DBC.tvm,DBC.texttbl,DBC.dbase, how they are joined

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Sandeepyadav 52 posts Joined 09/13
30 Sep 2014

Actully , DBC.texttbl table column texttype has two values C,R and the view on this table is DBC.tabletext with condition Texttbl.TextType = 'R' . i am just wana know why this view filter only R type of rows.
 
It will be great if you can tell what you mean by CreateTxtOverFlow and RequestTxtOverFlow ?
Thanks- Sandeep.
 

Thanks, -Sandeep.

Raja_KT 1246 posts Joined 07/09
30 Sep 2014

Maybe this expert's articel helping you? I have not checked it .
http://teradataforum.com/teradata/20060227_164410.htm

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

frnewbrough 41 posts Joined 03/08
30 Sep 2014

The request text field is like that and if you do an alter on the table then poof even if the full request text were there it would be gone. :-)
The best option is what Boopathi15 suggested. You can optionally rebuild the ddl from info in the data dictionary. It is real pain and 
Heres an example of a udf I put together to recreate just the partitioning part of the ddl.
 

REPLACE FUNCTION SYSLIB.get_partition_stmt (stmt VARCHAR(8000)) 
  RETURNS  VARCHAR(8000)
  SPECIFIC SYSLIB.get_partition_stmt 
  RETURNS NULL ON NULL INPUT
  CONTAINS SQL
  COLLATION INVOKER
  INLINE TYPE 1
 RETURN  
 (CASE WHEN SUBSTRING( OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) = '' THEN 
 SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) 
 END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE')  FROM 1 FOR 1) = 'P' THEN OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) = '' THEN 
 SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) 
 END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE')  ELSE  'PARTITION BY('||OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) = '' THEN 
 SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),')  )) 
 END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE') ||')' END) ;

Here is how to use it:

SELECT 

 DataBaseName

,TABLENAME

,get_partition_stmt (ConstraintText)

FROM

dbc.indexconstraintsV;

 

It may not be 100% and its ugly but it worked for what I needed to use it for. I think you can see though that its not a pleasant process. 

 

Here is something I scripted that you may find useful:

 

https://www.evernote.com/shard/s134/sh/2bb520e5-9f45-4de7-ac55-ed4698aba28f/44bc2aa3f4d57f2365f4a4ee48edd98f

 

-Fred

 

frnewbrough 41 posts Joined 03/08
30 Sep 2014

Raja_KT Thanks for the tip I've never used DBC.texttbl 

frnewbrough 41 posts Joined 03/08
30 Sep 2014

Actually  DBC.tvm has the same problem with RequestText being replaced with Alter statements. I explored this previously.
 
 
 

Sandeepyadav 52 posts Joined 09/13
30 Sep 2014

Thanks a lot Raja :)

Thanks, -Sandeep.

Sandeepyadav 52 posts Joined 09/13
30 Sep 2014

Thanks Fred :)

Thanks, -Sandeep.

frnewbrough 41 posts Joined 03/08
30 Sep 2014

Welcome. 

You must sign in to leave a comment.