TD Usefule Queries

Report CPU usage over 24 Hours

/* SQL to report CPU usage over 24 Hour period */
/* Make sure the collectintervals set correct */

select TheDate,
cast((cast((TheTime (format '99:99:99')) as char(8))) as time(0)) as TheTime,
(sum(CPUIdle) / TOTAL_CPU) * 100 AS CPUIdle,
(sum(CPUIoWait) / TOTAL_CPU) * 100 AS CPUIoWait,
(sum(CPUUServ) / TOTAL_CPU) * 100 AS CPUUServ,
(sum(CPUUExec) / TOTAL_CPU) * 100 AS CPUUExec,
SUM(CPUIdle + CPUIoWait + CPUUServ + CPUUExec) AS TOTAL_CPU,
(avg(CPUUServ + CPUUExec) / max(CPUUServ + CPUUExec)) * 100 AS CPUBusyParallelEff
from dbc.resusagespma
where thedate >= date - 1
group by 1,2
order by 1,2
;

SQL to check Cylinder Migration

/* SQL to check Cylinder Migration */
/* make sure the collectintervals value is set correct */

SELECT TheDate,
CAST((CAST((TheTime (FORMAT '99:99:99')) AS CHAR(8))) AS TIME(0)) AS TheTime,
SUM(FileCylAllocs),
SUM(FileCylMigrs),
SUM(FileMCylPacks),
SUM(FileCylDefrags),
(AVG(FileCylMigrs) / MAX(FileCylMigrs)) * 100 AS VprocCylMigrsParallelEff
FROM dbc.resusagesvpr
WHERE collectintervals = '2' AND
vprtype = 'AMP'
GROUP BY 1,2
ORDER BY 1,2
;

Find Invalid views

The below SQL can be used to identify invalid views .. (extract from the Unix script ..)

select 'select 1 from ${DATABASENAME}.'||TRIM(TableName)||' where 1 = 2; //
.IF ERRORCODE <> 0 THEN INSERT INTO temp_db.invalid_views values(''${DATABASENAME}'','''||TRIM(TableName)||''');' (TITLE '') from dbc.tables
where databasename = '${DATABASENAME}';

TPERF per user

Below script helps to check the amount of TPERF being used by a user. The eg. below uses 246 as the system TPERF, you have to change this to reflect your systems value.

SELECT dlt.UserName

,dlt.LogDate

/* Identify total CPU time used by queries executed by user for logdate */

,SUM(dlt.AMPCPUTime+ dlt.ParserCPUTime) AS Sum_CPUTime

/* Identify impact CPU time (ie to account for skew imbalance) used by queries executed by user for logdate */

,SUM((dlt.MaxAMPCPUTime*(HASHAMP()+1))+ dlt.ParserCPUTime) AS Sum_ImpactCPUTime

/* Identify total TPerfhours used by queries executed by user for logdate where system TPerf is 246 in this eg. */

,(Sum_ImpactCPUTime * 246.00) / (64.00 * 3600.00) AS Sum_ImpactTPerfHour

/* Remove 10% of avail TPerf for OS overhead */
,246.00 * 24 * 0.90 AS Sum_AvailTPerfHour
FROM dbqlogtbl_hst dlt
WHERE dlt.LogDate > CURRENT_DATE -95
AND dlt.UserName LIKE ANY ( 'UserName' )
GROUP BY 1,2;

Script to create base views (1 to 1 views)

SELECT
CASE WHEN ROW_NUMBER() OVER(PARTITION BY TABLENAME ORDER BY COLUMNID)=1
THEN 'REPLACE VIEW '||TRIM(DATABASENAME)||'_VIEW.'||TRIM(TABLENAME)||'
AS LOCKING ROW FOR ACCESS
SELECT '||TRIM(COLUMNNAME)
WHEN ROW_NUMBER() OVER(PARTITION BY TABLENAME ORDER BY COLUMNID DESC)=1
THEN ','||TRIM(COLUMNNAME)||' FROM '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
ELSE ','||TRIM(COLUMNNAME)
END
FROM DBC.COLUMNS
WHERE DATABASENAME=
ORDER BY TABLENAME,COLUMNID;

Releasing Online archive lock (V12)

One of our dictionary online backup failed due to not enough space in NETVAULT_CATALOG database. We used the below script to release the lock ..

.LOGON user/pwd;

LOGGING ONLINE ARCHIVE OFF FOR ("DBC") ALL;

RELEASE LOCK
("DBC") ALL;

.LOGOFF;

Pls make sure you run the script as (arcmain < release_lock.arc). The above did not work in the interactive mode.

Day of week SQL

There are different ways you could get this info ..

1.
SELECT CASE WHEN (DATE - DATE '1900-01-01') MOD 7 = 0 THEN 'Monday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 1 THEN 'Tuesday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 2 THEN 'Wednesday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 3 THEN 'Thursday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 4 THEN 'Friday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 5 THEN 'Saturday'
WHEN (DATE - DATE '1900-01-01') MOD 7 = 6 THEN 'Sunday'
END AS DAY_OF_WEEK;

2.
SELECT day_of_week FROM sys_calendar.calendar WHERE calendar_date = DATE;

3.
SELECT
SUBSTRING('Monday Tuesday Wednesday Thursday Friday Saturday Sunday '
FROM (1+10*((DATE - DATE '1900-01-01') MOD 7)) FOR 10)
AS DAY_OF_WEEK;

New Line Char in SQLAssistant

The '0D0A'xc char represents new line ..

SELECT 'ABC'|| '0D0A'xc || 'def'

Adding seconds to a Time Stamp

select current_time, current_time + interval '02' second;

Query to find Table Size

SELECT         DATABASENAME,

TABLENAME,

SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE"

FROM

DBC.TABLESIZE

WHERE

DATABASENAME = <'DATABASE_NAME'> AND TABLENAME = <'TABLE_NAME'>

GROUP BY 1,2;

============== query to find Database size ===

SELECT
        DATABASENAME DATABASE_NAME,
  SUM(MAXPERM)/(1024*1024*1024) TOTAL_PERM_SPACE,
        SUM(CURRENTPERM)/(1024*1024*1024) CURRENT_PERM_SPACE,
        TOTAL_PERM_SPACE-CURRENT_PERM_SPACE as FREE_SPACE
 FROM        DBC.DISKSPACE
WHERE        DATABASENAME = <'DATABASE_NAME'>
group by 1;

============

Query to List ALL database sizes

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

SEL TRIM(a.databasename) DBName
,SUM(maxperm)/(1024*1024*1024) (FORMAT 'zzz,zzz,zzz,zzz,999.99') AllocatedSpace
,SUM(currentperm)/(1024*1024*1024) (FORMAT 'ZZZ,ZZZ,ZZZ,ZZZ,999.99') UsedSpace
,(SUM(maxperm)-SUM(currentperm))/(1024*1024*1024) (FORMAT 'ZZZ,ZZZ,ZZZ,ZZZ,999.99') AvailSpace
,SUM(peakperm )/(1024*1024*1024) (FORMAT 'zzz,zzz,zzz,zzz,999.99') MaxSpaceDemand
, CAST ((UsedSpace/AllocatedSpace)*100 AS FLOAT FORMAT '999.9' ) PerCentUsed 
FROM dbc.diskspace a, dbc.databases b
WHERE maxperm > 0
AND a.databasename = b.databasename
and a.databasename like '%xxx%'
AND b.databasename in (SELECT child FROM dbc.children WHERE parent LIKE '%yyy%' UNION SELECT child FROM dbc.children WHERE child LIKE '%yyy%')
AND dbkind = 'd'
ORDER BY 5 DESC GROUP BY 1;

===========query to find no. of nodes,amps,and PE's ====

select count(t1.ProcId) Nodes, sum(t1.AmpCount) Amps, sum(t1.PECount) PEs
from table (MonitorPhysicalResource()) as t1
where t1.Status = 'U'
;


 Query for no of Nodes in a database(If RESUSAGE IS ENABLED):
Select count(distinct nodeid) from dbc.resusagescpu;


Number of Amps per node :
Select nodeid,count(distinct Vproc) from dbc.ResCpuUsageByAmpView group by 1;



Total no of amps in database :
Select Count( distinct vproc) from dbc.AmpUsage;
SELECT HASHAMP()+1;

==========================================

When do I need to collect statistics?
-->In the mentioned article, Ms. Ballinger recommends collecting full statistics on the following column types:
-->Non-indexed columns used in predicates
-->All NUSIs
-->USIs/UPIs if used in non-equality predicates (range constraints)
-->Most NUPIs (see below for a fuller discussion of NUPI statistic collection)
-->Full statistics always need to be collected on relevant columns and indexes on small tables (less than 100 rows per AMP)
-->PARTITION for all partitioned tables undergoing upward growth
-->Partitioning columns of a row-partitioned table