The db2 tutorial we are watching is: IBM DB2 daily maintenance summary (VI). 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
100.How to delete a sequence?
DROPSEQUENCESEQUENCE_NAME
101. Is there any file format for DB2 that supports import (IMPORT)?
There are: DEL, ASC, IXF, WSF, etc.
102. Is there any file format for DB2 that supports export (EXPORT)?
There are: DEL, IXF, WSF, etc.
ASC format is not supported.
103. Is there any file format for DB2 that supports loading (LOAD)?
There are: DEL, ASC, IXF, etc.
WSF format is not supported.
104. Is there any file format for DB2MOVE that supports DB2?
There are: IXF, etc.
ASC, DEL, WSF formats are not supported.
105. Two components of DB2 database monitoring?
SNAPSHOTMONITOR returns snapshots of database activity at a specific point in time.
Event monitoring (EVENTMONITOR) records the data of events.
106. What is the type of data element that the system monitors?
The counter (COUNTER) records the number of times the activity occurs.
Measure (GAUGE) The current value of the measurement entry.
The maximum or minimum value that the waterline (WATERMARK) reaches from the monitoring element.
Information (INFORMATION) details of the reference type of monitoring activity.
The date and time when the time point (TIMESTAMP) activity occurs.
Time (TIME) returns the time spent on an activity.
107. How to know the number of pages required to monitor the heap?
(NUMBEROFMONITORINGAPPLICATIONS+1)*(NUMBEROFDATABASES*(800+(NUMBEROFTABLES
ACCESSED*20)+((NUMBEROFAPPLICATIONSCONNECTED+1)*(200+(NUMBEROFTABLE
SPACES*100)))))/4096
Its size is controlled by the parameter MON_HEAD_SZ.
108.How to establish an event monitor?
CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFILE'D:\TEMP'
109. How to activate the event monitor?
SETEVENTMONITORTABLEMONSTATE1
110.How to stop the event monitor?
SETEVENTMONITORTABLEMONSTATE0
111. How to query the status of the monitor?
SELECTEVMONNAME,EVENT_MON_STATE(EVMONNAME)
112. How to delete event monitor?
DROPEVENTMONITORTABLEMON
What is the difference between creating a pipeline event monitor on WINDOWS (PIPE means EVNT, see MONITOR)?
Step 1: Define the event monitor
UNIX:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'/TMP/EVPIPE1'
WINDOWS:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'\\.\TMP\EVPIPE1'
Step 2: Establish a named pipeline
UNIX:
You can use the MKFIFO() function or the MKFIFO command.
WINDOWS:
You can use the CREATENAMEDPIPE() function, and the pipeline name is the same as the specified name of CREATEEVENTMONITOR.
Step 3: Open the named pipe
UNIX:
Use the OPEN() function.
WINDOWS:
Use the CONNECTNAMEDPIPE() function.
You can also use the DB2EVMON command, such as:
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(
WINDOWS:
The DISCONNECTNAMEDPIPE() function can be used.
Step 8: Delete the named pipe
UNIX:
The UNLINK() function can be used.
WINDOWS:
You can use the CLOSEHANDLE() function.
114.Category of SQL statements in DB2
DCL: Data control language, providing access to database objects.
DDL: Data definition language, creating, modifying, and deleting database objects.
DML: a data manipulation language, used to insert, update, and delete data.
What are the permissions?
CONTROL permissions: If the user creates an object, the user can fully access the object.
The GRANT statement grants permissions to the user.
The REVOKE statement revokes a user's permission.
What are there?
CREATE
DECLARE
ALTER
DROP
wait
What are there?
INSERT
SELECT
UPDATE
DELETE
wait
118. Is there a Boolean type in DB2?
No
119. How to query DB2's built-in functions?
In-house document ADMINISTION-->SQLREFERENCE-->FUNCTIONS
120. How to execute DB2 script files?
DB2-VTFFILENAME
121.What is the ROWNUM() of ORACLE in DB2?
ROW_NUMBER()OVER()
122. How to get the description of the error code in DB2?
DB2?SQLCODE
123. Is the function of converting VARCHAR to INTEGER in DB2 as?
CAST()
124. What is the function of converting INTEGER into VARCHAR in DB2?
CHAR()
125. Is the function of converting VARCHAR to DATE in DB2?
DATE()
126. Is the function of converting DATE into VARCHAR in DB2?
CHAR()
127. Can TRIGGER in DB2 be modified?
No, only rebuild can be deleted
How to know the port number of DB2?
\WINNT\SYSTEM32\DRIVERS\ETC\SERVICES
129.How to execute stored procedures in DB2?
Can DB2CALLPROCEDURE_NAME
130. How to enter the DOS command method of DB2?
DB2CMD
131. How to get the process number of DB2?
DB2LISTAPPLICATIONS
132. How to kill the DB2 process?
FORCEAPPLICATION(ID)
After the user installs DB2, how to start DATABASE with user B?
Add in .PROFILE under B user
./HOME/DB2INST/SQLLIB/DB2PROFILE
134. What is the snapshot similar to ORACLE in DB2?
SUMMARYTABLE
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
100.How to delete a sequence?
DROPSEQUENCESEQUENCE_NAME
101. Is there any file format for DB2 that supports import (IMPORT)?
There are: DEL, ASC, IXF, WSF, etc.
102. Is there any file format for DB2 that supports export (EXPORT)?
There are: DEL, IXF, WSF, etc.
ASC format is not supported.
103. Is there any file format for DB2 that supports loading (LOAD)?
There are: DEL, ASC, IXF, etc.
WSF format is not supported.
104. Is there any file format for DB2MOVE that supports DB2?
There are: IXF, etc.
ASC, DEL, WSF formats are not supported.
105. Two components of DB2 database monitoring?
SNAPSHOTMONITOR returns snapshots of database activity at a specific point in time.
Event monitoring (EVENTMONITOR) records the data of events.
106. What is the type of data element that the system monitors?
The counter (COUNTER) records the number of times the activity occurs.
Measure (GAUGE) The current value of the measurement entry.
The maximum or minimum value that the waterline (WATERMARK) reaches from the monitoring element.
Information (INFORMATION) details of the reference type of monitoring activity.
The date and time when the time point (TIMESTAMP) activity occurs.
Time (TIME) returns the time spent on an activity.
107. How to know the number of pages required to monitor the heap?
(NUMBEROFMONITORINGAPPLICATIONS+1)*(NUMBEROFDATABASES*(800+(NUMBEROFTABLES
ACCESSED*20)+((NUMBEROFAPPLICATIONSCONNECTED+1)*(200+(NUMBEROFTABLE
SPACES*100)))))/4096
Its size is controlled by the parameter MON_HEAD_SZ.
108.How to establish an event monitor?
CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFILE'D:\TEMP'
109. How to activate the event monitor?
SETEVENTMONITORTABLEMONSTATE1
110.How to stop the event monitor?
SETEVENTMONITORTABLEMONSTATE0
111. How to query the status of the monitor?
SELECTEVMONNAME,EVENT_MON_STATE(EVMONNAME)
112. How to delete event monitor?
DROPEVENTMONITORTABLEMON
What is the difference between creating a pipeline event monitor on WINDOWS (PIPE means EVNT, see MONITOR)?
Step 1: Define the event monitor
UNIX:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'/TMP/EVPIPE1'
WINDOWS:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'\\.\TMP\EVPIPE1'
Step 2: Establish a named pipeline
UNIX:
You can use the MKFIFO() function or the MKFIFO command.
WINDOWS:
You can use the CREATENAMEDPIPE() function, and the pipeline name is the same as the specified name of CREATEEVENTMONITOR.
Step 3: Open the named pipe
UNIX:
Use the OPEN() function.
WINDOWS:
Use the CONNECTNAMEDPIPE() function.
You can also use the DB2EVMON command, such as:
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 we are watching is: IBM DB2 daily maintenance summary (VI). )function.WINDOWS:
The DISCONNECTNAMEDPIPE() function can be used.
Step 8: Delete the named pipe
UNIX:
The UNLINK() function can be used.
WINDOWS:
You can use the CLOSEHANDLE() function.
114.Category of SQL statements in DB2
DCL: Data control language, providing access to database objects.
DDL: Data definition language, creating, modifying, and deleting database objects.
DML: a data manipulation language, used to insert, update, and delete data.
What are the permissions?
CONTROL permissions: If the user creates an object, the user can fully access the object.
The GRANT statement grants permissions to the user.
The REVOKE statement revokes a user's permission.
What are there?
CREATE
DECLARE
ALTER
DROP
wait
What are there?
INSERT
SELECT
UPDATE
DELETE
wait
118. Is there a Boolean type in DB2?
No
119. How to query DB2's built-in functions?
In-house document ADMINISTION-->SQLREFERENCE-->FUNCTIONS
120. How to execute DB2 script files?
DB2-VTFFILENAME
121.What is the ROWNUM() of ORACLE in DB2?
ROW_NUMBER()OVER()
122. How to get the description of the error code in DB2?
DB2?SQLCODE
123. Is the function of converting VARCHAR to INTEGER in DB2 as?
CAST()
124. What is the function of converting INTEGER into VARCHAR in DB2?
CHAR()
125. Is the function of converting VARCHAR to DATE in DB2?
DATE()
126. Is the function of converting DATE into VARCHAR in DB2?
CHAR()
127. Can TRIGGER in DB2 be modified?
No, only rebuild can be deleted
How to know the port number of DB2?
\WINNT\SYSTEM32\DRIVERS\ETC\SERVICES
129.How to execute stored procedures in DB2?
Can DB2CALLPROCEDURE_NAME
130. How to enter the DOS command method of DB2?
DB2CMD
131. How to get the process number of DB2?
DB2LISTAPPLICATIONS
132. How to kill the DB2 process?
FORCEAPPLICATION(ID)
After the user installs DB2, how to start DATABASE with user B?
Add in .PROFILE under B user
./HOME/DB2INST/SQLLIB/DB2PROFILE
134. What is the snapshot similar to ORACLE in DB2?
SUMMARYTABLE
Previous page [1] [2]