DB2 9 Express C Functionality

[p175]

If I have ESE 8.2.5, I assume v9 will be able to take such an
installation and upgrade same without problems ? [when available of
course]

I'm investigating whether we can also use Express C as a development /
test environment for same but am now reading that what I thought were
key elements of DB2 are not supported in Express C, for example:

MQT (Summary Tables)
MDC tables

I can't survive without my MQTs. If this indeed the case, then what
else is going to be missing inregard to internal features for I can
find no menton of such restrictions.

Are the following supported in Express C ?

Performance Expert
Automatic FCM tuning
Automatic Storage
Automatic Prefetchers / Pagecleaners
Automatic self-tuning memory
Developer Workbench
DGTTs
Table Compression

See:
http://www-128.ibm.com/developerwork...m=805#13854931

Does anyone have any links to this specific information as yet ? I can
see a lot of other generic comparison info but nothing that
SPECIFICALLY states support of internal functions such as MDC / MQT
restrictions.

Many thanks,

Tim.D

[Serge Rielau]

p175 wrote:

If I have ESE 8.2.5, I assume v9 will be able to take such an
installation and upgrade same without problems ? [when available of
course]

That's the idea. There are steps to be taken of course for DB2 V8
databases when migrating.

I'm investigating whether we can also use Express C as a development /
test environment for same but am now reading that what I thought were
key elements of DB2 are not supported in Express C, for example:

MQT (Summary Tables)
MDC tables

I didn't know that, but it makes kind of sense, since both MQT and MDC
are fairly advanced features typically used in warehousing which is not
the typical target of an DB2 Express C deployment.

I can't survive without my MQTs. If this indeed the case, then what
else is going to be missing inregard to internal features for I can
find no menton of such restrictions.

Are the following supported in Express C ?

Performance Expert

Performance expert is a tool. You pay for it separately. To the best of
my knowledge it doesn't care what edition of DB2 it connects to.

Automatic FCM tuning

FCM is about the DPF feature. Like worrying about not having a periscope
in a car :-)

Automatic Storage

Definitely supported. The whole idea of automation is more important for
small installs (liek a typical Express C) than big ones.

Automatic Prefetchers / Pagecleaners

Supported (same as above)

Automatic self-tuning memory

Supported (same as above)

Developer Workbench

DWB even supports Derby :-)

DGTTs

Definitly supported.

Table Compression

I strongly suspect NOT supported.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Burkhard Schultheis]

Serge Rielau schrieb:

p175 wrote:

>If I have ESE 8.2.5, I assume v9 will be able to take such an
installation and upgrade same without problems ? [when available of
course]

That's the idea. There are steps to be taken of course for DB2 V8
databases when migrating.

>I'm investigating whether we can also use Express C as a development /
test environment for same but am now reading that what I thought were
key elements of DB2 are not supported in Express C, for example:

MQT (Summary Tables)
MDC tables

I didn't know that, but it makes kind of sense, since both MQT and MDC
are fairly advanced features typically used in warehousing which is not
the typical target of an DB2 Express C deployment.

>I can't survive without my MQTs. If this indeed the case, then what
else is going to be missing inregard to internal features for I can
find no menton of such restrictions.

Are the following supported in Express C ?

Performance Expert

Performance expert is a tool. You pay for it separately. To the best of
my knowledge it doesn't care what edition of DB2 it connects to.

>Automatic FCM tuning

FCM is about the DPF feature. Like worrying about not having a periscope
in a car :-)

>Automatic Storage

Definitely supported. The whole idea of automation is more important for
small installs (liek a typical Express C) than big ones.

>Automatic Prefetchers / Pagecleaners

Supported (same as above)

>Automatic self-tuning memory

Supported (same as above)

>Developer Workbench

DWB even supports Derby :-)

>DGTTs

Definitly supported.

>Table Compression

I strongly suspect NOT supported.

See
http://www-128.ibm.com/developerwork...m=805#13854931

Regards,
Burkhard
--
A: Weil es die Lesbarkeit des Textes verschlechtert.
F: Warum ist TOFU so schlimm?
A: TOFU
F: Was ist das größte Ärgernis im Usenet?

[Another DB2 DBA]

I'm investigating whether we can also use Express C as a development /

test environment for same but am now reading that what I thought were
key elements of DB2 are not supported in Express C, for example:

MQT (Summary Tables)
MDC tables

I didn't know that, but it makes kind of sense, since both MQT and MDC
are fairly advanced features typically used in warehousing which is not
the typical target of an DB2 Express C deployment.

I can't survive without my MQTs. If this indeed the case, then what
else is going to be missing inregard to internal features for I can
find no menton of such restrictions.

As far as I can tell, MDC and MQTs are supported in Express-C. I have
been using both features in my tests of DB2 Express-C on 64-bit Linux.

Hope this helps,

Fred

[p175]

Seems we have some confusion in the ranks. It is, it isn't .. heh

So if one is to upgrade to DB2 9 over the top of 8.2.5, where can one
find the link for the appropriate download ? If there is a download is
it like the FP's in that we can install it and essentially upgrade an
existing installation ?

Still would really like to know about what Express C really does and
does not support .. LOL

[Serge Rielau]

p175 wrote:

Seems we have some confusion in the ranks. It is, it isn't .. heh

Actually no confusion

MQT (Summary Tables)
MDC tables

_I didn't know that, but it makes kind of sense_
When I don't now I say so....

>
So if one is to upgrade to DB2 9 over the top of 8.2.5, where can one
find the link for the appropriate download ? If there is a download is
it like the FP's in that we can install it and essentially upgrade an
existing installation ?

The downloads (both DB2 9 and Db2 V8.2.4) are off the DB2 Express Ch
omepage:
http://www-306.ibm.com/software/data.../download.html

I normally de-install before I install, but that's just me....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[p175]

Sorry Serge, I shudda explained .. I was after the ESE version .. my
bad.

I was investigating Express C simply because I want to take a look at
whether I can use it personally on my own home setup.

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>
Still would really like to know about what Express C really does and
does not support .. LOL

I have seen several IBM web sites that attempt to explain the differences,
but they are all contradictory or incomplete. I don't think anyone on this
planet actually knows.

[p175]

As Fred mentioned earlier above I am very happy to confirm the Express
C does indeed support MQT, so that posting in the IBM DB2 forum is
incorrect as it stands. It also supports Clustered Indexes which I was
led to believe was also not supported.

I've just finished an upgrade over the top of an 8.2.5 installation.
Only a couple of minor issues, the instance did not migrate
automatically and failed. I eventually ended up having to drop and
re-create. Database migratation was painless and fairly quick as it
turns out.

Registry variables had to be reset, doesn't hold any custom Control
Center Settings you may have.

Other than that, it's working a treat.

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

As Fred mentioned earlier above I am very happy to confirm the Express
C does indeed support MQT, so that posting in the IBM DB2 forum is
incorrect as it stands. It also supports Clustered Indexes which I was
led to believe was also not supported.

I've just finished an upgrade over the top of an 8.2.5 installation.
Only a couple of minor issues, the instance did not migrate
automatically and failed. I eventually ended up having to drop and
re-create. Database migratation was painless and fairly quick as it
turns out.

Registry variables had to be reset, doesn't hold any custom Control
Center Settings you may have.

Other than that, it's working a treat.

I wonder if the MQT support in Express-C was a mistake and may be removed
later. Every explanation I have seen says it not supported in any
distribution except ESE.

When you say clustered indexes, are your talking about MDC or regular
clustering? MDC is also supposed to only be supported in ESE. Regular
clustering is supported in all releases.

[p175]

Mark,

Regular clustering. I haven't yet attempted to use MDC tables in any
version so I would not expect it to work. I sure hope they do not
remove MQT functions as I would have thought that is essential in ANY
environment.

Only issue I have so far is a repeating message in the db2diag.log,
anyone got any clues on this ? This is going to get very annoying very
quickly .. heh

2006-08-09-03.11.27.396000-240 I16612H412 LEVEL: Severe
PID : 1048 TID : 1300 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000
APPHDL : 0-642 APPID: *LOCAL.DB2.060809071127
FUNCTION: DB2 UDB, base sys utilities, sqleattach_agent, probe:60
RETCODE : ZRC=0x805C00E8=-2141454104=SQLZ_RC_INV_NAMESPACE, SQLT_SQLEX
"Invalid Namespace"

I just configured automatic memory management as well, very interesting
to watch it work as it tunes up the various parameters, writes messages
to the db2diag.log for each change as it dynamically alters
bufferpools, sort heaps, package / catalog cache, locklist and database
memory.

Will be interesting to see what impact it has on overall performance.

[p175]

Just a word of caution regarding backups in Express C V9

Seems the location for any backup images has been changed in V9. Where
it used to be directed to subdirectories under the path of the
specified backup, it is now directed to a single file with a long file
name to the exact directory specified.

For example, a statement 'BACKUP DATABASE ABC TO "D:\BACKUP\DB2\ABC"
would have in the past created subdirectories under D:\BACKUP\DB2\ABC
being "\ABC.0\DB2\NODE0000\CATN0000\DATE\time.001

Whereas now it creates a single file being
"ABC.0.DB2.NODE0000.CATN0000.timestamp.001"

[Serge Rielau]

p175 wrote:

Just a word of caution regarding backups in Express C V9

Seems the location for any backup images has been changed in V9. Where
it used to be directed to subdirectories under the path of the
specified backup, it is now directed to a single file with a long file
name to the exact directory specified.

For example, a statement 'BACKUP DATABASE ABC TO "D:\BACKUP\DB2\ABC"
would have in the past created subdirectories under D:\BACKUP\DB2\ABC
being "\ABC.0\DB2\NODE0000\CATN0000\DATE\time.001

Whereas now it creates a single file being
"ABC.0.DB2.NODE0000.CATN0000.timestamp.001"

Here is the topic in What's New:
http://publib.boulder.ibm.com/infoce...c/c0023381.htm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[p175]

Thanks Serge, Makes for some very enlightening reading .. heh

Just so you know, I've installed compression on various tables in my
upgraded 8.2.5 database. Works very nicely indeed but one has to reorg
the table after setting the flag for compression to become active.

[Serge Rielau]

p175 wrote:

Thanks Serge, Makes for some very enlightening reading .. heh

Just so you know, I've installed compression on various tables in my
upgraded 8.2.5 database. Works very nicely indeed but one has to reorg
the table after setting the flag for compression to become active.

Is the issue that it's two statements or that it's not online?
Certainly wouldn't be a big deal to chain the reorg to the ALTER TABLE.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[p175]

It's no problem at all Serge.. LOL, I was just pointing out it's a two
step process. Simply altering the table and setting compression on will
not actually compress the table on it's own is all.

Looks as though MDC is indeed also supported as having run the Design
Advisor it recommended a couple of MDC table conversions.

[Knut Stolze]

p175 wrote:

It's no problem at all Serge.. LOL, I was just pointing out it's a two
step process. Simply altering the table and setting compression on will
not actually compress the table on it's own is all.

That's the usual behavior for some other changes made to tables. For
example, changes to the inline length (structured types) becomes effective
with a reorg only.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

It's no problem at all Serge.. LOL, I was just pointing out it's a two
step process. Simply altering the table and setting compression on will
not actually compress the table on it's own is all.

You would not want it any other way. For a large table, you could get hung
up for a long time just making the DDL change to specify compression.

Same applies to changing PCTFREE or adding a new column (the space for the
column does not get created until a reorg, or a value for that column is
updated/inserted).