Unanswered: Can I specify tablespace first,then create multiple tables without using 'IN' keyword
Can I specify tablespace first,then create multiple tables without using 'IN' keyword??
If I didn't specify 'IN tablespace_name' statement in my create table ddl, all the created tablespace will be resided in a tablespace named 'USERSPACE1' . what I want is to specify a tablespace other than the 'USERSPACE1' before creating tables, and therefore, I don't need to use the 'IN tablespace_name' in every ddl statement for creating table.....
just similar to the way i specify the schema (by command 'set current schema my_schema_name') before I create the tables and all the created tables will under the schema my_schema_name and I don't need to specfiy my_schema_name in every ddl statement for creating table.....
The answer is no. If you do not specify explicitly which tablespace to create a table in, DB2 uses a complex algorithm (based on USE privaleges) to determine which tablespace it will place the table. And if you have more than one tablespace that will qualify, DB2 will just pick one.
Frankly, if I am creating production tables, I would perfer to make the explicit decision where to place them. That way I can manage them better.
I believe the reason that there is nothing like SET CURRENT SCHEMA for tablespaces, is that table creation is done many orders of magnitude less then queries.