Best Practices - DB2/400 (SQL) Gateway

This is a collection of best-practice advice and tips for working with the Magic xpa DB2/400 (SQL) gateway.

• Database
• Isolation Level and Locking Strategy
• Cursor Optimization
• File Conversion from ISAM to SQL
• Variable Length Fields – VARCHAR
• When and How to Use Direct SQL
• SQL Indexing Tips
• ISAM as a Result Database for Direct SQL

Database

Keep the Change Tables in Studio setting set to No.

Isolation Level and Locking Strategy

The Isolation level used during the execution of SQL statements determines the degree to which the activation group (Magic xpa IBM i session in our case) is isolated from concurrently executing activation groups (other active IBM i sessions, RPG, COBOL, CLP programs, etc.). Therefore, when a Magic xpa IBM i job executes an SQL statement, the Isolation level determines:

• The degree to which rows retrieved by a Magic xpa job and database changes made by the Magic xpa job are available to other concurrently executing activation groups.

• The degree to which database changes made by concurrently executing activation groups can affect the Magic xpa job.

• The Isolation level in Magic xpa for DB2/400 UDB is specified as a parameter of SQL database in the SQL properties – Ctrl+Enter (SQL_ISOLATION_LEVEL=XX) and applies to the activation groups (Magic xpa IBM i connections) that use this SQL database.

The following descriptions of Isolation levels refer to locking data in row units.

DB2 UDB for IBM i supports five Isolation levels. For all Isolation levels except No Commit, the database manager places exclusive locks on every row that is inserted, updated, or deleted. This ensures that any row changed during a unit of work is not changed by any other activation group that uses a different commitment definition until the unit of work is complete.

Note: Practically speaking, only the UR and NC Isolation levels can be used for the Magic xpa multi-user online environment.

The Isolation levels are:

1. Repeatable Read (RR) SQL_ISOLATION_LEVEL=RR

Level RR ensures:

• Any row read during a unit of work is not changed by other activation groups that use different commitment definitions until the unit of work is complete.

• Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed.

• In addition to any exclusive locks, an activation group running at level RR acquires at least share locks on all the rows it reads. Furthermore, the locking is performed so that the activation group is completely isolated from the effects of concurrent activation groups that use different commitment definitions.

DB2 UDB for IBM i supports repeatable-read through COMMIT(*RR). Repeatable-read Isolation level is supported by exclusively locking the tables containing any rows that are read or updated. In the ANS and ISO standards, Repeatable Read is called Serializable.

2. Read Stability (RS) SQL_ISOLATION_LEVEL=RS

Like level RR, level RS ensures that:

• Any row read during a unit of work is not changed by other activation groups that use different commitment definitions until the unit of work is complete.

• Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed.

• Unlike RR, RS does not completely isolate the activation group from the effects of concurrent activation groups that use a different commitment definition. At level RS, activation groups that issue the same query more than once might see additional rows. These additional rows are called phantom rows.

For example, a phantom row can occur in the following situation:

• Magic xpa/400 Session P1 reads the set of rows n that satisfy some search condition.

• Activation group P2 then INSERTs one or more rows that satisfy the search condition and COMMITs those INSERTs.

• P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows inserted by P2.

• In addition to any exclusive locks, an activation group running at level RS acquires at least share locks on all the rows it reads.

DB2 UDB for IBM i supports read stability through COMMIT(*ALL) or COMMIT(*RS). In the ANS and ISO standards, Read Stability is called Repeatable Read.

3. Cursor Stability (CS) SQL_ISOLATION_LEVEL=CS

Like levels RR and RS, level CS ensures that any row that was changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed. Unlike RR and RS, level CS only ensures that the current row of every updateable cursor is not changed by other activation groups using different commitment definitions. Therefore, the rows that were read during a unit of work can be changed by other activation groups that use a different commitment definition. In addition to any exclusive locks, an activation group running at level CS has at least a share lock for the current row of every cursor.

DB2 UDB for IBM i supports cursor stability through COMMIT(*CS). In the ANS and ISO standards, Cursor Stability is called Read Committed.


4. (UR) Uncommitted Read (UR) SQL_ISOLATION_LEVEL=UR

For a SELECT INTO, a FETCH with a read-only cursor, subquery, or subselect used in an INSERT statement, level UR allows:

• Any row read during the unit of work to be changed by other activation groups that run under a different commitment definition.

• Any row changed (or a row that is currently locked with an UPDATE row lock) by another activation group running under a different commitment definition to be read even if the change has not been committed.

• For other operations, the rules of level CS apply.

DB2 UDB for IBM i supports uncommitted read through COMMIT(*CHG) or COMMIT(*UR). In the ANS and ISO standards, Uncommitted Read is called Read Uncommitted.

5. No Commit (NC) SQL_ISOLATION_LEVEL=NC

For all operations, the rules of level UR apply except:

• Commit and Rollback operations have no effect on SQL statements. Prepared statements are not discarded, cursors are not closed, and LOCK TABLE locks are not released. However, connections in the release-pending state are ended.

• Any changes are effectively committed at the end of each successful change operation and can be immediately accessed or changed by other application groups using different commitment definitions.

DB2 UDB for AS/400 supports No Commit through COMMIT(*NONE) or COMMIT(*NC).

Cursor Optimization

The result of the cursor optimization of SQL server on IBM i:

• If a file is opened more than once, then the 2nd close is a pseudo close cursor. This is a performance feature that allows the subsequent open cursor to be done very quickly. Display Open Files option (14) of Work with Job command shows many “open files”. Be aware that these are pseudo cursors only. If you perform a COMMIT or ROLLBACK, your cursors (including the pseudo closed ones) will be hard closed.

• When Share = None is selected in the Data Source repository,Magic xpalocks the table exclusively. The table will be locked until the end of the session. To “release” the lock, perform a COMMIT or ROLLBACK by direct SQL.

File Conversion from ISAM to SQL

Use a Direct SQL statement to copy records from ISAM files to SQL tables (INSERT with SELECT). It is the fastest way to create SQL tables from ISAM.

Write two Magic xpa programs for the conversion process: The first program should create SQL table rows with the same data from the ISAM file. The second program should go against the new SQL table and update new columns with new constants or new data.

Do not use any transaction in conversion programs; it creates unnecessary performance overhead.

Variable Length Fields – VARCHAR

Variable length field support on IBM i has been implemented with a spill area, thus creating two possible situations: the non-spill case and the spill case. With this implementation, when the data overflows, all of the data is stored in the spill portion. An example would be a variable length field that is defined with a maximum length of 50 bytes and an allocated length of 20 bytes. In other words, it is expected that the majority of entries in this field will be 20 bytes or less and occasionally there will be a longer entry up to 50 bytes in length. When inserting an entry that has a length of 20 bytes or less, that entry will be inserted into the allocated part of the field. This is an example of a non-spill case. However, if an entry is inserted that is, for example, 35 bytes long, all 35 bytes will go into the spill area.

To create the variable length field just described, use the following SQL/400 statement:

CREATE TABLE library/table-name (field VARCHAR(50) ALLOCATE(20) NOT NULL

When and How to Use Direct SQL

Direct SQL is a very powerful feature; however, in some cases, especially in Online programs, it might make the performance slower.

Use the Direct SQL for a small number of records in large tables. If the result of the Direct SQL is large, then split the selection into three steps:

1. Create a temporary table in QTEMP.

2. Fill in a temporary table using a D-SQL statement INSERT AS SELECT.

3. Create an ISAM-based browse task to display the result set.

Always use the FOR FETCH ONLY OPTIMIZE for 100 ROWS or ORDER BY clause at the end of every statement in order to activate the DB2 UDB Optimizer.

SQL Indexing Tips

The Temporary Index slows down the Magic xpa application process. In many cases, temporary indexing is not visible. It is very important from the performance point of view that queries comply with the structure of indexes. Magic xpa gives programmers the possibility "to Range" and "to Locate" whatever they like or to change the index in the Data repository without changing the real database table. However, the DB2 Optimizer of IBM i might not be able to find a proper index for query processing. It might lead to a full table scan or the generation of a temporary index. A full table scan and the generation of temporary indexes on big tables is very expensive in terms of system performance.

Sometimes it is practical to activate the SQL Server Enterprise Manager of the Operation Navigator to trace the queries. You will not be able to find all of the problems by observing the status of MGCLIENT/MGSERVER jobs.

In some cases, the Locate and Range expression of the task (Ctrl+C and Advanced) will improve performance of the query: Set the "Range" or "Locate" according to the existing index and Magic xpa will do the rest. Using the above technique may eliminate the temporary indexing and will increase the performance.

ISAM as a Result Database for Direct SQL

You can use the ISAM database as a result for running direct SQL statements. It is recommended to define an ISAM database in the QTEMP library for this purpose.