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.
Good morning,
What does "future consideration" mean?.
The AHA is already approved to be implemented or they are thinking about whether they will do so in the future.
Best Regards
Encarnaci��n Isabel Tamayo Bermejo
IBM Z Client Technical Specialist
Encarnacion.Tamayo.Bermejo1@ibm.com
Encarnacion-isa.tamayo@at.seg-social.es
De: IBM Data and AI Ideas
Enviado el: martes, 5 de septiembre de 2023 14:11
Para: TAMAYO BERMEJO, ENCARNACION ISABEL
Asunto: Modernizing ADABAS: Optimizing INSERT into Adabas Virtual Tables status has changed to Future consideration