15.00 - Names Subject to Object Naming Rules - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Names Subject to Object Naming Rules

 

Name

Example Usage

ACCOUNT (acc)

CREATE USER u1 as perm=10e6, password = pass1, account=acc2’, default journal table=u1jnl;

MODIFY USER u1 AS ACCOUNT=’acc1’;

BEGIN QUERY LOGGING WITH SQL ON ALL ACCOUNT =’acc1’;

CREATE PROFILE prof5 AS PERM=10e5 PASSWORD=pass5 ACCOUNT= ‘acc3’;

ATTRIBUTE (att)

CREATE TYPE udt2 AS (att1 INT, att2 DATE) NOT FINAL;

ALTER TYPE udt2 ADD ATTRIBUTE att3 FLOAT;

AUTHORIZATION (auth)

CREATE AUTHORIZATION db1.auth1 as INVOKER USER 'bdUsr' PASSWORD 'bdPsswd';

COLUMN (col)

CREATE FUNCTION fn1 (NumRows INTEGER)
RETURNS TABLE
(col1 INTEGER,
 col2 INTEGER)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME ‘SS!easy!easy.c’;
CREATE TABLE t1 (col1 int);

CONSTRAINT (con)

CREATE TABLE t2 (col1 int CONSTRAINT con2  CHECK (i=1));
CREATE TABLE t3 (col3 int CONSTRAINT con3  REFERENCES t1(c1));
ALTER TABLE t1 ADD CONSTRAINT con1 UNIQUE (c1);

CONSTRAINT (con)

(row level security constraint)

CREATE CONSTRAINT conA data_type,[NULL|NOT NULL],
VALUES value_name:integer_code ...
[, value_name:integer_code],
Insert SYSLIB.insert_udf_name ,
Update SYSLIB.update_udf_name ,
Delete SYSLIB.delete_udf_name ,
Select SYSLIB.select_udf_name ;

DATABASE (db)

CREATE DATABASE db1 as perm=10e6;

FUNCTION (fn)

CREATE FUNCTION fn2 (integer, float)
RETURNS FLOAT
LANGUAGE C
NO SQL
EXTERNAL NAME   ‘sp:/Teradata/tdbs_udf/usr/second1.so’;
RENAME FUNCTION fn1 AS fn2;
ALTER SPECIFIC FUNCTION fn1 EXECUTE PROTECTED;
CREATE CAST (udt1 as udt2) WITH FUNCTION sysudtlib.fn1(udt1);
CREATE ORDERING FOR udt1 
ORDER FULL BY MAP WITH FUNCTION SYSUDTLIB.fn3;
CREATE TRANSFORM FOR abov_strInt
abov_strInt_IO (TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.fn4);

GLOP Set (glop)

CREATE GLOP SET db.glop1;

INDEX (idx)

CREATE INDEX idx2 (c1) on t1;
COLLECT STAT USING SAMPLE ON db1.idx1 
COLUMN col1 FROM db2.t1;
CREATE TABLE t1 (col1 int, col2 int) 
INDEX idx1 (c2);
COLLECT STAT INDEX idx1 ON tab1;

MACRO

CREATE MACRO m1 AS (SELECT ‘abc’; );
RENAME MACRO m1 to m2;

METHOD (mth)

CREATE CONSTRUCTOR METHOD mth1
(P1 INTEGER)
FOR abov_strInt
EXTERNAL NAME 'SS!udt_strintcons!udt_strintcons.c!F!strintcons';
CREATE TYPE udt1 AS (P1 INTEGER)
NOT FINAL CONSTRUCTOR METHOD mth2 (P1 INTEGER)
RETURNS udt1
SELF AS RESULT
LANGUAGE C
DETERMINISTIC
NO SQL;
ALTER SPECIFIC METHOD mth2 FOR UDT_name EXECUTE NOT PROTECTED;

PARAMETER (pm)

CREATE MACRO m1 (pm1 int) AS (SEL :pm1; );
CREATE FUNCTION Find_Text (pm1 VARCHAR (500), pm2  VARCHAR (500))
RETURNS CHAR
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME
'SS:pattern2:/home/i18n/ca3/v2r5/UDF/tests/SrcNI/pattern2/pattern2.c:SI:pattern2:/home/i18n/ca3/v2r5/UDF/tests/SrcNI/pattern2/pattern2.h:SL:curses';
CREATE PROCEDURE xsp_cr003(IN pm1 varchar(20), OUT pm2 VARCHAR(20))
LANGUAGE C
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!xsp_cr003!xsp_cr003.c';
CREATE TYPE abov_strlnt AS (pm2 INTEGER)
NOT FINAL CONSTRUCTOR METHOD abov_strlnt (P1 INTEGER)
RETURNS abov_strlnt 
SELF AS RESULT
LANGUAGE C
DETERMINISTIC
NO SQL;
REPLACE PROCEDURE sp3(out pm1 integer)
BEGIN
  DECLARE var1 INTEGER DEFAULT 10;
  SET p1 = var1;
END;

PASSWORD (pass)

CREATE USER u1 as perm=10e6, 
password = pass1 , account=’acc1’;
MODIFY USER u1 AS PASSWORD=pass1;

PLAN_DIRECTIVE (pd)

INSERT PLAN_DIRECTIVE pd1 IN pd1cat 
(sel   'pd_dip002_TestID1_1_pd',
      t100k_b.i1, pd_db1.t100k_b.i2, pd_db1.t100k_b.i5
from  t100k_b, pd_db1.t200_a
where
      t100k_b.i2 = pd_db1.t200_a.i2
;
)
WITH '1: NESTED JOIN(DUPED( SCAN( PD_DB1.T100K_B)),'
'INDEXED WITH NO ROWIDLIST( INDEX(I2) PD_DB1.T200_A))'
COMMENT 'PD for pd_dip002_TestID1_1_pd'

PROCEDURE (sp)

CREATE PROCEDURE sp2 ( a varchar(20), OUT result1 VARCHAR(20))
LANGUAGE C
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!xsp_cr003!xsp_cr003.c';
RENAME PROCEDURE sp2 TO sp3;
ALTER PROCEDURE sp3 LANGUAGE C COMPILE ONLY;

PROFILE (prof)

CREATE PROFILE prof5 AS 
   PERM=10e5 
   PASSWORD=pass5 
   ACCOUNT= ‘acc5’;

PROXYUSER (pxyuser)

PROXYROLE (pxyrole)

SET QUERY_BAND='PROXYUSER=pxyuser1;
PROXYROLE=pxyrole1;' FOR SESSION;
BEGIN TRANSACTION;
SET QUERY_BAND='PROXYROLE=pxyrole2;' FOR TRANSACTION;
SELECT *
FROM table1;

QUERY (qry)

WITH qry1(a) AS (SELECT col1 FROM tab1) 
SELECT a FROM qry1;

ROLE (role)

CREATE ROLE role1;

EXTERNAL ROLE (extrole)

CREATE EXTERNAL ROLE extrole2;

TABLE (t)

CREATE TABLE t1 (col1 int);
CREATE ERROR TABLE t2 FOR t1;
RENAME TABLE t1 AS t3;
MODIFY USER u1 AS DEFAULT JOURNAL TABLE=t1;
CREATE TRIGGER trig5 AFTER UPDATE ON t3
REFERENCING OLD_TABLE AS t1 NEW_TABLE AS t2FOR EACH Statement
  When (35 <= (sel X.price from t2 X INNER JOIN t1 Y ON
                         X.pubyear= Y.pubyear))
(
  INSERT t4 SELECT t2.titles, t2.price FROM t2;
);
BEGIN LOADING db1.t3
ERRORFILES db1.t1, db1.t2
INSERT INTO db1.t3.*;
BEGIN DELETE MLOAD TABLES db1.t1
WITH db1.t2
ERRORTABLES db1.lt3;
BEGIN IMPORT MLOAD TABLES db1.t1 
WORKTABLES db1.t2
ERRORTABLES db1.t3 db1.t4 ;
CREATE USER u1 as perm=10e6, 
password = pass1, default journal table=t1;

TRANSFORM_GROUP (tg)

CREATE TRANSFORM FOR abov_strInt 
tg1
(TO SQL WITH SPECIFIC FUNCTION
SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION 
SYSUDTLIB.abov_StrIntFromSQL);
DROP TRANSFORM tg2 FOR arsv_point_t ;

TRIGGER (trig)

CREATE TRIGGER trig1 AFTER  UPDATE OF (i) 
ON t1 REFERENCING OLD AS OLDROW 
NEW AS NEWROW FOR EACH ROW
WHEN (i > 10)
(INSERT INTO t1log VALUES (OLDROW.i, NEWROW.i); );
RENAME TRIGGER trig1 TO trig2 ;

UDT (udt)

CREATE TYPE udt15 AS VARCHAR(15) FINAL;
CREATE CAST (udt1 as udt2) WITH FUNCTION 
sysudtlib.abov_DistFloattoDistInt(udt1)
CREATE ORDERING FOR udt3 
ORDER FULL BY MAP WITH FUNCTION 
SYSUDTLIB.abov_StrIntOrdering;
CREATE TRANSFORM FOR udt3
abov_strInt_IO (TO SQL WITH SPECIFIC 
FUNCTION SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION 
SYSUDTLIB.abov_StrIntFromSQL);
DROP TRANSFORM transform_io FOR udt4 ;

USER_NAME (u)

CREATE USER u1 as perm=10e6, 
password = pass1, account=’u1account’ ;

USING VARIABLE NAME (usingvar)

.import data file = c:\temp\fil1.data
using (usingvar1 int, usingvar2 char(10)) 
insert db1.tab1(:usingvar1, :usingvar2);

VIEW (v)

CREATE VIEW v1 AS SELECT ‘abc’ col1;
REPLACE VIEW v1 AS SELECT ‘abc’ col1;
RENAME VIEW v1 AS v2;