Sybase scripts are ported to DB2

? DB2

Introduction to System tables in DB2

DB2 command line Processor (DB2 CLP) is available in all DB2 products and can be used to run DB2 commands, operating system commands, or SQL statements.

When the DB2 database is created, DB2 will create some system tables. These system tables record information about all database objects, the data type of the columns of the table or view, the definition of the constraint, the permissions of the object, and the dependencies between objects. The schema for these system tables is SYSIBM, with the table name prefixed with SYS. For example: Systables, Sysviews and so on.

DB2 the corresponding read-only views for these system tables. The patterns of these views are SYSCAT, and their contents are all or part of the system tables that correspond to them. The names of these views do not have a SYS prefix. For example: Syscat.tables (is SYSIBM. The Systables view.

We can view all system tables and related view information through the list TABLES for system or the list TABLES for SCHEMA schemaname command. Below we will introduce some of the system tables and views used in this article.

To migrate a table operation:

Where no changes are needed:

• CHAR (30)
• Varchar (30)
• Simple SQL statement itself, select, CREATE TABLE, grant, and so on

Where to modify:

• INT instead: INTEGER
• GO to: Seal number;
• CLUSTER noncluster Modify: Delete directly
• Tinyinteger TinyInt Modified: SMALLINT
• Smallinteger Modified: SMALLINT
• EXEC Modify: Delete directly
• Timestamp Modification: Timestamp (10, 6)
• varchar (+) char (30) Modified: No modification
• Money modification: NUMERIC (19, 4)
• Print Modification: Echo
• Image modification: BLOB (1073741824)
• TEXT modified: CLOB (2147483647)

The Execute command is

The original Sybase SQL script file is as follows:

Stored Procedures

DB2 a special end character @ is required during the process of creating or updating a stored procedure.

connect to oibsdb @? —- 连接到数据库 drop procedure sp_EXPLTTel @???? —-? 删除一个存储过程 create procedure sp_EXPLTTel (??? —- 创建存储过程 ??? IN I_deal_no??????????? char(15),??? —- ??? IN I_goods_services???? varchar(6500), ??? INI_document_required?? varchar(6500), ??? IN I_add_conditions???? varchar(6500) ) LANGUAGE SQL??? —- BEGIN ??? DECLARE v_Debug???? CHAR(1)????? default ‘0’ ; ??? DECLARE s_rtcd????? INTEGER????? DEFAULT 0; ??? DECLARE s_rtst????? CHAR(5)????? DEFAULT ‘00000’ ; ??? DECLARE s_text????? VARCHAR(500) ; END 

DB2 Script Execution Command:

IDE UltraEdit

The process of modification is a laborious physical activity. Some simple grammatical rules are constantly rewritten to improve efficiency. The first is to use UltraEdit's global replacement function to find some globally replaceable elements to do a unified replacement

• Print replaced by Echo
• Go to replace with the seal number;
• Sybase has a C-style annotation for global substitution/* replacement
• int is replaced with integer, Tinyinteger, tinyInt replaced by smallint

The annotation methods supported in DB2 's SQL script are only single-line comments —-。 UltraEdit has a multi-line accelerator annotation feature in Eclipse (which can be set as shortcut key ctrl+shift+c like in Eclipse), but the default annotation method is to add the # number at the beginning of the line, so that we can annotate the useless rows with the # number, The last global substitution is--to reduce the amount of manual annotated work.

Further, sometimes the indentation of some code is not very canonical, you can add two # by using the comment shortcut in the indented place. Then the two # # Global is replaced with four spaces, so that the indentation operation is quickly implemented.

?

Sybase scripts are ported to DB2