DB6CONV leaves inconsistent systables

[former_member640444]

Hello,

while running an online db6conv of table SOFFCONT1 we run into the problem that archiving per TSM tape-pool becomes full.

After resolving that now we have the following situation with a new db6conv for SOFFCONT1:

When we drop the table we get:

db2xxx> db2 "drop table SAPR3.SOFFCONT1AKqHtBs"

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "SAPR3.SOFFCONT1AKQHTBS" is an undefined name.  SQLSTATE=42704

When we select in sysibm.systables:

db2 "select name, type from sysibm.systables where name like 'SOFFCONT1AKqHtBs'"

we find 1 row - this means the table still is in systables!

We are able to see the table in DBA-Cockpit! DB6 Version 10.1FP3 64-Bit on AIX 6.1.

Therefore the table still exist in systables but not in database! What can we do?

Maybe problem with mixed lower and upper case in tablename?

DB6 Gurus please help!

Kind regards

[former_member301417 · Accepted solution]

Hi,

please check the status of the aborted AMT conversion of  table SOFFCONT1 first!

what is the result of

db2 "select value from systools.admin_move_table where key = 'STATUS' and tabname = 'SOFFCONT1'"

?

regards, frank

[former_member640444 · Accepted solution]

Hi Frank,

your select returns no output (0 records). But we have deleted the record there and we dropped the table "SOFFCONT1AKQHTBS" per SQL

manually without any errors.

Regards, Karlheinz

[former_member301417 · Accepted solution]

Hi,

I would like to perform some more checks before attempting to manually delete table

SOFFCONT1AKqHtBs. what is the result of:

db2 "select count(*) from systools.admin_move_table where tabname = 'SOFFCONT1'"

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

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

regards, frank

[former_member640444 · Accepted solution]

Hi,

the outputs are:

db2mup> db2 "select count(*) from systools.admin_move_table where tabname = 'SOFFCONT1'"

1
-----------
          0

  1 record(s) selected.

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

1               2                              CREATE_TIME                TYPE
--------------- ------------------------------ -------------------------- ----
SAPR3           QCM8SOFFCONT1                  2014-07-10-10.58.33.342875 T
SAPR3           SOFFCONT1                      2014-07-08-13.59.47.419617 T
SAPR3           SOFFCONT1AKqHtBs               2014-07-08-15.03.35.653735 T

  3 record(s) selected.

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

1               2                              CREATE_TIME
--------------- ------------------------------ --------------------------

  0 record(s) selected.

[former_member301417 · Accepted solution]

hi,

thank you!

so we really have no remnants of an ADMIN_MOVE_TABLE run anymore besides
table  SOFFCONT1AKqHtBs .

proceed as follows:

1. RESET the new/latest DB6CONV conversion for  SOFFCONT1 first.

2. check again with db2 "select substr(tabschema,1,15), substr(tabname,1,30), create_time, type from syscat.tables where tabname like '%SOFFCONT1%'"; table QCM8SOFFCONT1 should not exist anymore after the reset, only SOFFCONT1 and SOFFCONT1AKqHtBs should be listed. only if this is true, finally proceed to the next step.

3. drop table SOFFCONT1AKqHtBs using the command

db2 'drop table SAPR3."SOFFCONT1AKqHtBs" '

4. define and start a new DB6CONV conversion for SOFFCONT1 from scratch.

regards, frank

[former_member640444 · Accepted solution]

Hi Frank,

I've done as described.

There are only these entries:

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

1               2                              CREATE_TIME                TYPE
--------------- ------------------------------ -------------------------- ----
SAPR3           SOFFCONT1                      2014-07-08-13.59.47.419617 T
SAPR3           SOFFCONT1AKqHtBs               2014-07-08-15.03.35.653735 T

  2 record(s) selected.

Now I've dropped with the command:

db2xxx> db2 'drop table SAPR3."SOFFCONT1AKqHtBs" '
DB20000I  The SQL command completed successfully.

Wow, great! Problem solved!

I have to put "" around the tablename and only around that without schema -right?

Many thx

Karlheinz

[former_member301417 · Accepted solution]

you're welcome!

yes, if the table name contains special characters or letters in lower case, the table name has to be put in """ (otherwise the lower case letters are implicitely converted to upper case, for example) - and in that case the db2 command has to be written within two single quotes.

regards, frank