My TD Codes



REPLACE MACRO BTEQ_EXPORT
(
DATABASENAME VARCHAR(100),
TABLENAME VARCHAR(100),
FILENAME VARCHAR(500),
SERVER VARCHAR(100) default '' ,
USERNAME VARCHAR(300) default '',
PASS VARCHAR(15) default 'password'

)
AS
(

SEL
'                  
.SESSIONS 5
.SET ECHOREQ ON
.SET FORMAT OFF
.SET QUIET ON
.SET WIDTH 1000
.SET RETCANCEL ON
.LOGON  '   || :SERVER || '/' || :USERNAME || ', ' || :PASS || '; '  ||     '
.EXPORT REPORT FILE =' || :FILENAME BTEQ_SCRIPT
    FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE
 
  UNION
  SEL '                   SEL TOP 1000000            '
  FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE

  UNION

  SEL   '                 TRIM(COALESCE (CAST(' || COLUMNNAME ||  'AS VARCHAR(300)),' ||'27'xc || '27'xc || ')) ||  ' ||'27'xc ||'|' || '27'xc || '||'
FROM DBC.COLUMNS
WHERE TABLENAME =:TABLENAME
AND DATABASENAME =:DATABASENAME
AND COLUMNNAME NOT LIKE ANY ('')
UNION
SEL  '                  CAST('
FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE
 
 UNION

 SEL '               FROM ' || :DATABASENAME || '.' || :TABLENAME   || ' ;'
 FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE

UNION
SEL '                AS VARCHAR(1000))           '
 FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE

 UNION

 SEL '.EXPORT RESET .LOGOFF'
 FROM SYS_CALENDAR.CALENDAR
WHERE DATE=CALENDAR_DATE
;
);

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


REPLACE MACRO COL_CHK
       (
       DB CHAR(30)
       ,TBL CHAR(30)
       )
       AS
   
     (
     SELECT TXT
     FROM
     (
     SELECT
   
     COLUMNID,
     ',SUM(CASE WHEN ' || COLUMNNAME || ' IS NULL THEN 0 ELSE 1 END) * 100.00 /ROW_CNT AS ' || COLUMNNAME TXT
     FROM DBC.COLUMNS
     WHERE DATABASENAME = :DB
     AND TABLENAME = :TBL
   
     UNION
   
     SELECT 1, 'SELECT SUM(1) AS ROW_CNT'
     FROM SYS_CALENDAR.CALENDAR
     WHERE CALENDAR_DATE = CURRENT_DATE
   
     UNION
   
     SELECT 19999, 'FROM ' || DATABASENAME || '.' || TABLENAME
     FROM DBC.COLUMNS
     WHERE DATABASENAME = :DB
     AND TABLENAME = :TBL
     ORDER BY 1
     )X
     ORDER BY COLUMNID
     ;
     );

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


REPLACE MACRO GENERATE_INDEX_STAT (DATABASENAME VARCHAR(100) ,TABLENAME VARCHAR(100) )
AS
(
SEL FP (TITLE '')
FROM
(SEL A.INDEXNUMBER,
 '  COLLECT STATS ON  ' || TRIM(A.DATABASENAME) ||TRIM( '.' )|| TRIM(A.TABLENAME) || ' INDEX ( ' || TRIM(COLUMNNAME) FP

FROM DBC.INDICES A
JOIN DW_D_T.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION=1

UNION ALL


SELECT A.INDEXNUMBER,' ,' || TRIM(COLUMNNAME) SP
FROM DBC.INDICES A
JOIN DW_D_T.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION <MAXPOSITION AND COLUMNPOSITION >1

UNION ALL

SELECT A.INDEXNUMBER, '); ' LP
FROM DBC.INDICES A
JOIN DW_D_T.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION =MAXPOSITION AND COLUMNPOSITION =1

UNION ALL

SELECT A.INDEXNUMBER, ', ' || COLUMNNAME || ');' LP1
FROM DBC.INDICES A
JOIN DW_D_T.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION =MAXPOSITION AND MAXPOSITION >1
ORDER BY 1
)X

ORDER BY INDEXNUMBER,FP
;
)
;

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


REPLACE MACRO JS_SMT_1(DATABASENAME VARCHAR(100),TABLENAME VARCHAR(100))
AS
(
SEL
TRIM(DATABASENAME) ,TRIM(TABLENAME),TRIM(COLUMNNAME),
CASE
WHEN COLUMNTYPE='TS' THEN 'TIMESTAMP(0)'
WHEN COLUMNTYPE='I' THEN 'INTEGER'
WHEN COLUMNTYPE ='CV' THEN 'VARCHAR(' || TRIM(COLUMNLENGTH) ||  ')'
WHEN COLUMNTYPE ='CF' THEN 'CHAR(' || TRIM(COLUMNLENGTH)||  ')'
WHEN COLUMNTYPE ='D' THEN 'DECIMAL(' || TRIM(COLUMNLENGTH) || ')'
END AS DATA_TYPE
,NULLABLE
FROM
 DBC.COLUMNS
 WHERE TABLENAME=:TABLENAME
 AND DATABASENAME=:DATABASENAME
 ORDER BY 1,2;
 );

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


REPLACE MACRO RPT_OMNI_DTL_1 (START_DATE DATE,END_DATE DATE)
AS
(

DEL FROM WORK_SPACE.JS_OMNI_DTL_SS;
INSERT INTO WORK_SPACE.JS_OMNI_DTL_SS
SEL
 B.PAGE_VIEW_DT
, B.PAGE_VIEW_DTTM
, B.ENTP_GRP_ID
, B.CONTACT_ID
, B.WEB_VISIT_ID || '-' || B.PAGE_VIEW_SEQ ROW_KEY
 ,B.WEB_FORM_DESC

FROM DW_COL_V.OMNI_DTL_BAS B
WHERE
B.WEB_FORM_DESC LIKE '%BURN%'
AND B.WEB_FORM_DESC NOT LIKE '%USERDISABLED%'
AND B.SITE_ID = 4
AND B.PAGE_VIEW_DT >= :START_DATE
AND B.PAGE_VIEW_DT <= :END_DATE
AND B.ENTP_GRP_ID IS NOT NULL
;

SELECT SUB.PAGE_VIEW_DT
, SUB.ENTP_GRP_ID
, SUB.CONTACT_ID
, SUB.CONTACT_NAME
, SUB.CONTACT_TTL
, SUB.SFDC_EMAIL_OPT_OUT
, SUB.CONTACT_EMAIL_ADDR_TXT
, SUB.SFDC_PHONE_OPT_OUT
, SUB.CONTACT_TEL_NBR
, SUB.ACCT_NM
, SUB.DUNS_NBR
, SUB.LICENSE_ACTV_FLG
, USER_INFO.EGU_ROLE_DESC
, CASE WHEN USER_INFO.GRP_USER_ACTV_FLG = 'Y' THEN 'Y' ELSE 'N' END USER_ACTV_FLG
, SUM(CASE WHEN SUB.BURN_TYPE = 'CM' THEN 1 ELSE 0 END) CM_BURNS
, SUM(CASE WHEN SUB.BURN_TYPE = 'FT' THEN 1 ELSE 0 END) FT_BURNS
, SUM(CASE WHEN SUB.BURN_TYPE = 'FR' THEN 1 ELSE 0 END) FR_BURNS
, SUM(CASE WHEN SUB.BURN_TYPE = 'OA' THEN 1 ELSE 0 END) OA_BURNS
, SUM(CASE WHEN SUB.BURN_TYPE = 'DL' THEN 1 ELSE 0 END) DL_BURNS
FROM
(
SELECT
 B.ENTP_GRP_ID
, B.CONTACT_ID
,B.PAGE_VIEW_DT
, B.PAGE_VIEW_DTTM
,ROW_KEY
, CASE WHEN B.WEB_FORM_DESC LIKE '%CONNECTMAIL%' OR B.WEB_FORM_DESC LIKE 'LOW CREDIT CM%' THEN 'CM'
WHEN B.WEB_FORM_DESC LIKE '%FAMILY TREE%' THEN 'FT'
WHEN B.WEB_FORM_DESC LIKE '%FIRST RESEARCH%' THEN 'FR' WHEN B.WEB_FORM_DESC LIKE '%OPTIMIZER%' THEN 'OA'
WHEN B.WEB_FORM_DESC LIKE '%DOWNLOAD%' THEN 'DL' ELSE NULL END BURN_TYPE
, TRIM(C.CONTACT_FIRST_NM) || ' ' || TRIM(C.CONTACT_LAST_NM) CONTACT_NAME
, C.CONTACT_TTL
, CEA.CONTACT_EMAIL_ADDR_TXT
, CASE WHEN CEA.CONTACT_EMAIL_OPT_OUT_FLG = 'Y' THEN 'Y' ELSE 'N' END SFDC_EMAIL_OPT_OUT
, CTN.CONTACT_TEL_NBR
, CASE WHEN CTN.CONTACT_DO_NOT_CALL_FLG = 'Y' THEN 'Y' ELSE 'N' END SFDC_PHONE_OPT_OUT
, CASE WHEN ACCT.ACCT_NM IS NOT NULL THEN ACCT.ACCT_NM ELSE EG.ENTP_GRP_NM END ACCT_NM
, ACCT.DUNS_NBR
, CASE WHEN EGL.LICENSE_ACTV_FLG = 'Y' THEN 'Y' ELSE 'N' END LICENSE_ACTV_FLG
FROM WORK_SPACE.JS_OMNI_DTL_SS B
LEFT OUTER JOIN DW_V.CONTACT C
ON C.CONTACT_ID = B.CONTACT_ID
AND C.DW_CURR_FLG = 'Y'
LEFT OUTER JOIN DW_V.CONTACT_TEL_NBR CTN
ON B.CONTACT_ID = CTN.CONTACT_ID
LEFT OUTER JOIN DW_V.CONTACT_EMAIL_ADDR CEA
ON B.CONTACT_ID = CEA.CONTACT_ID
LEFT OUTER JOIN DW_COL_V.ENTP_GRP_LICENSE EGL
ON EGL.ENTP_GRP_ID = B.ENTP_GRP_ID
AND B.PAGE_VIEW_DT BETWEEN EGL.LICENSE_START_DT AND EGL.LICENSE_EXPIRE_DT
AND EGL.LICENSE_APPL_TYPE_DESC = 'HOL'
AND EGL.PRMRY_PROD_FLG = 'Y'
AND EGL.LICENSE_ACTV_FLG = 'Y'
JOIN DW_V.ENTP_GRP EG
ON B.ENTP_GRP_ID = EG.ENTP_GRP_ID
LEFT OUTER JOIN DW_V.ACCT ACCT
ON ACCT.ACCT_ID = EG.ACCT_ID
) SUB


LEFT OUTER JOIN

(
SELECT EGU.ENTP_GRP_ID
, EGU.CONTACT_ID
, CASE WHEN ROLE_DESC = 'ADMIN NON-USER' THEN 'NON-USER ADMIN'
WHEN ROLE_DESC = 'UNKNOWN USER' THEN 'ANONYMOUS USER'
ELSE ROLE_DESC END EGU_ROLE_DESC
, EGU.GRP_USER_ACTV_FLG
, SUM(1) OVER (PARTITION BY EGU.ENTP_GRP_ID, EGU.CONTACT_ID
ORDER BY EGU.GRP_USER_ACTV_FLG, EGU.ROLE_DESC ROWS UNBOUNDED PRECEDING
) AS SEQ_RANK
FROM
(SELECT ENTP_GRP_ID, CONTACT_ID, CASE WHEN GRP_USER_ROLE_DESC = 'NON-USER ADMIN' THEN 'ADMIN NON-USER'
WHEN GRP_USER_ROLE_DESC = 'ANONYMOUS USER' THEN 'UNKNOWN USER'
ELSE GRP_USER_ROLE_DESC  END ROLE_DESC,
GRP_USER_ACTV_FLG
FROM DW_V.ENTP_GRP_USER
) EGU
QUALIFY SEQ_RANK = 1

)  USER_INFO
ON USER_INFO.CONTACT_ID = SUB.CONTACT_ID
AND USER_INFO.ENTP_GRP_ID = SUB.ENTP_GRP_ID
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14
;


)
;

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


REPLACE MACRO SEL_VW_MAP --_1Ai
( DB VARCHAR(100), TB VARCHAR(100))
AS
(
SELECT HEAD
FROM
(SEL CAST(1 AS DECIMAL(13,0)) AS ROWID1 ,' REPLACE VIEW VW' || :DB||  '.' || :TB  || '  AS LOCKING ROW FOR ACCESS  SEL '  AS HEAD
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
UNION
SEL COLUMNID,CASE WHEN COLUMNID= MAXID THEN COLUMNNAME ELSE COLUMNNAME || ',' END AS DETAILS
FROM DBC.COLUMNS A
JOIN
(
SEL MAX(COLUMNID) AS MAXID,TABLENAME AS TBN ,DATABASENAME AS DBN   FROM DBC.COLUMNS  GROUP BY 2,3
) M
ON A.TABLENAME =M.TBN
AND A.DATABASENAME=M.DBN
WHERE DATABASENAME=:DB
AND TABLENAME=:TB

UNION
SEL 9999999,'FROM ' || :DB || '.' || :TB || ';' AS TAIL
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
) S
ORDER BY ROWID1
;
);

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

SPLText
CREATE  PROCEDURE DW_D_T.
SAMPLE_PROCEDURE
 (MY_TABLE VARCHAR(30),
MY_DATABASE VARCHAR(30))
BEGIN
DECLARE MY_COLUMNS VARCHAR(128)
DEFAULT '(COL1 INT );';
CALL DBC.SYSEXECSQL('DEL FROM   ' || :MY_DATABASE ||
'.' || :MY_TABLE ) ;
END;

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

SPLText
CREATE PROCEDURE
sp_sample_01

(
OUT out_message CHAR(30)
)
BEGIN
    SET out_message = 'Sample Stored Procedure';
END;

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

SPLText
CREATE PROCEDURE
sp_sample_03

(
IN    in_v1 INTEGER,
INOUT    io_v2 INTEGER,
OUT    out_v3 INTEGER
)
BEGIN
  SET io_v2 = io_v2 + in_v1 + 10;
  SET out_v3 = io_v2 + in_v1 + 20;
END;

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

SPLText
CREATE PROCEDURE
sp_sample_06
 (INOUT io_p1 INTEGER)
BEGIN
Label_One:
  WHILE io_p1 > 0 DO
    SET io_p1 = io_p1 - 1;
    IF io_p1 > 5 THEN
      ITERATE Label_One;
    END IF;
    INSERT t_log_table (:io_p1);
  END WHILE Label_One;
END;

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

SPLText
CREATE PROCEDURE DW_D_T.
TESt_INTEGER
 (INOUT COL1 INT)
 BEGIN
 SET COL1=COL1+1;
 END;

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

SPLText
REPLACE PROCEDURE
Test_Variable_Syntax
 (asofdate DATE)
MAIN: BEGIN

DECLARE caldt DATE;
DECLARE wom INTEGER;

SELECT Calendar_Date, Week_of_Month FROM sys_calendar.calendar
WHERE calendar_date = :asofdate
INTO :caldt, :wom
;

INSERT INTO Test_Variable_Syntax_Landing
( InsertType
,caldt
,weekofmonth
)
SELECT
'With:'
,Calendar_date
,:wom --<--- With
FROM sys_calendar.calendar
WHERE calendar_date = :caldt
;


INSERT INTO Test_Variable_Syntax_Landing
( InsertType
,caldt
,weekofmonth
)
SELECT
'Without:'
,Calendar_date
,wom --<--- Without
FROM sys_calendar.calendar
WHERE calendar_date = :caldt
;

END MAIN;

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


REPLACE VIEW COL_POP
AS
SEL
'SEL ' || '27'XC || TRIM(DATABASENAME) ||'27'XC|| ','
 '27'XC || TRIM(TABLENAME) ||'27'XC|| ',' || '27'XC || TRIM(COLUMNNAME) ||'27'XC || ',COUNT ( '|| TRIM(COLUMNNAME) ||')*100.00 /COUNT(1) PC,COUNT(1) ROW_COUNT,DATE
FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || '
WHERE PC <> 100
AND ROW_COUNT >0
 UNION'  X-- (TITLE 'INSERT INTO DW_D_ETL_TV.ETL_COL_POP_CHK')
FROM DBC.COLUMNS
WHERE DATABASENAME='DW_COL_T'
AND TABLENAME <> 'INDV_TBL'
;

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


REPLACE VIEW CONSUMPTIVE_QUERIES
AS
SEL
CAST(QUERYTEXT AS VARCHAR(5000)) QUERYTEXT
,AVG(ELAPSEDTIME                   ) ELAPSEDTIME                
,AVG(MAXAMPCPUTIME                 )MAXAMPCPUTIME
,AVG(AMPCPUTIME                    ) AMPCPUTIME
,AVG( EFECTIVEAMPCPUTIME               )               EFECTIVEAMPCPUTIME
,AVG(CPU_SKEWNESS)CPU_SKEWNESS
,AVG(SKEW_FACTOR) SKEW_FACTOR
,AVG(NUMOFACTIVEAMPS   )NUMOFACTIVEAMPS
,SUM(EFECTIVEAMPCPUTIME) AS SUM_EFECTIVEAMPCPUTIME
,COUNT(*) "COUNT"
FROM
(
SEL        

QUERYID
,CAST(QUERYTEXT AS VARCHAR(5000)) AS QUERYTEXT
,ELAPSEDTIME                
,MAXAMPCPUTIME              
,AMPCPUTIME                  
, MAXAMPCPUTIME*NUMOFACTIVEAMPS    EFECTIVEAMPCPUTIME          
,(1-(1.00* AMPCPUTIME/(MAXAMPCPUTIME*NUMOFACTIVEAMPS)))*100 CPU_SKEWNESS
,(MAXAMPCPUTIME*NUMOFACTIVEAMPS)/(AMPCPUTIME)  SKEW_FACTOR
,NUMOFACTIVEAMPS

FROM     DBC.QRYLOG
WHERE   MAXAMPCPUTIME>1
AND STARTTIME>=DATE-30
AND QUERYTEXT LIKE ANY ('%SEL%','%INSERT%','%UPDATE%','%CREATE%','%DEL%')
) X

GROUP BY 1

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


REPLACE VIEW dbc_diskspace_v
                (
                databasename,
                current_perm_gb,
                max_perm_gb                )
                AS LOCKING ROW FOR ACCESS
                select databasename
                , sum(currentperm)/1024/1024/1024 current_perm_gb
                , sum(maxperm)/1024/1024/1024 max_perm_gb
                from DBC.DiskSpace
                where maxperm > 0
                group by databasename;

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


REPLACE VIEW DUP_CHK
AS
SEL
'SEL ' || '27'XC || TRIM(DATABASENAME) || '27'XC ||','  || '27'XC || TRIM(TABLENAME) ||  '27'XC || ' ,COUNT(1)
FROM ' ||   TRIM(DATABASENAME) || '.' ||TRIM(TABLENAME) || '0A'XC || 'MINUS SEL' ||  '27'XC || TRIM(DATABASENAME) ||  '27'XC || ',' ||  '27'XC || TRIM(TABLENAME) || '27'XC ||
',COUNT(1) FROM (SEL DISTINCT * FROM ' ||
 TRIM(DATABASENAME) || '.' ||TRIM(TABLENAME) || ')X;' X
FROM DBC.TABLES
WHERE DATABASENAME='DW_COL_T'
;

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


REPLACE VIEW FLAG_CHK_GENERIC_QUERY
AS
SEL
'
SEL ' || '27'xc || TRIM(DATABASENAME)  || '27'XC || ',' || '27'XC || TRIM(TABLENAME)  || '27'XC || ',' || '27'xc ||' Curr Flg Violation '
||'27'xc || '
FROM ' || trim(databasename) ||  '.' || trim(tablename) ||
' WHERE
(dw_curr_flg="y"
and date not between dw_eff_start_dttm and dw_eff_end_dttm
)
or
(
dw_curr_flg<>"n"
and date+1 between dw_eff_start_dttm and dw_eff_end_dttm
);INSERT INTO DW_D_ETL_TV.ETL_DATA_VAL
(DATABASENAME,TABLENAME,TEST_CASE)
' X
FROM DBC.COLUMNS
WHERE DATABASENAME IN ('DW_T','DW_COL_T')
AND COLUMNNAME='DW_CURR_FLG'

;

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


REPLACE VIEW MACRO_GEN_STATS
AS
SEL 'EXEC GENERATE_INDEX_STATS (' || '27'XC || TRIM(DATABASENAME) ||'27'XC ||',' || '27'XC || TRIM(TABLENAME) ||'27'XC || ');' AS X
FROM DBC.TABLES
WHERE

DATABASENAME IN ('DW_D_T','DW_D_COL_T','DW_ABS_T','DW_D_ETL_TV')
AND TABLEKIND='T'
AND TABLENAME NOT LIKE ALL ('%BKP%','%BKUP%','%TEMP%')
AND
(DATABASENAME,TABLENAME ) NOT IN
(
SEL DATABASENAME,TABLENAME
FROM DBC.COLUMNSTATS
WHERE (DATABASENAME,TABLENAME) NOT IN
(

SEL DATABASENAME,TABLENAME
FROM DBC.COLUMNSTATS
WHERE FIELDSTATISTICS IS NOT NULL
)
)
;

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


REPLACE VIEW STATS_BASICS_32BIT AS
/** CAUTION: USE THE RIGHT VERSION FOR YOUR SYSTEM.
    THIS IS THE 32-BIT VERSION FOR TERADATA ON MP-RAS AND TERADATA EXPRESS
**/
SELECT
  DATABASENAME,
  TABLENAME,
  COLUMNNAME,
  LASTALTERTIMESTAMP,
  COLUMNCOUNT,
  STATSTYPE,

    /** TD12 CHANGED THE HASHBUCKET FUNCTION (16 BIT VS. 20 BIT),
        ON TD12 (USING 20 BITS FOR HASHBUCKETS) THE RESULT MUST BE DIVIDED BY 16 **/
  ((HASHBUCKET()+1)/65536) AS TD12,

  SUBSTR(STATS, 1, 4) AS COLLECTDATE_,

  SUBSTR(STATS, 5, 4) AS COLLECTTIME_,

  POSITION(SUBSTR(STATS, 9, 1) IN '010203'XB) AS STATSVERSION,

/*** DIFFERENCES BETWEEN 32- AND 64-BIT START HERE ---> ***/

      CASE
        WHEN HASHBUCKET ('00'XB || SUBSTR(STATS, 11, 1) (BYTE(4))) / TD12 = 1
        THEN HASHBUCKET ('00'XB || SUBSTR(STATS, 12, 1) (BYTE(4))) / TD12
      END AS SAMPLESIZE,

      SUBSTR(STATS, 13, 8) AS NUMNULLS_,

      SUBSTR(STATS, 21, 2) AS NUMINTERVALS_,

      CASE
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 25, 8)
      END AS NUMALLNULLS_,

      CASE
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 33, 8)
      END AS AVGAMPRPV_,

      CASE
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 57, 2)
      END AS NUMAMPS_,

      CASE
        WHEN SUBSTR(STATS, 23, 1)  = '01'XB THEN 16
        ELSE 32
      END AS OFFSET,

      CASE
        WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 25 + OFFSET, 8)
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 59 + OFFSET, 8)
      END AS MODEFREQ_,

      CASE
        WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 25 + OFFSET + 8, 8)
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 59 + OFFSET + 8, 8)
      END AS NUMVALUES_,

      CASE
        WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 25 + OFFSET + 16, 8)
        WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 59 + OFFSET + 16, 8)
      END AS NUMROWS_

/*** DIFFERENCES BETWEEN 32- AND 64-BIT END HERE <--- ***/

FROM STATS_DATA
;

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


REPLACE VIEW STATS_BASICS_64BIT AS
/** CAUTION: USE THE RIGHT VERSION FOR YOUR SYSTEM.
    THIS IS THE 64-BIT VERSION FOR TERADATA ON LINUX AND WINDOWS.
**/
SELECT
  DATABASENAME,
  TABLENAME,
  COLUMNNAME,
  LASTALTERTIMESTAMP,
  COLUMNCOUNT,
  STATSTYPE,

    /** TD12 CHANGED THE HASHBUCKET FUNCTION (16 BIT VS. 20 BIT),
        ON TD12 (USING 20 BITS FOR HASHBUCKETS) THE RESULT MUST BE DIVIDED BY 16 **/
  ((HASHBUCKET()+1)/65536) AS TD12,

  SUBSTR(STATS, 1, 4) AS COLLECTDATE_,

  SUBSTR(STATS, 5, 4) AS COLLECTTIME_,

  POSITION(SUBSTR(STATS, 9, 1) IN '010203'XB) AS STATSVERSION,

/*** DIFFERENCES BETWEEN 32- AND 64-BIT START HERE ---> ***/
  CASE
    WHEN HASHBUCKET ('00'XB || SUBSTR(STATS, 11, 1) (BYTE(4))) / TD12 = 1
    THEN HASHBUCKET ('00'XB || SUBSTR(STATS, 12, 1) (BYTE(4))) / TD12
  END AS SAMPLESIZE,

  SUBSTR(STATS, 13 + 4, 8) AS NUMNULLS_,

  SUBSTR(STATS, 21 + 4, 2) AS NUMINTERVALS_,

  CASE
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 25 + 8, 8)
  END AS NUMALLNULLS_,

  CASE
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 33 + 8 , 8)
  END AS AVGAMPRPV_,

  CASE
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 57 + 8, 2)
  END AS NUMAMPS_,

  CASE
    WHEN SUBSTR(STATS, 23 + 4, 1)  = '01'XB THEN 16
    ELSE 32
  END AS OFFSET,

  CASE
    WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 33 + OFFSET, 8)
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 73 + OFFSET, 8)
  END AS MODEFREQ_,

  CASE
    WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 33 + OFFSET + 8, 8)
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 73 + OFFSET + 8, 8)
  END AS NUMVALUES_,

  CASE
    WHEN STATSVERSION < 3 THEN SUBSTR(STATS, 33 + OFFSET + 16, 8)
    WHEN STATSVERSION = 3 THEN SUBSTR(STATS, 73 + OFFSET + 16, 8)
  END AS NUMROWS_

/*** DIFFERENCES BETWEEN 32- AND 64-BIT END HERE <--- ***/

FROM STATS_DATA
;

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


REPLACE VIEW STATS_DATA AS
SELECT
  D.DATABASENAME AS DATABASENAME,
  T.TVMNAME AS TABLENAME,
  MAX(CASE WHEN I.FIELDPOSITION = 1
      THEN (CASE WHEN I.FIELDID = 0 THEN 'PARTITION'
                 ELSE TRIM(C.FIELDNAME)
            END)
      ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 2  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 3  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 4  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 5  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 6  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 7  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 8  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 9  THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 10 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 11 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 12 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 13 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 14 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 15 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION = 16 THEN ',' || TRIM(C.FIELDNAME) ELSE '' END) ||
  MAX(CASE WHEN I.FIELDPOSITION > 16 THEN ',...' ELSE '' END) AS COLUMNNAME,

  MAX(I.LASTALTERTIMESTAMP) AS LASTALTERTIMESTAMP,

  COUNT(*) AS COLUMNCOUNT,

  CASE INDEXTYPE
    WHEN 'M' THEN 'M'
    WHEN 'D' THEN 'D'
    ELSE 'I'
  END AS STATSTYPE,

  CAST(MAX(SUBSTR(I.INDEXSTATISTICS, 1, 128)) AS VARBYTE(128)) AS STATS

FROM DBC.INDEXES I
JOIN DBC.TVM T
  ON T.TVMID = I.TABLEID
JOIN DBC.DBASE D
  ON T.DATABASEID = D.DATABASEID
LEFT JOIN DBC.TVFIELDS C
  ON C.TABLEID = I.TABLEID
  AND C.FIELDID = I.FIELDID
GROUP BY
  DATABASENAME,
  TABLENAME,
  STATSTYPE,
  I.INDEXNUMBER
HAVING STATS IS NOT NULL

UNION ALL

SELECT
  D.DATABASENAME AS DATABASENAME,
  T.TVMNAME AS TABLENAME,
  C.FIELDNAME AS COLUMNNAME,
  C.LASTALTERTIMESTAMP,
  1 AS COLUMNCOUNT,
  'C' AS STATSTYPE,
  CAST(SUBSTR(C.FIELDSTATISTICS, 1, 128) AS VARBYTE(128)) AS STATS
FROM
  DBC.DBASE D
JOIN DBC.TVM T
  ON D.DATABASEID = T.DATABASEID
JOIN DBC.TVFIELDS C
  ON T.TVMID = C.TABLEID
WHERE STATS IS NOT NULL
;

Comments

Post a Comment

Popular posts from this blog

The Secret behind A B I N I T I O.

Background on Abinitio : Abinitio is considered one of the major ETL players  in line with informatica ,datastage ,SSIS ( if it can be considered major ETL player ;-) ) .In fact, in most respects it is considered better than all of the above . It has user interface and debugging simpler than Informatica  ( one of the positive points about informatica ) ,It has parallelism which data stage may still take years to reach . Superiority : (HearSay ) It has the ability to convert a 6 people ,6 month ETL project to 2 month 4 people ETL project . (Sounds hyperbole ,But yet it has been established in many cases that it takes half the development time compared to INFA with much better quality of code ) . Other statistics suggest that if you call Abinitio ,there is 75% chance you will be talking to a PhD holder.In other words it is considered to be ingenously simple. Secrecy : In spite of all this there is very little you would know about it (If at all ) . Reason : It is so because Abi
Read more

Finding Skewed Tables in Teradata

       Skewed Tables in Teradata                                                                  Teradata distributes its data for a given table based on its Primary Index and Primary Index alone .If this Primary Index is not selected appropriately it can cause performance bottle necks. Following are general parameters we should consider while creating Primary Indexes       1:Access Path       2:Volatility       3: Data Distribution        Volatility is usually not an issue in a well designed database .That is to say we do not expect update clauses updating the primary index itself. This usually leaves us with data distribution and Access Path.       Access Path implies particular column ( set of columns ) are always used in join conditions .Advantage of using these columns as PI is that it will avoid redistribution of data during join .( One of the most expensive operation for teradata ) and therefore can reduce usage of Spool files.        Data distribution implies
Read more

Macro to generate Mload Script

DROP MACRO MLOAD_EXPORT REPLACE MACRO MLOAD_EXPORT ( DB VARCHAR(100), TB VARCHAR(100), PATH VARCHAR(100), FS VARCHAR(100) ) AS ( SEL 1,CAST(1 AS BIGINT), CAST('.DATEFORM ANSIDATE; .LOGON server/username,password; .LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1; DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1; DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1; DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1; .ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ; .BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || ' WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1 ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1 ERRLIMIT          0 CHECKPOINT     100000 TENACITY 10000 SESSIONS        10 SLEEP 6 ; .LAYOUT InputFileLayout; ' AS VARCHAR(6000)) FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=DATE UNION SEL 2,CAST(COLUMNID AS BIGINT), CAST('.FI
Read more