Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Future consideration
Created by Guest
Created on May 18, 2023

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.

Needed By Not sure -- Just thought it was cool
  • Guest
    Reply
    |
    Sep 6, 2023

    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