CODEWITHSUNDEEP
db2
czajek
czajek

Reputation: 699

default tablespace for schema in DB2

We have got some DDLs for our schema. These DDLs create tables and indexes in a given tablespace. Something like:

    CREATE TABLE mySchema.myTable
(
    someField1              CHAR(2)  NOT NULL ,
    someField2            VARCHAR(70)  NOT NULL 
)
        IN MY_TBSPC
        INDEX IN MY_TBSPC;

We want to reuse this DDLs to run some integration tests using APACHE DERBY. The problem is that such syntax is not accepted by DERBY. Is there any way to define a kind of default tablespace for tables and indexes, so we can remove this 'IN TABLESPACE' statements.

Upvotes: 1

Views: 5265

Answers (1)

mustaccio
mustaccio

Reputation: 18945

There is no deterministic way of defining a "default" tablespace in DB2 (I'm assuming we're dealing with DB2 for LUW here). If the tablespaces are not explicitly indicated in the CREATE TABLE statement, the database manager will pick for table data the first tablespace with the suitable page size that you are authorized to use, and indexes will be stored in the same tablespace as data.

This means that if you only have one user tablespace it will always be used for both data and indexes, so in a way it becomes the default. However, if you have more than one tablespace with different page sizes you may end up with tables (and their indexes) in different tablespaces.

Upvotes: 2

Related Questions