Similarities and differences between DB2 database development and PostgreSQL development

This article mainly introduces some similarities between DB2 database development and PostgreSQL development in actual operations. What's more, I recently used DB2 databases and wrote some differences between them. Not necessarily true. But you can see the difference between the two.

1. Data Definition Language (DDL) and peripheral tools: the difference between DB2 and postgresql is almost 0. The specific difference is:

A. If no special tablespace is set for DB2 varchar, the upper limit is 32 KB. postgresql varchar does not have this limit;

B. Binary Large Object Type blob of DB2, which is replaced by bytea type in postgresql;

C. The clob and long varchar types of DB2 text are replaced by the text type in postgresql;

D. Replace serial or bigserial with the generated by default or generated always key words in DB2. (the biggest difference is that)

E. There is no sequence (sequence object) in DB2, and it is more flexible in postgresql;

F. The reset of the auto-increment primary key value in DB2 requires alter table, while the setval () series functions can be used in postgresql to set the sequence, which is simpler;

G. Spatial Data Types (points, lines, and surfaces) are not supported in DB2 database development, and postgresql supports them;

H. Definitions of transaction isolation levels in DB2 are not compatible with SQL standards, and postgresql is fully compatible with SQL standards;

I. The JDBC type4 driver in DB2 does not support GBK database encoding. postgresql does not have this problem;

J. DB2 database encoding conversion is very funny and complex. postgresql is very simple. You only need to set client_encoding to xxx;

K. Currently, DB2 8.1 does not support UNICODE backend encoding. postgresql supports multiple inner codes;

L. triggers of DB2 8.1 cannot read the triggered data table. postgresql does not have this restriction;

M, DB2 8.1 triggers cannot execute stored procedures, postgresql does not have this limit;

N. postgresql has bit (bit) and bitstring (bit String) types. DB2 does not;

O, the DB2 client DB2/DB2cmd needs to specify the separator to execute the SQL script, for example, @; the postgresql client defaults to a semicolon;

P. The DB2/DB2cmd client developed by the DB2 database does not have the command history function. The postgresql client can edit historical commands;

Q. DB2 cannot install different database versions on the same machine. postgresql can;

R, DB2 has the concept of a federated database, and postgresql does not;

S and DB2 II can replicate heterogeneous databases. postgresql needs to develop its own software;

T, DB2 8.1 does not have a good way to back up the entire database into a text script, postgresql is very easy;

U, DB2 8.1, java stored procedures need to restart the database, postgresql does not need;

V. By default, the SQL commands in DB2/DB2cmd on the DB2 client cannot use line breaks. postgresql can;

W. DB2 has no Chinese documents, and postgresql has complete documents;

X and DB2 have complete error codes. postgresql is not complete yet;

Y, DB2 needs to call the function: select func () from sysibm. sysdummy1, postgresql only needs: select func ();

Z. The stored procedure or function Writing of the returned result set in DB2 is very complex. Additional tablet is required to store the result set. postgresql only adds the return next statement;

A. the stored procedure in DB2 does not have the ROWTYPE/RECORD type concept, which is available in postgresql;

2. Data Operation Language (DML ):

There is almost no difference in basic data operations. The SQL standard function definitions are also identical. However, postgresql's type conversion function CAST is an SQL standard, which is slightly different from DB2. Considering that DB2 can only perform limited type conversion, postgresql actually frees DB2 database developers. Most postgrsql functions are similar to oracle, which is different from the functions provided by DB2.

Others are basically the same.

3. Client Interface

There must be a huge gap between C interfaces.

ODBC is a standard, and postgresql provides sufficient ODBC support.

JDBC is a standard, but postgresql only provides type 4, and DB2 seems to prefer type 2.

The difference between establishing a JDBC connection is:

DB2 class path: COM. ibm. DB2.jdbc. app. DB2 database development Driver (type2), com. ibm. DB2.jcc. DB2Driver (type4 ),

The postgresql class path is org. postgresql. Driver.