FAQ
|
| Q1: | I'm trying to subtract one year or 12 months from the leap year 2004-02-29 and I get the following error message: <2665 Invalid date>.
SELECT CAST('2004-02-29' AS DATE) - INTERVAL '12' MONTH
SELECT CAST('2004-02-29' AS DATE) - INTERVAL '1' YEAR |
| A1: | ANSI standard says MONTH/YEAR interval arithmetic should not adjust day of month.
Teradata implemented the ADD_MONTHS() extension specifically to address this problem:
SELECT ADD_MONTHS(date '2004-02-29', -12)
Results: 2003-02-28 |
| Q2: | I am planning on using the schmon -i or the schmon -b command in Priority Scheduler. Do you know if I need to adjust the CPU percentages on a regular basis? If so, how often should this be done? |
| A2: | Here are some explanations regarding just what these Priority Scheduler commands do:
The schmon -i (lowercase i) enables the prioritized queuing of FSG I/O requests. It should be turned on by default. It is not recommended that you turn it off, but it may not make much difference to do so if you are not experiencing I/O waits of any magnitude on your platform. What it does is enforce the prioritization of requests that are waiting for an I/O, which is usually a good thing. I am not aware of any negative trade-offs with having I/O Prioritization turned on.
The schmon -b command creates or modifies resource partitions. It is not uncommon to issue several schmon -b commands to alter the relative priorities of one or more already-defined resource partitions. Often a small batch of schmon -b commands is packaged into a cron job and scheduled regularly as a way to change priorities at different times of day. The most common change pattern I have seen is twice a day, once to change settings just prior to the batch cycle at night and then just before the day cycle in the morning. The resulting impact on CPU usage will depend on how much the assigned weights of the affected resource partitions are changed and the nature of the work running within them and the other active resource partitions.
If you are using PSA (Priority Scheduler Administrator), PSA can automate the changes in settings at specific times of day, so the DBA no longer needs to submit cron jobs or schmon commands to establish that level of control. |
| Q3: | When does an AMP give up the allocated worker tasks (AWTs)? Will it wait until the complete step has been finished on all AMPs before it releases all AWTs, or will it release the AWTs AMP by AMP? What if the step is highly skewed? |
| A3: | Teradata will release a given AWT at the time that particular AMP completes that query step. Each AMP will make a notification that it has completed that query step and then free up its AWTs for other work.
Only when the last AMP is done with that step does the system notify the dispatcher that all AMPs are done so the next step can be sent. If the step is highly skewed, some AMPs will be holding AWTs on behalf of that step, while other AMPs will have completed and released their AWTs to do other work. |
| Q4: | I am executing a query with a value in the where clause for a NUPI (Non-Unique Primary Index) on a table with a PPI (Partitioned Primary Index). The query is not using the NUPI.
My question is this: When you have a PPI and a NUPI (and the PPI is not part of the NUPI) and you supply values for the NUPI but not for the PPI, should the optimizer use the NUPI or go around it? Also, is it necessary or just recommended to incorporate the PPI column into the NUPI? |
| A4: | The answer depends on the size of the table and number of partitions.
When only the NUPI value is specified on a PPI table, every partition has to be accessed in order to find the NUPI value. If there are lots of partitions and it would be faster to scan the table, then that is what the optimizer will choose. This is the trade-off of partitioning a table that also needs to be accessed directly by a single PI value. If the PPI is valuable enough, then it is probably appropriate to put a NUSI on the direct selection columns to avoid all the probes to the partitions.
Should you put the partitioning column in the PI (Primary Index)? Usually not. In this case, if it is possible to specify the partitioning column value and the NUPI value in the where clause, then do that so that you have good PI-type access. If the partition value is not available, then having the partitioning column in the PI makes the PI useless even more of the time. |
| Q5: | We need to use big comments columns because we include management rules in the comments. However, the comment columns in Teradata are only 250 characters long. Is there anyway to capture the complete comment? |
| A5: |
Teradata offers Metadata Services (MDS), which provides the facility
for both capturing the business metadata and for disseminating the
business and technical metadata to users. It has the flexibility to
contain the large amount of text that is desirable for the business
metadata descriptions. It also helps with relationships between objects.
|
|
© Teradata Magazine-September 2004
Archived
FAQs
back to top |
Quick Links
WEB-ONLY
CONTENT 
Printable version
Send to a colleague