Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

48 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

The STRJRNPF currently does not support an *ALL option like the ENDJRNPF command.

So the command needs to be executed for each DB2 table object to associate these

tables with XYZ journal and journal receiver objects.

7. Save the newly configured schema to backup media, so that you do not have to do these

configuration steps again.

4.2 Comparing the SQL objects and the DDS files

When we create a table using SQL, under the covers a physical file is created. The same is

true when we create an SQL index or a SQL view—a logical file is created under the covers.

But there are some differences between SQL objects and DDS-created files that we need to

understand. In this section we address some of the differences between DDScreated objects

and SQL.

The new SQL objects reside in a schema. In 4.1.6, “Creating the new DB2 schema on the

iSeries server” on page 40, we covered the differences between a library and a schema.

4.2.1 SQL tables compared with physical files

There are some advantages of SQL tables over physical files. They are:

򐂰 The constraint definitions can be included in the object source.

򐂰 SQL does faster reads than HLL reads to a physical file. The main reason is that a cursor

reading an SQL table does not have the extra data cleansing code like a DDS PF reading.

򐂰 We can use longer and more descriptive column names.

򐂰 The data modeling tools have been made to support SQL.

򐂰 Automatically journaling if the tables are created in a schema, as we have stated before.

There are some disadvantages of SQL tables. These are:

򐂰 Slower writes, because the cursor created for SQL tables has more data validation code

than a cursor used for writing into a DDS PF.

򐂰 No Distributed Data Management (DDM) support, but SQL can utilize Distributed

Relational Database Architecture (DRDA®) connection by using SQL CONNECT

statement.

򐂰 Multiple member files. An SQL table does not support multi-member files, as we have

stated before. You can use an SQL ALIAS statement to create an alias for each physical

file member, as the example:

CREATE ALIAS MYSCHEMA/JANSALES FOR SALES(JANUARY)

4.2.2 SQL indexes compared with keyed logical files

SQL indexes and keyed logical files both cause the same internal OS/400 object to be built.

The main difference is that an SQL index can be either a binary radix tress structure or an

advanced bit mapped index structure known as an Encoded Vector Index.

Note: If the journal object was altered away from its default settings with the CHGJRN

CL command or iSeries Navigator, those customizations would need to be performed

on the journal object in the PROD schema.