[DB2 learning records] 7. Create a database

It's a bit messy, because some do not understand it. Wait until you understand it.

Start command line processor, CLP
Db2cmd DB2
Create Database my1stdb
* The database name can contain the following characters: A-Z, a-Z, 0-9, @, #, and $.
* The first character in the name must be an alphanumeric character, @, #, or $. It cannot be a number or a letter sequence sys, DBM, or IBM.
* The database name or database alias is a unique string that contains one to eight letters, numbers, or keyboard characters described above.
(It indicates that an instance must be unique and cannot exceed eight digits)

DB2 => Create Database my1stdb1;
Sql1001n "my1stdb1;" is not a valid database name. sqlstate = 2e000 --- it is considered as oracle.
DB2 => Create Database my1stdb1
Db20000i the create database Command completed successfully. --- successful.
DB2 => Create Database my1stdb11
Sql1001n "my1stdb11" is not a valid database name. sqlstate = 2e000 --- 9 bits, failed.
DB2 => Create Database my1stdb21
Sql1001n "my1stdb21" is not a valid database name. sqlstate = 2e000 --- 9 bits, failed.
DB2 => Create Database my1stdb2
Db20000i the create database Command completed successfully. --- successful.
DB2 => Create Database my1stdb2
Sql1005n the database alias "my1stdb2" already exists in either the local --- same name, failed.
Database directory or system database directory.

DB2 creation, including log files, configuration information, history files, and three tablespaces. These tablespaces are:
* Syscatspace: This is the place where the DB2 system catalog is stored. The system catalog tracks all metadata associated with the DB2 object.
* Tempspace1: temporary work area used by DB2 to place intermediate results.
* Userspace1: stores all user objects (tables and indexes) by default.
All these files are stored in the DB2 Directory on the default drive. The default drive is usually the volume of the DB2 product installed.

Before DB2 9, the create database Command creates SMS tablespace for all objects listed above.
In DB2 9, all tablespaces are defined as Automatic Storage (DMS) tablespaces.
If not, you can

CREATE TABLESPACE TEST MANAGED BY AUTOMATIC STORAGE NO

Check the created dB directory:
E:/DB2/node0000/my1stdb1> DIR/s
Volume in drive E is application
Volume serial number is 08bd-ef8e

Directory of E:/DB2/node0000/my1stdb1

03/20/2008 09:33 AM 0 sqlcrt. flg
03/20/2008 am <dir> t0000000 -- estimate the corresponding syscatspace tablespace
03/20/2008 am 67,108,864 c0000000.cat
03/20/2008 09:33 AM 0 sqlcrt. flg
03/20/2008 am <dir> t0000001 -- estimate the corresponding tempspace1 tablespace
03/20/2008 09:34 am <dir> c0000000.tmp
03/20/2008 am 512 sqltag. Nam
03/20/2008 09:34 AM 0 sqlcrt. flg
03/20/2008 am <dir> t0000002 -- estimate the corresponding userspace1 tablespace
03/20/2008 am 33,554,432 c0000000.lrg
03/20/2008 09:33 AM 0 sqlcrt. flg
There is also a directory that stores log files, configuration information, and so on:
E:/DB2/node0000/sql00008

If no path is specified, the database will be created on the default database path specified in the database management program settings (dftdbpath parameter:
Default database path (dftdbpath) = E:
 
The two commands are not successful. It is estimated that they are related to the path.
Create Database mydb on D:/test
 
Create Database mydb
Automatic Storage on
/DB2/storagepath001,
/DB2/storagepath002,
/DB2/storagepath003
Autoresize Yes --- when the tablespace uses up space, the system automatically expands the container size.
Initialsize 300 m --- the default initial size is 300 MB, and each container is 100 MB (three storage paths are available ).
Increasesize 75 m --- when the tablespace is used up, the total space is increased by 75 mb. You can specify a percentage to increase the current size.
Maxsize none --- there is no limit on the maximum tablespace size. You can limit the maximum tablespace size.
We also found that if you really want to run under CPL, you need to add/and continue to run in order to run.

Create tablespace test managed by automatic storage;
In this command, any parameters associated with the tablespace can be provided;
However, automatic storage can greatly simplify daily tablespace maintenance.
Tablespaces associated with important large production tables may require more DBA intervention.

When creating a tablespace in a database where automatic storage is not enabled,
The managed by system or managed by database clause must be specified.
Using these clauses, the SMS tablespace and DMS tablespace are created respectively.
In both cases, you must provide an explicit list of containers.
System Using ('container string ')
Database Using (File/device 'iner iner string 'number of pages)

Note: All containers must be unique in all databases. One container can only belong to one tablespace.

Character Code
DB2 has an associated character code page, which can be considered as a reference table to convert alphanumeric data into binary data stored in the database. A DB2 database can only use one code page.
The code page is set using the codeset and territory options in the create database Command.

For applications that need to use XML data,
Currently, DB2 only supports XML columns in databases defined as Unicode (UTF-8.
If Unicode support is not enabled during database creation, XML Columns cannot be created.

In addition:
DB2 => Create Database db2test4 Automatic Storage Yes
Sql1047n the application is already connected to another database. -- in this case, connect Reset
DB2 => connect Reset
Db20000i the SQL command completed successfully.
DB2 => Create Database db2test4 Automatic Storage Yes

DB2 => Create Database mydb
Sql1032n no start Database Manager Command was issued. -- DB service fails.

-- The database may not be available.
Communication error detected by sql30081n. Communication Protocol in use: "TCP/IP ". Communication in use
API: "Sockets ". Detected error location: "10.89.59.203 ". Failed communication:
"Connect ". Protocol-specific error code: "10061", "*", and "*". Sqlstate = 08001

Last example

( 1) CREATE DATABASE MY1STDB
( 2)  DFT_EXTENT_SZ 4
( 3)  CATALOG TABLESPACE MANAGED BY DATABASE USING
( 4)   (FILE 'C:/CAT/CATALOG.DAT' 2000, FILE 'D:/CAT/CATALOG.DAT' 2000)
( 5)    EXTENTSIZE 8
( 6)   PREFETCHSIZE 16
( 7) TEMPORARY TABLESPACE MANAGED BY SYSTEM USING
( 8)  ('C:/TEMPTS','D:/TEMPTS')
( 9) USER TABLESPACE MANAGED BY DATABASE USING
(10)   (FILE 'C:/TS/USERTS.DAT' 121)
(11)   EXTENTSIZE 24
(12)   PREFETCHSIZE 48

Think about it yourself!

Added later,

• Extentsize:Section (extent)Is a space unit in a tablespace container. Database objects (except lob and long varchar) are stored in DB2Page. These pages are combined into segments. The partition size is defined at the tablespace level. Once the partition size is specified for the tablespace, it cannot be changed. The Database Configuration Parameter dft_extent_sz specifies the default partition size for all tablespaces in the database. The value ranges from 2 to 256 pages. Therefore, the absolute size ranges from 8 KB to 1024 KB (for 4 kb pages ), or from 16 KB to 2048 KB (for 8 KB pages ). You can use the extentsize parameter in the create tablespace statement to overwrite this number.

  If you plan to use multi-dimensional clustering (MDC) in the design of the table, the Section is an important design decision. The MDC table assigns a partition for each new dimension set. If the section is too large, a large part of the section may be empty (for dimension sets with few records ). For more information about MDC and its impact on extentsize, see the DB2 administration guide.

• Prefetchsize: continuous pre-acquisition refers to the ability of the database management program to predict queries in advance and read these pages before actually referencing pages. This asynchronous retrieval can significantly reduce the execution time. You can modify the prefetchsize parameter in the create tablespace statement to control the positive degree of execution pre-acquisition. By default, this value is set to the dft_prefetch_sz database configuration parameter. This value indicates how many pages are read each time DB2 triggers a pre-Fetch request. By setting this value as a multiple of the segment size, you can read multiple segments in parallel. This function is even more efficient when the tablespace containers are on different hard disks.

The default values of these parameters are suitable for many applications, but for applications that execute many queries or analyze a large amount of data, you can consider setting a higher prefetchsize.

You can use the control center Wizard to create a database. The advantage is that you can finally obtain the corresponding SQL.