DB2 supports the following two types of tablespaces:
1. System Management Memory table space (Sms-system MANAGED STORAGE)
2. Database Management Memory table space (Dms-database MANAGED STORAGE)
Comparison of characteristics of SMS and DMS user table spaces
Features SMS DMS
The ability to dynamically increase the number of containers in a table space n y
Ability to store index data in different table spaces N Y
Ability to store long-form (LOB) data in a separate table space n y
Tables can be dispersed to multiple table spaces N Y
Allocate space only when needed y N
Table spaces can be directed to different types of disk space y N
After creation, the segment size (extent size) can change n n
Default table space:
When the database is created, DB2 will create three tablespaces by default: These are the default SMS modes for the table spaces. They are:
Syscatspace: Include system catalogs
TEMPSPACE1: Saving temporary tables
USERSPACE1: Contains user data
DB2 Basic Concepts
A few concepts from top to bottom in DB2:
Instance (Instance),
Database,
Table Space (tablespace),
Container (Container)
In one operating system, the DB2 data service can run multiple instances at the same time (as opposed to Oracle having only one instance within a system).
Database definition in an instance, an instance can contain multiple databases. The different databases in the same instance are completely independent and have their own separate system catalog tables.
There are 2 ways to manage your table space:
DMS (Database management Space) mode
SMS (System manegement Space) mode
The DMS and SMS methods are specified when the tablespace is established and cannot be converted after it is built. For DMS, a tablespace corresponds to one or more containers (Container), and the container specifies the physical storage location of the data. For SMS mode, only one directory can be specified and cannot be incremented.
Table spaces have the following types:
System catalog Table space (Syscatspace)
System Temp Table space (systempspace)
User table space (userspace)
User Temp table space (usertempspace)
There must be two system basic table spaces in a database, namely the System catalog table space and the system temporary table space. Any objects created in the database are reflected in the way that records are added to the System catalog table space, where the disk size is dynamically scaled according to usage, that is, the disk space is allocated only when needed and recycled after use. In addition, if you need to create a table, you need to create a user tablespace, and if you need to use a temporary table, you need to create a user temp table space.
There are three types of containers:
Files file
Devices Equipment
Directory directories
File and device, table space for DMS;
directory, a table space for SMS, which does not require manual management of data store files, DB2 can automatically add storage files in the directory as appropriate, as long as disk space allows.
In essence, a tablespace is a logical location definition of a data store, and a container is a physical location definition of the data store.
Performance of the database
The main factors that affect the performance of a database are the following:
Disks (disk)
Memory
Processor (CPU)
Networking (Network)
The disk is the most significant, 90% of the performance bottleneck may come from the disk IO competition;
The second is memory, on the one hand refers to the total amount of physical memory to meet demand, on the other hand, the memory-related configuration parameters should be configured correctly;
Of course, the performance of the processor is also very important, the multi-channel CPU will be dependent on the computational power of the complex SQL query has a significant effect;
The network is not the main factor, is an objective environmental factor, refers to the slow speed of the data will affect the transmission. Here are some ways to improve database performance:
The server running the database service can configure as many physical disks as possible, and the capacity of each block does not have to be too large, which effectively shares the disk IO contention between the data store and the read operation. That is, a disk with multiple small capacity is better than a disk with only a large capacity.
If conditions permit, try to keep the data storage service running on a physically separate disk from the operating system, respectively.
Table Space Managed by DMS (Database Management space).
Create more than one table space on a physically different disk. You can then store the data and indexes in separate table spaces, which can significantly improve performance. You can also split a large table that is used frequently into multiple small tables, stored in separate tablespaces, and then federated with a single view.
DB2 server can manage bare devices, except the system and the DB2 service running disk, for DB2 data storage separately prepared disk, can be multi-block, after partitioning does not need to format, after the creation of bare device directly to DB2 for management, for storing data.
The temporary tablespace of the system has a great impact on the performance of the database, and when the physical memory that is managed does not meet the needs of the database operation, DB2 writes the temporary data to disk, and the system temporary table space is used, and this happens frequently.
Try to place the data where the disk is near the inner track, because the disk is accessed faster.
Key performance-related parameters
The DB2 parameter configuration is divided into two levels, one at the instance level and the other at the database level. The parameters that affect the data service performance are mainly configured at the database level. Here are three of the more important memory configuration parameters:
Bufferpagelocklistsortheap
Bufferpage: Shared by all objects in the same database.
Sortheap: Used for sorting the memory swap area, non-shared, should not be set too large, otherwise, it is easy to cause memory exhaustion, because each transaction will request separate memory for sorting.
Locklist: Shared memory used to record locks established in the operation of the data service. It is recommended to set around 20Mb and adjust according to the actual situation when needed. DB2 uses row-level locks by default, and if the settings are too small, when the lock is too large, it can lead to low memory, and DB2 will escalate multiple row locks to a single table lock, which can significantly reduce the concurrency performance of the application. If the settings are too large, much of the allocated memory is seldom used, resulting in waste.
In DB2, the concepts of instance (Instance), Database, table space (tablespace), container (Container) are as follows:
Some of the other configuration parameters:
Numdb: Number of instances that can be started at the same time
Common commands for DB2:
Db2ilist list DB2 instances defined in the current system
Daslist List the DAS in the system
DB2 List database directory lists the databases defined in the current instance
DB2 list tablespaces lists the table spaces defined in the current database
DB2 list tabses [for all] lists the tables in the current database
DB2 list Active DB lists the active database
DB2 Get dbm Config
Get DB CFG for databasename
DB2 Update DB CFG for databasename using Bufferpage 600M
DB2 alter Bufferpool IABMDEFAULTBP size =1
DB2 List Applications Show Detail
The above command can be followed by the "Show Detail" parameter, which shows the details.
The page size of the DB2 data store can only be specified uniformly at the table space level (as distinguished from Oracle, defined at the table level) and cannot be modified after it is built.
You can manually create a DMS user temporary tablespace with a page size of 4K, and then remove the system's default SMS system temporary tablespace. In order to meet the needs of the application, it is generally necessary to establish a user temporary tablespace with a page size above 8K.
DB2 UDB V8.1 support for Redhat Linux 9 is not good, the GUI installer cannot be started by default (can be resolved by setting the environment ld_assume_kernel=2.2.5), and the sample database will not be installed and the control center will not start properly.
When you use the count () function, if the number of records in the table > 2 147 483 647 rows, the function may return an incorrect result, you can use the COUNT_BIG () function that returns a type of decimal (31, 0).
The DISTINCT keyword can be used in the count () function, such as SELECT count (DISTINCT ID) from TABLE, which means that duplicate values for the ID column will not be counted.
If more than one column name is written after the ORDER by clause, you need to specify ascending or descending separately.
You can temporarily turn off log options for a table when you load large amounts of data. Use: ALTER TABLE ... ACTIVATE not logged initially
Several special registers for DB2: current DATE, current time, current TIMESTAMP, user ID.
Operation on 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; Can be used under a set.
The ORDER BY clause cannot be used with the fetch n rows clause in the view's creation statement. However, the order by can be replaced by the following method, but it will affect efficiency.
CREATE View v_name1 (c1, C2, C3) as
SELECT * FROM (
Select Column1, Column2, Column3
from T1
Order by Column1) as T1;
The tablespace types are SMS and DMS, respectively, System management space, database management space. SMS is easy to use and simple, without the need to manually create and maintain data storage files. DMS needs to manually specify container and file names for storing data, and to ensure that sufficient disk space is available.
For a database, there is at least one system temporary tablespace with a page size of 4K, and you can create additional user temporary table space with a larger page size, which is automatically used by the system.
You cannot change the data type of a field with the ALTER statement, and you can change the data length for some fields, which is more restrictive than ORACLE,DB2.
Can be used: select expression from Sysibm.sysdummy; Instead, the following statements are equivalent: the values expression;
Table and view creation, update, delete operations, both write logs, so you can commit or rollback.
In an UPDATE statement, if a field that has a default value defined is not explicitly assigned, the field does not re-execute the expression defined in the default value when it is updated. In order for the expression to be re-executed by the default value definition, you can do this in the following ways:
CREATE TABLE T1 (C1 varchar (+), lastupdatetime with default current Timpstamp);
Update T1 Set c1 = ' new String ', LastUpdateTime = default;
For the DB2 database, you can specify the codepage parameter at creation time and cannot be modified after creation. When an application accesses a database, DB2 compares the codepage of the two, and the code page is automatically converted by inconsistency. To reduce the overhead of the conversion, try to ensure that the code page used by the application is consistent with the database.
The codepage of the DB2 CLP tool can be set, using: Db2set db2codepage= 1386, which is set in this example by the value of the Chinese GBK character set on the Windows platform. Note that this numeric value is defined by DB2 itself. The code page values for each character set on the corresponding platform can be found on the IBM Web site.
In the DB2 CLP, the operation of the remote database catalog:
DB2 CATALOG TCPIP node local_node_name remote HOSTNAME|IP SERVER service_name First maps the remote host to a local node, and this example uses a TCPIP connection. SERVICE_NAME is generally defined in the/etc/services file of the remote host.
DB2 CATALOG database db_name as Local_alias at Local_node_name USER username USING password then map the database of the known remote host to the local alias, note that the local alias is in the primary The machine level cannot be duplicated. Node name specifies the nodes that have just been cataloged above.
DB2 Connect to Local_alias USER username using password connecting the remote host database with the alias defined in the catalog just now
DB2 get configuration SHOW DETAIL get database details
For the error number returned by DB2, you can consult the description in the following way (for example, SQL 10008):
DB2? sql10008
Backup and recovery of the database:
Using the Backup and Recovery tool, you can complete the work of a complete staging database on a different server, with the following command line:
Backup
DB2 BACKUP DATABASE db_name USER user_name USING password to Backup_dir_name
DB2 BACKUP DATABASE dlhdb USER dlh USING admindlh to D:\backups
Recovery
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_name
DB2 RESTORE DATABASE dlhdb USER dlh USING admindlh from d:\backups taken at 20031209141056 to D:into newdb
Operations on the instance:
Setting the default instance environment variable
DB2 SET Db2instance=inst_name
Start the current instance
Db2start
Stop the current instance
db2stop [Force]
Connect to an instance
DB2 ATTACH to Anstance inst_name
Get configuration parameters for an instance
DB2 GET DBM CFG SHOW DETAIL
Export database complete definition to a script file, including tables, views, functions, database parameters, etc.
Db2look-d sample (database)-a-e-l-x-m-f-O (parameter) samplesql.out (output file)
When you load a table's data, it is possible to cause the tablespace to be in the backup pending (0x0020) state. For example, load the shaping data into a double type field.
The tablespace in the backup pending state cannot be accessed.
You can return to normal state (0x0) by running a backup operation on this tablespace.
For a self-increment field, you can specify it in two ways:
Generated by default as identity
Generated always as identity
The difference is that the first method allows you to manually specify the value of the self-increment field when inserting the data, as long as it is not repeated, and the database automatically sets the next value;
The second method is not allowed to be specified and can only be automatically assigned and inserted by the database.
DB2 the use of a literal in an SQL statement:
SELECT * from T1 where a like '%abc\%def ' escape ' \ ';
A sql1043c error occurred while creating the database, a possible problem:
The specified container is running out of disk space,
When the container is a file type, the following long-num parameter is incorrect, such as 25600 for 256Mb, but if you specify 256 it causes the above error.
Platform RH Linux 8
DB2 UDB v8.1
A data source was established to DB2 in was 5, but the connection failed, and the following error was returned:
[Servlet Error]-[sqlconnect]: java.lang.UnsatisfiedLinkError:SQLConnect
The reason is that the following environment variables are not set for the user running the was service:
Ld_library_path
LIBPATH
Db2instance
...
The above environment variables are defined in the $INSTHOME/sqllib/db2profile file and can be used in a solution:
SH stopserver.sh servername
. $INSTHOME/sqllib/db2profile
SH startserver.sh servername
You can also put Db2profile into the was startup script to execute first.
If only the Ld_library_path,libpath two environment variables are set, DB2 returns the following error:
cli0600e Invalid connection handle or connection is closed.
SQLSTATE S1000
corresponding to the job package capabilities of Oracle, DB2 is implemented through a GUI tool-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 one in an existing database, or you can create a separate database. The following commands are implemented:
Create catalog Tools schema_name Create new Database db_name
This command creates a database named Db_name for the cataloging tool and specifies a schema name.
Note: You cannot specify a codeset with a using clause, and the system uses the Iso8859-1 character set by default.
The Quest Center for DB2 features database performance diagnostics, dynamically monitoring DB memory, disk IO, tablespace, load, and more.
DB2 Type of client:
DB2 Runtime clients DB2 Runtime client
DB2 Management client DB2 Administrator client (contains all content of the runtime client)
DB2 Application Development Client DB2 Application Development client (contains all content for managing clients)
DB2 Thin clients DB2 Thin Client
DB2 Relational Connect federated database for connection to heterogeneous databases.
Db2idrop-f instance_name forcibly disconnects an existing connection, stops the instance and deletes it.
DB2IMIGR instance_name is used to migrate instances under UNIX.
DB2IUPDT instance_name update instance for instance to get access to some new product options or fix packs.
DB2 Get instance gets the instance that is currently in place.
When an instance-level or database-level parameter is updated, some can take effect immediately, and some need to be restarted for the instance to take effect. Immediate explicitly indicates that the change takes effect immediately, deferred explicitly indicates that the change takes effect after the instance has been re-started.
When you need to configure many 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 on each client. If the client has only run-time clients installed, you can use the
Db2cfimp Access_profile_name
Import the profile.
Question: Should the profile be exported by a configured client?
DB2 list Applications list all database connections
DB2 Force application (2) terminates the specified connection handle, and the transaction is interrupted and rolled back. Manipulating multiple handles can be separated by commas, or the keyword all is specified. This command terminates only the specified connection and does not prevent the new app from connecting to the database.
You can specify the sort method when you create a database: Collate using identity
The set of system patterns is created with each database, and they are placed in the Syscatspace table space:
SYSIBM:
Basic System Directory
Direct access is not recommended
SYSCAT:
Public is granted SELECT permission for this mode
Cataloging for read-only views
This is the recommended way to get directory information
SYSSTAT:
Updatable catalog views-impact Optimizer
Sysfun:
User-defined Functions
If an existing row in the table does not satisfy the constraint, the constraint cannot be defined. You can turn off constraint checking to speed up the addition of large amounts of data, but the table is in the check pending (check PENDING) state.
When you create a table, you can use options to specify one or more table spaces where tables and indexes will be placed:
CREATE TABLE TEST (
Column 1 definition, column 2 definition, ...
) in <tablespace name> INDEX in <index space name>
This command gives you an option to specify where to create the table and index. If you do not specify a separate index table space, the index is created in the same tablespace as the table. Once the table is created, there is no chance of creating an index in a different table space. Create an index to plan ahead!
CREATE <UNIQUE> INDEX <index name> on <table name>
(
Column 1 <ASC | Desc>,
Column 2 <ASC | Desc> ...
)
The Unique property tells DB2 that the index must enforce the uniqueness of all inserted values.
If the result set is sorted in ascending and descending order, allow REVERSE SCANS tells DB2 to include additional pointers in the index, allowing forward and reverse links to be valid in the record.
DB2 is able to add additional columns to the index that is being created. The CREATE Index command allows the user to specify columns that are not part of the actual index but are saved in the index record for performance reasons.
CREATE UNIQUE INDEX on EMPLOYEE (EMPNO) INCLUDE (lastname,firstname)
The index must be UNIQUE for the columns contained in the index. When an index is created, additional columns are added to the index value. Indexes do not use these values for sorting or to determine uniqueness, but they can be used when SQL queries are satisfied. For example, the following SELECT statement will not need to read the actual data rows:
SELECT LASTNAME, FIRSTNAME from EMPLOYEE WHERE EMPNO < ' 000300 '
To create a clustered index on a table, append the CLUSTER keyword to the end of the Create INDEX command, for example:
CREATE INDEX Depts-ix on EMPLOYEE (workdept) CLUSTER
Use the following general rules to determine the classic number of indexes defined for a table. The number of indexes depends on the primary purpose of the database:
For an online transaction processing (OLTP) environment, create one to two indexes.
For mixed query and OLTP environments, create two to five indexes.
For a read-only query environment, create more than five indexes.
Under DB2 command-line mode on the Windows platform, or under Unix-like platforms, use the command db2cc to start the control center.
Db2move provides the ability to bulk move data between databases, you can specify a table, or you can specify a table for the entire database.
Db2move dbname Action
Action can be specified as: Export, import, or load
Specify the folder where the LOB object is stored using the-l parameter
Performing an incremental backup requires setting the database configuration parameter "Trackmod" with a value of "YES"
Performing an online backup requires setting the database configuration parameter "Logretain" with the value "YES", online backup syntax:
DB2 Backup DB dbname online to Path
When the DAS user's home directory has been deleted and the DAS service has not been dropped first, then execution of Dasdrop is unsuccessful, prompting:
The Db2admin command could not be found because the Db2admin command was saved in the DAS home directory.
After you can delete the home directory for a clean DAS user, restart the server to resolve the issue.
DASUPDT Dasname Upgrade Das
DB2IUPDT Instname Upgrade Instance
DB2LICD End Stop License daemon
After the Linux system installs DB2 V8.1, the DB2FMCD process starts automatically, even if the instance and Das are not started,
The DB2 database service is installed in the Chinese Linux environment, and a database is created using the GBK code page (codepage), which can be connected normally when the operating system code page is changed to English, the "DB2 Connect" error is used, and the code page cannot be converted when connected. This file has been transferred from: http://www.cnblogs.com/millen/archive/2011/10/13/2210329.html
DB2 Basic Concepts
