Partitionning of DB2 Tables

[Steve Schiltz]

Hi everybody,

we're using Powerdesigner to document, maintain and create DB2 tables
(DB2 8.1 for MVS) by using ddl scripts generated out of PD. Our
reporting datawarehouse consists mainly of two types of tables:
1) the daily ones (for which I've managed to create correct ddl's
which were accepted by the DBA);
2) the monthly tables which are in fact unloads of the corresponding
daily tables in order to keep a history at the end of month.

Those monthly tables are aimed to keep 3 years of data, in 36
partitions. The partition key is made out of two columns, VALANE
(year / char(4)) and NUMMOI (month / char(2)). We use a system of
partition rolling that automatically when all the partitions are used
will push out the oldest one, roll all of the others one step up and
create empty space for the new data (using logical and physical
partitions).

Currently the ddl of such a table would be something like:

CREATE TABLE XXXX.ABCDEF
(NUMMOI CHAR(2) FOR SBCS DATA NOT NULL,
VALANE CHAR(4) FOR SBCS DATA NOT NULL,
COLUMN1 CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT,
...
...)
IN XXXX.ABCDEFS
PARTITION BY
(VALANE ASC,
NUMMOI ASC)
(PART 1 VALUES ('2010', '01'),
PART 2 VALUES ('2010', '02'),
.....)

On the tablespace side the ddl looks like:

CREATE LARGE TABLESPACE ABDCEFS
IN XXXX
...
NUMPARTS 36
(PART 1 USING STOGROUP ......,
PART 2 USING STOGROUP .....,
...)


Question :

Does anyone know how to generate this kind of ddl with the help of PD?

Regards

Steve

[rkkier]

Not conveniently.

We have an Oracle warehouse doing something similar. We use a stored
procedure in the database to add partitions after the table is created
that go through the current month and we call that in the End Script
for the table. So, in the table we just define what we want the first
partition to look like and we let the database handle the rest. This
worked well in our environment because we called the same database
package as part of our ETL processes to extend the tables as needed.

It looks something like:

begin
some_package.partition_mgr.new_part_range(p_begin_period => null,
p_end_period =>
to_char(sysdate,'yyyymm'),
p_owner => '%OWNER%',
p_table_nm => '%TABLE%',
p_freq_cd => 'M'); --
monthly (we had partitions at the day and week as well
end;
/

Obviously the syntax would be a bit different for DB2 but you get the
general idea. Since adding/removing partitions will probably be
taking place on tables between releases of your DDL it might make more
sense to code all that once into the database and just call the
procedure from PowerDesigner.

[Steve Schiltz]

Hi,

from what I know about Oracle, the handling of partitions is much
easier in Oracle than in DB2. You can't for instance in DB2 create
partitions dynamically i.e. adding partitions and subpartitions when
you need them:( Also, the number of partitions in DB2 is very limited
compared to Oracle. What's more, our Powerdesigner is not connected
directly to RDBMS, and we have to deliver a ddl script right to the
DBA. However, the idea of using the End script tab for the table
definition might be a good idea. Do you have any idea if it is
possible to call a VB script from in there in order to create my 36
partitions without having to code them individually; or might it be a
better idea to alter the DBMS script right away?
Regards
Steve

[rkkier]

You should be able to call vbscript by enclosing it in
the .vbscript/.endvbscript macro. You can also use a
BeforeDatabaseGenerate trigger to execute a method that puts text into
the after tab, which I have done.

They should get you to the same place. I would start by coding it in
a method for the table, then you can use executeCustomMethod to call
it either way.