db2常用sql（DB2 commonly used SQL）

1、db2常用sqlDB2 commonly used SQLDB2 commonly used SQL statements and string functions1, combination statement executionBEGIN ATOMICExpression 1 semicolon space / carriage returnExpression 2 semicolon space / carriage returnEND2, tables that should restrict access rights (should revoke these tables, PUB

2、LIC, SELECT access)Useful directory tablesSYSCAT.COLUMNS: contains each row corresponding to the columns defined in the table or viewSYSCAT.INDEXCOLUSE: contains all columns contained in each rowSYSCAT.INDEXES: contains each row corresponding to each index defined in the table or viewSYSCAT.TABLES:

3、each table, view, alias is created to correspond to one rowSYSCAT.VIEWS: each view created is corresponding to one row or rowsMaintain data uniqueness through indexes: CREATE, UNIQUE, INDEX, INDEXNAME, ON, TABLE (COLUMN)Eliminate duplicate rows: SELECT, DISTINCT, COLUMN, FROM, TABLE3, DB2 some funct

4、ions about timeThe year, month, day, hour, etc. of which the current time is obtained:YEAR (current, timestamp)MONTH (current, timestamp)DAY (current, timestamp)HOUR (current, timestamp)MINUTE (current, timestamp)SECOND (current, timestamp)MICROSECOND (current, timestamp)I got the date and time at t

5、he timeDATE (current, timestamp)TIME (current, timestamp)Some calculations about time:Current date + 1 YEARCurrent, date + 3, YEARS + 2, MONTHS + 15 DAYSCurrent time + 5 HOURS - 3 MINUTES + 10 SECONDSCalculate how many days between the two dates:Days (current, date) - days (date ( 1999-10-22 ')G

6、ets the current time to remove milliseconds:CURRENT TIMESTAMP - MICROSECOND (current, timestamp) MICROSECONDSConverts time to string:Char (current, date)Char (current, time)Char (current, date + 12, hours)Converts a string to time:TIMESTAMP ( 2002-10-20-12.00.00.000000 ')TIMESTAMP ('2002-10-

7、20, 12:00:00')DATE ( 2002-10-20 ')DATE ( 10/20/2002 ')TIME ( 12:00:00 ')TIME ( 12.00.00 ')Note: in the DB2 command editor, you can enter the SQL statement and the internal command in the DB2. To display the current time, you cannot enter current time directly. This can only be re

8、ferenced in the SQL language. To display the following:1) VALUES (current, time)() SELECT, CURRENT, TIME, FROM, SYSIBM.SYSDUMMY1,This is not the same as in SQL SERVER2000. In SQL SERVER2000, you can enter Getdate () to get the time, either to display or to use in the statement SQL.4, all expressions

9、 that return the previous N dataUsing the TOP N format in SQL SERVER2000For example: SELECT, TOP, 10, CARDNO, FROM, CARDIn DB2, use the fetch first N rows only formatFor example: SELECT, CARDNO, FROM, SEALCARD, fetch, first, 10, rows, only5, function useView system function: SELECT * FROM SYSibm.sys

10、functions;For example, ABS (-89) can be entered as a value in SQL, but in order to display the result of the function in the command editor, you can do the following:1) SELECT, ABS (-89), FROM, SYSIBM.SYSDUMMY1;2) VALUES ABS (-89);6, stored proceduresIn developing DB2 stored procedures, we can lever

11、age a lot of DB2's own tools, such as development centers, control centers, and so on. But sometimes scripting can give developers greater flexibility and greater productivity.When we start developing a new or modifying an existing stored procedure, we usually do some of the following preparator

12、y work:1. look at the table structure, field type, associated index, and sample data that will be used by the stored procedure.2. see the definition of a stored procedure or user-defined function (UDF).3. find invalid stored procedures and generate binding statements.4. if a table changes, look at a

13、ll the views, stored procedures, and user-defined functions (UDF) that depend on the tableAlthough the above information can be obtained through the development tools and management tools provided by DB2, the required information can be obtained more quickly through scripts and can be executed repea

14、tedly.The key to using scripts to accomplish these tasks is to understand and use DB2's system tables. Let's briefly review the system tables and views about the DB2:1. syscat.routines: store all stored procedures and user defined function (UDF) information. The routinename field is a stored

15、 procedure or a user-defined function (UDF) of the routinetype field name, said the record is stored procedure (P) or user-defined function (F), packet sequence number lib_id field generated for the compiled stored procedure, the origin field represents the stored procedure or a user-defined functio

16、n (Q. That is composed of SQL, E and user-defined external), valid field represents the stored procedure or user-defined function is valid if the origin field is not Q if the field is empty.2. syscat.packages: store all the bound packages. Where pkgname represents the package name and the valid fiel

17、d indicates whether the package is legal.3. syscat.packagedep: store dependencies on packages. Where the pkgname field represents the package name, the Btype field represents the type of dependent object, and the bName field indicates the name of the dependent object.4. syscat.routinedep: store depe

18、ndencies on programs (routine). Where the routinename field represents the program name, the Btype field represents the type of dependent object, and the bName field indicates the name of the object to be relied on.5. syscat.viewdep: stores dependencies on views. Where the VIEWNAME field represents

19、the view name, the Btype field represents the type of dependent object, and the bName field indicates the name of the object to be relied on.Reviewing and understanding the system tables and views above, we can easily create scripts to complete the preparations for the previously mentioned developme

20、nt stored procedures.1. see the table structure, field type, associated index, and sample dataAlthough we can query the sysibm.systables table for table structure, there is a simpler way to obtain table structure, that is, using the db2look tool. The tool generates the DDL that creates the table and

21、 the associated indexes. If we want to obtain the structure of the specified table in the specified database and the first 20 data for reference, we can write the script viewtbl d as follows. The incoming parameters are the database name and the table name.echo DDL of table%2 and C related index (Ex

22、) Cdb2look, -d,%1, -t,%2, -eecho fisrt rows in table%2 C 20 Cdb2 select * from%2 fetch first 20 rows only2. to see the existing stored procedures and user-defined function (UDF) definition, the results stored in the file, and automatically open the results file.You can do simple queries from the sys

23、cat.routines table to implement the script viewrtn d.3. view all invalid stored procedures and generate binding statementsDeleting a table referenced by a stored procedure causes the stored procedure to be invalid. Invalid stored procedures can be obtained using query syscat.routines and syscat.pack

24、ages methods:SELECTRTRIM (r.routineschema) | '| RTRIM (r.routinename) AS spname,RTRIM (r.routineschema)'P'|SUBSTR (CHAR | | '(r.lib_id+10000000), 2) AS pkgnameFROMSYSCAT.routines RWHERER.routinetype ='P'(AND(r.origin ='Q', AND, r.valid ='Y')OR EXISTS (.AND pkg

25、name ='P'|SUBSTR (CHAR (r.lib_id+10000000), 2)AND valid! ='Y'!)ORDER BY spnameNote that both the syscat.routines and syscat.packages tables must be queried at the same time because the valid value in syscat.routines may still be Y when the package is invalid.If you want to automatica

26、lly generate a binding statement, just rewrite the above SQL to invalidSP d:echo offDB2 "SELECT url=mailto:'db2 rebind package'| RTRIM (r.routineschema)'P'|SUBSTR (CHAR | | '(r.lib_id+10000000), 2) resolve any' FROM SYSCAT.routines R WHERE |''P' (r.routinetyp

27、e = AND (r.origin ='Q'AND r.valid! ='Y') OR EXISTS (SELECT syscat.packages WHERE 1 FROM pkgschema = r.routineschema AND pkgname ='P'|SUBSTR (CHAR (r.lib_id+10000000), 2) AND valid) >rebindsp.bat ='Y')!"4. look at the views, stored procedures, and user-defined

28、functions (UDF) that a table relies onUsing the system view above, it is easy to write scripts:echo offEcho - dependent SPs -DB2 select cschema, cname from syscat.routines R, cedures proc, syscat.packagedep pdep where pdep.bname=upper ('%2') and pdep.bschema=upper (

29、'%1') and r.specificname=proc.specificname AND pdep.pkgname ='P'|SUBSTR (CHAR (r.lib_id+10000000), 2)"Echo - dependent UDF -DB2, select, routineschema, routinename, from, syscat.routinedep, where, bschema = upper ('%1'), and, bName = upper ('%2'), and, Btype, =&#

30、39;T', order, by, bNameEcho - dependent view -DB2, select, viewschema, VIEWNAME, from, syscat.viewdep, where, bschema = upper ('%1'), and, bName = upper ('%2'), and, Btype, ='T', order, by, bNameIndustry experience: 3 best practices for improving the performance and robus

31、tness of DB2 stored proceduresBest practice 1: provide the necessary parameters in the creation of stored procedure statementsCreating a stored procedure statement (CREATE PROCEDURE) can contain many parameters, although they are not required from a syntax point of view, providing these parameters w

32、hen creating a stored procedure can improve execution efficiency. Here are some common parameters:L allow SQL (AllowedSQL)Allow the SQL (AllowedSQL) clause specifies the value of the stored procedure will use the SQL statement, if used, the type of how. Its possible values are as follows:NO SQL: ind

33、icates that the stored procedure is unable to execute any SQL statement.CONTAINS SQL: indicates that stored procedures can execute SQL statements, but they do not read SQL data and do not modify SQL data.READS SQL DATA: indicates that the stored procedure contains SQL statements that do not modify t

34、he SQL data. That is, the stored procedure reads data from the database only.MODIFIES SQL DATA: indicates that stored procedures can execute any SQL statement. That is, the data in the database can be added, deleted and modified.If there is no explicit declaration of allowed-SQL, its default value i

35、s MODIFIES SQL DATA. The efficiency of different types of stored procedure execution is different, among them, NO SQL is the best, and MODIFIES SQL DATA is the worst. If the stored procedure simply reads the data, but because there is no declaration of the allowed-SQL type, it will be executed as a

36、stored procedure that modifies the data, which obviously reduces the execution efficiency of the program. Therefore, when creating a stored procedure, its allowed-SQL type should be explicitly declared.L returns the number of result sets (DYNAMIC, RESULT, SETS, n)A stored procedure can return 0 or m

37、ore result sets. In order to return the result set from the stored procedure, the following steps need to be performed:In the DYNAMIC RESULT SETS clause of the PROCEDURE CREATE statement, declare the number of result sets that the stored procedure will return. If the number of returned result sets d

38、eclared here is less than the number of result sets actually returned in the stored procedure, DB2 will return a warning when the stored procedure is executed.Declare the cursor in the stored procedure body using the WITH RETURN clause.Open the cursor for the result set. When the stored procedure re

39、turns, keep the cursor open.When you create a stored procedure, specifying the number of returned result sets can help the programmer verify that the stored procedure has returned the expected number of result sets and improved the integrity of the program.Best practice 2: make necessary checks and

40、preprocessing of input parametersNo matter which programming language is used, it is necessary to judge the input parameters. The correct parameter verification is the premise to ensure the good operation of the program. Similarly, validation and processing of input parameters during the DB2 storage

41、 process is also important. The correct validation and preprocessing operations include:If the input parameter error, the stored procedure should return a clear value tells the client application, client application can then be processed according to the return value, or to submit a new stored proce

42、dure parameters, or to call other procedures.According to the business logic, the input parameters of a certain pretreatment, such as case conversion, NULL and empty string or 0 conversion.In the DB2 stored procedure development, if we need to initialize NULL, we can use the COALESCE function. This

43、function returns the first non - NULL parameter. For example, COALESCE (piName, '' '), if the variable piName is NULL, then the function returns'', otherwise the value of the piName itself is returned. Therefore, you can use the following code to check whether the piName is not N

44、ULL and is not an empty string:SET poGenStatus = 0;SET piName =RTRIM (COALESCE (piName, '' ');IF (piName ='')THENSET poGenStatus = 34100;RETURN poGenStatus;ENDIF;Similarly, COALESCE can be used to initialize or validate any type of input parameter. The following is a summary of t

45、he initialization rules for parameters:The 1. input parameter is the character type and is allowed to be NULL, for example, the default value is null string,You can use COALESCE (inputParameter, '' ') to convert NULL into an empty string;The 2. input type is integer and is allowed to be

46、NULL. If you want the default value to be 0, you can use COALESCE (inputParameter, 0) to convert NULL to 0;3. input parameters for the character type, and is not allowed to be NULL or an empty string, you can use the RTRIM (COALESCE (inputParameter ') NULL put into the empty string, and then ver

47、ify whether the function returns an empty string;The 4. input type is integer, and is not allowed to be NULL, and do not need to use the COALESCE function, and the IS NULL is used to verify it directly.Best practice 3: exception (condition) processingDuring the execution of a stored procedure, condi

48、tion is often generated because of data or other problems. Depending on the business logic, the stored procedure should handle the exception or return it directly to the caller. For the moment, condition is translated as an anomaly to facilitate understanding. In fact, some anomalies (condition) are

49、 not caused by errors.When the stored procedure in the SQLSTATE statement to return the value of more than 00000 of the time, it shows that in the storage process generated an exception (condition), which indicates that an error occurred, no data is found or there is a warning. In order to deal with

50、 the abnormal storage process, we must declare the exception handler in a procedure body (condition handler), it can decide how to respond to a stored procedure or a plurality of system defined exception or a custom exception.The exception handler type (handler-type) has the following:After the proc

51、essor operation is completed, the L CONTINUE: continues to execute the next statement after the exception statement is generated.L EXIT:, after the processor operation completes, the stored procedure terminates and returns the control to the caller.L UNDO:, DB2 will rollback the stored SQL operation

52、 before the processor operation is executed. After the processor operation completes, the stored procedure terminates and returns the control to the caller.Exception handlers can handle custom exceptions based on a particular SQLSTATE value, or handle system predefined exceptions. The 3 predefined t

53、ypes of the system are as follows:L NOT FOUND: identifies the SQLCODE value or SQLSATE to + 100 = 02000 abnormal. This exception usually occurs when SELECT does not return rows.The L SQLEXCEPTIOIN: identifies an exception that causes the SQLCODE value to be negative.L SQLWARNING: identifies an excep

54、tion that causes a warning exception or causes an SQLCODE value of more than 100.If an NOT, FOUND, or SQLWARNING exception is generated, and the exception handler is not defined for this exception, the system ignores the exception and turns the control flow to the next statement. If an SQLEXCEPTION

55、exception is generated and the exception handler is not defined for this exception, the stored procedure fails and the system returns the control flow to the caller. Therefore, if developers want to change this exception handling process, you must customize the exception handler. For example, you wa

56、nt to terminate the stored procedure when the SQLWARNING exception occurs, and change the variable named stmt to ABORTED. You can define the following statement to define the exception handler:DECLAREEXIT, HANDLER, FOR, SQLEXCEPTION, SQLWARNINGSET stmt ='ABORTED'If the predefined exception s

57、et still does not meet the requirements, you can declare custom exceptions for a particular SQLSTATE value, and then customize the exception declaration handler for this exception. The syntax for declaring custom exceptions for a particular SQLSTATE value is as follows:DECLARE, condition-name, CONDI

58、TION, FOR, SQLSATE, mysqlstateAfter defining exceptions and exception handlers,Any of the execution of the stored procedure uses the SIGNAL condition-name statement to trigger an exception of this custom type.Exception handlers can be defined by separate stored procedure statements, or by compound statement definitions. Note that in the implementation of a compound statement, SQLSATE and SQLCODE values can be changed, if you need to keep the abnormal before the SQLS