Skip to Content

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 .  DB2 schema.png

Comments and suggestions are most welcome !

Thanks

Rishi

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Aditya Sehrawat

    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

    (0) 
    1. Rishi Das Post author

      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

      (0) 
      1. parthiban jayaraman

        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

        (0) 
      1. wenzhuo li

        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.

        (0) 
        1. Frank-Martin Haas

          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

          (0) 
          1. Marcin Ozdzinski

            +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.

            (0) 
            1. wenzhuo li

              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”

              (0) 
              1. Marcin Ozdzinski

                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)

                (0) 

Leave a Reply