【導讀】DB2速查手冊。5.client

　　

【正文】 atabase release level = Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = [170][/home/db2inst1/sqllib/sqldbdir] $ db2 list db directory on /db2data Local Database Directory on /db2data Number of entries in the directory = 2 Database 1 entry: Database alias = TOOLSDB Database name = TOOLSDB Database directory = SQL00003 Database release level = Comment = Directory entry type = Home Catalog database partition number = 0 Database partition number = 0 Database 2 entry: Database alias = YIHANYU Database name = YIHANYU Database directory = SQL00001 Database release level = Comment = Directory entry type = Home Catalog database partition number = 0 Database partition number = 0 7. The Relationship between the DB2 Directories A Local Connection DB2 follows these steps: 1. Looks for the system database directory. 2. Inside the system database directory, looks for the entry with a database alias of MYLOCDB. 3. Determines the database name that corresponds to the database alias (in Figure the database alias and name are the same). 4. Determines if the database is local or remote by reviewing the Directory entry type field. In Figure , the entry type is Indirect, so the database is local. 5. Since the database is local, DB2 reviews the Database drive field, which indicates the location of the local database directory. In Figure , it is H:\MYINST2. 6. Looks for the local database directory. 7. Inside the local database directory, DB2 looks for the entry with a database alias that matches the database name of MYLOCDB. 8. Determines the physical location where the database resides by looking at the field Database Directory. In Figure , it is SQL00001. A Remote Connection to a DB2 Database on another Server DB2 follows these steps: 1. Looks for the system database directory. 2. Inside the system database directory, looks for the entry with a database alias of MYRMTDB. 3. Determines the database name that corresponds to the database alias. In Figure the database name is RMTDB. This information will later be used in step 8. 4. Determines if the database is local or remote by reviewing the Directory entry type field. In Figure , the entry type is Remote, so the database is remote. 5. Since the database is remote, DB2 reviews the Node name field, which indicates the entry name to look for in the node directory. In the figure, the node name is MYNODE1. 6. Looks for the node directory. 7. Inside the node directory, looks for the entry with a node name of MYNODE1. 8. Determines the physical location of the server or instance where the database this example, the TCP/IP protocol is used, so DB2 looks for the fields Hostname and Service Name. In Figure , their values are and 50000 respectively. With this information and the database name obtained in step 3, DB2 initiates the connection. 8. configure a connection using db2 discovery 9. Diagnose connection problems 10. 建表時表空間的分配使用方法 在建立表時，如果語句中沒有指定表空間 如： Create table test(id Int) 在這種情況下， DB2是怎樣選擇表空間來建立該表的呢？ A maximum of one IN clause is allowed at the table level. All data table spaces used by a table must have the same page size and extent size. If they do not all have the same prefetch size, a warning is returned. If all table spaces have AUTOMATIC prefetch size, no warning is returned. If only one table space is specified, all table parts are stored in this table space. This clause cannot be specified when creating a subtable (SQLSTATE 42613), because the table space is inherited from the root table of the table hierarchy. If this clause is not specified, a table space for the table is determined as follows: IF table space IBMDEFAULTGROUP (over which the user has USE privilege) exists with sufficient page size THEN choose it ELSE IF a table space (over which the user has USE privilege) exists with sufficient page size (see below when multiple table spaces qualify) THEN choose it ELSE return an error (SQLSTATE 42727) If more than one table space is identified by the ELSE IF condition, choose the table space with the smallest sufficient page size. If more than one table space qualifies, choose the table space in the following order of preference, depending on to whom the USE privilege was granted: authorization ID group to which the authorization ID belongs If more than one table space still qualifies, the final choice is made by the database manager. Table space determination can change if: ? Table spaces are dropped or created ? USE privileges are granted or revoked Partitioned tables can have their data partitions spread across multiple table spaces. When multiple table spaces are specified, all of the table spaces must exist, and they must all be either SMS or regular DMS or large DMS table spaces (SQLSTATE 42838). The authorization ID of the statement must hold the USE privilege on all of the specified table spaces. The sufficient page size of a table is determined by either the byte count of the row or the number of columns. 在建表時，先計算待建表的各字段的長度和，再在候選的用戶的表空間中挑選合適頁大小的表空間，如代建的表的各字段長度和在 4000以內(nèi)（要考慮 pctfree的值），則會用 4K的表空間，如超過 4000byte，則會選擇用 8K的表空間。 如果已經(jīng)確認合適的頁大小的表空間只有一個則會查看該用戶是否有 schema 的 createin和表空間的USEAGE權(quán)限。如有則在該表空間建立 如果 已經(jīng)確認合適的頁大小的表空間有多個，則會依據(jù)以下的順序的表空間的使用權(quán)限來使用表空