1 dB2
Program
1.1
When you build the storage process, you must do not use the Tab key 3 after CREATE.
1.2
Use temporary table 3
1.3
Take the first few records from the data sheet 3
1.4
Use of cursors 4
Note Commit and Rollback 4
Two definitions of cursors 4
Modify the current record of the cursor 5
1.5
Similar to Decode transcoding operation 5
1.6
Location 5 Location 5 in the string 5 similar to Charindex
1.7
Similar to DatediF calculation of different days of different days 5
1.8
Example 5 written by UDF
1.9
Table 6 with an identity value (ie, an id)
1.10
Processing of null value of fields 6
1.11
The number of recorded records 6 1.12 Returns the usage set (cursor) from the stored procedure 6
1.13
Type conversion function 8
1.14
Mutual call 8
1.15 C
Storage process parameters pay attention to 8
1.16
Storage procedure fence and unfence 8
1.17 SP
Error processing usage 9
1.18 import
Usage 9
1.19 VALUES
Use 9
1.20
Specify the isolation level 10 for the SELECT statement 10
1.21 Atomic
NOT Atomic Difference 10
2 dB2
Programming performance attention 10
2.1
Big data guide 10
2.2 SQL
Statement to write complex SQL 10 as much as possible
2.3 SQL SP
And C SP selection 10
2.4
Optimization of queries (Hash and RR_TO_RS) 11
2.5
Avoid using COUNT (*) and EXISTS 11
3 db2
Table and SP management 12
3.1
See the memory of the process 12
3.2
See the table structure 12 3.3 View the impact of the SP (which SP usage) 12 3.4 View which tables have been used 12 3.5 View which SPs are used by 12 3.6 Modify Table Structure 12 4 DB2 System Management 13 4.1 DB2 Installation 13 4.2 Create a Database 14 4.3 Handmade Database Remote (Alias) Configuration 14 4.4 Stop Launch Database Speed 14 4.5 Connecting Databases and See Current Connection Database 14 4.6 Stop Launch Database HEAD 15 4.7 Viewing and Stop Database Current Applications 15 4.8 Viewbook Instance Which Database 15 4.9 View and change database HEAD Configuration 16 4.9.1 Rigading Pile of Size 16 4.9.2 Size 16 4.9.3 Modify Program Stack Memory Size 16 4.10 View and Change Database Configuration 16 4.10.1 Opens the monitoring of the lock. 16 4.10.2 Changing Diagnostic Error Capture Level 17 4.11 DB2 Environment Variable 17 4.12 DB2 Command Environment Settings 17 4.13 Changing Isolation Level 17 4.14 Management DB / Instance Parameter 18 4.15 Upgrade Removal Version Question 18 4.16 View Database Table Dead Lock 18
1 dB2
Program
1.1
Don't use the Tab key after Create when Create is built
CREATE Procedure
After CREATE, you can only use spaces, not TAB health, otherwise compile will pass. Remember, remember. 1.2 Use the temporary table to note that the temporary table can only be built on the User Tempory Tables Space, if the Database only SYSTEM TEMPORY TABLE SPACE cannot build a temporary table. In addition, the Temporary table of DB2 and Sybase and Oracle are not quite, the Temporary table of DB2 is effective in one session. So, if the program has multiple threads, it is best not to use a temporary table, it is difficult to control. When you build a temporary table, you'd better add the with replace option, which can not display the DROP temporary table, if this option is not added, the temporary table is created in the session, and there is an error. . 1.3 Take the first few records from the data table SELECT * from TB_MARKET_CODE FETCH FIRST 1 ROWS ONLY, but below does not allow SELECT Market_code inst_market_code from TB_MARKET_CODE FETCH FIRST 1 ROWS ONLY; select the first record to a variable the following in lieu declare v_market_code char (1); declare cursor1 cursor for select market_code from tb_market_code fetch first 1 rows only for update; open cursor1; fetch cursor1 into v_market_code; close cursor1; special 1.4 cursor using Note commit and rollback use cursors Note If you don't add a Hold option, when commits and rollback, the cursor will be turned off. Commit and Rollback have a lot of things to pay attention. Two definitions careful manner a cursor to declare continue handler for not found begin set v_notfound = 1; end; declare cursor1 cursor with hold for select market_code from tb_market_code for update; open cursor1; set v_notfound = 0; fetch cursor1 into v_market_code WHILE V_NOTFOUND = 0 do --Work set v_notfound = 0; Fetch Cursor1 INTO V_MARKET_CODE; End While; Close Cursor1; This method is more complicated, but it is also more flexible. In particular, you can use the Hold option. If there is a COMMIT or ROLLBACK in the loop to keep the Cursor is not closed, you can only use this way.
Another PCURSOR1: for loopcs1 as coursor1 curusor as select marke_code as market_code from TB_MARKET_CODE for Update Do End for; this way is relatively simple, using Open, Fetch, Close without (nor allowed). But you can't use the with all option. If you want to use COMMIT in the cursor cycle, Rollback cannot use this way. If there is no requirements for commit or rollback, it is recommended to use this way (it seems that the way is problematic). Modified cursor method of the current record update tb_market_code set market_code = '0' where current of cursor1; However, to ⒁ ravioli Fu ursor1 defined as modifiable cursor declare cursor1 cursor for select market_code from tb_market_code for update; for update can not GROUP BY, DistINCT, ORDER BY, For Read Only and Union, Except, or InterSect But union ALL except. 1.5 Similar to Decode Transcoding Oracle has a function Select Decode (A1, '1', 'N1', '2', 'N2', 'N3') AA1 from DB2 without this function, but can be used SELECT CASE A1 WHEN '1' TEN 'N1' WHEN '2' THEN 'N2' Else 'N3' End AA1 from 1.6 Similar CHARINDEX Find Characters in the String Locate ('Y', 'DFDASFAY') Find ' Y 'in' DFDASFAY '. 1.7 Similar to DateDIF calculation of the number of days of different days DAYS (DATE ('2001-06-05')) - days (Date ('2001-04-01')) DAYS returns to start from 0001-01-01 Day 1.8 Write UDF Write SQLLIB / Samples / CLI / UDFSRV.C 1.9 Creating a Table Test for Table Table Test (T1 Smallint Not Null Generated ALWAYS AS IDENTITY) (Start With 500, Increment By 1), T2 Char (1)); only one Identity's column.1.10 is allowed in a table
The processing of the prevention field null value Select Deptno, DePtName, Coalesce (MGRNO, 'Absent'), the AdmrDept from Department Coalesce function returns the first expression in the expression list, can bring multiple expressions. Similar to Oracle's Isnull, but Isnull seems to be two expressions. 1.11 acquired the number of records declare v_count int; UPDATE TB_TEST SET T1 = '0' WHERE T2 = '2'; - Check the number of rows of modifications, determine if the specified record is available with Get Diagnostics v_ count = row_count; only UPDATE, INSERT, DELETE works. Do not valid for SELECT INTO 1.12 Return the result set (cursor) from the stored procedure 1, build a SP return result set Create Procedure DB2Inst1.proc1 () Language SQL Result Sets 2 (Return two Results Set) -------------------------------------------------- --------------------- - SQL Store ------------------------ ----------------------------------------------- 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, Jian A SP Tune the SP and uses its result set Create Procedure DB2Inst1.proc2 (out out_market_code char (1)) Language SQL ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------ S QL stored procedure ------------------------------------------------- ------------------------- P1: Begin Declare Loc1, Loc2 Result_set_locator varying; - Create a result set number of Call Proc1; - call the SP Returns the result set.
Associate Result Set Locator (LOC1, LOC2) WITH Procedure Proc1; - Associate the return result set and result set groups Allocate Cursor1 Cursor for Result Set Loc1; Allocate Cursor2 Cursor for Result Set Loc2; - Assign the result set group to Cursor Fetch CURSOR1 INTO OUT_MARKET_CODE; - Directly assigning close cursor1; END P1 3, dynamic SQL WIRECLARE CURSOR C1 for Stmt1; Prepare Stmt1 from 'Allocate C2 CURSOR for Result Set?'; 4, Note: First, if a SP call Several times, I can only take the result set of the last call. Second, allocate's Cursor cannot open again, but you can close, it is the corresponding Cursor in Close SP. 1.13 Type Conversion Function SELECT CAST (CURRENT TIME AS CHAR (8)) from TB_MARKET_CODE 1.14 The mutual call of the stored procedure is currently called each other. SQL SP can be called each other, and SQL SP can call C SP, but C spp can not call SQL SP (the latest statement) 1.15 C Storage process parameters Note Create Procedure Pr_task_ctrl (in in_branch_code char (4), in, in in_tradedate char (8 ), In-in_task_id char (2), in / set_task_id char (4), out out_success_flag integer) Dynamic Result sets 0 Language C Parameter Style General With NULLS (if not, SQL SP will not call the stored procedure written by C. Protective errors) No dbinfo fengt modifies SQL DATA EXTERNAL NAME'PR_CLEAR_TASK_CTRL !PR_CLEAR_TASK_CTRL'@1.16
The stored procedure of the stored procedure, and Unfence Fence enables a new address space, while the Unfence stored procedure and the process of calling it uses the same address space. In general, the stored procedure of Fence is more secure. But sometimes some special requirements, if you want to take the PID of the adjuster, the store process of the fence can not be removed, and only Unfence can be taken. 1.17 SP Error Process Usage If you call other return values, including the result set, temporary table, and output parameter type SP, DB2 will automatically send a SQLWarning. In our original processing, it is inserted into the log for SQLWarning, so that there will be a warning message for multiple SQLCode = 0. Process: Define a logo variable, such as Declare v_status integer default 0, after Call SPNAME, set v_status = 1, declare continue handler for sqlwarning begin if v_status <> 1 THEN - warning processing, insert log set v_status = 0; end; IF; end; 1.18 Import Usage DB2 Import from GH1.out of Del Messages Err.txt Insert INTO DB2IST1.TB_DBF_MATCH_HA Note To add SCHMA 1.19 Values If there are multiple set statements to pay the variable, it is best to use the VALUES statement, change For a sentence. This can improve efficiency. But note that Values cannot pay NULL values to a variable. VALUES (NULL) INTO OUT_RETURN_CODE; this statement will report an error. 1.20 Specify the isolation level to the SELECT statement Select * from TB_HEAD_STOTOCK_BALANCE WITH UR 1.21 Atomic and NOTOMIC Difference Atomic is specified as a whole, where any of the statements fails, the entire block is equivalent to nothing, including it The successful statement that has been implemented in the Atomic block is also quite done, it is a bit similar to Transaction. 2 DB2 Programming Performance Note 2.1 Data ID should be export, then LOAD performance is better, because LOAD does not write logs. It is better than Select Into. 3.4
See Table sp used which select bname from syscat.packagedep where btype = 'T' and pkgname in (select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname = 'PR_CLEAR_MATCH_DIVIDE_SHA')) 3.5 View function is What use sp select PROCNAME from SYSCAT.PROCEDURES where SPECIFICNAME in (select dname from sysibm.sysdependencies where bname in (select PKGNAME from syscat.packagedep where bname in (select SPECIFICNAME from SYSCAT.functions where funcname = 'GET_CURRENT_DATE'))) using the function Be careful, if you want DROP, the Function must first call all other stored procedures of the Function all DROP. You must create a FUNCTION, calling the Function SP to create success.
3.6 Modifying the table structure One time to add multiple fields DB2 "Alter Table TB_TEST ADD Column T1 Char (1) Add Column T2 Char (2) Add Column T3 Int" 4 DB2 System Management 4.1 DB2 Installation DB2 7.1 installation in Windows 98 DB2 7.1 Or other versions, if there is a JDBC error or WindWos 98 can't start, use the contents of AutoExec.Bat to replace: c: /progra ~ 1/trendp ~ 1/pcscan.exe C: / C: / Windows / Command / / NS / Win95 Rem C: /Windows/command.com / E: 32768 Rem [Header] Rem [CD-ROM Drive] Rem [misplacellaneous] Rem [Display] set path =% PATH%; C: / MSSQL / BINN; C: / Progra ~ 1 / SQLLIB / BIN; C: / Progra ~ 1 / Sqllib / Function; C: / Progra ~ 1 / SQLLIB / Samples / Repl; C: / Program ~ 1 / SqlliB / Help If EXIST C : /Progra ~ 1/ibm/imnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnq/imqenv.bat cavernnnnnnnnnnq/imnenv.bat call c: / progra ~ 1 / IBM / IMNNQ / IMNENV.BAT SET DB2INSTANCE = DB2 set classpath = .; c: /program 11/sqllib/java/db2java.zip; c: /progra ~1/sqllib/java/runtime.zip; C: /Progra ~1/sqllib/java/sqlj.zip;c :/progra ~ 1/sqllib/bin set mdis_profile = c: / progra ~ 1 / SQLLIB / METADATA / PROFILES SET LC_ALL = zh_cn set include = c: / progra ~ 1 / SQLLIB / INCLUDE; C: / Progra ~ 1 / SQLLIB / LIB; C: / Progra ~ 1 / SQLLIB / Templates / include set lib = c: / progra ~ 1 / sqllib / lib set db2path = c: / progra ~ 1 / sqllib set db2tempdir = c: / progra ~ 1 / sqllib set vws_templates = C: / Program ~ 1 / SQLLIB / Templates set vws_logging = c: / progra ~ 1 / sqllib / logging set vWSPATH = C: / progra ~ 1 / sqllib set vWS_folder = IBM DB2 SET ICM_FOLDER = Information Directory Manager 4.2
Create a Database Create Database Head Using Code IBM-EUCCN TERRITORY CN; this can support Chinese. 4.3 Handmade Database Remote (alias) Configuration DB2 Catalog Tcpip Node Node1 Remote 172.28.200.200 Server 50000 DB2 Catalog DB Head As Test1 At Node Node1 can then be used: DB2 Connect To Test1 User ... Using ... Even the Head library 4.4 stop start Database instance DB2START DB2STOP (FORCE) 4.5 Connection Database Database DB2 Connect To Head User DB2Inst1 Using DB2Inst1 Current Connection Database DB2 Connect 4.6 Stop Start Launch Database Head DB2 Activate DB Head DB2 Deactivate DB Head To note, if there is Connect, use deActivate DB. If it is a database that is started with Activate DB, you must use Deactivate DB to stop the database. (Of course, if DB2STOP will stop). Using Activate DB, this can reduce the waiting time in the first connection. Database is started by connecting the database using Activate DB to start by connecting the database, the DB will automatically stop when all the connections exit. 4.7 Viewing and Stop Database Current Application View Application: DB2 List Applications Show Detail Authorization Identity | App Program Name | Application Handle | Application Identification | Serial # | Agent | Coordination Program | Status | Status Change Time | DB Name | DB path | | Node number | PID / thread where: 1, the first part of the application identifier is the IP address of the application, but it has been 16-based. 2, the PID / thread is the thread number seen under Unix. Stop Application: DB2 "Force Application (236)" DB2 "Force Application All" where: This 236 is the application handle. 4.8
View this type of Database DB2 List Database Directory [ON / HOME / DB2INST1] 4.9 View and change database HEAD configuration Please note that in most cases, after changing the configuration of the data, only all connections are all broken. Later, it will take effect. View Database Head Formulation DB2 GET DB CFG for Head Change Database Head's Settings 4.9.1 Remove the Size DB CFG for Head Use SortHEAP 2048 to change the size of the sorting stack to 2048 pages, query comparison Many applications are best to set this value. 4.9.2 Size of the Recovery Log DB2 Update DB CFG for Head Uses Update DB CFG for Head Using Logfilsiz 40000 The size of this content should be adapted to the data processing of the database, if the thing is relatively large, you should change this value. Otherwise it is easy to handle the log file full error. 4.9.3 When the program stack memory is insufficient, the program stack memory size DB2 UPDATE DB CFG for Head useing ApplHeapsz 40000 This value cannot be too small, otherwise there will be no sufficient memory to run the application. 4.10 Viewing and changing the configuration of the database instance View Database Instance Configuration DB2 Get DBM CFG Change Database Instance Formulation 4.10.1 Open Monitoring of Locks. DB2 Update DBM CFG Using DFT_MON_LOCK ON 4.10.2 Change Diagnostic Error Capture Level DB2 Update DBM CFG Using Diaglevel 3 0 For Not Record Information 1 For Record Errors 2 Record Service and Non-Service Error Default is 3, record DB2 errors and warning 4 is to record all information, including information successfully executed, please do not use 4, which will cause the DB2 running very slow. 4.11 DB2
