Db2 Tools for zOS

 View Only
Expand all | Collapse all

Secondary Index - Virtual Table vs. Virtual View

  • 1.  Secondary Index - Virtual Table vs. Virtual View

    Posted Thu January 20, 2022 04:26 PM
    We have an IMS segment which is being read to get all records with a value matching a field.  This is usually a small number.

    This ended up being slow due to DVM needing to read the entire segment to "find all matches"...as I understand it, under this setup, DVM has to read the entire segment in order to "find all" since this is not a keyed field.

    We proceeded to get a secondary index set up, so now this is a keyed field.

    At this point, do we need a virtual table created using the secondary index or is it a virtual view?  As it turns out we need to use field A to get all the matches and only need one other field returned.

    Please advise...


    ------------------------------
    Kevin Flanagan
    General Motors
    ------------------------------

    #Db2Toolsforz/OS


  • 2.  RE: Secondary Index - Virtual Table vs. Virtual View

    Posted Fri January 21, 2022 02:06 PM
    Gig's Response:

    Assuming the secondary index is on the root segment AND the search fields are sourced from data in the root segment, you can map a single virtual table that can use either the root sequence field or secondary index based on search criteria specified in the SQL WHERE clause. To do this, you will need to extract the new DBD source into DVM so DVM is aware of the new secondary index. You will also need to extract new PSB source containing a regular PCB for the database and a separate PCB with a PROCSEQ for the secondary index.

    When querying the new table after bringing in the new DBD and PSB, DVM will select either the regular PCB or secondary index PCB based on the SQL WHERE clause for the table. If the WHERE clause can be used to create a qualified SSA for the secondary index, the secondary index will be used. Otherwise, the regular PCB without a PROCSEQ will be used.



    ------------------------------
    Jeffery Lutzow
    ------------------------------

    Original Message