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; The reserved query band names PROXYUSER and PROXYROLE are not permitted in the profile query band.
|
|
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 t2 FOR 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 ; |