All Forums Database
kjcohen 12 posts Joined 01/12
25 Jan 2012
Stored procedures

Currently I have a SQL statement that will generate an answer set of INSERT statements.

I want to write a stored procedure that will first run my initial SQL with a variable for databasename, after inputting the databasename it will perform SQL that will generate the SQL for the insert statements (lots of them).

I then want it to run all of those individual INSERT statements so they load into a hard coded table.

Any help would be great.

dnoeth 4628 posts Joined 11/04
25 Jan 2012

Of course it's possible within an SP, but "lots of INSERT statements" are probably the wrong way to handle it.

Are there multiple source/target tables for the INSERTs?

Could you share your existing logic to create those INSERTs?

Dieter

 

Dieter

dnoeth 4628 posts Joined 11/04
25 Jan 2012

Ok, it's different source tables for each INSERT, so there's no way to optimize the number of INSERTs.

But you should change some syntax:

The row count should be calculated without DatabaseName etc:

(SELECT CAST(COUNT(*) AS DECIMAL(18,0))

The same for the count per value, the outer most select should add 'DatabaseName, TableName, ColumnName'

Finally the percentage calculation should be simplified to:

TRIM(100 * Y/X)

Then it's just a CURSOR using this SELECT and a "CALL DBC.SysExecSQL(:variable_containing_Insert_statement);" for each row.

 

Btw, if you don't need fully detailed information info about row count and the number of rows for the most common values can also be extracted using HELP STATS when stats have been collected

Dieter

Dieter

dnoeth 4628 posts Joined 11/04
25 Jan 2012

Check the FOR syntax and the rules for dynamic SQL.

Depending on your TD release you can use dbc.SysExecSQL or EXECUTE IMMEDIATE to dynamically submit the Inserts.

FOR cur AS 
SELECT 'INSERT INTO UDWETLSANDBOX.PRFL_COL_DOMAIN '||
'SELECT '||            
 ...
                'GROUP BY 1,2,3,4) AS X (XDB,XTB,XCL,XCDV,XNV,XPERV,XTS);' AS str
FROM    DBC.COLUMNS A
                WHERE A.DATABASENAME = 'UDW1ETLSRCREP'
                AND A.COLUMNNAME LIKE '%CD%'
 DO
       CALL dbc.SysExecSQL(cur.str)  ;
END FOR;

Dieter

Dieter

dnoeth 4628 posts Joined 11/04
25 Jan 2012

This is a SP to collect row counts from all tables within a database, it's very basic, no error checking etc.

But it shows a cursor and dynamic SQL using dbc.SysExecSQL or EXECUTE IMMEDIATE:

CREATE SET TABLE RowCounts
     (
      DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      TableName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      RowCount DECIMAL(18,0),
      COllectTimeStamp TIMESTAMP(0))
PRIMARY INDEX ( DatabaseName ,TableName )
;
 

REPLACE PROCEDURE GetRowCounts(IN DBName VARCHAR(30))
BEGIN
  DECLARE SqlTxt VARCHAR(500);
   FOR cur AS
      SELECT
         TRIM(DatabaseName) AS DBName,
         TRIM(TableName) AS TabName
      FROM dbc.Tables
      WHERE DatabaseName = :DBName
      AND TableKind = 'T'
   DO
     SET SqlTxt =
         'INSERT INTO RowCounts ' ||
         'SELECT ' ||
         '''' || cur.DBName || '''' || ',' ||
         '''' || cur.TabName || '''' || ',' ||
         'count(*)' ||  ',' ||
         'CURRENT_TIMESTAMP(0) ' ||
         'FROM ' || cur.DBName ||
         '.' || cur.TabName || ';';
         
     CALL dbc.sysexecsql(:SqlTxt);
     -- EXECUTE IMMEDIATE sqlTxt;
   END FOR;
END;
 

Dieter

Dieter

kjcohen 12 posts Joined 01/12
25 Jan 2012

Thanks again! I tried running this after configuring it to line up with what I am doing but I am having an issue actually calling the procedure once its created.

I limited because of a SELECT WITH GRANT OPTION access problem, is this because the database I made the SP in does not have access, or because personally I do not have access?

kjcohen 12 posts Joined 01/12
25 Jan 2012

Resolved my issues thanks so much!

kk17 3 posts Joined 01/12
28 Jan 2012

 

Hai I am new for teradata can any one tell me what it mean by indicators dashboard? And what is use of it?

Thank you

jerboa 10 posts Joined 04/12
22 May 2012

Hello everyone!

I can't create stored procedure which will execute (call) other existing stored procedures.

Can somebody help me?

Vijay Karumudi 1 post Joined 07/12
11 Jul 2012

You might be getting the error,

'' An owner referenced by the user does not have execute permissions on the store procedure xyz ''.

To resolve this, you must have the execute grant access on the inner store procedure(xyz) to execute it in another procedure.

Administrator can grant the access.

(Additional Info : You may also need to check the SQL SECURITY OWNER/CREATOR info on both the store procedures. )

 

- Vijay Karumudi

 

slemmer 4 posts Joined 03/15
28 Dec 2015
REPLACE PROCEDURE UTILITY.sp_DBA_SECURITY_NewDatabaseSetup

 (IN DB_System VARCHAR(60))

 --ETL_ROLE -------------------------------------------------------------
  --_DWS_DB (Staging Database)
  BEGIN
    DECLARE SqlTxt VARCHAR(500);
    
    
    FOR cur AS
     SELECT TRIM(DatabaseName) as DBName
     from DBC.TablesV
     WHERE TRIM(DatabaseName) = :DB_System||'_DWS_DB'
     and TableKind = 'T'
    DO
    SET SqlTxt = 'GRANT
     SELECT,
     EXECUTE,
     EXECUTE PROCEDURE,  
     CREATE TABLE,
     DROP TABLE, 
     INSERT, 
     DELETE,  
     UPDATE
    ON "'||cur.DBName||'" TO "ETL_ROLE";';
    --EXECUTE IMMEDIATE sqlTxt;
    CALL dbc.sysexecsql(:SqlTxt);
   END FOR;
  END;

Any ideas why this code will not grant privileges?  It runs without error but does not assign privileges.  Even with a grant statement without using result of cursor does not work.
--Thank you
 
 

You must sign in to leave a comment.