Average GB of data in and out of Teradata database

Database

Average GB of data in and out of Teradata database

I have a requirement as below , if can track and report on a monthly basis
 
PROD/QA/DEV - GB of data in/out per day
 
I have been advised to see for any resusage macro’s that can help capture this.  Don’t know if this is in PDCR Toolkit or Viewpoint. Can i capture the above through some queries and drop into excel for  charting..
9 REPLIES
Highlighted
Teradata Employee

Re: Average GB of data in and out of Teradata database

See ResHostByLink in the Resource Usage Macros and Tables book in the Teradata documentation library. It is a macro to execute in a Teradata SQL session. It reports on available ResUsage data.

 

Enthusiast

Re: Average GB of data in and out of Teradata database

 

Hi Todd,

 

I see this macro, giving info about channnel traffic, can we convert , those columns data for one EDW  system, interpreting how much data came in (Insert) and how much was gone out? DBQL output would be much more tedious to find such?

 

Teradata Employee

Re: Average GB of data in and out of Teradata database

DBQL is a query by query log and is not necessariy turned on for all queries/utilities/... .

 

This macro will tell the data flows in and out by hostid. If the system has multiple hostids, there will be multiple records in the output that will need to be summed to get a system total.

Enthusiast

Re: Average GB of data in and out of Teradata database

Thanks Todd. 

 

I am trying to fire the below..  but get some differnt mesage . Result is not displayed for 1100 rows.

 

EXEC DBC.ResHostByLink
('2017-03-17','2017-03-18','00:00:00','23:59:59');

.set format off

Teradata Employee

Re: Average GB of data in and out of Teradata database

Need to see the message and a sample of rows if it appears incomplete information is displayed.

 

If the DBA/admin for the system deletes ResUsage or moves it to some archive location then the macro will not be able to see the data. The query will need to be extracted from the macro and run on the archived data if available.

Enthusiast

Re: Average GB of data in and out of Teradata database

Ran the below query for 1 day's date.. with following output.

There are various columns, do you need to group by few columns, to see , what was extracted and what was written?

 

 

 

SELECT TheDate (FORMAT 'yy/mm/dd', TITLE '// // Date'),
TheTime (FORMAT '99:99:99', TITLE '// // Time'),
NodeID (FORMAT '999-99', TITLE '// Node// Id'),
VprId (FORMAT 'ZZZZ9', TITLE '//Vproc// Id'),
HstType (FORMAT 'XXXXXXXX', TITLE '//Host//Type'),
HstId (FORMAT 'ZZZZZZZZZ9',TITLE '// Host// Id'),
IPaddr (TITLE '// //IP Address'),

/* Number of kilobytes read per second */
HostReadKB/Secs
(FORMAT 'ZZZZZ9.9', TITLE ' KBs// Read// /Sec'),

/* Number of kilobytes written per second */
HostWriteKB/Secs
(FORMAT 'ZZZZZ9.9', TITLE ' KBs// Write// /Sec'),

/* Number of blocks successfully read per second */
HostBlockReads/Secs
(FORMAT 'ZZ9.9', TITLE ' Blks// Read// /Sec'),

/* Number of blocks successfully written per second */
HostBlockWrites/Secs
(FORMAT 'ZZ9.9', TITLE ' Blks//Write// /Sec'),

/* Number of block read attempts that failed */
(HostReadFails*100)/NULLIFZERO(HostBlockReads+HostReadFails)
(FORMAT 'Z9.9%', TITLE ' Blk// Read//Fail%'),

/* Number of block write attempts that failed */
(HostWriteFails*100)/NULLIFZERO(HostBlockWrites+HostWriteFails)
(FORMAT 'Z9.9%', TITLE ' Blk//Write//Fail%'),

/* Average number of kilobytes per block read */
HostReadKB/NULLIFZERO(HostBlockReads)
(FORMAT 'ZZZZ9.9', TITLE ' KBs// /Blk// Read'),

/* Average number of kilobytes per block write */
HostWriteKB/NULLIFZERO(HostBlockWrites)
(FORMAT 'ZZZZ9.9', TITLE ' KBs// /Blk// Write'),

/* Average number of messages per block read */
HostMessageReads/NULLIFZERO(HostBlockReads)
(FORMAT 'ZZ9.9', TITLE ' Msgs// /Blk// Read'),

/* Average number of messages per block written */
HostMessageWrites/NULLIFZERO(HostBlockWrites)
(FORMAT 'ZZ9.9', TITLE ' Msgs// /Blk//Write')

FROM DBC.ResShstView

WHERE ( ( TheDate = '2017-03-17' AND TheTime >= '00:00:00' ) OR
( TheDate > '2017-03-17' )
) ANDOutputOutput
( ( TheDate = '2017-03-18' AND TheTime <= '23:59:59' ) OR
( TheDate < '2017-03-18' )
)

ORDER BY TheDate, TheTime, NodeID, VprId, HstId ;

echo '.set suppress off';
echo '.set format off';

Teradata Employee

Re: Average GB of data in and out of Teradata database

This is the detailed ResUsage data at 10 min intervals. Like any other detailed data it needs to be aggregated to what ever level of summary is desired.

Re: Average GB of data in and out of Teradata database

Thanks Todd so much on this.

 

I was referring devised below queries  and output.

 


LOCKING ROW FOR ACCESS
SELECT
thedate AS "THE_DATE"
,SUM(((FilePreReadKB+FileAcqReadKB)/1024)) AS "IOREADDATAMB"
,SUM(((FileWriteKB)/1024)) AS "IOWRITEDATAMB"
FROM pdcrdata.ResUsageSPMA_hst a
WHERE thedate =date-1
GROUP BY 1
ORDER BY 1;

 

Output

------------
THE_DATE    IOREADDATAMB     IOWRITEDATAMB
3/19/2017      109,212,793.98          19,757,383.29

 

 

Similary 

---------------

 

 

 


SELECT TheDate (FORMAT 'yy/mm/dd', TITLE '// // Date'),

-- NodeID (FORMAT '999-99', TITLE '// Node// Id'),
-- VprId (FORMAT 'ZZZZ9', TITLE '//Vproc// Id'),
HstType (FORMAT 'XXXXXXXX', TITLE '//Host//Type'),
--HstId (FORMAT 'ZZZZZZZZZ9',TITLE '// Host// Id'),
-- IPaddr (TITLE '// //IP Address'),
/* Number of kilobytes read per second */
--HostReadKB/Secs
--(FORMAT 'ZZZZZ9.9', TITLE ' KBs// Read// /Sec'),
/* Number of kilobytes read per second */
SUM(HostReadKB)/SUM(Secs)
(FORMAT 'ZZZZZ9.9', TITLE ' KBs// Read// /Sec'),
/* Number of kilobytes written per second */
-- HostWriteKB/Secs
-- (FORMAT 'ZZZZZ9.9', TITLE ' KBs// Write// /Sec')
/* Number of kilobytes written per second */
SUM(HostWriteKB)/SUM(Secs)
(FORMAT 'ZZZZZ9.9', TITLE ' KBs// Write// /Sec')
FROM DBC.ResShstView
WHERE TheDate = date-1
group by 1,2
ORDER BY TheDate

 

 

Date Host        Type        KBs Read /Sec  KBs Write /Sec
1 3/19/2017  NETWORK   32.66                    54.07

 

Any other feedback on this or modification you would like to suggest. Dont think, we have anything on Viewpoint :)

 

 

Teradata Employee

Re: Average GB of data in and out of Teradata database

Depends on the goal for the result.

If I were investigating the bandwidth requirements I would want the SUM of each for the day to understand the total bandwith requirements and the MAX of each to know the peak bandwidth required per 10 minute period in addition to the averages calculated in this query.