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 RETLIMIT 1000000
.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 ('%SSN%','%ACCNO%','%ACCT_NO%')
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 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
/*AND TABLENAME NOT LIKE 'STG_EM_%'
AND TABLENAME NOT LIKE 'STG_TMP_%'
AND TABLENAME NOT LIKE 'STG_W_IM_%'*/
UNION
SEL 9999999,'FROM ' || :DB || '.' || :TB || ';' AS TAIL
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
) S
ORDER BY ROWID1
;
);
--------------------------------------------------------------------------------
REPLACE MACRO .J_TABLESIZE (DATABASENAME VARCHAR(100),TABLENAME VARCHAR(100))
AS
(
SEL DATABASENAME,TABLENAME,SUM(CURRENTPERM)/(1024.0*1024.0) SPACE_IN_MB
,SUM(CURRENTPERM)/(1024.0*1024.0*1024.0) SPACE_IN_GB ,MAX(CURRENTPERM) *144*1.0 /(1024*1024.0) EFFECTIVE_SPACE_IN_ODIN_IN_MB
FROM DBC.TABLESIZE

WHERE
DATABASENAME =:DATABASENAME
AND
TABLENAME =:TABLENAME
GROUP BY 1,2
;
) ;
--------------------------------------------------------------------------------
REPLACE MACRO col_chk
       (
       db CHAR(30)
       ,tbl CHAR(30)
       )
       AS
    
     (
     SELECT
    
     columnid,
     ',sum(case when ' || columnname || ' is null then 0 else 1 end) * 100.00 /row_cnt as ' || columnname
     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
     ;
     );
--------------------------------------------------------------------------------
REPLACE MACRO .SEL_VW_MAP --_1Ai
( DB VARCHAR(100), TB VARCHAR(100))
AS
(
SEL CAST(1 AS DECIMAL(13,0)),' REPLACE VIEW VW' || :DB||  '.' || :TB  || '  AS LOCKING ROW FOR ACCESS  SEL '
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
UNION
SEL COLUMNID,COLUMNNAME || ','
FROM DBC.COLUMNS
WHERE DATABASENAME=:DB
AND TABLENAME=:TB
AND TABLENAME NOT LIKE 'STG_EM_%'
AND TABLENAME NOT LIKE 'STG_TMP_%'
AND TABLENAME NOT LIKE 'STG_W_IM_%'
UNION
SEL 9999999,'FROM ' || :DB || '.' || :TB || ';'
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
;
);
--------------------------------------------------------------------------------
REPLACE MACRO .BTEQ_EXPORT1
(
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 RETLIMIT 1000000
.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 ('%SSN%','%ACCNO%','%ACCT_NO%')
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 .SEL_VW_MAP_1Ai
( DB VARCHAR(100), TB VARCHAR(100))
AS
(
SEL CAST(1 AS DECIMAL(13,0)),' REPLACE VIEW VW' || :DB||  '.' || :TB  || '  AS LOCKING ROW FOR ACCESS  SEL '
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
UNION
SEL COLUMNID,COLUMNNAME || ','
FROM DBC.COLUMNS
WHERE DATABASENAME=:DB
AND TABLENAME=:TB
UNION
SEL 9999999,'FROM ' || :DB || '.' || :TB || ';'
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
;
);
--------------------------------------------------------------------------------
REPLACE MACRO .J_DATABASE_OCCUPIED_SPACE (DATABASENAME VARCHAR(100))
AS
(
SEL DATABASENAME,TABLENAME,SUM(CURRENTPERM)/(1024.0*1024.0) SPACE_IN_MB
,SUM(CURRENTPERM)/(1024.0*1024.0*1024.0) SPACE_IN_GB ,MAX(CURRENTPERM) *MF*1.0 /(1024*1024.0) EFFECTIVE_SPACE_IN_ODIN_IN_MB
FROM DBC.TABLESIZE
,
(SEL COUNT(DISTINCT VPROC) MF FROM DBC.TABLESIZE) X
WHERE
DATABASENAME =:DATABASENAME
/*AND
TABLENAME =:TABLENAME
*/GROUP BY 1,2
;
) ;
--------------------------------------------------------------------------------
REPLACE MACRO .DATABASE_COMPARISON
(DEV_DB VARCHAR(100)
,SIT_DB VARCHAR(100)
)
AS
(
SEL ' Following Talbe Could not be found on SIT schema but present in dev scehma <Ignore if nothing follows >'
;
SEL A.TABLENAME
FROM DBC.TABLES A
WHERE
A.DATABASENAME=:DEV_DB
AND TABLEKIND='T'
AND A.TABLENAME NOT IN
(SEL A.TABLENAME
FROM DBC.TABLES
WHERE DATABASENAME=:SIT_DB
AND TABLEKIND='T')
ORDER BY 1
;
  SEL ' Following Talbe Could not be found on DEV schema but present in SIT scehma  <Ignore if nothing follows > ' ;
 
  SEL A.TABLENAME
FROM DBC.TABLES A
WHERE
A.DATABASENAME=:SIT_DB
AND TABLEKIND='T'
AND A.TABLENAME NOT IN
(SEL A.TABLENAME
FROM DBC.TABLES
WHERE DATABASENAME=:DEV_DB
AND TABLEKIND='T')
ORDER BY 1
;
SEL 'Column Level Discrepancies <Ignore if nothing follows >';

(SEL
'DEV',A.TABLENAME ,A.COLUMNNAME,
CASE
WHEN A.COLUMNTYPE ='I' THEN 'INTEGER'
WHEN A.COLUMNTYPE='D' THEN 'DECIMAL'
WHEN A.COLUMNTYPE='CF' THEN 'CHAR'
WHEN A.COLUMNTYPE='CV' THEN 'VARCHAR'
WHEN A.COLUMNTYPE='DA' THEN 'DATE'
WHEN A.COLUMNTYPE='I8' THEN 'BIGINT'
ELSE A.COLUMNTYPE
END  AS DATATYPE
,A.COLUMNLENGTH
FROM DBC.COLUMNS A
WHERE
A.DATABASENAME=:DEV_DB
AND
TABLENAME NOT IN
(
SEL A.TABLENAME
FROM DBC.TABLES A
WHERE
A.DATABASENAME=:DEV_DB
--AND TABLEKIND='T'
AND A.TABLENAME NOT IN
(SEL A.TABLENAME
FROM DBC.TABLES
WHERE DATABASENAME=:SIT_DB
--AND TABLEKIND='T')
)
)
MINUS
SEL
'DEV',A.TABLENAME,A.COLUMNNAME,
CASE
WHEN A.COLUMNTYPE ='I' THEN 'INTEGER'
WHEN A.COLUMNTYPE='D' THEN 'DECIMAL'
WHEN A.COLUMNTYPE='CF' THEN 'CHAR'
WHEN A.COLUMNTYPE='CV' THEN 'VARCHAR'
WHEN A.COLUMNTYPE='DA' THEN 'DATE'
WHEN A.COLUMNTYPE='I8' THEN 'BIGINT'
ELSE A.COLUMNTYPE
END DATATYPE,
A.COLUMNLENGTH
FROM DBC.COLUMNS A
WHERE
A.DATABASENAME=:SIT_DB
)
UNION
(
SEL
'SIT',A.TABLENAME SIT_tABL,A.COLUMNNAME,
CASE
WHEN A.COLUMNTYPE ='I' THEN 'INTEGER'
WHEN A.COLUMNTYPE='D' THEN 'DECIMAL'
WHEN A.COLUMNTYPE='CF' THEN 'CHAR'
WHEN A.COLUMNTYPE='CV' THEN 'VARCHAR'
WHEN A.COLUMNTYPE='DA' THEN 'DATE'
WHEN A.COLUMNTYPE='I8' THEN 'BIGINT'
ELSE A.COLUMNTYPE
END  AS DATATYPE
,A.COLUMNLENGTH
FROM DBC.COLUMNS A
WHERE
A.DATABASENAME=:SIT_DB
MINUS
SEL
'SIT' ,A.TABLENAME,A.COLUMNNAME,
CASE
WHEN A.COLUMNTYPE ='I' THEN 'INTEGER'
WHEN A.COLUMNTYPE='D' THEN 'DECIMAL'
WHEN A.COLUMNTYPE='CF' THEN 'CHAR'
WHEN A.COLUMNTYPE='CV' THEN 'VARCHAR'
WHEN A.COLUMNTYPE='DA' THEN 'DATE'
WHEN A.COLUMNTYPE='I8' THEN 'BIGINT'
ELSE A.COLUMNTYPE
END DATATYPE,
A.COLUMNLENGTH
FROM DBC.COLUMNS A
WHERE
A.DATABASENAME=:DEV_DB
)
ORDER BY 2,3,1
;


);

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