DB2 notes
3 non - prod
dev/test
one is performance
stag / prod is one environment
4 DBs in prod
HCL Enterprise Websphere 9.1.5
DB2 Enterprise 11.1.5
db2fenc1 - Fence user never accessed by a person. the DB2 engine use this
db2inst1 - Instance user on mrsdevdb2601 password is passw0rd
db2admin -
DB2IADM1 - /etc/group DB2 Instance Aadmin #1 , db2admin member
DB2IADM2 -
8 characters or less
G! and Marc, after talking to Rajesh, this is what we agreed upon, but is subject to change. J
wcadmin is the database name in DB2
db2tst601.gcp.tmw.com à dev1/tst1, dev2/tst2, dev3/tst3-primary (4 CPU & 64 GB memory)
db2tst602.gcp.tmw.com à tst3-standby & reporting standby ( 2-4 CPU 16 GB Memory)
Below have not been built yet
Db2stg601.gcp.tmw.com àstage (4 CPU 16 GB Memory)
Db2prd601.gcp.tmw.com àprod-primary (4 CPU 32 GB Memory)
Db2prd602.gcp.tmw.com àprod-standby and reporting standby (4 CPU 32 GB Memory)
service now ticket for map96 to be added to tb-gcp-dba
Nearsync = Transactions on primary will commit only after relevant logs have been written to disk on primary and received
into memory on standby
For websphere ecommerce application:
Retry interval for client reroute: 10 seconds
Maximum retries for client reroute: 6 retries
take over = switch over
fail over = fail over
ask Rajesh about SSL
1. gunzip the 11.5 file in dbdump as root, gives TAR file, then unTAR
2. unzip license file dbdump as root
db2ese_c.lic is license file: /usr/opt/app/dbdump/ese_o/db2/license
cd server_dec - all of the files relating to doing the installation
pre-req check to ensure we have everything we need to install DB2 - /usr/opt/app/dbdump/server_dec>./db2prereqcheck -v 11.5.0.0
-v for version to just check for this version 11.5.0.0 - 4 digits
/usr/opt/app/dbdump/server_dec/requirements.out is where the pre-reqs are
db2_install (better than the GUI) or db2setup (recommended by IBM, dumb down GUI)
install sample database - syscat, systools -
logical way of organizing the tables in the database - schema definition
squirrel for DB2 query
get scripts from Naveen, Tariq, Namita
cron jobs
mailx
/usr/opt/app/dbdump/server_dec/db2/linuxamd64/tsamp>./prereqSAM
pureScale? DB2's really high availability
db2_install to install the engine
sudo su db2admin
attach to database
connect to database
. .bash_profile
. ~/sqllib/db2profile in the instance home
choose SERVER to install the engine
CLIENT on the application servers
license for all the databases - confirm with Noe question for Noe and answer is yes
db2sysc to check that the database running
cd $DB2_HOME/instance to create the instance/database or any instance related activities
$DB2_HOME/db2icrt to create the instance
50000 default port
./db2icrt -u dev1fnc1 dev1ins1 have not started DB2 yet . Go to instance home directory $DB2_HOME/instance
sudo su dev1ins1, is not defaulting to the instance home but going to DB2_HOME/instance
should go to /usr/opt/app/db2/admin/dev1ins1
sudo su - dev1ins1 takes you to home
~/sqllib/db2profile
>db2 <-- to get all the commands
>db2start
>ps -ef | grep db2sysc
WSADMIN schema
Naveen will load the data
hostname alias for DB2 servers to setup for HVR
sudo su - dev1ins1
. ~/sqllib/db2profile <-- to source the profile
db2stop <-- to shutdown the instance
exit out of dev1ins1
cd $DB2_HOME/instance>./db2idrop dev1ins1
>db2sampl ~dbpath /usr/opt/app/db2/admin/dev1ins1/db2data <-- to create the sample schema
>db2 activate <-- allocates the memory and resources. Whenever not used, it will release resources if not activated and first logon will be long
syscat.tables = dict in oracle
DEV1INS1 is the database name (schema in DB2)
db2 "select * from dev1ins1.staff fetch first 10 rows only"
no database created then activate at this time is just the instance
changing the port does not require a bounce
db2stop
db2start
sysadm group name = db2iadm1 <-- this is God and can do anything and whatever
sysmon group - basic monitoring role, cannot see the data
sysctrl group - granting priivileges
sysmaint group -
dbauser is the role for wsdba
>db2 get db cfg for wcdev1
"for" because we are not connected to the database
>db2 connect to wcdev1
> show details , column on the right is what the change will be once the database is restarted
>db2 list applications <-- to see active connections
>db2 terminate <-- to kill just your connection
>db2stop
>db2start
created the database logged in as wsdba
>db2 connect
> db2 list db directory <-- list the databases active or not that is associated with that instance
state 0X000 <-- look for this to know that the database
db2 list tablespaces show detail | grep State
. /usr/opt/app/db2/admin/tstins1/sqllib/db2profile <-- to source the profile and change databases
. /usr/opt/app/db2/admin/dev2ins2/sqllib/db2profile
>db2 grant dbadm with dataaccess on database to user wsadmin
>db2 list applications force all
>db2 list tablespaces show detail
staglog tablespaces
cashivl tablespaces
db2c <-- listener process
dbm is the master settings for the instance as a whole:- db2 get dbm cfg
db is only for that database :- db2 get db cfg
cat /etc/services | grep -i dbc <-- to see the port
>db2set all <-- all of the registry variables , need to have db2comm
>db2set db2comm=tcpip <-- a bounce needs to happen
status of 602 server - response file
status of 601 server
send Jeanne email about those acronyms - RPO RTO
on-prem server mrsdevdb2601
vmdb2adm <-- this is an AD account that expires April 29th every year pwd is N8ojU(wBXztX
vmdevfnc
vmdevins - instance owner
vmdba
vmadmin
wcs <-- password is passw0rd
on-prem server mrsdevdb2601 As gm15:
$ id
uid=191(gm15) gid=220(staff) groups=220(staff),3002(db2iadm1),3003(db2fsadm1) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
$ dzdo su - wcs
Enter your Windows Network Password:
[wcs@mrsdevdb2601 ~]$
>db2 list db directory
As gm15:
$ dzdo su - vmdb2adm
Enter your Windows Network Password:
[vmdb2adm@mrsdevdb2601 ~]$
[vmdb2adm@mrsdevdb2601 ~]$ dzdo su -
Sorry, user vmdb2adm is not allowed to execute '/usr/bin/su -' as root on mrsdevdb2601.tmw.com.
[vmdb2adm@mrsdevdb2601 ~]$ pwd
/export/home/vmdb2adm
[vmdb2adm@mrsdevdb2601 ~]$ cd /usr/opt/app/db2/admin/vmdevins/sqllib
[vmdb2adm@mrsdevdb2601 sqllib]$ ./db2profile
[vmdb2adm@mrsdevdb2601 sqllib]$ db2start
12/27/2021 14:13:49 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[vmdb2adm@mrsdevdb2601 sqllib]$ db2 connect to vmdev1
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = VMDB2ADM
Local database alias = VMDEV1
[vmdb2adm@mrsdevdb2601 sqllib]$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = VMDEV1
Database name = VMDEV1
Local database directory = /usr/opt/app/db2/admin/vmdevins/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = REPODB
Database name = REPODB
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Terry O'Bannion ... keep alive, 2 or 3 mins
/tmp/db2_install.log.4425 <-- install log
/tmp/db2icrt.log <-- instance log
instance>./db2icrt -u vmdevfnc vmdevins <-- to create instance
source profile: >. /usr/opt/app/db2/admin/vmdevins/sqllib/db2profile <-- to source do not have to be in the sqllib directory
sqllib>db2stat
>db2 backup db sample to /usr/opt/app/dbdump
>db2 restore db sample from /usr/opt/app/dbdump *taken at...*
>db2diag -H 5m
>db2 list applications <-- to see which applications are connecting to the database
>db2 force application all <-- to disconnect application
>db2 deactivate db wcdev1
>db2 drop db wcdev1
sudo su - <-- get to root first
sudo su - wsdba
wsdba>/home/db2admin/create_WCS_DB.sh wcdev1 wsdba hclwsdba_1 wsadmin <-- to recreate database as wsdba
sqllib is at the instance level, so things in there would not be deleted until you drop the instance , not the database
On PREM accounts on GCP server:
db2admin
dev1fnc1
dev1ins1
tst1fnc1
tst1ins1
dev2fnc2
dev2ins2
tst2fnc2
tst2ins2
dev3fnc3
dev3ins3
tst3fnc3
tst3ins3
wsdba
wsadmin
When we db2 drop database, it will remove everything except archive logs , go and manually remove the archive logs
standby
db2_install
db2icrt
>db2 restore database wctst3 from /usr/opt/app/dbdump/ taken at 'copy/paste timestamp from backup file' redirect generate script restore wctst3.sql without prompting - in dbdump and logged in as wsdba
>db2 -tvf restore_wctst3.sql logged in wsdba (sysctl and syadm and sysmaintenance access) and in dbdump directory
now roll forward to the end or to specific point in time just before the crash RPO , how many mins of data loss that can be accepted
>db2 rollforward db wctst3 query status - to get a time to rollforward to
>db2 rollforward db wctst3 to end of backup and complete - 'not pending status'
>db2 list db directory
>db2 connect to wctst3
HADR 120
Tuesday Rajesh Naveen KC Noe RPO RTO HADR , ACR (automatic client reroute) to the database from the client in an environment where there is no VIP ,
so in the cloud
hadr_target_list - for auxiliary server
set profile
update db cfg on both primary / standby
restore
>db2 terminate
>db2 deactivate db wctst3
:colorschema evening
rollforward status needs to be db pending status just before starting hadr
start the standby first
>db2 start hdr on db wctst3 as standby
>db2 "? errorcode" | more
we changed the hadr_loca_svc on standby from 50000 to 60000 and did not have to take it out of rollforward status
LOGINDEXBUILD needs to be set to ON for TSAMP , HCR on standby and primary
>db2 update db cfg for wctst3 using LOGINDEXBUILD on
>db2 rollforward db wctst3 query status
>db2 start hdr on db wctst3 as standby <-- we have a standby
primary does not have to be in not pending status
auxiliary reporting database needs to be in db pending status
now to setup TSAMP
restore vmdev on prem
rstore GCP
for setting the environment in your /home/gm15 modify the .aliases when we have a new instance
.aliases
.bash_profile
.bashrc
home - will take me to the instance home eg, /usr/opt/app/db2/admin/dev3ins3
601 in gcp /usr/local/db2
database directory cache refreshed
>db2 terminate
>db2 list db directory
in production ACR must be set first on the standby, because once we setup standby we cannot connect to it
build the databse shell, and that flushes the db config file, and catalog the database
>db2haicu DB2 High Availability Instance Configuration Utility
tst3tsa - is the TSAMP name , Tivoli Sysstem Automation
quorum device activated in GCP ?? did not need this, but we needed to build this with instance owner
need to use instance owner to create TSAMP does not have an option to select a user against. Else, the files created on 602 were created using the db2admin acct,
and no permissions
To get port number:
db2 "get dbm cfg"|grep -i svce
>db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON <-- needs to be set when restore from a database that has different credentials
then bounce the instance
>db2set -all <-- will show this profile registry setting
near sync - primary would not wait for the transaction to be committed to the secondary, and get ack that the file exist there
4core 32GB - prod , standby
stage 16GB, 4 core
outstanding: setting up the cluster RSCT - ports , network, hostname / IP
HADR is working and we can ping them
re-installed the database
TSAMP - two servers cannot talk , 3 components - UDP ports , stateless so does not show listening unless something is actually on them
TCP is different but not using it.
symbolic links is an issue for TSAMP
so you have not worked with sym locks - No
steps next week:
rebuild the databases Monday
mtnce winddow 11pm CST
working session with unix admin
open PMR:- HCL/IBM TSAMP is not woking
On 602 delete existing db2haicu setup
>db2haicu -delete logged in as db2admin
sudo su -
su - tst3ins3
. sqllib/db2profile
>db2haicu -delete logged in as tst3ins3
>db2haicu <-- to create
https://www.ibm.com/docs/en/data-server-manager/3.1.x?topic=database-creating-db2-repository
2. ensure the local firewall is not turned on , on reboo
1. reboot both servers including databses
3. both servers need on the same subnet, /32 is not it
4. the firewall between the two servers is preventing the ports to talk to each other
looks like UDP communication is being affected
echo PING_strict-1 >/var/cl/cfg/configure.cfg
operation and configuration quorum
configuration quorum:- more than half the nodes or communication in 2 nodes cluster that means that both have to communicate. Issues could be subnet issues
operation quorum:- have the ability to start resources and protect resourcea which is node +1
TSAMP is not supported on GCP or any cloud
what are the other options beside TSAMP
Pacemaker HADR 11.5.0.0.0 supported in 11.5.4 which is in development now
renaming the database from MALL
what is the status on the staging and the prod server
esbgcpuser - esbgcpuserd esbgcpusert esbgcpusers
uc4gcpjobs - uc4gcpjobsd uc4gcpjobst uc4gcpjobsstest
test/dev/stag/prod
and cleanup server
make sure no password aging for local accounts
fix pack install
Naveen - can solveda use wsdba and wsadmin accounts , if we are still connecting to that database
- live data , are we using the same data from existing solveda to migrate into stage and prod
ticket to roots mrsdevdb2601
preprpnode
startrpdomain
to get TSAMP to work we need to allowed tcp and udp on both primary and standby
ifconfig
10.40.34.1 is the gateway
lsrpnode - to verify that both nodes are online
reporting server is part of the super async part of the replication but not part of the failover
>db2ilist <-- list the instances
Using username "gm15".
Last login: Sun Apr 18 17:55:48 2021 from 172.27.240.14
$ dzdo su - vmdb2adm
[vmdb2adm@mrsdevdb2601 ~]$ . /usr/opt/app/db2/admin/vmdevins/sqllib/db2profile
[vmdb2adm@mrsdevdb2601 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.0.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
ROOT db2bp 20296 *LOCAL.vmdevins.210424210033 VMDEV1 1
WCS db2jcc_applica 19927 172.27.100.210.50170.210424160002 VMDEV1 1
WCS db2jcc_applica 20295 172.27.100.210.51268.210424210001 VMDEV1 1
WCS db2jcc_applica 19413 172.27.100.210.64934.210424090002 VMDEV1 1
WCS db2jcc_applica 20221 172.27.100.210.51032.210424200001 VMDEV1 1
WCS db2jcc_applica 20148 172.27.100.210.50823.210424190001 VMDEV1 1
WCS db2jcc_applica 19707 172.27.100.210.49488.210424130001 VMDEV1 1
WCS db2jcc_applica 19265 172.27.100.210.64492.210424070001 VMDEV1 1
WCS db2jcc_applica 20001 172.27.100.210.50364.210424170001 VMDEV1 1
db2 => list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = VMDEV1
Database name = VMDEV1
Local database directory = /usr/opt/app/db2/admin/vmdevins/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
db2 create database '<db_name>' on '<data location>' dbpath on '<db_path_location>'
look into RSP
To install the fixpack: It contains the entire DB2 database:
gunzip
tax xf
rm -rf server_dec
cd server_dec
ls -lrt
./db2prereqcheck -v 11.5.5.1
>./db2_install
choose server
.../server_dec/db2/linuxamd64/tsamp
need to take down tsamp for the upgrade
su - dev2ins2
-- during the outage then point databases to new upgraded version
>db2 list applications
we need stop fault monitoring daemon
>db2dm -D
>db2stop
>ipclean <-- to release resources. Note that >ipclean -a will bring DB2 to a complete stop
>cd .../11.5.5.1/instance
>db2iupdt -k dev2ins2 <-- to upgrade the instance to a fixpack, but use db2iupgrade to upgrade to a version like 12.1 for example
>su - dev2ins2
>db2start
>db2level to verify the version
>db2licm -l to verify the license
> db2licm -a db2ese_c
z9jDDZpQpLbkMX_7h
log in as the instance account - sudo su - tst1ins1
>restore database wctst1 from /usr/opt/app/dbdump taken at 20210415152414 without prompting
>db2 "? sql2539w"
>db2 connect to wctst1
>db2 rollforward database wctst1 to end of backup and complete
>db2 rollforward database wctst1 query status
MALL renamed to WCDEV1 on GCP
MALL renamed to VMDEV1 on-prem mrsdevdb2601
server modification request
64GB 601 total
24GB 602
>dsmtop -d VMDEV1 to get deadlocks etc
>db2 create database repodb pagesize 8 k <-- to create the repository for the db2 mgmt console on mrsdevdb2601 logged in as vmdb2adm
>db2 update
N8ojU(wBXztX / vmdb2adm
db2admin:x:1002:1015::/home/db2admin:/bin/bash
dev1fnc1:x:1003:1015::/home/dev1fnc1:/bin/bash
dev1ins1:x:1004:1015::/usr/opt/app/db2/admin/dev1ins1:/bin/bash
tst1fnc1:x:1005:1015::/home/tst1fnc1:/bin/bash
tst1ins1:x:1006:1015::/usr/opt/app/db2/admin/tst1ins1:/bin/bash
dev2fnc2:x:1007:1015::/home/dev2fnc2:/bin/bash
dev2ins2:x:1008:1015::/usr/opt/app/db2/admin/dev2ins2:/bin/bash
tst2fnc2:x:1009:1015::/home/tst2fnc2:/bin/bash
tst2ins2:x:1010:1015::/usr/opt/app/db2/admin/tst2ins2:/bin/bash
dev3fnc3:x:1011:1015::/home/dev3fnc3:/bin/bash
dev3ins3:x:1012:1015::/usr/opt/app/db2/admin/dev3ins3:/bin/bash
tst3fnc3:x:1013:1015::/home/tst3fnc3:/bin/bash
tst3ins3:x:1014:1015::/usr/opt/app/db2/admin/tst3ins3:/bin/bash
wsdba:x:1015:1016::/home/wsdba:/bin/bash db2inst1
wsadmin:x:1016:1017::/home/wsadmin:/bin/bash wcs
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
rajesh_gajula:x:1017:1018::/home/rajesh_gajula:/bin/bash
esbgcpuser:x:1018:1021::/home/esbgcpuser:/bin/bash
uc4jobs:x:1019:1021::/home/uc4jobs:/bin/bash
Could you create the following service accounts as local on the GCP server on db2tst601.gcp.tmw.com only.
dev4fnc4:x:1013:1015::/home/dev4fnc4:/bin/bash
dev4ins4:x:1014:1015::/usr/opt/app/db2/admin/dev4ins4:/bin/bash
wcs:x:1015:1016::/home/wcs:/bin/bash
db2inst1:x:1016:1017::/home/db2inst1:/bin/bash
The primary group for dev4fnc4, dev4ins4, wcs should be db2iadm1
wcs
db2inst1
/rd300 360GB disk space for 601 only
db2 create role tibco
db2 grant connect on database to role tibco
db2 grant role tibco to user esbgcpdb2devuser
db2 set schema wcs
db2 grant select on table blah to role tibco
db2 set schema wcs <-- even after logging in as esbuser
db2 "select * from wcs. "
>history
/home/db2admin/admin_scripts directory
db2 => list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = VMDEV1
Database name = VMDEV1
Local database directory = /usr/opt/app/db2/admin/vmdevins/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = REPODB
Database name = REPODB
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
db2 => connect to REPODB
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = VMDB2ADM
Local database alias = REPODB
db2 => list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 24576
Useable pages = 24572
Used pages = 22392
Free pages = 2180
High water mark (pages) = 22392
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4064
Used pages = 96
Free pages = 3968
High water mark (pages) = 96
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4092
Used pages = 104
Free pages = 3988
High water mark (pages) = 104
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 4
Name = TS4CONSOLE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 24576
Useable pages = 24544
Used pages = 24032
Free pages = 512
High water mark (pages) = 24032
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 5
Name = TS4CONSOLE_TEMP
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
>sudo lsblk <-- to see the disk
>ls /etc/fstab
>sudo blkld
>less /etc/fstab
disk name /rd300
linux takes the ID
UID is not sync between GCP console (which is seeing the name) and on fstab (which is seeing the UID)
>resize2fs /dev/sdc for the resized disk space to show up on "df -h"
wsdba wsadmin are AD accounts - passwords are the same
tst2ins2
port 50003
Virtual IP addresses is not allowed in the Cloud so we have to use ACR - Automatic Client Reroute
ACR routes the traffice - the connections
TSAMP is just for monitoring and triggering certain events
HADR is to replicate and assign the roles - primary or secondary
16GB SWAP 601 tst , still need to request SWAP for stg, prod 601
8GB SWAP 602 tst , still need to request SWAP for prod 602
vmdevfnc:x:436:3002:DB2 Admin User:/export/home/vmdevfnc:/bin/false
vmdevins:x:437:3002:DB2r Admin User:/usr/opt/app/db2/admin/vmdevins:/bin/false
vmdevins needs access to shell, so that we can schedule and run cron jobs
as gm15
$ dzdo su - vmdb2adm
[vmdb2adm@mrsdevdb2601 ~]$
dzdo su - vmdevins but from which account?
mgmtconsole - userid for DB2 MGMT CONSOLE management console
Oracle4ever - password
On GCP servers become
$ sudo su db2admin <-- this account is the only account to become root
[db2admin@dev601 dev1ins1 gm15]
$ sudo su - <-- become root
Last login: Tue May 11 13:00:09 CDT 2021 on pts/0
]# su - dev1ins1
Last login: Mon May 10 09:16:49 CDT 2021 on pts/0
[dev1ins1@dev601 ~]
$ db2 connect to wcdev1
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = DEV1INS1
Local database alias = WCDEV1
[dev1ins1@dev601 ~]
$ /home/db2admin/admin_scripts/db2.backup_db.ksh dev1ins1 wcdev1
/home/db2admin/admin_scripts/db2.backup_db.ksh settings
job = Backup
instance = dev1ins1
database = WCDEV1
host = db2tst601.gcp.tmw.com
log file = /home/db2admin/admin_scripts/output/WCDEV1_Backup22930.log
Backup: Succeeded at 2021-05-11-13.11.42.239338
[dev1ins1@dev601 ~]
$ crontab -l
#--- Daily backups [ at 3am each day ]
00 3 * * * /home/db2admin/admin_scripts/db2.backup_db.ksh dev1ins1 wcdev1 > /dev/null 2>&1
#--- Trancsaction log monitoring [ every 5 minutes ]
*/5 * * * * /home/db2admin/admin_scripts/db2.tx_mon.ksh dev1ins1 wcdev1 > /dev/null 2>&1
[dev1ins1@dev601 ~]
[root@db2tst601 ~]# su - dev2ins2
Last login: Wed May 5 11:23:30 CDT 2021 on pts/2
[dev2ins2@dev601 ~]
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WCDEV2
Database name = WCDEV2
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[dev2ins2@dev601 ~]
$ db2 connect to WCDEV2
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = DEV2INS2
Local database alias = WCDEV2
[dev2ins2@dev601 ~]
$ db2 "get dbm cfg"|grep -i svce <-- to get the port
TCP/IP Service name (SVCENAME) = 50002
SSL service name (SSL_SVCENAME) =
$ ps -ef | grep db2sysc <-- to see the databases up and running (similar to grepping smon)
dev2ins2 2287 2285 0 May05 ? 00:17:39 db2sysc 0
dev2ins2 3585 22870 0 08:41 pts/1 00:00:00 grep --color=auto db2sysc
tst3ins3 13917 13915 0 May05 ? 00:42:01 db2sysc 0
tst2ins2 22176 22174 0 May05 ? 00:37:13 db2sysc 0
tst1ins1 24272 24270 0 May05 ? 00:46:54 db2sysc 0
dev3ins3 25713 25711 0 May05 ? 00:36:33 db2sysc 0
dev1ins1 28276 28272 0 May05 ? 01:17:06 db2sysc 0
$ ls -lrt /usr/opt/app/dbdump//dev2ins2
total 25288788
-rw-------. 1 dev2ins2 db2iadm1 184627200 Mar 29 10:17 WCDEV2.0.dev2ins2.DBPART000.20210329101731.001
-rw-------. 1 dev2ins2 db2iadm1 184627200 Apr 5 16:02 WCDEV2.0.dev2ins2.DBPART000.20210405160214.001
-rw-------. 1 dev2ins2 db2iadm1 184627200 Apr 9 12:14 WCDEV2.0.dev2ins2.DBPART000.20210409121408.001
-rw-------. 1 dev2ins2 db2iadm1 188837888 Apr 16 09:11 WCDEV2.0.dev2ins2.DBPART000.20210416091113.001
-rw-------. 1 dev2ins2 db2iadm1 188837888 Apr 16 09:25 WCDEV2.0.dev2ins2.DBPART000.20210416092521.001
-rw-------. 1 dev2ins2 db2iadm1 188837888 Apr 16 09:33 WCDEV2.0.dev2ins2.DBPART000.20210416093307.001
-rw-------. 1 dev2ins2 db2iadm1 188837888 Apr 16 09:51 WCDEV2.0.dev2ins2.DBPART000.20210416095120.001
-rw-------. 1 dev2ins2 db2iadm1 179412992 Apr 16 10:03 WCDEV2.0.dev2ins2.DBPART000.20210416100340.001
-rw-------. 1 dev2ins2 db2iadm1 179412992 Apr 16 10:04 WCDEV2.0.dev2ins2.DBPART000.20210416100450.001
-rw-------. 1 dev2ins2 db2iadm1 179412992 Apr 16 11:22 WCDEV2.0.dev2ins2.DBPART000.20210416112242.001
-rw-------. 1 dev2ins2 db2iadm1 179412992 Apr 16 11:24 WCDEV2.0.dev2ins2.DBPART000.20210416112427.001
-rw-------. 1 dev2ins2 db2iadm1 196186112 Apr 16 11:30 WCDEV2.0.dev2ins2.DBPART000.20210416113034.001
-rw-------. 1 dev2ins2 db2iadm1 188850176 Apr 19 13:21 WCDEV2.0.dev2ins2.DBPART000.20210419132152.001
-rw-------. 1 dev2ins2 db2iadm1 188850176 Apr 19 13:22 WCDEV2.0.dev2ins2.DBPART000.20210419132246.001
-rw-------. 1 dev2ins2 db2iadm1 193036288 Apr 19 13:34 WCDEV2.0.dev2ins2.DBPART000.20210419133413.001
-rw-------. 1 dev2ins2 db2iadm1 193036288 Apr 19 13:38 WCDEV2.0.dev2ins2.DBPART000.20210419133807.001
-rw-------. 1 dev2ins2 db2iadm1 193036288 Apr 19 13:43 WCDEV2.0.dev2ins2.DBPART000.20210419134330.001
-rw-------. 1 dev2ins2 db2iadm1 193036288 Apr 19 13:44 WCDEV2.0.dev2ins2.DBPART000.20210419134410.001
-rw-------. 1 dev2ins2 db2iadm1 188850176 Apr 19 14:22 WCDEV2.0.dev2ins2.DBPART000.20210419142254.001
-rw-------. 1 dev2ins2 db2iadm1 188850176 Apr 19 14:25 WCDEV2.0.dev2ins2.DBPART000.20210419142553.001
-rw-------. 1 dev2ins2 db2iadm1 205099008 Apr 20 03:00 WCDEV2.0.dev2ins2.DBPART000.20210420030002.001
-rw------- 1 dev2ins2 db2iadm1 188850176 Apr 22 03:00 WCDEV2.0.dev2ins2.DBPART000.20210422030005.001
-rw------- 1 dev2ins2 db2iadm1 188850176 Apr 23 03:00 WCDEV2.0.dev2ins2.DBPART000.20210423030005.001
-rw------- 1 dev2ins2 db2iadm1 188850176 Apr 24 03:00 WCDEV2.0.dev2ins2.DBPART000.20210424030006.001
-rw------- 1 dev2ins2 db2iadm1 188850176 Apr 25 03:00 WCDEV2.0.dev2ins2.DBPART000.20210425030017.001
-rw------- 1 dev2ins2 db2iadm1 188850176 Apr 26 03:00 WCDEV2.0.dev2ins2.DBPART000.20210426030007.001
-rw------- 1 dev2ins2 db2iadm1 323223552 Apr 27 03:00 WCDEV2.0.dev2ins2.DBPART000.20210427030002.001
-rw------- 1 dev2ins2 db2iadm1 317452288 Apr 28 03:00 WCDEV2.0.dev2ins2.DBPART000.20210428030003.001
-rw------- 1 dev2ins2 db2iadm1 1174724608 Apr 30 14:41 WCDEV2.0.dev2ins2.DBPART000.20210430144149.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 Apr 30 14:42 WCDEV2.0.dev2ins2.DBPART000.20210430144208.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 Apr 30 14:46 WCDEV2.0.dev2ins2.DBPART000.20210430144633.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 May 1 03:00 WCDEV2.0.dev2ins2.DBPART000.20210501030007.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 May 2 03:00 WCDEV2.0.dev2ins2.DBPART000.20210502030007.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 May 3 03:00 WCDEV2.0.dev2ins2.DBPART000.20210503030011.001
-rw------- 1 dev2ins2 db2iadm1 1160503296 May 4 03:00 WCDEV2.0.dev2ins2.DBPART000.20210504030009.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 4 16:21 WCDEV2.0.dev2ins2.DBPART000.20210504162125.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 4 17:30 WCDEV2.0.dev2ins2.DBPART000.20210504173046.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 4 23:59 WCDEV2.0.dev2ins2.DBPART000.20210504235958.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 5 00:40 WCDEV2.0.dev2ins2.DBPART000.20210505004024.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 5 11:21 WCDEV2.0.dev2ins2.DBPART000.20210505112146.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 5 11:38 WCDEV2.0.dev2ins2.DBPART000.20210505113812.001
-rw------- 1 dev2ins2 db2iadm1 201408512 May 5 12:26 WCDEV2.0.dev2ins2.DBPART000.20210505122643.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 8 03:00 WCDEV2.0.dev2ins2.DBPART000.20210508030009.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 9 03:00 WCDEV2.0.dev2ins2.DBPART000.20210509030009.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 10 03:00 WCDEV2.0.dev2ins2.DBPART000.20210510030008.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 11 03:00 WCDEV2.0.dev2ins2.DBPART000.20210511030008.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 12 03:00 WCDEV2.0.dev2ins2.DBPART000.20210512030009.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 13 03:00 WCDEV2.0.dev2ins2.DBPART000.20210513030010.001
-rw------- 1 dev2ins2 db2iadm1 1543913472 May 14 03:00 WCDEV2.0.dev2ins2.DBPART000.20210514030011.001
[dev2ins2@dev601 ~]
$ ls -lrt /home/db2admin/
total 8
-rwxr-xr-x 1 db2admin db2iadm1 5862 May 4 15:13 create_WCS_DB.sh
drwxrwxr-x. 3 db2admin db2iadm1 143 May 10 15:51 admin_scripts
[dev2ins2@dev601 ~]
$ ls -lrt /home/db2admin/admin_scripts
total 68
-rwxrwxr-x. 1 db2admin db2iadm1 20979 Apr 16 11:55 db2.copyUser.ksh
-rwxrwxr-x. 1 db2admin db2iadm1 6110 May 10 10:53 db2.backup_db.ksh
-rwxrwxr-x. 1 db2admin db2iadm1 2531 May 10 10:58 os.capacity_chk.ksh
-rwxrwxr-x 1 db2admin db2iadm1 2970 May 10 11:00 os.memory_mon.ksh
-rwxrwxr-x 1 db2admin db2iadm1 4537 May 10 11:03 db2.tx_mon.ksh
drwxrwxrwx. 2 db2admin db2iadm1 245760 May 14 09:25 output
[dev2ins2@dev601 ~]
$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DEV2INS2 db2bp 28008 *LOCAL.dev2ins2.210514133713 WCDEV2 1
[dev2ins2@dev601 ~]
$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[dev2ins2@dev601 ~]
$ db2 list applications
SQL1611W No data was returned by Database System Monitor.
[dev2ins2@dev601 ~]
$
Friday May 14th 2021
$ db2 restore db MALL from /usr/opt/app/dbdump/keep taken at 20210514171516 redirect generate script wcdev3.sql without prompting
DB20000I The RESTORE DATABASE command completed successfully.
$ ls -lrt
total 4316000
drwxr-xr-x 4 dev2ins2 db2iadm1 4096 May 31 2019 ese_o
-rw-r--r--. 1 gm15 domain users 2069852160 Mar 13 12:02 DB2_Svr_11.5_Linux_x86-64.tar
-rw-r--r--. 1 gm15 domain users 1389623 Mar 13 12:02 DB2ESE_Restricted_Activation_11.5.zip
drwxr-xr-x 3 root root 4096 Mar 17 18:02 server_dec
-rw-r--r--. 1 gm15 domain users 25292800 Mar 24 10:02 DB2_SF_SSL_11.5_Linux_x86-64.tar
-rw-r--r--. 1 db2admin db2iadm1 643 Apr 2 22:06 restore_mall.out
-rw-r--r--. 1 db2admin db2iadm1 10194 Apr 5 16:15 restore_mall.sql
-rw-r--r--. 1 db2admin db2iadm1 889 Apr 5 16:16 restore_mall-2.out
-rw-r--r--. 1 db2admin db2iadm1 10254 Apr 5 16:23 restore_tst3.sql
-rw-r--r--. 1 db2admin db2iadm1 889 Apr 6 10:34 restore_mall_Apr6th.txt
-rw-r--r--. 1 db2admin db2iadm1 10181 Apr 13 08:41 restore-wcdev1-paths.sql
-rw-r--r--. 1 db2admin db2iadm1 907 Apr 13 08:52 restore-wcdev1-paths.out
-rw-r--r--. 1 gm15 domain users 201216 Apr 14 13:55 getsadata_9.0.0.tar
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 dev2
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst3
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst2
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst1
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 dev3
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:54 dev1
-rw-r--r-- 1 gm15 domain users 2075545600 Apr 23 10:45 v11.5.5fp1_linuxx64_server_dec.tar
-rw-r--r-- 1 dev1ins1 db2iadm1 5773239 Apr 29 00:08 export_tables.sql
-rw-r--r-- 1 dev1ins1 db2iadm1 3133788 Apr 29 00:25 export_tables.log
-rwxrwxr-x 1 dev1ins1 db2iadm1 5904402 Apr 29 00:39 load_tables.sql
-rw-rw-r-- 1 wsdba wsdba 379822 May 4 16:50 db_errors.log
-rw-rw-r-- 1 wsdba wsdba 7046458 May 4 17:41 rebuild_dev_ddl.log
-rw-rw-r-- 1 wsdba wsdba 1946 May 4 17:53 triggers.sql
-rwxrwxr-x 1 dev1ins1 db2iadm1 5972187 May 4 23:43 wcdev1_ddl-original.sql
-rw-rw-r-- 1 wsdba wsdba 7047447 May 5 00:14 wcdev2_rebuild.log
-rw-rw-r-- 1 wsdba wsdba 7588257 May 5 00:50 wcdev2_rebuild-2.log
-rw-r--r-- 1 dev2ins2 db2iadm1 7146731 May 5 11:34 wcdev2_rebuild-3.log
-rw-rw-r-- 1 wsdba wsdba 7037017 May 5 11:50 wcdev2_rebuild-4.log
-rwxr-xr-x 1 wsdba wsdba 6092841 May 5 12:20 wcdev1_ddl.sql
-rw-rw-r-- 1 wsdba wsdba 7045657 May 5 12:37 wcdev2_rebuild-5.log
-rw-r----- 1 wsdba wsdba 51676 May 5 12:54 rbind.out
-rw-rw-r-- 1 wsdba wsdba 5504574 May 5 13:30 wcdev1_export.sql
-rw-rw-r-- 1 wsdba wsdba 8833420 May 5 13:55 wcdev2_load.sql
-rw-r--r-- 1 db2admin db2iadm1 162603008 May 14 12:38 MALL.0.db2inst1.DBPART000.20210514171516.001
[dev3ins3@dev601 keep]
$ db2 restore db MALL from /usr/opt/app/dbdump/keep taken at 20210514171516 redirect generate script wcdev3.sql without prompting
DB20000I The RESTORE DATABASE command completed successfully.
[dev3ins3@dev601 keep]
$ vi wcdev3.sql
[dev3ins3@dev601 keep]
$ su - wsdba
Password:
Last login: Tue May 11 13:03:04 CDT 2021 on pts/0
Welcome to DB2TST601 Db2 server in GCP
In a standard configuration, this server houses databases for Development & Testing
dev1ins1
tst1ins1
tst2ins2
dev3ins3
tst3ins3
dev2ins2
Use an alias to change to the desired Db2 instance
alias dev1 -- dev1ins1
alias dev2 -- dev2ins2
alias dev3 -- dev3ins3
alias tst1 -- tst1ins1
alias tst2 -- tst2ins2
alias tst3 -- tst3ins3
The current instance is dev1ins1
[wsdba@dev601 dev1ins1 ~]
$ dev3
DB20000I The TERMINATE command completed successfully.
[wsdba@dev601 dev3ins3 ~]
$ pwd
/home/wsdba
[wsdba@dev601 dev3ins3 ~]
$ cd /usr/opt/app/dbdump
[wsdba@dev601 dev3ins3 dbdump]
$ cd keep
[wsdba@dev601 dev3ins3 keep]
$ ls -lrt
total 4316012
drwxr-xr-x 4 dev2ins2 db2iadm1 4096 May 31 2019 ese_o
-rw-r--r--. 1 gm15 domain users 2069852160 Mar 13 12:02 DB2_Svr_11.5_Linux_x86-64.tar
-rw-r--r--. 1 gm15 domain users 1389623 Mar 13 12:02 DB2ESE_Restricted_Activation_11.5.zip
drwxr-xr-x 3 root root 4096 Mar 17 18:02 server_dec
-rw-r--r--. 1 gm15 domain users 25292800 Mar 24 10:02 DB2_SF_SSL_11.5_Linux_x86-64.tar
-rw-r--r--. 1 db2admin db2iadm1 643 Apr 2 22:06 restore_mall.out
-rw-r--r--. 1 db2admin db2iadm1 10194 Apr 5 16:15 restore_mall.sql
-rw-r--r--. 1 db2admin db2iadm1 889 Apr 5 16:16 restore_mall-2.out
-rw-r--r--. 1 db2admin db2iadm1 10254 Apr 5 16:23 restore_tst3.sql
-rw-r--r--. 1 db2admin db2iadm1 889 Apr 6 10:34 restore_mall_Apr6th.txt
-rw-r--r--. 1 db2admin db2iadm1 10181 Apr 13 08:41 restore-wcdev1-paths.sql
-rw-r--r--. 1 db2admin db2iadm1 907 Apr 13 08:52 restore-wcdev1-paths.out
-rw-r--r--. 1 gm15 domain users 201216 Apr 14 13:55 getsadata_9.0.0.tar
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 dev2
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst3
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst2
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 tst1
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:52 dev3
drwxrwxr-x. 2 db2admin db2iadm1 4096 Apr 19 13:54 dev1
-rw-r--r-- 1 gm15 domain users 2075545600 Apr 23 10:45 v11.5.5fp1_linuxx64_server_dec.tar
-rw-r--r-- 1 dev1ins1 db2iadm1 5773239 Apr 29 00:08 export_tables.sql
-rw-r--r-- 1 dev1ins1 db2iadm1 3133788 Apr 29 00:25 export_tables.log
-rwxrwxr-x 1 dev1ins1 db2iadm1 5904402 Apr 29 00:39 load_tables.sql
-rw-rw-r-- 1 wsdba wsdba 379822 May 4 16:50 db_errors.log
-rw-rw-r-- 1 wsdba wsdba 7046458 May 4 17:41 rebuild_dev_ddl.log
-rw-rw-r-- 1 wsdba wsdba 1946 May 4 17:53 triggers.sql
-rwxrwxr-x 1 dev1ins1 db2iadm1 5972187 May 4 23:43 wcdev1_ddl-original.sql
-rw-rw-r-- 1 wsdba wsdba 7047447 May 5 00:14 wcdev2_rebuild.log
-rw-rw-r-- 1 wsdba wsdba 7588257 May 5 00:50 wcdev2_rebuild-2.log
-rw-r--r-- 1 dev2ins2 db2iadm1 7146731 May 5 11:34 wcdev2_rebuild-3.log
-rw-rw-r-- 1 wsdba wsdba 7037017 May 5 11:50 wcdev2_rebuild-4.log
-rwxr-xr-x 1 wsdba wsdba 6092841 May 5 12:20 wcdev1_ddl.sql
-rw-rw-r-- 1 wsdba wsdba 7045657 May 5 12:37 wcdev2_rebuild-5.log
-rw-r----- 1 wsdba wsdba 51676 May 5 12:54 rbind.out
-rw-rw-r-- 1 wsdba wsdba 5504574 May 5 13:30 wcdev1_export.sql
-rw-rw-r-- 1 wsdba wsdba 8833420 May 5 13:55 wcdev2_load.sql
-rw-r--r-- 1 db2admin db2iadm1 162603008 May 14 12:38 MALL.0.db2inst1.DBPART000.20210514171516.001
-rw-r--r-- 1 dev3ins3 db2iadm1 10314 May 14 17:49 wcdev3.sql
[wsdba@dev601 dev3ins3 keep]
$ db2 -tvf restore wcdev3.sql
DB21004E You cannot specify both an input file and a command when invoking
the Command Line Processor.
[wsdba@dev601 dev3ins3 keep]
$ mv wcdev3.sql restore_wcdev3.sql
[wsdba@dev601 dev3ins3 keep]
$ db2 -tvf restore_wcdev3.sql
UPDATE COMMAND OPTIONS USING S ON Z ON MALL_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
RESTORE DATABASE MALL FROM '/usr/opt/app/dbdump/keep' TAKEN AT 20210514171516 ON '/usr/opt/app/db2/admin/dev3ins3/db2data' INTO WCDEV3 REDIRECT WITHOUT PROMPTING
SQL1035N The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019
[wsdba@dev601 dev3ins3 keep]
$ vi restore_wcdev3.sql
[wsdba@dev601 dev3ins3 keep]
$ db2 -tvf restore_wcdev3.sql
UPDATE COMMAND OPTIONS USING S ON Z ON MALL_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
RESTORE DATABASE MALL FROM '/usr/opt/app/dbdump/keep' TAKEN AT 20210514171516 ON '/usr/opt/app/db2/admin/dev3ins3/db2data' INTO MALL REDIRECT WITHOUT PROMPTING
SQL1277W A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
RESTORE DATABASE MALL CONTINUE
DB20000I The RESTORE DATABASE command completed successfully.
[wsdba@dev601 dev3ins3 keep]
$ db2 "get dbm cfg"|grep -i svce
TCP/IP Service name (SVCENAME) = 50004
SSL service name (SSL_SVCENAME) =
[wsdba@dev601 dev3ins3 keep]
$ db2 connect wcs
SQL0104N An unexpected token "wcs" was found following "CONNECT". Expected
tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
[wsdba@dev601 dev3ins3 keep]
$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = WCDEV3
Database name = WCDEV3
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = MALL
Database name = MALL
Local database directory = /usr/opt/app/db2/admin/dev3ins3/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[wsdba@dev601 dev3ins3 keep]
$ db2 connect to MALL
SQL1117N A connection to or activation of database "MALL" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[wsdba@dev601 dev3ins3 keep]
$ db2 rollforward db mall to end of backup and complete
Rollforward Status
Input database alias = mall
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2021-05-14-17.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[wsdba@dev601 dev3ins3 keep]
$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = WCDEV3
Database name = WCDEV3
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = MALL
Database name = MALL
Local database directory = /usr/opt/app/db2/admin/dev3ins3/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[wsdba@dev601 dev3ins3 keep]
$ db2 connect to mall
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = WSDBA
Local database alias = MALL
[wsdba@dev601 dev3ins3 keep]
with password as passw0rd or hclwsadmin_1 or hclwcs_1
dev2ins2
port 50002
wsdba / hclwsdba_1
wsadmin / hclwsadmin_1
tst2ins2
port 50003
wsdba / hclwsdba_1
wsadmin / hclwsadmin_1
wcs and db2inst1
port 50004
MALL
DBA Account : db2inst1/passw0rd
User Account : wcs/passw0rd <-- this is an AD account
2021 May 17th
a 'restore' command will drop the database for us in the background
:colorscheme evening [ENTER] <-- to have good colors when doing VI editing
one instance of TSAMP per host
no symbolic links for DB2 , so going forward use /rd100/db2data
restore can be executed as db2admin
db2 -tvf restore_wcdev2.sql | tee restore_wcdev2.out
db2 rollforward db wcdev2 query status <-- to check the rollforward status
find /rd500 -type f -name 'filename'
fine /rd500 -type d -name 'directory'
"using local time" if the backup was not in UTC
by default it is UTC
Use account wsdba to terminate and drop the database
/home/db2admin/create_WCS_DB.sh wcdev2 wsdba hclwsdba_1 wsadmin <-- to create wcdev2 from scratch
2021 May 24th
db2diag -t 2021-05-21-19.00:2021-05-21-20.45 | more to look at the database log in a window
UOW-Waiting - Unit of Work Waiting
2021 June 15th
Questions:
what account owner - wsadmin or wcs (an active directory)
what will be the DBA account - wsdba
#getent passwd wcs <-- will show us the lsting from AD or from the local server
libnuma.so.1
as of 2021 June 16th
Dev1/tst1 is on HCL Websphere 9.1.5 for UAT only
dev2/tst2 is on HCL Websphere 9.1.6
2021 July 6th -
analyzing deadlocks DB2
https://datageek.blog/en/2012/01/23/analyzing-deadlocks-the-new-way/
Solarwinds in DB2 - I need to set this up.
Noon status meeting
9.1.6 application for performance testing on tst3
hvrgcpdb2tstuser / qt:h&7#LT?o5
DB2 11.5.5.1 is the version we have here
2021 july 15th query_to_file.sql file from Manoj
run as wsdba
2021 July 30th - to add RAM to db2tst601 and fix the hosts file
>db2 force application all <-- to disconnect application
>db2stop
grep -i sysc to ensure database is down
GCP does not supdbadmin.PS_POrt multiple nodes on a single subnet
IBM said we cannot do TSAMP in GCP
however, GCP said they do supdbadmin.PS_POrt TSAMP
2021 Aug 17th
db2set db2_compatibility_vector=08
db2 force application all; db2stop
db2start
db2set -all <-- to see the setting is done
2021 Aug 18th
db2prd601.gcp.tmw.com
wsdba / hc1wsdba_prd
[root@db2prd601 ~]# getent passwd wsdba <-- to check if an account is in active directory
wsdba:x:1005:1004::/home/wsdba:/bin/bash
[root@db2prd601 ~]# grep -i wsdba /etc/passwd
wsdba:x:1005:1004::/home/wsdba:/bin/bash
The above means that wsdba is not an active directory account
2021 Aug 25th
To change password for wcs:
db2 "CONNECT TO wctst1 USER wcs USING passw0rd"
wcs / passw0rd
wsadmin / passw0rd <-- not sure about this one.
db2 "CONNECT TO wctst2 user wcs using L36_wZMpk"
2021 Aug 29th
wcstg1
db2stg601.gcp.tmw.com
50000
wsdba / STG7_x23Ic
wcprd1
db2prd601.gcp.tmw.com
50000
wsdba / XQc%7RPiItmw
Online backup as wsdba
$ /home/db2admin/admin_scripts/db2.backup_db.ksh -i dev2ins2 -d wcdev2
$ db2 list applications
Offline backup as wsdba
db2 force application all; db2stop
$ /usr/local/db2/db2admin/admin_scripts/db2.backup_db.ksh -i dev1ins1 -d wcdev1 -o
lcH202191V^ <-- password to log into https://app.box.com/folder/133597203061 to upload backup
2021 Oct 5th
grant DBADM WITH DATAACCESS on database to MG252 MJ226; on wcstg1 and wcprd1 and needs to be revoked
grant DBADM WITH DATAACCESS on database to SBP15;
2021 Oct 11th
hvrgcpdb2prduser / G2OZ_1Fb_P5_i_fc0_P
create role tibco;
grant connect on database to role tibco;
grant role tibco to user esbgcpdb2prduser;
grant select, insert, update on table wcs.XGCPMSPINVENTORY to role tibco;
grant select, insert, update on table wcs.XGCPMSPSTOREINVENTORY to role tibco;
grant select, insert, update on table wcs.XGCPMSPSTOREDELTAINVENTORY to role tibco;
grant select, insert, update on table wcs.XGCPMSPDELTAINVENTORY to role tibco;
hostname: db2prd601.gcp.tmw.com
IP : 10.40.46.10
Alias name: wcsdb2prddbprmy
uc4gcpdb2prduser FhXPz4rHF'_+
uc4gcpdb2stguser fBV8E&MCn&0@
uc4gcpdb2devuser 0D*qgb4c6RuO zero then letter oh
uc4gcpdb2tstuser 6u0oSFs*ZM7x
2021 Oct 22nd
network tie breaker for TSAMP setup - Google is recommending this.
On db2prd602 is standby taking over 603:
in dbdump directory
db2haicu -o new_haicu_settings_20211025.xml
db2 takeover hadr on db wcprd1 <-- manual takeover
lssam -top <-- to see the log
Kill -9 the db2sysc process to pull the rug out from under the db
on db2prd603 , the standby
db2 get db cfg for wcprd1 | grep HADR <-- to check if it is running
ps -ef | grep db2sysc <-- to check if db is up
db2 update db cfg for wctst3 using hadr_peer_window 300 <-- let tsamp up to 5 mins to detect and issue , start and complete a failover
hadr_peer_window must match on both primary and standby
[root@db2tst601 ~]# ps -ef | grep db2sysc
tst1ins1 2176 2174 2 21:09 pts/1 00:00:01 db2sysc 0
tst2ins2 3582 3577 6 21:09 pts/1 00:00:02 db2sysc 0
tst3ins3 5139 5137 3 21:02 ? 00:00:14 db2sysc 0
root 6388 545 0 21:10 pts/1 00:00:00 grep --color=auto db2sysc
dev1ins1 29921 29849 0 21:08 pts/0 00:00:01 db2sysc 0
dev2ins2 30888 30885 1 21:08 pts/0 00:00:01 db2sysc 0
dev3ins3 31490 31488 0 21:08 pts/0 00:00:00 db2sysc 0
what is the best way to run stats on DB2.
getent passwd mj226 <-- to ago against active directory
db2 "force application (8342)"
To check performance of database
[prd1ins1@db2prd601 prd1ins1 prd1ins1]
$ dsmtop -d wcprd1 is old use dmctop -d wcdev2
dbtop
db2top
after cyber weekexp: 3.30am
stats: 4.15
hot: 6am
Database refreshes: from Tariq Nov 30th 2021
Masking data after post db refresh.
truncate table wcs.xuseroptin;
truncate table wcs.xemailevent;
update wcs.ppcextdata set datavalue ='dummyLastName' where attributename = 'billto_lastname';
update wcs.ppcextdata set datavalue ='dummyFirstName' where attributename = 'billto_firstname';
update wcs.ppcextdata set datavalue ='123-123-1234' where attributename = 'billto_phone_number';
Back up these two rows and update after post db refresh. This seems to be challenging as this is text with double quotes.
select * from wcs.COLLDESC where collateral_id in ( select collateral_id from wcs.COLLATERAL where name like '%MO_CONFIGURATION_DATA_CONTENT%');
Backup STORECONF and KEYS tables on lower environment and restore it.
2021 Dec 15th
----------------------------------
ACR turned off on the application
To switch to 602 from 601 , so On 602
/usr/opt/app/db2/admin/tst3ins3/tsa_tests/takeoverHADR.sh failover command switchover command
[tst3ins3@dev602 tsa_tests]
$ ls -lrt
total 8
-rwxr--r-- 1 tst3ins3 db2iadm1 528 Oct 28 14:13 crashDB.sh
-rwxr--r-- 1 tst3ins3 db2iadm1 134 Oct 28 14:14 takeoverHADR.sh
[tst3ins3@dev602 tsa_tests]
$ ./takeoverHADR.sh wctst3
2021-12-15-14.38.30.512458
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
real 0m16.12s
user 0m0.01s
sys 0m0.01s
[tst3ins3@dev602 tsa_tests]
$
ON 601 to switch back:
$ ls -lrt
total 8
-rwxr--r-- 1 tst3ins3 db2iadm1 528 Oct 28 14:13 crashDB.sh
-rwxr--r-- 1 tst3ins3 db2iadm1 134 Oct 28 14:14 takeoverHADR.sh
[tst3ins3@dev601 tsa_tests]
$ ./takeoverHADR.sh wctst3
2021-12-15-14.40.35.785159
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
real 0m15.55s
user 0m0.01s
sys 0m0.01s
[tst3ins3@dev601 tsa_tests]
On 602 bring standby down 602
$ ./crashDB.sh kill
2021-12-15-14.42.20.086210
[tst3ins3@dev602 tsa_tests]
$
waiting 5 mins for the peer window to expire
Mala and Sai confirmed that the application is up
on 602 to bring 602 back up
$ ./crashDB.sh
2021-12-15-14.48.56.352115
[tst3ins3@dev602 tsa_tests]
2021 Dec 31st:
To take online backup of vmdev1 on mrsdevdb2601:
as gm15
$ dzdo su - vmdb2adm
[vmdb2adm@mrsdevdb2601 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.5.1
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => quit
DB20000I The QUIT command completed successfully.
[vmdb2adm@mrsdevdb2601 ~]$ db2 list active databases
Active Databases
Database name = VMDEV1
Applications connected currently = 8
Database path = /rd100/db2data/vmdevins/NODE0000/SQL00001/MEMBER0000/
[vmdb2adm@mrsdevdb2601 ~]$ db2 connect to vmdev1
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = VMDB2ADM
Local database alias = VMDEV1
[vmdb2adm@mrsdevdb2601 ~]$ db2 backup database vmdev1 online to /usr/opt/app/dbdump/vmdevins compress without prompting
Backup successful. The timestamp for this backup image is : 20211231101510
-rw-------. 1 vmdevins db2iadm1 207695872 Dec 31 10:15 VMDEV1.0.vmdevins.DBPART000.20211231101510.001
[vmdb2adm@mrsdevdb2601 vmdevins]$ pwd
/usr/opt/app/dbdump/vmdevins
To take offline backup of vmdev1 on mrsdevdb2601:
as gm15
$ dzdo su - vmdb2adm
[vmdb2adm@mrsdevdb2601 ~]$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
WCS db2jcc_applica 56066 172.27.101.11.51846.211231190055 VMDEV1 1
[vmdb2adm@mrsdevdb2601 ~]$ db2 connect to vmdev1
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = VMDB2ADM
Local database alias = VMDEV1
[vmdb2adm@mrsdevdb2601 ~]$ db2 quiesce db immediate force connections
DB20000I The QUIESCE DATABASE command completed successfully.
[vmdb2adm@mrsdevdb2601 ~]$ db2 unquiesce db
DB20000I The UNQUIESCE DATABASE command completed successfully.
[vmdb2adm@mrsdevdb2601 ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
DB20000I The TERMINATE command completed successfully.
[vmdb2adm@mrsdevdb2601 ~]$ db2 force applications all
DB21024I This command is asynchronous and may not be effective immediately.
[vmdb2adm@mrsdevdb2601 ~]$ db2 deactivate db vmdev1
DB20000I The DEACTIVATE DATABASE command completed successfully.
[vmdb2adm@mrsdevdb2601 ~]$ db2 list applications
SQL1611W No data was returned by Database System Monitor.
[vmdb2adm@mrsdevdb2601 ~]$ db2 backup database vmdev1 to /usr/opt/app/dbdump/vmdevins compress without prompting
Backup successful. The timestamp for this backup image is : 20211231130159
-rw-------. 1 vmdevins db2iadm1 369221632 Dec 31 13:02 VMDEV1.0.vmdevins.DBPART000.20211231130159.001
[vmdb2adm@mrsdevdb2601 vmdevins]$ pwd
/usr/opt/app/dbdump/vmdevins
THEN BRING UP DATABASE:
[vmdb2adm@mrsdevdb2601 ~]$ db2start
12/31/2021 13:06:16 0 0 SQL1026N The database manager is already active.
SQL1026N The database manager is already active.
[vmdb2adm@mrsdevdb2601 ~]$ db2 connect to vmdev1
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.1
SQL authorization ID = VMDB2ADM
Local database alias = VMDEV1
[vmdb2adm@mrsdevdb2601 ~]$ db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
VMDB2ADM db2bp 56578 *LOCAL.vmdevins.211231190626 VMDEV1 1
2022 Jan 12th:
To get HADR settings:
$ db2 get db cfg for wcprd1 | grep HADR
HADR database role = STANDBY
HADR local host name (HADR_LOCAL_HOST) = db2prd603
HADR local service name (HADR_LOCAL_SVC) = 60000
HADR remote host name (HADR_REMOTE_HOST) = db2prd601
HADR remote service name (HADR_REMOTE_SVC) = 60000
HADR instance name of remote server (HADR_REMOTE_INST) = prd1ins1
HADR timeout value (HADR_TIMEOUT) = 20
HADR target list (HADR_TARGET_LIST) = db2prd602:60000|db2prd601:60000
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(12375000)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
2022 Jan 19th
CREATE WCDEV4
as root
/usr/opt/app/db2/product/11.5.5.1/instance <-- to create a new instance
#./db2icrt -u dev4fnc4 dev4ins4
su - wsdba
cd /usr/local/db2
create_WCS_DB.sh <-- db2name, dba user, dba password, db user <-- script needs this to create the DB specific for WCS
source the profile
. /usr/opt/app/db2/admin/dev4ins4/sqllib/db2profile
./create_WCS_DB.sh wcdev4 wsdba hclwsdba_1 wcs
#su - dev4ins4
db2 restore db wcdev1 from /usr/opt/app taken at time into wcdev4 redirect generate script scriptname
db2 list active databases
db2 deactivate db wcdev4
db2 -tvf dev4_create.sql | tee dev4_create.out <-- script in /usr/opt/app/dbdump/keep/dev4
***
db2 drob db wcdev4 <--- clears out log files because we tried to create it before and there were logs files created
then run the db2 -tvf dev4_create.sql against
db2 rollforward db wcdev4 query status
look at /usr/opt/app/dbdump/db_refresh/db2.refresh.sh to see a checklist
log target is the overflow log path
db2 "rollforward db wcdev4 to end of backup and complete overflow log path ('/usr/opt/app/dbdump/keep/dev4')"
db2 activate db wcdev4
db2 connect to wcdev4
db2 list tablespaces | grep -i state <--- 0x00000 means good
db2 get dbm cfg | grep -i instance <-- the default is too big
instance memory
db2 update dbm cfg using instance memory 225000 immediate
db2 get db cfg for wcdev4 | grep -i catalog <-- 16384 is good
2022 Jan 20th
db2 "? sql0290n" <-- to get what the error means on db2
To load data into a table from a file:
[dev1ins1@dev601 ~]
$ db2 import from /usr/opt/app/dbdump/prod-msp_list.txt of del replace into WCS.XIMAGE_TEMP
SQL3109N The utility is beginning to load data from file
"/usr/opt/app/dbdump/prod-msp_list.txt".
SQL3110N The utility has completed processing. "10820" rows were read from
the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "10820".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "10820" rows were processed from the input file. "10820" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 10820
Number of rows skipped = 0
Number of rows inserted = 10820
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 10820
[dev1ins1@dev601 ~]
$ db2 "select count(*) from WCS.XIMAGE_TEMP"
1
-----------
10820
1 record(s) selected.
Port numbers in use for the databases:
>cat /etc/services | grep db2c
db2c_dev1ins1 50000/tcp
db2c_tst1ins1 50001/tcp
db2c_dev2ins2 50002/tcp
db2c_tst2ins2 50003/tcp
db2c_dev3ins3 50004/tcp
db2c_tst3ins3 50005/tcp
db2c_dev4ins4 50006/tcp
db2c_tst4ins4 50007/tcp
db2c_dev5ins5 50008/tcp
db2c_tst5ins5 50009/tcp
2022 Jan 30th:
These scripts must be run as ROOT
The scripts are stored at /usr/local/db2/admin_scripts/
When starting the instances the script will perform validation of the databases and display the output when finished
To suppress monitoring:
cd /usr/local/db2/admin_scripts
./db2.alert_suppress.ksh disable
To re-enable monitoring:
cd /usr/local/db2/admin_scripts
./db2.alert_suppress.ksh enable
To stop instances:
cd /usr/local/db2/admin_scripts
./db2.instance_restart.sh stop
To start instances:
cd /usr/local/db2/admin_scripts
./db2.instance_restart.sh start
2022 Feb 1
db refresh of wcdev5 from wcdev1
look at /usr/opt/app/dbdump/db_refresh/db2.refresh.sh to see a checklist
run the script as the instance owner of the target database, eg dev5ins5
prep work includes
(1) taking a backup
(2) moving to the instance refresh directory which is /usr/opt/app/dbdump/db_refresh
(3) changing permissions , everyone needs to be able to read it, 644
(4) mkdir wctst5 wcdev5 in /usr/opt/app/dbdump/db_refresh
(5) chown dev5ins5:db2admin wcdev5
(6) chown tst5in5:db2admin wctst5
(7) chmod 755 wcdev5/ wctst5
>su - dev5ins5
>cd /usr/opt/app/dbdump/db_refresh
> db2 list applications <- to verify no connections to the database
>./db2.refresh.sh -o WCDEV1 -e 7 -i dev5ins5 -d WCDEV5
** hit YES at Warning returned restoring the database
>db2 archive log for db wctst1 <=== to move logs from /rd300 to /rd500 , archive the log
then physically move S0000*.LOG from /rd500 /usr/opt/app/dbdump/db_refresh/wctst5/NODE0000/LOGSTREAM
then change permissions on the file
>.fix-check-pending.sh -d wcdev5 <-- checking for tables if there is any referential integrity issues
> db2 list tablespaces | grep -i state
>db2 "select * from syscat.bufferpools"
> db2set -all <-- check for db2comm=tcpip
db2 start hadr on dn wcprd1 as standby
2022 March 24th
db2 get db cfg for wctst3 | grep HADR
db2 force application all ; db2 deactivate db wcdev2 ; db2stop ; db2 list applications
2022 May 23:
ecomrptuser / mK1YKv6yJt0%
1
2022 may 31st
In the admin_scripts directory, and db2admin cron
>./db2.log_cleaner.sh -h <-- -h will give help on how to use the script
run the above script as the instance owner , stg1ins1
2022 June 3rd
Deltas - full outage for the migration
export data import into a test database - dev1/tst1
5 to 7 test moves
Identifiers may have to go with new databases
2022 June 21st:
On WCSTG1
grant role tibco to user esbgcpdb2stguser; WJ&J^Ps865I9
Table WCS.XGCPMSPBUSINESSPRODUCT created.
grant select,insert,update,delete on table WCS.XGCPMSPBUSINESSPRODUCT to tibco;
Grant succeeded.
Table WCS.XGCPMSPUNIVERSEDELTAPRODUCT created.
grant select,insert,update,delete on table WCS.XGCPMSPUNIVERSEDELTAPRODUCT to tibco;
Grant succeeded.
Table WCS.XGCPMSPUNIVERSEPRODUCT created.
grant select,insert,update,delete on table WCS.XGCPMSPUNIVERSEPRODUCT to tibco;
Grant succeeded.
grant select, insert,update,delete on table WCS.XGCPMSPUNIVERSEPRODUCT to uc4;
grant select,insert,update,delete on table WCS.XGCPMSPBUSINESSPRODUCT to uc4;
grant select,insert,update,delete on table WCS.XGCPMSPUNIVERSEDELTAPRODUCT to uc4;
anything that we did not do
resources - non-prod
rto
rpo
how much downtime expected
/rd600 : The /rd600 is for storing the diagnostic files. I can take a look and verify that those files are being cleaned up regularly.
While the space consumed isn't usually very much per database, if there is a problem, trap files will be created to capture extra diagnostic information and those can get large fast.
2022 July 18th
db2ilist - show the instances already created
db2 drop db oratest
db2 drop db repodb
dev1ins1 / dev1fnc1 used for database tmwdev5
cd ~/sqllib/bnd
db2 connect to tmwdev5
db2 bind @db2ubind.lst blocking all grant public
#su - wsdba
source the profile/ set the environment
. /usr/opt/app/db2/admi/dev1ins1/sqllib/db2profile
db2 connect to tmwdev5
db2 truncate wcs.staglog immediate
db2 alter tablespace tab8k reduce max <-- to reclaim that disk space
set "ur"
db2set -all to show DB2AUTH
db2 set DB2AUTH=OSAUTHDB <-- this needs to be done for AD (active directory) accounts to connect successfully
then dbstop and db2start for changes to take effect, also db2 activate db tmwdev5
wsdba is a local account
2022 Sept 22nd;
On db2tst602.gcp.tmw.com
tmwdev5
dev1fnc1:x:1003:1015::/home/dev1fnc1:/bin/bash
dev1ins1:x:1004:1015::/usr/opt/app/db2/admin/dev1ins1:/bin/bash
tmwtst5
tst1fnc1:x:1005:1015::/home/tst1fnc1:/bin/bash
tst1ins1:x:1006:1015::/usr/opt/app/db2/admin/tst1ins1:/bin/bash
wcdev7 dev2ins2 decommissioned on 2024 Feb 9th
dev2fnc2:x:1007:1015::/home/dev2fnc2:/bin/bash
dev2ins2:x:1008:1015::/usr/opt/app/db2/admin/dev2ins2:/bin/bash
wctst7 tst2ins2 decommissioned on 2024 Feb 9th
tst2fnc2:x:1009:1015::/home/tst2fnc2:/bin/bash
tst2ins2:x:1010:1015::/usr/opt/app/db2/admin/tst2ins2:/bin/bash
dev3fnc3:x:1011:1015::/home/dev3fnc3:/bin/bash
dev3ins3:x:1012:1015::/usr/opt/app/db2/admin/dev3ins3:/bin/bash
wctst3 - port 25010
tst3fnc3:x:1013:1015::/home/tst3fnc3:/bin/bash
tst3ins3:x:1014:1015::/usr/opt/app/db2/admin/tst3ins3:/bin/bash
decom 2023 Dec 18th
wcdev8 - dev8ins8 on port 50004
dev8fnc8 /home/dev8fnc8:/bin/bash
dev8ins8 /usr/opt/app/db2/admin/dev8ins8:/bin/bash
decom 2023 Dec 18th
wctst8 - tst8ins8 on port 50005
tst8fnc8 /home/tst8fnc8:/bin/bash
tst8ins8 /usr/opt/app/db2/admin/tst8ins8:/bin/bash
wcdev9 - port 50006
dev9fnc9:x:1029:1015::/home/dev9fnc9:/bin/bash
dev9ins9:x:1030:1015::/usr/opt/app/db2/admin/dev9ins9:/bin/bash
wctst9 - port 50007
tst9fnc9:x:1031:1015::/home/tst9fnc9:/bin/bash
tst9ins9:x:1032:1015::/usr/opt/app/db2/admin/tst9ins9:/bin/bash
wcdev10 - port 50010
dev4fnc4:x:1029:1015::/home/dev4fnc4:/bin/bash
dev4ins4:x:1030:1015::/usr/opt/app/db2/admin/dev4ins4:/bin/bash
wctst10 - port 50011
tst4fnc4:x:1031:1015::/home/tst4fnc4:/bin/bash
tst4ins4:x:1032:1015::/usr/opt/app/db2/admin/tst4ins4:/bin/bash
2022 Oct 1
Create new database:
Source Database Name : vmdev1
DB Backup Directory : /usr/opt/app/dbdump/vmdevins
Timestamp of original backup : 20211231130159
Local database directory (your database standard list) : /export/home/db2inst1/ OR /export/home/vmdb2adm/ OR /export/home/vmdevins/
drwxr-xr-x. 2 vmdevfnc db2fsadm1 6 Mar 30 2021 vmdevfnc
drwxr-xr-x. 2 vmdevins db2iadm1 6 Mar 31 2021 vmdevins
drwx------. 4 db2inst1 db2iadm1 189 Apr 28 2021 db2inst1
drwx------. 4 vmdb2adm db2iadm1 189 Apr 29 2021 vmdb2adm
Target (New) database name : vmdev2
Offline backup taken:
[vmdb2adm@mrsdevdb2601 ~]$ db2 backup database vmdev1 to /usr/opt/app/dbdump/vmdevins compress without prompting
Backup successful. The timestamp for this backup image is : 20211231130159
password for vmdevins or how to get to vmdevins account or dzdo su - vmdevins but from which account?
As root change the shell for vmdevins so we can log in
#chsh -s /bin/bash vmdevins
archive logs: /rd100/db2arch_logs/vmdevins/VMDEV2 <-- manually create vmdev2
active logs we need to manually create a directory for : /rd100/db2logs/vmdevins/vmdev2
In /usr/opt/app/dbdump/vmdevins
>db2 restore db vmdev1 from . taken at 20211231130159 into vmdev2 redirect generate script vmdev2.create.sql
>colorscheme evening to see the font
In script, vmdev2.create.sql, Change NEWLOGPATH for the active logs.
>db2 -tvf vmdev2.create.sql | tee vmdev2.create.log
> db2 list directory
> db2 rollforward db vmdev2 query status to see pending status
> db2 rollforward db vmdev2 to end of backup and complete
> db2 activate db vmdev2
-----
2023 Jan 27th
db2 list db directory
Backup database
Backup settings - /usr/local/db2/admin_scripts/db2.cfg_backup.kshah
stop the applications - db2 list applications - to see connections
db2fm - D <-- to check fault monitor
db2stop
db2licm -l <-- to check the license. That is a letter elle
ipclean <-- clear out DB2 holding on to memory
./db2prereqcheck -i
export DB2TMPDIR=/usr/opt/app/dbdump/patch <-- dir that has the right privs, and have at least 5GB of space
./db2_install <-- from the server_dec directory
db2level <-- will tell us where the current version is installed
we do not have to precreate /usr/opt/app/db2/product/11.5.8.0
db2ls <-- to list the DB2 engines
cd /usr/opt/app/db2/product/11.5.8.0/instance
./db2iupdt -k dev8ins8 <-- run as root
su - dev8ins8
db2diag -A <-- to archive the diaglog
db2start
db2level
db2licm -l <-- to check the license
cd ~/sqllib/bnd <-- rebind all of the engines
db2 connect to sample
db2 bind @db2ubind.lst blocking all grant public <-- ignore the warnings. we do not have the systools tablespace. Not used at all. That is for pre-11 version database
db2cli.lst
db2schema.bnd .... sqlerror continue
db2 list tablespaces | grep State
db2 connect reset
db2 terminate
/usr/opt/app/dbdump/patch/server_dec/db2/license db2licm -a db2ese_t.lic <-- -t means trial license
2023 March 6th - creating wcdev10 database
cd /usr/opt/app/db2/admin
create dev4ins4 and tst4ins4 directories and fix permissions
cd /usr/local/db2
create directories with input dev10ins10
it will create dir in /rd100 and /rd500 because that will be created when we create the database
in admin_scripts directory it has a script to setup everything except create instance
cd /usr/opt/app/db2/product/11.5.8.0/instance
./db2crt -u dev4fnc4 dev4ins4
cat bash_profile_template.txt > /usr/opt/app/db2/admin/dev4ins4/.bash_profile
cat bashrc_tempplate.tat > /usr/opt/app/dbd2/admin/dev4ins4/.bashrc
mkdir /usr/opt/app/dbdump/dev4ins4
as wsdba because it has SECADM authority
create the databases
source the profile db2profile
db2start
cd to the admin_scripts directory <-- there is the updated version in /usr/local/db2/dev.create_WCS_DB.sh
/dev.create_WCS_DB.sh wctst10 wsdba hclwsdba_1 wcs
db2licm -l <-- to check the license
db2 update dbm cfg using svcename 50010 <--- to change the port
db2stop
db2start
db2 get dbm cfg | grep -i svcename <-- to get the port used
crontab -u dev4ins4 crontab-dev4ins4.bak copy the crontab .bak file from /usr/opt/app/dbdump/dev8ins8
hvrgcpdb2tstuser / qt:h&7#LT?o5
To clean up tablespaces:
db2 list tablespaces | more <-- USERSPACE1
db2 alter tablespace tab16k reduce max
db2pd -d wctst4 -extentmovement -repeat 3
to clear up space in tablespace.
$ db2 alter tablespace userspace1 reduce max
$ db2 alter tablespace tab8k reduce max
$ db2 alter tablespace tab16k reduce max
db2diag -H 60 -l "warning,error" | more
contents of archive logs are not in cronological order of transactions, so keeping from 3.30am, 3 days ago
S*.LOG does not carry the complete pciture that Db2 needs for restore or replay , the rest of the information is in the log chain which is the following path (../stg1ins1/WCSTG1/NODE0000/LOGSTREAM0000/C0000001
cmd_arch is the number in the command line when kicking off script manually -a cmd_arch
bkp_arch
6 days
in scripts.cfg
3 days of backups, and 4 days of archive logs
compress archive logs: db2 update db cfg using logarchcompre1 on immediate
db2 get db cfg for wcstg1 show detail | grep -i log <-- now , in effect next bounce
db2 connect reset
db2 terminate - disconnects me from the database, release memory held
crontab job line and add '-a 4'
$ cat largest_tables.sql
select substr(t.tabschema,1,18) as tabschema
, substr(t.tabname,1,40) as tabname
, (COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as tab_size_mb
, tableorg
from syscat.tables t join sysibmadm.admintabinfo ati on t.tabname = ati.tabname and t.tabschema = ati.tabschema
where t.type = 'T' and t.tabschema not like ('SYS%')
and t.tbspace = 'TAB8K'
order by 3 desc
fetch first 20 rows only with ur;
[tst2ins2@db2tst601 ~]
db2 get db cfg > /usr/opt/app/dbdump/prd-db-cfg.tx
db2 list utilities show detail
while true; do db2 list utilities show detail; sleep 30; done
db2 list history backup since 20230917 for wcprd1
to kill a session:
get the application handle
press lowercase a for the small window to pop up
enter the application Handle in that window
Press ENTER twice
Ctrl F to force application off
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:44 S0015306.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:44 S0015307.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:44 S0015308.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:44 S0015309.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:44 S0015310.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:46 S0015311.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:54 S0015312.LOG
-rw-r-----. 1 prd1ins1 db2iadm1 225288192 Oct 3 04:56 S0015313.LOG
HADR ports for non-prod:
db2tst601 need these ports re-opened:
60030 through 60034 , 50005 , 600035
db2tst602 need these ports re-opened:
60000 through 60005 and 50000
2023 Dec 7th:
restore wcdev10:
$mkdir /usr/opt/app/dbdump/keep/wcdev10
[dev4ins4@db2tst602 dev4ins4]
$db2 "restore db wcdev10 from /usr/opt/app/dbdump/dev4ins4 taken at 20231206060003 logtarget /usr/opt/app/dbdump/keep/wcdev10 without prompting" 11.55am
DB20000I The RESTORE DATABASE command completed successfully.
[dev4ins4@db2tst602 dev4ins4]
$ db2 "rollforward db wcdev10 to end of backup and complete overflow log path (/usr/opt/app/dbdump/keep/wcdev10)"
2023 Dec 14th
restore wcdev2
[dev2ins2@db2tst601 ~]
$ mkdir /usr/opt/app/dbdump/keep/wcdev2
[dev2ins2@db2tst601 ~]
$ cd /usr/opt/app/dbdump/dev2ins2/
[dev2ins2@db2tst601 dev2ins2]
$ ls -lrt
total 981352
-rw-r--r-- 1 dev2ins2 db2iadm1 471767 Nov 18 05:50 WCDEV2_Cfg_backup_1700308201.tar.gz
-rw-r--r-- 1 dev2ins2 db2iadm1 471772 Nov 25 05:50 WCDEV2_Cfg_backup_1700913001.tar.gz
-rw-r--r-- 1 dev2ins2 db2iadm1 471777 Dec 2 05:50 WCDEV2_Cfg_backup_1701517802.tar.gz
-rw-r--r-- 1 dev2ins2 db2iadm1 471624 Dec 9 05:51 WCDEV2_Cfg_backup_1702122601.tar.gz
-rw------- 1 dev2ins2 db2iadm1 973348864 Dec 13 06:01 WCDEV2.0.dev2ins2.DBPART000.20231213060002.001
-rw-r--r-- 1 dev2ins2 db2iadm1 3384 Dec 13 15:12 COLLDESC_2rows.del
-rw-r--r-- 1 dev2ins2 db2iadm1 28774557 Dec 13 15:12 COLLDESC.del.001.lob
-rw-r--r-- 1 dev2ins2 db2iadm1 806349 Dec 13 15:12 COLLDESC.del
-rw-r--r-- 1 dev2ins2 db2iadm1 22364 Dec 13 15:12 STORECONF.del
-rw-r--r-- 1 dev2ins2 db2iadm1 33593 Dec 13 15:12 KEYS.del
[dev2ins2@db2tst601 dev2ins2]
$ db2 connect to wcdev2
SQL1119N A connection to or activation of database "WCDEV2" cannot be made
because a previous restore is incomplete or still in progress. SQLSTATE=57019
[dev2ins2@db2tst601 dev2ins2]
$ db2 "restore db wcdev2 from /usr/opt/app/dbdump/dev2ins2/ taken at 20231213060002 logtarget /usr/opt/app/dbdump/keep/wcdev2 without prompting"
DB20000I The RESTORE DATABASE command completed successfully.
[dev2ins2@db2tst601 dev2ins2]
$ db2 "rollforward db wcdev2 to end of backup and complete overflow log path (/usr/opt/app/dbdump/keep/wcdev2)"
Rollforward Status
Input database alias = wcdev2
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0016410.LOG - S0016410.LOG
Last committed transaction = 2023-12-13-12.01.20.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[dev2ins2@db2tst601 dev2ins2]
2023 Dec 19th
To update memory on an instance, in this case wstst10:
db2 list db directory
db2 get dbm cfg | grep -i instance
exit
db2 update dbm cfg using INSTANCE_MEMORY 1572864 immediate
db2 terminate; db2 force applications all; db2stop force; db2start
free -m
db2 get dbm cfg | grep instance
4KB = 4096 bytes
1KB = 1024 bytes
2024 March 27th:
to generate an archive log:
cd /rd500/db2arch_logs/tst4ins4/WCTST10/NODE0000/LOGSTREAM0000/C0000042
db2 terminate <-- disconnect your session
db2 archive log for db wctst10
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WCTST3
Database name = WCTST3
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = db2tst602.gcp.tmw.com
Alternate server port number = 50000
db2 update alternate server for database wctst3 using hostname db2tst602.gcp.tmw.com port 50000
db2 update alternate server for database wctst3 using hostname db2tst601.gcp.tmw.com port 50005
$ db2 get db cfg for wcprd1 | grep -i timeout
Lock timeout (sec) (LOCKTIMEOUT) = 45 <-- default is -1 which means never timeout
HADR timeout value (HADR_TIMEOUT) = 20
Lock timeout events (MON_LOCKTIMEOUT) = NONE
db2 get db cfg for wctst3 | grep HADR
db2pd -db wctst10 -hadr
db2prd601:
Shutdown HADR
db2 stop hadr on db wcprd1
turn off monitoring - just the HADR monitoring
db2prd602:
db2 get db cfg for wcprd1 | grep HADR
HADR database role = STANDBY
HADR local host name (HADR_LOCAL_HOST) = db2prd602
HADR local service name (HADR_LOCAL_SVC) = 60000
HADR remote host name (HADR_REMOTE_HOST) = db2prd601
HADR remote service name (HADR_REMOTE_SVC) = 60000
HADR instance name of remote server (HADR_REMOTE_INST) = prd1ins1
HADR timeout value (HADR_TIMEOUT) = 20
HADR target list (HADR_TARGET_LIST) = db2prd601:60000|db2prd603:60000
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(12375000)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
turn off monitoring
did not take backup on standby
stop hadr and tsa
db2 deactivate db wcprd1
db2 stop hadr on db wcprd1
db2haicu -disable (1)
Are you sure you want to disable high availability (HA) for the database instance 'prd1ins1'. This will lock all the resource groups for the instance and disable the HA configuration parameter. The instance will not failover if a system outage occurs while the instance is disabled. You will need to run db2haicu again to enable the instance for HA. Disable HA for the instance 'prd1ins1'? [1]
1. Yes
2. No
1
Disabling high availability for instance 'prd1ins1' ...
Locking the resource group for HADR database 'WCPRD1' ...
Locking the resource group for HADR database 'WCPRD1' was successful.
Locking the resource group for DB2 database partition '0' ...
Locking the resource group for DB2 database partition '0' was successful.
Locking the resource group for DB2 database partition '0' ...
Locking the resource group for DB2 database partition '0' was successful.
Disabling high availability for instance 'prd1ins1' was successful.
All cluster configurations have been completed successfully. db2haicu exiting ...
$ lssam
Online IBM.ResourceGroup:db2_prd1ins1_db2prd601_0-rg Request=Lock Nominal=Online
'- Online IBM.Application:db2_prd1ins1_db2prd601_0-rs Control=SuspendedPropagated
'- Online IBM.Application:db2_prd1ins1_db2prd601_0-rs:db2prd601
Online IBM.ResourceGroup:db2_prd1ins1_db2prd602_0-rg Request=Lock Nominal=Online
'- Online IBM.Application:db2_prd1ins1_db2prd602_0-rs Control=SuspendedPropagated
'- Online IBM.Application:db2_prd1ins1_db2prd602_0-rs:db2prd602
Online IBM.ResourceGroup:db2_prd1ins1_prd1ins1_WCPRD1-rg Request=Lock Nominal=Online
'- Online IBM.Application:db2_prd1ins1_prd1ins1_WCPRD1-rs Control=SuspendedPropagated
|- Online IBM.Application:db2_prd1ins1_prd1ins1_WCPRD1-rs:db2prd601
'- Offline IBM.Application:db2_prd1ins1_prd1ins1_WCPRD1-rs:db2prd602
Online IBM.Equivalency:db2_prd1ins1_db2prd601_0-rg_group-equ
'- Online IBM.PeerNode:db2prd601:db2prd601
Online IBM.Equivalency:db2_prd1ins1_db2prd602_0-rg_group-equ
'- Online IBM.PeerNode:db2prd602:db2prd602
Online IBM.Equivalency:db2_prd1ins1_prd1ins1_WCPRD1-rg_group-equ
|- Online IBM.PeerNode:db2prd601:db2prd601
'- Online IBM.PeerNode:db2prd602:db2prd602
Online IBM.Equivalency:db2_public_network_0
|- Online IBM.NetworkInterface:eth0:db2prd601
'- Online IBM.NetworkInterface:eth0:db2prd602
[prd1ins1@db2prd602 prd1ins1]
$ db2pd -db wcprd1 -hadr
Database WCPRD1 not activated on database member 0 or this database name cannot be found in the local database directory.
Option -hadr requires -db <database> or -alldbs option and active database.
[prd1ins1@db2prd602 prd1ins1]
$
db2 restore db wcprd1 from /usr/opt/app/dbdump/prd1ins1 taken at 20240321050002
$ db2 restore db wcprd1 from /usr/opt/app/dbdump/prd1ins1 taken at 20240321050002
SQL2539W The specified name of the backup image to restore is the same as the
name of the target database. Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
db2 get db cfg for wcprd1 | grep HADR
db2 "update db cfg for wcprd1 using HADR_LOCAL_HOST db2prd602 HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST db2prd601 HADR_REMOTE_SVC 60000 HADR_TARGET_LIST db2prd601:60000|db2prd603:60000"
HADR database role = STANDBY
HADR local host name (HADR_LOCAL_HOST) = db2prd602
HADR local service name (HADR_LOCAL_SVC) = 60000
HADR remote host name (HADR_REMOTE_HOST) = db2prd601
HADR remote service name (HADR_REMOTE_SVC) = 60000
HADR instance name of remote server (HADR_REMOTE_INST) = prd1ins1
HADR timeout value (HADR_TIMEOUT) = 20
HADR target list (HADR_TARGET_LIST) = db2prd601:60000|db2prd603:60000
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(12375000)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
db2 start hadr on db wcprd1 as standby
** wait about 2 mins to verify that HADR is still running as expected
db2pd -db wcprd1 -hadr
db2haicu
$ db2haicu
Welcome to the DB2 High Availability Instance Configuration Utility (db2haicu).
You can find detailed diagnostic information in the DB2 server diagnostic log file called db2diag.log. Also, you can use the utility called db2pd to query the status of the cluster domains you create.
For more information about configuring your clustered environment using db2haicu, see the topic called 'DB2 High Availability Instance Configuration Utility (db2haicu)' in the DB2 Information Center.
db2haicu determined the current DB2 database manager instance is 'prd1ins1'. The cluster configuration that follows will apply to this instance.
db2haicu is collecting information on your current setup. This step may take some time as db2haicu will need to activate all databases for the instance to discover all paths ...
When you use db2haicu to configure your clustered environment, you create cluster domains. For more information, see the topic 'Creating a cluster domain with db2haicu' in the DB2 Information Center. db2haicu is searching the current machine for an existing active cluster domain ...
db2haicu found a cluster domain called 'prdtsa' on this machine. The cluster configuration that follows will apply to this domain.
db2haicu has detected that high availability has been disabled for the instance 'prd1ins1'. Do you want to enable high availability for the instance 'prd1ins1'? [1]
1. Yes
2. No
1
Retrieving high availability configuration parameter for instance 'prd1ins1' ...
The cluster manager name configuration parameter (high availability configuration parameter) is not set. For more information, see the topic "cluster_mgr - Cluster manager name configuration parameter" in the DB2 Information Center. Do you want to set the high availability configuration parameter?
The following are valid settings for the high availability configuration parameter:
1.TSA
2.Vendor
Enter a value for the high availability configuration parameter: [1]
1
Setting a high availability configuration parameter for instance 'prd1ins1' to 'TSA'.
Enabling high availability for instance 'prd1ins1' ...
Enabling high availability for instance 'prd1ins1' was successful.
All cluster configurations have been completed successfully. db2haicu exiting ...
[prd1ins1@db2prd602 prd1ins1]
$
turn on monitoring
db2 list history backup since 202403281111 for db wcprd1
db2 list history backup since 202403210930 for db wcprd1
11.5.9 patching suggestion - already completed
Jeff Johnson HCL Support to Everyone 1:25 PM
https://www.ibm.com/mysupport/s/defect/aCI3p000000PYRkGAO/dt169561?language=en_US
Bharat IBM to Everyone 1:51 PM
db2set -im DB2_PMODEL_SETTINGS=CKPWD_WAIT_TIMEOUT:60000000
db2set -im DB2_PMODEL_SETTINGS=
Bharat IBM 2:03 PM
https://www.ibm.com/mysupport/aCI3p0000004Jrh
For ACR to work in the application, it needs to be set in the application but ALSO, the database needs to be set:
[tst4ins4@db2tst602 ~]
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WCTST10
Database name = WCTST10
Local database directory = /rd100/db2data
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = db2tst601.gcp.tmw.com
Alternate server port number = 25010
[tst3ins3@db2tst601 ~]
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WCTST10
Database name = WCTST10
Local database directory = /usr/opt/app/db2/admin/tst3ins3
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = db2tst602.gcp.tmw.com
Alternate server port number = 50011
To see all the ports an instance is using:
$ cat /etc/services | grep tst3ins3
DB2_tst3ins3 20016/tcp
DB2_tst3ins3_1 20017/tcp
DB2_tst3ins3_2 20018/tcp
DB2_tst3ins3_3 20019/tcp
DB2_tst3ins3_4 20020/tcp
DB2_tst3ins3_END 20021/tcp
db2c_tst3ins3 25010/tcp
2024 June 19th
If the database restore fails at stage (step7):
Stage 6 complete
--------------------------------------------------------------------
Beginning stage 7: Rolling database forward
--------------------------------------------------------------------
SQL1265N The archive log file "S0088227.LOG" is not associated with the
current log sequence for database "WCDEV3" on database partition "0" and log
stream "0".
You do not have to
- restore from source , wcstg1 if you need to restore.
look for log files here:
- archive log dir
- overflow log path dir
But this is what you have to do.
Find the log file
Rename it
Restart the restore script from step 7
rename the log file back
$find / -type f -name 'S0088227.LOG' 2>/dev/null
./rd500/db2arch_logs/dev3ins3/WCDEV3/NODE0000/LOGSTREAM0000/C0000006/S0088227.LOG
[root@db2tst601 LOGSTREAM0000]# ls -l
total 120
drwxr-x---+ 2 dev3ins3 db2iadm1 4096 Dec 6 2021 C0000000
drwxr-x--- 2 dev3ins3 db2iadm1 20480 Jun 18 10:15 C0000001
drwxr-x--- 2 dev3ins3 db2iadm1 4096 Mar 31 2022 C0000002
drwxr-x--- 2 dev3ins3 db2iadm1 4096 Dec 22 2022 C0000003
drwxr-x--- 2 dev3ins3 db2iadm1 4096 Jun 3 2023 C0000004
drwxr-x--- 2 dev3ins3 db2iadm1 36864 Jul 31 2023 C0000005
drwxr-x--- 2 dev3ins3 db2iadm1 40960 Jun 3 14:06 C0000006
drwxr-x--- 2 dev3ins3 db2iadm1 4096 Jun 3 14:10 C0000052
drwxr-x--- 2 dev3ins3 db2iadm1 4096 Jun 18 09:58 C0000053
[root@db2tst601 LOGSTREAM0000]#
[root@db2tst601 C0000006]# ls -lrt S0088227.LOG
-rw-r----- 1 dev3ins3 db2iadm1 16384 Jun 2 20:15 S0088227.LOG
[root@db2tst601 C0000006]# mv S0088227.LOG S0088227.LOG_old
[root@db2tst601 C0000006]# pwd
/rd500/db2arch_logs/dev3ins3/WCDEV3/NODE0000/LOGSTREAM0000/C0000006
to start from stage 7:
$ ./db2.refresh.sh -s wcstg1 -t wcdev3 -l /usr/opt/app/dbdump/db_refresh -b 7 starting the script from step 7
Beginning stage 9: Updating HADR configuration parameters
--------------------------------------------------------------------
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "USING".
Expected tokens may include: "ACT_SORTMEM_LIMIT". SQLSTATE=42601
Stage 9: Failure returned updating HADR settings
We were expecting stage9 to fail, so we are starting from 10
to start from stage 7:
$ ./db2.refresh.sh -s wcstg1 -t wcdev3 -l /usr/opt/app/dbdump/db_refresh -b 10
then rename the file back:
db2 get db cfg | grep HADR to see the output:
$ db2 get db cfg | grep HADR
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
HADR SSL certificate label (HADR_SSL_LABEL) =
HADR SSL Hostname Validation (HADR_SSL_HOST_VAL) = OFF
Comments
Post a Comment