Detailed DB2 command

xiaoxiao2021-03-13  198

There are several important commands when using DB2 RDBMS. For additional information about these commands and other database commands, see the DB2 documentation. These commands are used for the following:

Start a DB2 interactive session

To start a interactive session, enter the following command:

DB2

Compile DB2 Server Node Directory

To prepare a DB2 server node directory, enter the following command:

DB2 Catalog TCPIP Node DB2Node Remote Hostname Server Service_name

Where DB2Node is a system name installed in the DB2 client (it must be unique in the node directory list), HostName is a fully qualified system name installed in the DB2 server, service_name is the connection port name defined in the Services file. Preparation remote DB2 database directory

To prepare a remote database directory, enter the following command:

DB2 Catalog Database DB_NAME AS Alias_name At Node DB2Node

Where DB_NAME is the name of the remote database, alias_name is the name of the client instance, and DB2Node is a system name installed a DB2 client.

Connect to DB2 servers

To connect to a DB2 server, enter the following command:

DB2 Connect to Database User Name Using Password

Where Database is the database name or alias in the system database directory, name is the username of the database instance owner, and Password is the password of the previous user.

Run the script

To run a script, enter the following command:

DB2 -F script_name -o -t -z log_name

Script_name is the name of the script, log_name is the name of the log file.

End a DB2 session

To log out of an interactive session, enter the following command:

quit

============================================================================================================================================================================================================= ==============

The following mainly based on DB27.x. The following characters are lowercase

This article is not looking for DB2 masters. 1.DB2 products have those levels? Enterprise version of NTERPRISEEDETION Working Group Workgroupedition Enterprise Extended EnterpriseExtendededing Personal version of the Personaledition Satellite version of the SatelliteEdition micro version of Everyplace 2. Can be connected to DB2 What are the products of the database? DB2 client DB2Connect DB2DATAPROPAGATOR DB2NET.DATA DB2DATAJOINER DB2RELATIONALCONNECT WebSphere Application Server and other 3. DB2 supported communication protocols? TCP / IP NetBIOS APPG IPX / SPX NamePipe and other 4.DB2 client products What? DB2 Run Time Client DB2RuntimeClient DB2 Management Client DB2AdministrationClient DB2 Application Development Client DB2ApplicationDevelopmentClient DB2 Thin Client DB2THINCLIENT 5. Can a database can be installed on multiple database servers? Can you 6. Which version of the stored procedure can be used to use SQL statements What is the relational extension of 7.db2 after 7.db2? Text Extender TextExtender Image Extender ImageExtender Audio AudioExtender Video Expander VideoExtender XML Extender XMLEXTender Network Search Extender Net.Searchextender 8.Windows And the DB2 installation directory structure in the OS / 2 environment? Use setup.exe to install the root directory of the / SQLLIB installation, including the ReadMe file / SQLLIB / ADSM contains the Adstar Distributed Storage Manager file / SQLLIB / BIN contains the executable of the DB2 tool. File / SQLLIB / BND contains the bind file of the DB2 tool / SQLLIB / CC contains the files required to run the control center / SQLLIB / CFG contains the default system configuration file / SQLLIB / CONV contains code page conversion table file / SQLLIB / DB2 default Instance directory / S QLLIB / DB2DAS00 default DB2 management server directory / SQLLIB / DOC contains DB2 online manual / SQLLIB / Function default user-defined function directory / SQLLIB / FUNCTION / Unfenced default non-separated user custom correspondence directory / SQLLIB / HELP online help File / SQLLIB / JAVADB2 Java class library Java12 contains JDK1.2 support / SQLLIB / MISC contains HTML search server file / SQLLIB / MSG / Prime contains information file / SQLLIB / QP contains QueryPatrol client file / SQLLIB / Samples contains sample programs and sample scripts / SQLLIB / SPMLOG contains DB2 Synchronous Point Manager log files / SQLLIB / THNSETUP contains thin client installation files 9. UNIX and Linux environment DB2 installation directory structure? Use db2setup.exe to install The following directory is also created under the root directory: Readme installed root directory, including the README file ADM contains the system management tool file ADSM contains the Adstar distributed storage manager file BIN contains the binary executable file of the DB2 tool contains the tie of the DB2 tool. The file CC contains the files required to run the control center CFG contains the default system configuration file CONV contains code page conversion table file DOC contains DB2 online manual Function default user-defined function directory Function / Unfenced default non-separation / unfront  谀 谀? Lt; br>

Install contains the installer instance contains instance scripts JavaDB2 required Java class libDB2 library file MAP contains the mapping files used by DB2Connect MISC containing HTML Search Server files Samples containing sample programs and sample scripts MSG / $ L Contains DB2 information files 10. Which command is installed in AIX to install the DB2? Installp command 11. Multiple DB2 databases can be installed under the same operating system? Can you stop instance? DB2Stop 13. How to start instance? DB2Start [Page] 14. How to modify the registration item Value? DB2SET can modify such as: Setting a parameter of the current instance DB2SETPARAMETER = VALUE Set a global level parameter db2setParameter = value-g (lowercase) View list of all variables set in the configuration file registry (DB2SET-LR) Small write) 15. How to perform an operating system command in the CLP? Adding "!" As a prefix DB2 =>! DIRC: / 16. How to do it in the CLP command? Use "/" as the continuation symbol 17. How to get the syntax related information of DB2 command? DB2? Display all DB2 command DB2? Command Display Command Information DB2? SQLnnnNn Display this SQLCode interpretation information DB2? Db2nnnn Display this DB2 error interpretation information 18. How to view the current CLP setting? DB2 =>

ListcomandOptions 19. How to update the specific item settings for the current CLP session? DB2UPDATECOMMANDOPTIONSUSINGOTIONS ... 20.COMMANDWINDOWS can pass which command call? DB2CMD Command 21. The default name of the management server is DB2DAS Windows under DB2DAS Windows, DB2DAS00 22. Common Management DB2 command server instance? DB2ADMINSTART start the DB2 Administration server instance DB2ADMINSTOP stopped DB2 Administration server instance DASICRTUNIX create drop the DB2 Administration server instance DB2ADMINCREATEWINDOWSOROS under the DB2 Administration server instance DASIDROPUNIX create DB2 Administration server instance DB2ADMINDROPWINDOWSOROS under / 2 instance delete DB2 Administration server under / 2 DB2GETADMINCFG Display DB2 Management Server DB2UPDATEADMINCFG Modifying DB2 Management Server DB2RESETMINCFG Setting DB2 Management Server The configuration parameters of DB2 management servers are divided into defaults 23. What are the specifics of the DC Directory Directory Directory DCS Directory Directory Directory Directory Directory 24. how to view the system database directory? how LISTDBDIRECTORY 25. Check the database server directory? 26.DB2 default port LISTNODEDIRECTORY server instances is? 50000 server name as an authentication type DB2CDB2 27.DB2UDB server have? sERVER SERVER_ENCRYPT CLIENT DCE DCE_SERVER_ENCRYPT KERBEROS KRB_SERVER_ENCRYPT 28.DB2 client authentication type Is there any type of permissions in Server Server_Encrypt DCS DCS_ENCRYPT Client DCE 29.DB2? Sysadm System Administration Permissions SYSCTRL System Control Permissions SYSMAINT System Maintenance DBADM Database Administration Permissions LOAD Table LOAD Operation Permissions 30. What is the permissions authorized by GRANT? Sysam sysctrl sysmaint To change the above permissions must modify the database manager configuration parameter 31. What are the types of tables? Permanent Table (Base Table) Temporary Table (Description Table) 32. How many tables do you know? SELECT * FROMSYSIBM.SYSTABLESWHERECREATOR = 'uSER' 33. how do I know the function at the user? select * fromIWH.USERFUNCTION select * fromsysibm.SYSFUNCTIONS 34. how do I know the number of users under VIEW? select * fromsysibm.sysviewsWHERECREATOR = 'uSER' 35. how do I know the current DB2 version? select * fromsysibm.sysvERSIONS 36. how do I know the number of users under TRIGGER? select * fromsysibm.SYSTRIGGERSWHERESCHEMA = 'uSER' 37. how do I know the situation TABLESPACE? select * fromsysibm.SYSTABLESPACES 38. how do I know the status of SEQUENCE? select * fromsysibm.SYSSEQUENCES 39. how do I know the status of SCHEMA? select * fromsysibm.SYSSCHEMATA 40. how do I know the status of INDEX? select * fromsysibm.SYSINDEXES 41. how do I know the status table fields? select * fromsysibm.SYSCOLUMNSWHERETBNAME = 'AAAA'

42. How do I know the DB2 data type? Select * fromSysibm.sysDataTypes 43. How to know the bufferpools situation? SELECT * version of the FROMSYSIBM.SYSBufferPools 44.db2 table's modification limit? You can only modify the varchar2 type and can only be increased. 45 how to view a table's structure? DESCRIBLETABLETABLE_NAME OR DESCRIBLESELECT * FROMSCHEMA.TABLE_NAME 46. how quickly remove a large table? how ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINITALLYWITHEMPTYTABLE 47. Check the package database? select * fromsysCAT.PACKAGES 48. how to view the database stored procedure? SELECT * FROMSYSCAT. Procedures 49. How to view the constraint of the table? Select * fromsyscat.checkswhereetabname = 'aaaa' 50. How to view the reference complete constraints? Select * fromsyscat.referenceswhereetabname = 'AAAA' Every time you use DB2, you may encounter some seemingly seemingly Simple questions, especially for novices, I am a simple summary, I will release it for everyone, I hope everyone can help, and also welcome everyone to discuss, develop together, and make progress! The following mainly DB27.x is Basic. The following characters are lowercase.

This article is not read for DB2 masters. All characters are lowercase. 51. Installing DB2 default example names? Devils in Windows or OS / 2 is DB2 in Linux or UNIX environment is DB2Inst1 52. Installation The default account is that the default is DB2Admin in the Linux or UNIX environment in Windows or OS / 2. What are the types of DB2AS 53? CLIENT (Customer) Standalone "Satellite (Satellite) EEDB2 EEE 54. How to create an example? DB2ICRTINSTNAME <... Parameters> 55. How to list all available cases on your system? DB2IList 56. How do you know which example of the current dialogue? GetInstance 57. How to update the configuration of the example? DB2IUPDT

58. How to delete the case? DB2IDropinstance_name The specific steps are as follows: Stopping Examples All applications perform DB2TERMINATE Run on all open command lines DB2STOP backup DB2Instprof registration variable pointed by directory Exit Current Example All Landers use DB2IDROP to delete ID 59. 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 licenses that have been purchased? DB2LICM- U

63. How to force only the number of purchased numbers? DB2LICM-Ehard64. How to update the licensing policy type used on the system? DB2LICM-P

RegisteredConcurrent 65. How to update the number of processors on the system? DB2LICM-N

66. How to query the version information of the license file record? DB2LICM-V 67. How to query DB2LICM help information? DB2LICM-H 68. What is at least which tablespaces? One or more user table spaces one or more Temporary table space [PAGE] 69. Different of the control mode between the data and the storage space, where is the space (SMS) database management space (SMS) 70. How to list system databases Directory content? ListDatabaseDirectory 71.createDatabase is a SQL command? Not ABC configuration parameter values? UPDATEDATABASECONFIGURATIONFORABC USING75. how do I restart the database? RESTARTDATABASEDATABASE_NAME 76. how do I activate the database? ACTIVATEDATABASEDATABASE_NAME 77. how can I stop the database? DEACTIVATEDATABASEDATABASE_NAME 78. how to delete a database? DROPDATABASEDATABASE_NAME 79. how to build models? how CREATESCHEMASCHEMA_NAME 80. Setup mode? SETSCHEMA = type SCHEMA_NAME 81. What table? basic summary table results table table table type sub-sub-type table declared temporary table system temporary table 82. how to define the sequence? CREATESEQUENCEORDERSEQSTARTWITH1INCREMENTBY1NOMAXVALUENOCYCLECACHE24 83. how to check the table placed in a suspended state? SETINTEGRITYTABLE_NAMEOFF 84. How to get the table's exclusion lock? LockTableTable_NameINExClusiveMode 85. How to adjust the table to have an online state without checking data? Setintegrityfortab How LE_NAMEALLIMMEDIATEUNCHECKED 86. lift the lock on the table? How COMMIT 87. Close the log table? ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINIALLY 88. How to delete a table? How DROPSCHEMA.TABLE_NAME 89. rename the table? RENAMETABLE_OLDTOTABLE_NEW 90. How to get the current time? SELECTCURRENTTIMESTAMPFROMSYSIBM.SYSDUMMY1

91. How to create a profile of DB2? DB2's profile function is similar to Oracle's physical view! Syntax is: createSummaryTableTable_NameAs (FullSelect) ... for example: Define a profile of refresh: createSummaryTableTable_nameas (SELECT *FOMTABLE_NAME1WHERECOL1 = aaa) DATAINITIALLYDEFERREDREFRESHDEFERRED provisions which DATAINITIALLYDEFERRED data can not be inserted into the table as part of CREATETABLE statement. REFRESHDEFERRED data specified in the table can get refreshed at any time using REFRESHTABLE statement! 92. how to refresh summary tables? REFRESHTABLESUM_TABLE which SUM_TABLE is a 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 first line defines the name of the temporary table defined in the second row of the provisions of the temporary table column to the third row. The constant column is not copied from the source result table. The fourth line stipulates that all rows of the table will be deleted if the With Gold cursor will be removed. The fifth line specifies the change of the table. For example: DeclareGlobalTemPoraryTableDec_bsempms As (SELECT * fromBSempms ?) DEFINITIONONLY EXCLUDINGIDENTITYCOLUMNATTRIBUTES ONCOMMITDELETEROWS NOTLOGGED 95. view of how to create a management view: CREATEVIEWVIEW_NAMEASSELECT * FROMTABLE_NAMEWHERE ... delete view: how DROPVIEWVIEW_NAME 96. know the contents of the view definition in the SELECT * FROMSYSCAT.VIEWS TEXT column 97.?. How to Create an alias behind CREATEALIASALIAS_NAMEFORPRO_NAME PRO_NAME can be TABLE, VIEW, ALIAS, NICKNAME, etc. 98. How to create a sequence such as:?.? CREATESEQUENCESEQUENCE_NAME STARTWITHSTART_NUMBER INCREMENTBYVALUE1 NOMAXVALUE NOCYCLE CACHEMAXIMUMNUMBEROFSEQUENCEVALUES first line defines the name of the sequence of the second row start value specified sequence. The third line regulates a new range. The fourth line regulates no maximum numerical limit. The fifth line specifies the maximum numerical limit .99. How to change the sequence? Altersequencesequence_name ... You can modify the parameter Startwith's Start_Number Increment VALUE1 NOMAXVALUE NOCYCLE Attribute MaximumNumBerofsequenceValues ​​Maximum Value

100. How to delete the sequence? Dropsequencesequence_name 101.db2 supports import (import) file format Is it: Del, ASC, IXF, WSF, etc. 102.DB2 Support Export (export) file format? Has: Del, IXF, WSF The ASC format is not supported. 103.DB2 Support load (LOAD) file format Is it: Del, ASC, IXF, etc. Does not support WSF format. 104.DB2 Support DB2MOVE file format Is there: IXF, etc. ASC, DEL, WSF format. 105.DB2 Database Monitoring two components? SnapshotMonitor can return a snapshot of database activity of a specific time point. Event monitor (EventMonitor) Record the data generated by the event. 106. System The monitoring data element type? Counter records the number of times of activity. The current value of the Gauge measurement entries. Watermark is the maximum or minimum value of the elements from monitoring. Information (Information) monitoring activities Type details. Timestamp activity The date time occurred. Time (time) returns a time taken. 107. How do I know the number of pages needed to monitor the heap? (NumberofMonitoringApplications 1) * (NumberofDatabases * (800 (NUMBEROFTABLES ACCESSED * 20) ((NUMBEROFAPPLICATIONSCONNECTED 1) * (200 (NUMBEROFTABLE SPACES * 100))))) / 4096 by its size parameters MON_HEAD_SZ control 108. how to create an event monitor CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFILE'D:.? / TEMP '109. How to activate the event monitor? STEVENTMONITORTABLEMOMOSTATATE1 110. How to stop the event monitor? SetEventMonitorTableMonState0 111. How to query the status of the monitor? SELECTEVMONNAME, EVENT_MON_STATE (EVMONNAME) fromsyscat.EventMonitors ? 112. How to delete event monitors to create a pipe event monitor on DROPEVENTMONITORTABLEMON 113.UNIX and WINDOWS (PIPE meaning EVNT see MONITOR) of different first step:? Define an event monitor UNIX: CONNECTTOSAMPLE CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE '/ TMP / EVPIPE1' WINDOWS: CONNECTOSMPLE CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITOPIPE '/ / / TMP / EVPIPE1'

Step 2: Establish a named pipeline UNIX: You can use the mkfifo () function or mkfifo command. Windows: You can use the CreateNameDpipe () function, the pipe name is the same name with the CreateEventMonitor specified. Step 3: Open Name Pipeline Unix: Use the open () function Windows: Using the connectNamedPipe () function. You can also use the db2evmon command, such as: DB2EVMON-dbsample-evmstmb2 Step 4: Activate Name Pipe Event Monitor Unless automatically activates the named pipe event monitor, setEventMonitorstMB2State1 Step 5: From Named Pipeline Read Data Unix: You can use the read () function. Windows: You can use the readfile () function. Step 6: Stop event monitor setEventMonitorstMB2State0 Step 7: Close Name Division Unix: You can use a close () function. Windows: You can use DISCONNECTNAMEDPIPE () function. Eight step: Delete Named Pipeline Unix: You can use a unlink () function. Windows: You can use a CloseHandle () function. 114.DB2 SQL statement Category? DCL: Data Control Language, provide database objects Access rights. DDL: Data Definition Language, Create, Modify, Delete Database Objects. DML: Data Manipulation Language, Used to Insert, Update, Delete Data. [Page] 115.DCL Who? Control Permissions: If users Create an object, then change the user fully access the object. The GRANT statement grants permissions to the user. The REVOKE statement revokes the permissions of a user. What are the 117.dml of CREATE DECLARE ALTER DROP? Insert SELECT UPDATE DELETE Waiting for 118.db2 Is there a Boolean type? No 119. How to query the built-in function of DB2? Free Document Administration -> SQLReference -> Functions 120. How to perform DB2 script file? DB2-vtffilename 121.db2 in Oracle Rownum () is? ROW _Number () over () 122.db2 How to get an error code? DB2? SQLCODE 123.DB2 VARCHAR converted to Integer's function? Cast () 124.db2 Integer converted to varchar function? Charr ( 125. DATE Switch to Date () 126.DB2 function "CHAR () 127.DB2 The TRIGGER in CHAR () 127.DB2 can be modified? Cannot, only delete reconstruction 128 .Windows How to know the port number of DB2? / Winnt / System32 / drivers / etc / services 129.db2 How to perform a stored procedure? Can DB2CallProcedure_name 130. How to enter DB2 DOS command mode? DB2CMD 131. How to get DB2 process number? DB2ListApplications 132. How to kill DB2 processes? ForceApplication (ID) 133.A User After installing DB2, how to start Database with B users? Plus. Plus in .Profile ./Home_DB2inst/sqllib/db2profile 134.db2 Is there a snapshot similar to Oracle?

DB2 function 135.avg () returns average of a set of values. Selectavg (Salary) fromBSempms; 136.corr (), correlation () returns a relational coefficient of a pair of values. SELECTCORRELATION (SALARY, BONUS) fromBSempms; 137.count ) Returns a group of rows or values. SelectCount (*) fromBSempms; 138.covar (), Covariance () Returns a collision of covariance. Selectcovar (Salary, Bonus) fromBSempms; 139.max () Returns a group The maximum value in the value. SELECTMAX (Salary) fromBSempms; 140.min () Returns the minimum value in a set of values. Selectmin (Salary) fromBSempms; 141.stddev () Returns the standard deviation of a set of values. Selectstddev (Salary) fromBSempms 142. Sum () Returns a set of data and. SelectSum (Salary) fromBSempms; 143.Var (), variance () returns a set of variances. Selectvariance (Salary) fromBSempms; 144.abs (), Absval () Returns the absolute value of the parameter. Selectabs (-3.4) fromBSempms; 145.acos () Returns the anti-hosple value of the 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 an anti-sicrosive function of the parameter indicated by the angle representation. Selectasin (0.9) fromBSempms; 148.atan () Returns the arranging argument, the parameter of the arc indicates the angle of the arc. Selectatan (0.9) fromBSempms;

149.ataN2 () Returns an unstextable value of the X and Y coordinates of the angle indicated by the arc. SELECTATAN2 (0.5, 0.9) fromBSempms; 150.bigint () Returns the 64-bit integer representation of the digital or string in the integer constant. SelectBigint (EMP_NO) fromBSempms; 151.ceiling () orceil () Returns the smallest integer value larger than the parameter or equal to the parameters. Selectceiling (3.56) fromBSempms; selectceil (4.67) fromBSempms; 152.char () Return date Time, Character String, integer, decimal or double precision floating point number string representation. SelectChar (Salary, ',') fromBSempms; 153.chr () Returns a character with the ASCII code specified by the parameter. SelectChar (167) fromBSempms; 154.concat () Returns the connection of two strings. SelectConcat (EMP_NO, EMP_NAM) fromBSempms; 155.year () returns the annual part of the value. Selectyear ('2003/01/02') fromBSempms; 156.varchar () Returns the string, Date type, graphic string variable length string representation. Selectvarchar (EMP_NAM, 50) fromBSempms; 157.ucase () ORUPPER () Returns the uppercase of the string. SelectUcase (EMP_NAM) fromBSempms; selectupper (EMP_NO) fromBSempms; 158. Truncate () ORTRUNC () starts truncation from the position on the right side of the expression decimal point. SELECTTRUNCATE (345.6789, 2) fromBSempms; 159.Time () returns a value in a value. SELECTTIME ('2001-03-19.12.30.123456 ') FromBSempms; 160.substr (exp1, exp2) Returns the substrings starting at EXP1 String EXP2. SelectSubstr (' cdnjfdjfjd ', 5) fromBSempms; selectSubstr (' cdnjfdjfjd ', 5, 2) fromBSempms; 161.sQRT () Returns the square root of this parameter. SelectSQRT (36) fromBSempms; 162.Space () Returns the length specified by the parameter, contains a string within the space. SELECTSPACE (10) fromBSempms; 163 .Second () returns a value of the second. Selectsecond ('18: 34: 32 ') fromBSempms; 164.rtrim () Delete spaces of the string tail. SelectrtRtem (' Comment ') fromBSempms; 165.Round (EXP1, EXP2) Returns the round-entered value starting at the EXP2 position on the right side of the EXP1 decimal point. SELECTROUND (2345.6789, 2) fromBSempms; 166.Replace (exp1, exp2, exp3) replacing all of EXP2 SelectChar in EXP1 (Romandd ',' NDD ',' CCB '), 10) fromBSempms; 167.Repeat (EXP1, EXP2) Returns EXP1 Repeat EXP2 String. Selectchar (Repeat', 3), 21) fromBSempms; 168.Real () Returns a value of a single quadrant floating point representation. SELECTREAL (10) fromBSempms;

169.Rand () Returns the random floating point 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 location of Exp2 in EXP1. SELECT ('Abcdefgh', 'D') fromBSempms; 172.Nullif (EXP1, EXP2) If exp1 = exp2, null, otherwise returning to the partition returning to lines for Exp1173.NodeNumber () No. SelectNodeNumber (EMP_NO) fromBSempms; 174.month () Returns a value of a value. SELECTMOTH ('2003/10/20') fromBSempms; 175.MOD (EXP1, EXP2) Returns EXP1 divided by exp2. SelectMod ( 20, 8) fromBSempms; 176.minute () Returns a value of a value of a value. Selectminute ('18: 34: 23 ') fromBSempms; 177.Ltrim () Deletes the space in front of the string. SelectLtrim (' cddd ') fromBSempms; 178.Hour () returns a value of a value. SELECTHOUR ('18: 34: 23 ') fromBSempms; 179.double () If the parameter is a digital expression, return the floating point number corresponding to it, if the parameter is a string Expression, returns the number of string expressions. Selectdouble ('5678') fromBSempms; 180.exp () Returns the index function of the parameter. SELECTEXP (2) fromBSempms; 181.float () Returns a number of floating points . SelectFloat (789) fromBSempms; 182.floor () Returns the maximum integer of less than or equal to the parameter. SelectFloor (88.93) fromBSempms; 183.Hex () Returns a 16-based representation representing the value of the string. SELECTHEX (16) FromSempms; ============== =======================================

1. common commands to establish a database DB2_GCB CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32 2. database connections connect to sample1 user db2admin using 8301206 3. alias create alias db2admin.tables for sysstat.tables; CREATE ALIAS DB2ADMIN.VIEWS fOR SYSCAT.VIEWS create alias db2admin.columns for syscat.columns; create alias guest.columns for syscat.columns; 4. creating tables create table zjt_tables as (select * from tables) definition only; create table zjt_views as (select * from views) definition only; 5. insert record insert into zjt_tables select * from tables; insert into zjt_views select * from views; 6. establishing view create view V_zjt_tables as select tabschema, tabname from zjt_tables; 7. establish trigger CREATE TRIGGER zjt_tables_del AFTER DELETE ON zjt_tables REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL Insert into zjt_tables1 values ​​(substr (o.tabschema, 1,8), substr (o.tabname, 1,10)) 8. establish unique index CREATE uNIQUE Index I_ZTables_TABNAME ON zjt_tables (tabname); 9. Display select tabname from tables where tabname = 'ZJT_TABLES'; 10. See column select SUBSTR (COLNAME, 1,20) as the column name, TYPENAME as type, LENGTH as the length from columns where tabname = ' ZJT_TABLES '; 11. Display configuration db2 describe table user1.department db2 describe select * from user.tables 12. See table index db2 describe indexes for table user1.department 13. See view select viewname from views where viewname =' V_ZJT_TABLES ' 14. View Index Select Indname from Indexes WHERE Indname = 'i_ZTables_TABNAME'; 15. Check the Storage Process Select Substr (Procschema, 1, 15), Substr (Procname, 1, 15) from syscat.procedures; 16. Type conversion ( Cast) IP DataType: VARCHAR SELECT CAST (IP as INTEGER)

50 from log_comm_failed 17. Reconnect Connect Reset 18. Interrupt Database Connection DISCONNECT DB2_GCB 19. View Application List Application; 20. Kill Application Force Application (0); DB2 Force Applications All (Forced all applications from database disconnection) 21. LOCK Table Lock Table Test in Exclusive Mode 22. Share Lock Table Test In Share Mode 23. Displays all table list tables 24. All system table list tables for system 25. Show current activity database List active Databases 26. View command option list command options 27. database directory LIST dATABASE dIRECTORY 28. tablespace list tablespaces 29. tablespace containers LIST tABLESPACE cONTAINERS FOR example: LIST tABLESPACE cONTAINERS FOR 1 30. display access to the user database instance GET AUTHORIZATIONS 31. start DB2START 32. Stop instance DB2Stop 33. Table or view privilege Grant SELECT, DELETE, INSERT, UPDATE ON TABLES TO USER GRANT All ON tables to user with grant Option 34. Program Package Grant Execute on package package-name to public 35. Mode privilege Grant Createin On Schema Schema-Name To User 36. Database Privilege Grant Connect, CreateTab, DBADM on Database to Use R 37. Index Privilege Grant Control on Index Index-Name To User 38. Information Help (? xxxnnnnn) Example:? SQL30081 39. SQL Help (Syntax for SQL Statement) Help Statement, for example, Help SELECT 40. SQLState Help (Description SQL Status and category code)? SQLSTATE or? Class-Code 41. Change the password DB2Admin setId username password related to "Manage Server" ). Create a Sample Database DB2SAMPL DB2SAMPL F: (Designation Disk) 43. Using the operating system command! DIR 44. Conversion Data Type (CAST) SELECT EMPNO, CAST (Resume As Varchar (370)) from Emp_Resume WHERE RESUME_FORMAT = 'ASCII' 45. UDF To run DB2 Java stored procedures or UDF, you also need to update the DB2 database management program on the server Configure,

Path DB2 Update DBM CFG USING JDK11_PATH D: SQLLIBJAVAJDK TERMINATE UPDATE DBM CFG USING SPM_NAME Sample 46. Check DB2 Database Administrative Program Configuration DB2 Get DBM CFG 47. Retrieve all authorized names with privilege Select Distinct Grantee , GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE' FROM SYSCAT.TABAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE' FROM SYSCAT.PACKAGEAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX' FROM SYSCAT .INDEXAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN' FROM SYSCAT.COLAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA' FROM SYSCAT.SCHEMAAUTH UNION SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER' FROM SYSCAT.PASSTHRUAUTH ORDER BY GRANTEE, GRANTEETYPE, 3 Create Table Yhdab (ID VARCHAR (10), Password Varchar (10), YWLX VARCHAR (10), KH VARCHAR (10)); Create Table Ywlb (Ywlbbh Varchar (8), YWMC VARCHAR (60)) 48. Modify Table Structure ALTER TABLE YHDAB ALTER KH SET DATA TYPE VARCHAR (13) ; Alter table yhdab ALTER ID SET DATA TYPE varchar (13); alter table lst_bsi alter bsi_money set data type int; insert into yhdab values ​​( '20000300001', '123456', 'user01', '20000300001'), ( '20000300002' , '123456', '2000030000002'); 49. Business Type Description Insert Into YWLBB VALUES ('USER01', 'Business Application ", (' USER02 ',' Business Undo '), (' USER03 ', 'Cost inquiry'), ('USER04', 'fee is self-recalled "), (' USER05 ',' fee pre-stored '), (' USER06 ',' password modification '), (' USER07 ',' invoice print ') ('GL01', 'Change User Basic Information'), ('GL02', 'Change Payment Information'), ('

GL03 ',' Daily Statistics '), (' GL04 ',' Billing Function '), (' GL05 ',' Sub-envelope ", ('GL06', 'Billing Function'), ('GL07' , 'Integrated Statistics') II. Directory View Description Directory View Check Confusion Syscat.Checks Columns Syscat.Columns Check the column Syscat.Colchecks in columns in the column syscat.keycoluse data type using the Syscat.KeyColuse data type of Syscat.DataTypes function parameters or function results Syscat.funcparms Reference Constraints Syscat.References Mode Syscat.Schemata Table Constrained Syscat.Tabconst Table Syscat.Tables Trigger Syscat.Triggers User Define Functions Syscat.Functions View Syscat.Views 3. String Type Binary Big Object (BLOB) String. Character Large Object (CLOB) string, its character sequence can be a single-byte character or multibyte character, or a combination of both. Double-byte character large object (dbclob) string, its character sequence is a double-character character. IV. Database Frame Sequence Form: Each row in the table has a value, never a set of values. The second specification: The fact that each column in the keyword is not provided depends on the fact that the entire keyword is provided. The third specification: Each non-key column provides facts with other non-keyword columns and depends only on the keyword. The fourth specification: There is no row containing two or more independent factors related to an entity. V. Data Type Data Type Type Characteristics Example or Range CHAR (15) Delivery Strings Maximum length is 254 'Sunny Day' VARCHAR (15) The maximum length of 4000 'sunny day' smallint number length is 2 bytes accuracy The 5-bit range is -32768 to 32767 Integer number length of 4 bytes of accuracy is 10-bit range is -2147483648 to 2147483647 Real digital single precision floating point 32-bit approximation range is -3.402E 38 to -1.175E-37 or 1.175 E-37 to -3.402E 38 or zero Double Digital Double Precision floating point 64-bit approximation range is -1.79769E 308 to -2.225E-307 or 2.225E-307 to 1.79769E 308 or zero Decimal (5, 2 ) Digital accuracy is 5 decimal bits of 2 ranges to -10 ** 31 1 to 10 ** 31-1 Date Date Time 3 Part Value 1991-10-27 Time Date Time 3 Part Value 13.30.05 TimeStamp Date Time Seven Value 1991-10-27-13.30.05.000000 6. The column function column function calculates a set of values ​​in the column to obtain a single result value. The following is an example of some column functions. AVG Returns the value of a group to returns a group of rows or values ​​of the number of rows or values ​​in this group, returns a set of maximum MIN returns a set of values ​​in a set of values. Seven. Scalar function scalar function The value is calculated to return another value. The following is some examples of scalar functions provided by the DB2 universal database. The hexadecimal of the absolute value of the ABS returns HEX returns the value of the value of LENGTH returns the number of bytes in the argument (returns the number of double-character characters for the graphic string.) Year Extraction date time value

======================================================================================================================================================================================================================================================================================================】 there are those? NTERPRISEEDITION enterprise Edition workgroup Edition enterprise WORKGROUPEDITION expanded version of PERSONALEDITION satellite ENTERPRISEEXTENDEDEDITION personal Edition version SATELLITEEDITION miniature version of EVERYPLACE 2. can connect to the DB2 database product What? DB2 client DB2CONNECT DB2DATAPROPAGATOR DB2NET.DATA DB2DATAJOINER DB2RELATIONALCONNECT WEBSPHERE What are the communication protocols supported by application servers? TCP / IP NetBIOS APPG IPX / SPX NamePipe and other 4.DB2 client products What are the DB2 Run Time Client DB2RuntimeClient DB2 Management Client DB2AdministrationClient DB2 Application Development Client DB2ApplicationDevelopmentClient DB2 Thin Client DB2THINCLIENT 5. Can a database can be installed on multiple database servers? Can you 6. Which version of the stored procedure can be created with a SQL statement? 7.1 After 7.db2 provides which relational expansion? Text expander TextExtender Image Extender ImageExtender Audio AudioExtender Video Expander VideoExtender Space Data Extension SpatialeExtender XML Extender XMLEXTender Network Search Extender NET.SEARCHEXTENDER 8.Windows and OS / 2 Environment DB2 installation directory structure? Use setup.exe Install / SQLLIB installation root directory, including ReadMe file / SQLLIB / ADSM contains Adstar Distributed Storage Manager file / SQLLIB / BIN contains executable files containing DB2 tools / SQLLIB / BND contains bind files for DB2 tools / SQLLIB / CC Run control The file required by the center / SQLLIB / CFG contains the default system configuration file / SQLLIB / CONV contains code page conversion table file / SQLLIB / DB2 default instance directory / SQLLIB / DB2DAS00 default DB2 management server directory / SQLLIB / DOC DB2 Online Manual / SQLLIB / Function Default User Custom Function Directory / SQLLIB / Function / Unfenced Default Non-separated User Custom Correspondence Directory / SQLLIB / HELP Online Help File / SQLLIB / JavaDB2 The Java Class Library Java12 includes JDK1. 2 Support program / SQLLIB / MISC contains HTML search server file / SQLLIB / MSG / prime contains information file / SQLLIB / QP contains QueryPatroller client file / SQLLIB / Samples contains sample programs and sample scripts / SQLLIB / SPMLOG contains DB2 Synchronous Point Manager log file / SQLLIB / THNSETUP contains thin client installation files 9. UNIX and Linux environment DB2 installation directory structure?

Use DB2Setup.exe to install the installed root directory, you will also create the following directory: The root directory of the Readme installation, including the ReadMe file ADM contains the system management tool file ADSM contains the Adstar distributed storage manager file BIN contains a binary executable of DB2 tools BND contains the bind file of the DB2 tool CC contains the file CFG required for the Running Control Center. CFG contains the default system profile. CONV includes code page conversion table file DOC contains DB2 online manual Function default user-defined function directory Function / Unfenced default Isolation User Custom Correspondence Directory Install Containing Instance Instance Contains Instance Script JavaDB2 Required Java Class Libdb2 Library File Map Contains MSPLES MISC contains HTML Search Server files Samples containing sample programs and sample scripts MSG / $ L Which command contains DB2 information file 10.Aix to install DB2? Installp command 11. Multiple DB2 databases can be installed under the same operating system? Can you stop instance? DB2Stop 13. How to start instance? DB2Start 14. How to modify The value of the registration item? DB2SET can be modified as: Set a parameter of the current instance db2setParameter = value Set a global level parameter db2setParameter = value-g (lowercase) View all variables that can be set in the configuration file registration table DB2Set -LR (lowercase) 15. How to execute an operating system in the CLP? "!"! "As a prefix DB2 =>! DIRC: / 16. What should I do if the command is too long in the CLP? Use" / "to continue Symbol 17. How to get the syntax related information of the command of DB2? DB2? Display all DB2 command DB2? Command Display Command Information DB2? SQLNNNN displays this SQLCode interpretation information DB2? Db2nnn displays this DB2 error Interpretation information 18. How to view the current CLP Settings? DB2 => ListComandOptions 19. How do I update the specific item settings for the current CLP session? DB2UPDATE COMMANDOPTIONSUSINGOPTION ... 20.COMMANDWINDOWS Which command call? DB2CMD command 21. Manage the server's default name? Unix is ​​DB2DAS Windows under DB2AS Windows for DB2DAS00 22. Commonly managed DB2 server instance command? DB2AdminStart launches DB2 management server instance DB2Adminstop stop create a DB2 Administration server instance to delete under DASIDROPUNIX create drop the DB2 Administration server instance DB2GETADMINCFG next instance DB2ADMINDROPWINDOWSOROS / 2 DB2 Administration server display configuration parameters DB2UPDATEADMINCFG DB2 Administration server to modify the DB2 Administration server under instance DB2ADMINCREATEWINDOWSOROS / 2 DB2 Administration server under instance DASICRTUNIX DB2 Administration server Configuration Parameters DB2RESETADMINCFG Set the configuration parameters of the DB2 management server to the default value 23. What are the specifics of the DC Directory? System Database Directory Local Database Directory Node Directory DCS Directory Management Node Directory 24. How to view the system database directory? Listdbdirectory 25. How to view Database server directory? ListNodedIRectory

The default port of the server instance is 26.DB2? 50000 server name as an authentication type DB2CDB2 27.DB2UDB server have? Authentication type SERVER SERVER_ENCRYPT CLIENT DCE DCE_SERVER_ENCRYPT KERBEROS KRB_SERVER_ENCRYPT 28.DB2 client have? SERVER SERVER_ENCRYPT DCS DCS_ENCRYPT CLIENT DCE 29.DB2 What kinds of permissions in the SYSADM System Administration Sysctrl System Control Permissions SYSMAINT System Maintenance DBADM Database Administration Permissions LOAD Table Permissions 30. Can I not pass Grant authorization? Sysam sysctrl sysmaint The authority must modify the database manager configuration parameter 31. What are the types of the table? Permanent Table (SMS) Temporary Table (Derived Table) 32. How do you know how many tables have a user? Select * fromSysibm. SYSTABLESWHERECREATOR = 'uSER' 33. how do I know the function at the user? select * fromIWH.USERFUNCTION select * fromsysibm.SYSFUNCTIONS 34. how do I know the number of users under VIEW? select * fromsysibm.sysviewsWHERECREATOR = 'uSER' 35. how do I know the current DB2 version? select * fromsysibm.sysvERSIONS 36. how do I know the number of users under TRIGGER? select * fromsysibm.SYSTRIGGERSWHERESCHEMA = 'uSER' 37. how do I know the situation TABLESPACE? select * fromsysibm.SYSTABLESPACES 38. how do you know the status of SEQUENCE? select * fromsysibm .Ssequences 39. How to know the situation of Schema? Select * fromsystem.syss Chemata 40. How to know the situation of Index? Select * fromSysibm.sysIndexes 41. How to know the status of the table? Select * frOxysibm.sysColumnswheretbname = 'AAAA' 42. How do I know DB2 data type? Select * fromsibm.sysDataTypes 43. How to know BUFFERPOOLS situation? modify restrictions field select * fromsysibm.SYSBUFFERPOOLS 44.DB2 table? You can only modify the type of VARCHAR2 and can only increase not decrease. 45. how to view the table structure? DESCRIBLETABLETABLE_NAME OR DESCRIBLESELECT * FROMSCHEMA.TABLE_NAME 46. how quickly remove a large table? Lai Yan elete two commit actions of ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINITALLYWITHEMPTYTABLE ALTER TABLETABLE_NAME ACTIVE nOT lOGGED iNITALLY WITH EMPTYTABLE is a non-logged commit alter table ab activate not logged intially delete ab where id> 1000 commit (ab created table does not record Log:

Create Table Ab (ID INT) Not Logged Initially 47. How to view the package of the database? Select * fromsyscat.packages 48. How to view the stored procedure of the database? Select * fromsyscat.procedures 49. How to view the constraint of the table? Select * fromsyscat. CheckswhereTabname = 'aaaa' 50. How to view the reference complete constraint for the table? Select * fromsyscat.referenceswhereTabName = 'aaaa' 51. Install the DB2 default example name? In Windows or OS / 2, default is DB2 in Linux or UNIX environment The default account after the installation is DB2Inst1 52. The default account after the installation is DB2Admin is DB2Admin in the Linux or UNIX environment is DB2AS 53. What are the types of examples? Client (Customer) Standalone ) Satellite EEDB2 EEE 54. How to create an example? DB2icrtinstname <... Parameters> 55. How to list all available cases on your system? DB2IList 56. How do you know which example of current conversation? GetInstance 57. How to Configuration of the update case? DB2IUPDT 58. How to delete an example? DB2IDropinstance_name The specific steps are as follows: Stopping Examples All applications perform DB2TERMINATE Run on all open command lines DB2STOP backup DB2Instprof registration variables pointed out the example directory exit example All landings use DB2IDROP You can delete ID 59. How to list all products of 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 your purchase License number? DB2LICM-U 63. How to force only Use the number of purchased? DB2LICM-Ehard

64. How to update the license policy type used on the system? DB2LICM-PregisteredConcurrent 65. How to update the number of processors on the system? DB2LICM-N 66. How to query the version information recorded by the license file? DB2LICM-V 67. How to query DB2LICM Help information? DB2LICM-H 68. A database includes at least which tablespaces? One or more temporary tablespaces for one or more user tablespaces 69. Differently, according to the control mode of the data and the storage space, Which two tablespaces? System management space (SMS) database management space (DMS) 70. How to list the contents of the system database directory? ListDatabaseDirectory 71.createdDatabase is a SQL command? No, it is a system command 72. How to view the contents of the configuration file database ABC's? GETDATABASECONFIGURATIONFORABC 73. how ABC database parameter settings to default values? RESETDATABASECONFIGURATIONFORABC 74. ABC how to modify the database configuration parameter values? UPDATEDATABASECONFIGURATIONFORABC USING 75. how do I restart the database? RESTARTDATABASEDATABASE_NAME 76. how do I activate the database? ACTIVATEDATABASEDATABASE_NAME 77. How to stop the database? DeActivatedatabaseDatabase_name 78. How to delete a database? DropDatabaseDatabase_name 79. How to create a mode? CreateSchemaschema_name 80. How to set the mode? Setschema = schema_name 81. What are the type of table? Basic table result table profile table Table Table Declare Temporary Temporary Temporary Temporary Temporary Table 82. How to Define Sequences? CreatequernceRSEQStartwith1IncrementBy1NomaxValuenoCyleCle CACHE24 83. How to check the table placed in a suspended state? SETINTEGRITYTABLE_NAMEOFF 84. How to obtain exclusive lock on a table? LOCKTABLETABLE_NAMEINEXCLUSIVEMODE 85. How to adjust the table without checking online data? SETINTEGRITYFORTABLE_NAMEALLIMMEDIATEUNCHECKED 86. How to unlock the table? COMMIT 87. how to close the log table? ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINIALLY 88. how to delete a table? how DROPSCHEMA.TABLE_NAME 89. rename the table? RENAMETABLE_OLDTOTABLE_NEW 90. how to get the current time? SELECTCURRENTTIMESTAMPFROMSYSIBM.SYSDUMMY191. how to create DB2 summary tables? DB2 summary table function is similar to ORACLE Substation view! Syntax is: createSummarytableTable_nameas (fullselect) ... for example: Define a profile of refresh: createSumMarytableTable_nameas (SELECT * FROMTABLE_NAME1WHERECOL1 = 'aaa'

) DATAINITIALLYDEFERREDREFRESHDEFERRED provisions which DATAINITIALLYDEFERRED data can not be inserted into the table as part of CREATETABLE statement. REFRESHDEFERRED data specified in the table can get refreshed at any time using REFRESHTABLE statement! 92. How to refresh summary tables? REFRESHTABLESUM_TABLE which SUM_TABLE is a 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 first line defines the name of the second row of the provisions of the temporary table column defined temporary table third. The rules are not the constant columns from the source result table definition. The fourth line stipulates that all rows of the table will be deleted if the With Gold cursor will be removed. The fifth line specifies the change of the table's changes. For example: declareglobalTemPoraryTableDec_bsempms As (SELECT * ? management FROMBSEMPMS) DEFINITIONONLY EXCLUDINGIDENTITYCOLUMNATTRIBUTES ONCOMMITDELETEROWS NOTLOGGED 95. view of how to create a view: CREATEVIEWVIEW_NAMEASSELECT * FROMTABLE_NAMEWHERE ... delete view: how DROPVIEWVIEW_NAME 96. how to know the contents of the view definition of the SELECT * FROMSYSCAT.VIEWS TEXT column 97. create?. Alias? Createaliasalias_nameforpro_name The pro_Name can be Table, View, Alias, Nickname et al. 98. How to create a sequence? For example: createquencedsequence_name Start WITHSTART_NUMBER INCREMENTBYVALUE1 NOMAXVALUE NOCYCLEMAXIMUMNUMBEROFSEQUENCEVALUES Name of the first line specified sequence. The second line specifies the start value of the sequence. The third line regulates the new amplitude. The fourth line stipulates that the maximum numerical limit. 99 .?? how to change the sequence ALTERSEQUENCESEQUENCE_NAME ... how property values ​​NOCYCLE MAXIMUMNUMBEROFSEQUENCEVALUES VALUE1 NOMAXVALUE can be modified START_NUMBER INCREMENT parameters STARTWITH maximum value of 100. the deleted sequences DROPSEQUENCESEQUENCE_NAME 101.DB2 supports importing (iMPORT) file formats are there:? DEL , ASC, IXF, WSF, etc. Is the file format for export (export)? There are: Del, IXF, WSF, etc. Does not support ASC format. 103.DB2 Support load (LOAD) file format Is there:::::: Del, ASC, IXF, etc. Does not support WSF format. 104.DB2 Support for DB2MOVE file format Is: IXF, etc. Does not support ASC, DEL, WSF format. 105.DB2 database monitoring? Snapshot monitoring ( SnapshotMonitor) The snapshot of the database activity of a specific time point can be returned. Event Monitor logging data. 106. Data element type of system monitoring?

Counter recording the number of times of activity. The current value of the Gauge measurement entries. Watermark is the maximum or minimum value of the elements from the monitoring. Information (INFORMATION) monitoring activities. Time points ( TimeSTAMP) The date time occurred. Time (time) returns a time to take a time. 107. How do I know the number of pages required for monitoring stacks? (NumberOfMonitoringApplications 1) * (NumberOfDatabaseses * (800 (NumberOftables Accessed * 20) (NumberofApplicationsConnected 1) * (200 (NumberOftable Spaces * 100)))))))) / 4096 This size is controlled by parameter MON_HEAD_SZ control. 108. How to establish an event monitor? CreateEventMonitorTableMonfortablesWrittOfile'd: / temp '109. How to activate events monitor? how SETEVENTMONITORTABLEMONSTATE1 110. stop the event monitor? SETEVENTMONITORTABLEMONSTATE0 111. how to check monitor status? SELECTEVMONNAME, EVENT_MON_STATE (EVMONNAME) FROMSYSCAT.EVENTMONITORS 112. how to delete event monitors? create a pipe event monitor on DROPEVENTMONITORTABLEMON 113.UNIX and WINDOWS ? different devices (PIPE meaning EVNT see mONITOR) the first step: defining event monitors UNIX: CONNECTTOSAMPLE CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE '/ TMP / EVPIPE1' WINDOWS: CONNECTTOSAMPLE CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE '//./ TMP / EVPIPE1'

Step 2: Establish a named pipeline UNIX: You can use the mkfifo () function or mkfifo command. Windows: You can use the CreateNameDpipe () function, the pipe name is the same name with the CreateEventMonitor specified. Step 3: Open Name Pipeline Unix: Use the open () function Windows: Using the connectNamedPipe () function. You can also use the db2evmon command, such as: DB2EVMON-dbsample-evmstmb2 Step 4: Activate Name Pipe Event Monitor Unless automatically activates the named pipe event monitor, setEventMonitorstMB2State1 Step 5: From Named Pipeline Read Data Unix: You can use the read () function. Windows: You can use the readfile () function. Step 6: Stop event monitor setEventMonitorstMB2State0 Step 7: Close Name Division Unix: You can use a close () function. Windows: you can use DISCONNECTNAMEDPIPE () function. Eight step: Delete Named Pipeline Unix: You can use the unlink () function. Windows: You can use a closehandle () function. 114.DB2's SQL statement category DCL: Data Control Language, provide access to database objects Permissions. DDL: Data Definition Language, Create, Modify, Delete Database Objects. DML: Data Manipulation Language, Used to insert, Update, Delete Data. 115.DCL Permissions What are the permissions? Control Permissions: If the user creates an object, Then change the user fully access the object. The GRANT statement grants permissions to the user. The REVOKE statement revokes the permissions of a user. What are the 117.dml of Create DTDL? Insert SELECT UPDATE DELETE, etc. 118.db2 Is there a Boolean type? No 119. How do I query DB2 built-in functions? Belt Document Administration -> SQLReference ->

In Functions 120. How to perform DB2 script files? DB2-VTFFileName 121.db2 象 象 象 () is? Row_Number () over () 122.db2 How to get an error code? DB2? Sqlcode 123.db2 VARCHAR Converts to Integer's Function to Cast () 124.db2 Integer Converts to VARCHAR Function as? CHAR () 125.db2 VARCHAR Switch to Date "Date () 126.db2 Date () 126.db2 DATE Conversion For VARCHAR's function? CHAR () 127.DB2 does the Trigger can modify? Cannot, can only delete the reconstruction 128.Windows How to know the port number of DB2 under the rebuild? / WinNT / System32 / drivers / etc / services 129.db2 Execute a stored procedure? Can DB2CallProcedure_Name 130. How to enter DOS command mode? DB2CMD 131. How to get DB2 process number? How to kill DB2 processes? ForceApplication (ID) 133.A User Install DB2, how to use B User launches Database? Plus. Plus. Plus ./Home_Db2inst/sqllib/db2profile 134.db2 Snaphu "SummaryTableDB2 function 135.avg () returns a set of values. Selectavg ( Salary) fromBSempms; 136.corr (), correlation () returns a relational coefficient of a pair of values. SelectCorRelation (Salary, Bonus) fromBSempms; 137.count () Returns a number of rows or values. SelectCount (*) fromBSempms; 138.covar (), COVARIANCE () Returns the covariance of a pair of values. SelectcoVar ( SALARY, BONUS) fromBSempms; 139.max () Returns the maximum value in a set of values. SELECTMAX (Salary) fromBSempms; 140.min () Returns the minimum of a set of values. Selectmin (Salary) fromBSempms; 141.stddev ( ) Returns a set of standard deviations. Selectstddev (Salary) fromBSempms; 142.Sum () Returns a set of data and. SelectSum (Salary) fromBSempms; 143.VAR (), variance () Returns the variance of a set of values. Selectvariance (Salary), 144.Abs (), absval () returns the absolute value of the parameter. Selectabs (-3.4) fromBSempms; 145.acos () Returns the anti-cosrot value of the 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 an anti-sicrosive function of the arc indicated by the angle representation. SELECTASIN (0.9) fromBSempms; 148.atan () Returns parameters The anterior correction value, the parameter indicated by the arc, Selectatan (0.9) fromBSempms;

149.ataN2 () Returns an unstextable value of the X and Y coordinates of the angle indicated by the arc. SELECTATAN2 (0.5, 0.9) fromBSempms; 150.bigint () Returns the 64-bit integer representation of the digital or string in the integer constant. SelectBigint (EMP_NO) fromBSempms; 151.ceiling () orceil () Returns the smallest integer value larger than the parameter or equal to the parameters. Selectceiling (3.56) fromBSempms; selectceil (4.67) fromBSempms; 152.char () Return date Time, Character String, integer, decimal or double precision floating point number string representation. SelectChar (Salary, ',') fromBSempms; 153.chr () Returns a character with the ASCII code specified by the parameter. SelectChar (167) fromBSempms; 154.concat () Returns the connection of two strings. SelectConcat (EMP_NO, EMP_NAM) fromBSempms; 155.year () returns the annual part of the value. Selectyear ('2003/01/02') fromBSempms; 156.varchar () Returns the string, Date type, graphic string variable length string representation. Selectvarchar (EMP_NAM, 50) fromBSempms; 157.ucase () ORUPPER () Returns the uppercase of the string. SelectUcase (EMP_NAM) fromBSempms; selectupper (EMP_NO) fromBSempms; 158. Truncate () ORTRUNC () starts truncation from the position on the right side of the expression decimal point. SELECTTRUNCATE (345.6789, 2) fromBSempms; 159.Time () returns a value in a value. SELECTTIME ('2001-03-19.12.30.123456 ') FromBSempms; 160.Substr (exp1, exp2) Returns the substrings starting at EXP1 String from EXP2. SELECTSUBSTR (' cdnjfdjfj D ', 5) fromBSempms; selectSubstr (' cdnjfdjfjd ', 5, 2) fromBSempms; 161.sqrt () Returns the square root of this parameter. SelectSQRT (36) fromBSempms; 162.Space () Returns the length specified by the parameter, including spaces String. SelectSpace (10) fromBSempms; 163.Second () Returns a number of seconds. SelectseCond ('18: 34: 32 ') fromBSempms; 164.rtrim () Deletes spaces of the string tail. SelectrtRim. SELECTRIM 'Comment') fromBSempms; 165.Round (EXP1, EXP2) Returns the round-entered value starting at the EXP2 location on the right side of the EXP1 decimal point. SELECTROUND (2345.6789, 2) fromBSempms; 166.Replace (Exp1, Exp2, Exp3) Replace EXP1 with EXP3 All exp2 selectchar (REPLACE ('Romandd', 'NDD', 'CCB'), 10) fromBSempms;

转载请注明原文地址:https://www.9cbs.com/read-129068.html

New Post(0)