D: / Winnt / System32 / Drivers / etc / service file is parsing to the machine named IP address. D: / Winnt / System32 / Drivers / etc / hosts file is parsing to port names to port numbers. The above two are not DB2, but in DB2.
DB2ADMIN START start DB2 management
Help to a command in DB2 is:> DB2? This command
DB2 Connect to the remote database First steps to build a node:> DB2 Catalog TCPIP Node Name Remote Database Server IP Address Server Port (50000) Second Step Construction One Continued Joint Alias:> DB2 Catalog DB Database Name AT AT Node Node Name Third Step Establishing Joint:> DB2 Connect To Alias User User Name Using User Password Disconfraction: DB2 Connect RESET
A system environment variable is required in DB2: DB2CODEPAGE, the default value is 1386, this value is very important, the client does not connect to the database end when the DB2CodePage of the client is different.
A full name of a table in DB2 is: schema. Table name, the table seeing the different users in the case of a different user is different, and the full name of the table must be used in the SQL statement of the program.
See what procedures are using this database:> DB2 List Application has a field application-handle in killing this reference
Kill a program to use for this database:> db2 force application {all | (Application-hand)}
DB2STOP cannot be executed when the library is used
The parameters of the DB2 command: -t; as an end -f point to a file (ie, the statement in a file is executed, some commands in the Command window can not be executed, you can first build a file with Notepad and then execute it) -v (I don't know what) Note: -v is used to display the currently executed SQL command.
A general user in a Windows NT can be connected to the library, but there is no right to execute the execution of the SQL statement, authorize to a user:> DB2 GRANT SQL statement (for example: select) ON table name To user user name to cancel the user's weight:> DB2 Revoke SQL Statement ON Name from User Name
DB2 logs have two: loop logs and permanent log cyclic logs: There are three file loop written, so it will produce the previous operation records covered. Benefits: The log file size is unchanged, the backup is convenient, but it is not possible to back up online. Permanent log: The log files continue to increase, but the operation record will not be overwritten, and you can back up online.
How to backup:> DB2 Backup DB database name to device name (such as: c: /); recovery:> db2 restore db database name from device name (rollback rollforward, I don't know) Note: RollForWord is the meaning of the front roll, The first time, to maintain the consistency of data, the recovery after online backup, the recovery is a time from the Log log to a time in the log. Database operation can only be processed only if the data consistency is guaranteed.
Connect to the database Times rollback error with the following command: DB2 ROLLFORWARD DB FMISADD TO End of Logs and Complete
Export Database: - ExportDb2Move HADB Export -u Userid -p Password; - IMPORTDB2MOVE HADB IMPORT -U Userid -p Password Import from file name. iXF of IXF CREATE INTO table Data format has two DEL (file formats) and WSF, but the IXF format information is the most complete, containing table structure information, and can recover deleted tables.
SQL INNER / LEFT / Right / Full Join, these concepts have instructions in << Database Subject >>, LEFT is dominated by the left table, Right is mainly based on the right, and the FULL left and right records will be in the results. For example:> SELECT AA, BB FROM DB1 LEFT JOIN DB2 on DB1.ID = DB2.ID
Merger: Merge two or several findings into a field (condition is the field must be compatible) format: select ... union select ....;
Use a temporary table: with tmptable (SELECT ....) - Built a temporary table SELECT field 1, .... from tmptable, another table where .... This temporary table is in another table. ** ORDER BY must appear in the result set and cannot be used in the temporary table.
Intercept string Substr (field name, start position, characters)
Judging whether it is empty character name is NULL
Example: When is it alive for 10,000 days: SELECT DISTINCT DATE ('1980-01-01') 1000 day from a a total of live how many days: Select Distinct Days (Current Date) -days (Date ('1980-01- 01 ')) from a
Type conversion: use CAST, Example: Select Distinct Cast (Current Date As Char (10)) || 'AA' from A
Use: Select Case When Length (RTRIM) = 0 Then 'Unknown Education' Else XL End, RS from A Example: Search a company's age distribution: with tmptable as (SELECT CASE WHEN YEAR (CURRENT DAY) CSRQ <20 THEN 'smaller than 20 years old' When Year (current day) -csrq <25 Then '20 -24 years old 'When Year (Current Day) -CSRQ <30 TEN '25-29 years old' When Year (Current Day) - CSRQ <35 TEN '30-34 years old 'When Year (Current Day) -csrq <40 TEN' 35-39 years old 'When Year (Current Day) -CSRQ <45 TEN '40-44 years old' When Year (Current Day) -CSRQ <50 THEN '45-49 Else 'greater than 50 years old' endas x from a) SELECT X, Count (*) from tmptable group by x; some system built in a database is used to save the library Information, such as: syscat.tables Example: How many userids check how many userids: select count (*) from syscat.tables where type = 't' and tabschema = 'uerid' example: Generate a list of bare libraries Text: Select 'export to' || TabName || '.ixf of ixf select * from userid.' || tabname || ';' from syscat.tables where type = 't' and tabsch EMA = 'userid';
Performance Adjustment: It affects DB2 performance: the performance of the server can be used: 1. Parallelism 2. Reduce communication
Establish index <,>, = first type of search predicate, <>, LIKE second type search predicate, need to use the third type of search predicate,
SELECT * from A * Which field should be written as much as possible.
Blocking concept client requests once, the database returns N records.
Composite SQL (I don't know) Note: means that he is talking to Union, establish a complex SQL statement such as a temporary table.
DB2 will optimize the SQL statement, the more information about the system table, the better the optimization, so the RunStatsrunStats command tells the system, which generally uses this command once when the amount of data is doubled.
The Reorg command is a database finishing, similar to disk fragmentation.
Get various parameters of this instance: (DBM CFG is the entire DB2 parameter, DB CFG is the parameters of a database)> DB2 GET DBM CFG> DB2 GET DB CFG for Database Name Modify Parameters:> DB2 Update DBM CFG Using Parameters You want the number> DB2 Update DB CFG for Database name Using parameter You want to apply for a memory buffer when the first user is connected to the database, the default is 250 pages per page 4K 1M. Buffer pool size <4k> [buffpage] = 250 Modify this value:> DB2 Update DB CFG for test Using buffpage you wants but its effective depends on whether NPages in syscat.BufferPools is -1, to -1 It is valid, otherwise it is subject to NPAGE in syscat.bufferpols. You can use the following command:> select * from syscat.bufferpools General This value should be around 40% of the system memory, too large, make the system are too hard to use virtual memory.
Optimization level of SQL statement: generally take 2 or 5 (highest 9) Default Query Optinization Class
Parallelism setting: Degree = -1 Parallel is completely completed by the operating system. When the operating system has parallel processing, the number of degree = -1 ***** CUP and the hard disk is generally 1: 4 to 1: 6;
User's greatest joint Node:
