Different common SQL statement records of DB2 and Oracle

Different common SQL statement records of DB2 and Oracle, you can choose to include additional column data when creating an index. These additional column data will be stored together with the key, but in fact they

Different common SQL statement records of DB2 and Oracle, you can choose to include additional column data when creating an index. These additional column data will be stored together with the key, but in fact they

1. view the data in the first three rows. If the column alias does not have an AS clause, the derived column is named 2, which indicates that it is the second column in The result set.

Db2 => select name, salary + comm from staff fetch first 3 rows only
NAME 2
-------------------
Sanders-
Pernal 78783.70
Marenghi-
3 record (s) selected.

2. db2 supports inserting multiple values in the same insert statement using commas (,).

Db2 => insert into tt values (1), (1)
DB20000I The SQL command completed successfully.
3. db2 does not have the "create table TABLE_A as select *****" statement. To create a table with the same structure, you can only use

Db2 => create table pers like staff
DB20000I The SQL command completed successfully.

4. The UPDATE statement is used to modify data in a table or view. By specifying the WHERE clause, you can modify the values of one or more columns in each row that meets the conditions.

Db2 => update staff set (dept, salary) = (150) where id =
DB20000I The SQL command completed successfully.
Oraclehedb2 supports the following methods:

Update tttt set id = 3, name = 'C' where id = 1;
5. the columns in the target of the data modification operation (insert, update, or delete) become columns in the intermediate result table. You can reference these columns by name in the selection list of the query.

Db2 => select salary from old table (update staff set salary = salary * 0.2 where id = 10)
SALARY
---------
19671.50
1 record (s) selected.

Db2 => select salary from old table (update staff set salary = salary * 0.2 where id = 10)
SALARY
---------
3934.30
1 record (s) selected.

6. db2 automatically submits transactions by default. To create a transaction, specify the "+ c" parameter. Note that if a transaction is specified, even if the ddl statement db2 needs to commit or roll back, db2 does not display or implicitly display rollback transactions.

Session 1

Db2 => connect to sample
Database Connection Information
Database server = DB2/LINUX 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE

Db2 => select * From czm_1

We can see that after session 1 executes the rollback statement, the czm_1 object cannot be found,

Db2-td @-vf createSQLproc. db2

The db2 command specifies the-td option flag, which enables the command line handler to use @ as the statement termination character (because the semicolon has been used as the statement termination character in the process body ); -v indicates that the command line handler displays the command text back to the standard output;-f indicates that the command line handler reads the command input from the specified file instead of the standard input.
8. Create a table in the specified tablespace

Db2 => create table books (bookid integer, bookname varchar (100), isbn char (10) in userspace1

9. Modification of table column features
The following statement changes the ype of the bookname column from VARCHAR (100) to VARCHAR (200), and changes the maintainability of the ISBN column to not null:

Db2 => alter table books alter bookname set data type char (150) alter isbn set not null
DB20000I The SQL command completed successfully.
Modify the table column restrictions:
When modifying the length of a string column, you can only increase the length.
When modifying the Data Type of a column, the new data type must be compatible with the existing data type. For example, CHAR columns can be converted to VARCHAR columns, but they cannot be converted to GRAPHIC or numeric columns. Numeric columns can be converted to any other numeric data type, as long as the length of the new data type is sufficient to accommodate the value. For example, the INTEGER column can be converted to BIGINT, but the DECIMAL () Column cannot be converted to SMALLINT.
A fixed-length string can be converted to a variable-length string. a variable-length string can also be converted to a fixed-length string. For example, CHAR (100) can be converted to VARCHAR (150 ). There are similar restrictions on variable-length graphical strings.
Some features of a table cannot be changed. For example, you cannot modify the Data Type of certain columns, the tablespace or column sequence in which the table resides. To change such a feature, you must save the table data, delete the table, and recreate the table.