How to specify the tablespaces when create DB2 table in DS

ibillow · Post by **ibillow** » Sun May 17, 2009 10:06 pm

I am using DB2 Enterprise stage to create tables in the DB2 9.1 partitioning db. It seems it use the default tablespaces by default and no other option.

Is there any way to specify the tablespaces use when creating tables by DB2 Enterprise stage ?

Thanks in advance.

chulett · Post by **chulett** » Sun May 17, 2009 10:29 pm

Well... that *is* what the 'default tablespaces' are for, to be used by default when none are specified.

I'm not familiar with the gory details of the DB2 stage, but whatever normal syntax you'd use for this should be perfectly valid there as well. I would assume there's some kind of 'user-defined' or 'custom' option available for this.

ibillow · Post by **ibillow** » Sun May 17, 2009 11:36 pm

I also assume there's some kind of custom option available in DataStage .

But I couldn't find anyway to specify the tablespaces when create DB2 tables through using DB2 Enterprise Stage.

Anyone knows how to specify that ?
Thanks.

chulett wrote:Well... that *is* what the 'default tablespaces' are for, to be used by default when none are specified.

I'm not familiar with the gory details of the DB2 stage, but whatever normal syntax you'd use for this should be perfectly valid there as well. I would assume there's some kind of 'user-defined' or 'custom' option available for this.

ray.wurlod · Post by **ray.wurlod** » Mon May 18, 2009 12:16 am

Only if you can edit the generated DDL. I prefer to copy the generated DDL from DataStage, paste it into a suitable tool (like DB2 Control Center or TOAD for DB2), and amend it there for any requirements I have - tablespaces, indexes, named constraints, and so on).

ibillow · Post by **ibillow** » Mon May 18, 2009 12:34 am

I understand I could create the table by using DDL to specify the tablespaces.
But just wondering whether DataStage can specify tablespaces also. (it makes the process simpler

ray.wurlod · Post by **ray.wurlod** » Mon May 18, 2009 1:57 am

It does not.

ibillow · Post by **ibillow** » Mon May 18, 2009 2:03 am

Too bad...

Thanks Ray...
btw... anyway to change the default tablespace DS is using ?

ray.wurlod · Post by **ray.wurlod** » Mon May 18, 2009 2:12 am

Not in DataStage. But you can change the default tablespace that the DB2 user is using within DB2.

stefanfrost1 · Post by **stefanfrost1** » Mon May 18, 2009 3:42 am

In the DB-enterprise stage using for example MODE = Write Create, you get a option called DB Options within which you can specify tabelspace amongst other (however missing a whole lot of options available in regular DDL)

Type: String
Specify an optional table space or partitioning keys to be used by DB2 to create a table. You can specify this option only when Write Mode is set to Create or Replace. By default, the table is created in all processing nodes in the default table space and uses the first column in the table, corresponding to the first input column on the input link, as the partitioning key. You specify arguments as a string enclosed in braces in the form: {tablespace=<t_space>,[key=<col0>,...]}, i.e. This string can contain a single tablespace argument, and multiple key arguments

ibillow · Post by **ibillow** » Mon May 18, 2009 8:03 pm

Great ! it works !
I put
tablespace="<tblspace>"
in the db option... it works.

Thanks stefanfrost1 !

stefanfrost1 wrote:In the DB-enterprise stage using for example MODE = Write Create, you get a option called DB Options within which you can specify tabelspace amongst other (however missing a whole lot of options available in regular DDL)

Type: String
Specify an optional table space or partitioning keys to be used by DB2 to create a table. You can specify this option only when Write Mode is set to Create or Replace. By default, the table is created in all processing nodes in the default table space and uses the first column in the table, corresponding to the first input column on the input link, as the partitioning key. You specify arguments as a string enclosed in braces in the form: {tablespace=<t_space>,[key=<col0>,...]}, i.e. This string can contain a single tablespace argument, and multiple key arguments