User carlos a. ibarra - TeradataQuestions

Answer by Carlos A. Ibarra for Teradata SQL Assistant takes forever to interrupt large answerset

2010-05-10T21:57:55Z

This only happens if I submit the query with F9 (run in parallel) instead of F5. According to Teradata, they can't tell if more resultsets are coming, so they can't abort the query and have no choice but to ignore the remaining rows. To get the desired behavior, use F5.

Teradata SQL Assistant takes forever to interrupt large answerset

2010-02-11T03:30:41Z

I am using Teradata SQL Assistant version 13.0.0.08 on Windows. I think it's the version written in C#.

When I run a query that returns more than 2000 rows, which is my max in the options for rows to display, and it asks me if I want to cancel after 2000 rows and I click Yes, I expect it to immediately give me back control. Instead, it still takes a long time (sometimes minutes) to come back. During this time, SQL Assistant is unresponsive and the query pane background is light red, like it's busy. I have checked the network activity and it appears that the rest of the rows are still being read from Teradata. Very annoying.

This happens the same way whether I am using the .Net provider or ODBC to connect to Teradata. I have looked through all the options but nothing seems like it would help. Tomorrow I will try installing a slightly newer version that I think is available.

How can I fix this?

Answer by Carlos A. Ibarra for Difference between 'Delete' and 'Delete All'

2010-03-30T23:16:55Z

There is no difference. At one point the manuals seemed to imply that DELETE ALL was different from DELETE but that was later understood to be a misinterpretation.

See Dieter Noeth's answer to this question

Answer by Carlos A. Ibarra for Teradata metadata help

2010-03-17T19:22:42Z

If DBQL is set up to log with enough detail in your system, you can use the DBC.QryLogObjects view to determine what database columns are accessed by your query. The tricky part if finding your query in the log. The following technique uses "label queries" around your query to bracket what you want to examine in the log. Make sure you wait 10 minutes or whatever your DBQL flush interval is before running the summary query.

-- These selects should bracket your query
select 'BeginQueryTracking001';
-- your query here:
  Select a.columnA,b.ColumnB from subjectarea1.table1 a left outer join subjectarea2.table2 b on a.id = b.id;
select 'EndQueryTracking001';

-- Wait 10 minutes before issuing this summary query that returns your object names.
-- Make sure that the BeginQueryTracking001/EndQueryTracking001 match what you used above.
select distinct O.objectdatabasename,O.objecttablename,O.objectcolumnname
 from  dbc.qrylog L1
 join   dbc.qrylog L2 
    on L1.sessionid=l2.sessionid
    and L1.starttime < L2.starttime
    and L1.querytext = 'select ''BeginQueryTracking001'';'
    and L2.querytext = 'select ''EndQueryTracking001'';'
join dbc.qrylog M
  on M.sessionid = L1.sessionid
  and  L1.starttime < M.starttime and M.starttime < L2.starttime
join dbc.qrylogobjects O 
   on M.queryid = o.queryid
and O.objectcolumnname is not null
order by 1,2,3

Speeding up SELECT MAX(timestamp_column) FROM T, when T is loaded via continuous TPump

2010-03-13T15:57:19Z

How can I speed up a query that's currently doing a full table scan to get the maximum value of a timestamp column?

I have a large table with a timestamp column. There is a TPump running all day with many sessions inserting and updating rows, serialized using the PI. I need to run the equivalent of:

SELECT MAX(ts) FROM TABLE1

many times a day, which does a full table scan. Is there any index that can be added or something that can be done to speed this up?

A normal NUSI will not be used by Teradata because it is not ordered.
A value-ordered NUSI cannot be created on a timestamp column.
I cannot use an aggregate join index that precomputes the max because the AGI will have a different PI from the table, causing the different TPump sessions to block each other when updating the singe AGI value.
I cannot reduce the number of TPump sessions to 1 to avoid the AGI blocking problem because the feed requires a high update rate.

The best solution I have up to now is to change the tpump to keep another table updated that keeps the max(ts) for large enough groups of rows, so that the number of groups is small enough to make the SELECT max(ts) run fast, but large enough to get enough parallelism in the TPump to not become a bottleneck for the TPump. The TPump would have to use a different serialization key appropriate for this table's PI.

I would prefer is something could be done inside Teradata without having to create more tables or change the Tpump.

Answer by Carlos A. Ibarra for How to interpret value of field 'CreateDate' in TDWM tables?

2010-03-02T03:21:51Z

Teradata dates are stored as integers encoded as:

     (year-1900) * 10000
   +  month      *   100
   +  day

This makes them look pretty reasonable for dates before the year 2000, for example

690720 represents 1960-07-20
840523 represents 1984-05-23
991231 represents 1999-12-31

For dates after 2000, (year-1900) is at least 100, so the date integer is more than 1 million, thus that pesky 1 at the beginning:

1010911 represents 2001-09-11
1091114 represents 2009-11-14
1100301 represents 2010-03-01

Does RollbackPriority=TRUE mean to use Rush or No Rush for rollbacks?

2010-02-26T16:21:58Z

This DBSControl setting is supposed to control whether rollbacks run at Rush priority or not. They used to always run at Rush because Teradata wanted to get them out of the way as soon as possible, but later they provided this RollbackPriority DBSControl parameter so the DBA can decide. However, it is not clear whether setting it to TRUE means that the rollback runs at Rush or not.

Does TRUE mean that rollbacks run at Rush or that they run at the user's logon priority?

The help for this setting is comically ambiguous:

RollbackPriority - This Field defines the system default for the rollback priority. This allows the DBA to either take the default priority of Rush or the user's logon priority. To enable this feature set the field to TRUE. To disable the feature set the field to FALSE. Note that this new feature will affect all users on the system.

Answer by Carlos A. Ibarra for How to perform Bit-wise operations in SQL?

2010-01-19T01:52:51Z

Teradata doesn't have bitwise operations out of the box.

However if you can install a UDF pack, check out the Teradata UDFs for Byte Operations package.

Also, it is possible to do bit operations with the help of a table of powers of 2. Something like this:

    create table bitmask(i integer, mask integer);
    insert into bitmask(0,1);
    insert into bitmask(1,2);
    insert into bitmask(2,4);
    insert into bitmask(3,8);
    insert into bitmask(4,16);
    insert into bitmask(5,32);

    -- 1 | 2
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (1/a.mask) mod 2 = 1
    or (2/a.mask) mod 2 = 1;
Answer: 3

    -- 5 & 9
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (5/a.mask) mod 2 = 1
    and (9/a.mask) mod 2 = 1;
Answer: 1

    -- 5 XOR 9
    select coalesce(sum(a.mask),0) answer from bitmask a
    where 
        (5/a.mask) mod 2 <> (9/a.mask) mod 2;
Answer: 12

Answer by Carlos A. Ibarra for order by issues

2010-01-07T22:50:48Z

You can't use count as a column name because it's a reserved word. I changed it to c and simplified your query a bit.

Here is how you can get your desired ordering:

SELECT  
   case
      when grouping(pname) =  1 then 'Total'  
      when grouping(version) = 1 then 'Subtotal ' || pname
      else   pname 
     end    "NAME", version "VERSION", SUM(c) "Qty" 
FROM    (       
SELECT  pname, version, 1 c
from    tt
 )as derived_table GROUP BY rollup (pname,version)
 order by grouping(pname),pname,grouping(version);

Answer by Carlos A. Ibarra for Rounding increase by 1

2010-01-07T22:29:57Z

Rounding is the default behavior when casting to a lower precision decimal. For example:

select cast(72.7 as decimal(2))

Answer: 73

Answer by Carlos A. Ibarra for Least function in TD

2009-11-10T14:01:23Z

Teradata doesn't have a built-in least function.

You could have your DBA install the Teradata UDFs for popular Oracle functions pack that contains a least() function, although that UDF only handles two arguments. If you are going to keep doing a lot of Oracle-like stuff, it might be a good idea to install this pack anyway.

To apply the two-argument UDF least to more arguments, you can do:

  least(least(a,b),c)
  least(least(least(a,b),c),d)

You could also use a CASE statement like this:

-- for two values
case when a < b then a
     else b
end

-- for three values
case when a < b then
     case when a < c then a
         else c
     end
     else -- b < a
         case when b < c
             then b
             else c
         end
end

And so on, but the number of lines of code you have to write becomes big really fast if more values need to be compared.

Answer by Carlos A. Ibarra for 2665 Invalid Date

2009-11-05T12:25:44Z

The format DD-MMM-YYYY doesn't match the date string 1-01-2008. Your could change the Java program to produce a two-digit day or preprocess the file to fix it.

Depending on which utility you are using to load the file, you may be able to use a CASE statement like this:

insert into Tablea ...
case when character_length(:datestring) < 10 then '0'||:datestring
     else :datestring
end

Answer by Carlos A. Ibarra for 6760 Invalid timestamp

2009-10-27T12:19:05Z

You didn't post your CREATE TABLE statement but I think you defined end_date as a timestamp type column. If so, you don't need to cast current_timestamp(0) to CHAR. Just use:

 update tablea
   set status = 'Succeeded'
     , end_date = current_timestamp(0)
 where id = 3456;

The data in timestamp columns is not kept in any specific format. The format in the column definition is used as the default output format when converting the timestamp to char for output. If you want to output in that format, you can define the column like this:

create table tablea
(
  id integer not null,
  status char(20),
  end_date timestamp(0) format 'dd/mm/y4bhh:mi:ss'
) primary index(id);

insert into tablea values (3456, 'ok',current_timestamp(0));

update tablea
   set status = 'Succeeded'
        , end_date = current_timestamp(0)
where id = 3456;

help table tablea;

Now when you convert the column to char you get the format you want:

select cast(end_date as char(19)) from tablea;

    end_date
    29/10/2009 14:59:31

Answer by Carlos A. Ibarra for The format or data contains a bad character

2009-10-25T16:34:52Z

The best way would be to have your DBA install the Is_numeric() User Defined Function (UDF) from the Teradata Download Center here. Then you can do something like this, which converts the valid ones and returns invalid ones as -1.

select 
   case when is_numeric(customer_no)=0
      then cast(customer_no as decimal(18))
      else -1
   end 
from CUSTOMER_TABLE
;

You can check which UDFs are installed by running this query. If a UDF is installed, it will appear as a row in the result.

select * from dbc.udfinfo;

If you don't have the is_numeric, but you have the ostranslate UDF installed, you can do this:

select
   case
      when otranslate(customer_no,'0123456789','')=''
      then cast(customer_no as decimal(18))
      else -1
   end 
from CUSTOMER_TABLE
;

Finally, if you can't use these UDFs, you can test whether your varchar is composed of all digits like this:

   select
       case
          when index('0123456789',substr(customer_no,1,1))>0   
              and index('0123456789',substr(customer_no,2,1))>0   
              and index('0123456789',substr(customer_no,3,1))>0   
              and index('0123456789',substr(customer_no,4,1))>0   
              and index('0123456789',substr(customer_no,5,1))>0   
              and index('0123456789',substr(customer_no,6,1))>0   
              and index('0123456789',substr(customer_no,7,1))>0   
              and index('0123456789',substr(customer_no,8,1))>0   
              and index('0123456789',substr(customer_no,9,1))>0   
              and index('0123456789',substr(customer_no,10,1))>0   
              and index('0123456789',substr(customer_no,11,1))>0   
              and index('0123456789',substr(customer_no,12,1))>0   
          then cast(customer_no as decimal(18))
          else -1
       end 
    from CUSTOMER_TABLE
    ;

Be aware that if you put the check in the WHERE clause, depending on the plan Teradata might try to convert the varchar to numeric before applying the check, so it is better to have the CASE statement in the SELECT list.

What UDFs would you like to see written?

2009-09-30T04:05:02Z

Teradata was missing a lot of the string manipulation and binary handling functions found on other DBMSs until they added the ability to write our own user defined functions. People wrote a lot of useful ones, most of which can be found in the Teradata Download Center.

I wish someone would write a regular expression search UDF. Maybe I'll tackle this some day.

Which UDFs do you wish for?

Comment by Carlos A. Ibarra

2010-08-26T03:35:59Z

If you have an integer number of days i, use current_timestamp - (interval '1' day) * i.

Comment by Carlos A. Ibarra

2010-05-12T17:48:27Z

I think the difference is that F9 submits all of your semicolon-separated statements as a single multi-statement request, while F5 submits them one at a time, waiting for results one by one. So there is still a need for F9 because there is no other way to specify that you want a multi-statement request in SQL Assistant.

Comment by Carlos A. Ibarra

2010-03-17T23:43:29Z

The question is not clear but I think he wants the list of columns that the query is accessing.

Comment by Carlos A. Ibarra

2010-03-15T15:16:39Z

The query probably has to be WHERE CAST(ts as DATE) >= CURRENT_DATE(), so that Teradata realizes it can use partition elimination. However, since the partitioning expression would not be part of the PI, partitioning might significantly affect the TPump performance. I have to check how many partitions would have data per each PI value. Thanks.

Comment by Carlos A. Ibarra

2010-02-11T03:17:02Z

Wow, nice trick with that CHAR2HEXINT(TRANSLATE()) thing!

Comment by Carlos A. Ibarra

2010-01-20T04:16:02Z

You cannot qualify the left hand part of the SET. Change set A.ORD_NBR to simply SET ORD_NBR without the A.

Comment by Carlos A. Ibarra

2010-01-09T23:24:22Z

Try a larger number like decimal(10) then.

Comment by Carlos A. Ibarra

2010-01-09T23:22:42Z

Then please accept the answer by clicking the checkmark icon next to it.

Comment by Carlos A. Ibarra

2010-01-08T02:35:28Z

Can you try formatting your questions better by using the "code sample" button to mark embedded SQL code? Thanks.

Comment by Carlos A. Ibarra

2009-10-29T20:04:34Z

See my expanded explanation of output format.

Comment by Carlos A. Ibarra

2009-10-04T16:23:12Z

That's good news about Teradata's regular expressions plans. They may have to be extra careful to use an implementation that avoids pathological cases where the engine recurses a lot.