IBM DB2 routine maintenance Summary

1. What are the levels of DB2 products?
Enterprise Edition nterpriseedition
Workgroupedition
Extended Enterprise version: iseextendedition
Personal Edition
Satellite version of satelliteedition
Micro version everyplace
 
2. What products can be connected to the DB2 database?
DB2 Client
Db2connect
Db2datapropagator
Db2net. Data
Db2datajoiner
Db2relationalconnect
WebSphere Application Server
And so on
 
3. What communication protocols does DB2 support?
TCP/IP
Netbios
Appg
IPX/SPX
Namepipe
And so on
 
4. What are the DB2 client products?
DB2 runtime client db2runtimeclient
DB2 management client db2administrationclient
DB2 Application Development client db2applicationdevelopmentclient
DB2 thin client db2thinclient
 
5. Can a database be installed on multiple database servers?
Yes
 
6. Which version of the stored procedure can be created using SQL statements?
After version 7.1
 
7. What relational extenders does DB2 provide?
Textextender
Image extensioner imageextender
Audio extensioner audioextender
Videoextender
Space Data extender spatialex.pdf
XML extender xmlextender
Net. searchextender
 
8. What is the DB2 installation directory structure in windows and OS/2 environments?
Use setup. EXE for Installation
 
/Sqllib installation root directory, including readme file
/Sqllib/adsm contains the adstar Distributed Storage Manager File
/Sqllib/bin contains the executable files of the DB2 Tool
/Sqllib/BND: bind file containing DB2 Tool
/Sqllib/CC contains the files required by the Operation Control Center
/Sqllib/CFG contains the default system configuration file
/Sqllib/Conv contains the code page conversion table File
/Sqllib/DB2 default instance directory
/Sqllib/db2das00 default DB2 Management Server Directory
/Sqllib/DOC contains the DB2 online manual
/Sqllib/function Default User-Defined Function directory
/Sqllib/function/unfenced default non-isolated user-defined correspondence directory
/Sqllib/Help Online Help File
/Sqllib/Java class library required by javadb2
Java 12 supports jdk1.2
/Sqllib/MISC contains HTML search server files
/Sqllib/MSG/prime contains information files
/Sqllib/QP: client file containing querypatroller
/Sqllib/samples contains sample programs and sample scripts
/Sqllib/spmlog contains the DB2 synchronization point Manager Log File
/Sqllib/thnsetup contains the thin client Installation File

9. What is the DB2 installation directory structure in UNIX and Linux environments?
Use db2setup. EXE for Installation
The following directories will also be created under the installed root directory:
The root directory for readme installation, including the README file
ADM contains system management tool files
Adsm contains the adstar Distributed Storage Manager File
Bin contains binary executable files of the DB2 Tool
Bnd contains the BIND file of the DB2 Tool
CC contains the files required by the Operation Control Center
CFG contains the default system configuration file
Conv contains the code page conversion table File
Doc contains the DB2 online manual
Function Default User-Defined Function directory
Function/unfenced default non-isolated user-defined correspondence directory
Install contains the installer
Instance contains instance scripts
Java class library required by javadb2
Libdb2 Library File
Map contains the db2ing file used by db2connect
Misc contains HTML search server files
Samples contains sample programs and sample scripts
MSG/$ L contains DB2 information files

10. Which command is used to install DB2 under Aix?
Installp command
 
11. How many DB2 databases can be installed in the same operating system?
Yes
 
12. How do I stop an instance?
Db2stop
 
13. How do I start an instance?
Db2start
 
14. How do I modify the value of the registration item?
Db2set can be modified
For example:
Sets a parameter for the current instance.
Db2setparameter = Value
 
Set a global Parameter
Db2setparameter = VALUE-G (lower case)
 
View the list of all variables that can be set in the configuration file Registry
DB2SET-LR (lower case)
 
15. How to execute operating system commands in CLP?
Add "! "As prefix
DB2 =>! Dirc :/
 
16. What should I do if the command in CLP is too long?
Use "/" as the continuation symbol
 
17. How to obtain the syntax information of DB2 commands?
DB2? Show all DB2 commands
DB2? Command: displays command information.
DB2? Sqlnnnn displays the explanation of this sqlcode
DB2? Db2nnnn displays the explanation of this DB2 Error
 
18. How do I view the current CLP settings?
DB2 => listcomandoptions
 
19. How to update the specific settings of the current CLP session?
Db2updatecommandoptionsusingoptions...
 
20. Which command can be used to call commandwindows?
Db2cmd command
 
21. What is the default name of the management server?
Db2as in UNIX
Db2das00 in Windows

22. Commonly Used commands for managing DB2 server instances?
Db2adminstart start the DB2 management server instance
Db2adminstop stop the DB2 management server instance
Create a DB2 management server instance in dasicrtunix
Delete A DB2 management server instance under dasidropunix
Create a DB2 management server instance under db2admincreatewindowsoros/2
Delete A DB2 management server instance under db2admindropwindowsoros/2
Db2getadmincfg displays the configuration parameters of the DB2 Management Server
Db2updateadmincfg modify the configuration parameters of the DB2 Management Server
Db2resetadmincfg sets the configuration parameters of the DB2 Management Server to the default value.
 
23. What types of DB2 directories are divided?
System database directory
Local Database directory
Node directory
DCs directory
Manage node Directories
 
24. How do I view the system database directory?
Listdbdirectory
 
25. How to view the database server directory?
Listnodedirectory
 
26. What is the default port of the DB2 instance server?
50000
The server name is db2cdb2.
 
27. What are the authentication types on the db2udb server?
Server
Server_encrypt
Client
DCE
Dce_server_encrypt
Kerberos
Krb_server_encrypt
 
28. What are the authentication types of the DB2 client?
Server
Server_encrypt
DCS
Dcs_encrypt
Client
DCE
 
29. What types of permissions does DB2 have?
Sysadm System Management permission
Sysctrl system control permission
Sysmaint system maintenance permission
Dbadm database management permission
Permission to load a table
 
30. Which of the following permissions cannot be granted through grant?
Sysam
Sysctrl
Sysmaint
To modify this permission, you must modify the Database Manager configuration parameters.
 
31. What types of tables are there?
Permanent Table (base table)
Temporary table (description table)
Temporary table (derived table)
 
32. How many tables does a user have?
Select * fromsysibm. systableswherecreator = 'user'
 
33. How do I know the functions under a user?
Select * fromiwh. userfunction
Select * fromsysibm. sysfunctions

34. How do I know the number of views under a user?
Select * fromsysibm. sysviewswherecreator = 'user'
 
35. How do I know the current DB2 version?
Select * fromsysibm. sysversions
 
36. How do I know the number of triggers under a user?
Select * fromsysibm. systriggerswhereschema = 'user'
 
37. How do I know the status of tablespace?
Select * fromsysibm. systablespaces
 
38. How do I know the sequence status?
Select * fromsysibm. syssequences
 
39. How do I know the schema status?
Select * fromsysibm. sysschemata
 
40. How do I know the index status?
Select * fromsysibm. sysindexes
 
41. How do I know the status of table fields?
Select * fromsysibm. syscolumnswheretbname = 'aaa'
 
42. How do I know the DB2 data type?
Select * fromsysibm. sysdatatypes
 
43. How do I know the bufferpools status?
Select * fromsysibm. sysbufferpools
 
44. Restrictions on modifying fields in the DB2 table?
Only the varchar2 type can be modified and can be increased or not decreased.
 
45. How to view the table structure?
Describletabletable_name
Or
Describleselect * fromschema. table_name
 
46. How to quickly clear a large table?
Altertabletable_nameactivenotloggedinitallywithemptytable
Alter tabletable_name active not logged initally with emptytable
The Delete action between two commit does not record logs.
Commit
Alter table AB activate not logged intially
Delete AB where ID> 1000
Commit
(The created table AB does not record the log: Create Table AB (ID INT) not logged initially)
 
47. How to view database packages?
Select * fromsyscat. Packages
 
48. How to view the stored procedure of the database?
Select * fromsyscat. Procedures
 
49. How to view table constraints?
Select * fromsyscat. checkswheretabname = 'aaa'
 
50. How can I view the complete reference constraints of a table?
Select * fromsyscat. referenceswheretabname = 'aaa'

51. What is the default case name for installing DB2?
In Windows or OS/2, DB2 is used by default.
In Linux or UNIX, the default value is db2inst1.
 
52. What is the default account after installation?
In Windows or OS/2, the default value is db2admin.
In Linux or UNIX, the default value is db2as.

53. What types of cases are there?
Client (customer)
Standalone (independent)
Satellite (satellite)
Eedb2
Eee
 
54. How to create an example?
Db2icrtinstname <... parameters>
 
55. How do I list all the available cases on the system?
Db2ilist
 
56. How do I know which example of the current conversation is used?
Getinstance
 
57. How to update the instance configuration?
Db2iupdt
 
58. How to delete an example?
Db2idropinstance_name
The procedure is as follows:
Stop all applications in the case
Execute db2terminate on all open command lines
Run db2stop
Back up the example directory pointed out by the db2instprof registration variable
All logged-on users in the exit case
Use db2idrop
You can also delete the ID.
 
59. How do I list all products with license information on the local system?
DB2LICM-L
 
60. How to add a product license?
DB2LICM-AFILENAME
 
61. How to delete a product license?
DB2LICM-RPRODUCTPASSWORD
 
62. How to update the number of purchased licenses?
DB2LICM-U
 
63. How to force only the purchased quantity to be used?
DB2LICM-EHARD

64. How to update the license policy types used on the system?
DB2LICM-PREGISTEREDCONCURRENT
 
65. How to update the number of processors on the system?
DB2LICM-N
 
66. How do I query the version information recorded in the license file?
DB2LICM-V
 
67. How can I query the help information of db2licm?
DB2LICM-H
 
68. What table spaces does a database contain?
One directory tablespace
One or more user tablespaces
One or more temporary tablespace
 
69. Which two types of tablespaces can be used to control the movement between data and buckets?
System Management space (SMS)
Database Management space (DMS)
 
70. How to list the contents of the system database directory?
Listdatabasedirectory
 
71. Is createdatabase an SQL command?
No, it's a system command
 
72. How can I view the configuration file content of database ABC?
Getdatabaseconfigurationforabc
 
73. How do I set the ABC parameter of the database to the default value?
Resetdatabaseconfigurationforabc
 
74. How to modify the value of the ABC configuration parameter in the database?
Updatedatabaseconfigurationforabc
Using
 
75. How to restart the database?
Restartdatabasedatabase_name
 
76. How to activate the database?
Activatedatabasedatabase_name
 
77. How do I stop a database?
Deactivatedatabasedatabase_name

78. How to delete a database?
Dropdatabasedatabase_name
 
79. How to Establish a mode?
Createschemaschema_name
 
80. How do I set the mode?
Setschema = Schema_name
 
81. What types of tables are there?
Basic table
Result table
Summary table
Type table
Child type
Sub-table
Declared temporary table
System temporary table

82. How to define a sequence?
Createsequenceorderseqstartwith1incrementby1nomaxvaluenocyclecache24

83. How to put the table in the check pending status?
Setintegritytable_nameoff
 
84. How to obtain the table exclusion lock?
Locktabletable_nameinexclusivemode
 
85. How can I adjust the table to the online status without checking the data?
Setintegrityfortable_nameallimmediateunchecked

86. How can I unlock a table?
Commit
 
87. How do I disable table logs?
Altertabletable_nameactivenotloggedinially
 
88. How to delete a table?
Dropschema. table_name
 
89. How to rename a table?
Renametable_oldtotable_new
 
90. How to get the current time?
Selectcurrenttimestampfromsysibm. sysdummy1

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 the close () 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

DB2 Functions
135. AVG ()
Returns the average value of a set of values.
Selectavg (salary) frombsempms;
 
136. Corr (), correlation ()
Returns the relational coefficient of a number.
Selectcorrelation (salary, bonus) frombsempms;
 
137. Count ()
Returns the number of rows or values.
Selectcount (*) frombsempms;
 
138. covar (), covariance ()
Returns the covariance of a value.
Selectcovar (salary, bonus) frombsempms;
 
139. Max ()
Returns the maximum value of a group of values.
Selectmax (salary) frombsempms;

140. Min ()
Returns the minimum value of a set of values.
Selectmin (salary) frombsempms;
 
141. stddev ()
Returns the standard deviation of a set of values.
Selectstddev (salary) frombsempms;
 
142. sum ()
Returns the sum.
Selectsum (salary) frombsempms;
 
143. var (), variance ()
Returns the variance of a set of values.
Selectvariance (salary) frombsempms;
 
144. Abs (), absval ()
Returns the absolute value of a parameter.
Selectabs (-3.4) frombsempms;
 
145. ACOs ()
Returns the arc cosine of a parameter.
Selectacos (0.9) frombsempms;
 
146. ASCII ()
Returns the ASCII code of the leftmost character of the integer parameter.
Selectascii ('R') frombsempms;
 
147. asin ()
Returns the arcsin function of the Angle Parameter in radians.
Selectasin (0.9) frombsempms;
 
148. atan ()
Returns the arc tangent of a parameter, which is an Angle Parameter in radians.
Selectatan (0.9) frombsempms;
 
149. atan2 ()
Returns the arc tangent of the angle X and Y coordinates in radians.
Selectatan2 (0.5, 0.9) frombsempms;
 
150. bigint ()
Returns the 64-bit integer representation of a number or string in an integer constant.
Selectbigint (emp_no) frombsempms;
 
151. Ceiling () orceil ()
Returns the smallest integer value greater than or equal to the parameter.
Selectceiling (3.56) frombsempms;
Selectceil (4.67) frombsempms;
 
152. Char ()
Returns the string representation of the datetime type, String, integer, decimal or double-precision floating point number.
Selectchar (salary, ',') frombsempms;
 
153. CHR ()
Returns the characters with the specified ASCII code.
Selectchar (167) frombsempms;

154. Concat ()
Returns the connection of two strings.
Selectconcat (emp_no, emp_nam) frombsempms;
 
155. Year ()
Returns the year of the value.
Selectyear ('2014/1/02 ') frombsempms;
 
156. varchar ()
Returns the string, date type, and variable-length string representation of the graphic string.
Selectvarchar (emp_nam, 50) frombsempms;
 
157. ucase () orupper ()
Returns the string in uppercase.
Selectucase (emp_nam) frombsempms;
Selectupper (emp_no) frombsempms;

158. truncate () ortrunc ()
This value is truncated from the right of the decimal point in the expression and returned.
Selecttruncate (345.6789, 2) frombsempms;
 
159. Time ()
Returns the time in a value.
Selecttime ('2017-03-19.12.30.123456 ') frombsempms;
 
160. substr (exp1, exp2)
Returns the substring of the exp1 string starting with exp2.
Selectsubstr ('cdnjfdjfjd ', 5) frombsempms;
Selectsubstr ('cdnjfdjfjd ', 5, 2) frombsempms;
 
161. SQRT ()
Returns the square root of the parameter.
Selectsqrt (36) frombsempms;
 
162. Space ()
Returns a string of the specified length, including spaces.
Selectspace (10) frombsempms;
 
163. Second ()
Returns the second of a value.
Selectsecond ('18: 34: 32') frombsempms;
 
164. rtrim ()
Removes spaces at the end of a string.
Selectrtrim ('comment') frombsempms;
 
165. Round (exp1, exp2)
Returns the rounded value starting at the position exp2 on the right of the exp1 decimal point.
Selectround (2345.6789, 2) frombsempms;
 
166. Replace (exp1, exp2, exp3)
Use exp3 to replace all exp2 in exp1
Selectchar (replace ('romandd', 'ndd', 'ccb'), 10) frombsempms;
 
167. Repeat (exp1, exp2)
Returns the string that exp1 repeats exp2.
Selectchar (Repeat ('repeat', 3), 21) frombsempms;
 
168. Real ()
Returns the single-precision floating-point representation of a value.
Selectreal (10) frombsempms;
 
169. Rand ()
Returns a random floating point number between 0 and 1.
Selectrand () frombsempms;
 
170. Power (exp1, exp2)
Returns the exp2 power of exp1.
Selectpower (2, 5) frombsempms;
 
171. posstr (exp1, exp2)
Returns the position of exp2 in exp1.
Select ('abcdefgh', 'D') frombsempms;
 
172. nullif (exp1, exp2)
If exp1 = exp2, It is null; otherwise, it is exp1.

173. nodenumber ()
Returns the Partition Number of the row.
Selectnodenumber (emp_no) frombsempms;
 
174. Month ()
Returns the month of a value.
Selectmonth ('2014/1/20') frombsempms;
 
175. Mod (exp1, exp2)
Returns the remainder of exp1 divided by exp2.
Selectmod (20, 8) frombsempms;
 
176. Minute ()
Returns the minute of a value.
Selectminute ('18: 34: 23') frombsempms;
 
177. ltrim ()
Removes spaces before a string.
Selectltrim ('cddd') frombsempms;
 
178. Hour ()
Returns the hour of a value.
Selecthour ('18: 34: 23') frombsempms;
 
179. Double ()
If the parameter is a numeric expression, return the corresponding floating point number. If the parameter is a string expression, return the string expression of the number.
Selectdouble ('200') frombsempms;
 
180. exp ()
Returns the exponential function of the parameter.
Selectexp (2) frombsempms;
 
181. Float ()
Returns the floating point representation of a number.
Selectfloat (789) frombsempms;
 
182. Floor ()
Returns the maximum integer that is less than or equal to the parameter.
Slectfloor (88.93) frombsempms;
 
183. HEX ()
Returns a hexadecimal representation of the string value.
Selecthex (16) frombsempms;