![]() ![]() WARNING: psql version 9.2, server version 8.0. bash-4.2$ psql -h. -U postgres -d tpch -p 5439 The following example create the schema on PostgreSQL psql -d tpch -f pgtpchtables.sqlĪnd the following on Redshift bash-4.2$ psql -h. -U postgres -d tpch -p 5439 -f pgtpchtables.sql Run the files as follows under PostgreSQL or Redshift to create the desired tables. This generates a series of files containing the required DDL as follows: CREATE TABLE customer ( On Linux systems you can use the bash shell to generate the DDL for all tables with one command, for example: for sys in customer lineitem nation orders part partsupp region supplier do pg_dump -U postgres -h localhost tpch -t $sys -schema-only -f $sys.sql done To create the DLL for PostgreSQL or Amazon Redshift (note you can create a template local PostgreSQL database and the DDL is 100% compatible to create a database in Redshift) use the pg_dump command as follows: pg_dump -U postgres -h localhost tpch -t table_name -schema-only -f table.sql Mysql> source /home/mysql/TPCHDATA/createtpch.sql `S_PHONE` char(15) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,Ĭreate a file containing all of the table creation statements and run as follows: sql> use tpch `S_ADDRESS` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `S_NAME` char(25) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `S_COMMENT` varchar(102) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, ![]() You can turn off this feature to get a quicker startup with -A Reading table information for completion of table and column names Be aware that if foreign keys are defined at this stage they will significantly impact load performance. Run the file as follows: db2 -tvf tpchcreate.sqlĭB20000I The SQL command completed successfully.ĬREATE TABLE "DB2INST1"."ORDERS" ( "O_ORDERKEY" INTEGER NOT NULL, "O_CUSTKEY" INTEGER NOT NULL, "O_ORDERSTATUS" CHAR(1 OCTETS) NOT NULL, "O_TOTALPRICE" DOUBLE NOT NULL, "O_ORDERDATE" DATE NOT NULL, "O_ORDERPRIORITY" CHAR(15 OCTETS) NOT NULL, "O_CLERK" CHAR(15 OCTETS) NOT NULL, "O_SHIPPRIORITY" INTEGER, "O_COMMENT" VARCHAR(79 OCTETS) NOT NULL ) IN "USERSPACE1" ORGANIZE BY ROWįor MySQL use the show create table command. "O_COMMENT" VARCHAR(79 OCTETS) NOT NULL ) "O_ORDERPRIORITY" CHAR(15 OCTETS) NOT NULL , "O_ORDERSTATUS" CHAR(1 OCTETS) NOT NULL , The output file will contain output as follows: DDL Statements for Table "DB2INST1"."ORDERS" db2look -d TPCH -a -e -x -o tpchcreate.sql Oracle Database 12c Enterprise Edition Release 12.1.0.2.0įor Db2 use the db2look command, this can generate the DDL for all objects within a schema with one command. create table newtable (id int ) The maximum length for the table name is 127 bytes longer names are truncated to 127 bytes. Joining these files together can then be run against the database to create the schema of empty tables: sqlplus tpch/tpchĬopyright (c) 1982, 2014, Oracle. This produces a Create Table statement such as follows: CREATE TABLE "TPCH"."ORDERS"ĬONSTRAINT "ORDERS_PK" PRIMARY KEY ("O_ORDERKEY") SQL>select DBMS_METADATA.GET_DDL('TABLE','ORDERS') from dual As the user owning the template database at a sqlplus prompt run a GET_DDL SQL statement as follows, noting that you need to set the long and pagesize values correctly to view all of the output. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |