Home

DB2 with WebSphere Commerce v7

 

+

Search Tips   |   Advanced Search


Contents

  1. Overview
  2. Catalog
  3. Configuration
  4. Dynacache
  5. AD Deliv
  6. AD Stage
  7. ST Delivery
  8. ST Stage
  9. QA Live
  10. QA Stage
  11. AT Live
  12. PR Preview
  13. PR Stage
  14. PR Live
  15. Settings
  16. Database changes
  17. Post DB restore steps


See also

  1. Common DB2 commands
  2. DB purge scripts


Overview

All new database activity requests on WCS7 must be accompanied with an ECR request approval from System Engineer or Arvind before executing any HPSM Ticket for WCS7 related activity

In all environments: AD, AT, ST, QT, etc:

D**012SA = LIVE
D**014SA = STAGE
** = environment

There are some exceptions, where we have multiple environments at the same level, like AD1, AD2, AD3...

DA1012SA = LIVE DA1014SA = STAGE

Currently, reorg is scheduled every Sunday at 5:30 AM EST, runstat is scheduled Mon-Sat at 5:30 AM EST, and rebind every day at 5:00 PM EST. See Abhishek Acharya for more info.


Catalog

A script has been written to automatically generate the required SQL for cataloging databases by parsing dbnames, hosts, and ports from WAS extracts. If you ever want to run, log on to s1np1a and execute...

cd $DOC_ROOT/reports
./mkcatalog.sh

Here is output that is currently generated...

db2 catalog tcpip node da1014sa remote s1ad6b.myco.com server 50141
db2 catalog db da1014sa as da1014sa at node da1014sa
db2 catalog tcpip node dqa014sa remote s1qa4b.myco.com server 50140
db2 catalog db dqa014sa as dqa014sa at node dqa014sa
db2 catalog tcpip node dqa012sa remote s1qa8b.myco.com server 50120
db2 catalog db dqa012sa as dqa012sa at node dqa012sa
db2 catalog tcpip node dqa012sa remote s1qa8b.myco.com server 50120
db2 catalog db dqa012sa as dqa012sa at node dqa012sa
db2 catalog tcpip node ds1012sa remote s1st5b.myco.com server 50121
db2 catalog db ds1012sa as ds1012sa at node ds1012sa
db2 catalog tcpip node DS1014SA remote s1st6b.myco.com server 50141
db2 catalog db DS1014SA as DS1014SA at node DS1014SA
db2 list node directory


Version

$ db2level
DB21085I Instance "db2con01" uses "64" bits and DB2 code release "SQL09073" with level identifier "08040107".
Informational tokens are "DB2 v9.7.0.3", "special_25384", "IP23092_25384", and Fix Pack "3".
Product is installed at "/opt/db2/db2_97_03".


AD Deliv - DB2

database db001sa
Port 50180
50121
Server s1ad7b
DB2 user name wasadmin
DB user password foo
Instance db2con01
poc
a1sa12
Database user group wasgrp01
DB2 user home /home/wasadmin
DB2 admin name db2con01
pocsa12
DB2 admin password poctest01
DB2 admin home /opt/db/db2/instance/db2con01
DB2 HOME /opt/db2/db2_97_03
Schema wasadmin

For example...

db2con01@s1ad7a: /opt/db/db2/instance/db2con01> db2 connect to db001sa user pocsa12
Enter current password for pocsa12:

Database Connection Information

Database server = DB2/AIX64 9.7.3
SQL authorization ID = POCSA12
Local database alias = DAT012SA

User wasadmin owns all of the tables, but Mel granted db admin access to TAUSER12.

Here is original, pre-migration createInstance.properties


AD Stage - DB2

Server s1ad6b.myco.com
Instance / Node a1sa14
Database da1014sa
Port 50141


ST Delivery - DB2

db name ds1012sa
server s1st5b.myco.com
port 50121
user wasadmin
password foo
schema owner WASADMIN


ST Stage - DB2

DB name DS1014SA
Node name S1SA14
server s1st6b.myco.com
port 50141
user wasadmin
password foo
schema owner WASADMIN
Appservers s1st6a


QA Live - DB2

Server s1qa8b.myco.com
Instance / Node name qasa12
Database dqa012sa
Port 50120
DB admin wasadmin
DB pass foo


QA Stage - DB2

db2 connect to dqa014sa user wasadmin using foo

qasa14@s1qa4b: /opt/db/db2/instance/qasa14> db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DQA014SA
 Database name                        = DQA014SA
 Local database directory             = /opt/db/db2/dbdir/qasa14/dqa014sa
 Database release level               = d.00
 Comment                              = WCS71 QA STAGE DATABASE
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

PORT number 50140


AT Live - DB2

s1at2b: /opt/db/db2/runlog> db2 connect to db001sa

   Database Connection Information

Database server        = DB2/AIX64 9.7.3
SQL authorization ID   = ATSA12
Local database alias   = DAT012SA


Database settings

LIVE

update database configuration for <bname> using stmtheap 60000;
update database configuration for <bname> using locklist 250000;
update database configuration for <bname> using maxlocks 80;
update database configuration for <bname> using pckcachesz 60000;

STAGE

update database configuration for dqp012sa using stmtheap 60000;
update database configuration for dqp012sa using locklist 80000;
update database configuration for dqp012sa using maxlocks 80;
update database configuration for dqp012sa using pckcachesz 60000;

See DB Admin for more info.


PR Preview - DB2

App Server s1sa2a.myco.com
DB Server host s1sa2b.myco.com
DB Instance prsa20
DB Name dpr020sa
DB Port 50200


PR Stage - DB2

DB Server s1sa3b.myco.com
Port 50140
DB Name dpr014sa
Primary node s1sa3a.myco.com

To catalog...

db2 catalog db dpr014sa as dpr014sa at node dpr014sa
db2 catalog tcpip node dpr014sa remote s1sa3b.myco.com server 50140
db2 list node directory


PR Live - DB2

App Server s1sa7b.myco.com
DB Server host s1sa7a.myco.com
DB Instance prsa12
DB Name dpr012sa
DB Port 50120


Database changes

Database data and schema changes consolidated from the 5.6 ER change requests list.

Live:

Stage:

For questions, see Commerce Admin (770-433-8211 ext. 82875)


Post DB restore steps

  1. Give DB Grants

  2. DBM CFG
    db2 update dbm cfg using DFT_MON_BUFPOOL ON
    db2 update dbm cfg using DFT_MON_LOCK ON
    db2 update dbm cfg using DFT_MON_SORT ON
    db2 update dbm cfg using DFT_MON_STMT ON
    db2 update dbm cfg using DFT_MON_TABLE ON
    db2 update dbm cfg using DFT_MON_TIMESTAMP OFF
    db2 update dbm cfg using DFT_MON_UOW ON
    db2 update dbm cfg using MON_HEAP_SZ 8192
    db2 update dbm cfg using JAVA_HEAP_SZ 8192
    db2 update dbm cfg using QUERY_HEAP_SZ 5000
    db2 update dbm cfg using SYSADM_GROUP DB2SADM
    db2 update dbm cfg using SYSCTRL_GROUP DB2SCTL
    db2 update dbm cfg using SYSMAINT_GROUP DB2SMNT
    db2 update dbm cfg using SYSMON_GROUP DB2SMON
    db2 update dbm cfg using SHEAPTHRES 10000
    

  3. DB CFG
    update db cfg for dqp012sa using stmtheap AUTOMATIC;
    update db cfg for dqp012sa using locklist 2400 AUTOMATIC;
    update db cfg for dqp012sa using pckcachesz 4096 AUTOMATIC;
    update db cfg for dqp012sa using LOCKTIMEOUT 60;
    update db cfg for dqp012sa using LOGPRIMARY 50;
    update db cfg for dqp012sa using LOGSECOND 50;
    update db cfg for dqp012sa using LOGRETAIN recovery;
    update db cfg for dqp012sa using LOGARCHMETH1 TSM;