Solutions to multiple common functions in DB2 (1)

Introduction
A relational model is a wonderful thing. Compromise on it is a far-fetched invitation, just like a baseball cap on Angela's David. However, things that do not belong to a pure relational model may eventually appear in your database or application. If you take the vow of a monk seriously, you do not have to continue reading this article. This article will take you to the red-light area of RDBMS for those who may wish to walk in the wild. Please continue reading this article to find out how:

• Store derived values
• Id each line to create a staff key
• Search by DB2®Generated id value
• Number of rows in the result set
• Request data and then truncate the result set
• Delete all rows in the table without logging
• Use the view and rename table command "delete" column
• Widen the varchar Column
• Learning when to use a real view and a view that is not well known as a summary table)
• Learn the differences between declared temporary tables and common table expressions

Some of these technologies can improve performance, as you may see in the RDBMS reference manual. It recommends that you align data with the fourth paradigm, then, make some compromises before implementing the design to achieve the required performance. Other technologies make the work of application programmers easier and bring unexpected performance results.

Derived value
You may have learned somewhere in a university course about relational databases. Do not store derived values. After all, you can compute these values in the Assembly result set to avoid redundant data in the database and obtain the correct answer. DB2 introduces the generated columns in version 7 for the following reasons.

• The user requesting data may be using an application that you do not have control. If they are performing the SELECT * operation using an application that can complete the work by moving and clicking, they may not realize that what they really want to see is COMPENSATION, the COMMISSION and SALARY columns must be added. A generated column allows you to store this value and maintain its accuracy:

  create table employee (name char(10), salary dec(10,2), commission dec(10,2), compensation dec(11,2) generated always as (commission + salary))

  Use the following SQL statement to ensure the accuracy of the COMPENSATION column:

  insert into employee (name, salary, commission) values ('Blair',5,10)

  update employee set salary=0

• You must uniquely identify each row. We will discuss this later.
  
• It is required to create an index without case sensitivity. Next we will introduce this point.

Case Sensitive
Case sensitivity is powerful, and RDBMS can search faster if it knows that Greenland does not match greenland. However, if a user submits a search for "Macinnis", they may actually want your application to return "MacInnis ". For NAME search, you may want to create an index on the NAME column. However, the values in DB2 indexes are case sensitive. Make MacInnis = Macinnis very simple. You only need to use the UPPER or UCASE function:

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

However, this will force a table scan and you will not be able to get an index. This is the purpose of introducing the generated columns: if the standard access method is about name search, use the generated columns to store names in the uppercase/lowercase format:

CREATE TABLE EMPLOYEE (NAME VARCHAR(10),     NAME_UP         GENERATED ALWAYS AS (UPPER(name)))

Create an index in the upper-case version of this column:

CREATE INDEX NAME_IND ON EMPLOYEE ( NAME_UP )

This query can benefit from indexing and avoid table scanning:

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

Let's take a look at how to use the generated columns. Why do we need to number rows? The relational theory tells us that there is no internal order between rows and columns: You can specify the order when requesting data. But people like to give things numbers, from the page number in the book to the number on the sports shirt. You may know that data in computer science is seen as a tabular relational model. How many of your Users learn to view table-type data in Lotus and Excel workbooks with a downward increasing row number on the left of the screen? Most relational database management systems have internal RID row identifiers) or TID tuples ). OS/390®This is embodied in DB2 and Oracle, so that the program can easily identify rows without knowing the content. We didn't specify the DB2 row ID on Windows/UNIX/OS/2, because we allowed it to change: a dangerous feature in a potential primary key. DB2 does have other methods to use a column as the manual primary key.

Before using any of the basic methods that uniquely identify each row, try to find the real primary key: ask yourself this question: "If we write each row on paper, how should we uniquely identify it; assume that customers or suppliers call to ask about the status-how can we find the data they ask?" If you add a date and a time stamp on each sheet, This is the primary key.

Generate row ID
Let's start with the application that migrates data from other databases to DB2. If you migrate from SQL Server, you may get tired of preaching about the purity of the relationship and want to understand IDENTITY. The following describes how to use DB2 v7 to create an IDENTITY column:

CREATE TABLE T1     (C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY         (START WITH 10),           C2 INTEGER)

There is also the option to cache the id value in memory, which makes insertion faster, but if your system suffers a hardware or software crash when generating the IDENTITY value, the interval will be left in the Identification sequence. The default value is to increase integer 1 at a time, but you can also increase the value by other values such as 2 and 10. After insertion, you will naturally be curious about the generated values. You may need to know the value for the next logic of the application. On Windows, the IDENTITY_VAL_LOCAL () function recorded in x: \ sqllib \ release.txt can retrieve this value for you.

IDENTITY is unique in each table. Those Oracle fans will be glad to learn about DB2 version 7, and revision package 3 will bring the SEQUENCE column into DB2. Sequences are unique throughout the database-this is useful for values used in multiple tables. You can also loop through the sequence to reuse these values. SEQUENCE and IDENTITY are not data types: they use existing data types such as SMALLINT, INTEGER, or decimal number. INT and BIGINT are the best options. They give you good performance and a proper value range. Negative values are also allowed.

There are other methods to generate a manual primary key. If only one user accesses the table at a time and only one row is inserted at a time), the trigger is good. Define your primary key column as a default non-null value, so that when it is not specified in INSERT, it will get a value trigger that will overwrite the value of this virtual ):

CREATE TRIGGER AutoIncrement NO CASCADE BEFORE    INSERT ON Foobar      REFERENCING NEW AS n           FOR EACH ROW MODE DB2SQL SET (n.col1) =      (SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar )

DB2 also has a function named GENERATE_UNIQUE. This function uses the node number for multi-partition databases) and timestamps, so it can be used together with the enterprise extended version EEE. IDENTITY and SEQUENCE cannot be used with EEE before the emergence of the next major version of DB2. GENERATE_UNIQUE has two disadvantages: the DATA type CHAR (13) for bit data does not increase sequentially and is not as easy to use as the numerical DATA type.

The simpler solution is to mark the quantum query expression:

INSERT INTO Foobar (key_col, ...)      VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...)

Get one screen data
These methods are good for patterns and applications that have the opportunity to design before the database and application are put into production. But do you still remember the 4.5-letter words starting with AARIES flight booking environment simulation) and ACID atomicity, consistency, isolation, and durability? If you have booked a flight, you want them to remember this when you arrive at the airport. This is durability: Useful data is persistent. This means that even if you define a good primary key, some people may query the "First 20 rows" of the result set, regardless of the number of rows in the result set. Worse, someone asks you to display 21st to 40 rows. But wait a moment, you will raise an objection. The rows in the relational table are not ordered! For users who want to see 20 rows at a time in their Netscape browser, you seem to be talking about ice Island. DB2 allows you to sort the result set in real time and extract any number of rows from the beginning or end of the result set:

SELECT NAME FROM ADDRESS     ORDER BY NAME         FETCH FIRST 10 ROWS ONLY

SELECT NAME FROM ADDRESS     ORDER BY NAME DESC         FETCH FIRST 10 ROWS ONLY

Order by will force sorting of the entire result set in memory. Therefore, in ORDER to improve the performance of the DB2 server, we do not do this although sending only 10 rows to the client may improve the network performance ). If you do not care about the ORDER and only want to know that at least 10 rows meet the result set, clear order by to save sorting on the DB2 Server:

SELECT NAME FROM ADDRESS         FETCH FIRST 10 ROWS ONLY

So now we can see that you have selected a subset for the row number. Assuming that we number rows for some performance advantages, this will destroy the relational model. We have almost completely compromised, and we have committed six of the seven sins of the relationship. There is another link that you haven't violated: Let's give the line numbers in real time and sacrifice the performance and the purity of the relationship. How can we prove the correctness of this operation? Condemn it on the Internet.