Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem defining containers for a DMS(DB managed Sorage) Tablespace. Can you help?

Posted on 2011-03-06
5
1 solution
Medium Priority
?
1,072 Views
Last Modified: 2012-05-11
Hello all,

I am on DB29.5 Data Warehouse Edition(DWE)  I am unable to complete a tablespace container definition because of this error:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1757N  A USING clause without an ON DATABASE PARTITION clause is missing.
SQLSTATE=428B1

During SQL processing it returned:SQL1756N  More than one clause specifies containers without the ON NODES clause.  SQLSTATE=428B1
The Tablespace definition that I am using

CREATE LARGE TABLESPACE "DWS_TS_00049_001" IN DATABASE PARTITION GROUP PDPG PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE  '/db2fs1p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p1/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DBPARTITIONNUMS (1)
USING (FILE  '/db2fs1p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p2/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DBPARTITIONNUMS (2)
USING (FILE  '/db2fs1p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p3/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DBPARTITIONNUMS (3)

EXTENTSIZE 16 PREFETCHSIZE AUTOMATIC
BUFFERPOOL BP_16K
OVERHEAD 5.750000 TRANSFERRATE 0.400000
AUTORESIZE YES INCREASESIZE 1 M
MAXSIZE 50M
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;

Ok, out of curiosity, I changed the partition parameters as follows:

CREATE LARGE TABLESPACE "DWS_TS_00049_001" IN DATABASE PARTITION GROUP PDPG PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE  '/db2fs1p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p1/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DATABASE PARTITION (1)
USING (FILE  '/db2fs1p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p2/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DATABASE PARTITION (2)
USING (FILE  '/db2fs1p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p3/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON DATABASE PARTITION (3)

and got the following error.  
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DATABASE PARTITION" was found following
"_00049_001' 300 ) ON".  Expected tokens may include:  "<node_keyword>".
SQLSTATE=42601


Can anyone help me with the correct syntax ASAP?

Thanks everyone


0
Comment
Question by:Okonita001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35046388
try


CREATE LARGE TABLESPACE "DWS_TS_00049_001" IN DATABASE PARTITION GROUP PDPG PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE  '/db2fs1p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p1/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p1/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON NODES (1)
USING (FILE  '/db2fs1p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p2/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p2/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON NODES (2)
USING (FILE  '/db2fs1p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs2p3/bcuaixd/edwd/DWS_TS_00049_001' 300,
       FILE  '/db2fs3p3/bcuaixd/edwd/DWS_TS_00049_001' 300 ) ON NODES (3)
0
 

Author Comment

by:Okonita001
ID: 35048473
Thank you for your response. So that I can understand, what is really wrong with the
ON DBPARTITIONNUMS (1) , ON DBPARTITIONNUMS (2) , and ON DBPARTITIONNUMS (3)  parameters?
Most of the tablespaces that we have in this shop are defined that way and I have used it in the shop myself. But this time I am getting the error I have described earlier. I want to be able to explain a change to what you may consider a standard in the shop.
Can you help in this regard? If I can keep the specs the same as most of the other tablespaces (ie ON DBPARTITIONNUMS (1) ), that will be very great.

Thanks


0
 

Author Comment

by:Okonita001
ID: 35056244
momi_sabag,

I seemed to have solve my problem. We have 24 nodes/partitions in our prod system. Because the tablespace being created was relatively small, I reasoned that I don't need to utilize all 24 parts for this tablespace and chose to use parts 1 to 3. After the upteenth time trying my way, I decided to expand the containers definition to all 24 parts. This is when the definition worked withe ON DBPARTITIONNUMS expression.

My question is this: Can I conclude that if a Db2 DB partitioned system has X number of partitions, must all tablespace definition use all partitions? If true, what is the reason behind it?

0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 35056667
no

when you define the tablespace you used
IN DATABASE PARTITION GROUP PDPG

each partition group can contain a different subset of the database partitions,
so in your case, PDPG contains all 24

you can create another partition group which will only contain 1 to 3 and then use it in your definition of the tablespace
0
 

Author Closing Comment

by:Okonita001
ID: 35078954
I have been able to confirm your explanation of what the source of my problem is. In short, you have to define your tablespace containers to span the number of partition set defined for the partition group. In my case I was trying to use only 3 partitions out of 24 and DB2 did not approve of that. When I striped the definition across all partitions, I was successful.

Thank you Momi_sabaq
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Access It Now
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Lone Date Ranger :: there is just one right way
Article by: PortletPaul
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite Queries in Oracle
Article by: Swadhin
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Linux/ Unix Bash Shell: Working with Files
Video by: Dototot
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Executing a SQL Script from a Unix Shell Script and Passing Parameters
Video by: Steve
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Advertise Here

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here