• Table
  • Create Table
  • Rename Table
  • Truncate Table
  • Drop Table
  • Column
    • Add Column
    • Rename Column
    • Drop Column
• View
  • Create View
  • Drop View
• Index
  • Create Index
  • Drop Index
• Constraint
  • Create Primary Key
  • Create Foreign Key
• Procedure
  • Create Procedure
  • Drop Procedure

Table

Create Table

JDataStore allows to create a new table. After entering the table name and column information, DtSQL can generate and execute the SQL to create the table.

• Column Name
• Column Type
• Length/Precesion
• Scale (decimal)
• Default Value
• Nullable
• Primary Key
• Unique

Sample :
CREATE TABLE "DEFAULT_SCHEMA"."TEST_TABLE" (
a bigint DEFAULT 1 NOT NULL,
b char ( 12 ) DEFAULT 'abc' NOT NULL,
c int,

PRIMARY KEY ( a, b ),
UNIQUE ( b,c )
)

Rename Table

JDataStore allows to rename an existing table. After entering the table name, DtSQL can generate and execute the SQL to rename the table.

Sample : RENAME TABLE "DEFAULT_SCHEMA"."TEST_TABLE" TO TESTTABLE

Truncate Table

JDataStore does not allows to truncate an table and deleting table is used instead. After entering the table name, DtSQL can delete all data in the table.

Sample : DELETE FROM "DEFAULT_SCHEMA"."TEST_TABLE"

Drop Table

JDataStore allows to drop an existing table. After entering the table name, DtSQL can generate and execute the SQL to drop the table.

Sample : DROP TABLE "DEFAULT_SCHEMA"."TEST_TABLE"

Column

Add Column

JDataStore allows to add a new column to an existing table. After selecting the table and entering column information, DtSQL can generate and execute the SQL to add the new column to the table.

• Column Name
• Column Type
• Length/Precesion
• Scale (decimal)
• Default Value
• Nullable

Sample :
ALTER TABLE
"DEFAULT_SCHEMA"."TEST_TABLE"
ADD
d varchar(20) DEFAULT 'def' NOT NULL

Rename Column

JDataStore allows to rename the column name. After entering the new column name, DtSQL can generate and execute the SQL to change the column name.

Sample :
ALTER TABLE "DEFAULT_SCHEMA"."TEST_TABLE"
ALTER COLUMN F RENAME TO F1

Drop Column

JDataStore allows to delete an existing column. After selecting the column, DtSQL can generate and execute the SQL to delete the column.

Sample :
ALTER TABLE "DEFAULT_SCHEMA"."TEST_TABLE"
DROP COLUMN F

View

Create View

JDataStore allows to create a new view. After entering the view name and select SQL, DtSQL can generate and execute the SQL to create the view.

Sample :
CREATE VIEW "DEFAULT_SCHEMA"."TEST_VIEW"
AS
SELECT * FROM "DEFAULT_SCHEMA"."TEST_TABLE"

Drop View

JDataStore allows to drop an existing view. After selecting the view name, DtSQL can generate and execute the SQL to delete the view.

Sample : DROP VIEW "DEFAULT_SCHEMA"."TEST_VIEW"

Index

Create Index

JDataStore allows to create a new index. After selecting the table columns and entering the view name, DtSQL can generate and execute the SQL to create the index.

Sample :
CREATE UNIQUE INDEX "TEST_INDEX"
ON "DEFAULT_SCHEMA"."TEST_TABLE" ( A, B DESC, C DESC )

Drop Index

JDataStore allows to drop an existing index. After selecting the index name, DtSQL can generate and execute the SQL to delete the index.

Sample : DROP INDEX "DEFAULT_SCHEMA"."TEST_INDEX"

Constraint

Create Primary Key

JDataStore allows to create a primary key for a table. After selecting the table columns, DtSQL can generate and execute the SQL to create the primary key.

Sample :
ALTER TABLE
"DEFAULT_SCHEMA"."TEST_TABLE"
ADD
PRIMARY KEY ( B, C )

Create Foreign Key

JDataStore allows to create a foreign key for a table to reference to another table. After selecting the table columns and referenced table columns, DtSQL can generate and execute the SQL to create the foreign key.

Sample :
ALTER TABLE
"DEFAULT_SCHEMA"."TEST_TABLE"
ADD
FOREIGN KEY ( A, C )
REFERENCES "DEFAULT_SCHEMA"."TEST_TB" (B, A )

Procedure

Create Procedure

JDataStore allows to create procedures. After entering the procedure name, parameter names and procedure body, DtSQL can generate and execute the SQL to create the procedure.

Sample :
CREATE JAVA_METHOD "DEFAULT_SCHEMA"."TEST_PROC"
AS
'java.lang.Math.abs'

Drop Procedure

JDataStore allows to drop an existing procedure. After selecting the procedure, DtSQL can generate and execute the SQL to delete the procedure.

Sample : DROP PROCEDURE "DEFAULT_SCHEMA"."TEST_PROC"