Page 1 of 1
How to specify the tablespaces when create DB2 table in DS
Posted: Sun May 17, 2009 10:06 pm
by ibillow
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.
Posted: Sun May 17, 2009 10:29 pm
by chulett
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.
Posted: Sun May 17, 2009 11:36 pm
by ibillow
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.
Posted: Mon May 18, 2009 12:16 am
by ray.wurlod
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).
Posted: Mon May 18, 2009 12:34 am
by ibillow
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
Posted: Mon May 18, 2009 1:57 am
by ray.wurlod
It does not.
Posted: Mon May 18, 2009 2:03 am
by ibillow
Too bad...
Thanks Ray...
btw... anyway to change the default tablespace DS is using ?
Posted: Mon May 18, 2009 2:12 am
by ray.wurlod
Not in DataStage. But you can change the default tablespace that the DB2 user is using within DB2.
Posted: Mon May 18, 2009 3:42 am
by stefanfrost1
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
Posted: Mon May 18, 2009 8:03 pm
by ibillow
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