Jack,
There is definitely a terminology disconnect here. I think
you're
having a problem generating the *table* DDL when you have a
range
partitioned table.
Here's what I get (I'm running on 9.1 Fixpack 3a, so I'm a little
behind you):
-------- cut here --------
$ db2look -d sample -z ian -t test_part -e -l
-- No userid was specified, db2look tries to use Environment
variable USER
-- USER is: IBJORHOV
-- Specified SCHEMA is: IAN
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- This CLP file was created using DB2LOOK Version 9.1
-- Timestamp: Wed Jan 7 11:11:04 PST 2009
-- Database Name: SAMPLE
-- Database Manager Version: DB2/AIX64 Version 9.1.3
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE
CONNECT TO SAMPLE;
-- Mimic tablespace
ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 12.670000
FILE SYSTEM CACHING
TRANSFERRATE 0.180000;
ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 12.670000
FILE SYSTEM CACHING
TRANSFERRATE 0.180000;
------------------------------------------------
-- DDL Statements for table "IAN "."TEST_PART"
------------------------------------------------
The db2look utility could not generate table DDL rc = 1090
The db2look utility is now terminating
-------- cut here --------
db2look runs for about 15-20 seconds before printing the "could
not
generate table DDL" error.
I said above that the issue is with generating the DDL for a
partitioned table, not the tablespace. On my system, the -l
option
has no effect on whether db2look runs successfully or not - it
always
fails. Note, if I just run `db2look -d sample -l` I do get the
DDL
for all tablespaces in the database, no problem.
Is this what you are seeing as well? If so, I'd open a PMR for
this.
Ian Bjorhovde
On Wed, Jan 7, 2009 at 10:59 AM, Jack Campbell
<[login to unmask email]> wrote:
> Jeff
>
> It may be my db2 z/os terminology causing the confusion - see
below for a
> copy of my test DDL.
>
> 1) I created 3 tablespaces (TS_JACK_001 - 003)
> 2) I created jack.TEST_PART (and partitioned by range)
> 3) db2look does not generate the tablespace definitions
>
> *we have similar tables already defined in our PROD
environment (using just a
> single tablespace but multiple partition by range clauses on
the table)
>
> --------------------------------------------------------
> -- create tablespace
> --------------------------------------------------------
> CREATE REGULAR TABLESPACE TS_JACK_001
> IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
> PAGESIZE 16384 MANAGED BY SYSTEM
> USING ('/codsdwh/fs0/TS_JACK_001_01')
> EXTENTSIZE 4
> PREFETCHSIZE 192
> BUFFERPOOL BP_16K
> OVERHEAD 7.500000
> TRANSFERRATE 0.060000
> DROPPED TABLE RECOVERY OFF
> NO FILE SYSTEM CACHING
> ;
>
> CREATE REGULAR TABLESPACE TS_JACK_002
> IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
> PAGESIZE 16384 MANAGED BY SYSTEM
> USING ('/codsdwh/fs0/TS_JACK_002_01')
> EXTENTSIZE 4
> PREFETCHSIZE 192
> BUFFERPOOL BP_16K
> OVERHEAD 7.500000
> TRANSFERRATE 0.060000
> DROPPED TABLE RECOVERY OFF
> NO FILE SYSTEM CACHING
> ;
>
> CREATE REGULAR TABLESPACE TS_JACK_003
> IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
> PAGESIZE 16384 MANAGED BY SYSTEM
> USING ('/codsdwh/fs0/TS_JACK_003_01')
> EXTENTSIZE 4
> PREFETCHSIZE 192
> BUFFERPOOL BP_16K
> OVERHEAD 7.500000
> TRANSFERRATE 0.060000
> DROPPED TABLE RECOVERY OFF
> NO FILE SYSTEM CACHING
> ;
>
> --------------------------------------------------------
> -- create table
> --------------------------------------------------------
> create table jack.TEST_PART
> (PART_PK integer not null generated BY DEFAULT
> AS IDENTITY (
> START WITH 1,
> INCREMENT BY 1,
> CYCLE,
> NO ORDER,
> MAXVALUE 2147483647,
> MINVALUE 1,
> NO CACHE)
> ,PART_NO integer not null
> ,PART_TEXT char(50) not null
> ,LAST_UPDATE_TS timestamp not null with default
> )
> PARTITION BY RANGE(PART_NO)
> (STARTING FROM(0) ENDING(3) in "TS_JACK_001"
> ,STARTING FROM(4) ENDING(6) in "TS_JACK_002"
> ,STARTING FROM(7) ENDING(9) in "TS_JACK_003"
> )
> ;
>
> --------------------------------------------------------
> -- create index
> --------------------------------------------------------
> create unique index jack.PK_TEST_PART
> on jack.TEST_PART
> (PART_PK ASC)
> NOT PARTITIONED
> pctfree 0
> allow reverse scans
> ;
>
> create index jack.TEST_PART_IDX1
> on jack.TEST_PART
> (PART_NO ASC
> ,PART_TEXT ASC
> )
> pctfree 0
> allow reverse scans
> ;
> --------------------------------------------------------
> -- create <Primary Key>
> --------------------------------------------------------
> alter table jack.TEST_PART
> add constraint TEST_PART_PK primary key (PART_PK)
> ;
>
>
______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
http://IDUG.ORG/Events
*
>
______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new
password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your
member profile. If you are not already an IDUG.org member, please
register at
http://www.idug.org/component/juser/register.html
>
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
http://IDUG.ORG/Events *
______________________________________________________________________
IDUG.org was recently updated requiring members to use a new
password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your
member profile. If you are not already an IDUG.org member, please
register at
http://www.idug.org/component/juser/register.html
