DB2 Error while moving table to new tablespace
DB2 10.5 on Windows
I am moving some tables to a new tablespace and when I try I get an error. The command I am using is:
The error I get is:
Error 8/24/2017 10:09:42 AM 0:00:01.515 <link> - DB2 Database Error: ERROR [42603] [IBM][DB2/NT64] SQL0010N The string constant beginning with "'RUNSTATS ON TABLE ON ALL COLU " does not have an ending string delimiter.
Any clues? I have done a goolge search of course and have not found anything yet. Thank you!
Jim
I am moving some tables to a new tablespace and when I try I get an error. The command I am using is:
CALL SYSPROC.ADMIN_MOVE_TABLE( 'Schema1', 'TableName', 'PRD_DATA','PRD_INDX','PRD_LOB','', '', '', '','','MOVE');The error I get is:
Error 8/24/2017 10:09:42 AM 0:00:01.515 <link> - DB2 Database Error: ERROR [42603] [IBM][DB2/NT64] SQL0010N The string constant beginning with "'RUNSTATS ON TABLE ON ALL COLU " does not have an ending string delimiter.
Any clues? I have done a goolge search of course and have not found anything yet. Thank you!
Jim
ASKER
It is fun yet frustrating to learn a new system. Thank you for all your awesome answers, I will try your suggestion and let you know if it works. Thanks again!
Jim
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Jim,
The trailing space on the schema name is OK. The double quoted name is treated like a VARCHAR and the trailing spaces are trimmed.
SELECT count(*) FROM "SYSIBM "."TABLES"
That works just fine.
Since you can't have 2 tables with the same name in a single schema, DB2 creates the shadow table with an invented name. After the original table is deleted, the shadow table is renamed to the name of the original table.
You can do this yourself. But there are a number of things to keep in mind.
- If you do this manually you probably can't move the changes that occur to the original table while the switch process is running.
- Internally, DB2 (and most every other database engine) references objects by their ID (primary key) value, not their name. Everything in the system (other than source SQL) that referenced the original table or anything in it, becomes invalid since it references the original table ID, not the new one. That's why you have to rebuild the indexes, rerun statistics, etc.
- Expounding on the statement above, any referential integrity that uses the original table as the child or parent becomes invalid. You'll have to recreate the RI manually.
- Views, triggers, and stored procedures that referenced the original table will all need to be checked, recreated, or recompiled.
It may not be that big a task. You just need to know what lies ahead....
Kent
The trailing space on the schema name is OK. The double quoted name is treated like a VARCHAR and the trailing spaces are trimmed.
SELECT count(*) FROM "SYSIBM "."TABLES"
That works just fine.
Since you can't have 2 tables with the same name in a single schema, DB2 creates the shadow table with an invented name. After the original table is deleted, the shadow table is renamed to the name of the original table.
You can do this yourself. But there are a number of things to keep in mind.
- If you do this manually you probably can't move the changes that occur to the original table while the switch process is running.
- Internally, DB2 (and most every other database engine) references objects by their ID (primary key) value, not their name. Everything in the system (other than source SQL) that referenced the original table or anything in it, becomes invalid since it references the original table ID, not the new one. That's why you have to rebuild the indexes, rerun statistics, etc.
- Expounding on the statement above, any referential integrity that uses the original table as the child or parent becomes invalid. You'll have to recreate the RI manually.
- Views, triggers, and stored procedures that referenced the original table will all need to be checked, recreated, or recompiled.
It may not be that big a task. You just need to know what lies ahead....
Kent
ASKER
OK, I found the issue. Not sure exactly what caused the error but I ran
RUNSTATS ON TABLE SCHEMA.TABLE UNSET PROFILE
and that seems to have fixed it. I was able to move the table to a new tablespace after that.
Thanks all!
Jim
RUNSTATS ON TABLE SCHEMA.TABLE UNSET PROFILE
and that seems to have fixed it. I was able to move the table to a new tablespace after that.
Thanks all!
Jim
"UNSET PROFILE"
That's an option that I never paid any attention to, and never used. Glad that you found it!
Kent
That's an option that I never paid any attention to, and never used. Glad that you found it!
Kent
ASKER
Found the answer.
I've never used the utility so I'm flying blind here. So let me start with some basic understanding...
As I understand things, that call will move Schema1.TableName to the PRD_DATA tablespace, with the indexes and LOBs moving to PRD_INDX and PRD_LOB.
There seems to be some changes to the procedure between DB2 versions 9 and 10, but this usage looks to be the same. The 11 parameter version moves a table. (DB2 allows overloading of function/procedure names by varying the parameters/returned values, much like C++, Java, and other languages do.)
I don't have a 10.7 version close by where I can test this. If I did, I'd start by trying to move just the table (data) and leave the indexes and LOBs where they are. If that's successful, I'd repeat the test including the indexes. Do you have LOBs in the table that you need to deal with?
You certainly do keep finding interesting things to talk about. It's a shame that they are usually at the expense of your peace and quiet....
Kent