Stuff related to SAP on DB2 :
Delete SAP* on DB2 or Unlock SAP* or any other user:
You found incorrect values in field UFLAG (User Lock Status), table USR02. SAP Knowledge Base Article - Preview 1887820 - Incorrect User Lock Status (UFLAG) in table USR02. You found incorrect values in field UFLAG (User Lock Status), table USR02. SAP Knowledge Base Article - Preview 1887820 - Incorrect User Lock Status (UFLAG) in table USR02. Stuff related to SAP on DB2: Delete SAP. on DB2 or Unlock SAP. or any other user: Login as db2adm or sidadm to command prompt: db2 delete dbschemaname.usr02 where mandt='000' and bname='SAP.' Unlock SAP. from CLI: db2 = update dbschemaname.usr02 set uflag=0 where mandt='000' and bname='SAP.' DB2 useful commands for SAP Basis.
Login as db2adm or sidadm to command prompt:
db2 delete dbschemaname.usr02 where mandt=’000′ and bname=’SAP*’
Unlock SAP* from CLI :
db2 => update dbschemaname.usr02 set uflag=0 where mandt=’000′ and bname=’SAP*’
DB2 useful commands for SAP Basis:
db2 list application – list active process whenever you are going to stop sap/db
db2diag -f | tail – Check DB2 logs below is another command when you know the time error occurred –
db2diag -l Error -time 2010-05-01
db2 => get dbm cfg – collect DB2 config some time SAP asked to collect this info and send them to see if any tune up require
Sap Uflag 8
SAP on DB2 Backup / Restore Commands:
DB2 online backup command on disk:
db2 backup db PRD online to /path
See backup progress using below command:
db2 list utilities show detail
Offline db2 backup command:
backup db dev to /OFFLINE_BKP/MAR
See backup progress using below command:
db2 list utilities show detail
Restoring whole SAP on DB2:
I was doing some patch upgrade (ABAP/BASIS etc.,) of my ECC system when suddenly some serious DDIC dumps occurred and we raised message with SAP and finally SAP recommended to restore whole system there was no other way.. here;s what I did –
1) Offline backup taken before starting patches
a) login to CMD with db2sid
b) stopsap
c) db2start
d) backup db DEV to /OFFLINE_BKP/SEPT/W2
2) Patches stuck SAP asked to restore whole system 😦
3) I checked for last offline backup and found one above
a) login to CMD with db2sid
Sap Uflag 128
b) stopsap
c) db2stop
d) db2start
e) restore db DEV from /OFFLINE_BKP/SEPT/W2
You will see some warning message SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database file will be deleted.
Do you want to continue ? (y/n) : y
Monitor the backup progress by executing below command:
db2 list utilities show detail
After DB restore I started SAP and logged in successfully the system was in state before I have started patches, after applying SAP recommendation I started again patching of SAP system successfully.
Useful DB2 commands for SAP Basis:
DB2 System Commands:
DB2LEVEL : checks version of DB2 installed
DB2ILIST : lists all instances installed
DB2CMD : opens a command line processor
DB2CC : opens db2 control center
DB2LICM -l :gets db2 type.
DB2 LIST NODE DIRECTORY :Lists all nodes
DB2 LIST DATABASE DIRECTORY :list databases
DB2 GET DB CFG FOR SID :get configuration info for the SAMPLE db
DB2 CONNECT TO SID USER myuser USING mypass :connect to db
DB2 DISCONNECT SID :disconnects
DB2 LIST APPLICATIONS SHOW DETAIL :shows all running db’s
DB2 GET DBM CFG :view paramaters
Uflag Sap
DB2 GET DATABASE CONFIGURATION :gets current database configuration
DB2 VALUES CURRENT USER :gets the current user
DB2 VALUES CURRENT SCHEMA :gets the current schema
DB2 VALUES CURRENT QUERY OPTIMIZATION :get query optimization level.
Commands for Schema:
Sap Uflag 224
DB2 SELECT SCHEMANAME FROM SYSCAT.SCHEMATA :list all schema
DB2 VALUES CURRENT SCHEMA :gets the current schema
Commands for Tables:
DB2 LIST TABLES FOR schema_name :list all tables for particular schema
DB2 LIST TABLES SHOW DETAIL; :show detail about tables
Commands for Tablespaces:
Sap Usr02 Uflag
DB2 LIST TABLESPACES SHOW DETAIL :show detail about table spaces
SELECT * FROM SYSCAT.TABLESPACES; :show what syscat has about tablespaces
SELECT tbspace, bufferpoolid from syscat.tablespaces; :get tablespace and bufferpoolid
SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACE=2; :Check what TABLES are in tablespace where id = 2.