In DB2, in an instance, a database, a TableSpace, a ContaSpace, and the like: In an operating system, DB2 data services can run multiple instances at the same time (different from Oracle in one system) Only one instance can only be used), the database is defined in an instance, and an instance can contain multiple databases. Different databases in the same example are completely independent and have their own independent system catalogs. Table space is divided into DMS mode and SMS (System Manegement Space), defined in the database, and there must be two system basic tablespaces in a database, namely system catalog table space (Syscatspace) and system temporary table space (SystemPspace) . Any object created in the database is embodied in a manner in which the record is added in the system catalog table space. For temporary tablespace, its occupied disk size is dynamically expanded according to the usage, that is, allocating disk space only when needed, and Recycling after use. In addition, if the user needs to create a table, you need to create a user table space (UserSpace) If you need to use a temporary table, you need to create a user temporary table space (UserTempSpace). The DMS is specified when the SMS type is established in the table space, and it cannot be converted after it is built. For DMS mode, a table space corresponds to one or more containers (Container), and the container specifies the physical storage location of the data. For SMS mode, you can only specify a directory and cannot be increased. The container is divided into three types. The first two are files and devices for tablespaces for DMS methods; there is a directory for tablespaces for SMS mode, this method does not require manual management data storage files, DB2 can Automatically add storage files in the directory according to the situation, as long as the disk space is allowed. In essence, the tablespace is the logical location definition of data storage, and the container is the physical location definition of data storage. The performance of a database is mainly based on the following factors: Disk (DISK), Memory, Processor (CPU), Network. Among them, the magnetic disk is most significant, 90% of the performance bottleneck may come from the disk IO competition; second is memory, one side refers to the total amount of physical memory to meet the demand, and on the other hand, the configuration parameters related to the memory should be correctly configured Of course, the performance of the processor is also very important. How many CPUs will have a significant effect on which complex SQL queries dependent computing power; the network is not mainfaster, which is an objective environmental factor, refers to the slow network speed. The transmission of data has affected. The following is listed below: Methods for increasing database performance: Configure multiple physical disks for servers running database services, and the capacity of each block does not have to be too large, which can effectively share data storage and read operation processes IO competition. That is, a multi-small-capacity disk is superior to only one large-capacity disk. If the conditions are allowed, try to make the data storage service and the operating system on the physical separated disk. Table space management method in DMS (Database Management Space). Create multiple tablespaces on different disks. You can then store data and indexes in different tablespaces, which can significantly improve performance. It is also possible to remove one of the frequent large blasters into multiple small tables, which are stored in different tablespaces, and then combined with one view. DB2 servers can manage naked devices, that is, in addition to the system and the DB2 service running the disk, to store the DB2 data separately, can be multi-block, no formatting after the partition, create a naked device to DB2 directly to DB2 for management, Storing data.
The temporary table space of the system has a lot of database performance. When the management of physical memory cannot meet the needs of database operations, the temporary data will be written to disk, and the system temporary table space is used, and this The situation will occur frequently. Try to place data from the position of the disk near the innermost track because the access speed of the disk is faster. The parameter configuration of DB2 is divided into two levels, one is an instance level, and the other is the database level. Parameters that have greater impact on data service performance are mainly configured in the database level. The following is three important memory configuration parameters: bufferpage: Share by all objects in the same database. SortHeap: Memory switching area for sorting, non-sharing, should not set too much, otherwise, it is easy to cause memory consumption, because each transaction will apply for independent memory for sorting. LockList: Shared memory and is used to record the lock created in the data service run. It is recommended to set up about 20MB and adjust it according to the actual situation. DB2 uses a row lock using a row lock. If the setting is too small, when the lock record is too much, it will cause insufficient memory. At this time, the DB2 will upgrade multiple line locks to a table lock, which will greatly reduce the concurrency performance of the application. . If the setting is too large, the multi-allocated memory will rarely be used, resulting in waste. Some other configuration parameters: NUMDB: Simultaneous instance number number DB2 can be started: DB2IList lists the DB2 List Database Directory in the current system DASDB2 LIST DATABASE DIRECTORY Lists Database Databasespaces defined in the current instance DB2 List TableSpaces List the table space defined in the current database DB2 list tabses [for all] lists the table DB2 List Active DB lists the current database DB2 GET DBM Configget DB CFG for DatabaseNamedb2 Update DB CFG for DatabaseName Update DB CFG for DatabaseName Update DB CFG for DatabaseName Using Bufferpage 600MDB2 Alter BufferPool IABMDEFAULTBP SIZE = 1DB2 List Applications Show Detail Order You can add "Show Detail" parameters later to display details. The page size of DB2 data store can only be specified in the tablespace level (different from Oracle, which can be defined in the table level) and cannot be modified after it is built. You can handle a page size of 4K DMS user temporary table space, and then delete the system's default SMS system temporary table space. In order to meet the application needs, a user temporary table space of 8k or more should be established again. DB2 UDB V8.1 is not well supported by Redhat Linux 9. The GUI installer cannot be started by default (can be set up by setting the environment LD_Assume_kernel = 2.2.5), and the SAMPLE database will not be installed, the control center will not start normally. When using a count () function, if the record number of records in the table is> 2 147 483 647, the function may return the result of the error, at which point the return type Decimal (31, 0) can be used. The DistINCT keyword can be used in a count () function, such as: select count (Distinct ID) from Table, which will not count the repetition value of the ID column. After the ORDER BY clause, if multiple column names are written, you need to specify ascending or descending order. You can temporarily turn off the log options for the Table of the LOAD.
Use: ALTER TABLE ... ACTIVATE NOT Logged InitialLyDB2 Several Special Registers: Current Date, Current Time, Current TimeStamp, User (User ID). Date: Current TimeStamp 2 Days (or Hours, Seconds, Months, YEARS, ETC.) Use of CASE statement: Case WHEN Condition One Then Action One ELSE Action Two End; You can use it. The ORDER BY clause cannot be used in the creative statement of the view and the fetch n rows clause. But for the order by the following methods, it can be used instead of implementation, but it will affect efficiency. Create View V_Name1 (C1, C2, C3) Asselect * from (Select Column1, Column2, Column3From T1RDER BY Column1) AS T1;
The tablespace type is divided into SMS and DMS, which are System Management Space, Database Management Space. SMS is easy, simple, no need to manually create and maintain data storage files. DMS needs to manually specify the filename of Container and store data, and ensure that there is enough disk space available. For a database, there is at least one system temporary table space with 4K, which can additionally create a user-temporary table space with larger Page Size, and the system will be used automatically. Unable to change the data type of a field with an Alter statement, you can change the data length for some fields, which is more than the limit on Oracle and DB2. You can use: SELECT Expression from sysibm.sysdummy; alternative, the following statement is equivalent: VALUES expression; table and view creation, update, delete operation, write log, so you can commit or rollback. In the UPDATE statement, this field does not re-execute the expression defined in the default value if there is no value to default to a field defined by default. In order to re-execute the expression of the default value, the following method can be employed: Create Table T1 (C1 VARCHAR (32), LastUpdateTime with default current TIMPSTAMP); Update T1 set c1 = 'new string', lastupdatetime = default; for DB2 The database can specify the CODEPAGE parameter when creating, and cannot be modified after being created. When the application accesses the database, DB2 will compare whether the CodePage of both is consistent, and inconsistencies, the code page is automatically converted. In order to reduce the overhead brought about by the conversion, the code page used by the application is consistent with the database. You can set up the CodePage of the DB2 CLP tool, use: DB2SET DB2CODEPAGE = 1386, in this example is the value corresponding to the Chinese GBK character set in the Windows platform. Note that this numeric value is defined by DB2. The code page values corresponding to the various character sets in the corresponding platform can be found in the IBM website. In DB2 CLP, actions for remote databases: DB2 catalog tcpip node local_node_name remote hostname | IP server service_name first maps the remote host to a local node, the node name is specified, and this example uses TCPIP connections. Service_name is typically defined in the / etc / service file of the remote host. DB2 Catalog Database DB_NAME AS LOCAL_ALIAS AT LOCAL_NODE_NAME User User_Node_name User User Using Password Then map the database of known remote hosts to the local alias, pay attention to the local alias cannot be repeated at the host level. The node name specifies the node of the above-mentioned college entrance.
DB2 Connect To Local_Alias UserName Unsword Connect to the Alias Connection Remote Host Database DB2 Get Configuration Show Detail by the Alias Get Configured Database Database Database Details Configure the error number returned to DB2, you can use the following method to view the instructions (take SQL 10008 as an example): DB2? SQL10008 Database Backup and Recovery: Using Backup and Recovery Tools to complete the work of different servers see the full transfer database, the command line is as follows: Backup DB2 Backup Database DB_NAME User_name Using Password to backup_dir_namedb2 Backup Database DLHDB User DLH Using admindlh tO d: / backups restore DB2 rESTORE DATABASE source_db_name USER user_name USING password FROM backup_dir_name TAKEN AT backup_file_create_time tO driver_letter INTO new_db_nameDB2 rESTORE DATABASE dlhdb USER dlh USING admindlh FROM d: / backups TAKEN AT 20031209141056 tO d: operation INTO newdb For example: setting Default Example Environment Variable DB2 SET DB2INSTANCE = INST_NAME Start Current Instance DB2Start Stop Current Instance DB2Stop [Force] Connect to an instance DB2 attach to an. Including DB2LOOK -D SAMPLE (Database) -A -E -E -L -X -M-F -O (parameter) Samplesql.out (output file) load data, such as table, view, function, database parameters, etc. Causes the table space in the BACKUP PENDING (0x0020) state. For example, in the field of shaping data load to Double type. Table space in the Backup Pending state cannot be accessed. You can return to the normal state (0x0) by running a Backup operation on this table space. For the self-integrated field, it can be specified in two ways: generated by default as IdentityGenerated ALWAYS AS IDENTITY difference is that the first mode allows manually specifying the value of the self-add field when inserting data, as long as it is not repeated, and the database will automatically Set the next value; the second method is not allowed to be specified, and can only be assigned and inserted by the database. DB2 SQL statement use: Select * from T1 WHERE A LIKE '% ABC /% DEF' Escape '/' When the File type, the back long-Num parameter is not correct, such as 25600 represents 256MB, but if specified 256 will cause the above error.
Platform RH Linux 8DB2 UDB V8.1 creates data source to DB2 in WAS 5, but the connection fails, returns the following error: [servlet error] - [SQLConnect]: java.lang.unsatisfiedlinkerror: SqlConnect reason is not to run WAS service the user sets the following environment variables: LD_LIBRARY_PATHLIBPATHDB2INSTANCE ... more environment variables defined in the $ INSTHOME / sqllib / db2profile file, the solution may be adopted:. sh stopServer.sh servername $ INSTHOME / sqllib / db2profilesh startServer.sh servername can also put DB2PROFILE is first executed in the WAS boot script. If only two environment variables of LD_Library_Path, libPath are set, DB2 returns the following error: CLI0600E Invalid Connection Handle or Connection Is Closed.sqlState S1000 corresponds to the JOB package function of Oracle, DB2 is implemented through a GUI tool-task center (task center) . You need to make the necessary tool settings before using the task center, you need to create some database objects, you can create a database in an existing database or you can create a database separately. Implement: CREATE CATALOG Tools Schema_name CREATE New Database DB_NAME This command creates a database named DB_NAME and specifies a model name. Note: You cannot specify a CODESET with the USING clause, and the system will use the ISO8859-1 character set by default. Quest Center for DB2 With database performance diagnostics, dynamic monitoring DB memory, disk IO, tablespace, load, etc. DB2 Client Type: DB2 Runtime Client DB2 Runtime ClientDB2 Manage Client DB2 Administrator Client (All Contents for Runtime Client) DB2 Application Development Client DB2 Application Development Client (including all content of the management client) DB2 thin client DB2 Thin ClientDB2 Relational Connect Federal Database for connecting a heterogeneous database.
DB2IDrop -f instance_name enforces disconnected, stop the instance and delete. DB2Imigr instance_name is used to migrate instances under UNIX. DB2IUPDT Instance_Name Update instance, used for access to some new product options or revisions. DB2 GET Instance Gets the instance of the current location. Once the instance level or the database level is updated, some can take effect immediately, and some need to restart the instance to take effect. Immediate explicitly indicated changes to the change immediately, and DeferRed explicitly indicates that the change will take effect after the instance is restarted. When you need to configure a plurality of clients to connect to the DB2 server, you can use the Configuration Assistant to export the server's profile, and then import the profile using the Configuration Assistant using the Configuration Assistant. If the client is only installed a runtime client, you can import a profile using DB2CFIMP Access_profile_name. Doubt: Should a profile is exported by a configured client? DB2 List Applications lists all database connections DB2 Force Application (2) Terminate the specified connection handle, the transaction is interrupted and rolled back. At the same time, the plurality of handles can be separated by commas, or specify the keyword ALL. This command only terminates the specified connection and does not block new applications from connecting to the database. When you create a database, you can specify a sort mode: The Collate Using Identity system mode set is created with each database, and they are placed in the syscatspace tablespace: SYSIBM: Basic system directory recommends not to directly access Syscat: public is granted to this mode SELECT Permissions Catalog This is a recommended way to get directory information SysStat: Update Directory View - Impact Optimizer Sysfun: User-defined Functions If the existing row in the table does not satisfy the constraint, the constraint cannot be defined. The constraint check can be closed to speed up the addition of large amounts of data, but the table is at the check pending state. When you create a table, you can use the option to specify one or more tablespaces, tables, and indexes will be placed in it: Create Table Test (Column 2 Definition, Column 2 Definition, ...) in
