[LUW] Question on "db2look"

Jack Campbell

[LUW] Question on "db2look"
We are running DB2 LUW v9.1 fix pack 4a.

I am having trouble with the DB2LOOK - DDL and statistics generator. I can
successfully extract the TABLESPACE ddl for any table when the tablespace is
not a partitioned space.

But if the table is partitioned DB2LOOK does not extract the tablespace DDL?

Anyone familiar with this issue (or have work arounds or fixes)?

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

DB2 DBA Jeffrey Benner

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
This may be your problem? ( from *http://tinyurl.com/6sfcf4 ):

"*The *db2look* command has changed due to the introduction of automatic
storage databases to multiple partition configurations. You must now ensure
that all database partitions are active before issuing the *db2look* command
is issued. If any of the database partitions is not active, a warning
message stating that DDL for a table space could not be generated is issued.
This change to the *db2look* command affects all types of table spaces.*"
*
On Fri, Jan 2, 2009 at 11:52, Jack Campbell <[login to unmask email]> wrote:

> We are running DB2 LUW v9.1 fix pack 4a.
>
> I am having trouble with the DB2LOOK - DDL and statistics generator. I can
> successfully extract the TABLESPACE ddl for any table when the tablespace
> is
> not a partitioned space.
>
> But if the table is partitioned DB2LOOK does not extract the tablespace
> DDL?
>
> Anyone familiar with this issue (or have work arounds or fixes)?
>
> Regards
>
> Jack
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * 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 Melbourne, Australia * 18-20 March * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to DB2 DBA Jeffrey Benner)
Jeff

thanks for the reply - we are not using automatic storage DB - all our spaces
are either SMS or DMS defined.

For my test tablespace the spaces are defined using SMS (DDL below):

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
;

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

Ravi Khandelwal

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
what is the exact db2look command you are issuing?




________________________________
From: Jack Campbell <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, January 2, 2009 11:52:39 AM
Subject: [DB2-L] [LUW] Question on "db2look"

We are running DB2 LUW v9.1 fix pack 4a.

I am having trouble with the DB2LOOK - DDL and statistics generator. I can
successfully extract the TABLESPACE ddl for any table when the tablespace is
not a partitioned space.

But if the table is partitioned DB2LOOK does not extract the tablespace DDL?

Anyone familiar with this issue (or have work arounds or fixes)?

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to Ravi Khandelwal)
Ravi

very basic command to interogate catalog for table jack.TEST_PART (test
table with 3 partitions)


db2look -d namdwh -z jack -t TEST_PART -e -l

*If i use the same cmd on a none partitioned table it extract the tablespace
DDL - but not when run against a partitioned table.

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

DB2 DBA Jeffrey Benner

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
Jack: So, following up on my note about the documentation caveat, can you
confirm that all partitions are active before running the command?

I just ran a local version of the command you posted, on a non-DPF database
at version 9 Fixpack 4 and it generated tablespace DDL fine. If you have
verified all partitions are active, and the command still does not generate
tablespace DDL, it sounds like you have grounds for opening a PMR.

On Mon, Jan 5, 2009 at 08:44, Jack Campbell <[login to unmask email]> wrote:

> Ravi
>
> very basic command to interogate catalog for table jack.TEST_PART (test
> table with 3 partitions)
>
>
> db2look -d namdwh -z jack -t TEST_PART -e -l
>
> *If i use the same cmd on a none partitioned table it extract the
> tablespace
> DDL - but not when run against a partitioned table.
>
> Regards
>
> Jack
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * 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 Melbourne, Australia * 18-20 March * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to DB2 DBA Jeffrey Benner)
Jeff

Sorry I need to ask this - how do I verify the partitions are active (and
activate them if necessary). I've just picked up UDB support and it is taking a
while to get upto speed?

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

David G Stritzinger

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
Jack,

By default a data base is not active when the instance starts.
When the first connect to the data base is made, the data base comes
alive (activates). When the last connection to a data base signs off
(connect reset), by default the data base goes to sleep (deactivates).

If you want to keep the data base alive, after you start the
instance ( or any time after that) do the activate command, db2 activate
db db_name. If you want to "shut the data base down" run the deactivate
command, db2 deactivate db db_name.

See the DB2 Command Reference Guide which documents these two
commands.


David Stritzinger

(804) 965-7166 (Office Number)

(804) 334-3629 (Cell Number)


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jack Campbell
Sent: Monday, January 05, 2009 1:00 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Question on "db2look"

Jeff

Sorry I need to ask this - how do I verify the partitions are active
(and activate them if necessary). I've just picked up UDB support and
it is taking a while to get upto speed?

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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 Melbourne, Australia * 18-20 March * 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

David G Stritzinger

Re: [LUW] Question on "db2look"
(in response to David G Stritzinger)

One more thing. I can not remember if you were asking about Unix
/ AIX or Windows, but if you are on AIX, if the data base is active you
will see the DB2 processes running for the data base. Lets say your
instance is db2inst1 and your data base name is SAMPLE, you can do
something like ps -ef|grep db2inst1|grep -i sample. If none of the DB2
processes come back (i.e db2agent(p), db2logmgr, etc) then I would have
to assume that the data base is not active.

Of course if you want to see if all the partitions are started
(I am assuming that all the partitions are running on the same LPAR for
both examples) you can check for the db2sysc process (there should be
one for each DB2 partition), ps -ef|grep db2inst1|grep db2sysc

David Stritzinger

(804) 965-7166 (Office Number)

(804) 334-3629 (Cell Number)


-----Original Message-----
From: Stritzinger, David G
Sent: Monday, January 05, 2009 3:30 PM
To: 'DB2 Database Discussion list at IDUG'
Subject: RE: [DB2-L] [LUW] Question on "db2look"

Jack,

By default a data base is not active when the instance starts.
When the first connect to the data base is made, the data base comes
alive (activates). When the last connection to a data base signs off
(connect reset), by default the data base goes to sleep (deactivates).

If you want to keep the data base alive, after you start the
instance ( or any time after that) do the activate command, db2 activate
db db_name. If you want to "shut the data base down" run the deactivate
command, db2 deactivate db db_name.

See the DB2 Command Reference Guide which documents these two
commands.


David Stritzinger

(804) 965-7166 (Office Number)

(804) 334-3629 (Cell Number)


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jack Campbell
Sent: Monday, January 05, 2009 1:00 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Question on "db2look"

Jeff

Sorry I need to ask this - how do I verify the partitions are active
(and activate them if necessary). I've just picked up UDB support and
it is taking a while to get upto speed?

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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 Melbourne, Australia * 18-20 March * 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

nadir doctor

Re: [LUW] Question on "db2look"
(in response to David G Stritzinger)
You're hitting APAR IZ27818/IZ29944/IZ29945 [depending on usage of db2
version 8, 9 or 9.5].


Regards,
Nadir

On Fri, Jan 2, 2009 at 11:52 AM, Jack Campbell <[login to unmask email]>wrote:

> We are running DB2 LUW v9.1 fix pack 4a.
>
> I am having trouble with the DB2LOOK - DDL and statistics generator. I can
> successfully extract the TABLESPACE ddl for any table when the tablespace
> is
> not a partitioned space.
>
> But if the table is partitioned DB2LOOK does not extract the tablespace
> DDL?
>
> Anyone familiar with this issue (or have work arounds or fixes)?
>
> Regards
>
> Jack
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * 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 Melbourne, Australia * 18-20 March * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to nadir doctor)
David

thanks for the information. I am running DB2 UDB on AIX....

I attempted an "activate db xxxxx" and received msg "application is already
connected to an active database" ...so that piece looked good

The ps -ef | grep xxxx - listed db2agent / db2logmgr....etc- but I could not
find any db2sysc processes?

I again tried my "db2look" to no avail (still no tablespace DDL)

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

Ian Bjorhovde

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
Jack,

Are you running the db2look command as the instance owner? Or are you
running it under a different ID?

With partitioned databases, db2look invokes db2_all under the covers
to get information about the tablespace containers on each database
partition. If the ID you are using to execute db2look isn't set up to
execute db2_all properly, you'll either get errors or your tablespace
DDL won't have any actual container definitions shown.

You can verify this by executing "db2_all date" - you should an answer
back from each database partition.






On Fri, Jan 2, 2009 at 10:52 AM, Jack Campbell <[login to unmask email]> wrote:
> We are running DB2 LUW v9.1 fix pack 4a.
>
> I am having trouble with the DB2LOOK - DDL and statistics generator. I can
> successfully extract the TABLESPACE ddl for any table when the tablespace is
> not a partitioned space.
>
> But if the table is partitioned DB2LOOK does not extract the tablespace DDL?
>
> Anyone familiar with this issue (or have work arounds or fixes)?
>
> Regards
>
> Jack
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * 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 Rome, Italy * 5-9 October * 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

David G Stritzinger

Re: [LUW] Question on "db2look"
(in response to Ian Bjorhovde)
Jack,

In your example below where you found the db2agent etc
processes, but no db2sysc processes, this makes sense if .... the xxxx
in your example is the data base name. The db2sysc process is at the
instance level (you can almost say it is the instance).

If you only have one instance running on the LPAR you can just
go a ps -ef|grep db2sysc and you should see one for each DB2 partition.
If you have more that one instance running on a LPAR (instance names,
db2inst1 and db2inst2 for example) then to check if a particular
instance (db2inst1) db2sysc processes are running (i.e., the instance
partitions are started) then you would have to go a ps -ef|grep
db2inst1|grep db2sysc.

I hope that clears it up a little bit more. Some DB2 processes
are at the instance level and others are data base specific.

Here is a link to an old tech article about the DB2 processes.
It is getting a little dated, but looks like it is still valid (at least
through V8.2).


http://www-128.ibm.com/developerworks/db2/library/techarticle/0304chong/
0304chong.html#table1



David Stritzinger

(804) 965-7166 (Office Number)

(804) 334-3629 (Cell Number)


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jack Campbell
Sent: Monday, January 05, 2009 4:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [LUW] Question on "db2look"

David

thanks for the information. I am running DB2 UDB on AIX....

I attempted an "activate db xxxxx" and received msg "application is
already connected to an active database" ...so that piece looked good

The ps -ef | grep xxxx - listed db2agent / db2logmgr....etc- but I could
not find any db2sysc processes?

I again tried my "db2look" to no avail (still no tablespace DDL)

Regards

Jack

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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 Rome, Italy * 5-9 October * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to David G Stritzinger)
Ian

thanks for the information - I tried both of the ADMIN userid's available to me
and execute db2_all date - I received "permission denied" in both cases.

Looks like I may need to find out how to get instance owner privelege?

Jack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
I checked with one of our systems guys about access to "db2_all" - he
indicated that this would only apply under UDB EEE - we are UDB EE, running a
single instance on a sngle NODE.

So still stumped on why db2look does not show the tablespace DDL


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * 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

DB2 DBA Jeffrey Benner

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
It sounds like there is considerable confusion about partitioning . . . in
your original post you said "I can successfully extract the TABLESPACE ddl
for any table when the tablespace is
not a partitioned space." - if you are using partitioning then you ARE what
used to be called EEE (now DPF). So which is it? You already said you are
able to extract tablespace DDL for a "non-partitioned space" so you should
be fine, if that is the only kind of space you have (i.e. you are not EEE).

On Tue, Jan 6, 2009 at 10:14, Jack Campbell <[login to unmask email]> wrote:

> I checked with one of our systems guys about access to "db2_all" - he
> indicated that this would only apply under UDB EEE - we are UDB EE, running
> a
> single instance on a sngle NODE.
>
> So still stumped on why db2look does not show the tablespace DDL
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * 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 Rome, Italy * 5-9 October * 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to DB2 DBA Jeffrey Benner)
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

DB2 DBA Jeffrey Benner

Re: [LUW] Question on "db2look"
(in response to Ian Bjorhovde)
OK, sounds like you're using table partitioning but not Data Partitioning
Feature (DPF). At long last I think I know the problem. In the version 9
db2look command syntax [ *http://tinyurl.com/9rprrt *] it says, under the
option flag section "-t Tname1 Tname2 ... TnameN":

"When -t is used with -l, the combination does not support partitioned
tables in DB2 Version 9.1."

So the particular syntax you were using for db2look is no longer supported:

db2look -d namdwh -z jack -t TEST_PART -e -l

On Wed, Jan 7, 2009 at 11:59, 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

Ian Bjorhovde

Re: [LUW] Question on "db2look"
(in response to Jack Campbell)
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

Ian Bjorhovde

Re: [LUW] Question on "db2look"
(in response to DB2 DBA Jeffrey Benner)
Dang. I'm usually the guy who says RTFM, too.

Good catch!


Ian Bjorhovde



On 1/7/09, Jeffrey Benner, DB2 DBA <[login to unmask email]> wrote:
> OK, sounds like you're using table partitioning but not Data Partitioning
> Feature (DPF). At long last I think I know the problem. In the version 9
> db2look command syntax [ *http://tinyurl.com/9rprrt *] it says, under the
> option flag section "-t Tname1 Tname2 ... TnameN":
>
> "When -t is used with -l, the combination does not support partitioned
> tables in DB2 Version 9.1."
>
> So the particular syntax you were using for db2look is no longer supported:
>
> db2look -d namdwh -z jack -t TEST_PART -e -l
>
> On Wed, Jan 7, 2009 at 11:59, 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
>

______________________________________________________________________

* 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

Jack Campbell

Re: [LUW] Question on "db2look"
(in response to Ian Bjorhovde)
Jeff and Ian

thanks for all your help......Looks like I at least have a work around. I can use
db2look -d xxxxx -l to get the tablespace DDL and just merge this into the
table/index script.

Anyone know if this "feature" is corrceted in higer releases?

Regards

Jack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * 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