SQL0104N error while trying to use ADMIN_MOVE_TABLE for a range partitioned table.

Technote (troubleshooting)


Problem(Abstract)

The SQL0104N error is returned when trying to move a range partitioned table using the ADMIN_MOVE_TABLE procedure.

Symptom

The following error message can be seen:

SQL0104N An unexpected token "-" was found following "S_1003 STARTING(2010".
Expected tokens may include: ",". SQLSTATE=42601

The error messages seems to indicate a syntax error in a part of the range partition clause.



Diagnosing the problem

Test case:


db2 "CREATE TABLE DB2INST1.VINNY  (col1 BIGINT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE, col4 CHAR(1), col5 VARCHAR(20)) COMPRESS YES DISTRIBUTE BY HASH(col1)
     PARTITION BY RANGE("col3")
     PART "RVRS_1003" STARTING('2010-03-01') ENDING('2010-04-01') EXCLUSIVE IN "RCIRVRS",
     PART "RVRS_1004" STARTING('2010-04-01') ENDING('2010-05-01') EXCLUSIVE IN "RCIRVRS",
     PART "RVRS_1005" STARTING('2010-05-01') ENDING('2010-06-01') EXCLUSIVE IN "RCIRVRS",
     PART "RVRS_1006" STARTING('2010-06-01') ENDING('2010-07-01') EXCLUSIVE IN "RCIRVRS")"


CALL SYSPROC.ADMIN_MOVE_TABLE(
'DB2INST1',
'VINNY',
'A_RCIRVRS',
'A_RCIRVRS',
'A_RCIRVRS',
' ',
' ',
'PARTITION BY RANGE("col3")
(PART RVRS_1003 STARTING 2010-03-01 ENDING 2010-04-01 EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1004 STARTING 2010-04-01 ENDING 2010-05-01 EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1005 STARTING 2010-05-01 ENDING 2010-06-01 EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1006 STARTING 2010-06-01 ENDING 2010-07-01 EXCLUSIVE IN "A_RCIRVRS",
'',
'',
'MOVE')

SQL0104N  An unexpected token "-" was found following "S_1003 STARTING(2010".
Expected tokens may include:  ",".  SQLSTATE=42601


Resolving the problem

The syntax problem with the date range can be eliminated by using two single quotes for the date ranges in the ADMIN_MOVE_TABLE procedure as below:


CALL SYSPROC.ADMIN_MOVE_TABLE(
'DB2INST1',
'VINNY',
'A_RCIRVRS',
'A_RCIRVRS',
'A_RCIRVRS',
' ',
' ',
'PARTITION BY RANGE("col3")
(PART RVRS_1003 STARTING ''2010-03-01'' ENDING ''2010-04-01'' EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1004 STARTING ''2010-04-01'' ENDING ''2010-05-01'' EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1005 STARTING ''2010-05-01'' ENDING ''2010-06-01'' EXCLUSIVE IN "A_RCIRVRS",
PART RVRS_1006 STARTING ''2010-06-01'' ENDING ''2010-07-01'' EXCLUSIVE IN "A_RCIRVRS",
'',
'',
'MOVE')

  Result set 1
  --------------

  KEY                              VALUE
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1
  CLEANUP_END                      2011-07-21-12.10.51.260106
  CLEANUP_START                    2011-07-21-12.10.42.574514
  COPY_END                         2011-07-21-12.10.32.626891
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER
  COPY_START                       2011-07-21-12.10.29.709728
  COPY_TOTAL_ROWS                  0
  INDEXNAME
  INDEXSCHEMA
  INIT_END                         2011-07-21-12.10.27.998640
  INIT_START                       2011-07-21-12.10.24.140491
  REPLAY_END                       2011-07-21-12.10.42.242231
  REPLAY_START                     2011-07-21-12.10.32.627588
  REPLAY_TOTAL_ROWS                0
  REPLAY_TOTAL_TIME                1
  STATUS                           COMPLETE
  SWAP_END                         2011-07-21-12.10.42.525867
  SWAP_RETRIES                     0
  SWAP_START                       2011-07-21-12.10.42.364168
  VERSION                          09.07.0003

  20 record(s) selected.

  Return Status = 0

Note: The quotes for the date range used in the ADMIN_MOVE_TABLE alone are two single quotes. The quotes used for the new tablespace ("A_RCIRVRS") are double quotes.


Related information

ADMIN_MOVE_TABLE

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Advanced Enterprise Server, Enterprise Server, Express, Personal, Workgroup Server

Reference #:

1507250

Modified date:

2012-12-26

Translate my page

Machine Translation

Content navigation