The author has been working on the development and management of the database! Not long ago, just completed the Sybase database to work to the DB2 database!
Requirements for friends and some classmates! I want to introduce some of the strategies and methods of database migration!
At present, there is less information on online database migration! I am experiencing technical difficulties and solutions in my actual work, brief introduction! I hope that friends who want to migrate the database and don't know how to get started! This article lists 51 actual issues and solutions! Very pay great attention to actual!
1. Sybase data types and DB2 have those differences?
A: DateTime corresponds to TimeStamp
Tinyint, Smallint Corresponding to Smallint
Money type corresponds to Numeric (19, 4)
2. Does the DB2 database support self-added column settings?
A: Support, for example: sysid bigint generated always as identity
Change the original SYSID Numeric (18, 0) to a Bigint type
3. Does DB2 have a Convert () function, how does Sybase transitions under Sybase?
A: Convert (DateTime, Convert (Char (8), DateAdd (DAY, -1, Getdate ()), 112))
There is no Convert () function in DB2, and the following functions can be used with respect to the conversion
Char (), timestamp (), date (), Time () ... You can also use system variables within the system
Current TimeStamp, Current Date, Current Time Represents System Current Date Time
If the above can be converted like this
TimeStamp (current Date -1 Days, Time ('00.00.00 ')))
4. How to convert Sybase's ISNULL (@ VC_PICI_ID, '0')?
Answer: Use value (vc_pici_id, '0') or coalesce (VC_PICI_ID, '0') or Coalesce
5. How do I call the stored procedure and call in DB2 to return to the stored procedure? A: (1) No return parameter calls Call Proc1 (v_empno, v_salary)
(2) Return parameter call
Declare RET_VAR INTEGER DEFAULT 0;
Call Proc1 (var1, var2);
Get Diagnostics Ret_var = RETURN_STATUS;
---------
Declare Err_code integer default 0;
Call proc1 (var1, err_code);
IF err_code = 1 THEN
Xxxx;
END IF;
6. Sybase is very convenient, using sqlcode, sqlstate to control, do not know how DB2 is controlled? A: DB2 downpreet control is not very easy and convenient, you can also use Sqlcode, SqlState, or users yourself, DB2 sqlcode, SQLState can't be used directly, you must declare it, (that is, Sqlcode, SQLState is instantiated by local copy). A method of co-controlling the user-defining a cursor switch and SQLCode return information together.
Example 1: (here illustrate a problem, the cursor switch is bundled with sqlcode. '02000 'is SQLCODE number)
// -------
Example of standard use cursors While do control cursor
// -------
CREATE Procedure Creditp
(In i_perinc decimal (3, 2),
INOUT O_NUMREC DECIMAL (5, 0))
Language SQL
Begin - Here is user management transaction
Declare proc_cusnbr char (5);
Declare Proc_Cuscrd Decimal (11, 2);
Declare NumRec Decimal (5,0);
DECLARE AT_END INT Default 0; - Switch Definition
Declare not_found condition for '02000'; - No data, cursor end definition
Declare Cursor for Select Cusnbr, Cuscrd from OordApplib.customer;
Declare Continue Handler for not_found set at_end = 1; - Define Continue Conditions
Declare exit handler for sqlexception rollback; --sqlcode is not '01''00'02', quit and rollback transactions
Set NumRec = 0;
Open C1;
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
While AT_END = 0 DO
Set proc_cuscrd = proc_cuscrd (Proc_Cuscrd * i_perinc);
SET NUMREC = NumRec 1;
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
End while;
SET O_NUMREC = NUMREC;
CLOSE C1;
Commit; - Submit a transaction
End
Example 2:
- Declaration Cursor C1
Declare CURSOR for
Select Cusnbr, Cuscrd
From ORDAPPLIB.CUSTOMER;
Open C1; - Open the cursor
FETCH C1 INTO PROC_CUSNBR, Proc_Cuscrd; - Get data from the cursor
If SQLSTATE = '02000' Then - Declating whether the cursor has data (none)
Call Data_not_found; - Return call
Else
Do while (SUBSTR (SQLSTATE, 1, 2) = '00' | SUBSTR (SQLSTATE, 1, 2) = '01');
Fetch C1 INTO Proc_cusnbr, Proc_Cuscrd;
......
END IF;
Close C1; - Close the cursor 7. What is the standard format of the DB2 stored procedure? A: DB2 stored procedure is more flexible, but the syntax format is very strict. Basically
Cteate procedure xhzq_db.procname
In Para_1 Type Default Value,
OUT Para_2 Type Default Value)
Begin (user management transaction)
- User-defined variables
Declare Var1 Type Default Value;
- User definition control temporary variable
Declare error_code int default 0; declare at_end int default 0;
- User defined condition control variable
Declare not_found condition for '02000';
- User-defined cursor
Declare CURSOR for SELECT COL from Table_name
- User definition conditions and switch variables association
Declare Continue Handler for not_found set at_end = 1;
- User-defined transaction rollback processing conditions
Declare exit handler for sqlexception rollback;
- Initialization variable straight
SET VAR = 0;
- Judging the entrance parameters
IF (Para_1 Is Null) THEN
SET PARA = 100;
END IF;
- Storage process statement set
Open C1;
FETCH C1 INTO XX, XX2, XX3;
.
CLOSE C1;
- Submit a transaction
Commit Work hold;
SET Para_2 = 0;
Return Para_2;
- Return parameter 0 success
END 8. DB2 CHAR () function Answer: Char (Date, Keyword), Char (Time, Keyword)
Keyword
Date Format
Time Format
USA
07/17/2001
01:48 PM
ISO
2001-07-17
13.48.04
EUR
17.07.2001
13.48.04
JIS
2001-07-17
13.48.04
Select Current Date, Current Time From Sysibm> SysdumMY1;
9. DB2 TimeStamp () Function A: TimeStampdiff (K, Char (TS1 - TS2))
K
Date Part
K
Date Part
256
Years
128
Quarters
64
Months
32
Weeks
16
Days
8
Hours
4
Minutes
2
SECONDS
10. How to make a string combination stitching in DB2? A: Use '||' to handle, concat () functions
For example: Declare VC_SQL_STR VCHAR (100);
Declare vc_where vchar (100);
Declare vc_from vchar (100);
SET VC_FROM = 'from xhzq_db.sys_parameter_tab';
Set vc_where = 'where vc_id is null';
SET VC_SQL_STR = 'SELECT *' || VC_From || VC_where;
