Summary of common SQL statements of DB2

1. Combined statement execution

Begin atomic

Expression 1, semicolon, space, and carriage return

Expression 2; semicolon (,), space, and carriage return

End

2. Tables with limited access permissions (the Public select access permission for these tables should be revoked)

Syscat. dbauth

Syscat. tabauth

Syscat. packageauth

Syscat. indexauth

Syscat. colauth

Syscat. passthruauth

Syscat. schemaauth

 

Useful directory tables

Syscat. columns: Contains each row corresponding to the columns defined in the table or view.

Syscat. indexcoluse: contains all columns contained in each row

Syscat. indexes: Contains each row corresponding to each index defined in the table or view.

Syscat. Tables: Each created table, view, and alias corresponds to one row.

Syscat. Views: Each created view corresponds to one or several rows.

 

Data uniqueness through indexes: create unique index indexname on table (column)

Remove duplicate rows: Select distinct column from table

 

3. DB2 Time Functions

Obtain the year, month, day, and hour of the current time:

Year (current timestamp)
Month (current timestamp)
Day (current timestamp)
Hour (current timestamp)
Minute (current timestamp)
Second (current timestamp)
Microsecond (current timestamp)

Obtain the date and time of the current time respectively.

Date (current timestamp)
Time (current timestamp)

Time calculation:

Current date + 1 year
Current date + 3 years + 2 months + 15 days
Current Time + 5 hours-3 minutes + 10 seconds

Calculate the number of days between two dates:

Days (current date)-days (date ('2017-10-22 ′))

The current time in milliseconds to be removed:

Current timestamp-microsecond (current timestamp) microseconds

Convert time to string:

Char (current date)
Char (current time)
Char (current date + 12 hours)

Convert string to time:

Timestamp ('2017-10-20-12.00.00.000000 ′)
Timestamp ('2017-10-20 12:00:00 ′)
Date ('1970-10-20 ′)
Date ('1970/10/20 ′)
Time ('12: 00: 00 ′)
Time ('12. 00.00 ′)

 

Note: In the DB2 command editor, you can enter SQL statements and internal commands in DB2. To display the current time, you cannot directly enter the current time, which can only be referenced in the SQL language. to display the current time, follow these steps:

1) values (current time)

2) Select current time from sysibm. sysdummy1

This is different from SQL Server2000. in SQL Server2000, you can enter getdate () to get the time, which can be displayed or used in SQL statements.

 

4. All expressions that return the first n data records

Use Top N format in SQL Server2000

For example, select top 10 cardno from card

Use the fetch first n rows only format in DB2

For example, select cardno from sealcard fetch first 10 rows only

 

5. Function usage

View system functions: Select * From sysibm. sysfunctions;

For example, ABS (-89) can be input to SQL as a value, but you can use the following methods to display the function results in the Command Editor:

1) Select ABS (-89) from sysibm. sysdummy1;

2) values ABS (-89 );

 

6. Stored Procedure

During the development of the DB2 stored procedure, we can use many tools that come with DB2, such as the development center and control center. However, sometimes using scripts can give developers greater flexibility and productivity.

When developing a new or modifying an existing stored procedure, we usually perform the following preparations:

1. view the table structure, field type, related indexes, and sample data used in the stored procedure.

2. View related stored procedures or user-defined functions (udfs) definitions.

3. Search for invalid stored procedures and generate binding statements.

4. If a table changes, view all views, stored procedures, and user-defined functions (udfs) dependent on the table)

Although the above information can be obtained through the development 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 complete the preceding tasks is to understand and use DB2 system tables. Let's first briefly review the system tables and views of DB2:

1. syscat. Routines: stores all stored procedures and user-defined functions (udfs) information. The routinename field is the name of the stored procedure or user-defined function (UDF). The routinetype field indicates whether the record represents the Stored Procedure (p) or user-defined function (f ), the lib_id field is the package serial number generated after the stored procedure is compiled. The origin field indicates the source of the stored procedure or user-defined function (Q indicates that it is composed of SQL, e Indicates user-defined and external). The valid field indicates whether the stored procedure or user-defined function is valid. If the origin field is not Q, the field is blank.

2. syscat. Packages: stores all boundProgramPackage. Pkgname indicates the package name, and valid indicates whether the package is valid.

3. syscat. packagedep: stores dependencies on packages. The pkgname field indicates the package name, The btype field indicates the type of the dependent object, and the bname field indicates the name of the dependent object.

4. syscat. routinedep: stores dependencies between programs (routine. The routinename field indicates the program name, The btype field indicates the type of the dependent object, and the bname field indicates the name of the dependent object.

5. syscat. viewdep: stores the dependency between views. The viewname field indicates the view name, The btype field indicates the type of the dependent object, and the bname field indicates the name of the dependent object.

After reviewing and understanding the above system tables and views, we can easily create scripts to complete the preparations for the previously mentioned stored procedure development.

1. view the table structure, field type, related indexes, and sample data.

Although we can query the sysibm. Tables tables Table to obtain the table structure, there is a simpler way to obtain the table structure, that is, using the db2look tool. This tool can generate DDL statements for creating the table and related indexes. For example, to obtain the structure of the specified table in the specified database and the first 20 pieces of data as a reference, you can write the script viewtbl. CMD as follows. The input parameters are the Database Name and table name respectively.

@ Echo ---------------- DDL of table % 2 and Related Index (Ex )------------------

@ Db2look-D % 1-T % 2-e

@ Echo ---------------- Fisrt 20 rows in Table % 2 ------------------

@ DB2 select * from % 2 fetch first 20 rows only

2. view existing stored procedures and user-defined functions (udfs), save the results to a file, and automatically open the results file.

You can perform a simple query in the syscat. Routines table to implement the viewrtn. CMD script.

@ DB2 select text from syscat. Routines where routinename = upper ('% 1')> % 1. SQL

@ Start % 1. SQL

3. View All invalid stored procedures and generate binding statements

Deleting a table referenced by a stored procedure will invalidate the stored procedure. Invalid stored procedures can be obtained by querying syscat. Routines and syscat. packages:

Select

Rtrim (R. routineschema) | '.' | rtrim (R. routinename) as spname,

Rtrim (R. routineschema) | '.' | 'P' | substr (char (R. lib_id + 10000000), 2) As pkgname

From

Syscat. Routines R

Where

R. routinetype = 'P'

And (

(R. Origin = 'Q' and R. Valid! = 'Y ')

Or exists (

Select 1 from syscat. Packages

Where pkgschema = R. routineschema

And pkgname = 'P' | substr (char (R. lib_id + 10000000), 2)

And valid! = 'Y'

)

)

Order by spname

Note that you must query the syscat. Routines and syscat. Packages tables at the same time, because the valid value in syscat. routines may still be y when the package is invalid.

To automatically generate a rebinding statement, you only need to rewrite the preceding SQL statement to invalidsp. CMD:

@ Echo off

DB2 "select'@ DB2Rebind package '| rtrim (R. routineschema) | '. '| 'P' | substr (char (R. lib_id + 10000000), 2) | 'resolve any' from syscat. routines R where R. routinetype = 'p' and (R. origin = 'Q' and R. valid! = 'Y') or exists (select 1 from syscat. packages where pkgschema = R. routineschema and pkgname = 'P' | substr (char (R. lib_id + 10000000), 2) and valid! = 'Y') "> rebindsp. bat

4. view the views, stored procedures, and user-defined functions (udfs) on which a table depends)

Using the system view above, we can easily compile a script:

@ Echo off

Echo --- 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 bname

 

Echo --- dependent view ---

DB2 select viewschema, viewname from syscat. viewdep where bschema = upper ('% 1') and bname = upper (' % 2') and btype = 't'order by bname

Industry experience: three best practices to improve the performance and robustness of DB2 stored procedures

Best Practice 1: Provide necessary parameters in the creation of stored procedure statements

The create procedure statement can contain many parameters. Although they are not required in syntax, these parameters can improve execution efficiency. Below are some common parameters:

L allow SQL (allowed-SQL)

The value of the SQL (allowed-SQL) clause specifies whether the stored procedure uses SQL statements. If so, what is the type of the SQL statement. Its possible values are as follows:

No SQL: The Stored Procedure cannot execute any SQL statement.

Contains SQL: indicates that the stored procedure can execute SQL statements, but does not read or modify SQL data.

Reads SQL data: Contains SQL statements that do not modify SQL data during storage. That is to say, the stored procedure only reads data from the database.

Modifies SQL data: indicates that the stored procedure can execute any SQL statement. You can add, delete, and modify data in the database.

If allowed-SQL is not explicitly declared, its default value is modifies SQL data. Different types of stored procedures have different execution efficiency, of which no SQL is the most efficient and modifies SQL data is the worst. If the stored procedure only reads data but does not declare the allowed-SQL type, it will be executed as a stored procedure for data modification, which will obviously reduce the execution efficiency of the program. Therefore, when creating a stored procedure, the allowed-SQL type should be clearly stated.

L number of returned result sets (dynamic result sets N)

A stored procedure can return 0 or more result sets. To return a result set from a stored procedure, perform the following steps:

Declare the number of result sets to be returned by the stored procedure in the dynamic result sets clause of the create procedure statement. If the number of returned result sets declared here is less than the number of actually returned result sets in the stored procedure, DB2 returns a warning when executing the stored procedure.

Use the with return clause to declare the cursor in the Stored Procedure body.

Open a cursor for the result set. When the stored procedure returns, keep the cursor open.

Specifying the number of returned result sets when creating a stored procedure can help the programmer verify whether the stored procedure has returned the expected number of result sets and improve the program integrity.

Best Practice 2: Perform necessary checks and preprocessing on input parameters

Either MethodProgramming LanguageThe input parameters are required. Correct parameter verification is a prerequisite to ensure the program runs well. Similarly, it is important to verify and process input parameters in the DB2 stored procedure. Correct verification and preprocessing operations include:

If the input parameter is incorrect, the stored procedure should return a clear value to inform the customer application. Then, the customer application can process the returned value or submit a new parameter to the stored procedure, or call other programs.

Input parameters are pre-processed based on the business logic, such as case-sensitivity conversion, and conversion between null and null strings or 0.

In the development of DB2 storage process, if you need to initialize null, we can use the coalesce function. This function returns the first non-null parameter. For example, coalesce (piname, ''). If the piname variable is null, the function returns''. Otherwise, the piname value is returned. Therefore, you can use the followingCodeCheck whether piname is a non-null and non-empty string:

Set pogenstatus = 0;

Set piname = rtrim (coalesce (piname ,''));

If (piname = '')

Then

Set pogenstatus = 34100;

Return pogenstatus;

Endif;

Similarly, you can use coalesce to initialize or verify any type of input parameters. The following is a summary of the parameter initialization rules:

1. If the input parameter is of the character type and can be null, you can use coalesce (inputparameter, '') to convert null to a null string;

2. If the input type is integer and null is allowed, if you want the default value to be 0, you can use coalesce (inputparameter, 0) to convert null to 0;

3. if the input parameter is of the character type and cannot be null or a null string, you can use rtrim (coalesce (inputparameter, '') to convert null to an empty string, then, verify whether the function return value is a null string;

4. The input type is integer and cannot be null. You do not need to use the coalesce function to directly use is null for verification.

Best Practice 3: Exception Handling

In the execution of stored procedures, exceptions are often caused by data or other problems ). According to the business logic, the stored procedure should handle exceptions or directly return them to the caller. Here, condition is translated as an exception for ease of understanding. In fact, some exceptions are not caused by errors.

When the SQL state returned by the statement in the Stored Procedure