major difference between db2 and db400

[dileep.gare]

hi All,

can any help me out

Major difference between db2 and db400?
how can we use db2 in As400 like db400?
From sql interface when we creating table we are getting return message like table is created but not journaled if this is the way to use db2 then what is the use of table without journaling.

Thanks
Dileep

[tomliotta]

Re: major difference between db2 and db400

The use of a table is mostly the same with or without journaling. You can INSERT, DELETE and UPDATE just the same whether a journal is associated or not. However, there are a few things that do require a journal. Processing transactions under commitment control is an example. If commitment control isn't needed, then a journal might not be needed. (Note that it's a good idea to use journals even when not required.)

There are two general ways to create a table. Tables can be created in a library or in a schema. A 'schema' is implemented as a special form of a library. If you create a schema, a journal will be created for it automatically, and every table that you create in that schema will be journaled automatically.

If instead you simply create a 'library', you need to take an additional step to cause automatic journaling. You also have the option of not journaling at all, either for specific tables or for all tables in the library. (See Automatically starting journaling in the Information Center for more information.)

In your case, you probably created your table in a 'library' that did not have automatic journaling. If you want your table to be journaled (and you have a journal that is available for this use), you can use the STRJRNPF command to make the association.

The distinction between 'schema' and 'library' exists mostly because the original libraries were objects that were available before SQL. Schemas didn't come along until SQL became commercially available. After SQL showed up in the industry, IBM began adding enhancements and extensions to older objects. Schemas were implemented as extensions to libraries, but the older library object kept its original characteristics.

[dileep.gare]

Re: major difference between db2 and db400

Thanks for your valuable information

What i understood is the file that is created in the library should configured to automatic journaling process.

can u provide Major difference between db400 and db2
Best way to migrate and learn db2 from db400

Thanks
Dileep

[B.Hauser]

Re: major difference between db2 and db400

DB2 for i (the term DB400 is as outdated as the term AS/400) and DB2 for LUW (Linux Unix Windows) and DB2 for z/OS are about 90 (or even more) percent identical.
All of the DB2 databases cover the (ANSI) SQL standard. An DB2 for i covers the SQL Standard for 100 %.
You may check the following link:
The SQL Reference for Cross-Platform Development

In the following link the different terms used for SQL and traditional file access on the i are described.

Terminology: SQL versus traditional file access

A schema and a library describe exactly the same object type, i.e. a container for storing (database) objects.
When creating a schema (or collection) the the SQL command CREATE SCHEMA or CREATE COLLECTION a library is generated (in the same way as if executing the CRTLIB CL command) but after a journal a journal receiver and several catalog views are added to the empty container.

... also if you create a journal with the name QSQJRN manually in a library that is generated with the CRTLIB command, any table created with the CREATE TABLE SQL command is automatically registered within the journal QSQJRN (even though there is QDFTJRN data area available within the library).

Birgitta

Birgitta

[tomliotta]

Re: major difference between db2 and db400

... also if you create a journal with the name QSQJRN manually in a library that is generated with the CRTLIB command, any table created with the CREATE TABLE SQL command is automatically registered within the journal QSQJRN (even though there is QDFTJRN data area available within the library).

Is that a 7.1 feature, or enabled by PTF? It's not how it works on my 6.1 system. The QDFTJRN journal takes precedence for both DDS and SQL when a valid QDFTJRN data area and a QSQJRN journal both exist in the library.

[tomliotta]

Re: major difference between db2 and db400

can u provide Major difference between db400 and db2
Best way to migrate and learn db2 from db400

At least one big difference is that "db400" handles not only SQL standard tables, but also native files. There are attributes of native files that aren't handled quite directly within SQL such as multiple members. A migration away from "db400" needs to account for multi-member files.

Also, the native file system allows for multi-format files, and SQL can have real difficulty handling those.

The best way to "learn db2 from db400" might be to do as much work as possible using SQL interfaces and clients. I don't know a better way than actually doing the work. When using SQL, you might run into elements of "db400" that do not translate well to DB2. Those will be indications of issues with migration.