Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Course Of The Month
8 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Problem defining containers for a DMS(DB managed Sorage) Tablespace. Can you help?
Posted on 2011-03-06
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/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
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/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
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
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/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
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/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
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
[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
-
3
2
5 Comments
try
CREATE LARGE TABLESPACE "DWS_TS_00049_001" IN DATABASE PARTITION GROUP PDPG PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE '/db2fs1p1/bcuaixd/edwd/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
CREATE LARGE TABLESPACE "DWS_TS_00049_001" IN DATABASE PARTITION GROUP PDPG PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE '/db2fs1p1/bcuaixd/edwd/DW
FILE '/db2fs2p1/bcuaixd/edwd/DW
FILE '/db2fs3p1/bcuaixd/edwd/DW
USING (FILE '/db2fs1p2/bcuaixd/edwd/DW
FILE '/db2fs2p2/bcuaixd/edwd/DW
FILE '/db2fs3p2/bcuaixd/edwd/DW
USING (FILE '/db2fs1p3/bcuaixd/edwd/DW
FILE '/db2fs2p3/bcuaixd/edwd/DW
FILE '/db2fs3p3/bcuaixd/edwd/DW
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
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
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?
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?
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
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
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
Thank you Momi_sabaq
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Learn how to get help with Linux/Unix bash shell commands.
Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
In a previous video, we went over how to export a DynamoDB table into Amazon S3. In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 5 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.