User monis iqbal - TeradataQuestions

Answer by Monis Iqbal for Channel Node in Teradata

2010-08-18T19:25:53Z

Channel nodes do not have AMPs on them so they don't do actual data keeping but add up to the parsing capability of the Teradata system. As an AMP less node it enhances the session connectivity and PE related tasks of the overall system with relatively low cost.

Does the column AwtInUseMax from ResUsageSpma provide aggregated value for all the AMPs in the Node?

2010-05-20T07:51:15Z

In ResUsageSpma data is reported per node. The definition of column AwtInUseMax is given as:

Peak number of AWTs (Max) on this node. This is not the Peak or the Max value stored in the Priority Scheduler (sch) data structure and reported by the puma utility. The sch peak value is the Max value since startup is never set and Max is the maximum allowed value.

We don't know if this gives us the maximum AWT count from all the AMPs in the node OR sum of maximum AWT count from all AMPs

I'm asking this because we are thinking of dividing the AwtInUseMax by the number of AMPs in the node i.e. AwtInUseMax/Vproc1. Is it right to divide by the number of AMPs to determine the maximum AWT usage per node per AMP?

Can anyone please confirm?

Note: I have also posted this question on the official Teradata forums: http://bit.ly/bon8er

Answer by Monis Iqbal for Does the column AwtInUseMax from ResUsageSpma provide aggregated value for all the AMPs in the Node?

2010-05-24T05:44:18Z

Got the answer from a follow up on Carrie's blog post. http://developer.teradata.com/blog/carrie/2009/09/ more-on-resusagesawt-w...

AwtInUseMax gives us the value representing the max AWT in-use count for ALL the AMPs in the node.

This was also backed up by data from Spma and Sawt. Below is the data for a single node for the same date and time.

ResUsageSpma

AwtInUseMax
77.00

ResUsageSawt

VprId   InuseMax
1   74.00
3   75.00
5   74.00
7   74.00
9   73.00
11  74.00
13  70.00
15  73.00
17  74.00
19  74.00
21  74.00
23  70.00
25  73.00
27  74.00
29  73.00
31  74.00
33  68.00
35  74.00
37  69.00
39  74.00
41  74.00
43  74.00
45  74.00
47  72.00
49  74.00
51  77.00
53  74.00
55  74.00
57  75.00
59  74.00
61  74.00
63  68.00
65  73.00
67  74.00
69  70.00
71  74.00
73  74.00
75  75.00
77  75.00
79  74.00

Answer by Monis Iqbal for 3848: The ORDER BY clause must contain only integer constants

2010-04-24T15:43:24Z

You can also give the column number in the order by clause. Usually column names work within the order by clause but in some cases you have to go with column numbers e.g. when using within the WITH RECURSIVE clause.

Is it true that PSF's Priority Scheduler Administrator (PSA) is not active on a new Teradata install?

2010-03-25T17:59:33Z

Until we haven't run the DBCManager setup on a fresh Teradata install, there are no PSA configuration tables. And also none of TDWM tables.

So by default which Priority Scheduler is active? if both the PS of TDWM and PSA are not there?

Can multiple account ids (mapping to multiple Performance Groups) be supplied for a Teradata connection?

2010-03-16T20:52:05Z

When connecting to Teradata via JDBC, we can supply any of the account ids available for the user we are connecting with. e.g. if my user has Account=$SINCITY$,$H,$Mon&H$ Then this connection string: jdbc:teradata://ccbez/account=$SINCITY$

connects with the Account String = $SINCITY$ which will also appear as this in the DBQL's AcctString column and if TDWM is disabled, then this user's session will be mapped to a Performance Group (in PSA), named SINCITY.

The question is, can we make this user session to map to multiple account ids? (and therefore to multiple PGs), e.g. jdbc:teradata://ccbez/account='$SINCITY$','$Mon&H$'

(when I provided this second example, the account string being selected is $SINCITY$)

How to interpret value of field 'CreateDate' in TDWM tables?

2010-03-01T12:32:58Z

CreateDate and RemoveDate are two fields in many of the tables related to TDWM e.g. in WlcDefs. The values and their meanings (according to me) are like:

1100301 - 2010/03/01 1091114 - 2009/11/14

What does the first '1' represent in these date fields? and is my interpretation correct?

How to perform Bit-wise operations in SQL?

2010-01-18T09:18:40Z

How to perform simple bit-wise operations like OR (|), AND (&), XOR (^) in SQL queries?

Can these be performed on numeric values? like

sel 1 | 2;

Answer by Monis Iqbal for Difference between FilePres and FilePreReads

2010-01-26T18:16:51Z

Below are the (more explanatory) definitions from Teradata 13 documentation:

FilePres - Total number of times a logical data prefetch was performed (either as a cylinder read or individual block reads).

FilePreReads - Number of times a data prefetch was physically performed either as a cylinder read or individual blocks read.

We asked Teradata about the major differences between the 'Physical IO Count' and 'Logical IO Count' formulas and they responded with these correct formulas:

Physical I/O Counts - FileAcqReads + FilePreReads + FileWrites

Logical I/O Counts - FileAcqs + FilePres + FileRels

From Teradata, the Teradata 13 documentation provides the correct definitions/formulas however for the physical/logical counts they have entered a DR to rectify it.

Difference between FilePres and FilePreReads

2009-12-21T07:24:02Z

From Teradata documentation, the definitions of these ResUsageSpma columns are:

FilePres         Total number of times a cylinder is loaded.

FilePreReads     Number of times a cylinder is loaded.

which look similar, but I observed on our lab's Teradata and found quite different values with an observation that FilePres = 2 x FilePreReads (approx.)

I also looked into KA but couldn't find any explanation. Please respond if you know the meaning of these fields.

The story doesn't end here. When I compared different versions of Teradata documents (V2R6, 12, 13) I found out quite a few differences among them.

The screenshots of the differences (from Excel) are attached.

Any idea on differences or this maybe a Teradata documentation problem?

How to yield maximum decimal digits from a cast?

2010-01-13T11:49:49Z

I want to demonstrate the problem with the help of an example.

Accurate division: 7299 / 48 = 152.0625 However, in Teradata all precision is lost when querying for select 7299/48 = 152

We have to apply a cast for precision, like: select cast(7299/48 as decimal(8,5))

Assuming that my data column has a length of 8. Here I cannot provide value greater than 8 in the first argument. In the second argument (number of digits after decimal point), at max I can provide 5 because the result yields 3 digits in the numeric part and as a remainder we get 5 (8-3).

I'm asking this because I can go into troubles if I apply the same cast to: select 72990/48 = 1520.625 because here the numeric part has 4 digits and I can only supply 4 in the second argument.

Is there a way that I can specify only the first argument 8 (equaling to the length of the column) and get the result with complete precision?

Answer by Monis Iqbal for Rounding increase by 1

2010-01-13T10:03:42Z

I think double casting should do the trick for you:

select cast( cast(72.70 as decimal(2)) as decimal(3,1))

Result: 73.0

Determine the maximum number of AWTs

2009-12-16T10:03:48Z

The default number of AWTs per AMP is defined to be 80. Can this number grow to some larger value than 80 and can we set a threshold (of number of AWTs) on an AMP level or on Node or System level?

Additional note: ResUsageSpma and ResUsageSawt has AwtInuseMax but it does not give us the maximum defined number of AWTs.

Answer by Monis Iqbal for Determine the maximum number of AWTs

2009-12-16T10:21:19Z

I think the maximum number of AWTs can be determined by the

AWT Limit defined in TDWM's PSA in 'System level parameters'? or
In case of PSA the AWT Limit defined under the 'PD Set'?

If yes, then this information can be extracted from

table: TDWM.PsfGlobalStates, column: LimitAWT
table: TDMNGR.PSAProfile, column: LimitAWT

TDWM configuration data - purging practices

2009-11-19T19:26:42Z

Is TDWM configuration data e.g. WlcDefs, RuleDefs, SysCons etc. ever purged? I've noticed that whenever some modification is made in TDWM the old configuration data is kept in the table with the field RemoveDate, RemoveTime. Is this data purged after some configured duration? or is it a common practice to purge this data in the industry?

I'm asking this because there are clear instructions about purging the ResUsage and DBQL data in Teradata documents but so far I haven't found anything on TDWM configuration data.

Answer by Monis Iqbal for Teradata documentation online search

2009-11-23T09:55:42Z

Searching about Teradata is a bit unconventional and you have to make yourself accustom to the resources available.

I am listing the searching sites priority wise:

Teradata documentation (I guess these are the PDFs that you've mentioned in your question).
Teradata@YourService resources (http://www.teradata.com/t/At-Your-Service-login.aspx?id=6988). a. Search for 'Orange Books' on the desired topic. b. Search KA 'Knowledge Areas'.
Search the old Teradata forums: http://www.teradata.com/teradataForum/
Search or Ask question here: http://teradataquestions.stackexchange.com/
Search or Ask questions on the new TD forums: http://developer.teradata.com/forum

(For me 4 & 5 should be given same priority, reason being that good and helping people are required to answer your queries if you cannot find them anywhere and these people are available at different locations at different times, sometimes even at Twitter!)

Answer by Monis Iqbal for Help Writing a Query for Historical Disk Space Usage

2009-11-20T08:54:51Z

i'm not sure if you can fetch the historical permspaces from Teradata. To find out current spaces:

SELECT DatabaseName as "DB Name", SUM(CurrentPermSpace) as "Current Perm", SUM(PeakPermSpace) as "Peak Perm" FROM DBC.DatabaseSpace ds, DBC.DBase db WHERE ds.DatabaseId=db.DatabaseId AND db.RowType='D' GROUP BY DatabaseName ORDER BY DatabaseName;

Relation between Priority Scheduler in Priority Scheduler Administrator and the Priority Scheduler in TDWM

2009-11-16T12:28:32Z

In TDWM we have a Priority Scheduler where we can map WD to AG, add new RP, AG etc. The key here is the 'Priority' that maps WD to AG. This 'Priority' can be set in the WD as 'Enforcement Priority' and can be mapped to AG in the Priority Scheduler screen in the TDWM. Uptill here the relation between TDWM's workloads and priority is clear, and this association can be confirmed by observing the SlgPriority field in the TDWM.WlcDefs table.

However the 'Priority Scheduler Administrator' shows a different picture of priority and the association between RP, AG, Perf Group, Perf Period looks to be contained in this administrator itself.

There seems to be no relation between the priority of these two (TDWM and PSA). Is this designed this way? If so, then what is the purpose of PSA? If not, then how can the priority in TDWM's Priority Scheduler be linked to PSA?

Thanks,

Reading DBS Control values via SQL

2009-11-12T06:05:10Z

To read values in DBS Control we have to execute the DBSControl utility. Is there a way to read these by issuing queries against Teradata database?

Does TDWM include queries by DBC user?

2009-10-23T08:04:39Z

Is the processing done against DBC.ResUsage tables and DBQL tables monitored by TDWM? I've read that the TDWM Filters cannot filter DBC data but we wanted to create WD over DBC. Is it possible?

Teradata Database Setup errors out for some queries

2009-10-19T09:24:32Z

I have posted the same issue on: http://developer.teradata.com/forum/tools/teradata-database-setup-errors-out-for-some-queries

We have installed Teradata on Windows machine. Now on running Database Setup and providing the following information (in the WIndows GUI):

Database Name : db1
Super User: DBC
Super User Password: dbc
Console Password: dbc
Set up DB for Teradata Manager: Yes
Perm Space: 100 MB
DB Type: MPRAS
Spool Space: Same as Parent

Create a Teradata Manager User: Yes
User Name: DBCMANAGER
User Account String: $H-DBC-MANAGER
User Password: dbcmngr
Perm Space: 100MB
Spool Space: Same as Parent
Give user privilages to run: Selected all options in combo
Migrate TDQM Database: Yes

On proceeding further, there are some errors in the log file, while the user is created and most of the operations succeed. We want to eliminate the errors, more specifically the error while creating the table TDWMUpsertConfigurations, because when creating TDWM we get errors against this table.

Below is the error log for this (after running Database Setup). I can also provide the complete log if any one wants a peek:

REPLACE PROCEDURE TDWMUpsertConfigurations(
IN ConfigId INTEGER,
IN ConfigName VARCHAR(30),
IN Description VARCHAR(80),
IN MinEstConfidence VARCHAR(10),
IN DeadlockCycles INTEGER,
IN DeadlockAction VARCHAR(20),
IN MonitorInterval INTEGER,
IN LoggingInterval INTEGER,
IN DashboardInterval INTEGER,
OUT NewConfigId INTEGER,
OUT CreateDate INTEGER,
OUT CreateTime INTEGER,
OUT ResultCode INTEGER
)
BEGIN
DECLARE cdate INTEGER;
DECLARE ctime INTEGER;
DECLARE myConfigId INTEGER;
SELECT DATE INTO :cdate;
SELECT TIME INTO :ctime;
SET ResultCode = 0;
SET CreateDate = cdate;
SET CreateTime = ctime;
SET myConfigId = ConfigId;
SET NewConfigId = myConfigId;
IF ConfigId <= 0 THEN
BEGIN
SELECT ZEROIFNULL(MAX(ConfigId)) + 1 INTO :myConfigId
FROM Configurations;
SET NewConfigId = myConfigId;
END;
ELSE
BEGIN
LOCKING Configurations FOR WRITE
UPDATE Configurations SET
RemoveDate = :cdate,
RemoveTime = :ctime
WHERE ConfigId = :ConfigId AND ConfigId = :myConfigId AND RemoveDate = 0;
END;
END IF;
INSERT Configurations (ConfigId, ConfigName, CreateDate, CreateTime,
Description, RemoveDate, RemoveTime,
MinEstConfidence, DeadlockCycles, DeadlockAction,
MonitorInterval, LoggingInterval, DashboardInterval)
VALUES (:myConfigId, :ConfigName, :cdate, :ctime,
:Description, 0, 0,
:MinEstConfidence, :DeadlockCycles, :DeadlockAction,
:MonitorInterval, :LoggingInterval, :DashboardInterval);
END;
*** Query failed
*** CLI2: BADSEGMENT(373): TDSP Input Segment is InValid.

Code= 373 Function= 4

Answer by Monis Iqbal for Teradata Database Setup errors out for some queries

2009-10-21T12:25:20Z

This error cropped up on our V2R6 install. There were some C++ errors in the log as well. We tried installing the .net runtime but in vain.

On another install of TD 12, we installed MS Visual Studio 2003 first (with C++ selected) and then ran Database setup. It ran without any C++ errors and after that all TDWM tables were created successfully and we are able to create WD rulesets and save them on database.

Different Explains of a same query on different Teradata systems

2009-09-30T14:07:56Z

Here is the query: SELECT max(TheTime) AS maxTime, max(TheDate) as maxDate FROM DBC.ResUsageSvpr tm, ( SELECT max(TheDate) As maxDate FROM DBC.ResUsageSvpr WHERE (TheDate = '2009-09-22' and TheTime <= '230049.525') or TheDate < '2009-09-22') as dt WHERE dt.maxDate = tm.TheDate and TheTime <= ( case when TheDate='2009-09-22' then '230049.525' else '240000' end ) HAVING count(*) > 0;

And below are the two explains. We can ignore the difference in execution time as the number of records on both system is also different:

Explanation 1 1) First, we lock DBC.ResUsageSvpr for access. 2) Next, we do an all-AMPs SUM step to aggregate from DBC.ResUsageSvpr by way of an all-rows scan with a condition of ( "((DBC.ResUsageSvpr.TheDate = DATE '2009-09-22') AND (DBC.ResUsageSvpr.TheTime <= 2.30049525000000E 005 )) OR (DBC.ResUsageSvpr.TheDate < DATE '2009-09-22')"). Aggregate Intermediate Results are computed globally, then placed in Spool 3. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 3 is estimated with high confidence to be 1 row (19 bytes). The estimated time for this step is 57.10 seconds. 3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table or table function dt) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.01 seconds. 4) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of ("NOT (dt.MAXDATE IS NULL)") into Spool 8 (all_amps), which is duplicated on all AMPs. The size of Spool 8 is estimated with high confidence to be 2,560 rows (43,520 bytes). The estimated time for this step is 0.02 seconds. 5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an all-rows scan, which is joined to DBC.tm by way of an all-rows scan with a condition of ("DBC.tm.TheTime <= (( CASE WHEN (DBC.tm.TheDate = DATE '2009-09-22') THEN ('230049.525') ELSE ('240000') END )(FLOAT, FORMAT '-9.99999999999999E-999'))"). Spool 8 and DBC.tm are joined using a single partition hash_ join, with a join condition of ("MAXDATE = DBC.tm.TheDate"). The input table DBC.tm will not be cached in memory. The result goes into Spool 7 (all_amps), which is built locally on the AMPs. The size of Spool 7 is estimated with no confidence to be 13,801 rows ( 372,627 bytes). The estimated time for this step is 57.56 seconds. 6) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 9. The size of Spool 9 is estimated with high confidence to be 1 row (35 bytes). The estimated time for this step is 0.04 seconds. 7) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of an all-rows scan with a condition of ("(Field_4 (INTEGER))> 0") into Spool 5 (group_amps), which is built locally on the AMPs. The size of Spool 5 is estimated with high confidence to be 1 row (33 bytes). The estimated time for this step is 0.01 seconds. 8) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 5 are sent back to the user as the result of statement 1. The total estimated time is 1 minute and 55 seconds.

Explanation 2 1) First, we lock DBC.ResUsageSvpr for access. 2) Next, we do an all-AMPs SUM step to aggregate from DBC.ResUsageSvpr by way of an all-rows scan with a condition of ( "((DBC.ResUsageSvpr.TheDate = DATE '2009-09-22') AND (DBC.ResUsageSvpr.TheTime <= 2.30049525000000E 005 )) OR (DBC.ResUsageSvpr.TheDate < DATE '2009-09-22')"). Aggregate Intermediate Results are computed globally, then placed in Spool 3. The size of Spool 3 is estimated with high confidence to be 1 row (19 bytes). The estimated time for this step is 0.54 seconds. 3) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table or table function dt) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.01 seconds. 2) We do an all-AMPs RETRIEVE step from DBC.tm by way of an all-rows scan with a condition of ("DBC.tm.TheTime <= (( CASE WHEN (DBC.tm.TheDate = DATE '2009-09-22') THEN ('230049.525') ELSE ('240000') END )(FLOAT, FORMAT '-9.99999999999999E-999'))") into Spool 8 (all_amps), which is redistributed by the hash code of (DBC.tm.TheDate) to all AMPs. Then we do a SORT to order Spool 8 by row hash. The size of Spool 8 is estimated with no confidence to be 12,512 rows (312,800 bytes). The estimated time for this step is 0.53 seconds. 4) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of ("NOT (dt.MAXDATE IS NULL)") into Spool 9 (all_amps), which is redistributed by the hash code of (DBC.ResUsageSvpr.TheDate) to all AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated with high confidence to be 1 row (17 bytes). The estimated time for this step is 0.00 seconds. 5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a RowHash match scan, which is joined to Spool 9 (Last Use) by way of a RowHash match scan. Spool 8 and Spool 9 are joined using a merge join, with a join condition of ("MAXDATE = TheDate"). The result goes into Spool 7 (all_amps), which is built locally on the AMPs. The size of Spool 7 is estimated with no confidence to be 112 rows (3,024 bytes). The estimated time for this step is 0.01 seconds. 6) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 10. The size of Spool 10 is estimated with high confidence to be 1 row (35 bytes). The estimated time for this step is 0.03 seconds. 7) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of an all-rows scan with a condition of ("(Field_4 (INTEGER))> 0") into Spool 5 (group_amps), which is built locally on the AMPs. The size of Spool 5 is estimated with high confidence to be 1 row (33 bytes). The estimated time for this step is 0.01 seconds. 8) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 5 are sent back to the user as the result of statement 1. The total estimated time is 1.12 seconds.

I know it's tedious but if someone can look into it and give some insights as to what maybe different on both the systems that causes different execution steps.

Comment by Monis Iqbal

2010-05-20T15:43:53Z

thanks Rob :) We don't have enough nodes in our lab to have concrete results. I too am waiting to analyze a customer's data. Let's see who wins :D

Comment by Monis Iqbal

2010-04-09T20:18:49Z

you're right Rob. On another fresh system, I tried schmon -a and it returned: Resource Partitions (0 - 4) Id Partition Name Weight Limit 0 Default 100 none However there was no data in the PSA tables.

Comment by Monis Iqbal

2010-04-02T13:00:12Z

The schmon utility gives empty results. Later, when we ran the database setup utility on the system, it created the TDWM and PSA (in DBSMNGR) tables but there was no data in them. I think this implies that both aren't working, right?

Comment by Monis Iqbal

2010-03-18T12:23:29Z

A single session can have no more than one account string. right?

Comment by Monis Iqbal

2010-03-02T06:18:28Z

Carlos, thanks for the quick and perfect response. Is this your observation or is there a Teradata resource out there that states this fact?

Comment by Monis Iqbal

2010-03-01T12:39:14Z

We asked Teradata's observation about this and they said that they have seen varied practices, however majority has been the ones who never purge the configuration data.

Comment by Monis Iqbal

2010-01-26T18:18:48Z

@Rob, We asked Teradata about the differences and they said that Teradata 13 documentation is correct except for the Logical/Physical IO Counts, for which they provided the correct formulas. I've mentioned them in my answer.

Comment by Monis Iqbal

2010-01-21T17:06:25Z

Thanks Carlos. I'm limited with the usage of UDFs as the database is not our own and we only have select rights.

Comment by Monis Iqbal

2010-01-19T10:54:39Z

An additional note for casting in decimal: Max precision can be achieved like cast(X as decimal(18, Y)), where Y is the number of digits after the decimal.

Comment by Monis Iqbal

2010-01-19T10:52:44Z

Cool way of achieving the result. Do you think this will be faster than the cast as decimal? Sorry for the late comment. I was busy in other stuff so didn't have time to test things.

Comment by Monis Iqbal

2009-12-24T10:56:45Z

Rob, please check the edited question. I found out some bizarre differences among the same fields in different Teradata versions.

Comment by Monis Iqbal

2009-12-23T06:27:37Z

Right to the point. Much thanks.

Comment by Monis Iqbal

2009-12-21T07:54:25Z

I agree with the non-NULL value in PSAProfile, but according to my understanding, PSA is always running when TDWM rules are disabled. Therefore, won't it be wrong for the PSA to have NULL values when it is enabled, what values will it be using then? or is my understanding about PSA wrong?

Comment by Monis Iqbal

2009-12-16T20:45:10Z

but isn't PSA always running when TDWM is not running?

Comment by Monis Iqbal

2009-12-16T10:21:51Z

Please confirm if this is true.