|
View previous topic :: View next topic
|
| Author |
Message |
masterlock
New User
Joined: 16 Mar 2007
Posts: 6
Location: India
|
|
|
|
Hi Experts,
I have a requirement for partitioning a table. I tried the below query in our shop -
| Code: |
CREATE TABLE ACEDB002.PARTTB
( EMPNO SMALLINT,
FNAME CHAR(10),
STATE CHAR(2))
PARTITION BY (EMPNO ASC)
(PARTITION 1 ENDING AT ( 100),
PARTITION 2 ENDING AT ( 200),
PARTITION 3 ENDING AT ( 300))
IN ACEDB002.ACESLOCL; |
Below is the error that I am getting in SPUFI.
| Code: |
DSNT408I SQLCODE = -20183, ERROR: THE PARTITIONED, ADD PARTITION, ADD
PARTITIONING KEY, ALTER PARTITION, OR ROTATE PARTITION CLAUSE
SPECIFIED ON CREATE OR ALTER FOR ACEDB002.PARTTB IS NOT VALID
DSNT418I SQLSTATE = 428FT SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIPKY SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 5 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000005' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION |
The DB2 version I am using is 8.1 which should support Table Controlled Partition. Following is the snapshot of -DIS GROUP
| Code: |
*** BEGIN DISPLAY OF GROUP(DSND01G ) GROUP LEVEL(810) MODE(N)
PROTOCOL LEVEL(2) GROUP ATTACH NAME(D01G) |
Can you plase tell me why I am getting this error? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
MasterLock,
Try this,
| Code: |
CREATE TABLE ACEDB002.PARTTB
( EMPNO SMALLINT,
FNAME CHAR(10),
STATE CHAR(2)) IN ACEDB002.ACESLOCL
PARTITION BY (EMPNO ASC)
(PARTITION 1 ENDING AT ( 100),
PARTITION 2 ENDING AT ( 200),
PARTITION 3 ENDING AT ( 300)) ; |
Does ACESLOCL tablespace has 3 partitions ?
Masterlock you need to unlock & explore DB2 V8 - SQL REFERENCE manual.
Thanks,
Sushanth |
|
| Back to top |
|
|
masterlock
New User
Joined: 16 Mar 2007
Posts: 6
Location: India
|
|
|
|
Thanks everyone,
My initial query is working now. The problem was with the non-partitioned tablespace as you mentioned.
Can we use any scalar function in the PARTITION BY clause? For example if we need to partion based on the last two digits of an employee id. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
| Quote: |
| Can we use any scalar function in the PARTITION BY clause? |
Try, let us know if you have any errors.
Sushanth |
|
| Back to top |
|
|
masterlock
New User
Joined: 16 Mar 2007
Posts: 6
Location: India
|
|
|
|
| I have tried as PARTITION BY (MOD(EMPNO,10) ASC) but it is not working. Just wanted to know if this feature is availabe in DB2 or not. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Masterlock,
Hmm....
You can have index on expressions, in which you can use SCALAR functions. Combine it with index partitioning.
You will have what you want.
But, the problem is, to it i have be in v9.
Good news is last week IBM announced V10.
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
