3

I am creating my tablespaces in the sql scripts that create the database schema. This causes a problem in continuous integration processes, where consecutive execution of the script is not possible because it always tries to create a new instance of the same tablespace.

For triggers there is the call CREATE OR REPLACE TRIGGER but I couldn't find one for tablespaces. Any way around this other than dropping the tablespaces when the schema is dropped?

mustaccio
  • 20,465
  • 17
  • 49
  • 60
user1340582
  • 193
  • 4
  • 1
    Doesn't really make any sense to implement such a feature for tablespaces, as they're storage, not code. – Philᵀᴹ Mar 24 '14 at 14:29
  • Well, database setup is an important part of continuous integration. The database scripts themselves should be part of the CI process. Therefore it does make sense to continuously re-create the database. – user1340582 Mar 24 '14 at 19:02
  • Just a thought...if you are really going to follow continuous integration all the way, why not have it drop and re-create the database from scratch every time? We actually do that where I work. The same scripts we use to deploy a new instance of this database is used by our CI server to drop and rebuild the databases. It is probably overkill as Phil mentions. But it does work. In our case we script most everything with KSH and check it into Subversion. We use Jenkins to check things out and deploy and run them on our AIX development boxes. – Chris Aldrich Mar 25 '14 at 12:54

1 Answers1

0

You can do it programmatically by querying the catalog. However, there is no command like in DB2.

You can query the view SYSCAT.TBSPACES and look for you tablespace. If it exists drop it, then create a new one.

Tom V
  • 15,330
  • 7
  • 58
  • 86
AngocA
  • 579
  • 4
  • 17