index table space still points to production tablespace after system refresh in refreshed system(quality system) for newly created tables.

[Former Member]

hi experts,

please assist on below issue

issue : index table space still points to production tablespace after system refresh in refreshed system(quality system) for newly created tables.

we have done homogeneous system copy and followed redirect backup/restore method for system copy

as per system copy guide we followed the steps below for DB2 specific activities:

1.renamed table spaces using below command

db2 rename tablespace <old name> to <new name>

2.renamed virtual table space names

db6util -rtvt <old tbs> <new tbs>

3.updated the tablespace names in tables TSDB6, IADB6, and TADB6

4.dropped tablespaces SYSTOOLSPACE, SYSTOOLTMPSPACE

5.created TABLESPACE statements for SYSTOOLSPACE and SYSTOOLSTMPSPACE

and executed statement on command line.

Thanks

srimann

[martin_mikala · Accepted solution]

Hi,
I don't do this TS renaming. I think this do more problems than benefits.

Problem is often with virtual tables. And better is materialize them.

I only recreate SYSTOOL... TS.

B.R: Martin

[Former Member · Accepted solution]

hi Martin,

we follwed the system copy guide provided by sap .they asked us to follow the steps based on our environment (OS-AIX,Database-DB2 UDB)

SAP Systems Based on the Application Server ABAP of SAP NetWeaver on UNIXValid for SAP NetWeaver 7.0 to 7.0 Including Enhancement Package 3

System Copy Guide

Document version 1.6-24-07-07

Thanks,

siva

[martin_mikala · Accepted solution]

Hi Siva,

Yes, I've understood, but I didn't find benefits for this rename.

Seems, that relation between data TS and index TS i in table sapsr3.TSDB6.

db2 describe table sapsr3.TSDB6

                                Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

TABSPACE                        SYSIBM    VARCHAR                     90     0 No

INDSPACE                        SYSIBM    VARCHAR                     90     0 No

You can check it, if TS name are OK here.

db2 "select * from sapsr3.TSDB6"

And of course, error message should be useful. 🙂

B.R.

Martin

[martin_mikala · Accepted solution]

Hi,

I'v used to this command to rename TS names in DB6 tables.

db2 "update sapsr3.tsdb6 set tabspace = 'QAS#'||substr(tabspace,5,length(tabspace)-4), indspace='QAS#'||substr(indspace,5,length(indspace)-4)"

Martin

[Former Member · Accepted solution]

thanks martin

the issue has been resolved after we ran above query(update <connect_user_name>.tsdb6 set tabspace = '<SAPSID_TARGET>#'||substr(tabspace,5,length(tabspace)-4),indspace='<SAPSID_TARGET>#'||substr(indspace,5,length(indspace)-4))

initially we only ran the query for tabspace not for indspace and it was causing the issue.

Thanks,

siva

[manumohandas82]

Hi Siva ,

Believe you will get an option to mark the correct answer ( your own fix )  and close the thread  .

I  dont know as i havent yet created a question

Thanks ,

Manu

[manumohandas82]

Thanks for the update Siva .Please close the thread .

You would have easily found this issue if you would have compared the TSDB6  and IADB6/TADB6 tables in se16

Thanks ,

Manu

[Former Member]

thanks again manu

may i know how to close thread?

[Former Member]

thanks a lot Martin and manu for prompt response on this issue

this issue has been resolved after we ran the query

update <connect_user_name>.tsdb6 set tabspace = '<SAPSID_TARGET>#'||substr(tabspace,5,length(tabspace)-4),indspace='<SAPSID_TARGET>#'||substr(indspace,5,length(indspace)-4)

initially we only ran the query(update <connect_user_name>.tsdb6 set tabspace = '<SAPSID_TARGET>#'||substr(tabspace,5,length(tabspace)-4)) for tabspace not for indspace and it was causing the issue.

Thanks,

siva

[manumohandas82]

Hi  ,

Is this issue fixed ?

Thanks ,

Manu

[Former Member]

hi manu,

this issue has been resolved after we ran the query

update <connect_user_name>.tsdb6 set tabspace = '<SAPSID_TARGET>#'||substr(tabspace,5,length(tabspace)-4),indspace='<SAPSID_TARGET>#'||substr(indspace,5,length(indspace)-4)

initially we only ran the query(update <connect_user_name>.tsdb6 set tabspace = '<SAPSID_TARGET>#'||substr(tabspace,5,length(tabspace)-4)) for tabspace not for indspace and it was causing the issue.

Thanks,

siva

[manumohandas82]

Hi Srimann ,

Would you mind sending the screenshots for IADB6 and TADB6

If you are in Kernel version above 7:20 use the following syntax ( 1227165 - DB6: RENAME TABLESPACE in virtual tables)

db6util -rtvt <SID_OLD>#% <SID_NEW>#%

Thanks ,

Manu

[Former Member]

please find attachment on this

[manumohandas82]

Hi Siva ,

As per the screenshot you have done the changes on the tablespace . But where is it that you are getting the following error is it in the db2diag ?

issue : index table space still points to production tablespace after system refresh in refreshed system(quality system) for newly created tables.

Did you rename the virtual tables as detailed in the previous post .

Can you identify the  the concerned index tablespace from the db2diag / SM21

Check whether all the table space is mapped correctly to their corresponding index spaces in TADB6 and IADB6

Thanks ,

Manu

[Former Member]

hi manu,

these issue happens only for newly created tables not for tables which are in database.

we are seeing some tables points prod index table spaces in tcodes ddstoarge and storage parameter section of SE14 tcode  which were newly created in sap while doing transport  .transport ended with error due to this issue.

we ran the command to renam virtual table space names

db6util -rtvt <old tbs> <new tbs>

it happens all index tablespaces

Thanks,

siva

[Former Member]

for more clarification we did below steps for db2 specific activities

1.renamed table spaces using below command

db2 rename tablespace <old name> to <new name>

2.renamed virtual table space names

db6util -rtvt <old tbs> <new tbs>

3.updated the tablespace names in tables TSDB6, IADB6, and TADB6

4.dropped tablespaces SYSTOOLSPACE, SYSTOOLTMPSPACE

5.created TABLESPACE statements for SYSTOOLSPACE and SYSTOOLSTMPSPACE

and executed statement on command line.

[martin_mikala]

Hi Siva,

Also you can think, if you need virtual tables. I've materialized all, this empty take only few MB. 1224865 - DB6: Materialization of virtual tables

Of course, you can materialize every time tables in QAS before renaming TS or how did me, in PROD only once. But I recommended test it 1st in SANDBOX.

B.R.

Martin

[Frank-Martin]

Hi,

renaming tablespace prefixes in virtual tables with db6util is pretty easy with a current patch level of db6util ( note 1227165 ).

db6util -rtvt <SID_OLD>#% <SID_NEW>#%

Materializing all virtual tables in an ERP system usuall requires several GB of space. One of the main advantages of virtual tables is that they do not contribute to the number of database objects in  a tablespace. DB2 only allows around 51000 objects per tablespace.

Regards

                Frank