db2常用sql（DB2 commonly used SQL）.doc

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, PUBLIC, 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: 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 functions 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 the 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 ')Gets 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-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 referenced 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 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.sysfunctions;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 leverage 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 preparatory 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 all 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 repeatedly.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 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 function (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 field 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 dependencies 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 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 development 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 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) 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 syscat.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.packages 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 pkgname ='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 automatically 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.routinetype = 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 functions (UDF) that a table relies onUsing the system view above, it is easy to write scripts:echo offEcho - dependent SPs -DB2 select proc.procschema, proc.procname from syscat.routines R, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper ('%2') and pdep.bschema=upper ('%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, ='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 robustness 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 when 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: indicates 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 the 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 is 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 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 more 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 declared 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 returns, 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 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 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 procedure 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 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 NULL 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 the 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 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 verify 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, condition 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 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 tha