Shulou(Shulou.com)06/01 Report--

Start the database:

Start the db2 service: db2start

Activate database instance: db2 activate database

View the active database: db2 list active databases

 

Shut down the database:

Failed database example: db2 deactivate database

Shut down the database service: db2stop

View the database: db2 list db directory

View database applications: db2 list applications

View database application and process number: db2 list applications show detail

View database tablespaces: db2pd-db-tablespace

View database configuration: db2 get db cfg for

Connect to the database: db2 connect to

Db2 connect to user[user _ name] using [password]

Disconnect the database: db2 connect reset/db2 terminate

Create a database: db2 create db

Delete database: db2 drop database (if it cannot be deleted, try to disconnect the active connection or restart db2)

List the system table: db2 list tables for system

List all user tables: db2 list tables

List all tables: db2 list tables for all

List specific user tables: db2 list tables for schema [user]

Copy a table: db2 create table T1 like T2

Show table structure: db2 describe table tablename

Query table: db2 "select * from table tablename where..."

Execute the SQL script: db2-tvf scripts.sql

View the error code message:

Db2? 10054

Stop the active connection:

Db2 force application all

Db2 force application all

Db2 force application all

Db2stop

View deadlocks:

Db2 get snapshot for locks on

Db2 "select agent_id,tabname,lock_mode from table (snap_get_lock (')) as aa"

Kill the process:

Db2 force application (NUM)

Monitor SQL statements that consume a lot of DB2:

Eg: (DB_NAME=SUNDB)

Db2top-d SUNDB-View consumed resources press l according to the prompt, Application Handle appears, and find the Application Handle with high resource consumption (stat)

Write down the app handle.

Db2pd-d SUNDB-dyn-application > / tmp/db2pd1.txt-everywhere conversation statements, ready for tuning

Set the connection method (restart will take effect):

Db2set DB2COMM=tcpip

Db2set DB2COMM=

I. Foundation of db2

Basic grammar

Note: "-" (two minus signs)

String concatenation: "| |"

For example, set msg='aaaa' | | 'bbbb', then msg is' aaaabbbb'.

String reference:''(be sure to use single quotation marks). If you need to enter single quotation marks, enter two single quotation marks.

End of statement: ";"

Syntax source: PASCLE

Escape character

If you want the query string to contain'%'or'_', you have to use the escape character (Escape Characters). For example, to query a record that contains the string'99%'in book_title:

SELECT * FROM books WHERE book_title like'% 99%% 'escape'!'

Escape'!'in the back Is to specify an escape character'!', indicating that it is immediately followed by the escape character'!' The latter% is no longer a wildcard.

DB2 command parameter options

Db2 list command options can view

-a display SQLCA OFF

-c automatically implement ON

-e display SQLCODE/SQLSTATE OFF

-f read the input file OFF

-l record commands in a history file OFF

-n remove the newline character OFF

-o display output ON

-p displays interactive input prompt ON

-r Save the output to the report file OFF

-s stops executing OFF when a command goes wrong

-t sets the statement termination character OFF

-v send back the current command OFF

-w displays FETCH/SELECT warning message ON

-x do not print column headings OFF

-z Save all output to the output file OFF

The specific functions of these options and their default settings are:

.a displays the data of SQLCA. The default is OFF.

C whether the SQL command is implemented automatically, the default is ON.

.e {c | s} displays SQLCODE or SQLSTATE. The default is OFF.

The .f file name specifies the input of the command from standard input to a file, which defaults to OFF.

Note: command "db2"

< 文件名"与"db2 -f 文件名"作用相同。 .l 文件名将命令记录到历史文件中，缺省为 OFF。 .n 除去换行字符，缺省为 OFF。 .o 将输出数据及信息送到标准输出，缺省为 ON。 .p 在交互方式下显示命令行处理器的提示信息，缺省为 ON。 .r 将输出保存到指定文件中，缺省为 OFF。 .s 执行批处理文件中或交互方式下的命令出错时即停止执行操作，缺省为 OFF。 .t 设置语句终止字符，缺省为 OFF。 .v 回送当前命令到标准输出，缺省为 OFF。 .w 显示 FETCH 或 SELECT 警告信息，缺省为 ON。 .x 不打印列标题，缺省为 OFF。 .z 文件名将所有输出保存到输出文件，缺省为 OFF。 SQLSTATE含义 在db2命令行方式下输入：？ 20012（SQLSTATE值） 可以获取sql错误含义 import和export的用法 IMPORT FROM CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES importmsgs.txt INSERT INTO cmmcorc.CO_ACCEPTANCEDRAFT; EXPORT TO CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES exportmsgs.txt SELECT * FROM cmmcorc.CO_ACCEPTANCEDRAFT; 如果是Sybase导出的文本，并且用TAB分隔符的文档，那么可以采用 Db2 import from filename.txt of del modified by COLDEL0x09 insert into tablename; 其中COLDEL是关键字，0x09是16进制，表示tab符号 Load用法 load from tempfile of del modified by delprioritychar replace into TABLENAME nonrecoverable； 说明： 在不相关的数据表export数据时，可以采取并发的形式，以提高效率； TABLENAME指待清理table的名称； modified by delprioritychar防止数据库记录中存在换行符,导致数据无法装入的情况; replace into对现数据库中的内容进行替换，即将现行的数据记录清理，替换为数据文件内容； nonrecoverable无日志方式装入； 查询出用户表 SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR='USER' 如何知道当前DB2的版本? select * from sysibm.sysversions 如何知道TABLESPACE的状况? select * from sysibm.SYSTABLESPACES 如何知道INDEX的状况? select * from sysibm.sysindexes where tbname='XXXX' or describe indexes for table table_name show detail 测试SQL的执行性能 db2batch -d DB_NAME -f select.sql -r benchmark.txt -o p3 select.sql是select语句写在文件中 如何获取连接的进程 List applications 删除当前正在使用的application: db2 "force application (Id1,Id2,Id3)" Id1,Id2,Id3 是List显示的应用号； 删除所有的进程 db2 force application all 查看当前应用号的执行状态： db2 get snapshot for application agentid 299 |grep Row 如何修改缓冲池 db2 alter bufferpool ibmdefaultbp size 10240 如何知道表的字段的状况? select * from sysibm.syscolumns where tbname='XXXX' 如何知道DB2的数据类型? select name,* from sysibm.sysdatatypes 如何知道BUFFERPOOLS状况? select * from sysibm.sysbufferpools 如何查看表的结构? describe table table_name or describe select * from schema.table_name 如何快速清除一个大表? alter table table_name activate not logged initally with empty table or import from null_file of del replace into table_name 如何查看数据库的包? select * from syscat.packages 如何查看数据库的存储过程? select procname,text,* from syscat.procedures Or select procname,text from sysibm.sysprocedures 如何查看数据库SAMPLE的配置文件的內容? get database configuration for sample or get db cfg for sample 如何將数据库SAMPLE的参数设置为默认数值? reset database configuration for sample or reset db cfg for sample 如何修改数据库SAMPLE配置参数数值? update database configuration for sample using or update db cfg for sample using 如何重新啟動数据库? Restart db db_name 如何激活数据库? Activate db db_name 如何停止数据库? Deactivate db db_name 如何重命名表? Rename old_tablename to new_tablename 如何设置DB2环境变量 Db2set命令，语法如下： Db2set variant=value 如何除去quiesce状态 1. 连接到数据库 2. 用 list tablespaces 判断哪个tablespace处于quiesce状态和和取得对象（object）ID 3. 判断对象ID对应的表 a)用 db2 "select tabname from syscat.tables where tablid=对象ID" 得到表名 b)用 db2 list history 判断是那个表 4. 用 db2 quiesce tablespaces for table 表名 reset 去除quiesce状态 如何实施已删除表的恢复(Dropped Table Recovery) 1. 首先数据库要可以前滚恢复(数据库配置参数logretain或userexit打开)。 2. 对要实施Drop Table Recovery的表空间(限regular tablespace)，执行： alter tablespace 表空间名 dropped table recovery on 3. 用 list history dropped table all for 数据库名 得到删除表的tableid(例如 0000000000006d0000020003)和表结构的生成语句(DDL)，记录tableid和该语句以便恢复。之后，用drop命令删除的表中的数据可以在前滚恢复时导出。 4. 恢复数据库后，如果想恢复已删除的表，在前滚时加recover dropped table tableid to 目标目录 。 在该目录下被删除的表中的数据导出，例如导出至 ../NODE0000/data文件。利用上面提到表结构生成语句生成被删除了的表，然后用import命令将数据导入表中。 如何备份数据库 db2 "backup database sample tablespace (syscatspace, userspace1) online to /dev/rmt0 without prompting" or db2 "backup database sample user db2admin using db2admin to c:\backup with 3 buffers buffer 1000 without prompting" 如何生成所有对象的DDL db2look -d DB_NAME -a -e -m -l -f -o filename.sql 如何恢复数据库 restore db db1 to /tstdb2/catalog into db newlogpath /tstdb2/db2log buffer 2048 replace existing redirect parallelism 16; set tablespace containers for 1 using (path '/tstdb2/db2tmp'); set tablespace containers for 2 using (device '/dev/rtstcontlv00' 2621440, device '/dev/rtstcontlv01' 2621440, device '/dev/rtstcontlv02' 2621440, device '/dev/rtstcontlv03' 2621440 ) ; restore db db1 continue; 恢复完成后，执行db2s命令，如果出错，再执行如下命令： db2 rollforward db db to end of logs and complete 如何查看数据库目录 1)首先用 db2 list database directory 命令查看系統資料庫目錄（System Database Directory）中有沒有該資料庫。 如果有，應該再確定該資料庫是沒有用的資料庫之後用 db2 drop database 資料庫名將其刪除。 2)如果沒有，再用 db2 list database directory on location 看在本端資料庫目錄(Local Database Directory）中有沒有該資料庫，location指定資料庫的位置（如Windows下的C: ,Unix下的/home/db2inst1）。 如何更改本地系统名称 1．db2set db2system=新系统名 2. db2 terminate 3. db2 uncatalog node 原节点名 4. db2 terminate 如何使用DB2MOVE Db2move DB_NAME action -u USERID -p PASSWORD Action:export,import,load Options如下： Option Default Notes -tc table-creators all_creators EXPORT.Wildcard -tn table-names all_user_tables EXPORT.Wildcard -sn schema-names all_schemas EXPORT.Wildcard -ts tblspace-names all_tablespace EXPORT.Wildcard -tf tables from file -io import-option REPLACE_CREATE IMPORT_only. -lo load-option INSERT LOAD_only. -l lobpaths current_dir separated_by_commas.NO -u userid logged_on_userid -p password logged_on_password -aw allow-warnings false include warnings during export. 例如： Db2move ehrdb export -u ehr -p ehr Db2move ehrdb import -u her -p ehr IMPORT如何避免日志满出错 在使用IMPORT命令向数据库出入数据时,如何避免日志空间满错误? 在执行IMPORT命令时, 如果使用循环日志, 有时会出现日志满错误, 这时可用COMMITCOUNT参数来解决. 因为日志空间满往往是因为所有的日志均处于活动状态导致的. 而COMMIT执行后, 会释放所占据的资源, 其中包括日志 . 这样, 被当前事务使用的日志在COMMIT命令执行后, 即变成非活动状态了. 如何处理日值 －日志处理 DB2日志是以文件的形式存放在文件系统中，分为两种模式：循环日志和归档日志。当创建新数据库时，日志的缺省模式是循环日志。在这种模式下，只能实现数据库的脱机备份和恢复。如果要实现联机备份和恢复，必须设为归档日志模式。 目前在综合业务系统中，设置的均是归档日志模式；其它系统（如事后监督、经营决策、中间业务等）一般都设置为循环日志模式。至于采用何种模式，可以通过修改数据库配置参数（LOGRETAIN）来实现： 归档日志模式：db2 update db cfg for using logretain on 注：改为on后，查看数据库配置参数logretain的值时，实际显示的是recovery。改变此参数后，再次连接数据库会显示数据库处于备份暂挂（BACKUP PENDING）状态。这时，需要做一次对数据库的脱机备份（db2 backup db ），才能使数据库状态变为正常。 循环日志模式：db2 update db cfg for using logretain off －处理步骤 必须按照以下正确的步骤进行操作： 要求必须使用DB2命令PRUNE进行清理，不建议使用rm命令删除。 删除前应保证应用已停止（即联机已下来）。 查看当前使用的日志文件目录及第一活动日志文件 用 "db2 get db cfg for "命令查看日志文件目录（Path to log files）参数，确定数据库当前使用的日志文件目录。 例如：Path to log files = /db2log/，说明DB2日志存放目录是/db2log 用 "db2 get db cfg for "命令查看第一活动日志文件（First active log file）参数，该参数对应的日志文件之前的日志文件均为归档日志文件，如果确认没有用，可以删除。例如：First active log file = S0015913.LOG，说明当前第一活动日志文件是S0015913.LOG。 备份好要删除的归档日志 删除归档日志 以应用用户（如BTP）登录，执行： $ db2 connect to $ db2 prune logfile prior to S???????.LOG 注：S???????.LOG为查看到的第一活动日志文件。此命令可以将当前第一活动日志文件之前的归档日志文件全部删除。 如何清理db2diag.log文件 db2diag.log，是用来记录DB2数据库运行中的信息的文件。可以通过此文件，查看记录的有关DB2数据库详细的错误信息。此文件也是不断增大的，需要定期进行清理。 可以通过查看实例的配置参数DIAGPATH，来确定db2diag.log文件是放在哪个目录下：db2 get dbm cfg 如果Diagnostic data directory path(DIAGPATH) = /home/db2inst1/sqllib/db2dump，则此文件是放在/home/db2inst1/sqllib/db2dump目录下。当文件系统/home的使用率达到80%－90%左右时，应及时删除db2diag.log文件。 请按以下正确步骤操作：确认应用（如BTP）、DB2已经停止。 将原db2diag.log文件备份到其它文件系统下。 删除db2diag.log文件。删除后，DB2会自动创建一个新的文件。 标准函数 length: 返回自变量中的字节数 CAST: 变量类型转换或截断字符串 如：CAST(RESUME AS VARCHAR(370)) Select CAST(colname as integer) from tablename; decimal: 变量转换为指定精度的数值 如: select decimal(amount,16,2) from tablename; WHERE 子句条件 谓词 功能 x = y x 等于 y x y x 不等于 y x < y x 小于 y x >

Y

X is greater than y

X = y

X is greater than or equal to y

IS NULL/IS NOT NULL

Test null value

Get system date or system time

Select current time into curtime from (values 1) as tmp

Select current date into curdate from (values 2) as tmp

Select year (current date) into curdate from (values 2) as tmp;-- get the system year

Select month (current date) into curdate from (values 2) as tmp;-- get the system month

Select day (current date) into curdate from (values 2) as tmp;-- get system days

(CURRENT TIMESTAMP accuracy reaches microseconds)

How to prevent null value

DB2's COALESCE function returns the first non-empty expression in the expression list in (), which can take multiple expressions, similar to oracle's isnull. The syntax format is as follows:

CLEASCE (colname,default_value)

The method of Associated updating of multiple tables

Db2's update syntax does not support "update table1 set t1.col1=t2.value1 from table1 t1jib Table2 T2 where..." But it can be solved by the following methods:

Update table1 T1 set T1. Col1 = (select t2.col1 from table2 T2 where …)

Example:

Update test T1 set (t1.username from sysinsttb t1.instcode) = (select t2.instcoderect t2.instname from sysinsttb T2 code)

Get the number of records for the operation (insert, update)

GET DIAGNOSTICS rcount=ROW_COUNT

Note:

Get diagnostics rcount = ROW_COUNT

It only works on update,insert,delete.

Not valid for select into

How to execute optimization commands such as RUNSTATS

Db2 runstats on table with distribution and detailed indexes all

Db2 reorgchk update statistics on table all

Check when runstats has been carried out

Db2 "select name, stats_time from sysibm.systables"

The full implementation is as follows:

Db2 connect to ocrm1 user db2iocrm using db2iocrm

Db2 runstats on table db2iocrm.eosoperator with distribution and indexes all

Db2 reorg table db2iocrm.eosoperator allow read access

Db2 reorg indexes all for table db2iocrm.eosoperator allow read access

Db2 connect reset

How to get the first N rows of data in the result set

Select * from tablename fetch first N rows only

Install DB2 defaults?

The default instance in WINDOWS or OS/2 is DB2

In LINUX or UNIX environment, the default instance is DB2INST1.

The default account in WINDOWS or OS/2 is DB2ADMIN

DB2AS is the default account in LINUX or UNIX environment

How to define a sequence

CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24

For example:

Create sequence pk_only_empid

Select NEXTVAL FOR pk_only_empid from (values 1) as tmp

How to close the log of a table

ALTER TABLE TABLE_NAME ACTIVE NOT LOGGED INIALLY

How to get the SQL execution plan

SQL interpretation tool

The SQL interpretation tool provides details about the access plan selected by the query optimizer for the SQL statement. This information is stored in the EXPLAIN table and can be formatted later using tools such as Visual Explain, db2expln, dynexpln, and db2exfmt to represent it in a friendly visual manner.

The EXPLAIN table can be created automatically the first time you use Visual Explain. Even if you don't create them, you can create them manually, as follows:

% cd\ sqllib\ misc

% db2 connect to bank

% db2-tvf EXPLAIN.DDL

In this article, we use the db2exfmt tool. For example, using db2exfmt to interpret dynamic SQL statements, follow these steps in the DB2 command window:

% db2 connect to

% db2 set current explain mode explain

% db2-tvf

% db2 set current explain mode no

% db2exfmt-d-g TIC-w-1-n%-s%-# 0-o

How do I create a case?

DB2ICRT INSTNAME

If it is a client, add the client keyword

For example:

1) Log in with root and use the command to create a user db2inst1 for smitty for database management.

2) Log in with root, create an instance DB2 instance using db2icrt, and use the following command.

Cd / usr/opt/db2*/instance/

. / db2icrt-s client db2inst1

3) Log in with the db2inst1 user, create the local node directory of the database, and establish a remote database mapping alias, using the following command.

Cd / usr/opt/db2*/bin

Db2 catalog tcpip node wmsint remote 182.247.70.94 server 60000

Db2 catalog database wmsint as wmsint at node wmsint

Db2set db2codepage=1386

Db2 terminate

4) to enable root users to use the db2 command, add the following text from the .profile file under home/db2inst1 to the .profile in the root directory.

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.

Export PATH

# The following three lines have been added by UDB DB2.

If [- f / home/db2inst1/sqllib/db2profile]; then

. / home/db2inst1/sqllib/db2profile

Fi

5) log out of root users and log in again

Writing skills of SQL

How to use in/not in without using the select clause in multiple fields

Select * from tabschema.tabname where (colA, colB, colC) [not] in (values (valueA1, valueB1, valueC1), (valueA2, valueB2, valueC2),... (valueAn, valueBn, valueCn))

Update tablenameA t1

Set (colA,colB,colC) = (selelct b.colA from tablenameB b.colBrect b.colC from tablenameB T2 where t1.key = t2.key)

Use partial command

List all instance DB2ILIST

Get the current instance GET INSTANCE

Update the configuration DB2IUPDT of the case

Delete case DB2IDROP INSTANCE_NAME

The specific steps are as follows:

Stop all applications on the case

Execute DB2 TERMINATE on all open command lines

Run DB2STOP

Back up the case directory indicated by the DB2INSTPROF registration variable

Withdraw from the case of all landers

Use DB2IDROP

You can also delete ID

List all products with license information on the local system DB2LICM-L

Add a product license DB2LICM-A FILENAME

Delete a license for a product DB2LICM-R PRODUCT PASSWORD

Update the number of licenses purchased DB2LICM-U

Force only the quantity already purchased to be used DB2LICM-E HARD

Update the license policy type DB2LICM-P REGISTERED CONCURRENT used on the system

Update the number of processors on the system DB2LICM-N

Query the version information recorded in the license file DB2LICM-V

Query DB2LICM help information DB2LICM-H

II. Stored procedures

How to declare a stored procedure

CREATE PROCEDURE stored procedure name (IN input variable name input variable type, OUT output variable name output variable type)

This is followed by a list of stored procedure properties

Commonly used are: LANGUAGE SQL, MODIFIES SQL DATA, RESULT SETS 1 (the number of result sets returned)

The body of a stored procedure starts with begin

L stored procedure body ends with end

Stored procedure constraint rules

Calling a stored procedure in a stored procedure

CALL stored procedure name (parameter 1, parameter 2, parameter n)

Example:

Call spco_init_custom (bankcode,errno,errmsg)

GET DIAGNOSTICS retval=RETURN_STATUS

If (retval0) then

Set errno=errno

Set errmsg=errmsg

Return errno

End if

Definition of variables

Variables must be defined before they can be used. The method is

DECLARE variable name variable type (default default)

Example:

DECLARE SQLCODE INTEGER DEFAULT 0

DECLARE inum INTEGER DEFAULT 0

DECLARE curtime char (8)

DECLARE bcode char (6)

DECLARE sqlstate char (5)

If expression

If condition 1 then

Logical body

Elseif condition 2 then

Logical body

Else

Logical body

End if

Example:

IF rating = 1 THEN

UPDATE employee

SET salary = salary * 1.10, bonus = 1000

WHERE empno = employee_number

ELSEIF rating = 2 THEN

UPDATE employee

SET salary = salary * 1.05, bonus = 500

WHERE empno = employee_number

ELSE

UPDATE employee

SET salary = salary * 1.03, bonus = 0

WHERE empno = employee_number

END IF

Case expression

Case variable name when

Variable value 1 then

. . .

When

Variable value 2 then

Else

. . .

End case

Or

Case when

Variable name = variable value 1 then

. . .

When

Variable name = variable value 2 then

Else

. . .

End case

Example 1:

CASE v_workdept

WHEN 'A00'

THEN UPDATE department

SET deptname = 'DATA ACCESS 1'

WHEN 'B01'

THEN UPDATE department

SET deptname = 'DATA ACCESS 2'

ELSE UPDATE department

SET deptname = 'DATA ACCESS 3'

END CASE

Example 2:

CASE

WHEN v_workdept = 'A00'

THEN UPDATE department

SET deptname = 'DATA ACCESS 1'

WHEN v_workdept = 'B01'

THEN UPDATE department

SET deptname = 'DATA ACCESS 2'

ELSE UPDATE department

SET deptname = 'DATA ACCESS 3'

END CASE

For expression

For loop name as

Cursor name or select expression

Do

Sql expression

End for

Example:

1)

DECLARE fullname CHAR (40)

FOR vl AS

SELECT firstnme, midinit, lastname FROM employee

DO

SET fullname = lastname | |','| | firstnme | |''| | midinit

INSERT INTO tnames VALUE (fullname)

END FOR

2)

For loopcs1 as cousor1 cursor as

Select market_code as market_code

From tb_market_code

For update

Do

End for

Goto expression

Goto tag name

Mark name:

Logical body

Example:

GOTO FAIL

...

SUCCESS: RETURN 0

FAIL: RETURN-200

While expression

While conditional expression do

Logical body

End while

LOOP expression

LOOP... END LOOP

Example:

OPEN c1

Ins_loop:

LOOP

FETCH c1 INTO v_dept, v_deptname, v_admdept

IF at_end = 1 THEN

LEAVEins_loop;-break cycle

ELSEIF v_dept = 'D11' THEN

ITERATEins_loop;-the next cycle

END IF

INSERT INTO department (deptno, deptname, admrdept)

VALUES ('NEW', v_deptname, v_admdept)

END LOOP

CLOSE c1

About cursors

Define cursors:

DECLARE cursor name CURSOR FOR

Select statement

Open the cursor:

OPEN cursor name

Value:

FETCH cursor name INTO variable list

Example:

DECLARE c1 CURSOR FOR

SELECT CAST (salary AS DOUBLE)

FROM staff

WHERE DEPT = deptNumber

ORDER BY salary

DECLARE EXIT HANDLER FOR NOT FOUND

SET medianSalary = 6666

SET medianSalary = 0

SELECT COUNT (*) INTO v_numRecords

FROM staff

WHERE DEPT = deptNumber

OPEN c1

WHILE v_counter < (v_numRecords / 2 + 1) DO

FETCH c1 INTO medianSalary

SET v_counter = v_counter + 1

END WHILE

CLOSE c1

Note: if the cursor statement is placed in the middle section, use "begin... end;". Segment segmentation flag split

Dynamic sql

1) declare stmt varchar (1024)

Set stmt='create table zhouhaiming (F1 smallint, f2 varchar (9), f3 char (5))'

Prepare s1 from stmt

Execute s1

Set stmt='insert into zhouhaiming values (1 recording www recording recording AA')'

Prepare s1 from stmt

Execute s1

2) DECLARE CURSOR C1 FOR STMT1

PREPARE STMT1 FROM

'ALLOCATE C2 CURSOR FOR RESULT SET?'

Establishment of temporary table

DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME

AS (FULLSELECT) DEFINITION ONLY

EXCLUDING IDENTITY COLUMN ATTRIBUTES

ON COMMIT DELETE ROWS

NOT LOGGED IN temporary tablespace name with replace

The first line specifies the name of the time table.

The second row specifies the definition of the columns of the timing table.

The third row specifies that it is not an identity column in the definition of the source results table.

The fourth row states that all rows in the table will be deleted if WITH GOLD lighting is not turned on.

The fifth line specifies that changes to the table are not recorded.

The With replace option implicitly automatically deletes the temporary table.

For example:

DECLARE GLOBAL TEMPORARY TABLE DEC_BSEMPMS

AS (SELECT * FROM BSEMPMS) DEFINITION ONLY

EXCLUDING IDENTITY COLUMN ATTRIBUTES

ON COMMIT DELETE ROWS

NOT LOGGED

Several global variables in DB2

N ROW_COUNT- affects number of rows

UPDATE CORPDATA.PROJECT

SET PRSTAFF = PRSTAFF + 1.5

WHERE DEPTNO = deptnbr

GET DIAGNOSTICSrcount = ROW_COUNT

N RETURN_STATUS-- returns status

CALL TRYIT;-- calls stored procedures

GET DIAGNOSTICSRETVAL = RETURN_STATUS

IF RETVAL 0 THEN

...

LEAVE A1

ELSE

...

END IF

N SQLSTATE-SQL returns error code

Note: it must be defined before use.

Declare sqlstate char (5)

Declare state char (5)

Insert into tbname values (…)

Set state=sqlstate

If (state '00000') then

Return-1

End if

About ATOMIC and NOT ATOMIC

Transactions in the P1:BEGIN ATOMIC-P1 segment are automatically rolled back

Transactions in the P1:BEGIN NOT ATOMIC-P1 segment are not automatically rolled back

Conditional handle in DB2

Handle type:

N CONTINUE

N EXIT

N UNDO

Condition type:

N SQLSTATE string

N SQLEXCEPTION

N SQLWARNING

N NOT FOUND

Example:

1) DECLARE EXIT HANDLER FOR NOT FOUND

SET medianSalary = 6666

2) DECLARE not_found CONDITION FOR SQLSTATE '02000'

DECLARE EXIT HANDLER FOR not_found

SET rating =-1

3) DECLARE not_found CONDITION FOR SQLSTATE '02000'

DECLARE c1 CURSOR FOR

SELECT deptno, deptname, admrdept

FROM department

ORDER BY deptno

DECLARE CONTINUE HANDLER FOR not_found

SET at_end = 1

How to extract / submit stored procedures

Db2 "get routine into file name from procedure stored procedure name"

Decimation stored procedure

Submit stored procedure

Db2 "put routine from File name"

Install the compiled stored procedure.

How to submit a stored procedure from a command line

Add the @ symbol to the end of the stored procedure and type: db2-td@-vf procfile.sql at the command line to generate the procedure.

Non-stored procedure SQL file, type: db2-tvf sqlfile.sql at the command line

The use of returning a result set (cursor) from a stored procedure

1. Create a sp to return the result set

CREATE PROCEDURE DB2INST1.Proc1 (

LANGUAGE SQL

Result sets 2-- (returns two result sets)

P1: BEGIN

Declare c1 cursor with return to caller for

Select market_code

From tb_market_code

Specifies that the result set is used to return to the caller

Declare c2 cursor with return to caller for

Select market_code

From tb_market_code

Open c1

Open c2

END P1

2. Build a SP to call the sp and use its result set

CREATE PROCEDURE DB2INST1.Proc2 (

Out out_market_code char (1))

LANGUAGE SQL

P1: BEGIN

Declare loc1,loc2 result_set_locator varying

Create an array of result sets

Call proc1

Call the SP to return the result set.

Associate result set locator (loc1,loc2) with procedure proc1

Associate the returned result set with the result set array

Allocate cursor1 cursor for result set loc1

Allocate cursor2 cursor for result set loc2

-- assign the result set array to cursor

Fetch cursor1 into out_market_code

-- assign values directly from the result set

Close cursor1

END P1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.