Simple operation of DB2

Turn

Recently in see DB2, read while writing some, write down, although write a mess. For later use.
These are all very simple to write. I think it is some simple operation, and some are excerpted from other people's blog specific is to cite which is not very remember.

One, DB2 two kinds of annotation notation
-----Notes
/* Comment */
Note: It seems that the DB2 database schema is somewhat similar to Oracle and has a user and database concept. For example, I just built a table in the J_test database as follows, CREATE TABLE table_name (field list), and then I see in the view that the TABLE_NAME mode is ABC (that is, I log on to the operating system ID), And I if the query select * from Obpm_rt.table_name is not found, that is to say that table_name is part of the ABC account other users are not visible, although in the same database.

II. basic data types of DB2
Numeric type
* Small integer, SMALLINT: int is a two-byte integer with a precision of 5 bits.
* Large integer, Integer: Or INT is a four-byte integer with a precision of 10 bits.
* Jumbo, BIGINT: The Giant is a eight-byte integer with a precision of 19 bits.
* Decimal type, decimal (p,s), DEC (P,s), NUMERIC (p,s), or NUM (p,s):
* Single precision floating point number REAL
* Double precision floating point number double,double PRECISION or float

String type
* Fixed length string: CHARACTER (n) or CHAR (n): the length of the fixed-length string is between 1 and 254 bytes. Default of 1 bytes.
* variable-length string, varchar (n), CHARACTER VARYING (n), or CHAR VARYING (n): VARCHAR (N)-type strings are variable-length strings up to 32,672 bytes long.
A string of *long Varchar:long VARCHAR type is a variable-length string up to 32,700 bytes long.
* Character Large object string CLOB (n): CLOB is a variable-length string, up to 2,147,483,647 bytes long. If only n is specified, then the value of n is the maximum length.
* Binary Large object, BLOB (n[k| m| G]): A binary large object is a variable-length string up to 2,147,483,647 bytes in length. If only n is specified, then n is the maximum length.


Date-Time Data type
*date:date is a three-part value (year, month, and day).
*time:time is a three-part value (hours, minutes, and seconds).
*timestamp:timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond).
* The string representation of a date value is a string that starts with a number and is not less than 8 characters long. The month and day parts of the date value can be omitted from the preceding 0.
* The string representation of a time value is a string that starts with a number and is not less than 4 characters long. The hour portion of the time value preceding 0 can be omitted, and the second part can be omitted completely. If the value of the second is not specified, then it is considered to be 0.
* The string representation of a timestamp value is a string that starts with a number and is not less than 16 characters long. The full timestamp string representation is yyyy-mm-dd-hh.mm.ss.nnnnnn.
The number of months, days, or hours of the timestamp value preceding 0 can be omitted, and microseconds can be truncated or omitted altogether. If the microsecond portion of any timestamp value is omitted at the end of 0, then the vacant digits are assumed to be zero.

Example
CREATE TABLE Db2admin.t_test1 (ID smallint,name character (), addr clob (+), indate date);
Insert into Db2admin.t_test1 (id,name,addr,indate) VALUES (001, ' Jenny ', ' CCCCCCCCC ', ' 2009-10-01 ');

Third, DB2 SQL some syntax

Declare: Defining variables and defining error handling (declare must be followed by begin, and DB2 does not have an anonymous block concept)
Format: Declare sql-var-name data-type default Default-values

Set:set statements to assign values to variables and array elements
Format: SET variable_name = value/expression/null;
Example: SET total = (select SUM (c1) from table);

Value into: Assigning a value to a variable
Format: Values into variable name;
Example: VALUES ' TEST ' into v1;

SELECT into: Assigning Values to variables
Format: SELECT field/function into variable name from TABLE;
Example: SELECT COUNT (*) into var1 from T1;

Returns the current date and time (the private register implementation), similar to a system function such as Oracle's sysdate, that can be accessed and referenced in SQL statements or SQL PL statements:
Current DATE can also be written as Current_date
Current time can also be written as Current_time
Current TIMESTAMP can also be written as Current_timestamp
Current USER can also be written as Current_User
Current PATH can also be written as Current_path

Four, DB2 the line number of the wording
Row_number () over () as number

Five, how to commit the stored procedure under the command character
CMD-->db2cmd
Add the @ symbol at the end of the stored procedure and then enter it at the command: DB2 [email protected]-VF Procfile.sql can generate the process.
SQL file for non-stored procedure, break in command: DB2–TVF sqlfile.sql

Six, DB2 cursors
Format: DECLARE cursor_name cursor for select-statement [without Hold]/[with hold] [without Return]/[with RETURN] [to caller]/[ To CLIENT]
Keyword Explanation:
Select-statement: is a valid SQL SELECT statement
Without Hold/with hold: option defines the cursor state (open/close) after the COMMIT operation. Without hold is on by default.
If a cursor is defined with the Hold option, the cursor remains OPEN after the COMMIT operation.
After the ROLLBACK operation, all cursors are closed.
The without Return/with return option specifies whether the result table of the cursor is used as a result set returned from a procedure.
The With Return to CALLER option specifies that the result set from the cursor is returned to the caller, which can be another procedure or a client application. This is the default option. \
The With Return to client option specifies that the result set from the cursor is returned to the client application, bypassing any intermediate nested procedures.

To return a result set from a procedure, you need:
Creates a procedure that specifies the DYNAMIC RESULT sets clause when it is created.
Declares a cursor, specifying a with RETURN clause when declared.
Open the cursor and leave it in open state.
If the cursor is closed, the result set cannot be returned to the caller application.

Cursor Handling
To handle the results of a cursor in a process, you need to do the following:
DECLARE a cursor at the beginning of a stored procedure block.
Open the cursor.
Extracts the result of the cursor to a local variable that was previously declared.
Closes the cursor. (Note: If you do not close the cursor now, the cursor will be closed implicitly when the procedure terminates).

Seven. Selection, branching, looping syntax
1) If then else (as in Oracle notation)
IF condition Then
Statement
ELSEIF condition Then
Statement
ELSE
Statement
END IF;
2) case (same as Oracle notation)
Case
When condition then statement
.
.
.
Else statement end case;

3) loop loop (there are four types of loop,while, REPEAT, for, only loop and for are introduced here)
Loop_name:loop
SQL statements;
LEAVE Loop_name;
END LOOP Loop_name

Typical cursor loop format:
OPEN cursor_name;
Loop_name:loop
FETCH cursor_name into variable name (can be separated by commas);

IF end_ <> 0 Then-exit loop when last row is reached
LEAVE Loop_name;

END IF;
Loop Execution Statement
END LOOP Loop_name;

4) Implicit loop on the for result set (Db2admin.p_for_example)
For Loop_name as SELECT ... From
Do
SQL statements;
END for;


Viii. Stored Procedures
Format: CREATE PROCEDURE procedure_name ([in/out/inout paraname TYPE])
Specific Specific_name
DYNAMIC RESULT Sets Integer
Modifies SQL Data/contains sql/reads SQL DATA
Not deterministic/deterministic
called on NULL INPUT
LANGUAGE SQL
PARAMETER Ccsid/ascii/unicode
BEGIN [NOT] ATOMIC
Sql-procedure-body:
END

Keyword Explanation:
Procedure-name: The name of the stored procedure, in the same mode as the same database, cannot have stored procedures with the same number of parameters as the stored procedure name, even if the parameters are of different types.
(In | Out | INOUT parameter-name Data-type,...) : Incoming parameters
In: Input parameters
Out: Output parameters
INOUT: As input and output parameter
Parameter-name: The parameter name, which is a unique identifier in this stored procedure.
Data-type: A parameter type that can receive SQL types and tables created. Long VARCHAR, long vargraphic, DATALINK, reference, and user-defined types are not supported.
Specific specific-name: A unique name (alias) that can be replaced with a stored procedure name, which is used to Dorp stored procedures, or to add gaze to a stored procedure, but not to call a stored procedure. If not specified, the database automatically generates a YYMMDDHHMMSSHHN timestamp name. It is recommended to give an alias.
DYNAMIC Result Sets Integer: Specifies the maximum number of results that the stored procedure returns. Although there is no return statement in the stored procedure, it can return a result set.
CONTAINS sql, READS SQL data, modifies SQL data: Specifying SQL access levels in stored procedures
CONTAINS sql: Indicates that the stored procedure can be executed, neither reading SQL data nor modifying SQL data.
READS SQL data: Indicates that the stored procedure can be executed, read SQL, but not modify the SQL.
Modifies SQL DATA: Indicates that a stored procedure can execute any SQL statement. Data in the database can be added, deleted, and modified.
Deterministic or not deterministic: Indicates that the stored procedure is dynamic or non-dynamic. The dynamic return value is indeterminate. Non-dynamic stored procedures return the same value each time the execution is performed.
Called on NULL input: Indicates that a stored procedure can be called regardless of whether any of the input parameters are null, and any out or inout parameter can return a NULL or non-null value. Verifying that the parameter is null is done in the process.
LANGUAGE sql: Specifies that the body of the program is in the SQL language.
EXTERNAL action or no EXTERNAL action: Indicates whether the stored procedure performs some activity that alters the state of the database, not through the database Manager tube. Default is
EXTERNAL ACTION. If you specify no EXTERNAL ACTION, the database determines the best optimization scenario.
PARAMETER CCSID: Specifies the encoding of all output string data, by default Unicode encoding database is PARAMETER CCSID Unicode, and the other database defaults to PARAMETER CCSID 3 ASCII.

Nine, get db cfg for DatabaseName get configuration information for the database
Update db CFG for testdatabase using Logfilsiz 6000 modify log file size
DB2? sql964 viewing error explanations based on error codes

X. SQL functions (case statements cannot be used according to experiments)
CREATE FUNCTION [functionname] (<<[ParameterName]> [inputdatatype],... >)
RETURNS [[Outputdatatype] |
TABLE ([ColumnName] [Columndatatype],...) |
ROW ([ColumnName] [Columndatatype],...)]
<specific [specificname]>
<language sql>
<deterministic | Not deterministic>
<external ACTION | NO EXTERNAL action>
<contains SQL | READS SQL DATA | Modifies SQL data>
<static dispatch>
<called on NULL input>
[Sqlstatements] | RETURN [Returnstatement]

functionname Specifies the name of the SQL function to be created.
PARAMETERNAME Specifies the name of one or more function arguments.
INPUTDATATYPE Specifies the data type required for the parameters identified by the parametername.
OUTPUTDATATYPE Specifies the type of data returned by the function, which returns a single value
Table function, return a table, call method select * FROM table (function_name (parameter)) alias
Row row function, return one row, call method temporarily unknown
COLUMNNAME Specifies the name of one or more columns returned by the function (if the function returns a table or row).
COLUMNDATATYPE Specifies the data type returned by the column identified by ColumnName.
SPECIFICNAME specifies the specific name assigned to the UDF. This name can be used to reference or delete a function, but it cannot be used to invoke a function.
sqlstatements specifies one or more SQL statements that are executed when the function is called. These statements are combined into a dynamic composite SQL statement.
returnstatement Specifies the return SQL statement for the application that returns the calling function. (If the SQL function body consists of a dynamic compound statement, it must contain at least one return statement, and a return statement must be executed when the function is called.) If the function is a table function or a row function, it can contain only one RETURN statement, and the statement must be the last statement used.

Xi. string addition and Oracle one-phase use | |

12. Dynamic SQL execution
Dynamic SQL is not aware of all of its SQL statements at run time relative to static SQL statements. For example, if col1 and tabname are the two variables in the statement, we are working with dynamic SQL:
' SELECT ' | | col1 | | ' From ' | | TabName;
Dynamic SQL is recommended in DDL to avoid subordinate issues and package invalidation issues. Dynamic SQL is also required to perform recursive operations.
You can perform dynamic SQL in the following two ways:
? Using the Execute Immedate statement – This is an ideal method for SQL executed alone (as with Oracle, except that Oracel seems to have only one execution method)
? Use the Prepare statement before the EXECUTE statement – this method is ideal for multi-execution SQL statements.
CREATE PROCEDURE dyn1 (in value1 int, in value2 int)
Specific DYN1
BEGIN
DECLARE stmt varchar (255);
DECLARE St STATEMENT;
SET stmt = ' INSERT into T2 VALUES (?,?) ';
PREPARE St from stmt;
EXECUTE St USING value1, value1;
EXECUTE St USING value2, value2;
SET stmt = INSERT into T2 VALUES (9,9) ';
EXECUTE IMMEDIATE stmt;
END

13, DB2 with the data in single quotation marks as a string, if you want to enter a single quotation mark you need to enter two, the first as an escape character (like Oracle)

14. Comparison of DB2 users and Oracle users
A database corresponds to only one instance, but one instance can have multiple databases (as with Oracle), and the larger difference is that the user's definition is not the same

DB2: Must be a system user
Oracle: You can create a new user with a user with DBA authority, not necessarily the operating system user

DB2: Each user creates a new table that belongs to the user, but a user can create a new query to update the data for the B user. You only need to add the B user ID in front of you. DB2 is that all data is placed in the same database, but when the table is built, the current user ID is considered part of the table name.
Oracle: Each user creates a new table that is subordinate to that user, and other users are not able to make additional user data deletions unless the user is given the ability to manipulate other user data. Oracle is a fully user-independent database.




See Stored procedure text

Select text from syscat.procedures where procname= ' PROC1 ';

--Look at the table structure
--describe table Syscat.procedures Describe select * from Syscat.procedures




XV, instance operations
Win cmd input: Db2cmd-->db2icrt < Instance name > create a new instance
Db2ilist shows how many instances of the database
DB2 Get instance shows the currently running instance
Set Db2instance= instance name sets the current instance (you can also set the Db2instance variable in the WIN environment variable)
Db2start starting the current instance
Db2stop stopping the current instance
Db2stop Force stop
Db2idrop Deleting an instance

Note: If you need to display an instance or a database (added by command) in the control center, you need to add it manually in the control center (right-click Add-Find)

DB2 CREATE DATABASE name create a new database under the current instance
DB2 List DB directory displays all databases for the instance
DB2 drop DATABASE Database name Delete one
DB2 Connect to <database_name> Connection database

As the database is created, several default objects are also created: Table space, table, buffer pool, log file.
The default number of table spaces is as follows (basic and Oracle almost, except for names only)
The Syscatspace (Oracle:system) Table space contains the catalog table (catalog), which contains system information that cannot be changed or is not deleted, or the database does not work correctly
TEMPSPACE1 (oracle:temp) is used when DB2 implements some sort of operations that require additional space such as sorting.
USERSPACE1 (oracle:users) If no space is specified when creating a table, the system typically uses USERSPACE1 to store your database tables.
Systoolspace (Oracle:sysaux) System tool class storage space,

16. DB2 Configuration
Use the Configuration Wizard tool (Configur) tion Advisor tool) to set the DB2 parameters. Right-click the database in the control center and select "Configuration Advisor".
Depending on your description of system resources and workload, the Configuration Wizard provides a list of DB2 recommended parameters that you can read to get more detailed information about the DB2 configuration, or to use the values provided by the Configuration Wizard directly.
A DB2 server can be configured at four different levels:
Environment variables
Database Manager configuration file (db cfg)
Database configuration file (db cfg)
DB2 Profile Registration Form

Environment variables are set at the server's operating system level. The database Manager profile variables are set at the instance level.
Database configuration variables are set at the database level. DB2 profile registration can be set at the operating system layer or the instance layer

1) Environment variables
Environment variables are variables that are set at the operating system level. Db2instance is one of the main environment variables. This variable shows an instance of the current activity-that is, an instance of your DB2 command to perform the operation.
For example, to set an active instance ' myinst ' in a command window, you can run the following operating system command: Set Db2instance=myinst.

2) Database Manager configuration file
The Database Manager configuration file (dbm cfg) contains parameters that affect the corresponding instance and its database. You can view and modify the Database Manager configuration file from the command line or the DB2 control center.
To start dbm CFG from Control center, select the instance object within the control center instance (instance) folder, then right-click and select Configuration parameters (Configure Parameters) on the pop-up menu.
After selecting the configuration parameters (Configure Parameters), the screen displays a list of dbm cfg parameters,
Many parameters are dynamic and take effect immediately; However, there are some parameter modifications that require the instance to be restarted. You can do this by using the Db2stop and Db2start commands at the command line.
Before stopping an instance, you must disconnect all applications from the instance. If you want to force the instance to stop, you can use the Db2stop Forces command.
You can also stop and start the instance through the control center-click the instance object to select Stop or start.
DB2 get dbm CFG to regain information for dbm CFG
DB2 update dbm CFG using <parameter_name> <value> updating parameter information for dbm CFG

3) Database configuration file (db cfg)
The database configuration file (db cfg) contains the parameters that affect the corresponding database. The database configuration file can also be viewed or modified through the command line or the control center.
Starting DB CFG from the control center, you can select the database object in the database folder and right-click the pop-up menu and select configuration parameters (Configure Parameters) from the menu.
After selecting the configuration parameters (Configure Parameters), the screen displays a list of db cfg parameters
Get DB CFG for <database_name> re-get DB cfg information for the specified database
Update db CFG for <database_name> using <parameter_name> <value> updating db cfg parameter values

4) DB2 Profile Registration Form
The DB2 profile Registry contains parameters for platform-related global (affecting all instances) or power levels (affecting only one instance).

17. Some other statements
DB2 list Applications Show Detail query connection details currently connecting to the database
DB2 list applications queries the connection that is currently connecting to the database
DB2 Force application All disconnect all connections to the database
DB2 Force application () (<application> fill in the handle of the program "Db2bp.exe", this value is obtained by the DB2 List Applications command)
logsecond--the number of level two log files that can be allocated when the primary log file is fully occupied

18, CREATE table can specify the default value of the column, the format is as follows with default value
Create an identity for the DB2 automatic assignment SubscriberId INTEGER GENERATED always as identity (START with the INCREMENT by 100),
Create series and references (like Oracle, the first call needs to be initialized, that is, the first call can only be made with Nextval): Create a format: Creation SEQUENCE Seq_name START with the INCREMENT by 1 NO CYCLE
Reference format: Nextval for Seq_name (next value)/prevval for Seq_name (current value)
SYSIBM. SYSDUMMY1. This is a system catalog table that contains one row of columns. If a query requires only one output, you can use the table. (such as Oracle's dual table)

? SYSCAT. TABLES
? SYSCAT. INDEXES
? SYSCAT. COLUMNS
? SYSCAT. FUNCTIONS
? SYSCAT. Procedures

Simple operation of DB2