SAP is the application which i am working with right now. So let me give an eagle view of DB2 on SAP.

SAP is one hell of a package, it is nice to work with and simplifies all the Commands that we issue in DB2 into simple Transaction codes. SAP has its own customized tablespaces, bufferpools, tables, which it will create during the Installation of the software. As we know db2 creates 3 tablespaces in the beginning to work for, one SYSCATSPACE, one USERSPACE1, and one TEMPSPACE. While installing SAP, it makes lot of changes in the DB2. Basically in SAP, for every Instance an Unique Systemidentifier will be given, which they call as SID. While installing, SAP creates an instance named as DB2<SID>, it creates users with sysadm and sysctrl privileges with userids db2sid and sidadm. SID can be anything that is specified by us, i.e for a production system we can give SID as PRD. In that case, the instance name would be db2prd and users db2prd and prdadm will be created. Also it will drop Userspace and tempspace and creates its own customized tablespaces. Based upon the installation you are performing (i.e whether you are installing r3 or B.W or ICH or APO e.t.c these are few names of the modules in SAP ) tablespaces with customized tables will be created. For eg:- in R3 system most of the data will be stored in PSAPBTABD tablespace, likewise for a B.W system the data will go into PSAPFACTD, PSAPODSD, PSAPDIMI tablespaces.

Tablespaces in a R3 system

TBSPACE
————————–
PSAPBTABD
PSAPBTABI
PSAPCLUD
PSAPCLUI
PSAPDDICD
PSAPDDICI
PSAPDIMD
PSAPDIMI
PSAPDOCUD
PSAPDOCUI
PSAPEL31GI
PSAPEL46BD
PSAPEL46BI
PSAPEL640D
PSAPES31GI
PSAPES46BD
PSAPES46BI
PSAPES640D
PSAPFACTD
PSAPFACTI
PSAPLOADD
PSAPLOADI
PSAPODSD
PSAPODSI
PSAPPOOLD
PSAPPOOLI
PSAPPROTD
PSAPPROTI
PSAPSOURCED
PSAPSOURCEI
PSAPSTABD
PSAPSTABI
PSAPTEMP
PSAPUSER1D
PSAPUSER1I
SYSCATSPACE
SYSTOOLSPACE
SYSTOOLSTMPSPACE

38 record(s) selected.

Also SAP will set the registry variables of DB2, in newer versions SAP WORKLOAD knob is available. That is registry variables DB2_WORKLOAD is set to SAP which automatically influences other registry variables to be set according to SAP.

Then there is the physical layout of the tablespaces and containers. Naming convention used by SAP to store the datafiles or containers is

/db2/SID/sapdataN in unix where N is an integer can be from 1 to 10

C:\db2\SID\sapdataN in windows where the drive ‘C’ is not mandatory, anydrive can be specified which has enough space.

As far logs are considered, SAP identifies the logs in the directory /db2/SID/log_dir/SID. So after installing we should create a symbolic link from the usual Log Directory i.e /db2/SID/db2SID/NODE0000/SQL00001/SQLOGDIR/ to the /db2/SID/log_dir/SID, so that all logs will be transferred to the new location and stored there from now on. So all active logs can be found at the path /db2/SID/log_dir/SID

Similarly folders /db2/SID/log_archive/SID and /db2/SID/log_retrieve/SID are used for the archiving and retrieving purposes. After the SAP is installed, the Database with name SID is created. And this database would be in Circular Logging Mode. One has to turn on Logretain On and Userexit On and take a backup to change it to Archival Logging mode. Then the Logs will start archiving to /db2/SID/log_archive/SID once they get offline. For that to happen SAP supplies a Customized Admin Tools which sets the environment variables in the .Profile file as follows :-

setenv DB2DB6_ARCHIVE_PATH /db2/DVL/log_archive # used for ARCHIVE
setenv DB2DB6_RETRIEVE_PATH /db2/DVL/log_retrieve # used for RETRIEVE

This Admin Tools has to be downloaded from SAP service marketplace and has to installed using the command sddb6ins..you can search for db2udb admintools in the service marketplace to find the procedure to install admin tools. while installing the admin tools we can specify to create a Admindatabase or not to create it. If we choose to create it, one more database named as ADMSID will be created in the local database directory.  you can see two databases if you list the database directory as below :-

System Database Directory

Number of entries in the directory = 3

Database 1 entry:

Database alias                            = PRD
Database name                          = PRD
Local database directory          = /db2/PRD
Database release level               = a.00
Comment                                     = SAP R3 database PRD
Directory entry type                   = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

Database 2 entry:

Database alias                              = ADMPRD
Database name                             = ADMPRD
Local database directory             = /db2/PRD
Database release level                  = a.00
Comment                                       =
Directory entry type                     = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =
And the Dump files are stored in /db2/SID/db2dump directory.

An example of physical layout of SAP on Unix system is given below :-

/dev/log_dirlv 9.00 7.75 14% 26 1% /db2/DVL/log_dir
/dev/trans_lv 27.50 26.25 5% 20603 1% /usr/sap/trans
/dev/lv_datavg1 15.00 4.00 74% 17 1% /db2/DVL/sapdata1
/dev/lv_datavg2 20.00 4.59 78% 20 1% /db2/DVL/sapdata2
/dev/lv_datavg3 19.00 3.94 80% 21 1% /db2/DVL/sapdata3
/dev/lv_datavg4 18.00 4.53 75% 18 1% /db2/DVL/sapdata4
/dev/lv_datavg5 27.50 10.92 61% 19 1% /db2/DVL/sapdata5
/dev/lv_sapdata6 14.00 2.01 86% 16 1% /db2/DVL/sapdata6
/dev/lv_sapdata7 13.00 3.95 70% 14 1% /db2/DVL/sapdata7
/dev/put_lv 4.00 1.07 74% 4365 2% /usr/sap/put
/dev/db2dump 15.00 14.90 1% 30 1% /db2/DVL/db2dump
/dev/log_retrieve 5.00 5.00 1% 5 1% /db2/DVL/log_retrieve
/dev/log_archive 26.00 14.23 46% 236 1% /db2/DVL/log_archive
/dev/sapmnt 5.00 4.02 20% 80279 8% /sapmnt
/dev/sapdatat 5.00 5.00 1% 8 1% /db2/DVL/sapdatat

So this is the basic setup that of DB2 on SAP.

signing off for now…

siva