DB2 common dummies 1000 questions (3)

Source: Internet
Author: User
Tags table definition

When applying DB2, you may encounter some seemingly simple problems, especially for beginners.
To sum up, I will release it to you. I hope it will be helpful to you. At the same time, you are welcome to discuss, develop, and make progress together!

 

The characters below are mainly in lower case based on DB27.X.

This article does not need to be viewed by DB2 experts.
  
All characters are in lowercase.
  
91. How to Create a summary table of DB2?
The overview table function of DB2 is similar to the materialized view of ORACLE!
Syntax:
CREATESUMMARYTABLETABLE_NAMEAS (FULLSELECT )...
For example:
Define a refresh summary table:
CREATESUMMARYTABLETABLE_NAMEAS (SELECT * FROMTABLE_NAME1WHERECOL1 = AAA)
DATAINITIALLYDEFERREDREFRESHDEFERRED
DATAINITIALLYDEFERRED specifies that data cannot be inserted into the table as part of the CREATETABLE statement.
REFRESHDEFERRED specifies that the data in the table can be refreshed when any REFRESHTABLE statement is used!
  
92. How to refresh the summary table?
REFRESHTABLESUM_TABLE
SUM_TABLE is the summary table.
  
93. How to modify the summary table?
ALTERTABLESUM_TABLE...
  
94. How to create a temporary table?
Syntax:
DECLAREGLOBALTEMPORARYTABLETABLE_NAME
AS (FULLSELECT) DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
The first row specifies the name of the temporary table.
Row 2 defines the columns in the temporary table.
Row 3 specifies that the constant column is not copied from the source result table definition.
Row 4 specifies that if the WITHGOLD cursor is not enabled, all rows in the table will be deleted.
The fifth line does not record changes to the table.
For example:
DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMS
AS (SELECT * FROMBSEMPMS) DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
  
95. View Management?
How to Create a view:
CREATEVIEWVIEW_NAMEASSELECT * FROMTABLE_NAMEWHERE...
Delete View:
DROPVIEWVIEW_NAME
  
96. How do I know the content defined by the view?
SELECT * FROMSYSCAT. VIEWS in the TEXT column.
  
97. How to create an alias?
CREATEALIASALIAS_NAMEFORPRO_NAME
The following PRO_NAME can be TABLE, VIEW, ALIAS, And NICKNAME.
  
98. How to Create a sequence?
For example:
CREATESEQUENCESEQUENCE_NAME
STARTWITHSTART_NUMBER
INCREMENTBYVALUE1
NOMAXVALUE
NOCYCLE
CACHEMAXIMUMNUMBEROFSEQUENCEVALUES
The name of the sequence specified in the first line.
The second row specifies the starting value of the sequence.
Row 3 specifies the range of each addition.
Row 4 specifies that there is no maximum value limit.
Limit on the maximum value specified by the fifth line.
99. How to change the sequence?
ALTERSEQUENCESEQUENCE_NAME...
Modifiable Parameters
START_NUMBER of STARTWITH
VALUE1 of INCREMENT
NOMAXVALUE Value
NOCYCLE attributes
MAXIMUMNUMBEROFSEQUENCEVALUES maximum value

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.