DB2 - L

 View Only
Expand all | Collapse all

DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

  • 1.  DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 02, 2021 02:16 PM
    Esteemed Listers,
    We observe a strange situation. When we try to view the rows in a table using ADMIN tool by issuing SELECT on the table, or by browsing using file aid we are able to view the rows.
    But when we try to execute the IBM UNLOAD utility we get error -904 due to missing VSAM file.
    And really that underlying VSAM file does not exist .

    So how does the data get displayed for SELECT query ?
    Anyone else faced similar issue ?

    Thanks in advance 
    Vardhini

    ------------------------------
    ParvathavardhiniKannanIBM
    ------------------------------


  • 2.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 02, 2021 02:29 PM

    Could you provide the UNLOAD statements, and the full error messages, please?

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer

    Intelligent Z Optimization and Transformation

    BMC Software

    Direct

    +44 118 921 8517

     

    Mobile

    +44 7840 023 560

     

    Email

    marcus_davage@bmc.com

     

     

     






  • 3.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 02, 2021 02:34 PM
    Index Only?

    Data still in buffer pool?

    Sent from my iPad





  • 4.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 02, 2021 02:55 PM
    Hi Phil,
    Yes looks like it is due to the index.
    Looks like the VSAM dataset for INDEX is still existing, while the VSAM dataset for the table is missing.

    Will it be possible to have the VSAM dataset for table created using the available VSAM dataset for Index ?

    ------------------------------
    ParvathavardhiniKannanIBM
    ------------------------------



  • 5.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 02, 2021 03:14 PM
    Okay, Now I used DSNTIAUL using SELECT query and got the data unloaded, and get the table loaded.

    The 2 tables having this issue were small tables. one was having only one column, and the index also contained this column.
    The other table contained only 2 columns and the index also contained both the columns.

    Thanks once again

    ------------------------------
    ParvathavardhiniKannanIBM
    ------------------------------



  • 6.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 04, 2021 02:23 PM
    Wow...lucky break! DSNTIAUL works through SQL, so it uses the index just like any other query. Glad you were able to restore the data.

    One more piece of advice...figure out where that tablespace dataset went...and make sure that doesn't happen again! I don't know if you already know who deleted it...if not, you might want to check your SMS dataset retentions. If you had columns that were NOT in the index, you may have been in for a bad time. :)



    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 7.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 05, 2021 04:08 PM
    Hi Mark,
    Thanks for your reply. It was a test system.
    Here the underlying VSAM files of the table get migrated and then deleted after a certain period of unuse.
    These 2 tables being very small ones, and all the cols in the table were in the index.
    So looks like all the access to the table was INDEX ONLY, hence the index vsam only was used. so it got retained. But since the table's VSAM file was not used at all, So after the preset time, the table's VSAM has got deleted.

    ------------------------------
    ParvathavardhiniKannanIBM
    ------------------------------



  • 8.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 05, 2021 04:28 PM
    Word of warning on auto deletion of datasets (apart from the one you just found)

    I remember the havoc caused when SMS deleted a dataset that hadn't been opened for 6 months - only to find out it was referenced (but not used) in a big chunk of our overnight batch. Which then all keeled over with JCL errors - one by one

    Painful

    Phil G

    Sent from my iPad





  • 9.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing

    Posted Nov 06, 2021 11:10 AM
    I saw a devops conference do a war stories session, where people submitted anonymous stories early and a panel picked a variety of them to share.

    That would be an interesting session at idug.





  • 10.  RE: DB2 V12 : SELECT query returns rows when underlying VSAM file is missing