DB2 - L

 View Only
  • 1.  Existence check for underlying vsam datasets

    Posted Nov 17, 2021 11:40 AM
    Esteemed Listers,
    Is there any REXX code, or script which can be used to identify if underlying VSAM file for a table exists or not ?

    PS :  We have to verify in a test system for 900+ objects if underlying VSAM file exists or not

    Thanks in advance
    Vardhini

    ------------------------------
    ParvathavardhiniKannan
    ------------------------------


  • 2.  RE: Existence check for underlying vsam datasets

    Posted Nov 17, 2021 06:18 PM
    I don't have code written for this, but I can steer you in the right direction...REXX has a TSO/E function called SYSDSN that can be used for this purpose. It's on page 146 of the reference: https://www-01.ibm.com/servers/resourcelink/svc00100.nsf/pages/zOSV2R3SA320972/$file/ikja300_v2r3.pdf

    Are you generating your dataset list from the Db2 catalog?

    The other option would just be to SELECT something from every table/index...or to issue an ACCESS DATABASE for all of those objects, or run an image-copy for them...and see what breaks. I guess you only really need the tablespaces...indexes can be rebuilt.


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



  • 3.  RE: Existence check for underlying vsam datasets

    Posted Nov 17, 2021 08:39 PM
    Dear Mark,
    Thanks for the reply.
    Already we tried SYSDSN. looks like this works only with PS or PDS. It doesnt work for VSAM dataset.
    Executing utility and seeing if it fails or not is an option.
    But was trying to see if any way to avoid executing 900+ jobs

    Thanks
    Vardhini

    ------------------------------
    ParvathavardhiniKannanKyndryl
    ------------------------------



  • 4.  RE: Existence check for underlying vsam datasets

    Posted Nov 17, 2021 09:16 PM
    In that case LISTDSI (same manual) might be better.

    Or, if you really want to get low level, IGGCSI00. There is a sample program IGGCSIRX.
    https://www.ibm.com/docs/en/zos/2.4.0?topic=catalogs-catalog-search-interface-users-guide

    James Campbell

    On 18 Nov 2021 at 1:38, Parvathavardhini Kannan via I wrote:

    > Dear Mark,
    > Thanks for the reply.
    > Already we tried SYSDSN. looks like this works only with PS or PDS. It doesnt work for VSAM dataset.
    > Executing utility and seeing if it fails or not is an option.
    > But was trying to see if any way to avoid executing 900+ jobs
    >
    > Thanks
    > Vardhini
    >
    > ------------------------------
    > ParvathavardhiniKannanKyndryl
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 17, 2021 06:18 PM
    > From: Mark Wieczorkowski
    > Subject: Existence check for underlying vsam datasets
    >
    > I don't have code written for this, but I can steer you in the right direction...REXX has a TSO/E function called SYSDSN that can be used for this purpose. It's on page 146 of the reference: https://www-01.ibm.com/servers/resourcelink/svc00100.nsf/pages/zOSV2R3SA320972/$file/ikja300_v2r3.pdf


  • 5.  RE: Existence check for underlying vsam datasets

    Posted Nov 18, 2021 05:14 AM
    Hi,

    How about calling SYSPROC.ADMIN_DS_LIST()

    Regards,
    Colin Raybould.


    ------------------------------
    ColinRaybouldSibaya Technical Support Ltd.
    ------------------------------



  • 6.  RE: Existence check for underlying vsam datasets

    Posted Nov 18, 2021 09:58 AM
    In the SYSTABLEPART catalog table, tablespaces that have been defined with DEFINE NO will have a value of -1 in the SPACE column. But that won't find tablespaces where the dataset has been migrated or scratched.

    ------------------------------
    RussellPetersCentral Technology Services
    ------------------------------



  • 7.  RE: Existence check for underlying vsam datasets

    Posted Nov 18, 2021 10:25 AM
    If the utility IS an option, you could use LISTDEF to pick up everything in the database.

    If you want to do it in REXX, and SYSDSN doesn't work for VSAM LDS...you could try just using an ADDRESS TSO "ALLOC" command for each dataset name...allocate it to a DDname dynamically, capture the return-code. 

    Here's an example from my shop:

    SAY "--------------------------------------------"
    Say "Test DS 1..."
    tstds1 = "'DSNDSGI1.DSNDBC.DSN8D12U.NEWDEPT.I0001.A001'"
    Address TSO "ALLOCATE DD(TEST1) DA("tstds1") SHR"
    Say 'RC = '||RC
    Say "Test DS 2..."
    tstds2 = "'DSNDSGI1.DSNDBC.DOES.NOT.EXIST'"
    Address TSO "ALLOCATE DD(TEST2) DA("tstds2") SHR"
    Say 'RC = '||RC
    Say "Test DS 3..."
    tstds3 = "'DSNDSGI1.DSNDBC.DSN8D12U.NEWPHONE.I0001.A001'"
    Address TSO "ALLOCATE DD(TEST3) DA("tstds3") SHR"
    Say 'RC = '||RC


    Output:

    Test DS 1...
    RC = 0
    Test DS 2...
    IKJ56228I DATA SET DSNDSGI1.DSNDBC.DOES.NOT.EXIST NOT IN CATALOG OR CATALOG CAN
    IKJ56701I MISSING DATA SET NAME+
    IKJ56701I MISSING NAME OF DATA SET TO BE ALLOCATED
    RC = 12
    Test DS 3...
    RC = 0

    RC comes back 0 if the dataset allocates successfully, 12 if it doesn't. Dunno if your shop allows 900 datasets to be dynamically allocated to a TSO session at once, but I suppose you could always just ALLOC and FREE the same DD with different names. I assume you'd also need access to allocate each dataset. But if you wanted to try them all outside of Db2, there you go. Again, you could generate a dataset list based on the values in the catalog, or you could just put them all in a REXX array, or in a dataset and have your REXX read them in one at a time and test them.



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



  • 8.  RE: Existence check for underlying vsam datasets

    Posted Nov 19, 2021 10:34 AM
    Edited by David Alban Nov 19, 2021 10:40 AM

    We do not have a script to share, but at a high level one design is to use IDCAMS DCOLLECT to generate a dataset list based on a Db2 environment's storage group(s). Depending on one's environment other parameters can be used as well in place of storage groups.

    Ex IDCAMS input:
    DCOL -
    OFILE(OUTDS) -
    STOG(<insert stogroup1>, -
    <insert stogroup2>)

    Use a program/script to traverse the catalog and match it against the DCOLLECT output (pre-sorting and applicable list compare logic recommended to help avoid scanning the entire DCOLLECT file for each object). Depending on storage group setup or other inputs, this can include indexes as well which is also beneficial to validate. With this DCOLLECT output, existence can be validated and also anything important/interesting can be extracted and stored in a user table. DCOLLECT has good information that is not in the catalog about Db2 object datasets. As others have mentioned, DEFINE NO must taken into account. Depending on the amount of logic added, the whole process runs in about 20 seconds for a 20k object subsystem.

    DCOLLECT Output Record Structure - IBM Documentation



    ------------------------------
    Thanks,
    David
    ------------------------------