Shulou(Shulou.com)06/01 Report--

At the end of the year, the company needs to deploy a set of DB2 environment + application system to demonstrate related functional modules to customers. Before, there is not much contact with DB2. Take advantage of this opportunity to open the road of exploration and learning of DB2.

DB2 database deployment

Hardware environment information:

Machine IP:10.162.64.78

OS version: RHEL 6.6

Memory: 256GB

1. File system partition

View the PV,VG that has been created

# pvs

PV VG Fmt Attr PSize PFree

/ dev/sda3 VolGroup00 lvm2 a muri-557.38g 379.38g

# vgs

VG # PV # LV # SN Attr VSize VFree

VolGroup00 1 2 0 wz--n- 557.38g 379.38g

Create LV

# lvcreate-L 20g-n lvdb2log VolGroup00

# lvcreate-L 30g-n lvdb2arch VolGroup00

# lvcreate-L 150G-n lvdb2data VolGroup00

Format LV

# mkfs.ext4 / dev/VolGroup00/lvdb2log

# mkfs.ext4 / dev/VolGroup00/lvdb2arch

# mkfs.ext4 / dev/VolGroup00/lvdb2data

Create a directory and mount it

# mkdir-p / oradata4

# mkdir-p / db2arch

# mkdir-p / db2log

# mount / dev/VolGroup00/lvdb2log / db2log

# mount / dev/VolGroup00/lvdb2arch / db2arch

# mount / dev/VolGroup00/lvdb2data / oradata4

# tune2fs-c 0-I 0 / dev/VolGroup00/lvdb2log

# tune2fs-c 0-I 0 / dev/VolGroup00/lvdb2arch

# tune2fs-c 0-I 0 / dev/VolGroup00/lvdb2data

# echo "/ dev/VolGroup00/lvdb2log / db2log ext4 defaults 00" > > / etc/fstab

# echo "/ dev/VolGroup00/lvdb2arch / db2arch ext4 defaults 00" > > / etc/fstab

# echo "/ dev/VolGroup00/lvdb2data / oradata4 ext4 defaults 00" > > / etc/fstab

two。 Install the required software packages

Libstdc++.so.6

3. Modify system kernel parameters

# vi / etc/sysctl.conf

Kernel.shmmax = 137438953472-set to half or slightly larger than physical memory

Kernel.shmall = 67108864

Kernel.shmmni = 4096

Kernel.sem = 250 32000 100 128

Fs.file-max = 65536

Net.ipv4.ip_local_port_range = 1024 65000

Net.core.rmem_default=262144

Net.core.rmem_max=262144

Net.core.wmem_default=262144

Net.core.wmem_max=262144

4. Create a user

# groupadd-g 1011 db2iadm1

# groupadd-g 1012 db2fadm1

# useradd-u 1016-g db2iadm1-d / home/db2inst1-m db2inst1

# useradd-u 1017-g db2fadm1-d / home/db2fenc1-m db2fenc1

# echo 'db2inst1' | passwd-- stdin db2inst1

# echo 'db2fenc1' | passwd-- stdin db2fenc1

# chown db2inst1:db2iadm1 / oradata4

# chown db2inst1:db2iadm1 / db2log

# chown db2inst1:db2iadm1 / db2arch

5. Install the database

# tar zxvf v10.5fp7_linuxx64_universal_fixpack.tar.gz

# cd universal/

#. / db2_install

DBI1324W Support of the db2_install command is deprecated.

Default directory for installation of products-/ opt/ibm/db2/V10.5

* * *

Install into default directory (/ opt/ibm/db2/V10.5)? [yes/no]

Yes

Specify one of the following keywords to install DB2 products.

SERVER

CONSV

EXP

Enter "help" to redisplay product names.

Enter "quit" to exit.

* * *

SERVER

* * *

Do you want to install the DB2 pureScale Feature? [yes/no]

No

DB2 installation is being initialized.

.

The execution completed successfully.

For more information see the DB2 installation log at

"/ tmp/db2_install.log.25687"

6. View and install license

# find /-name db2licm

/ opt/ibm/db2/V10.5/adm/db2licm

# / opt/ibm/db2/V10.5/adm/db2licm-l

Product name: "DB2 Enterprise Server Edition"

License type: "License not registered"

Expiry date: "License not registered"

Product identifier: "db2ese"

Version information: 10.5

#. / db2licm-a / install/isrs.lic

Product name: "DB2 Enterprise Server Edition"

License type: "CPU Option"

Expiry date: "Permanent"

Product identifier: "db2ese"

Version information: 10.5

7. Create an instance

Modify the / etc/hosts configuration as follows:

127.0.0.1 localhost

10.162.64.78 OSS-ZYGL-294

# / opt/ibm/db2/V10.5/instance/db2icrt-a server-p 9999-u db2fenc1 db2inst1

DBI1446I The db2icrt command is running.

DB2 installation is being initialized.

.

The execution completed successfully.

For more information see the DB2 installation log at "/ tmp/db2icrt.log.9284".

DBI1070I Program db2icrt completed successfully.

# / opt/ibm/db2/V10.5/instance/db2ilist

Db2inst1

Start the instance:-- switch to the db2inst1 user

Configure environment variables-root and db2inst1 users

# vi ~ / .bash_profile

$vi ~ / .bash_profile

PATH=$PATH:$HOME/bin:/opt/ibm/db2/V10.5/bin

# source ~ / .bash_profile

$source ~ / .bash_profile

$db2start

01/23/2017 13:36:37 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

8. Create DB

$db2 "create db RESDB on / oradata4 using codeset UTF-8 territory cn"

DB20000I The CREATE DATABASE command completed successfully.

Note: if you specify a path when creating a database, the default path and the home directory of the database user will be used, as shown below:

$db2 get dbm cfg | grep-I DFTDBPATH

Default database path (DFTDBPATH) = / home/db2inst1

9. Parameter adjustment

$db2 connect to resdb

$db2iauto-on db2inst1-- set the database instance to boot automatically

9.1 Parameter adjustment

$db2 update dbm cfg using SVCENAME 9999 DFT_MON_BUFPOOL on DFT_MON_UOW on NUM_POOLAGENTS 4100

DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

SQL1362W One or more of the parameters submitted for immediate modification

Were not changed dynamically. Client changes will not be effective until the

Next time the application is started or the TERMINATE command has been issued.

Server changes will not be effective until the next DB2START command.

Note: database restart is required to take effect.

9.2 Chronicle Adjustment

-- Log location and resizing

$db2 update db cfg for RESDB using NEWLOGPATH / db2log

$db2 update db cfg for RESDB using LOGARCHMETH1 disk:/db2arch

$db2 update db cfg for RESDB using LOGFILSIZ 25600 LOGPRIMARY 13 LOGSECOND 12 CATALOGCACHE_SZ 2048 LOGBUFSZ 4096 UTIL_HEAP_SZ 10240 DB_MEM_THRESH 100 DLCHKTIME 5000 LOCKTIMEOUT 15

9.3 other parameter adjustment

Db2set DB2_PARALLEL_IO=*

Db2set DB2_SKIPINSERTED=YES

Db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES

Db2set DB2_EVALUNCOMMITTED=YES

Db2set DB2_ANTIJOIN=EXTEND

Db2set DB2_SKIPDELETED=YES

Db2set DB2CODEPAGE=1208

After the parameters are adjusted, the database is restarted:

$db2stop

01/23/2017 13:48:24 0 0 SQL1025N The database manager was not stopped because databases are still active.

SQL1025N The database manager was not stopped because databases are still active.

$db2 force application all

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

$db2stop

01/23/2017 13:49:39 0 0 SQL1064N DB2STOP processing was successful.

SQL1064N DB2STOP processing was successful.

$db2start

01/23/2017 13:50:13 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

The / etc/security/limits.conf configuration file needs to be adjusted at this point

Db2list1 soft nofile 65536

Db2inst1 hard nofile 65536

Db2inst1 soft nproc 65536

Db2inst1 hard nproc 65536

Reconnect to the database:

$db2 connect to resdb

SQL1116N A connection to or activation of database "RESDB" failed because the

Database is in BACKUP PENDING state. SQLSTATE=57019

Note: if you change the LOGRETAIN parameter in the data from circular log mode to archive mode, it will result in database BACKUP PENDING, as follows

$db2 update db cfg for resdb using LOGRETAIN RECOVERY

SQL1597N Configuring the DB2 environment failed because the specified DB2

Configuration parameter is discontinued.

Unable to connect at this time, the database needs to be fully offline so that the state can be restored to normal so that it can be accessed.

$db2 backup db resdb to / dev/null

Backup successful. The timestamp for this backup p_w_picpath is: 20170123135258

$db2 connect to resdb

10. Turn on ORACLE compatibility mode

$db2set DB2_COMPATIBILITY_VECTOR=ORA

$db2set-all | grep 'DB2_COMPATIBILITY_VECTOR'

Restart the database

$db2stop force

$db2start

11. Create a database object

$db2 "create bufferpool BP_DAT_32K size 16000 pagesize 32k"

$db2 "create bufferpool BP_IDX_32K size 16000 pagesize 32k"

$db2 "create bufferpool BP_TMP_32K size 16000 pagesize 32k"

$db2 "CREATE LARGE TABLESPACE TBS_DAT_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_DAT_32K AUTORESIZE YES INCREASESIZE 1G NO FILE SYSTEM CACHING"

$db2 "CREATE LARGE TABLESPACE TBS_IDX_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_IDX_32K AUTORESIZE YES INCREASESIZE 1G NO FILE SYSTEM CACHING"

$db2 "CREATE system TEMPORARY TABLESPACE TBS_SYSTMP_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP_TMP_32K NO FILE SYSTEM CACHING"

twelve。 Database recovery

Create the required directory:

$mkdir-p / oradata4/db2

First of all, analyze the backup files:

$ls-al | grep-I RESLT4

-rw-r--r-- 1 db2inst1 db2iadm1 13223706624 Jan 19 12:42 RESLT4.0.db2inst1.DBPART000.20170118202238.001

Generate restore.clp file

$db2 restore db RESLT4 taken at 20170118202238 redirect generate script restore.clp

DB20000I The RESTORE DATABASE command completed successfully.

Modify the restore.clp file:

RESTORE DATABASE RESLT4

-- USER

-- USING'

FROM'/ oradata4/db2'

TAKEN AT 20170118202238

-- ON'/ oradata4/db2'

-- DBPATH ON'

INTO RESDB

-- NEWLOGPATH'/ oradata4/db2/db2inst1/NODE0000/SQL00004/LOGSTREAM0000/'

-- WITH BUFFERS

-- BUFFER

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM

WITHOUT ROLLING FORWARD

-- WITHOUT PROMPTING

-- SET STOGROUP PATHS FOR IBMSTOGROUP

-- ON'/ oradata4/db2'

--

RESTORE DATABASE RESLT4 CONTINUE

Note: the database for remote backup is named RESLT4, the remote backup path is / oradata4/db2, and the local database is called RESDB. You need to create a / oradata4/db2 path locally.

Perform a restore operation:

$db2-tvf restore.clp

UPDATE COMMAND OPTIONS USING S ON Z ON RESLT4_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 RESLT4 FROM'/ oradata4/db2' TAKEN AT 20170118202238 INTO RESDB REDIRECT WITHOUT ROLLING FORWARD

SQL2529W Warning! Restoring to an existing database that is different from

The backup p_w_picpath database, and the alias name "RESDB" of the existing database

Does not match the alias name "RESLT4" of the backup p_w_picpath, and the database

Name "RESDB" of the existing database does not match the database name

"RESLT4" of the backup p_w_picpath. The target database will be overwritten by the

Backup version. The Roll-forward recovery logs associated with the target

Database will be deleted.

Do you want to continue? (YBO) y

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 RESLT4 CONTINUE

DB20000I The RESTORE DATABASE command completed successfully.

Deployment of application environment

13.WAS application deployment connects to DB2 database

The previously deployed WAS environments are all connected to Oracle libraries. This is the first time that the data source configuration of libraries connected to DB2 has been configured. Find some information and record the differences from configuring Oracle as follows:

13.1 configure DB2 environment variables-this WAS deployment is in a stand-alone environment

Select Environment-- > WebSphere-- > variables-- > scope in the console interface (select server1)

Click "new" to create the DB2UNIVERSAL_JDBC_DRIVER_PATH variable with the value of "/ was/DB2lib"

Note: the host deploying the was application creates this directory and passes the required jar package to the path, jar package location / opt/ibm/db2/V10.5/java

13.2 configure the JDBC provider:

Select Resources-- > JDBC-- > JDBC providers-- > select "server1" in the console interface, and click "New".

Name: DB2 Universal JDBC Driver Provider

Select "DB2" for database type, "DB2 Universal JDBC Driver Provider" for provider type, "Connection pool data source" for implementation type, and click "Next".

Class path settings:

/ was/DB2lib/db2jcc.jar

/ was/DB2lib/db2jcc_license_cu.jar

13.3 create J2C users

Db2inst1/db2inst1

13.4. Data source configuration

JdbcResTxDataSource jdbc/ResTxDataSource

JdbcoracleResTxDataSource jdbc/oracle/ResTxDataSource

Data source connection test

14. Application deployment, test verification

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.