96 | 22 Sep 2014 @ 11:08 PDT | Database | Reply | Access Right 'G' abbreviation | Hi Nick2408,
the docs have following abbreviations - see Data Dictionary, Appendix A View Column Values:
GC - CREATE GLOP
GD - DROP GLOP
GM - GLOP MEMBER
Regards,
Vl... |
95 | 22 Sep 2014 @ 11:03 PDT | Database | Reply | Selecting all rows that have a unique column | Hi Brim,
if you want to find rows with unique ID, the following SQL applies. The window analytic functions are great for such purposes.
select id, name, email
from source_table
qu... |
94 | 22 Sep 2014 @ 10:52 PDT | Database | Reply | checking I/O skew | Hi Moutusi,
there are columns for maximum I/O and average I/O to get the I/O skew:
max = MaxAMPIO
avg = TotalIOCount / nullifzero( NumOfActiveAMPs )
skewfactor is (100... |
93 | 22 Sep 2014 @ 10:43 PDT | Database | Reply | Split Percentage based on total. | Hi Sreeni,
try something like this (has to be tested):
select organization_code, product_family, the_sum, quarter_name,
the_sum * 100.00 / nu... |
92 | 07 Aug 2014 @ 11:25 PDT | General | Reply | Query performance issue | Hi,
you can try to use a join instead a correlated query, i.e. something like this:
SELECT
count(distinct(TBL_OUTPUT.C_CID)) as CNT
FROM
... |
91 | 29 Jul 2014 @ 11:43 PDT | Database | Reply | Thoughts on CPUBusy caclulations. | Hi Ekladios,
there could be queries that run more than one hour, or at least span two hours. If AMPCpuTime is summed up to "per hour", which is probably a query with a GROUP ... |
90 | 29 Jul 2014 @ 11:24 PDT | Analytics | Reply | Why subqueries in case are not working in teradata? | Hi,
one of possible solutions is to do a left join with the lookup table, and see whether or not the record is found:
select ...
and B.TYP_CD IS NULL /* and T... |
89 | 28 Jul 2014 @ 04:42 PDT | Database | Reply | Viewpoint and Corresponding DBC Table |
p.s. forgot the alias, it is SELECT * FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)) as t1;
Both MonitorSession and DBQL do not have skew column pre-calcul... |
88 | 28 Jul 2014 @ 01:11 PDT | Database | Reply | Viewpoint and Corresponding DBC Table | Hi Santanu,
for the near-real-time data, you might want to check MonitorSession:
SELECT * FROM TABLE (SYSLIB.MonitorSession(-1,'*',0));
for the history data, the DBQL ... |
87 | 27 Nov 2013 @ 10:17 PST | UDA | Reply | Cancel Rollback on a table with referential integrity | Hi Raja,
I've seen it here: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Utilities/B035_1102_111A/RecoveryMgr.51.23.html
With kind regards,
Vlad.
|
86 | 27 Nov 2013 @ 06:05 PST | Training | Reply | difference between soft referential integrity and hard referential integrity in teradata | Hi,
in the example above, the soft RI is not defined. The phrase "-- here constraint is not used/commented" is not defining soft RI.
If there is ... |
85 | 27 Nov 2013 @ 05:58 PST | UDA | Reply | Cancel Rollback on a table with referential integrity | Hi Noel,
If you have found a solution for this, then please kindly share.
The documentation says that it is not allowed.
Regards,
Vlad.
|
84 | 27 Nov 2013 @ 05:54 PST | Database | Reply | Explain plan for set table | Hi Abin,
it is possible to measure. Create two similar tables - one set and one multiset, with the same data. Insert same new data into both tables. Ant then measure CPU consu... |
83 | 26 Nov 2013 @ 09:28 PST | Database | Reply | Explain plan for set table | Hi Khurram,
Thank you for agreeing with me. However, I believe that there is no need to dedicate an additional post to say only that. :)
Regards,
Vlad.
|
82 | 26 Nov 2013 @ 08:55 PST | Database | Reply | Explain plan for set table |
Hello Abin,
you have really provided the answer in the first paragraph, the are no additional steps. The phrase "defined as set and then altered it as set" has the "s... |
81 | 22 Nov 2013 @ 12:00 PST | Database | Reply | Database Performance: large Table getting redistributed causing performance problems | Hi Naga,
first thing is to check the statistics. And information about 2nd table is, indeed, very needed.
Apart from that, if you select only a few columns(4) from a table, a distrib... |
80 | 20 Nov 2013 @ 11:38 PST | Database | Reply | recursive query numeric overflow issue | Hello Arun,
if the recursion is not too deep, then one of the possible options is to store all the "previous" manager ids in a separate column, and then filter by that column, li... |
79 | 20 Nov 2013 @ 11:16 PST | Database | Reply | CASE STATEMENT | - The brackets [] should better be ()
- = ('WHS') should better be = 'WHS'
apart from that, I just agree with Ulrich.
Regards,
Vlad.
|
78 | 20 Nov 2013 @ 02:52 PST | Database | Reply | CASE STATEMENT | just substitute:
if -> case when
use -> then
and add the "end" at the end.
|
77 | 20 Nov 2013 @ 12:20 PST | Database | Reply | macro sql Optimization | sorry for my previous post, I've not seen the 2nd explain.
|
76 | 16 Nov 2013 @ 01:34 PST | Database | Reply | Get previous column vale till it reaches another not null value | Hi Bikky,
There is one more option. Apart from using the Ordered Analytical Functions, you might want to introduce the Start_date and End_date into the LKP table. This approa... |
75 | 16 Nov 2013 @ 01:27 PST | Database | Reply | regarding the conversion of currency from INR to n e other currency | Hi Peiter,
when you join two tables, both of them having start_date and end_date, then the join condition for those columns (in addition to currency_code, etc.) should be:
&n... |
74 | 15 Nov 2013 @ 06:43 PST | Database | Reply | Spool Space Usage | p.s. also take Skew into account, because spool space limit is divided equally across the AMPs. So, in reality, it would be more precise to capture maximum value across AMPs also (group by b... |
73 | 15 Nov 2013 @ 06:41 PST | Database | Reply | Spool Space Usage | Hello Kishore,
For the average, you can setup a script that would run, say, every one hour or every 10 minutes, and insert-select from dbc.databases the "currentspool" field, grouped by ... |
72 | 15 Nov 2013 @ 03:18 PST | Database | Reply | macro sql Optimization |
Hello,
your Explain shows that partition elimination does happen, both for PMART_ST.PRE_PAYMENT_DETAIL and PMART_ST.PRE_PAYMENT_RESETL_DETAIL tables (the phrase ... |