IBM Support

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

Troubleshooting


Problem

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

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Admin views and table functions","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF010","label":"HP-UX"}],"Version":"9.7","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21507250

Page Feedback