Teradata - Running query passed as parameter

Question

I am trying to learn Teradata and trying to simplify the way we copy data from production DB to testing DB for testing.

For this process we need to fill up an excel with below details and send to our TDBA:-

Prod Table DbName
Prod Table Name
Prod Table Perm_Size
Prod Table GB Size
Prod Table Record Count
Filter SQL Query to fetch data From Prod DB
Filtered Record Count

I was trying to create a simple SQL utility that accepts parameters (Italics and Bold in the list above) and output the remaining fields.

I kind of started but got stuck in creating and running queries passed as parameters. I also tried using parameters as '?dbName' etc to accept values at run time. But wasn't able to fix that too. Any guidance would be great.

WITH ParamInp(dbName, tblName, fltrQry) AS
(SELECT 'PRDDB', 'EMPL', 'SELECT * FROM PRDVIEWS.EMPL WHERE ID IN (1,2,3)') -- We have select access only on PRDVIEWS schema
SELECT 
    Upper(Trim(ParamInp.dbName)) AS DATABASENAME,
    Substr(Upper(Trim(ParamInp.dbName)), 1, Length(Trim(ParamInp.dbName))-2) || 'VIEWS' AS VIEWNAME, -- Creating View DB schema name
    Upper(Trim(ParamInp.tblName)) AS TABLENAME,
    fltrQry AS FILTER_QUERY,  -- do not want to execute fltrQry here. It is only to include in the excel
    Sum(currentperm) AS PERM_SIZE,
    Sum(currentperm)/1024**3 AS TOTAL_SIZE, -- GigaByte
    (SELECT Cast(Count(*) AS BIGINT) FROM (Substr(Upper(Trim(dbName)), 1, Length(Trim(dbName))-2) || 'VIEWS').Upper(Trim(tblName)) ) 
        AS TOTAL_COUNT,     -- Unable to get this working
    (SELECT Cast(Count(*) AS BIGINT) FROM (ParamInp.fltrQry)) 
        AS FILTERED_COUNT   -- This is where fltrQry should run
FROM dbc.allspace, ParamInp 
WHERE TABLENAME = ParamInp.tblName   
AND databasename = ParamInp.dbName   
GROUP BY 1,2,3
ORDER BY 1,2;

I think I will not be able to do it in one query. In that case, how should I approach this. I run my queries in Teradata SQL assistant and sometimes get lists of tables to be loaded from production.

I expect the output as

DATABASENAME  | VIEWNAME  | TABLENAME  | FILTER_QUERY                       | PERM_SIZE  | TOTAL_SIZE  | TOTAL_COUNT  | FILTERED_COUNT  |
--------------------------------------------------------------------------------------------------------------------------
PRDDB         | PRDVIEWS  | EMPL       | SELECT * FROM PRDVIEWS.EMPL WHERE ID IN (1,2,3)  | 1111111    | 2.2         | 333333       | 444             |

You're trying to pass a tablename as a string parameter, which no DBMS allows. You need Dynamic SQL, which can be used only in a Stored Procedure. — dnoeth, Mar 02 '18 at 12:45

score 0 · Answer 1 · answered Mar 02 '18 at 21:01

Other then Stored procedure you may do it with a two step approach in bteq.

with a select concatenate the needed sql-command(s), inserting the parameters where needed. the result is exported into a file
run these created sql-commands from the file

I did not test the following, it is intended to show the general idea. I am sure some additional tweaking is necessary to get the syntax of generated command(s) right.

.logon tdpid/user,pass
.set format off
.set titledashes off
.export file /tmp/myQuery.bteq
select 'SELECT 
    Upper(Trim('||dbName||')) AS DATABASENAME,
    Substr(Upper(Trim('||dbName||')), 1, Length(Trim('||dbName||'))-2) || 'VIEWS AS VIEWNAME,
    Upper(Trim('||tblName||')) AS TABLENAME,
    '||fltrQry||' AS FILTER_QUERY,
    Sum(currentperm) AS PERM_SIZE,
    Sum(currentperm)/1024**3 AS TOTAL_SIZE,
    (SELECT Cast(Count(*) AS BIGINT) FROM (Substr(Upper(Trim('||dbName||')), 1, Length(Trim('||dbName||'))-2) || 'VIEWS).Upper(Trim('||tblName||')) ) 
        AS TOTAL_COUNT,
    (SELECT Cast(Count(*) AS BIGINT) FROM ('||fltrQry||')) 
        AS FILTERED_COUNT 
FROM dbc.allspace 
WHERE TABLENAME = '||tblName||'   
AND databasename = '||dbName||'   
GROUP BY 1,2,3
ORDER BY 1,2;'  (TITLE '')
from (
SELECT 'PRDDB' as dbName, 'EMPL' as tblName, 'SELECT * FROM PRDVIEWS.EMPL WHERE ID IN (1,2,3)' as fltrQry
) as commands;
.export reset
.run file = /tmp/myQuery.bteq

Teradata - Running query passed as parameter

1 Answers1