31. DB2 loop control statement loop usage?
A: Loop example:
Open C1;
Set at_end = 0;
Set NumRec = 0;
FETCH_LOOP: 1
Loop
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
If SQLCODE = 0 THEN
Set proc_cuscrd = proc_cuscrd * 1.2;
Update Ordapplib.customer
Set cuscrd = proc_cuscrd
WHERE CURRENT OF C1;
SET NUMREC = NumRec 1;
Else
Leave fetch_loop; 2
END IF;
End loop fetch_loop; 3
CLOSE C1;
32. DB2 loop control statement while usage?
A: While Example:
Open C1;
Set at_end = 0;
Set NumRec = 0;
While AT_END = 0 DO
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
If SQLCODE = 0 THEN
Set proc_cuscrd = proc_cuscrd * 1.2;
Update Ordapplib.customer
Set cuscrd = proc_cuscrd
WHERE CURRENT OF C1;
SET NUMREC = NumRec 1;
Else
SET AT_END = 1;
END IF;
End while;
CLOSE C1;
33. DB2 loop control statement REPEAT usage?
A: REPEAT example
Set NumRec = 0;
Fetch_loop:
Repeat
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
If SQLCODE = 0 THEN
Set proc_cuscrd = proc_cuscrd * 1.2;
Update Ordapplib.customer
Set cuscrd = proc_cuscrd
WHERE CURRENT OF C1;
SET NUMREC = NumRec 1;
END IF;
Until SQLCode <> 0
End repeat fetch_loop;
34. DB2 loop control statement for use?
A: For example
For Each_Record AS
Cursor1 CURSOR for
Select Cusnbr, Cuscrd from Ordapplib.customer
DO
Update Ordapplib.customer
Set Cuscrd = Cuscrd * 1.1
WHERE CURRENT OF CURSOR1;
End for;
34. Sybase loop Control Break, Continue in DB2 Usage?
A: Break in DB2 is converted to Leave Lab, Continue Converts to Iterate Lab
Is equivalent to a goto statement
For example, as follows:
=============== Leave example
Open C1;
Set at_end = 0;
Set NumRec = 0;
FETCH_LOOP: 1
Loop
Fetch C1 Into Proc_Cusnbr, Proc_Cuscrd; if Sqlcode = 0 THEN
Set proc_cuscrd = proc_cuscrd * 1.2;
Update Ordapplib.customer
Set cuscrd = proc_cuscrd
WHERE CURRENT OF C1;
SET NUMREC = NumRec 1;
Else
Leave fetch_loop; 2
END IF;
End loop fetch_loop;
CLOSE C1;
============ iprerate example
Begin
Open C1;
INS_LOOP: 1
Loop
Fetch C1 INTO V_DEPT, V_DEPTNAME, V_ADMDEPT;
IF at_end = 1 THEN
Leave INS_LOOP;
Elseif v_dept = 'D11' Then
Iterate INS_LOOP; 2
END IF;
INSERT INTO SAMPLEDB02.DEPTNEW (DePTNO, Deptname, AdmrDept)
VALUES (V_DEPT, V_DEPTNAME, V_ADMDEPT);
End loop;
CLOSE C1;
END;
35. Use scroll cursors (scrollable cursor) during DB2 stored procedures?
A: example
Create Procedure mymax
(In FLD_NAME CHAR (30),
IN file_name char (128),
INOUT MAX_VALUE INTEGER)
Language SQL
Begin Atomic
Declare SQL_STMT Char (256);
Declare not_found
Condition for '02000';
Declare C1 Dynamic Scroll Cursor for S1; - Declaration Dynamic Scrolling Cursor
Declare Continue Handler for Not_Found
Set max_value = null;
Set SQL_STMT = 'SELECT' || FLD_NAME || 'from' || File_Name ||
'ORDER BY 1'; - Combined SQL statement
PREPARE S1 from SQL_STMT;
Open C1;
Fetch Last from C1 Into Max_Value; - Go to the last line
CLOSE C1;
End
Example of the scrolling game: (rpg)
EXEC SQL Begin Declare Section;
Char FLD_NAME [30];
Char file_name [128];
Integer Max_Value;
Short ind3;
Exec SQL End Declare Section;
THEN THE INDICATOR VARIABLE IS Used In The Call Statement:
EXEC SQL
Call mymax (: fld_name,: file_name,: max_value: ind3);
36. Dynamic Cursor is used during storage during DB2?
A: Use prepare, execute, execute immediate statement example:
Create Procedure DYNSQLSAMPLE ()
Language SQL
Begin
Declare Stmt Varchar (256);
Set stmt = 'update employee set salary = salary * 1.1 where Empno =?'; 1
PREPARE S1 from stmt;
INS_LOOP:
For Each_Department AS
C1 Cursor for
Select Mgrno from Department Where Mgrno Is Not Null
DO
EXECUTE S1 Using MGRNO;
End for;
END;
Execute Immediate Statement example:
Prepare S1 from 'Update Employee Set Salary = SALARY * 1.1 Where
Empno in (SELECT DISTINCT MGRNO from Department Where Mgrno is not null);
Execute S1;
Equivalent to
Execute Immediate 'Update Employee Set Salry = SALARY * 1.1 Where
Empno in (SELECT DISTINCT MGRNO from Department Where Mgrno is not null);
The most basic dynamic cursor statement
...
Declare stmt varchar [256];
...
Set Stmt = 'Select Column1, Column2, Column3 from TBL1'
Prepare prepaaredStatement from S1;
Declare Cursor1 Cursor for prepaaredStatement;
...
37. Does the DB2 support the stored procedure for returning results collection?
A: Support, Example 1
Create Procedure getCusName ()
Result sets 1
Language SQL
Begin
Declare CURSOR with RETURN RETURN FOR
Select Cusnam from Customer Order by Cusnam;
Open C1;
SET RESULT SETS CURSOR C1;
End
Example 2
Create Procedure GetRankv4r5
(In Proc_Year Decimal (4,0),
In Proc_Month Decimal (2,0),
INOUT Proc_Rank Integer
Result sets 2 ---- 2 Two Results Set
Language SQL
Begin
...
Declare C1 Dynamic Scroll Cursor for S1;
Declare C2 Dynamic Scroll Cursor for S2;
...
Set Result Sets Cursor C1, CURSOR C2;
End
38. DB2 Database Limit, Column, and Limitations of Variety of Various Variations in Table Spaces
A: The limit of each row of tables in the DB2 database is as follows: [Platform] Windows 9X / NT / 2000, UNIX, Linux [version] 6.x / 7. X Table Space Page Size Table Space Middle Row Length Limit (Bytes) Table Space Most Limits Limitations Space Maximum Row 4K 4005 500 2558K 8101 1012 255
16K 16293 1012 255
32k 32677 1012 255
Note: The table space page is only 4K, 8K, 16K, 32K.
39. Some SQL statements may be very complicated, such as a plurality of tables or triggered a plurality of triggers, when compiling such SQL statements, how to deal with SQL0101N errors
A: For a complex SQL statement, when you call multiple tables or trigger multiple triggers, you may take up a lot of system resources. When SQL0101N errors appear, you first need to confirm that there is no recursive trigger in the system exists. Solve this problem by adding the value of the following parameters: 1) Stmtheap 2) ApplHeapsz 3) PCKCACHESZ
40. How to implement online ¤?
A: Database Establishment Log Mode The default is a cycle log mode (Circular Log), which is not available to online backup. Therefore, I hope to implement online backups, first change the log mode to archive log mode (Archival log).
Take the SAMPLE database as an example to change the configuration parameters of the Sample database in the control center to Recovery, or use DB2 Update DB CFG for Sample Using Logretain on the command line. After changing this parameter, connect the database again to display the database at the backup PENDING state. At this time, you need to make an offline backup of the database. In the Control Center, select the database offline backup or implement it with DB2 Backup DB Samples in the command line. Thereafter, the database can be backed up. You can choose to work online backup in the control center, or implement it with DB2 Backup DB Sample Online in the command line. Note: The relevant log file is also required when the Image file obtained by online backup is recovered.
QQ: 50839655
MSN: DEKKER@hotmail.com
Email: zhangtao @ xhzq.com, dekker99 @ 163.com
