Modernizing ADABAS: Optimizing INSERT into Adabas Virtual Tables

One of the key points in a modernization project for a site with Adabas is to “normalize” Adabas Data Model. DVM offers a normalized Data Model for any Adabas file, converting MU/PE fields into auxiliary Tables. This normalization has a cost: you must insert ROW by ROW all the data in the auxiliary tables. It means that, if you need to insert a MU/PE field with 100 elements, you need to execute 102 SQL sentences.

The objective of this AHA is to reduce the need of 102 SQLs just to 2 SQL sentences. In case that the DVM client is in a remote location, the benefits will be huge.

The problem:

Suppose you have a MU field and that you want to Insert a new record with 100 values for the MU field.

Using Non flatten Virtual tables, you need 102 SQL sentences to complete the Insert; if the client is in a remote localization, to execute 102 round trips can need a high elapsed time.

Currently you need to execute:

1.- INSERT INTO MAIN_TABLE (…) VALUES (…)

2.- SELECT .. FROM MAIN_TABLE WHERE … to get the CHILD_KEY generated by the previous INSERT

3.- INSERT INTO MU_AUX_TABLE (…) VALUES ()

…

102.- INSERT INTO MU_AUX_TABLE (…) VALUES ()

This AHA proposal:

This 102 SQLs can be reduced jus to 2 SQLs with next 2 improvements.

Improvement 1. Supporting JDBC method getGeneratedKeys()

You can consider that the columns RECORD_ID, BASE_KEY and CHILD_KEY as KEYS generated by ADBADAS/DVM which is nearly true

You can create the Statement using (currently supported)

m_statement = m_connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );

execute the First Insert

int num_rows = m_statement.executeUpdate();

and as response you can obtain the desired data (RECORD_ID, CHILD_KEY,…) using the method

ResultSet rs = m_statement.getGeneratedKeys();

After this improvement, the SELECT you currently need to execute after the first insert for getting the same data (RECORD_ID, CHILD_KEY,…) , is not more needed.

Improvement 2: Supporting the INSERT sentence with multiple VALUE lines.

This improvement will convert the 100 INSERT sentences currently needed, in JUST one INSERT with 100 VALUE lines.

INSERT INTO

table_name(column_list)

VALUES

(value_list_1),

(value_list_2),

(value_list_3),

...;

The most important improvement is the second one, that is, support INSERT with multiple VALUE lines.