CREATE SCHEMA statement

Recently at work we had an Informix consultant work with us for three months. Other than a small newspaper cutting entitled Disasters blamed on over-confidence, his other advice to me was be anal!

So it was with this simple dictum in mind that I approached the rebuild of some databases using dbschema.

Perhaps at this point I should warn readers that this post does not cover anything new – the feature I am about to talk about has been in Informix since at least version 10.00 and is in many ways unremarkable – but it seems to be rarely used, judging by how infrequently I have seen it mentioned in various postings over the years.

The scenario is simple: I want to import a database under my own user id but at the end of the exercise I want all the tables, indices, constraints, triggers, views and procedures/functions to be owned by a single schema user.

I thought I had it licked by diligently specifying owners for all the objects in the schema. However when I dug a little deeper and ran statements like:

select trim(t.owner) || '.' || t.tabname || '#' || trim(c.owner) || '.' || c.constrname from sysconstraints c, systables t where c.tabid=t.tabid and t.tabid>99 and c.owner!='schemaowner' and c.constrtype='N';

I found that all the not null constraints were owned by myself and some of the other objects as well.

Thinking back to the past, I think Informix 7.31 used to export the owner and name of not null constraints when dbschema was used but this feature seems to have gone, even when the -ss option is used.

So what to do? This kind of thing is a source of irritation to the anal DBA. Would I need to do through all my create table statements and add explicit constraint owner and names in for all the not null columns as well as checking for a plethora of other problems like implicit indices? Of course I could create the tables whilst logged in as the schema owner but such actions upset PCI compliance officers.

Browsing the docs today I happened across the CREATE SCHEMA statement. This is a simple one-liner you can put at the start of the script to create your objects. Interestingly you have to remove the semi-colons from your script and leave just one at the end, a little bit like using the FOREACH statement in SPL. It makes you wonder whether Informix needs this terminator most of the time.

A simple example of this is:

create schema authorization schemaowner
create table tab1 (
col1 int not null,
col2 int not null
)

create table tab2 (
col1 int not null,
col2 int not null
);

Checking sysconstraints and systables everything now belongs to schemaowner. I feel I can rest at night now 🙂

Advertisements

One Comment on “CREATE SCHEMA statement”

  1. Sean Durity says:

    I did not know about this statement or how it could be effective for the problem at hand. Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s