Hello All,

Couple of weeks back I got a chance to performed DB2 version upgrade from 9.7 to 10.5.

Details of the environment are mentioned below.

Operating System:     AIX 6.1

SAP System:             BI 7.31.

Basis Release:          731 SP 08

Kernel Release:         720 SP 401

DB2 version:             9.7 fix pack 10

DBSL patch level:     323

To be upgraded to 10.5 fix pack 5

Let’s begin…

Pre-steps:

Below table gives us an overview of pre-requisites that should be met before we start with upgrade activity.

As per the above table the dbsl patch for the system is lower than required so, we had to upgrade the kernel and to avoid issues during starting and stopping of SAP we have to make sure SAP note 1734769 is also implemented too.

Now, the kernel version after upgrade is 721_EXT SP 623 and DBSL Patch level 600.

We are now all set to go ahead with upgrade activity.

Few activities mentioned below can be performed ONLINE (when the system/DB is up and running) so I have mentioned ONLINE in “( )” next to the steps below.

Installing DB (Online):

Here we are using VNC (GUI tool) to install DB2.

• Open VNC session and switch to root user in VNC session. Goto path where you have kept the installation files and start setup.

cd /DB2/DB2_LUW_10.5_FP5SAP2_RDBMS_AIX_HP_SO_/AIX_64/ESE/disk1

./db2setup

In case you get error message regarding to display variable, we have to set ‘DISPLAY’ environment variable to get screen.

To do that execute command ‘export DISPLAY=hostname:1.0’ as root user.

This will bring installation wizard window as indicated in below screenshot.

• Click on ‘Install a Product’ link on left pane and then scroll down to the end of section ‘DB2 Version 10.5 Fix Pack 5 Workgroups, Enterprise and Advanced Editions’.

        Click on ‘Install Now’ button placed at the end of section highlighted in above point.

• This will bring up ‘DB2 setup’ installation screen. Click on ‘Next’ to move further.

Select radio button to accept license agreement and click on ‘Next’.

• Select ‘Typical’ radio button and click on ‘Next’

• Click on ‘Install DB2 Server Edition on this Computer’ radio button and click on ‘Next’.

• By default installation directory would be different, ensure to change it to ‘/db2/db2<sid>/db2_software_v105’. Finally click on ‘Next’ button.

• If asked, enter user details as indicated in below screen.

• Select ‘Do not create a DB2 instance’ radio button and click on ‘Next.

• In case you get below mentioned screen, select ‘Do not set up your DB2 server to send notifications at this time’ option and click on Next button.

• Summary screen will appear which will contains details on all options we have selected so far. Verify if all our inputs were correct.

Click on ‘Finish’ button. This will start installation of binaries on mentioned path.

• This will create folder which we have provided in installation steps.i.e. /db2/db2<sid>/db2_software_v105

Additional details on installation logs can be found under /tmp folder. File of our interest would be ‘db2setup.log’.

Continue to monitor installation phase.

• Below is confirmation screen for binary installation. Goto Log file tab and check we had occurred any errors during installation. Same can be found in /tmp/db2setup.log file.

Client update (Online):

This step can be performed online, however it is recommended to execute this as offline activity.

• Switch to <sid>adm user.

Determining the DB2 Client Type Used by the System.

For this use command R3trans –x or R3trans –d and check trans.log file generated for below entry.

This indicates path at which CLI drivers are installed. In case path is similar to ‘../SYS/global/db6/….’ then we can follow steps mentioned in this blog. If not please check standard documentation on client update steps.

• Backup db6 directory from path obtained i.e. /usr/sap/SID/SYS/global/

Go to installation path and execute client update script as indicated in below screenshot.

cd /DB2/DB2_LUW_10_5_CLIENT_FP5SAP2/CLIENT

./db6_update_client.sh –u

Backup current configuration (Online)

This step can be performed online, however it is recommended to execute this as offline activity.

Switch to db2<sid> user

• Backup db and dbm information.

Create directory cfg_backup under /db2/db2<sid>.

Execute below commands to backup database and dbm configuration related information.

mkdir /db2/db2<sid>/cfg_backup

cd /db2/db2<sid>/cfg_backup

env > env_before_upg_105.txt

db2set -all > reg_before_upg_105.txt

db2 get dbm cfg > dbm_before_upg_105.txt

db2 get db cfg for <DBSID> > db_before_upg_105.txt

• Backup sqllib.

Goto folder /db2/db2<sid> and backup sqllib folder using ‘cp –hrp’ copy command. In case you get some errors while copying some file as a result of permissions, switch to root user to take backup.

cd /db2/db2<sid>

cp -hrp sqllib sqllib_v97_old

• Database backup

Check database backup. SAP recommends to have offline DB backup before we start instance upgrade. For production, we will request for flash backup during MW. For other systems, ensure we have raised SRs beforehand requesting additional online backup such that it finishes before our activity.

• Other information

Take screenshots of db2level, db2licm –l, dbacockpit (version information), System Status screen etc.

DB2 Upgrade verification (Downtime)

• Stop all application servers (including ERS, hacs etc.)

Ensure sapstartsrv services are also stopped for each instance.

Kill ITM database monitoring agent. Check process and kill it with option 9 with root user.

ps -aef|grep kuddb2 | grep -i sid

kill -9 <PID>

• Switch to user db2<sid> and stop database using command ‘db2stop force’.
• Start database using command ‘db2start’.
• Execute below command to start Upgrade Verification phase.

cd /db2/db2<sid>

/db2/db2<sid>/db2_software_v105/instance/db2ckupgrade -e -l upgrade.log

We can also monitor verification command by checking upgrade.log file under /db2/db2<sid> folder.

Output of the command should be similar to above screen where it indicates verification utility completed successfully and database can be upgraded.

In case of errors we have to troubleshoot them and take appropriate actions.

In any case we will still execute command to verify if we have any invalid objects in DB to avoid any potential issues during DB upgrade.

As a first step execute command as db2<sid>

db2 “CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)“.

Ideally it should end with Return Status as 0. If not we will need to investigate further by looking at error logs.

As second step we will check in table SYSCAT.INVALIDOBJECTS to see if any invalid objects are reported. We can use below SQL.

db2 “SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA,SUBSTR(OBJECTNAME,1,30) OBJECTNAME, SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, OBJECTTYPE, INVALIDATE_TIME, LAST_REGEN_TIME FROM SYSCAT.INVALIDOBJECTS”

In case output from above command is not empty follow steps mentioned in section – ‘’.

• Further also check on schema owner for objects being reported as invalid. In above screenshot we have objectype as F which is routine in DB2 so information can be derived from table syscat.routines. For this we can use below SQL.

db2 “select substr(OWNER, 1, 15) OWNER, substr(ROUTINESCHEMA,1,30) ROUTINESCHEMA, substr(ROUTINENAME, 1, 30) ROUTINENAME, ROUTINETYPE, CREATE_TIME from syscat.routines where ROUTINENAME='<routine_name_obtained_in_above_SQL'”

E.g.

db2 “select substr(OWNER, 1, 15) OWNER, substr(ROUTINESCHEMA,1,30) ROUTINESCHEMA, substr(ROUTINENAME, 1, 30) ROUTINENAME, ROUTINETYPE from syscat.routines where ROUTINENAME=’EXPLAIN_GET_MSGS'”

• Once all errors are addressed execute command as db2<sid> again.

db2 “CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)“

• Verify no additional invalid object is returned using SQLs mentioned in point

db2 “SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA,SUBSTR(OBJECTNAME,1,30) OBJECTNAME, SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, OBJECTTYPE, INVALIDATE_TIME, LAST_REGEN_TIME FROM SYSCAT.INVALIDOBJECTS”

Database instance upgrade (Downtime)

Switch to user root

• Edit files .cshrc and .profile for <sid>adm and db2<sid> users to comment LIBPATH environment variable.

These files are placed under /home/<sid>adm and /db2/db2<sid> folders respectively.

In case files are not there, we can ignore this step. Ensure to backup these files before making any changes.

• Switch to user db2<sid> and stop database using command ‘db2stop force’

Follow this by command ‘db2 terminate’

Switch to root user and execute below command to perform instance upgrade.

cd /tmp

/db2/db2<sid>/db2_software_v105/instance/db2iupgrade -u db2<sid> db2<sid>

Database upgrade (Downtime)

Switch to user db2<sid>

• Start database using command ‘db2start’.

You would notice that license applied in Trail (can be verified with command db2licm –l) and we have to apply correct license so that we do not face issues in future. We will perform this after database upgrade.

• To upgrade database execute command

db2 upgrade database SID’ as db2<sid>

On successful upgrade, we will get confirmation as indicated in above screenshot.

License installation (Downtime)

After upgrade we will apply permanent license.

db2licm -a <path>/db2aese_c.lic

Execute db update script  (Downtime)

• Update sapdb scripts as mentioned in note # 1734769 – DB6: Error in Scripts startdb and startj2eedb

Switch to user <sid>adm

Copy files as <sid>adm startdbdb6 startj2eedbdb6 downloaded from sap note 1734769 to all Kernel directories (Central and Local).

• Execute db6_update_db script as per sap note # 1365982.

Switch to db2<sid>

Create directory ‘update_<SID>’,

e.g. mkdir update_SID

cd update_SID

Execute db update script from the above directory just created using command

This is similar step which we follow during FP upgrade. Ensure to select N (NO) in case script asks to reset any parameter.

../db6_update_db.sh -m -d <SID>

On successful execution it will create a SQL file and log file as mentioned in the output in above screen-shot.

• Execute command as indicated in output of above script.

db2 -z db6_update_db_out.log -tvf db6_update_db_out.sql

Verify output file db6_update_db_out.log generated under execution directory (mostly update_<SID>) and see we have no errors. In case of errors we need to troubleshoot them.

• Re-execute SQL to check if we have any invalid object. In case we get any entry try revalidating it using SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS routine and execute SQL to check if we still have invalid object. If yes, check ‘Addressing INVALIDOBJECTS’

db2 “SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA,SUBSTR(OBJECTNAME,1,30) OBJECTNAME, SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, OBJECTTYPE, INVALIDATE_TIME, LAST_REGEN_TIME FROM SYSCAT.INVALIDOBJECTS”

Re-execute revalidation routine for corrected object only –  e.g. for ‘DBH_WLM_STATS_COLLECT’ object –

db2 “CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, ‘SAPTOOLS’, ‘DBH_WLM_STATS_COLLECT’)”

In case we have corrected errors reported we need to bind objects again. For this use command

db2rbind SID -l db2rbind.log all  —–> if no corrections were made then we can skip this point.

Backup of parameters after upgrade (Downtime)

Switch to user db2<sid>

Execute below command to backup information.

cd /db2/db2sid/cfg_backup   —> We created this directory to backup parameters before upgrade

env > env_after_mig_105.txt

db2set -all > reg_after_mig_105.txt

db2 get dbm cfg > dbm_after_mig_105.txt

db2 get db cfg for SID > db_after_mig_105.txt

Update LOGFILSIZE and LOGBUFSZ parameters (Downtime)

Compare the settings for the database and database manager configuration parameters in your upgraded database with the values suggested in SAP Note # 1851832, which always contains the most up-to-date proposals for these parameters from SAP.

In particular, pay attention to the LOGFILSIZ and LOGBUFSZ database parameters:

In DB2 10.1, the size of the log record header has been increased. Recommendation is we increase the value of the database configuration parameters LOGFILSIZ and LOGBUFSZ by 10 to 15 percent if you upgraded from DB2 9.7

Check LOGFILSIZ and LOGBUFSZ values and increase them by 15% and restart database.

db2 get db cfg for <SID>|grep -i LOGFILSIZ

db2 get db cfg for <SID>|grep -i LOGBUFSZ

db2 update db cfg for <SID> using LOGFILSIZ <new_Value>

db2 update db cfg for <SID> using LOGBUFSZ <new_value>

Post steps (Online)

• Start database and start SAP system.
• Perform health-check on SAP system.
• Ensure correct FP information is reflecting in SYSTEM->Status, dbacockpit and workprocess trace file.
• Monitor db2diag.log for some time

I ‘ll soon sum up all the issues I faced during upgrade and their solutions in another blog.

That’s all folks!!!

Thank you reading, and yes your comments are always welcomed.

Regards

Prithviraj.