The db2 tutorial I am looking at is: 1000 common fool questions for DB2 (three). When applying DB2, you may encounter some seemingly simple problems. Especially for novices, I'm here to
Let me summarize and post it to everyone. I hope it will be helpful to everyone. At the same time, everyone is welcome to discuss together, develop together and make progress together!
The following are mainly based on the characters. The following characters are lowercase.
This article is not necessary for DB2 experts.
All characters are lowercase.
91. How to create a summary table for DB2?
DB2's summary table function is similar to ORACLE's entity view!
The syntax is:
CREATESUMMARYTABLETABLE_NAMEAS(FULLSELECT)...
For example:
Define a summary table that can be refreshed:
CREATESUMMARYTABLETABLE_NAMEAS(SELECT*FROMTABLE_NAME1WHERECOL1= AAA )
DATAINITIALLYDEFERREDREFRESHDEFERRED
Among them, DATAINITIALLYDEFERRED stipulates 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 whenever the REFRESHTABLE statement is used!
92. How to refresh the summary table?
REFRESHTABLESUM_TABLE
Where SUM_TABLE is the summary table.
93. How to modify the summary table?
ALTERTABLESUM_TABLE...
94. How to create a temporary table?
grammar:
DECLAREGLOBALTEMPORARYTABLETABLE_NAME
AS(FULLSELECT)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
The first line specifies the name of the temporary table.
The second row specifies the definition of columns in the temporary table.
The third row specifies an identity column that is not copied from the source result table definition.
The fourth row stipulates that if the WITHGOLD cursor is not opened, all rows of the table will be deleted.
The fifth line stipulates that no changes to the table are recorded.
For example:
DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMS
AS(SELECT*FROMBSEMPMS)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
95. View management?
How to create a view:
CREATEVIEWVIEW_NAMEASSELECT*FROMTABLE_NAMEWHERE...
Delete the view:
DROPVIEWVIEW_NAME
96. How to know what the view definition is?
In the TEXT column in SELECT*.
97. How to create an alias?
CREATEALIASALIAS_NAMEFORPRO_NAME
The subsequent PRO_NAME can be TABLE, VIEW, ALIAS, NICKNAME, etc.
98.How to create a sequence?
For example:
CREATESEQUENCESEQUENCE_NAME
STARTWITHSTART_NUMBER
INCREMENTBYVALUE1
NOMAXVALUE
NOCYCLE
CACHEMAXIMUMNUMBEROFSEQUENCEVALUES
The first line specifies the name of the sequence.
The second line specifies the start value of the sequence.
The third line stipulates the amount of new additions each time.
The fourth line stipulates that there is no maximum numerical limit.
The fifth element stipulates the maximum numerical limit.
99.How to change the sequence?
ALTERSEQUENCESEQUENCE_NAME...
Parameters that can be modified
STARTWITH's START_NUMBER
VALUE1 of INCREMENT
NOMAXVALUE value
NOCYCLE attribute
MAXIMUMNUMBEROFSEQUENCEVALUES maximum value
Let me summarize and post it to everyone. I hope it will be helpful to everyone. At the same time, everyone is welcome to discuss together, develop together and make progress together!
The following are mainly based on the characters. The following characters are lowercase.
This article is not necessary for DB2 experts.
All characters are lowercase.
91. How to create a summary table for DB2?
DB2's summary table function is similar to ORACLE's entity view!
The syntax is:
CREATESUMMARYTABLETABLE_NAMEAS(FULLSELECT)...
For example:
Define a summary table that can be refreshed:
CREATESUMMARYTABLETABLE_NAMEAS(SELECT*FROMTABLE_NAME1WHERECOL1= AAA )
DATAINITIALLYDEFERREDREFRESHDEFERRED
Among them, DATAINITIALLYDEFERRED stipulates 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 whenever the REFRESHTABLE statement is used!
92. How to refresh the summary table?
REFRESHTABLESUM_TABLE
Where SUM_TABLE is the summary table.
93. How to modify the summary table?
ALTERTABLESUM_TABLE...
94. How to create a temporary table?
grammar:
DECLAREGLOBALTEMPORARYTABLETABLE_NAME
AS(FULLSELECT)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
The first line specifies the name of the temporary table.
The second row specifies the definition of columns in the temporary table.
The third row specifies an identity column that is not copied from the source result table definition.
The fourth row stipulates that if the WITHGOLD cursor is not opened, all rows of the table will be deleted.
The fifth line stipulates that no changes to the table are recorded.
For example:
DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMS
AS(SELECT*FROMBSEMPMS)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
95. View management?
How to create a view:
CREATEVIEWVIEW_NAMEASSELECT*FROMTABLE_NAMEWHERE...
Delete the view:
DROPVIEWVIEW_NAME
96. How to know what the view definition is?
In the TEXT column in SELECT*.
97. How to create an alias?
CREATEALIASALIAS_NAMEFORPRO_NAME
The subsequent PRO_NAME can be TABLE, VIEW, ALIAS, NICKNAME, etc.
98.How to create a sequence?
For example:
CREATESEQUENCESEQUENCE_NAME
STARTWITHSTART_NUMBER
INCREMENTBYVALUE1
NOMAXVALUE
NOCYCLE
CACHEMAXIMUMNUMBEROFSEQUENCEVALUES
The first line specifies the name of the sequence.
The second line specifies the start value of the sequence.
The third line stipulates the amount of new additions each time.
The fourth line stipulates that there is no maximum numerical limit.
The fifth element stipulates the maximum numerical limit.
99.How to change the sequence?
ALTERSEQUENCESEQUENCE_NAME...
Parameters that can be modified
STARTWITH's START_NUMBER
VALUE1 of INCREMENT
NOMAXVALUE value
NOCYCLE attribute
MAXIMUMNUMBEROFSEQUENCEVALUES maximum value