I have often faced issues with the DB2 schema user after a PROD copy to the QA system using the redirected restore approach . many times the R3trans -D will not working and cause headache for Basis Administrators.
This did not happen in earlier versions as the sap schema owner was by default : SAPR3 ( same in QA and PROD )
Assuming that my PROD system is PRD , the schema owner for the objects in production will be SAPPRD.
And assuming my Quality system is QAT , the schema owner for the objects in quality will be SAPQAT.
The issues happen after a standard redirect restore because the schema owner of the objects change to SAPPRD in the QA system post refresh .
1. As DB2 uses authorization from the OS level users , hence the user SAPPRD will need to be created in the QA Host .
2. Using the password for the SAPPRD user recently created , you should be able to connect to QAT DB2 system.
db2 connect to QAT user SAPPRD using <Passwd>
3. Once this done , we can go ahead with the next step . It involves creation of the password entry for the user recently created in the Database. Before this – as per the standard refresh procedure , we need to give DBADM authorization to QATADM user on the database .
As QATADM user , perform these steps :
dscdb6up -create sapprd <Passwd>
db2 connect to QAT user sapprd using <Passwd>
dscdb6up sapprd <Passwd>
Please see the below screenshot for the actual output and execution of this critical step . 
Comments and suggestions are most welcome !
Thanks
Rishi
Hi Rishi,
Find below the script as post db refresh step to change the schema ownership.
1) create the db2prd user on target system (QAT in here)
2) Reset the password for this new db2prd user on target box.
3) Shutdown QAT, leaving db2qat up/running.
4) Login to target box, with newly created unix user db2prd
5) Put db2 commands in a file "db2_grant_commands.clp ", using vi editor, given below:
--******************************************************************************
--* Grant DBADM to sidadm and the schema owner (usually sap<sid>).
--******************************************************************************
connect to QAT user db2prd ;
select substr(grantor,1,15) as grantor,
substr(grantee,1,15) as grantee,
granteetype as gtype,
dbadmauth as dbadm,
createtabauth as createtab,
bindaddauth as bindadd,
connectauth as connect,
nofenceauth as nofence,
implschemaauth as implschema,
loadauth as load,
externalroutineauth as xrtn,
quiesceconnectauth as qconn,
libraryadmauth as libadm,
securityadmauth as secadm
from syscat.dbauth
order by 1, 2;
grant dbadm on database to user db2qat ;
grant dbadm on database to user sapqat ;
grant dbadm on database to user sapprd ;
grant dbadm on database to user qatadm ;
grant dbadm on database to group dbqatadm ;
grant secadm on database to user db2qat ;
grant secadm on database to user qatadm ;
grant secadm on database to user sapr3 ;
grant secadm on database to group dbqatadm ;
select substr(grantor,1,15) as grantor,
substr(grantee,1,15) as grantee,
granteetype as gtype,
dbadmauth as dbadm,
createtabauth as createtab,
bindaddauth as bindadd,
connectauth as connect,
nofenceauth as nofence,
implschemaauth as implschema,
loadauth as load,
externalroutineauth as xrtn,
quiesceconnectauth as qconn,
libraryadmauth as libadm,
securityadmauth as secadm
from syscat.dbauth
order by 1, 2;
connect reset ;
6) Save the Unix file and run it on by issuing command “db2 –tvnf db2_grant_commands.clp –z db2_grant_commands.log”
7) Restart db2 for QAT and then SAP for QAT
This should help resoving your problem,Next time if you plan for db refresh, perform steps 3 to 7 from above.
Regards,
Aditya Sehrawat
Hi Aditya
Thanks for the steps. I wanted to share my experience with this schema renaming thing after the DB2 Post refresh and hence put it up in a blog..
Anyways.. thanks
Rishi
Hi Rishi,
My SAP system has been refreshed from PRD to QAT.
Currently the QAT system , DBid is PRD and sid is QAT.
DBschema name is SAPPRD and need change to SAPQAT as dbschema, because its affecting during java export.
please give me a suggestion.
Thanks,
Parthi
Hi Rishi,
I am a novice DB2,can you tell me how to copy prd system to qas .
Simplest approach is to use SPM and make homogenous system copy to same SID and then rename copy into qas SID
Thanks for your response. we want to use backup and restore ways to refresh our DB.
Our PROD system is PRD , the schema is SAPPRD, Quality system is QAS , the schema is SAPQAS.
After system copy,we also holp our Quality system SAP sid is QAS and DB sid is QAS.
my main steps as below:
PRD:
db2 backup db prd to /db2/db2backup compress
db2 restore database PRD from /db2/db2backup/ taken at 20150202220445 into QAS redirect generate script qas.clp without rolling forward
QAS:
db2 -tvf /db2/qas.clp
but it not work,our sap system cann't start.can you show me detail steps.
Backup und redirected restore are not all steps that need to be performed during a homogenous system copy.
If you use the SWPM tool for this task it will take care of all additional steps like for example:
- setting of dbs_db6_user and dbs_db6_schema environment variables
- since the new connect user may differ from the original database the ownership of all SAP database objects needs to be transfered to the new user using TRANSFER OWNERSHIP commands
and some more ...
Therefore I strongly recommend to use the SAP tools.
Regards
Frank
+1 🙂
So whole process takes 3 Software Provisioning Manager steps:
1. Delete whole QAS instance - with dropping DB and removing user accounts.
2. Homogenous system copy of PRD into QAS host - with db restore from PRD backup
3. System rename from PRD sid into QAS sid
i've made it more less 10 times last year - every SPM release - less problems to manual override 🙂
Be aware that PRD schema will not change after that.
my main steps as below:
PRD:
db2 backup db prd to /db2/db2backup compress
db2 restore database PRD from /db2/db2backup/ taken at 20150202220445 into QAS redirect generate script qas.clp without rolling forward
QAS:
db2 -tvf /db2/qas.clp
now,i have been restored DB,can you tell me some detail of the step "3. System rename from PRD sid into QAS sid"
You are mixing two ways to restore PRD copy:
- you are trying to perform classic DB2 redirect restore - so google or read SAP note for all steps to perform
- i was suggesting to use SPM tool from https://service.sap.com/sltoolset
perform uninstall , homogenous system copy to same SID then system rename - all needed steps are described in SAP documentation located above and in https://service.sap.com/instguides
one thing you need to perform manually AFTER succesfull homogenous copy - materialize all virtual tables from DB02 - otherwise it will take ages to perform it during system rename (hint for SPM developers - add this action as option in tool)