http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/orderSummary.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/orderSummary.java b/docs/spj_guide/src/resources/source/orderSummary.java index d10e98a..8b8e5bb 100644 --- a/docs/spj_guide/src/resources/source/orderSummary.java +++ b/docs/spj_guide/src/resources/source/orderSummary.java @@ -25,7 +25,7 @@ public static void orderSummary( java.lang.String onOrAfter DriverManager.getConnection( "jdbc:default:connection" ) ; // Get the number of orders on or after this date - s = "SELECT COUNT(ordernum) FROM demo.sales.orders " + s = "SELECT COUNT(ordernum) FROM trafodion.sales.orders " + "WHERE order_date >= CAST(? AS DATE) " ; @@ -44,13 +44,13 @@ public static void orderSummary( java.lang.String onOrAfter + " o.ordernum " + " , COUNT(d.partnum) AS num_parts " + " , SUM(d.unit_price * d.qty_ordered) AS amount " - + " FROM demo.sales.orders o, demo.sales.odetail d " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail d " + " WHERE o.ordernum = d.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + " GROUP BY o.ordernum " + " ) amounts " - + " , demo.sales.orders orders " - + " , demo.persnl.employee emps " + + " , trafodion.sales.orders orders " + + " , trafodion.persnl.employee emps " + "WHERE amounts.ordernum = orders.ordernum " + " AND orders.salesrep = emps.empnum " + "ORDER BY orders.ordernum " @@ -62,7 +62,7 @@ public static void orderSummary( java.lang.String onOrAfter // Open a result set for order detail rows s = "SELECT d.*, p.partdesc " - + "FROM demo.sales.odetail d, demo.sales.parts p, demo.sales.orders O " + + "FROM trafodion.sales.odetail d, trafodion.sales.parts p, trafodion.sales.orders O " + "WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + "ORDER BY d.ordernum "
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/partData.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/partData.java b/docs/spj_guide/src/resources/source/partData.java index d85f980..f8bf8e1 100644 --- a/docs/spj_guide/src/resources/source/partData.java +++ b/docs/spj_guide/src/resources/source/partData.java @@ -27,7 +27,7 @@ public static void partData( int partNum // Retrieve detail about this part into the output parameters PreparedStatement getPartInfo = conn.prepareStatement( "SELECT P.partdesc, P.price, P.qty_available " - + "FROM demo.sales.parts P " + + "FROM trafodion.sales.parts P " + "WHERE partnum = ? " ) ; @@ -47,9 +47,9 @@ public static void partData( int partNum // quantity of this part that was ordered. PreparedStatement getOrders = conn.prepareStatement( "SELECT O.*, QTY.QTY_ORDERED " - + "FROM demo.sales.orders O " + + "FROM trafodion.sales.orders O " + " , ( select ordernum, sum(qty_ordered) as QTY_ORDERED " - + " from demo.sales.odetail " + + " from trafodion.sales.odetail " + " where partnum = ? " + " group by ordernum ) QTY " + "WHERE O.ordernum = QTY.ordernum " @@ -65,7 +65,7 @@ public static void partData( int partNum // have on hand. PreparedStatement getLocations = conn.prepareStatement( "SELECT * " - + "FROM demo.invent.partloc " + + "FROM trafodion.invent.partloc " + " WHERE partnum = ? " ) ; @@ -76,7 +76,7 @@ public static void partData( int partNum // suppliers who supply this part. PreparedStatement getSuppliers = conn.prepareStatement( "SELECT * " - + "FROM demo.invent.partsupp " + + "FROM trafodion.invent.partsupp " + "WHERE partnum = ? " ) ; @@ -87,10 +87,10 @@ public static void partData( int partNum // sales reps that have sold this part. PreparedStatement getReps = conn.prepareStatement( "SELECT * " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum in ( SELECT O.salesrep " - + " FROM demo.sales.orders O, " - + " demo.sales.odetail D " + + " FROM trafodion.sales.orders O, " + + " trafodion.sales.odetail D " + " D.partnum = ? " + " O.ordernum = D.ordernum ) " + "ORDER BY empnum " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/partlocations.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/partlocations.java b/docs/spj_guide/src/resources/source/partlocations.java index 2a4df24..88cdaae 100644 --- a/docs/spj_guide/src/resources/source/partlocations.java +++ b/docs/spj_guide/src/resources/source/partlocations.java @@ -16,7 +16,7 @@ public static void partLocations( int partNum PreparedStatement getLocationsExact = conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " - + "FROM demo.invent.partloc L " + + "FROM trafodion.invent.partloc L " + "WHERE L.partnum = ? " + " AND L.qty_on_hand = ? " + " ORDER BY L.partnum " @@ -27,7 +27,7 @@ public static void partLocations( int partNum PreparedStatement getLocationsMoreThan = conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " - + "FROM demo.invent.partloc L " + + "FROM trafodion.invent.partloc L " + "WHERE L.partnum = ? " + " AND L.qty_on_hand > ? " + "ORDER BY L.partnum " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/persnl_dept_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/persnl_dept_table.sql b/docs/spj_guide/src/resources/source/persnl_dept_table.sql index 85b4d19..91b35f3 100644 --- a/docs/spj_guide/src/resources/source/persnl_dept_table.sql +++ b/docs/spj_guide/src/resources/source/persnl_dept_table.sql @@ -1,10 +1,10 @@ -CREATE TABLE demo.persnl.dept +CREATE TABLE trafodion.persnl.dept ( deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , deptname CHARACTER (12) NO DEFAULT NOT NULL , manager NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , rptdept NUMERIC (4) UNSIGNED DEFAULT 0 NOT NULL , location VARCHAR (18) DEFAULT ' ' NOT NULL -, PRIMARY KEY (deptnum) +, PRIMARY KEY ( deptnum ) ) ; CREATE INDEX xdeptmgr ON dept @@ -15,12 +15,12 @@ CREATE INDEX xdeptrpt ON dept ( rptdept ) ; -ALTER TABLE demo.persnl.dept +ALTER TABLE trafodion.persnl.dept ADD CONSTRAINT mgrnum_constrnt CHECK (manager BETWEEN 0000 AND 9999) ; -ALTER TABLE demo.persnl.dept +ALTER TABLE trafodion.persnl.dept ADD CONSTRAINT deptnum_constrnt CHECK ( deptnum IN ( 1000 @@ -39,7 +39,7 @@ ALTER TABLE demo.persnl.dept ) ; 3 -CREATE VIEW demo.persnl.mgrlist +CREATE VIEW trafodion.persnl.mgrlist ( first_name , last_name , department @@ -52,7 +52,7 @@ FROM dept, employee WHERE dept.manager = employee.empnum ; -INSERT INTO demo.persnl.dept VALUES +INSERT INTO trafodion.persnl.dept VALUES ( 1000, 'FINANCE', 23, 9000, 'CHICAGO' ) , ( 1500, 'PERSONNEL', 213, 1000, 'CHICAGO' ) , ( 2000, 'INVENTORY', 32, 9000, 'LOS ANGELES' ) @@ -67,4 +67,4 @@ INSERT INTO demo.persnl.dept VALUES , ( 9000, 'xxCORPORATE', 1, 9000, 'CHICAGO' ) ; -UPDATE STATISTICS FOR TABLE demo.persnl.dept ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.persnl.dept ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/persnl_employee_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/persnl_employee_table.sql b/docs/spj_guide/src/resources/source/persnl_employee_table.sql index 1af4323..58bac1f 100644 --- a/docs/spj_guide/src/resources/source/persnl_employee_table.sql +++ b/docs/spj_guide/src/resources/source/persnl_employee_table.sql @@ -1,15 +1,15 @@ -CREATE TABLE demo.persnl.employee +CREATE TABLE trafodion.persnl.employee ( empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , first_name CHARACTER (15) DEFAULT ' ' NOT NULL , last_name CHARACTER (20) DEFAULT ' ' NOT NULL , deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , jobcode NUMERIC (4) UNSIGNED DEFAULT NULL , salary NUMERIC (8, 2) UNSIGNED DEFAULT NULL -, PRIMARY KEY (empnum) +, PRIMARY KEY ( empnum ) ) ; -ALTER TABLE demo.persnl.employee - ADD CONSTRAINT empnum_constrnt CHECK (empnum BETWEEN 0001 AND 9999) +ALTER TABLE trafodion.persnl.employee + ADD CONSTRAINT empnum_constrnt CHECK ( empnum BETWEEN 0001 AND 9999 ) ; CREATE INDEX xempname ON employee @@ -21,7 +21,7 @@ CREATE INDEX xempdept ON employee ( deptnum ) ; -CREATE VIEW demo.persnl.emplist AS +CREATE VIEW trafodion.persnl.emplist AS SELECT empnum , first_name @@ -31,69 +31,69 @@ SELECT FROM employee ; -INSERT INTO demo.persnl.employee VALUES - ( 1, 'ROGER', 'GREEN', 9000, 100, 175500.00 ) -, ( 23, 'JERRY', 'HOWARD', 1000, 100, 137000.10 ) -, ( 29, 'JANE', 'RAYMOND', 3000, 100, 136000.00 ) -, ( 32, 'THOMAS', 'RUDLOFF', 2000, 100, 138000.40 ) -, ( 39, 'KLAUS ', 'SAFFERT', 3200, 100, 75000.00 ) -, ( 43, 'PAUL', 'WINTER', 3100, 100, 90000.00 ) -, ( 65, 'RACHEL', 'MCKAY', 4000, 100, 118000.00 ) -, ( 72, 'GLENN', 'THOMAS', 3300, 100, 80000.00 ) -, ( 75, 'TIM', 'WALKER', 3000, 300, 32000.00 ) -, ( 87, 'ERIC', 'BROWN', 4000, 400, 89000.00 ) -, ( 89, 'PETER', 'SMITH', 3300, 300, 37000.40 ) -, ( 93, 'DONALD', 'TAYLOR', 3100, 300, 33000.00 ) -, ( 104, 'DAVID', 'STRAND', 4000, 400, 69000.00 ) -, ( 109, 'STEVE', 'COOK', 4000, 400, 68000.00 ) -, ( 111, 'SHERRIE', 'WONG', 3500, 100, 70000.00 ) -, ( 178, 'JOHN', 'CHOU', 3500, 900, 28000.00 ) -, ( 180, 'MANFRED', 'CONRAD', 4000, 450, 32000.00 ) -, ( 201, 'JIM', 'HERMAN', 3000, 300, 19000.00 ) -, ( 202, 'LARRY', 'CLARK', 1000, 500, 25000.75 ) -, ( 203, 'KATHRYN', 'HALL', 4000, 400, 96000.00 ) -, ( 205, 'GINNY', 'FOSTER', 3300, 900, 30000.00 ) -, ( 206, 'DAVE', 'FISHER', 3200, 900, 25000.00 ) -, ( 207, 'MARK', 'FOLEY', 4000, 420, 33000.00 ) -, ( 208, 'SUE', 'CRAMER', 1000, 900, 19000.00 ) -, ( 209, 'SUSAN', 'CHAPMAN', 1500, 900, 17000.00 ) -, ( 210, 'RICHARD', 'BARTON', 1000, 500, 29000.00 ) -, ( 211, 'JIMMY', 'SCHNEIDER', 1500, 600, 26000.00 ) -, ( 212, 'JONATHAN', 'MITCHELL', 1500, 600, 32000.00 ) -, ( 213, 'ROBERT', 'WHITE', 1500, 100, 90000.00 ) -, ( 214, 'JULIA', 'KELLY', 1000, 500, 50000.00 ) -, ( 215, 'WALTER', 'LANCASTER', 4000, 450, 33000.50 ) -, ( 216, 'JOHN', 'JONES', 4000, 450, 40000.00 ) -, ( 217, 'MARLENE', 'BONNY', 4000, 900, 24000.90 ) -, ( 218, 'GEORGE', 'FRENCHMAN', 4000, 420, 36000.00 ) -, ( 219, 'DAVID', 'TERRY', 2000, 250, 27000.12 ) -, ( 220, 'JOHN', 'HUGHES', 3200, 300, 33000.10 ) -, ( 221, 'OTTO', 'SCHNABL', 3200, 300, 33000.00 ) -, ( 222, 'MARTIN', 'SCHAEFFER', 3200, 300, 31000.00 ) -, ( 223, 'HERBERT', 'KARAJAN', 3200, 300, 29000.00 ) -, ( 224, 'MARIA', 'JOSEF', 4000, 420, 18000.10 ) -, ( 225, 'KARL', 'HELMSTED', 4000, 450, 32000.00 ) -, ( 226, 'HEIDI', 'WEIGL', 3200, 300, 22000.00 ) -, ( 227, 'XAVIER', 'SEDLEMEYER', 3300, 300, 30000.00 ) -, ( 228, 'PETE', 'WELLINGTON', 3100, 300, 32000.20 ) -, ( 229, 'GEORGE', 'STRICKER', 3100, 300, 32222.00 ) -, ( 230, 'ROCKY', 'LEWIS', 2000, 200, 24000.00 ) -, ( 231, 'HERB', 'ALBERT', 3300, 300, 33000.00 ) -, ( 232, 'THOMAS', 'SPINNER', 4000, 450, 45000.00 ) -, ( 233, 'TED', 'MCDONALD', 2000, 250, 29000.00 ) -, ( 234, 'MARY', 'MILLER', 2500, 100, 56000.00 ) -, ( 235, 'MIRIAM', 'KING', 2500, 900, 18000.00 ) -, ( 321, 'BILL', 'WINN', 2000, 900, 32000.00 ) -, ( 337, 'DINAH', 'CLARK', 9000, 900, 37000.00 ) -, ( 343, 'ALAN', 'TERRY', 3000, 900, 39500.00 ) -, ( 557, 'BEN', 'HENDERSON', 4000, 400, 65000.00 ) -, ( 568, 'JESSICA', 'CRINER', 3500, 300, 39500.00 ) -, ( 990, 'THOMAS', 'STIBBS', 3500, NULL, NULL ) -, ( 991, 'WAYNE', 'O''NEIL', 3500, NULL, NULL ) -, ( 992, 'BARRY', 'KINNEY', 3500, NULL, NULL ) -, ( 993, 'PAUL', 'BUSKETT', 3100, NULL, NULL ) -, ( 994, 'EMMY', 'BUSKETT', 3100, NULL, NULL ) -, ( 995, 'WALT', 'FARLEY', 3100, NULL, NULL ) +INSERT INTO trafodion.persnl.employee VALUES + ( 1, 'ROGER', 'GREEN', 9000, 100, 175500.00 ) +, ( 23, 'JERRY', 'HOWARD', 1000, 100, 137000.10 ) +, ( 29, 'JANE', 'RAYMOND', 3000, 100, 136000.00 ) +, ( 32, 'THOMAS', 'RUDLOFF', 2000, 100, 138000.40 ) +, ( 39, 'KLAUS ', 'SAFFERT', 3200, 100, 75000.00 ) +, ( 43, 'PAUL', 'WINTER', 3100, 100, 90000.00 ) +, ( 65, 'RACHEL', 'MCKAY', 4000, 100, 118000.00 ) +, ( 72, 'GLENN', 'THOMAS', 3300, 100, 80000.00 ) +, ( 75, 'TIM', 'WALKER', 3000, 300, 32000.00 ) +, ( 87, 'ERIC', 'BROWN', 4000, 400, 89000.00 ) +, ( 89, 'PETER', 'SMITH', 3300, 300, 37000.40 ) +, ( 93, 'DONALD', 'TAYLOR', 3100, 300, 33000.00 ) +, ( 104, 'DAVID', 'STRAND', 4000, 400, 69000.00 ) +, ( 109, 'STEVE', 'COOK', 4000, 400, 68000.00 ) +, ( 111, 'SHERRIE', 'WONG', 3500, 100, 70000.00 ) +, ( 178, 'JOHN', 'CHOU', 3500, 900, 28000.00 ) +, ( 180, 'MANFRED', 'CONRAD', 4000, 450, 32000.00 ) +, ( 201, 'JIM', 'HERMAN', 3000, 300, 19000.00 ) +, ( 202, 'LARRY', 'CLARK', 1000, 500, 25000.75 ) +, ( 203, 'KATHRYN', 'HALL', 4000, 400, 96000.00 ) +, ( 205, 'GINNY', 'FOSTER', 3300, 900, 30000.00 ) +, ( 206, 'DAVE', 'FISHER', 3200, 900, 25000.00 ) +, ( 207, 'MARK', 'FOLEY', 4000, 420, 33000.00 ) +, ( 208, 'SUE', 'CRAMER', 1000, 900, 19000.00 ) +, ( 209, 'SUSAN', 'CHAPMAN', 1500, 900, 17000.00 ) +, ( 210, 'RICHARD', 'BARTON', 1000, 500, 29000.00 ) +, ( 211, 'JIMMY', 'SCHNEIDER', 1500, 600, 26000.00 ) +, ( 212, 'JONATHAN', 'MITCHELL', 1500, 600, 32000.00 ) +, ( 213, 'ROBERT', 'WHITE', 1500, 100, 90000.00 ) +, ( 214, 'JULIA', 'KELLY', 1000, 500, 50000.00 ) +, ( 215, 'WALTER', 'LANCASTER', 4000, 450, 33000.50 ) +, ( 216, 'JOHN', 'JONES', 4000, 450, 40000.00 ) +, ( 217, 'MARLENE', 'BONNY', 4000, 900, 24000.90 ) +, ( 218, 'GEORGE', 'FRENCHMAN', 4000, 420, 36000.00 ) +, ( 219, 'DAVID', 'TERRY', 2000, 250, 27000.12 ) +, ( 220, 'JOHN', 'HUGHES', 3200, 300, 33000.10 ) +, ( 221, 'OTTO', 'SCHNABL', 3200, 300, 33000.00 ) +, ( 222, 'MARTIN', 'SCHAEFFER', 3200, 300, 31000.00 ) +, ( 223, 'HERBERT', 'KARAJAN', 3200, 300, 29000.00 ) +, ( 224, 'MARIA', 'JOSEF', 4000, 420, 18000.10 ) +, ( 225, 'KARL', 'HELMSTED', 4000, 450, 32000.00 ) +, ( 226, 'HEIDI', 'WEIGL', 3200, 300, 22000.00 ) +, ( 227, 'XAVIER', 'SEDLEMEYER', 3300, 300, 30000.00 ) +, ( 228, 'PETE', 'WELLINGTON', 3100, 300, 32000.20 ) +, ( 229, 'GEORGE', 'STRICKER', 3100, 300, 32222.00 ) +, ( 230, 'ROCKY', 'LEWIS', 2000, 200, 24000.00 ) +, ( 231, 'HERB', 'ALBERT', 3300, 300, 33000.00 ) +, ( 232, 'THOMAS', 'SPINNER', 4000, 450, 45000.00 ) +, ( 233, 'TED', 'MCDONALD', 2000, 250, 29000.00 ) +, ( 234, 'MARY', 'MILLER', 2500, 100, 56000.00 ) +, ( 235, 'MIRIAM', 'KING', 2500, 900, 18000.00 ) +, ( 321, 'BILL', 'WINN', 2000, 900, 32000.00 ) +, ( 337, 'DINAH', 'CLARK', 9000, 900, 37000.00 ) +, ( 343, 'ALAN', 'TERRY', 3000, 900, 39500.00 ) +, ( 557, 'BEN', 'HENDERSON', 4000, 400, 65000.00 ) +, ( 568, 'JESSICA', 'CRINER', 3500, 300, 39500.00 ) +, ( 990, 'THOMAS', 'STIBBS', 3500, NULL, NULL ) +, ( 991, 'WAYNE', 'O''NEIL', 3500, NULL, NULL ) +, ( 992, 'BARRY', 'KINNEY', 3500, NULL, NULL ) +, ( 993, 'PAUL', 'BUSKETT', 3100, NULL, NULL ) +, ( 994, 'EMMY', 'BUSKETT', 3100, NULL, NULL ) +, ( 995, 'WALT', 'FARLEY', 3100, NULL, NULL ) ; -UPDATE STATISTICS FOR TABLE demo.persnl.employee ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.persnl.employee ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/persnl_job_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/persnl_job_table.sql b/docs/spj_guide/src/resources/source/persnl_job_table.sql index 0692af0..29d59f1 100644 --- a/docs/spj_guide/src/resources/source/persnl_job_table.sql +++ b/docs/spj_guide/src/resources/source/persnl_job_table.sql @@ -1,10 +1,10 @@ -CREATE TABLE demo.persnl.job +CREATE TABLE trafodion.persnl.job ( jobcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , jobdesc VARCHAR (18) DEFAULT ' ' NOT NULL -, PRIMARY KEY (jobcode) +, PRIMARY KEY ( jobcode ) ) ; -INSERT INTO demo.persnl.job VALUES +INSERT INTO trafodion.persnl.job VALUES ( 100, 'MANAGER' ) , ( 200, 'PRODUCTION SUPV' ) , ( 250, 'ASSEMBLER' ) @@ -17,4 +17,4 @@ INSERT INTO demo.persnl.job VALUES , ( 900, 'SECRETARY' ) ; -UPDATE STATISTICS FOR TABLE demo.persnl.job ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.persnl.job ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/persnl_project_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/persnl_project_table.sql b/docs/spj_guide/src/resources/source/persnl_project_table.sql index 35c2999..21526ec 100644 --- a/docs/spj_guide/src/resources/source/persnl_project_table.sql +++ b/docs/spj_guide/src/resources/source/persnl_project_table.sql @@ -1,14 +1,14 @@ -CREATE TABLE demo.persnl.project +CREATE TABLE trafodion.persnl.project ( projcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , projdesc VARCHAR (18) DEFAULT ' ' NOT NULL , start_date DATE DEFAULT DATE '2011-07-01' NOT NULL , ship_timestamp TIMESTAMP DEFAULT TIMESTAMP '2011-08-01:12:00:00.000000' NOT NULL , est_complete INTERVAL DAY DEFAULT INTERVAL '30' DAY NOT NULL -, PRIMARY KEY (projcode, empnum) +, PRIMARY KEY ( projcode, empnum ) ) ; -INSERT INTO demo.persnl.project +INSERT INTO trafodion.persnl.project VALUES ( 1000, 213, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) , ( 1000, 211, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) @@ -42,4 +42,4 @@ VALUES , ( 5000, 218, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) ; -UPDATE STATISTICS FOR TABLE demo.persnl.project ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.persnl.project ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/persnl_schema.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/persnl_schema.sql b/docs/spj_guide/src/resources/source/persnl_schema.sql index bad0734..140ab97 100644 --- a/docs/spj_guide/src/resources/source/persnl_schema.sql +++ b/docs/spj_guide/src/resources/source/persnl_schema.sql @@ -1,2 +1,2 @@ -CREATE SCHEMA DEMO.PERSNL ; -SET SCHEMA DEMO.PERSNL ; +CREATE SCHEMA trafodion.persnl ; +SET SCHEMA trafodion.persnl ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/projectTeam.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/projectTeam.java b/docs/spj_guide/src/resources/source/projectTeam.java index 039b534..0d150c4 100644 --- a/docs/spj_guide/src/resources/source/projectTeam.java +++ b/docs/spj_guide/src/resources/source/projectTeam.java @@ -13,7 +13,7 @@ public static void projectTeam( int projectCode PreparedStatement getMembers = conn.prepareStatement( "SELECT E.empnum, E.first_name, E.last_name, D.location " - + "FROM demo.persnl.employee E, demo.persnl.dept D, demo.persnl.project P " + + "FROM trafodion.persnl.employee E, trafodion.persnl.dept D, trafodion.persnl.project P " + "WHERE P.projcode = ? " + " AND P.empnum = E.empnum " + " AND E.deptnum = D.deptnum " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/sales_customer_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/sales_customer_table.sql b/docs/spj_guide/src/resources/source/sales_customer_table.sql index 96a1c7c..31efef0 100644 --- a/docs/spj_guide/src/resources/source/sales_customer_table.sql +++ b/docs/spj_guide/src/resources/source/sales_customer_table.sql @@ -1,4 +1,4 @@ -CREATE TABLE demo.sales.customer +CREATE TABLE trafodion.sales.customer ( custnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , custname CHARACTER (18) NO DEFAULT NOT NULL , street CHARACTER (22) NO DEFAULT NOT NULL @@ -6,10 +6,10 @@ CREATE TABLE demo.sales.customer , state CHARACTER (12) DEFAULT ' ' NOT NULL , postcode CHARACTER (10) NO DEFAULT NOT NULL , credit CHARACTER (2) DEFAULT 'C1' NOT NULL -, PRIMARY KEY (custnum) +, PRIMARY KEY ( custnum ) ) ; -INSERT INTO demo.sales.customer VALUES +INSERT INTO trafodion.sales.customer VALUES ( 21, 'CENTRAL UNIVERSITY', 'UNIVERSITY WAY', 'PHILADELPHIA', 'PENNSYLVANIA', '19104', 'A1' ) , ( 123, 'BROWN MEDICAL CO', '100 CALIFORNIA STREET', 'SAN FRANCISCO', 'CALIFORNIA', '94944', 'C2' ) , ( 143, 'STEVENS SUPPLY', '2020 HARRIS STREET', 'DENVER', 'COLORADO', '80734', 'A2' ) @@ -27,4 +27,4 @@ INSERT INTO demo.sales.customer VALUES , ( 9033, 'ART SUPPLIES, INC.', '22 SWEET ST.', 'PITTSBURGH', 'PENNA.', '08333', 'C3' ) ; -UPDATE STATISTICS FOR TABLE demo.sales.customer ON EVERY COLUMN; +UPDATE STATISTICS FOR TABLE trafodion.sales.customer ON EVERY COLUMN; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/sales_odetail_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/sales_odetail_table.sql b/docs/spj_guide/src/resources/source/sales_odetail_table.sql index 13cfb61..da6fa37 100644 --- a/docs/spj_guide/src/resources/source/sales_odetail_table.sql +++ b/docs/spj_guide/src/resources/source/sales_odetail_table.sql @@ -1,84 +1,84 @@ -CREATE TABLE demo.sales.odetail +CREATE TABLE trafodion.sales.odetail ( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL , partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , unit_price NUMERIC (8, 2) NO DEFAULT NOT NULL , qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL -, PRIMARY KEY (ordernum,partnum) +, PRIMARY KEY ( ordernum, partnum ) ) ; -INSERT INTO demo.sales.odetail VALUES - ( 100210, 244, 3500.00, 3 ) -, ( 100210,2001, 1100.00, 3 ) -, ( 100210,2403, 620.00, 6 ) -, ( 100210,5100, 150.00, 10 ) -, ( 100250, 244, 3500.00, 4 ) -, ( 100250,5103, 400.00, 10 ) -, ( 100250,6301, 245.00, 15 ) -, ( 100250,6500, 95.00, 10 ) -, ( 101220, 255, 3900.00, 10 ) -, ( 101220,5103, 400.00, 3 ) -, ( 101220,7102, 275.00, 7 ) -, ( 101220,7301, 425.00, 8 ) -, ( 200300, 244, 3500.00, 8 ) -, ( 200300,2001, 1000.00, 10 ) -, ( 200300,2002, 1400.00, 10 ) -, ( 200320,5504, 165.00, 5 ) -, ( 200320,6201, 195.00, 16 ) -, ( 200320,6301, 245.00, 6 ) -, ( 200320,6400, 540.00, 7 ) -, ( 200490,3210, 715.00, 1 ) -, ( 200490,5505, 350.00, 1 ) -, ( 300350, 244, 2800.00, 20 ) -, ( 300350,5100, 150.00, 5 ) -, ( 300350,5110, 525.00, 12 ) -, ( 300350,6301, 245.00, 5 ) -, ( 300350,6400, 550.00, 5 ) -, ( 300380, 244, 3000.00, 6 ) -, ( 300380,2402, 320.00, 12 ) -, ( 300380,2405, 760.00, 8 ) -, ( 400410, 212, 2450.00, 12 ) -, ( 400410, 255, 3800.00, 12 ) -, ( 400410,2001, 1000.00, 36 ) -, ( 400410,6301, 240.00, 48 ) -, ( 400410,6400, 500.00, 70 ) -, ( 400410,7301, 415.00, 36 ) -, ( 500450, 212, 2500.00, 8 ) -, ( 500450, 255, 3900.00, 12 ) -, ( 500450,2001, 1100.00, 16 ) -, ( 500450,2002, 1500.00, 16 ) -, ( 500450,2402, 330.00, 48 ) -, ( 600480,2001, 1000.00, 60 ) -, ( 600480,2002, 1450.00, 20 ) -, ( 600480,2003, 1900.00, 40 ) -, ( 600480,3103, 4000.00, 40 ) -, ( 600480,3205, 625.00, 20 ) -, ( 600480,5100, 135.00, 60 ) -, ( 600480,5103, 390.00, 20 ) -, ( 600480,7301, 425.00, 40 ) -, ( 700410,2003, 1900.00, 65 ) -, ( 700410,2403, 650.00, 10 ) -, ( 700510, 255, 4000.00, 4 ) -, ( 700510,6500, 95.00, 8 ) -, ( 700510,7102, 275.00, 5 ) -, ( 800660, 244, 3000.00, 6 ) -, ( 800660,2001, 1000.00, 30 ) -, ( 800660,2403, 600.00, 48 ) -, ( 800660,2405, 795.00, 10 ) -, ( 800660,3201, 525.00, 6 ) -, ( 800660,3205, 600.00, 18 ) -, ( 800660,3210, 715.00, 6 ) -, ( 800660,4102, 26.00, 130 ) -, ( 800660,5100, 150.00, 12 ) -, ( 800660,5101, 200.00, 6 ) -, ( 800660,5110, 490.00, 48 ) -, ( 800660,5504, 165.00, 18 ) -, ( 800660,6201, 195.00, 6 ) -, ( 800660,6301, 235.00, 24 ) -, ( 800660,6400, 525.00, 30 ) -, ( 800660,6401, 700.00, 36 ) -, ( 800660,6500, 95.00, 22 ) -, ( 800660,7102, 275.00, 6 ) -, ( 800660,7301, 425.00, 12 ) +INSERT INTO trafodion.sales.odetail VALUES + ( 100210, 244, 3500.00, 3 ) +, ( 100210, 2001, 1100.00, 3 ) +, ( 100210, 2403, 620.00, 6 ) +, ( 100210, 5100, 150.00, 10 ) +, ( 100250, 244, 3500.00, 4 ) +, ( 100250, 5103, 400.00, 10 ) +, ( 100250, 6301, 245.00, 15 ) +, ( 100250, 6500, 95.00, 10 ) +, ( 101220, 255, 3900.00, 10 ) +, ( 101220, 5103, 400.00, 3 ) +, ( 101220, 7102, 275.00, 7 ) +, ( 101220, 7301, 425.00, 8 ) +, ( 200300, 244, 3500.00, 8 ) +, ( 200300, 2001, 1000.00, 10 ) +, ( 200300, 2002, 1400.00, 10 ) +, ( 200320, 5504, 165.00, 5 ) +, ( 200320, 6201, 195.00, 16 ) +, ( 200320, 6301, 245.00, 6 ) +, ( 200320, 6400, 540.00, 7 ) +, ( 200490, 3210, 715.00, 1 ) +, ( 200490, 5505, 350.00, 1 ) +, ( 300350, 244, 2800.00, 20 ) +, ( 300350, 5100, 150.00, 5 ) +, ( 300350, 5110, 525.00, 12 ) +, ( 300350, 6301, 245.00, 5 ) +, ( 300350, 6400, 550.00, 5 ) +, ( 300380, 244, 3000.00, 6 ) +, ( 300380, 2402, 320.00, 12 ) +, ( 300380, 2405, 760.00, 8 ) +, ( 400410, 212, 2450.00, 12 ) +, ( 400410, 255, 3800.00, 12 ) +, ( 400410, 2001, 1000.00, 36 ) +, ( 400410, 6301, 240.00, 48 ) +, ( 400410, 6400, 500.00, 70 ) +, ( 400410, 7301, 415.00, 36 ) +, ( 500450, 212, 2500.00, 8 ) +, ( 500450, 255, 3900.00, 12 ) +, ( 500450, 2001, 1100.00, 16 ) +, ( 500450, 2002, 1500.00, 16 ) +, ( 500450, 2402, 330.00, 48 ) +, ( 600480, 2001, 1000.00, 60 ) +, ( 600480, 2002, 1450.00, 20 ) +, ( 600480, 2003, 1900.00, 40 ) +, ( 600480, 3103, 4000.00, 40 ) +, ( 600480, 3205, 625.00, 20 ) +, ( 600480, 5100, 135.00, 60 ) +, ( 600480, 5103, 390.00, 20 ) +, ( 600480, 7301, 425.00, 40 ) +, ( 700410, 2003, 1900.00, 65 ) +, ( 700410, 2403, 650.00, 10 ) +, ( 700510, 255, 4000.00, 4 ) +, ( 700510, 6500, 95.00, 8 ) +, ( 700510, 7102, 275.00, 5 ) +, ( 800660, 244, 3000.00, 6 ) +, ( 800660, 2001, 1000.00, 30 ) +, ( 800660, 2403, 600.00, 48 ) +, ( 800660, 2405, 795.00, 10 ) +, ( 800660, 3201, 525.00, 6 ) +, ( 800660, 3205, 600.00, 18 ) +, ( 800660, 3210, 715.00, 6 ) +, ( 800660, 4102, 26.00, 130 ) +, ( 800660, 5100, 150.00, 12 ) +, ( 800660, 5101, 200.00, 6 ) +, ( 800660, 5110, 490.00, 48 ) +, ( 800660, 5504, 165.00, 18 ) +, ( 800660, 6201, 195.00, 6 ) +, ( 800660, 6301, 235.00, 24 ) +, ( 800660, 6400, 525.00, 30 ) +, ( 800660, 6401, 700.00, 36 ) +, ( 800660, 6500, 95.00, 22 ) +, ( 800660, 7102, 275.00, 6 ) +, ( 800660, 7301, 425.00, 12 ) ; -UPDATE STATISTICS FOR TABLE demo.sales.odetail ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.sales.odetail ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/sales_orders_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/sales_orders_table.sql b/docs/spj_guide/src/resources/source/sales_orders_table.sql index 45dd0a0..b418082 100644 --- a/docs/spj_guide/src/resources/source/sales_orders_table.sql +++ b/docs/spj_guide/src/resources/source/sales_orders_table.sql @@ -1,14 +1,14 @@ -CREATE TABLE demo.sales.orders +CREATE TABLE trafodion.sales.orders ( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL , order_date DATE DEFAULT DATE '2011-07-01' NOT NULL , deliv_date DATE DEFAULT DATE '2011-08-01' NOT NULL , salesrep NUMERIC (4) UNSIGNED DEFAULT 0 NOT NULL , custnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL -, PRIMARY KEY (ordernum) +, PRIMARY KEY ( ordernum ) ) ; -ALTER TABLE demo.sales.orders - ADD CONSTRAINT demo.sales.date_constrnt CHECK (deliv_date >= order_date) +ALTER TABLE trafodion.sales.orders + ADD CONSTRAINT trafodion.sales.date_constrnt CHECK ( deliv_date >= order_date ) ; CREATE INDEX xordrep ON orders @@ -19,15 +19,15 @@ CREATE INDEX xordcus ON orders ( custnum ) ; -CREATE VIEW demo.sales.ordrep AS SELECT +CREATE VIEW trafodion.sales.ordrep AS SELECT empnum , last_name , ordernum , o.custnum FROM - demo.persnl.employee e -, demo.sales.orders o -, demo.sales.customer c + trafodion.persnl.employee e +, trafodion.sales.orders o +, trafodion.sales.customer c WHERE e.empnum = o.salesrep AND o.custnum = C.custnum ; @@ -37,17 +37,17 @@ CREATE INDEX xcustnam ON customer custname ) ; -CREATE VIEW demo.sales.custlist AS SELECT +CREATE VIEW trafodion.sales.custlist AS SELECT custnum , custname , street , city , state , postcode -FROM demo.sales.customer +FROM trafodion.sales.customer ; -INSERT INTO demo.sales.orders VALUES +INSERT INTO trafodion.sales.orders VALUES ( 100210, DATE '2011-04-10', DATE '2011-04-10', 220, 1234 ) , ( 100250, DATE '2011-01-23', DATE '2011-06-15', 220, 7777 ) , ( 101220, DATE '2011-07-21', DATE '2011-12-15', 221, 5635 ) @@ -63,4 +63,4 @@ INSERT INTO demo.sales.orders VALUES , ( 800660, DATE '2011-10-09', DATE '2011-11-01', 568, 3210 ) ; -UPDATE STATISTICS FOR TABLE demo.sales.orders ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.sales.orders ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/sales_parts_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/sales_parts_table.sql b/docs/spj_guide/src/resources/source/sales_parts_table.sql index ed7d2fc..267a814 100644 --- a/docs/spj_guide/src/resources/source/sales_parts_table.sql +++ b/docs/spj_guide/src/resources/source/sales_parts_table.sql @@ -1,16 +1,16 @@ -CREATE TABLE demo.sales.parts +CREATE TABLE trafodion.sales.parts ( partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , partdesc CHARACTER (18) NO DEFAULT NOT NULL , price NUMERIC (8, 2) NO DEFAULT NOT NULL , qty_available NUMERIC (5) DEFAULT 0 NOT NULL -, PRIMARY KEY (partnum) +, PRIMARY KEY ( partnum ) ) ; CREATE INDEX xpartdes ON parts ( partdesc ) ; -INSERT INTO demo.sales.parts VALUES +INSERT INTO trafodion.sales.parts VALUES ( 186, '186 MegaByte Disk', 186186.86, 186 ) , ( 212, 'PC SILVER, 20 MB', 2500.00, 3525 ) , ( 244, 'PC GOLD, 30 MB', 3000.00, 4426 ) @@ -37,9 +37,9 @@ INSERT INTO demo.sales.parts VALUES , ( 6400, 'STREAMING TAPE,M20', 550.00, 1268 ) , ( 6401, 'STREAMING TAPE,M60', 725.00, 1308 ) , ( 6500, 'DISK CONTROLLER', 95.00, 2532 ) -, ( 6603, 'PRINTER CONTROLLER', 45.00, 430 ) +, ( 6603, 'PRINTER CONTROLLER', 45.00, 430 ) , ( 7102, 'SMART MODEM, 1200', 275.00, 2200 ) , ( 7301, 'SMART MODEM, 2400', 425.00, 2332 ) ; -UPDATE STATISTICS FOR TABLE demo.sales.parts ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.sales.parts ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/sales_schema.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/sales_schema.sql b/docs/spj_guide/src/resources/source/sales_schema.sql index 3049491..f3867bd 100644 --- a/docs/spj_guide/src/resources/source/sales_schema.sql +++ b/docs/spj_guide/src/resources/source/sales_schema.sql @@ -1,2 +1,2 @@ -CREATE SCHEMA DEMO.SALES ; -SET SCHEMA DEMO.SALES ; +CREATE SCHEMA trafodion.sales ; +SET SCHEMA trafodion.sales ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/supplierinfo.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/supplierinfo.java b/docs/spj_guide/src/resources/source/supplierinfo.java index a75c617..c98a392 100644 --- a/docs/spj_guide/src/resources/source/supplierinfo.java +++ b/docs/spj_guide/src/resources/source/supplierinfo.java @@ -18,7 +18,7 @@ public static void supplierInfo( BigDecimal suppNum PreparedStatement getSupplier = conn.prepareStatement( "SELECT suppname, street, city, " + " state, postcode " - + "FROM demo.invent.supplier " + + "FROM trafodion.invent.supplier " + "WHERE suppnum = ?" ) ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/supplyquantities.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/supplyquantities.java b/docs/spj_guide/src/resources/source/supplyquantities.java index 466f444..59a6911 100644 --- a/docs/spj_guide/src/resources/source/supplyquantities.java +++ b/docs/spj_guide/src/resources/source/supplyquantities.java @@ -16,7 +16,7 @@ public static void supplyQuantities( int[] avgQty conn.prepareStatement( "SELECT AVG(qty_on_hand), " + " MIN(qty_on_hand), " + " MAX(qty_on_hand) " - + "FROM demo.invent.partloc" + + "FROM trafodion.invent.partloc" ) ; ResultSet rs = getQty.executeQuery() ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/topSalesReps.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/topSalesReps.java b/docs/spj_guide/src/resources/source/topSalesReps.java index 96be5db..beee8ca 100644 --- a/docs/spj_guide/src/resources/source/topSalesReps.java +++ b/docs/spj_guide/src/resources/source/topSalesReps.java @@ -26,10 +26,10 @@ public static void topSalesReps( int whichQuarter PreparedStatement getTopReps = conn.prepareStatement( "SELECT [first 5] e.empnum, e.first_name, " + "e.last_name, totals.total " - + "FROM demo.persnl.employee e, " + + "FROM trafodion.persnl.employee e, " + " ( SELECT o.salesrep, " + " SUM( od.unit_price * od.qty_ordered ) as total " - + " FROM demo.sales.orders o, demo.sales.odetail od " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail od " + " WHERE o.ordernum = od.ordernum " + " AND QUARTER( o.order_date ) = ? " + " GROUP BY o.salesrep " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc index 2a33dcc..4ead225 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -61,7 +61,7 @@ transaction (BEGIN WORK…COMMIT WORK) or when AUTOCOMMIT is OFF. To run the [cols="2*", options="headhttp://trafodion.apache.org/docs/sql_reference/index.html#limitser";] |=== | Statement | What It Does -| <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion database. +// | <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion database. | <<alter_table_statement,ALTER TABLE Statement>> | Changes attributes for a table. | <<alter_user_statement,ALTER USER Statement>> | Changes attributes for a user. | <<create_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a Trafodion database. @@ -155,7 +155,7 @@ authorization to access libraries or to execute SPJs or UDFs: [cols="2*",options="header"] |=== | Statement | What It Does -| <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion database. +// | <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion database. | <<call_statement,CALL Statement>> | Initiates the execution of a stored procedure in Java (SPJ) in a Trafodion database. | <<create_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a Trafodion database. | <<create_library_statement,CREATE LIBRARY Statement>> | Registers a library object in a Trafodion database. @@ -235,6 +235,7 @@ within a table. These statistics are used to devise optimized access plans. |=== +//// <<< [[alter_library_statement]] == ALTER LIBRARY Statement @@ -258,7 +259,7 @@ ALTER LIBRARY [[catalog-name.]schema-name.]library-name [[alter_library_syntax]] === Syntax Description of ALTER LIBRARY -* `[[_catalog-name_.]_schema-name_.]_library-name_` +* `\[[_catalog-name_.]_schema-name_.]_library-name_` + specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a library object that has already been registered in the schema. if you do not fully qualify the library name, trafodion sql @@ -305,7 +306,7 @@ ALTER LIBRARY sales.saleslib FILE Sales2.jar;` ``` ALTER LIBRARY myudfs FILE $TMUDFLIB; ``` - +//// <<< [[alter_table_statement]] @@ -757,7 +758,7 @@ argument-list is: + specifies an ANSI logical name of the form: + -`[[_catalog-name_.]_schema-name_.]_procedure-name_` +`\[[_catalog-name_.]_schema-name_.]_procedure-name_` + where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see <<identifiers,identifiers>> and <<database_object_names,database object names>>. @@ -1184,7 +1185,7 @@ specifies the name of the function and any SQL parameters that correspond to the + specifies an ANSI logical name of the form: + -`[[_catalog-name_.]_schema-name_.]_function-name_` +`\[[_catalog-name_.]_schema-name_.]_function-name_` + where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see <<identifiers,identifiers>> and <<database_object_names,database object names>>. @@ -1241,7 +1242,7 @@ trafodion. see <<data_types,data types>>. + specifies the case-sensitive name of the external functionâs method. -* `library [[_catalog-name_.]_schema-name_.]_library-name_` +* `library \[[_catalog-name_.]_schema-name_.]_library-name_` + specifies the ANSI logical name of a library containing the external function. if you do not fully qualify the library name, trafodion sql qualifies it according to the schema of the current session. @@ -1519,7 +1520,7 @@ CREATE LIBRARY [[catalog-name.]schema-name.]library-name [[create_library_syntax]] === Syntax Description of CREATE LIBRARY -* `[[_catalog-name_.]_schema-name_.]_library-name_` +* `\[[_catalog-name_.]_schema-name_.]_library-name_` + specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify a name that is unique and does not exist for @@ -1649,26 +1650,26 @@ the SPJ method. + specifies an ANSI logical name of the form: + -`[[_catalog-name_.]_schema-name_.]_procedure-name_` +`\[[_catalog-name_.]_schema-name_.]_procedure-name_` + -where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, +where each part of the name is a valid SQL identifier with a maximum of 128 characters. For more information, see <<identifiers,identifiers>> and <<database_object_names,database object names>>. + -specify a name that is unique and does not exist for any procedure or function in the same schema. trafodion sql -does not support the overloading of procedure names. that is, you cannot register the same procedure name more than +specify a name that is unique and does not exist for any procedure or function in the same schema. Trafodion +does not support the overloading of procedure names. That is, you cannot register the same procedure name more than once with different underlying SPJ methods. + -if you do not fully qualify the procedure name, trafodion sql qualifies it according to the schema of the current session. +If you do not fully qualify the procedure name, then Trafodion qualifies it according to the schema of the current session. -** _sql-parameter_ +** `_sql-parameter_` + -specifies an sql parameter that corresponds to the signature of the SPJ method: +specifies an SQL parameter that corresponds to the signature of the SPJ method: + `[_parameter-mode_] [_sql-identifier_] _sql-datatype_` *** `_parameter-mode_` + -specifies the mode in, out, or inout of a parameter. the default is in. +specifies the mode `in`, `out`, or `inout` of a parameter. The default is `in`. **** `in` + @@ -1676,19 +1677,19 @@ specifies a parameter that passes data to an SPJ. **** `out` + -specifies a parameter that accepts data from an SPJ. the parameter must be an array. +specifies a parameter that accepts data from an SPJ. The parameter must be an array. **** `inout` + -specifies a parameter that passes data to and accepts data from an SPJ. the parameter must be an array. +specifies a parameter that passes data to and accepts data from an SPJ. The parameter must be an array. *** `_sql-identifier_` + -specifies an sql identifier for the parameter. for more information, see <<identifiers,identifiers>>. +specifies an SQL identifier for the parameter. For more information, see <<identifiers,identifiers>>. *** `_sql-datatype_` + -specifies an sql data type that corresponds to the java parameter of the SPJ method. +specifies an SQL data type that corresponds to the Java parameter of the SPJ method. + _sql-datatype_ can be: + @@ -1733,11 +1734,11 @@ specifies the case-sensitive name of the SPJ method of the form: + `[_package-name_.]_class-name_._method-name_` + -the java method must exist in a java class file, _class-name_.class, within a library registered in the database. -the java method must be defined as public and static and have a return type of void. +The Java method must exist in a Java class file, _class-name_.class, within a library registered in the database. +The Java method must be defined as `public` and `static` and have a return type of `void`. + -if the class file that contains the SPJ method is part of a package, you must also specify the package name. -if you do not specify the package name, the create procedure statement fails to register the SPJ. +If the class file that contains the SPJ method is part of a package, then you must also specify the package name. +If you do not specify the package name, the create procedure statement fails to register the SPJ. ** `_java-signature_` + @@ -1745,44 +1746,44 @@ specifies the signature of the SPJ method and consists of: + `([_java-datatype_[, _java-datatype_]…])` + -the java signature is necessary only if you want to specify a java wrapper class (for example, java.lang.integer) instead of a java -primitive data type (for example, int). an sql data type maps to a java primitive data type by default. +The Java signature is necessary only if you want to specify a Java wrapper class (for example, `java.lang.integer`) instead of a java +primitive data type (for example, `int`). An SQL data type maps to a Java primitive data type by default. ++ +The Java signature is case-sensitive and must be placed within parentheses, such as `(java.lang.integer, java.lang.integer`). +The signature must specify each of the parameter data types in the order they appear in the Java method definition within +the class file. Each Java data type that corresponds to an out or inout parameter must be followed by empty square +brackets (`[ ]`), such as `java.lang.integer[]`. + -the java signature is case-sensitive and must be placed within parentheses, such as (java.lang.integer, java.lang.integer). -the signature must specify each of the parameter data types in the order they appear in the java method definition within -the class file. each java data type that corresponds to an out or inout parameter must be followed by empty square -brackets ([ ]), such as java.lang.integer[]. - <<< *** `_java-datatype_` + -specifies a mappable java data type. for the mapping of the java data types to sql data types, see _sql-datatype_. +Specifies a mappable Java data type. For the mapping of the Java data types to SQL data types, see _sql-datatype_. -* `library [[_catalog-name_.]_schema-name_.]_library-name_` +* `library \[[_catalog-name_.]_schema-name_.]_library-name_` + -specifies the ANSI logical name of a library containing the SPJ method. if you do not fully qualify the library name, -trafodion sql qualifies it according to the schema of the current session. +specifies the ANSI logical name of a library containing the SPJ method. If you do not fully qualify the library name, +then Trafodion qualifies it according to the schema of the current session. * `external security _external-security-type_` + determines the privileges, or rights, that users have when executing (or calling) the SPJ. An SPJ can have one of these types of external security: -** invoker determines that users can execute, or invoke, the stored procedure using the privileges of the user who invokes -the stored procedure. this behavior is referred to as _invoker rights_ and is the default behavior if external security is -not specified. invoker rights allow a user who has the execute privilege on the SPJ to call the SPJ using his or her existing -privileges. in this case, the user must be granted privileges to access the underlying database objects on which the SPJ operates. +** `invoker` determines that users can execute, or invoke, the stored procedure using the privileges of the user who invokes +the stored procedure. This behavior is referred to as _invoker rights_ and is the default behavior if external security is +not specified. Invoker rights allow a user who has the execute privilege on the SPJ to call the SPJ using his or her existing +privileges. In this case, the user must be granted privileges to access the underlying database objects on which the SPJ operates. + -note: granting a user privileges to the underlying database objects gives the user direct access to those database objects, -which could pose a risk to more sensitive or critical data to which users should not have access. for example, an SPJ -might operate on a subset of the data in an underlying database object, but that database object might contain other +NOTE: Granting a user privileges to the underlying database objects gives the user direct access to those database objects, +which could pose a risk to more sensitive or critical data to which users should not have access. For example, an SPJ +might operate on a subset of the data in an underlying database object but that database object might contain other more sensitive or critical data to which users should not have access. -** definer determines that users can execute, or invoke, the stored procedure using the privileges of the user who created -the stored procedure. this behavior is referred to as _definer rights_. the advantage of definer rights is that users are +** `definer` determines that users can execute, or invoke, the stored procedure using the privileges of the user who created +the stored procedure. This behavior is referred to as _definer rights_. The advantage of definer rights is that users are allowed to manipulate data by invoking the stored procedure without having to be granted privileges to the underlying -database objects. that way, users are restricted from directly accessing or manipulating more sensitive or critical data in -the database. however, be careful about the users to whom you grant execute privilege on an SPJ with definer external security +database objects. That way, users are restricted from directly accessing or manipulating more sensitive or critical data in +the database. However, be careful about the users to whom you grant execute privilege on an SPJ with definer external security because those users will be able to execute the SPJ without requiring privileges to the underlying database objects. <<< @@ -1792,46 +1793,46 @@ specifies that the external user-defined routine is written in the java language * `parameter style java` + -specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the java language. +specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the Java language. * `no sql` + -specifies that the SPJ cannot perform sql operations. +specifies that the SPJ cannot perform SQL operations. * `contains sql | modifies sql data | reads sql data` + -specifies that the SPJ can perform sql operations. all these options behave the same as contains sql, meaning that the SPJ -can read and modify sql data. use one of these options to register a method that contains sql statements. if you do not specify -an sql access mode, the default is contains sql. +specifies that the SPJ can perform SQL operations. All these options behave the same as `contains sql`, meaning that the SPJ +can read and modify SQL data. Use one of these options to register a method that contains SQL statements. Ff you do not specify +an SQL access mode, then the default is `contains sql`. * `dynamic result sets _integer_` + -specifies the maximum number of result sets that the SPJ can return. this option is applicable only if the method signature -contains a java.sql.resultset[] object. if the method contains a result set object, the valid range is 1 to 255 inclusive. -the actual number of result sets returned by the SPJ method can be less than or equal to this number. if you do not specify -this option, the default value is 0 (zero), meaning that the SPJ does not return result sets. +specifies the maximum number of result sets that the SPJ can return. This option is applicable only if the method signature +contains a `java.sql.resultset[]` object. If the method contains a result set object, then the valid range is 1 to 255 inclusive. +The actual number of result sets returned by the SPJ method can be fewer than or equal to this number. If you do not specify +this option, then the default value is 0 (zero), meaning that the SPJ does not return result sets. * `transaction required | no transaction required` + -determines whether the SPJ must run in a transaction inherited from the calling application (transaction required, the default -option) or whether the SPJ runs without inheriting the calling applicationâs transaction (no transaction required). typically, -you will want the stored procedure to inherit the transaction from the calling application. however, if the SPJ method does -not access the database or if you want the stored procedure to manage its own transactions, you should set the stored -procedureâs transaction attribute to no transaction required. for more information, see +determines whether the SPJ must run in a transaction inherited from the calling application (`transaction required`, the default +option) or whether the SPJ runs without inheriting the calling applicationâs transaction (`no transaction required`). Typically, +you want the stored procedure to inherit the transaction from the calling application. However, if the SPJ method does +not access the database or if you want the stored procedure to manage its own transactions, then you should set the stored +procedureâs transaction attribute to no transaction required. For more information, see <<effects_of_the_transaction_attribute_on_spjs,effects of the transaction attribute on SPJs>>. <<< * `deterministic | not deterministic` + specifies whether the SPJ always returns the same values for out and inout parameters for a given set of argument values -(deterministic) or does not return the same values (not deterministic, the default option). if you specify deterministic, -trafodion sql is not required to call the SPJ each time to produce results; instead, trafodion sql caches the results and -reuses them during subsequent calls, thus optimizing the call statement. +(`deterministic`) or does not return the same values (`not deterministic`, the default option). If you specify `deterministic`, +Trafodion is not required to call the SPJ each time to produce results; instead, Trafodion caches the results and +reuses them during subsequent calls, thus optimizing the CALL statement. * `no isolate | isolate` + -specifies that the SPJ executes either in the environment of the database server (no isolate) or in an isolated environment -(isolate, the default option). trafodion sql allows both options but always executes the SPJ in the udr server process (isolate). +specifies that the SPJ executes either in the environment of the database server (`no isolate`) or in an isolated environment +(`isolate`, the default option). Trafodion allows both options but always executes the SPJ in the UDR server process (`isolate`). [[create_procedure_considerations]] === Considerations for CREATE PROCEDURE @@ -1865,15 +1866,15 @@ _Using Transaction Control Statements or Methods_ If you specify TRANSACTION REQUIRED (the default option), a CALL statement automatically initiates a transaction if there is no active transaction. In this case, you should not use transaction control statements (or equivalent JDBC transaction methods) in the SPJ method. Transaction control statements include COMMIT WORK and ROLLBACK WORK, and the equivalent JDBC transaction -methods are Connection.commit() and Connection.rollback(). If you try to use transaction control statements or methods in an -SPJ method when the stored procedureâs transaction attribute is set to TRANSACTION REQUIRED, the transaction control statements +methods are `Connection.commit()` and `Connection.rollback()`. If you try to use transaction control statements or methods in an +SPJ method when the stored procedureâs transaction attribute is set to TRANSACTION REQUIRED, then the transaction control statements or methods in the SPJ method are ignored, and the Java virtual machine (JVM) does not report any errors or warnings. When the -stored procedureâs transaction attribute is set to TRANSACTION REQUIRED, you should rely on the transaction control statements +stored procedureâs transaction attribute is set to TRANSACTION REQUIRED, then you should rely on the transaction control statements or methods in the application that calls the stored procedure and allow the calling application to manage the transactions. _Committing or Rolling Back a Transaction_ -If you do not use transaction control statements in the calling application, the transaction initiated by the CALL statement +If you do not use transaction control statements in the calling application, then the transaction initiated by the CALL statement might not automatically commit or roll back changes to the database. When AUTOCOMMIT is ON (the default setting), the database engine automatically commits or rolls back any changes made to the database at the end of the CALL statement execution. However, when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client session or until you explicitly commit @@ -1904,7 +1905,7 @@ procedure. If you specify NO TRANSACTION REQUIRED and if the SPJ method creates a JDBC default connection, that connection will have autocommit enabled by default. You can either use the autocommit transactions or disable autocommit (conn.setAutoCommit(false);) and use the -JDBC transaction methods, Connection.commit() and Connection.rollback(), to commit or roll back work where needed. +JDBC transaction methods, `Connection.commit()` and `Connection.rollback()`, to commit or roll back work where needed. <<< [[create_procedure_examples]] @@ -1923,7 +1924,7 @@ CREATE PROCEDURE lowerprice() MODIFIES SQL DATA; ``` + -Because the procedure name is not qualified by a catalog and schema, Trafodion SQL qualifies it according to the current +Because the procedure name is not qualified by a catalog and schema, Trafodion qualifies it according to the current session settings, where the catalog is TRAFODION (by default) and the schema is set to SALES. Since the procedure needs to be able to read and modify SQL data, MODIFIES SQL DATA is specified in the CREATE PROCEDURE statement. + @@ -2010,7 +2011,7 @@ CREATE PROCEDURE sales.ordersummary(IN on_or_after_date VARCHAR (20), To invoke this SPJ, use this CALL statement: + ``` -CALL neo.sales.ordersummary('01-01-2014', ?); +CALL trafodion.sales.ordersummary('01-01-2014', ?); ``` + The ORDERSUMMARY procedure returns this information about the orders on or after the specified date, 01-01-2014: @@ -3417,7 +3418,7 @@ DROP FUNCTION [[catalog-name.]schema-name.]function-name [[drop_function_syntax]] === Syntax Description of DROP FUNCTION -* `[[_catalog-name_.]_schema-name_.]_function-name_` +* `\[[_catalog-name_.]_schema-name_.]_function-name_` + specifies the ANSI logical name of the function, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a function that has already been registered in the schema. if you do not fully qualify @@ -3530,7 +3531,7 @@ DROP LIBRARY [[catalog-name.]schema-name.]library-name [restrict | cascade] [[drop_library_syntax]] === Syntax Description of DROP LIBRARY -* `[[_catalog-name_.]_schema-name_.]_library-name_` +* `\[[_catalog-name_.]_schema-name_.]_library-name_` + specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a library object that has already been registered in the schema. if @@ -3600,7 +3601,7 @@ DROP PROCEDURE [[catalog-name.]schema-name.]procedure-name [[drop_procedure_syntax]] === Syntax Description of DROP PROCEDURE -* `[[_catalog-name_.]_schema-name_.]_procedure-name_` +* `\[[_catalog-name_.]_schema-name_.]_procedure-name_` + specifies the ANSI logical name of the stored procedure in java (SPJ), where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a procedure that has already been registered in the @@ -4236,7 +4237,7 @@ displays a list of all the existing components. displays the names of all the user-defined functions (UDFs) in the catalog and schema of the current session. By default, the catalog is TRAFODION, and the schema is SEABASE. -* `FUNCTIONS FOR LIBRARY [[_catalog-name_.]_schema-name_.]_library-name_` +* `FUNCTIONS FOR LIBRARY \[[_catalog-name_.]_schema-name_.]_library-name_` + displays the UDFs that reference the specified library. @@ -4258,7 +4259,7 @@ displays the libraries in the specified schema. displays the names of all the procedures in the catalog and schema of the current session. by default, the catalog is trafodion, and the schema is seabase. -* `procedures for library [[_catalog-name_.]_schema-name_.]_library-name_` +* `procedures for library \[[_catalog-name_.]_schema-name_.]_library-name_` + displays the procedures that reference the specified library. @@ -4319,7 +4320,7 @@ and the schema is seabase. + displays the names of all the views in the specified schema. for the _catalog-name_, you can specify only trafodion. -* `views on table [[_catalog-name_.]_schema-name_.]_table-name_` +* `views on table \[[_catalog-name_.]_schema-name_.]_table-name_` + displays the names of all the views that were created for the specified table. if you do not qualify the table name with catalog and schema names, get uses the catalog and schema of the current session. for the _catalog-name_, you can specify @@ -4903,7 +4904,7 @@ specifies one or more component privileges to grant. The comma-separated list ca |=== | Component | Component Privilege | Description | SQL_OPERATIONS | ALTER | Privilege to alter database objects -| | ALTER_LIBRARY | Privilege to alter libraries +// | | ALTER_LIBRARY | Privilege to alter libraries | | ALTER_TABLE | Privilege to alter tables | | ALTER_VIEW | Privilege to alter views | | CREATE | Privilege to create database objects @@ -5614,7 +5615,7 @@ SQL>prepare empsal from +>select salary from employee +>where jobcode = 100; -*** ERROR[4082] Table, view or stored procedure NEO.INVENT.EMPLOYEE does not exist or is inaccessible. +*** ERROR[4082] Table, view or stored procedure TRAFODION.INVENT.EMPLOYEE does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. SQL> ``` @@ -6399,7 +6400,7 @@ specifies the row from the old table exposed by the embedded delete. the old tab operation. new is not allowed. an implicit old.* return list is assumed for a delete operation that does not specify a return list. <<< -**** `_col-expr_ [[as] _name_]` +**** `_col-expr_ [[AS] _name_]` + specifies a derived column determined by the evaluation of an sql value expression in the list. any column referred to in a value expression is from the row in the old table exposed by the delete. the old table refers to column values before the delete operation.
