DB6CONV aborts: SQL0601N - identical objects

[marie_renneke]

Hi experts,

we have a problem with an online conversion via DB6CONV. We use this tool to "reorganise" all tablespaces of a system to gain freespace after a massive data deletion. Therefore we renamend the "old" tablespaces, created new ones and used the online conversion to move the content of the "old" tablespaces into the new ones. We're using DB2 9.7 FP 6. Unfortunately we can't just use the "reduce high water mark" function of DB 9.7 because the tablespaces weren't natively created with 9.7

The conversion was processed with the options online conversion, copy with indexes, delete source table after conversion, update statistics after conversion, compression for data and indexes=yes. We started the Job with only one process because we already faced problems with parallelism in other systems.

We successfully converted several tablespaces by now in the exact same way in this system - but with the last one we got stuck. It had trouble converting some tables - seems like it somehow got confused during the conversion. Most aborted table conversions could be reprocessed through "continue". But two of them are still left. We tried several times just reprocessing them with "continue" - didn't work. At last we tried resetting them (which worked) and planned a new conversion but the same error occured (example): SQL0601N  The name of the object to be created is identical to the existing name "SAPWIL.BUT_TODO_PREMOAP4fIfs" of type "TABLE".  SQLSTATE=42710

The source table (e.g. BUT_TODO_PREMO) in the old tablespace as well as the table in the new tablespace with this temporary name (BUT_TODO_PREMOAP4fIfs) are still both existing. I'd really like to just go ahead and rename/delete the new table to start a totally new conversion but unfortunately I can't because it's not existing as a dictionary or database object. Though you can see it via DBACOCKPIT.

Do you have any idea how to proceed in this case?

Because I'm running out of ideas now...

Thanks in advance and best wishes
Marie

[maria_shen · Accepted solution]

Hello Marie,

Is it possible to attach the complete DB6CONV log file?

There seems some staging table left in previous conversion.

Please also save the output of below commands, and save it in a file and attach here.

db2 "select substr(tabschema,1,15), substr(tabname,1,30), create_time, type from syscat.tables where tabname like '%BUT_TODO_PREMO%'"

db2 "select substr(trigschema,1,15), substr(trigname,1,30), create_time from syscat.triggers where trigname like '%BUT_TODO_PREMO%'"

db2 "select key, value from systools.admin_move_table where tabname = 'BUT_TODO_PREMO'"

It will be great if all results can be attached as a file, instead of direct paste.
Then we can take further check.

Thanks and kind regards
Maria

[marie_renneke · Accepted solution]

Hello Maria,

first of all thanks a lot for your response.

Attached you can find the requested logs and results of the select statements.

There's one log of the conversion of BUT_TODO_PREMO from the time before we reset the conversion (...BeforeReset.txt). After that we'd planned a conversion of the single table BUT_TODO_PREMO (...AfterReset.txt).

The table conversion of BUT_TODO_PREMO is currently in the state "ABORTED" so the temporary table QCM8BUT_TODO_PREMO still exists. I know it would be deleted when restting the conversion.

In the logs before reset you'll find an error that says there hadn't been sufficient storage space which was the initial reason for aborting. Weird thing is, that there has never been any storage bottleneck in this database so we guessed the conversion just got confused somehow (which sometimes seems to happen from our experience).

Then we repeated the conversion. From this point on you can see that the conversion aborted without prompting any real error. We waited some time and tried to analyse the problem. Some days ago we just tried it again and lots of "ABORTED" tables could suddenly be processed without any problem. Only two tables are left now - getting the mentioned SQL0601N error.

The table named BUT_TODO_PREMOAP4fIfs is left from the first conversion trials - you can see it if you compare the creation timestamp with the first log entries.

Is it possible (or advisable) to just delete/rename the entry in syscat.tables? Or are there any further relations to these entries?

Thanks & best regards

Marie

DB6CONV_Log_BUT_TODO_PREMO_AfterReset.txt.zip

DB6CONV_Log_BUT_TODO_PREMO_BeforeReset.txt.zip

DB6CONV_SelectResultsFor_BUT_TODO_PREMO.txt.zip

[maria_shen · Accepted solution]

Hello Marie,

Thank you for your update and attachment.

Firstly, it is not possible to delete the entry in syscat.tables.
Currently both staging table and QCM table exist. But unfortunately there seems no information in the protocol table.

The log file shows the cancel option didn't work here. The possible way to overcome current situation is to try as follows:
- db2 'drop table SAPWIL."BUT_TODO_PREMOAP4fIfs"'
- db2 "drop table SAPWIL.QCM8BUT_TODO_PREMO"

Thanks and kind regards
Maria

[marie_renneke · Accepted solution]

Hello Maria,

you're brilliant - that solved our problem!

These are the steps I've just performed:

• reset the aborted conversion --> table QCM8BUT_TODO_PREMO disappeared
• drop table BUT_TODO_PREMOAP4fIfs (successfully)
• create and start a new conversion for BUT_TODO_PREMO
  • status PAUSED after some seconds
  • press continue and conversion goes on
  • conversion finished
  • control of syscat.tables shows that BUT_TODO_PREMO is now part of the new tablespace

I've also reprocessed the other table the same way and everthing looks fine now!

Never thought about just dropping the table because it didn't seem to exist... (select etc. didn't work).

Many many thanks for your help! Now we can go on with further conversions.

Have a nice week

Best wishes

Marie

[maria_shen · Accepted solution]

Hello Marie,

Glad to see the problem got solved.

Here are some points that may help in the future.

- Never do any manual actions beyond DB6CONV unless you get suggestions from SAP. This is to avoid unexpected result which may make the problem extremely complicated.

- Regularly check with note#1513862 for any update, and always use the latest version of DB6CONV. I believe the pdf guide attached to the note is already known to you.

1513862 - DB6: Table conversion using DB6CONV Version 5.01 or higher

Success with the conversions.

Thanks and kind regards
Maria