The db2 tutorial I am watching is: DB2 UDB V8.1 Management Study Notes (II). Table space types are divided into SMS and DMS, namely system management space and database management space. SMS is easy to use and simple, without manually creating and maintaining data storage files. DMS needs to manually specify the container and file name for storing data, and ensure that there is enough disk space available.
For a database, at least one temporary tablespace for system with a page size of 4K can be created. An additional temporary tablespace for user with a larger page size can be created, and the system will use it automatically.
The data type of a field cannot be changed using the alter statement. You can change the data length for some fields. In this regard, DB2 has more restrictions than Oracle.
You can use: select expression from; instead, the following statement is equivalent: values expression;
The creation, update, and deletion operations of tables and views are all written to log, so you can commit or rollback.
In the update statement, if no explicit assignment is made to a field that defines the default value, the field does not re-execute the expression defined in the default value when updated. In order to re-execute the expression defined by the default value, you can use the following methods:
create table t1 (c1 varchar(32), lastupdatetime with default current timpstamp);update t1 set c1 = 'new string', lastupdatetime = default;
For DB2 databases, codepage parameters can be specified during creation, and cannot be modified after creation. When the application accesses the database, DB2 will compare whether the codepages of the two are consistent, and if they are inconsistent, they will automatically convert the code page. In order to reduce the overhead caused by conversion, the code page used by the application should be ensured to be consistent with the database.
You can set the codepage of the DB2 CLP tool and use:
$ db2set DB2CODEPAGE= 1386
In this example, the value corresponding to the Chinese GBK character set on the Windows platform is set. Note that this numeric value is defined by DB2 itself. The corresponding code page values for various character sets on the corresponding platform can be found on the IBM website.
In DB2 CLP, for the operation of cataloging remote databases, the remote host is first mapped to the local node, and the node name is specified by itself. This example uses TCPIP connection. service_name is generally defined in the /etc/services file of the remote host.
$ db2 CATALOG TCPIP NODE local_node_name REMOTE hostname|ip SERVER service_name
Then map the database of the known remote host to the local alias, note that the local alias cannot be repeated at the host level. The node name specifies the node just listed above.
$ db2 CATALOG DATABASE db_name AS local_alias AT local_node_name USER username USING password
You can now connect to the remote host database with the alias defined in the catalog just now
$ db2 CONNECT TO local_alias USER username USING password
Get detailed database configuration information
$ db2 GET CONFIGURATION SHOW DETAIL
For the error number returned by DB2, you can use the following method to check the description (taking sql 10008 as an example):
$ db2 ? sql10008
Database backup and recovery:
Use backup and recovery tools to complete the transfer of databases on different servers. The command line method is as follows:
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
recover
$ 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 related to the example:
Set default instance environment variables
$ 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 the configuration parameters of the instance
$ db2 GET DBM CFG SHOW DETAIL
Export the complete definition of the database to the script file, including tables, views, functions, database parameters, etc.
$ db2look -d sample(database) -a -e -l -x -m -f -o(parameters)(output file)
When loading the data of a table, it may cause the table space to be in the backup pending(0x0020) state. For example, load the plastic surgery data into a double-type field.
Table spaces in the backup pending state cannot be accessed.
You can return to normal state (0x0) by running a backup operation on this tablespace.
For self-increment fields, you can specify them in two ways:
generated by default as identitygenerated always as identity
The difference is that the first method allows manual specification of the value of the self-increment field when inserting data, as long as it is not repeated, and the database will automatically set the next value;
The second method does not allow specification, and can only be automatically allocated and inserted by the database.
Use of escape characters in DB2 SQL statement:
select * from t1 where a like '%abc\%def' escape '\';
When creating the database, SQL1043C error occurred, possible problems:
The disk space specified in the specified container is insufficient.
When the container is file type, the following long-num parameter is incorrect. For example, 25600 represents 256Mb, but if 256 is specified, the above error will be caused.
Platform RH Linux 8
DB2 UDB v8.1
Establishing a data source to db2 in WAS 5, but the connection failed, returning the following error:
[Servlet Error]-[SQLConnect]: : SQLConnect
The reason is that there is no
D_LIBRARY_PATHLIBPATHDB2INSTANCE...
The above environment variables are defined in the $INSTHOME/sqllib/db2profile file. The solutions that can be adopted:
$ sh servername$ . $INSTHOME/sqllib/db2profile$ sh servername
You can also put the db2profile into the was startup script and execute it first.
If only LD_LIBRARY_PATH and LIBPATH are set, DB2 will return the following error:
CLI0600E Invalid connection handle or connection is S1000
Corresponding to Oracle's Job package function, DB2 is implemented through a GUI tool - task center. Before using the Task Center, you need to make necessary tool settings and create some database objects. You can create them in an existing database or create a separate database. Implemented by the following command:
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 the using clause, the system will use the ISO8859-1 character set by default.
Quest Center for DB2 has database performance diagnostic functions, dynamic monitoring of db memory, disk io, tablespace, load, etc.
Types of DB2 client:
DB2 Runtime Client DB2 Runtime Client
DB2 Management Client DB2 Administrator Client (contains all contents of the runtime client)
DB2 Application Development Client (Contains all contents of the management client)
DB2 Thin Client
DB2 Relational Connect Federated database, used to connect to heterogeneous databases.
For a database, at least one temporary tablespace for system with a page size of 4K can be created. An additional temporary tablespace for user with a larger page size can be created, and the system will use it automatically.
The data type of a field cannot be changed using the alter statement. You can change the data length for some fields. In this regard, DB2 has more restrictions than Oracle.
You can use: select expression from; instead, the following statement is equivalent: values expression;
The creation, update, and deletion operations of tables and views are all written to log, so you can commit or rollback.
In the update statement, if no explicit assignment is made to a field that defines the default value, the field does not re-execute the expression defined in the default value when updated. In order to re-execute the expression defined by the default value, you can use the following methods:
create table t1 (c1 varchar(32), lastupdatetime with default current timpstamp);update t1 set c1 = 'new string', lastupdatetime = default;
For DB2 databases, codepage parameters can be specified during creation, and cannot be modified after creation. When the application accesses the database, DB2 will compare whether the codepages of the two are consistent, and if they are inconsistent, they will automatically convert the code page. In order to reduce the overhead caused by conversion, the code page used by the application should be ensured to be consistent with the database.
You can set the codepage of the DB2 CLP tool and use:
$ db2set DB2CODEPAGE= 1386
In this example, the value corresponding to the Chinese GBK character set on the Windows platform is set. Note that this numeric value is defined by DB2 itself. The corresponding code page values for various character sets on the corresponding platform can be found on the IBM website.
In DB2 CLP, for the operation of cataloging remote databases, the remote host is first mapped to the local node, and the node name is specified by itself. This example uses TCPIP connection. service_name is generally defined in the /etc/services file of the remote host.
$ db2 CATALOG TCPIP NODE local_node_name REMOTE hostname|ip SERVER service_name
Then map the database of the known remote host to the local alias, note that the local alias cannot be repeated at the host level. The node name specifies the node just listed above.
$ db2 CATALOG DATABASE db_name AS local_alias AT local_node_name USER username USING password
You can now connect to the remote host database with the alias defined in the catalog just now
$ db2 CONNECT TO local_alias USER username USING password
Get detailed database configuration information
$ db2 GET CONFIGURATION SHOW DETAIL
For the error number returned by DB2, you can use the following method to check the description (taking sql 10008 as an example):
$ db2 ? sql10008
Database backup and recovery:
Use backup and recovery tools to complete the transfer of databases on different servers. The command line method is as follows:
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
recover
$ 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 related to the example:
Set default instance environment variables
$ 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 the configuration parameters of the instance
$ db2 GET DBM CFG SHOW DETAIL
Export the complete definition of the database to the script file, including tables, views, functions, database parameters, etc.
$ db2look -d sample(database) -a -e -l -x -m -f -o(parameters)(output file)
When loading the data of a table, it may cause the table space to be in the backup pending(0x0020) state. For example, load the plastic surgery data into a double-type field.
Table spaces in the backup pending state cannot be accessed.
You can return to normal state (0x0) by running a backup operation on this tablespace.
For self-increment fields, you can specify them in two ways:
generated by default as identitygenerated always as identity
The difference is that the first method allows manual specification of the value of the self-increment field when inserting data, as long as it is not repeated, and the database will automatically set the next value;
The second method does not allow specification, and can only be automatically allocated and inserted by the database.
Use of escape characters in DB2 SQL statement:
select * from t1 where a like '%abc\%def' escape '\';
When creating the database, SQL1043C error occurred, possible problems:
The disk space specified in the specified container is insufficient.
When the container is file type, the following long-num parameter is incorrect. For example, 25600 represents 256Mb, but if 256 is specified, the above error will be caused.
Platform RH Linux 8
DB2 UDB v8.1
Establishing a data source to db2 in WAS 5, but the connection failed, returning the following error:
[Servlet Error]-[SQLConnect]: : SQLConnect
The reason is that there is no
[1] [2] Next page
The db2 tutorial I am watching is: DB2 UDB V8.1 Management Study Notes (II). Set the following environment variables for users running the wass service:D_LIBRARY_PATHLIBPATHDB2INSTANCE...
The above environment variables are defined in the $INSTHOME/sqllib/db2profile file. The solutions that can be adopted:
$ sh servername$ . $INSTHOME/sqllib/db2profile$ sh servername
You can also put the db2profile into the was startup script and execute it first.
If only LD_LIBRARY_PATH and LIBPATH are set, DB2 will return the following error:
CLI0600E Invalid connection handle or connection is S1000
Corresponding to Oracle's Job package function, DB2 is implemented through a GUI tool - task center. Before using the Task Center, you need to make necessary tool settings and create some database objects. You can create them in an existing database or create a separate database. Implemented by the following command:
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 the using clause, the system will use the ISO8859-1 character set by default.
Quest Center for DB2 has database performance diagnostic functions, dynamic monitoring of db memory, disk io, tablespace, load, etc.
Types of DB2 client:
DB2 Runtime Client DB2 Runtime Client
DB2 Management Client DB2 Administrator Client (contains all contents of the runtime client)
DB2 Application Development Client (Contains all contents of the management client)
DB2 Thin Client
DB2 Relational Connect Federated database, used to connect to heterogeneous databases.
Previous page [1] [2]