Introduction to db2

1. Sequence Creation
Create sequence "ZGC3". "SP_SEQ_MO_wbk" AS INTEGER
MINVALUE 0 MAXVALUE 2147483647
Start with 21 increment by 1
CACHE 20 no cycle no order;

Select next value for my_seq FROM sysibm. sysdummy1
 
2. Paging
Select * from (
Select ROWNUMBER () OVER () as ROWID, DESCRIPTION from SS. T_ROLE
) As a where a. ROWID> = 5 and a. ROWID <= 8
3.1 type conversion functions:
Convert to numeric type:
Decimal, double, Integer, smallint, real
Hex (arg): convert to the hexadecimal representation of the parameter.
Converted to string type:
Char, varchar
Digits (arg): returns the string representation of arg, which must be decimal.
Converted to date and time:
Date, time, timestamp
2. Date and Time:
Year, quarter, month, week, day, hour, minute, second
Dayofyear (arg): returns the Daily Value of arg within the year.
Dayofweek (arg): returns the Daily Value of arg within a week.
Days (arg): returns the integer representation of the date, from-01-01.
Midnight_seconds (arg): the number of seconds between midnight and arg.
Monthname (arg): returns the month name of arg.
Dayname (arg): returns the week of arg.
3. String functions:
Length, lcase, ucase, ltrim, rtrim
Coalesce (arg1, arg2 ....) : The first non-null parameter in the returned parameter set.
Concat (arg1, arg2): connects two strings: arg1 and arg2.
Insert (arg1, pos, size, arg2): returns one. arg1 is deleted from the pos and inserted into this position.
Left (arg, length): returns the leftmost length string of arg.
Locate (arg1, arg2, <pos>): searches for the location where arg1 first appeared in arg2. If you specify pos, you can find the location where arg1 first appeared at the pos of arg2.
Posstr (arg1, arg2): returns the position where arg2 first appeared in arg1.
Repeat (arg1, num_times): returns the string that arg1 is repeated for num_times.
Replace (arg1, arg2, arg3): replace all arg2 in arg1 with arg3.
Right (arg, length): returns a string consisting of the Left length bytes of arg.
Space (arg): returns a string containing arg spaces.
Substr (arg1, pos, <length>): returns the length starting from the pos position in arg1. If the length is not specified, the remaining characters are returned.
4. mathematical functions:
Abs, count, max, min, sum
Ceil (arg): returns the smallest integer greater than or equal to arg.
Floor (arg): returns the smallest integer less than or equal to the parameter.
Mod (arg1, arg2): returns the remainder of arg1 divided by arg2. the symbol is the same as that of arg1.
Rand (): returns a random number between 1 and 1.
Power (arg1, arg2): returns the arg2 Power of arg1.
Round (arg1, arg2): rounding to truncation. arg2 is the number of digits. If arg2 is negative, rounding to the number before the decimal point.
Sigh (arg): returns the symbol indicator of arg. -1, 0, 1 indicates.
Truncate (arg1, arg2): truncates arg1. arg2 is the number of digits. If arg2 is a negative number, the arg2 before the decimal point of arg1 is retained.
5. Others:
Nullif (arg1, arg2): If the two parameters are equal, null is returned. Otherwise, parameter 1 is returned.

The following describes how to back up a DB2 database:
Operate Under db2inst1.
Run the following command in the current directory where you want to save the data:
$ Db2stop force (stop database)
$ Db2start (start database)
* Run the following command without stopping the database:
$ Db2 connect to Database Name user Username using password (connect to database)
$ Db2 backup db database name
The system automatically backs up and generates a timestamp data backup file,
For example: xxxx.0.db2inst1. node).catn316.20070814031212.001
 
Restore database
Run the following command in the current directory where you backed up the data:
$ Db2 restore db database name taken at 20070814031212 (take the timestamp generated by the Data Backup directly.

6. load
Db2 load from s. del of del insert into table
7. Use with to query:
With temp (t1, t2, t3, t4, t5)
(Select conntr_no, org_cd, dept_cd, sum (acc_bal), sum (avg_bal_Y) from deposit_m_acct where org_cd = '2013' group by conntr_no, org_cd, dept_cd)
Select t1, t2, t3, t4 from temp
Union all
Select t1, t2, t3, t5 from temp;