Learn to use DB2 commands and Fault Diagnosis

I feel that the answers to many basic questions can be obtained through the help of DB2, because DB2 itself has very detailed help information after installation, so I think it is necessary to summarize how to get help from the DB2 command syntax!

We will discuss DB2 8.2 In aix5.2 or WINXP, which focuses mainly on maintenance. I am not easy to learn, I am not very involved in the database, and I hope you can correct and supplement it!

Directory:
I. How to learn to use DB2 commands
1. view the illustration
2. Use DB2 commands
3. Search for materials
Ii. Fault Diagnosis
1. Types of error messages
2. Confirm the error message.
3. View db2diag. Log
4. Collect and analyze db2trc tracking information


I. How to learn to use DB2 commands

1. view the illustration

When using DB2 help and reading various documents, you may encounter command interpretation diagrams. Therefore, to learn how to use DB2 commands, you must first understand the two commonly used command diagrams in DB2 help.

Command syntax exists in the form of an image. The figure shows the operations that can be performed using special commands, and shows the relationship between different values of the same option before and sometimes. Commonly used syntax diagrams in DB2 have two different types: track charts and BNF (Backus-Naur format) charts.

==> How to read a track chart

Each track chart starts with two right arrows and ends with a pair of left and right arrows. Rows starting with a single right arrow are continuation rows. You can read the track chart from left to right, from top to bottom, and in the direction of the arrow.

Other conventions for track charts are as follows:

>-A--B--C -----------------------------------------------------> <

You must specify values A, B, and C. The value must be displayed on the primary line of the track chart.

>-+ --- + -------------------------------------------------------> <
'--'

You can specify. Display the optional values under the main line of the track chart.

>-+-A-+ -------------------------------------------------------> <
+-B-+
'-C -'

Values A, B, and C are optional. You must specify one of them.

>-+ --- + -------------------------------------------------------> <
+-A-+
+-B-+
'-C -'

Values A, B, and C are optional. You can specify one of them.

.-,-----.
V |
> --- +-+ ---------------------------------------------------> <
+-A-+
+-B-+
'-C -'

You can specify one or more values of A, B, and C. Any required delimiter (in this example, it is a comma (,) of multiple or repeated values is displayed on the arrow.

.-+ --- + -.
| '-,-' |
V |
> --- +-+ ---------------------------------------------------> <
'--'

You can specify value a multiple times. The separators in this example are optional.

.--.
>-+ --- + -------------------------------------------------------> <
+-B-+
'-C -'

Values A, B, and C are optional. You can specify one of them. If the displayed value is not specified, use the default value A (the value displayed on the main line ).

>-| Name | ------------------------------------------------------> <

Name

| -- A -- + --- + ----------------------------------------------------- |
'-B -'

The track fragment names are displayed in the main track chart.

Punctuation Marks and uppercase values are exactly specified as shown in.
The lower-case value (for example, name) provides your own text to replace the name variable.

This command interpretation method is often used in the DB2 online documentation. For example, the usage of the "DB2 list history" command is described as follows:

Code:

>-List history ---- + ------------------- + --------------------------> +-Backup ------------ + +-Rollforward ------- + +-Alter tablespace -- + +-Dropped table ----- + +-Load -------------- + '-Rename tablespace -' > ----- +-All -------------------------------- + --------------------> +-Since -- timestamp ------------------- + '-Containing -- +-schema. object_name-+ -' '-Object_name --------' > ---- For -- + ---------- + --- database-alias ------------------------> < +-Database-+ '-DB -------'

==> How to read the BNF Diagram

In BNF, write the syntax in the form of rows. Square brackets ([and]), curly brackets ({And}), and vertical bar signs (|) are used to describe the syntax. All these symbols are called as controls.

Spaces are used to separate the syntax elements from square brackets, curly brackets, and vertical bars. Use the "read next word" (usually Ctrl + right arrow) and "read previous word" (usually Ctrl + left arrow) of your screen reader) move between the continuous BNF control and the syntax element.

The characters (for example, Parentheses, (and) and comma-separated separators) that you need to include when constructing a command are located at the point where you need to enter them. For example, you can read the left parenthesis before the Option List. In someProgramming Language(Such as C, C ++, and Java), square brackets, curly braces, and vertical bars are used in the language. To simplify the identifier, square brackets, curly brackets, or vertical bars appear in the syntax diagram as part of the programming language, there is an escape character \ (backslash) before it ). For example, if you hear \ {, you know that {is actually input at this point when constructing a command, and this {is not part of the syntax graph structure.

The BNF syntax works as follows:

· Braces {And} are used to represent a set of syntax elements, just as (and) are used in algebra. For example, auth ({userid | systemid}) indicates that userid and systemid are a set of options for the auth keyword. Note that the syntax in this example contains the values that you need to enter outside (and) of the selected options ). If you select the userid option, your command will be auth (userid ).
· Square brackets [and] indicate optional structures. The optional structure starts with [and ends. Square brackets [and] And curly braces {And} can be used in the same group of syntax elements. For example, the syntax [auth ({userid | systemid})] contains square brackets, which means you can omit the keyword auth and its options from the constructed command.
· Vertical bar | yes or mark and indicates backup. | All construction on the right is | the standby structure on the left. | The scope is bounded by the end of the group or structure (displayed as] Or}) or the end of the syntax. Vertical bars apply to all items in a group, not just those closely adjacent to the bars. For example, {A | B c} Means "A, B, or C ". If you want to indicate "A and C or B and C", the correct syntax will be {A | B} C.
· The exclamation point on the side of the value! Indicates the default value in the backup set. For example, auth ({! Userid! | Systemid}) indicates that userid is the default option of the auth keyword. Note the location of any square brackets [and] that contain the default value of the constructor. If the square brackets are outside the entire constructor, rather than outside the standby set, for example, [auth ({! Userid! | Systemid})], which means the default value applies to the entire structure. If you omit the auth keyword from the completed command, the default auth (userid) is automatically included ). However, if square brackets exist outside the entire constructor and the standby set, for example, [auth [({! Userid! | Systemid})], which means that the default value is only applicable to the backup set. In this case, if you omit the auth keyword from the completed command, the default setting is not included. If you include the auth keyword but do not specify the option you selected, the default option userid is applied.
· Ellipsis... following the constructor means that the given syntax elements in the constructor can be repeated. For example, {Host | state | type}... means that you can contain more than one keyword host, State, and type. Note: If the constructor only contains one syntax element, you can repeat the same syntax element multiple times. However, if you construct an element that contains more than one syntax, you can use more than one element, but you cannot use each element multiple times. In the above example, you can write the host state, but you cannot write the host.
· The syntax element that starts and ends with % (percentage) means that the element is the name of the syntax fragment, not the text. It indicates a reference defined elsewhere. For example, % OP1 % means that you should reference a single syntax segment op1. In the syntax for obtaining this example, OP1 provides a list of further options.

In DB2 CLP, this is the main command description method. For example, the preceding "DB2 list history" command is described as follows:

Code:

Cutester-mymm> DB2? List history List history {backup | rollforward | reorg | Create tablespace | alter tablespace | dropped table | load | Rename tablespace | archive log} {All | since timestamp | containing {schema. object_name | object_name }} For [database] database-alias Note: from the operating system prompt, prefix commands with 'db2 '. Special characters may require an escape sequence (\), for example: DB2 \? Change Database DB2? Change Database XXX comment with \ "text \"

2. Use DB2 help in CLP

There are two cases: DB2 commands and DB2 SQL commands can both use "DB2? Command to get help, such:

Code:

Cutester-mymm> DB2? Prune Prune history timestamp [with force option] [and delete] Prune logfile prior to log-file-name Note: from the operating system prompt, prefix commands with 'db2 '. Special characters may require an escape sequence (\), for example: DB2 \? Change Database DB2? Change Database XXX comment with \ "text \"

The following commands under the DB2 CLP are collected. If you are interested, you can use them "? "Check it and you will learn a lot!

A brief summary of DB2 CLP commands:

Code:

CLP session control "LIST Command Options" "Update Command Options" "Change isolation level" "Set runtime degree" "Terminate" "Quit" Database Manager Control "Start Database Manager" "Stop Database Manager" "Get Database Manager Configuration" "Reset Database Manager Configuration" "Update Database Manager Configuration" "Autoconfigure" Database Control "Restart database" "Create database" "Drop database" "Migrate Database" "Activate database" "Deactivate Database" "Quiesce" "Unquiesce" "List indoubt transactions" "List drda indoubt transactions" "Get Database Configuration" "Reset database configuration" "Update Database Configuration" "Autoconfigure" Database Directory management "Catalog Database" "Uncatalog database" "Catalog DCS database" "Uncatalog DCS database" "Change Database comment" "List database directory" "List DCS directory" ODBC Management "Catalog ODBC Data Source" "List ODBC Data Sources" "Uncatalog ODBC Data Source" "Get CLI configuration" "Update CLI configuration" Client/Server Directory management "Catalog local node" "Catalog Named Pipe node" "Catalog APPC node" "Catalog Appn node" "Catalog NetBIOS node" "Catalog TCP/IP node" "Uncatalog node" "List node directory" Network Support "Register" "Deregister" "Update LDAP node" "Catalog LDAP database" "Uncatalog LDAP database" "Catalog LDAP node" "Uncatalog LDAP node" "Refresh LDAP" DB2 Administration Server "Get admin configuration" "Reset admin configuration" "Update admin configuration" "Create tools catalog" "Drop tools catalog" Recovery "Archive log" "Backup database" "Reconcile" "Restore database" "Rollforward Database" "List history" "Prune history/logfile" "Update history file" "Initialize tape" "Rewind tape" "Set tape position" Operational utilities "Force Application" "List packages/Tables" "Reorgchk" "Reorg indexes/table" "Runstats" Database monitoring "Get monitor switches" "Update monitor switches" "Get Database Manager monitor switches" "Get snapshot" "Reset monitor" "Inspect" "List active databases" "List Applications" "List DCS Applications" Data utilities "Export" "Import" "Load" "Load query" Health Center "Add contact" "Add contactgroup" "Drop contact" "Drop contactgroup" "Get alert configuration" "Get contactgroup" "Get contactgroups" "Get contacts" "Get description for health indicator" "Get health notification contact list" "Get health snapshot" "Get recommendations" "Reset alert configuration" "Update alert configuration" "Update contact" "Update contactgroup" "Update health notification contact list" Application preparation "Precompile" "Bind" "Rebind" Remote Server utilities "Attach" "Detach" Table space management "List tablespace containers" "Set tablespace containers" "List tablespaces" "Quiesce tablespaces for table" Database partition management "Add dbpartitionnum" "Drop dbpartitionnum verify" "List dbpartitionnums" Database partition Group Management "List database partition groups" "Redistribute database partition group" Data Links "Add datalinks manager" "Drop datalinks manager" "List datalinks managers" Additional commands "Describe" "Echo" "Get authorizations" "Get connection state" "Get instance" "Get routine" "Help" "Ping" "Put routine" "Query client" "Set Client"

Another case is that for DB2 system commands, such commands do not have a uniform way To get command help. The best way is to check command reference. but many commands can use "-h" or "-? "Parameter help, such as db2icrt, db2idrop, db2licm, db2pd, and db2support. However, there are also many commands that do not need to add parameters that cannot be used in the preceding method, for example: db2stop, db2start, and db2ilist, but with "-h" or "-? "There will only be an error message, but some may not be easy to try, such as db2_kill. It is best to read the manual! These commands exist in the sqllib/bin directory under the instance directory. If you are interested, please take a look!

3. Search for materials
Several documents help scholars learn and find instructions: command reference, SQL reference volume 1, and SQL reference volume 2.


Ii. Fault Diagnosis

1. Types of error messages
During maintenance, you can use the error code returned by DB2 to identify the error. The error information provided by DB2 is prefixed with the code to identify the error categories, which are divided into the following categories:

Code:

ADM messages generated by using DB2 components. Asn messages generated by DB2 Replication Aud messages generated by the DB2 audit facility. CCA messages generated by the client configuration Assistant CLI messages generated by call level interface DBA messages generated by the database administration tools DBI messages generated by installation and configuration DBT messages generated by the database tools DB2 messages generated by the command line Processor Dia diagnostics messages generated by using DB2 components. DWC messages generated by the Data Warehouse Center Dxx messages generated by the XML Extender Gov messages generated by the DB2 Governor utility. GSE messages generated by the DB2 spatial Extender messages generated by the Information catalog Center Sat messages generated in a satellite environment SPM messages generated by the sync point manager SQL messages generated by the database manager when a warning or error condition has been detected.

2. Confirm the error message.

One way to determine the meaning of the error code is to view the document Message Reference volume 1 and Message Reference Volume 2. Another method is the most commonly used and convenient method, use CLP to query the code meaning. For example, if sql0100w is reported during table delete, You can query the Code as follows:

Code:

Cutester-mymm> DB2? Sql0100 Sql0100w no row was found for Fetch, update or delete; or Result of a query is an empty table. Explanation: One of the following conditions is true: O no row was found that meets the search conditions specified In an update or delete statement. O the result of a SELECT statement was an empty table. O A fetch statement was executed when the cursor was positioned After the last row of the result table. O the result of the select used in an insert statement is Empty. No data was retrieved, updated, or deleted. User response: No action is required. processing can continue. Sqlcode: + 100 Sqlstate: 02000

3. View db2diag. Log
Sometimes SQL code queries do not intuitively show the fault, but the DB2 diagnosis log is required. For example, an error is reported during db2start:

Code:

Cutester-mymm> db2start 04/20/2005 11:59:47 0 0 sql5043n support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully. Sql1063n db2start processing was successful.

Check sql5043 directly and you cannot determine the problem. If you check db2diag. Log, it is clear:

Code:

11.59.46.112.167 + 480 e33845468c447 level: Error PID: 25032 TID: 1 proc: db2sysc 0 Instance: db2inst1 node: 000 Function: DB2 UDB, common communication, sqlcctcpconnmgr, probe: 50 Message: adm7006e the svcename dBm configuration parameter was not Configured. Update the svcename Configuration Parameter Using Service name defined in the TCP/IP Services file.

You can change the svcename!

The DB2 diagnostic log exists in sqllib/db2dump under the instance directory (default in UNIX, Windows exists in the instance directory). You can modify the DBM parameter diagpath to change the diagnostic log directory, this directory also contains dump and trap files. The diagnostic log has five diagnostic levels:

Reference:

0 -- no diagnostic information is recorded 1 -- Record only serious errors 2 -- Record all errors 3 -- Record all errors and warnings (default) 4 -- Record all error, warning, and information Information

The diagnosis level can be changed through the DBM parameter diaglevel. If you want to collect more information, you can change the level to 4, but the cost is db2diag. log is growing too fast. Please note that the file system space should not be full! Set it to 3!

The following is an example of the db2diag. log file, which can help you understand the file:

Code:

(1) 2002-05-17-17.30.32.140000 (2) instanceb2mpp (3) node: 000 (4) PID: 2204(db2bp.exe) (5) TID: 2224 (6) appid: * local. db2mpp. 020517213032 (7) database_utilities (8) sqlubckp (9) Probe: 26 Diagdata (10) 2cfc FFFF 2002-05-17-10917.109793000 instanceb2mpp node: 000 PID: 596(db2syscs.exe) TID: 2176 appid: Base_sys_utilities sqlemergesqlca Probe: 20 Database: Sample Received sqlcode 1496 for request 8000001e from node number 1 (11) Data title: sqlca PID: 596 TID: 2176 node: 000 Sqlcaid: sqlca sqlcabc: 136 sqlcode: 1496 sqlerrml: 0 Sqlerrmc: Sqlerrp: sqlesrsu Sqlerrd: (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000001 Sqlwarn: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) Sqlstate: 1. indicates the timestamp when this log is recorded 2. Instance name. In this example, the Instance name is db2mpp. 3. Partition Number. In a single-partition database, the total value is 0. 4. Application or proxy process ID. 5. Application or proxy thread ID. This value is valid only on Windows platforms. 6. Application ID. This value corresponds to the output of the List applications command. Each application has a unique application ID. 7. component name (component ). 8. Name of the function module that reports an error or information. The function module belongs to the component above. 9. Probe point of the function module. corresponding to the function module that returns errors and informationSource codeLocation. 10. diagnostic information. in this example, db2diag. log files are from windows, so the dump information is in reverse byte order. to convert this information to sqlcode, You need to convert 2cfc FFFF to FFFF fc2c and convert it from hexadecimal to decimal. note that not all values can be converted to valid sqlcode.

The preceding log file is under db27.2. In db28.2, The db2diag. log format is improved to make it easier to read:

Code:

5.26.53.418280 + 480 i11170c296 level: Event PID: 43018 TID: 1 proc: db2flacc Instance: db2inst1 node: 000 Function: DB2 UDB, config/install, sqlflogupdatecfgparam, probe: 30 Change: cfg dbm: "svcename" from: "" to: "db2_db2inst1" 5.26.55.895210 + 480 i11467c395 level: Warning PID: 20886 TID: 1 proc: db2sysc 0 instance: db2inst1 node: 000 function: DB2 UDB, routine_infrastructure, sqlerkillallfmps, probe: 5 message: bringing down all db2fmp processes as part of db2stop data #1: hexdump, 4 bytes 0x2ff21760: 0000 0000 .... 5.26.55.989040 + 480 i11863c354 level: severe PID: 21876 TID: 1 proc: db2hmon 0 Instance: db2inst1 node: 000 Function: DB2 UDB, routine_infrastructure, sqlerfmponetimeinit, probe: 100 Message: diagdata Data #1: hexdump, 4 bytes 0x2ff22690: FFFF fbee

Is this information easier to understand? In addition, 8.2 provides a very convenient log diagnostic analysis tool db2diag. You can view and format the output of db2diag. log as needed. If you are interested, you can use "db2diag-h" to check the help!

4. Collect and analyze db2trc tracking information
The diagnostic logs for problems occurring in the AP are often uncertain, so we need to track the problem with db2trc!