IDMS

  • 1.  Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Posted Oct 18, 2018 09:56 AM
    Hi,
    I am a COBOL developer and in a few months my project will start using CA-IDMS as the backend.
    I have experience only in using DB2 and IMS as backends and IDMS is new to me
    I would be grateful if the IDMS experts can give their opinion on 1 question (and 2 related questions) that has been asked of me.
    Question: Which facilitates faster record retrieval:
                    1) Indexes on tables
                        OR
                    2) defining CALCS / RECORDS / SETS on the underlying tables?
    Related question: Would the life of IDMS administrators become easier if there were no CALCS+RECORDS+SETs on the IDMS schemas?
    Related question: Does having CALCS+RECORDS+SETS cause delay while executing common IDMS utilities like: REORG / LOAD / UNLOAD?
    My COBOL team is undergoing a training session and it will take some months for us to start co-ordinating with our client team who are managing IDMS (project is still in SOW negotiation stage)
    Meanwhile I have been told to go through the existing COBOL modules and suggest performance improvements.
    One thing that struck me is the backend Central Versions have lots of record structures clubbed together in chain linked sets which I understand cause various records to point to each other
    Despite the existence of these record sets the COBOL code is still accessing the tables using in-built indexes (.i.e normal select sql statements which cause IDMS to load a specific index during runtime)
    I can suggest that "Existing COBOL code be revamped to use the CALC+RECORD+SET objects instead of simple select SQL statements which use only indexes"
    But before I suggest I want to make sure that I am not causing extra strain to the backend IDMS admin team
    Would appreciate if the IDMS experts can provide some useful pointers on the 3 questions above
    Thanks
    Infra Application Developer


  • 2.  Re: Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Broadcom Employee
    Posted Oct 19, 2018 02:11 AM

    1. There would be many other influencing factors such as buffering and the condition of the database, but in isolation, a CALC retrieval on its own will be faster than an index retrieval as it should require fewer IOs (in general one as opposed to potentially three - again, depending on the condition of the database).
    But if you compare reading records with an index to using a CALC retrieval to get an entire cluster (CALC and the walking sets, getting owners, etc) then the difference is less easy to define.

    2. I would never advocate avoiding using CALC but I have heard of some sites (very few) that don't like the complications that using sets brings. By avoiding sets, then their IDMS databases are logical. In a corrupt database situation, this can make resolving things easier.
    But you would be denying yourself some very useful functionality for minimal gain.

    3. I don't think so, but each database design is different.

    Regarding your later comments, SQL SELECTs in IDMS do not by definition use only indexes.
    If a more efficient database path can be found for which using CALC and walking sets will be more efficient, then that's the path IDMS will use.
    You can use the EXPLAIN facility to determine the path that a particular SQL statement will use.
    https://docops.ca.com/ca-idms-ref/19/en/sql-reference/statements/explain



  • 3.  Re: Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Posted Oct 19, 2018 02:49 AM

    Hi Ian,

     

    Thanks for your reply.

     

    I have posted my understanding on the functioning of RECORDs and SETs here:

     

    SET OBJECT IN CA-IDMS (RELATING RECORDS and USING DATA STRUCTURE / BUBBLE DIA... 

     

    Please do go through it whenever you have the time and please do let me know if my understanding needs corrections

     

    I will surely work on using EXPLAIN statements in my COBOL applications.

     

    Thanks once again

     

    Thanks & Regards

    Infra Application Developer

     

    A NOTE OF THANKS: Thanks for letting me know that EXPLAIN statement enables the best access path. Initially I had a doubt that to utilize the CALC/RECORD/SET objects I would have to write separate JCLs including FIND/OBTAIN statements and incorporate those JCLs within my COBOL applications (I heard that the IDMS utility IDMSBCF executes native IDMS scripts from within JCLs and can be called from an application)

     

    But now after going through the EXPLAIN statement I am confident that I can start using the CALC objects much easily.



  • 4.  Re: Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Broadcom Employee
    Posted Oct 19, 2018 09:22 AM

    Your understanding of how CALC keys work is not correct.
    Also, you are mixing IDMS's traditional network database with SQL, which at some point will be useful because IDMS mixes them, too.
    However, if you are trying to learn the basic concepts of how an IDMS database works, I would suggest concentrating on the network database first.

     

    I recommend you have a read of this section in the documentation (and the eight sub-sections):
    https://docops.ca.com/ca-idms-ref/19/en/navigational-dml-programming-reference/database-concepts



  • 5.  Re: Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Posted Oct 19, 2018 09:45 AM

    Even DB2 recognized the value in CALC records – V 10 introduced  HASH storage

     

    Chris Hoelscher

    Technology Architect, Database Infrastructure Services

    Technology Solution Services

    Humana Inc.

    123 East Main Street

    Louisville, KY 40202

    Humana.com

    (502) 476-2538 or 407-7266



  • 6.  Re: Which facilitates faster record access- SQL select statements which use only indexes or customized FIND/OBTAIN scripts (using CALC+RECORD+SETS)

    Posted Oct 20, 2018 04:18 AM

    Hi Ian,

     

    Thanks for your reply. I will surely go through the links you have mentioned and will also correct my understanding on CALC keys.

     

     

    I am going through some PDF files on various technologies which I might be using in my upcoming projects and regularly post articles on my understanding of various concepts (specially database concepts). Do visit the below forums whenever you have the opportunity:

     

    https://plus.google.com/communities/115509438000076679723 (general discussion on mainframe)


    https://plus.google.com/communities/109422505413992934298 (COBOL)

     

    Anyway thanks for your inputs

     

    Hi Chris,

     

    Thanks for the DB2 hash information.

     

    Thanks & Regards

    Infra Application Developer