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]