DB2 Problems and Solutions

DB2 problems and solutions:

I. Code Page in DB2.

A code page error occurs during DB2 backup. After the code page is modified, the backup is normal, but the code page error occurs again when the database is created. This is because the code page configuration used by the DB2 server is different from the code page configuration used by the client (note: the code page configuration of the DB2 server is independent, and operations on the server with different code pages will produce errors. The local operating server is called a local client. The code page used by the operating system may be different from the configuration of the DB2 server, which may cause the above problems like the remote client ). The code page is related to the character set used by the system. This is also why database backup in windows and database backup in Linux cannot be restored to each other (codePage in Windows is 819, the general national standard database/GBK is 1386 ). You can use the db2set command to set the server code page. (For specific settings, see the following document)

Locale command to view the local character set

Ii. Tianjin Database Backup cannot be recovered

When the Tianjin database is backed up and restored, the Linux system prompts that the iner is in use. This is because the Tianjin database adopts a tablespace other than the system and cannot be restored using the conventional method.

Recovery steps:

DB2 create dB targetdb using codeset GBK territory zh_cn

(Create a database for recovery)

 

DB2 connect to Tianjin

(Connect to the Tianjin database)

DB2 list tablespaces

(View the tablespace used by the Tianjin database)

DB2 list tablespace containers for 3

(View the container used by table space 3)

............

DB2 list tablespace containers for 7

(View the container used by table space 7 .)

(Seven tablespaces are used in the Tianjin database, of which 1 and 2 are default and others are created by yourself. If you already know the tablespace of the database, the above steps can be omitted)

 

Mkdir [Directory]

(The directory where the container used to create the tablespace is located. How many containers are required and how many directories are created. This directory must be a directory that DB2 users have permission)

 

DB2 connect Reset

(Release all connections)

 

DB2 restore dB sourcedb from/db2data/backupdata/into targetdb redirect

(Recover database)

 

DB2 "set tablespace containers for 3 using (path '/home/db2inst1/TT/1 ')"

(Set the tablespace container. The path is the path of the container)

DB2 "set tablespace containers for 4 using (path '/home/db2inst1/TT/2', path'/home/db2inst1/TT/3 ')"

(When multiple paths are used in the tablespace)

............

DB2 "set tablespace containers for 7 using (path '/home/db2inst1/TT/6 ')"

 

DB2 restore dB Tianjin continue

(Complete database recovery)

 

Connect to the database and verify the installation.

3. Database movement in different operating systems (db2move)

Because our system uses multiple users and multiple tablespaces, we cannot directly use db2move for restoration. Therefore, we must create a tablespace and table before inserting data.

1. Generate DDL files

Db2look-D database alias-e-p-l-O target file name-I username-W Password

-D specifies the database,-O specifies the target file,-l indicates that the tablespace is generated,-I indicates the user name, and-W indicates the password.

For example, db2look-D jsyrem-e-p-l-O jsyrem. DDL-I zgc3-W zgc

Note: The source database must be catalogued on the local client. The generated files are stored in the current directory.

2. Generate the export file for db2move

Db2move database alias export-l large object storage directory (can be omitted)-u user name-P Password

For example, db2move jsyrem export-l lob-u zgc3-P zgc

Note: The source database must be catalogued on the local client. You do not need to create a directory for storing large objects, which is automatically generated by the system. The generated files are stored in the current directory.

3. Create a target database

4. Create tablespaces and tables in the target database

DB2-tvf DDL file name

For example: DB2-tvf jsyrem. DDL

Note:

① The code pages in the two databases must be set to the same

② Before executing the command, you must modify the DDL file and set the connection in it to connect to the target database (multiple connections may need to be set in the file ).

③ Before executing the command, you must modify the DDL file, set the tablespace address, and specify the storage location for the tablespace created in the target database. These directories may need to be created in advance. (Directories 1, 2 ...... N is automatically generated by the system)

④ The command must be executed in the directory where the generated file is stored.

5. Import Data

Db2move database name import-io insert-l large object storage directory

-Io specifies the import method. If it is set to create, the table is automatically generated when the table does not exist in the Database. If it is set to replace, the original content is replaced. If it is set to insert, only data is inserted; -L specifies the directory for storing large objects.

Db2move jsy2 import-u username-P Password

Note: The command must be executed in the directory where the generated file is stored.

6. Others: Due to the key relationship between database tables, conflicts may occur during data import. You need to write down the conflicting tables and modify db2move. LST file, move the corresponding rows of these tables to the final generation of the file.

7. You can use the Export and Import files to view the system information during data import and export, and use the tablennn. MSG file to view the system information of a table during import and export.

Note: The above operations are intended for Windows clients, and errors may occur in Linux.

Iv. Joint Data Object Creation

Federated data objects can map users and tables in a database to another database. Users can access the latter by accessing the former.

Procedure:

1. Preliminary work 1: configure the database instance name parameters and set federated under "management" to ""; yes

2. Preliminary work 2: Code the source database to the client

3. Create a wrapper in the federated database object. (Select drda for the ing package name of DB2, and use the default db2drda. DLL for the database name)

4. Create a server. (For the name of the remote data source, enter the database code alias. For the ing server type of DB2, select DB2/UDB, select the correct version number, and enter the user ID and password of the target database, the remaining content can be used by default)

5. Create a user ing. Select remote user and local user for ing.

6. Create an alias ing, that is, table ing.

5. Common commands in DB2

1. db2set command

Db2set: view common DB2 settings

Db2set-LR: View All DB2 Variables

 

Db2set variable name = variable value: Set the variable in DB2. For example, db2set db2codepage = GBK; db2set db2country = zh_cn. (This is the two most commonly used settings. Set codePage to gbk gb library and country to zh_cn China. After setting, the error code page occurs during database creation and backup. Once the settings are complete, stop using DB2 terminate)

2. view database configurations

View Database Manager Configuration: DB2 get dBm cfg

View the configuration of a database:

Two methods:

1. DB2 get dB CFG for database name. For example, DB2 get dB CFG for Tianjin

2. Connect to the database with the connect command, and then use DB2 get dB cfg. For example:

Connect to Tianjin (or: connect to Tianjin user zgc3 using zgc)

DB2 get dB cfg

You can view the configurations of only one of them, for example, view territory.

DB2 get dB CFG for Tianjin | grep terr

Modify Database Configuration: DB2 update dB CFG using parameter value

3. database backup, database creation, and database recovery

Database Backup: DB2 backup database name to backup location (DB2 users must have permissions on the backup location ). For example, DB2 backup database Tianjin to/home/db2inst1/backup. Note: When backing up a database, apply DB2 connect reset to remove all connections.

Database creation: name of the DB2 create database. For example, DB2 create database dbname

Create a database with a specific character set: DB2 create database name using codeset [codeset] territory [terriroty]. For example, DB2 create database dbname using codeset GBK territory CN

Database restoration instructions: if there is only one backup file in the folder, you do not need to write a taken. If an error occurs during database recovery, you can use Restore database dbname continue/abort To continue and cancel the restoration.

Alternatively, when creating a database on the console, select PRC (People's Republic of China) in Step 6 region)

4. Other Common commands

Db2move: port databases in different operating systems. However, files must be edited due to foreign key constraints.

Db2level; view the DB2 Revision

Db2look: Export DDL?

DB2 List table/tablespaces/dB ...... List corresponding content

(See the IBM Redbook for details)

5. Add and view nodes and database catalogs on the client

Add node catalog: DB2 catalog TCPIP node name remote node IP address server 50000

View node catalog: DB2 list node directory

Delete node catalog: DB2 uncatalog node alias

Add database cataloguing: DB2 catalog dB remote database name as database alias at node name

View database catalog: DB2 list dB directory

Delete database catalog: DB2 uncatalog DB database alias

6. db2move ):

Export: db2move dbname Export

Import: db2move dbname import (-io replace/create-u username-P password)

Load: db2move dbname Load

Note: run the Export command to store the generated file in the current directory. dbname is the alias of catalog. Import also reads files from the current directory. When importing a database into a new database, you should first create a database and then db2move the new database name import ....... -The IO parameter indicates that the table in the imported database overwrites/creates a new database, and-U and-P indicate the user name and password used for the database creation.

7. Remote database operations

DB2 attach to database alias User Username using Password

You can operate a remote database locally (create dB, drop dB, and other operations)

Note: The database to be operated must be catalogued locally.

To cancel the attach command, run DB2 detach or attach to another database.

8. Save the console information for database operations to the document

Command name to be executed> console information file name

9. DB2 help

DB2? The content to be queried (such as: DB2? Sql30082n)

Or directly enter the command (db2move) without Parameters

10. Stop Application

Errors may occur during database operations, resulting in unavailability of the database. You can use the force application command to stop this operation. The command format can be DB2 force application number (stop a single application) or DB2 force application all (stop all applications.

11. Others

Vi. Suggestions on System Optimization

1. Add buffpage Configuration

2. Add sortheap configuration (generally 4 MB or above)

3. Set locktimeout to on or yes.

4. Recovered logs are retained.

(For specific settings, see the IBM Redbook)

Command:

DB2 connect to Tianjin

DB2 update dB CFG using logretain Yes

DB2 backup dB Tianjin // back up the database after logretain is set

/Home/db2inst1/sqllib/bin // enter this directory

Db2empfa Tianjin; // multi-page file allocation Enabled

DB2 update dB CFG for Tianjin using buffpage 25000 // or more

DB2 update dB CFG for Tianjin using locklist 1000;

DB2 update dB CFG for Tianjin using locktimeout 15

DB2 update dB CFG for Tianjin using sortheap 1000 // or more

DB2 update dB CFG for Tianjin using logfilsiz 10000

DB2 alter bufferpool ibmdefaultbp size-1

DB2 update dBm CFG using sheapthres 25000 // Max to half of the total mem.

7. Start the DB2 console in the root window

1. Log On With db2inst1: su-db2inst1 (-indicates that the environment variable of db2inst1 is read at the same time)

2. Export display = 127.0.0.1: 0.0

3. xhost +

4. db2cc

8. Data Export

1. Export to 'C:/ur_policise.del 'of del select * From zgc3.ur _ Enterprise

2. Export to 'C:/ur_enterprise.ixf' of ixf select * From zgc3.ur _ Enterprise

9. View Node

1. List node directory

2. delete node :? Uncatalog

10. Sequence Creation

Create sequence "zgc3". "sp_seq_mo_wbk" as integer

Minvalue 0 maxvalue 2147483647

Start with 21 increment by 1

Cache 20 no cycle no order;

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liu_xing_hui/archive/2008/11/20/3340522.aspx