Summary of routine maintenance of IBM DB2 (6)

Source: Internet
Author: User
Tags db2 port ibm db2 table definition

The db2 tutorial is: Daily maintenance Summary of IBM DB2 (6 ). 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
  
100. How to delete a sequence?
Drow.quencesequence_name
  
101. Which of the following file formats does DB2 support IMPORT?
Includes DEL, ASC, IXF, and WSF.

102. What file formats does DB2 support exporting (EXPORT?
Such as DEL, IXF, and WSF.
ASC format is not supported.
  
103. What file formats does DB2 support LOAD?
Such as DEL, ASC, and IXF.
The WSF format is not supported.
  
104. Which of the following file formats does DB2 support DB2MOVE?
Such as IXF.
The ASC, DEL, and WSF formats are not supported.
  
105. What are the two components of DB2 database monitoring?
SNAPSHOTMONITOR returns snapshots of database activity at a specific time point.
Event monitoring records event data.
  
106. What is the data element type monitored by the system?
COUNTER (COUNTER) records the number of activities.
Current Value of the measurement entry (GAUGE.
WATERMARK refers to the maximum or minimum number of elements that are monitored.
Details of reference types of INFORMATION monitoring activities.
The time when a TIMESTAMP activity occurs.
TIME returns the TIME spent by an activity.
  
107. How do I know the number of pages required by the monitoring heap?
(NUMBEROFMONITORINGAPPLICATIONS + 1) * (NUMBEROFDATABASES * (800 + (NUMBEROFTABLES
  
ACCESSED * 20) + (NUMBEROFAPPLICATIONSCONNECTED + 1) * (200 + (NUMBEROFTABLE
  
SPACES * 100)/4096
Its size is controlled by the MON_HEAD_SZ parameter.
  
108. How to create an event monitor?
CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFILE 'd: \ TEMP'
  
109. How to activate the event monitor?
SETEVENTMONITORTABLEMONSTATE1
  
110. How do I stop the event monitor?
SETEVENTMONITORTABLEMONSTATE0
  
111. How do I query the monitor status?
SELECTEVMONNAME, EVENT_MON_STATE (EVMONNAME) FROMSYSCAT. EVENTMONITORS

112. How do I delete event monitors?
DROPEVENTMONITORTABLEMON
  
113. What is the difference between creating a pipeline event MONITOR on UNIX and WINDOWS (for PIPE, see MONITOR for EVNT?
Step 1: Define the event monitor
UNIX:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE '/TMP/EVPIPE1'
WINDOWS:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE '\. \ TMP \ EVPIPE1'

Step 2: Create a named pipe
UNIX:
You can use the MKFIFO () function or the MKFIFO command.
WINDOWS:
You can use the CREATENAMEDPIPE () function. The Pipe name is the same as that specified by CREATEEVENTMONITOR.
  
Step 3: Open the Named Pipe
UNIX:
Use the OPEN () function.
WINDOWS:
Use the CONNECTNAMEDPIPE () function.
You can also use the DB2EVMON command, for example:
DB2EVMON-DBSAMPLE-EVMSTMB2
  
Step 4: Activate the named pipe event monitor
Unless the named pipe event monitor is automatically activated
SETEVENTMONITORSTMB2STATE1

Step 5: read data from the Named Pipe
UNIX:
You can use the READ () function.
WINDOWS:
You can use the READFILE () function.

Step 6: Stop the event monitor
SETEVENTMONITORSTMB2STATE0
  
Step 7: Close the Named Pipe
UNIX:
You can use CLOSE (

[1] [2] Next page

The db2 tutorial is: Daily maintenance Summary of IBM DB2 (6 ).) Function.
WINDOWS:
You can use the DISCONNECTNAMEDPIPE () function.
  
Step 8: Delete the named MPs queue
UNIX:
You can use the UNLINK () function.
WINDOWS:
You can use the CLOSEHANDLE () function.
  
114. DB2 SQL statement category
DCL: Data Control language that provides access permissions to database objects.
DDL: Data Definition Language, creation, modification, and deletion of database objects.
DML: data manipulation language used to insert, update, and delete data.

115. What permissions does DCL have?
CONTROL permission: if a user creates an object, the user can access the object completely.
GRANT the permission to the user.
The REVOKE statement revokes the permission of a user.

116. What are DDL functions?
CREATE
DECLARE
ALTER
DROP
And so on

117. What about DML?
INSERT
SELECT
UPDATE
DELETE
And so on
  
118. Is there a boolean type in DB2?
No
  
119. How do I query the built-in functions of DB2?
Built-in document ADMINISTION --> SQLREFERENCE --> FUNCTIONS

120. How to execute the DB2 script file?
DB2-VTFFILENAME
  
121. What is ROWNUM () like ORACLE in DB2?
ROW_NUMBER () OVER ()
  
122. How does DB2 get an explanation of the error code?
DB2? SQLCODE

123. What is the function for converting VARCHAR to INTEGER in DB2?
CAST ()
  
124. The function for converting INTEGER in DB2 to VARCHAR is?
CHAR ()
  
125. What is the function for converting VARCHAR to DATE in DB2?
DATE ()
  
126. The DATE function in DB2 to convert to VARCHAR is?
CHAR ()
  
127. Can I modify the TRIGGER in DB2?
No. Only reconstruction can be deleted.
  
128. How do I know the DB2 port number in WINDOWS?
\ WINNT \ SYSTEM32 \ DRIVERS \ ETC \ SERVICES
  
129. How does DB2 execute stored procedures?
DB2CALLPROCEDURE_NAME
  
130. How do I access the doscommand of DB2?
DB2CMD
  
131. How do I obtain the DB2 process number?
DB2LISTAPPLICATIONS
  
132. How to kill the DB2 process?
FORCEAPPLICATION (ID)
  
133. After user A installs DB2, how does user B start the DATABASE?
Add the. PROFILE file under user B.
./HOME/DB2INST/SQLLIB/DB2PROFILE
  
134. What are ORACLE-like snapshots in DB2?
SUMMARYTABLE

Previous Page [1] [2]

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.