All Forums Database
ratzesberger 7 posts Joined 02/11
01 Sep 2012
Problems with dynamic creation of Volatile tables

Guys,

 

Playing with some engine designs that require volatile tables structures based on existing tables structures on a system. The number of unique table structures is very high, so minimal customization should be required or it becomes a sustainability problem.

Very simple example: 

Take any existing table structure and create an empty Volatile table. Sounds simple:

 

CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 

WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

 

Simple enough.

Here is the issue: On certain structures tht have named SIs on them, the above statement will fail:

 

CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 

WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

Sep 1, 2012 7:37:28 AM groovy.sql.Sql eachRow

WARNING: Failed to execute: CREATE VOLATILE TABLE myTemp AS SourceDb.SourceTable 

WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

 because: [Teradata Database] [TeraJDBC 14.00.00.13] [Error 5339] [SQLState HY000] Index name not allowed for volatile table.

     

We dont need nor want any SIs on the Volatile table. Looked through all the Database documentation for 13.10 (only JDBC driver above is 14.x) could not find anything to prevent this.

 

Any ideas how do deal with that? 

 

 

 

 

 

twitter: @ratzesberger https://twitter.com/ratzesberger
Fred 1096 posts Joined 08/04
01 Sep 2012

Since you are specifying NoPI (vs. trying to copy the existing PI):

CREATE VOLATILE TABLE myTemp AS (SELECT * FROM SourceDb.SourceTable)

WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

ratzesberger 7 posts Joined 02/11
01 Sep 2012

Love it when the simple solution right in front of your own eyes is making you go in cricles. 

Much appriciated!

twitter: @ratzesberger https://twitter.com/ratzesberger

MadMac 14 posts Joined 10/04
01 Sep 2012

I consider this to be a documentation issue (it could be an implementation issue - but we don't know).

The CT AS documentation for 13.10 is not at all clear about the behavior of indexes following the table creation. One could assume it does or does not. Example: what is the behavior for a table with a referencing Join Index?

It might well be in some other manual an an inherited behavior from CT SET AS [Tablename], but if INDEX Creation is going to follow the Table Reference in the default CT Volatile [name] AS [tablename], it should say that.  

ratzesberger 7 posts Joined 02/11
01 Sep 2012

And to add on to this the work around that is great for this one usecase, opens up another set of issues: What about the likes of Stats?

Creating a copy of an existing table without SIs but with existing Stats should be possible but this solution prevents stats from coming with the table definition. Same for compression and other features... 

twitter: @ratzesberger https://twitter.com/ratzesberger

ulrich 816 posts Joined 09/09
03 Sep 2012

just to play arround - not fully tested, no exception handling etc. maybe more index types to consider but a SP might become handy for this...

REPLACE PROCEDURE test_db_uli.p_create_tb_copy
(IN source_db VARCHAR(30),
 IN source_tb VARCHAR(30),
 IN target_db VARCHAR(30),
 IN target_tb VARCHAR(30),
 IN with_data CHAR(1),
 IN WITH_STATS CHAR(1),
 IN WITH_NPI CHAR(1),
 OUT status VARCHAR(2000 )
 )
BEGIN
     
     DECLARE CNT SMALLINT;
     DECLARE SQL_TEXT VARCHAR(10000);
     
     
     SELECT COUNT(*) INTO :cnt
     FROM dbc.indices 
     WHERE databasename = :source_db
                       AND tablename = :source_tb
                       AND columnposition = 1
                       AND indextype = 'S'
     ;

            
     IF ( cnt = 0 ) 
           THEN 
                BEGIN    
                    SET SQL_TEXT = 'Create table ' !! target_db !! '.' !! target_tb !! ' as ' !! source_db !! '.' !! source_tb !! ' WITH ' !! CASE WHEN with_data = 'N' THEN ' NO ' ELSE ' ' END !! 'DATA ' !! CASE WHEN WITH_STATS = 'Y' THEN 'AND STATISTICS' ELSE '' END !! CASE WHEN WITH_NPI = 'Y' THEN 'NO PRIMARY INDEX' ELSE '' END ;    
                    CALL DBC.SYSEXECSQL( :SQL_TEXT);	      
                    SET status = 'NO INDEX' !! SQL_TEXT;
                END;
     ELSE           
            BEGIN           
 
                 SET SQL_TEXT = 'Create table ' !! target_db !! '.' !! target_tb !! ' as ' !! source_db !! '.' !! source_tb !! ' WITH NO DATA ' !! CASE WHEN WITH_STATS = 'Y' THEN 'AND STATISTICS' ELSE '' END !! CASE WHEN WITH_NPI = 'Y' THEN 'NO PRIMARY INDEX' ELSE '' END ;    
                 CALL DBC.SYSEXECSQL( :SQL_TEXT);	      
                 SET status = 'NO INDEX' !! SQL_TEXT;
                                        
                 SET SQL_TEXT = 'delete from test_db_uli.gt_drop_index_stmt ';
                 
                 CALL DBC.SYSEXECSQL( :SQL_TEXT);	     
                 
                 SET SQL_TEXT = '
                 insert into test_db_uli.gt_drop_index_stmt 
                 WITH RECURSIVE base (databasename, tablename, indexnumber, columnname, columnposition)
                    AS
                    (
                    SELECT databasename, tablename, indexnumber, CAST(columnname AS VARCHAR(1000)), columnposition
                    FROM DBC.Indices
                    WHERE columnposition = 1
                                    AND INDEXTYPE = ''s''
                    UNION ALL
                    SELECT i.databasename, 
                           i.tablename,
                           i.indexnumber,
                           b.columnname !! '','' !! TRIM(i.columnname),
                           i.columnposition
                    FROM DBC.Indices i
                         JOIN 
                         base b
                          ON b.databasename = i.databasename
                             AND b.tablename = i.tablename
                             AND b.indexnumber = i.indexnumber
                             AND b.columnposition + 1 = i.columnposition
                    ) 
                    SELECT ''DROP INDEX ('' !! columnname !! '') ON '!! target_db !! '.' !! target_tb !! ';'' AS txt
                    FROM base 
                    WHERE databasename = ''' !! source_db !! '''
                    AND tablename = ''' !!  source_tb !! '''
                    QUALIFY columnposition = MAX(columnposition) OVER (PARTITION BY databasename, tablename, indexnumber)
                  ';
   
                    CALL DBC.SYSEXECSQL( :SQL_TEXT);	     
                                 
                    FOR STCURSOR AS RESULTSET CURSOR  FOR
                     SELECT TXT FROM  test_db_uli.gt_drop_index_stmt  
                   
                     DO
                         BEGIN
                                SET SQL_TEXT = STCURSOR.txt;
                                CALL DBC.SYSEXECSQL( :SQL_TEXT);	      
                         END;
                            
                     END FOR;
                             
                 IF ( with_data = 'Y' ) 
                    THEN
                       BEGIN
                            SET SQL_TEXT = 'INSERT INTO '!! target_db !! '.' !! target_tb !! ' select * from  ' !! source_db !! '.' !! source_tb; 
                            CALL DBC.SYSEXECSQL( :SQL_TEXT);	      
                       END;
                  END IF;
                 SET status = 'with index ' !!  SQL_TEXT;
 
              END;
              
      END IF;
      
 END;

In case secondary indies exists the table will be created with no data option.

then the indices are droped and if with data was specified the table gets populated - durch die brust ins auge

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
03 Sep 2012

Forgot the GT for the drop index statements...

CREATE GLOBAL TEMPORARY TABLE gt_drop_index_stmt ( txt VARCHAR(1000)) ON COMMIT PRESERVE ROWS;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ratzesberger 7 posts Joined 02/11
03 Sep 2012

Nice! Thanks a lot!

And I was hoping a single line CT VOLITABLE TABLE AS might do... ;-)

Was just rewriting that old TeraCompress tool from years ago, but with dynamic sampling - hence the Volatile tables. The first workaround works better in this case - even though not perfect, since it requires the least amount of code to be maintained. 

As Michael said the documentation definitelly needs to speel this out.

 

twitter: @ratzesberger https://twitter.com/ratzesberger

ratzesberger 7 posts Joined 02/11
03 Sep 2012

The forums software here needs a "Like" or "Thank You" button on posts. Would make search so much smarter when you can actually ranked liked responses higher... 

Sorry for the transgression - I could not help... ;-)

twitter: @ratzesberger https://twitter.com/ratzesberger

neilotoole 78 posts Joined 11/08
05 Sep 2012

@ratzesberger: it'll be coming soon... add any other items to the wishlist here:

 

http://forums.teradata.com/forum/general/wish-list-for-forum-development#comment-23180

DevX Platform Architect

Deepak Agrawal 5 posts Joined 06/11
14 May 2013

Hi,
 
My client has given requirement to create a GT using WITH DATA option. However, I am getting error "5509: Global temporary table cannot be created WITH DATA". I am using database release 13.10.
Creation VT instead of GT can be a possible workaround I can see. Any expert opinion is greatly be appreciated on this. 
 
 

kulpreetkhanna 1 post Joined 06/13
06 Jun 2013

Hi, I have a question. I am running a create volatile table with on commit preserve rows followed by 2 insert into statements.
Select * returns the 2 entered rows on my machine and login whereas the same code is not returning any rows for a friend of mine.
Could you please suggest if some option needs to be updated or administrator needs to update some setting for this user?

Shelley 28 posts Joined 09/10
06 Jun 2013

Volatile tables and their data are only available to the session that creates them. They are not available to other sessions.
Your friend needs to run the create volatile table (including the on commit rows) plus the 2 inserts in order to be able to see them in his session.
Also he needs to make sure he has all the appropriate privileges if he is pulling data from another table to insert into the volatile table.
--Shelley

ditaya 1 post Joined 11/13
12 Nov 2013

Hi,
I am new to Teradata and I have been working lately on creating volatile tables. Am currently using Teradata Studio Express for Mac. (Teradata 13.10). I have not been successful in creating volatile tables. This is a sample query that am trying to execute:

  CREATE VOLATILE TABLE test_volatile

(

  HIGHEST_SALARY INTEGER,

  AVERAGE_SALARY INTEGER,

  LOWEST_SALARY INTEGER

)

    primary index(highest_salary)

    ON COMMIT PRESERVE ROWS;

 

The output message that I get is as follows:

 

Executed as Single statement.  

Elapsed time = 00:00:00.108 

STATEMENT 1: COMMIT  completed. 0 rows processed.  
However, when i try to query data from the above table:
select * from test_volatile / SELECT COUNT(*) from test_volatile;
I get the following message:

Executed as Single statement.  Failed [3807 : 42S02] Object 'test_volatile' does not exist. 

Elapsed time = 00:00:00.079 

STATEMENT 1: Select Statement failed. 

 

Am I doing something wrong here? 

 

- Ditaya

Raja_KT 1246 posts Joined 07/09
12 Nov 2013

Hi,
Are you querying using the same session?
Cheers,
Raja

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.

M.Saeed Khurram 544 posts Joined 09/12
12 Nov 2013

Hi,
The first thing is I am not sure you can create an index on volatile table. But if still it gives you that 0 rows have been processes. then the table is their, I think it is a problem with your default database. Try by qualifying the name of table with you user database.
 

Khurram

Raja_KT 1246 posts Joined 07/09
12 Nov 2013

Hi,

 

You can create index on volatile table. I am not pretty sure also if it applies the same that a min of 512 bytes perm space be assigned for a user or db for table header;

 

In TD 14.10, there are even more features added for gtt, vt.

 

Cheers,

Raja

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.

You must sign in to leave a comment.