Basic Learning of DB2 database

Characteristics of DB2

Full Web enabled: HTTP can be used to send inquiries to the server.

Highly scalable and reliable: multi-processor and large memory can be leveraged at high load, distributed database and data load across servers, can be quickly recovered with minimal data loss, and provides multiple backup strategies.

DB2 Database kai-Stop

Start database: Db2start

Stop database: Db2stop

Check for existing databases

LIST DATABASE DIRECTORY

database connection, disconnect

CONNECT to DatabaseName

CONNECT RESET

creating, deleting Databases

CREATE DB DatabaseName

Note: If a database is already attached, the database cannot be created and the "application is already connected to a database" error

DROP DB DatabaseName

section II Table

Data Type

Can be classified as numeric (numeric), string (character string), graphic string (graphic string), binary string (binary string), or DateTime. There is also a special data type called Datalink. The Datalink value contains a logical reference to a file that is stored outside the database.

numeric data types include:

Small integral type, SMALLINT: Two-byte integer, precision 5-bit. range from-32,768 to 32,767.

Large integral, integer or int: four-byte integer with a precision of 10 digits. range from-2,147,483,648 to 2,147,483,647.

BIGINT: Eight-byte integer with a precision of 19 digits. range from-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Decimal, Decimal (p,s), DEC (P,s), Numberic (p,s), or num (p,s): A decimal value is a compressed decimal number that has an implied decimal point. Compressed decimal numbers are stored in a variant of the two-decimal encoding (binary-coded DECIMAL,BCD) notation. The position of the decimal point depends on the precision of the number (p) and decimal places (s). A decimal point is a number of digits in the decimal part of the number, it can not be negative, and can not be greater than precision. The maximum accuracy is 31 bits. The range of decimal types ranges from -10**31+1 to 10^**31-1.

Single-precision floating-point number, real: a single-precision floating-point number is a 32-bit approximation of the actual number. The number can be zero, or range from -3.402E+38 to -1.175E-37 or from 1.175E-37 to 3.402E+38.

Double-precision floating-point number, double or float: a double-precision floating-point number is a 64-bit approximation of a real number. The number can be zero, or range from -1.79769E+308 to -2.225E-307 or from 2.225E-307 to 1.79769E+308.

The string includes:

Fixed-length string, CHARACTER (n) or char (n): between 1 and 254 bytes in length. If no length is specified, it is considered to be 1 bytes.

Variable-length string, VARCHAR (n), CHARACTER varying (n), or char varying (n): Up to 32,672 bytes long. The long varchar can be up to 32,700 bytes in length.

Character large object string, CLOB (n): Up to 2,147,483,647 bytes long

Fixed-length graphics string, GRAPHIC (n): between 1 and 127 double-byte characters in length. If no length is specified, it is considered to be 1 double-byte characters.

Variable-length graphics strings, vargraphic (n), long vargraphic: up to 16,336 double-byte characters in maximum length.

Double-byte character large object string, Dbclob (n): Up to 1,073,741,823 characters long. Used to accommodate non-traditional data, such as pictures, voice, or mixed media, and to accommodate structured data for user-defined types and user-defined functions.

Binary large object, BLOB (n): A binary large object is a variable-length string that can be up to 2,147,483,647 bytes long.

Date-time data type

Date:date is a three-part value (year, month, and day). The year portion ranges from 0001 to 9999. The month portion ranges from 1 to 12. The daily portion of the range is from 1 to N, where the value of n depends on the month. The date column is 10 bytes long.

Time:time is a three-part value (hours, minutes, and seconds). The range of the hour portion is from 0 to 24. The Minutes and Seconds sections range from 0 to 59. If the hour is 24, the minute and second values are 0. The time column is 8 bytes long.

Timestamp:timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond). The year portion ranges from 0001 to 9999. The month portion ranges from 1 to 12. The daily portion of the range is from 1 to N, where the value of n depends on the month. The range of the hour portion is from 0 to 24. The Minutes and Seconds sections range from 0 to 59. The microsecond portion ranges from 000000 to 999999. If the hour is 24, then the minute value, the second value, and the microsecond value are 0. The timestamp column is 26 bytes long.

 

Field name	Data Type	Field Length	constraint	Description
hospital_id	INTEGER		Primary key	Hospital ID
Hospital_code	VarChar	30	Index	Hospital code
Hospital_name	VarChar	30	Index	Hospital name
Hospital_describe	VarChar	60		Hospital description
Hospital_isactive	Char	1	Default value (Y)	Valid (Y) is not valid (N)

Field name	Data Type	Field Length	constraint	Description
department_id	INTEGER		Primary key	Department ID
Department_code	VarChar	30	Index	Department Code
Department_name	VarChar	30	Index	Department Name
Department_describe	VarChar	60		Department description
Department_parent	INTEGER		Index	Superior Department
Department_islast	Char	1	Default value (Y)	End Sign
hospital_id	INTEGER		Foreign KEY, index	Hospital ID
Department_isvirtual	Char	1		Whether the virtual department
Department_isactive	Char	1	Default value (Y)	Valid (Y) is not valid (N)

 

Creation of Tables

Table field Additions, primary keys, foreign keys, constraints

Insert Row

INSERT [into] {table_name} [(column_list)]

VALUES {DEFAULT | value_list | select_statement}

For example: INSERT INTO XXX (id,name) values (1,limingzhong);

Update Rows

UPDATE table_name

SET column_name = value [, COLUMN_NAME = value]

[From TABLE_NAME]

[WHERE condition]

Delete Row

DELETE [from table (s)]

[WHERE condition]

Query

SELECT * FROM table_name WHERE condition (s)

For example: SELECT * from XU. Hospital where Hospital_name like ' _ West one% '

Union operator

SELECT * from XU. AAA Union SELECT * from XU. BBB;

table joins (join)

SELECT * from XU. CCC a INNER join XU. AAA b on A.idid=b.id where b.id=1001

View

A view is a virtual table that accesses a subset of columns from one or more tables, an object that derives data from one or more tables, and a table that derives data called a base table or an underlying table. It is a query that is stored as an object in a database.

View provides a security mechanism. It ensures that users can only retrieve and modify the data they see. The rest of the data in the base table is neither visible nor accessible. The use of complex queries can also be simplified through views. Complex queries can be stored as views, and the data in the view can be extracted using simple queries.

After you define a view, it can be referenced just like any other table in the database. Although the view is similar to a table, it is not stored in the database. It derives its collection of values from the base table.

Create a View

CREATE VIEW view_name

[(column_name [, column_name] ...)]

As Select_statement

In the view, the ORDER BY clause cannot be used in a SELECT statement.

Delete View

DROP VIEW view_name [, view_name] ...

modifying data through views

You can modify the base table data by modifying the data in the view. The modification of the data does not affect more than one base table, that is, each modification can only affect one table.

Index

An index is an internal table structure that provides fast access to rows in a table based on the values of one or more columns in the table.

Advantages of Indexing

The primary goal of indexing is to increase the speed at which data pages are accessed. Instead of scanning each page for the required data, the server scans the index, obtains the address of the data storage unit, and accesses the information directly.

• Increase the speed at which inquiries are performed

• Implementing Data uniqueness

L speed up the connection between tables

Disadvantages of indexing

It is unwise to create an index for each column in a table.

L It takes time to create an index

L Index also needs space to store data

L The index needs to be updated each time the data is modified

Cluster Index

L data is physically sorted

L should build indexes on properties that have a high percentage of unique values and are infrequently modified.

non-clustered index

The physical order of the L rows is different from the order of the indexes

L is generally created on a column for a connection and a WHERE clause, and its value may be modified frequently.

L The nonclustered index is created by default when the CREATE INDEX command is given.

attributes of an index

L index accelerates query, sorting, and grouping of join tables

L index can be used to enforce the uniqueness of rows

Index is useful for most unique columns of data. Indexes are of little use to columns with a large number of duplicate data

l When you modify the data in the indexed column, the related index is automatically updated

L You need time and resources to maintain the index. You should not create indexes that are not used frequently

The L-cluster index should be created before a non-clustered index. The cluster index changes the order of the rows. If the nonclustered index is created before the clustered index, it needs to be reconstructed

L Typically, nonclustered indexes are created on foreign keys

section Three implementing stored Procedures

A stored procedure is a collection or batch of SQL statements and control flow statements that is stored under a name and executed in a stand-alone unit. It can help improve the performance of queries.

benefits of stored procedures

A stored procedure is a precompiled object that is stored in a database. This means that the process is precompiled and can be delivered to a variety of application executions. Sending queries to the server, parsing, and compiling processes does not take time.

• Improve performance: Applications do not have to compile this process repeatedly.

• Ease network congestion: for processing, applications do not need to submit multiple SQL statements to the server.

L Good consistency: Because processes act as a single point of control, the coded logic and SQL statements defined in the process are uniformly implemented in all applications.

• Improve security mechanisms: Users can be granted permission to execute stored procedures, although they do not own the process.

Executing stored procedure statements

Call Proc_name (param,...)

passing parameters to a procedure

Parameter: A placeholder or a stored procedure in a query that accepts user-defined values as long as the query or stored procedure is executed.

Type of parameter:

L Input parameter: Allows the caller to pass data values to the procedure.

L OUTPUT parameter: Allows a stored procedure to return data values to the caller.

return keyword

Allows a stored procedure to return an integer value to the caller. If no value is specified, the stored procedure returns the default value of 0 or 1, depending on the successful execution of the stored procedure.

return value

Nesting Procedures

You can execute or invoke a procedure from another procedure.

CREATE PROCEDURE PR1 () language SQL P1:begin declare ID INTEGER;DECLA name VARCHAR (?); set Id=10;set name= ' abc '; return 0;e nd p1

DROP Procedure Statement

DROP PROCEDURE Proc_name

You cannot undo a procedure that is being called by another procedure.

fourth section triggers

definition and characteristics of triggers

A trigger is a block of code consisting of a set of SQL statements that activates the statement set in response to certain actions. A trigger can also be interpreted as a specific type of stored procedure that is executed whenever the action occurs.

Triggers are fired whenever the data in the underlying table is affected by the data manipulation language (DML) statement--insert, update, or delete.

Triggers help maintain the consistency, reliability, and correctness of the data in the table.

characteristics OF Triggers

l When any data modification statement is emitted, it is automatically fired.

L in the case of a stored procedure, it is not explicitly invoked and executed.

L It prevents incorrect, unauthorized, and inconsistent changes to the data.

L It cannot return data to the user.

Triggers can be nested up to a maximum. The nesting of triggers occurs when a trigger performs an initialization action on another trigger.

Creating triggers

CREATE TRIGGER XU. T1 after UPDATE of ' NAME on XU. AAA for each ROW MODE Db2sql inserts into BBB (ID1,NAME1) VALUES (1005, ' country ')

Insert Trigger

The INSERT trigger is fired when you try to insert a row into the trigger table. When the INSERT statement is emitted, a new row is added to the trigger and the inserted table.

Delete Trigger

The DELETE trigger is fired when an attempt is made to delete a row from the trigger table.

There are three ways to use the Delete trigger to implement referential integrity:

L-Concatenation Method-Deletes a record from a dependent table whenever a record is deleted from the primary table.