Results 1 to 9 of 9

Thread: VDF holds locks in DB2 Mainframe catalog tables

  1. #1
    Join Date
    Mar 2012
    Posts
    11

    Default VDF holds locks in DB2 Mainframe catalog tables

    We are running VDF 17.0.28.0 and using it to access DB2 tables on our Mainframe using an ODBC connection.
    Once we use VDF to access a list of tables on DB2, the locks are held against the VM DB2 catalog tables until
    the application is terminated. This prevents other users from dropping and creating objects in DB2. For example,
    this the screen I am on. Even while sitting here, locks are being held (see 2nd screen shot).

    Click image for larger version. 

Name:	VDFScreenShot_Locks.JPG 
Views:	150 
Size:	90.1 KB 
ID:	4975


    User Agent: 9 User-ID: VDFUSER SQL-ID: VDFUSER
    is R/W APPL B986A
    Agent is not processing and is in communication wait.
    State started: 2012-03-28 12:56:39
    Conversation started: 2012-03-28 12:52:29
    LUWID: AC10098D.EBB2.120328175030.0001
    EXTNAM: Studio.exe 27FC0000001"""""""""""""""
    Requester: SQL09074 (DB2/NT64) at EWZELL7
    Protocol: DRDA via TCP/IP
    Package: NULLID.SYSSN200 Section: 4
    Connection ID: 45

    DBSPACE LOCK NUMBER
    AGENT USER NUMBER TYPE IN SIX IS IX S U X Z WAITERS
    9 VDFUSER DB 0 0 0 1 0 0 0 0 0
    9 VDFUSER 1 DBSP 0 0 1 0 0 0 0 0 0
    9 VDFUSER 1 IKEY 0 0 0 0 467 0 0 0 0 <-- Locks being held
    9 VDFUSER 1 ROW 0 0 0 0 526 0 0 0 1 <-- Locks being held
    9 VDFUSER 1 TABL 0 0 10 0 0 0 0 0 0
    9 VDFUSER 2 DBSP 0 0 1 0 0 0 0 0 0
    9 VDFUSER 2 TABL 0 0 0 0 1 0 0 0 0
    9 VDFUSER 17 DBSP 0 0 0 1 0 0 0 0 0
    9 VDFUSER 17 PAGE 0 0 0 0 0 0 2 0 0
    9 VDFUSER 17 TABL 0 0 0 0 3 0 0 1 0
    9 VDFUSER 295 DBSP 0 0 1 0 0 0 0 0 0
    9 VDFUSER 295 TABL 0 0 1 0 0 0 0 0 0
    9 VDFUSER 10041 INT 0 0 0 0 0 0 1 0 0
    9 VDFUSER 10042 INT 0 0 0 0 0 0 0 1 0
    9 VDFUSER 10043 INT 0 0 0 0 0 0 1 0 0
    9 VDFUSER 10044 INT 0 0 0 0 0 0 0 1 0
    9 VDFUSER 10045 INT 0 0 0 0 0 0 0 1 0
    9 VDFUSER 10046 INT 0 0 0 0 0 0 0 1 0
    9 VDFUSER 10047 INT 0 0 0 0 0 0 0 1 0
    9 VDFUSER 10048 INT 0 0 0 0 0 0 1 0 0
    9 VDFUSER 10049 INT 0 0 0 0 0 0 1 0 0



    Ed Zell
    Illinois Mutual Life
    ewzell@illinoismutual.com
    (309) 636-0107

  2. #2
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    8,850

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Hi Ed,

    is this new to VDF 17.0? Have you made changes since this worked? Which CK version are you using?

  3. #3
    Join Date
    Mar 2012
    Posts
    11

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Hi Dennis,

    This is a brand new installation of VDF for Illinois Mutual Life. It is our first attempt to access DB2 tables on our z/VM Mainframe
    system using the product. We have used DB2 Connect for quite some time with other applications however and we have been
    at 9.7 Fixpack 4 for over a year.

    We are running the latest beta (as far as I know). Here is some of the Help/About screen.

    Runtime Version: 17.0.21
    Package Version: 17.0.2
    FMAC Version: 17.0.84

    Thanks for your time and help!

  4. #4
    Join Date
    Mar 2012
    Posts
    11

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    This is from our developer, thanks Chuck!



    From the build notes it appears it is CK 5.1.0.92

    Chuck Atkinson
    Senior Dataflex Solutions Developer
    IT
    Illinois Mutual

  5. #5
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    8,850

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Hi Ed,

    in your original post, there is only one screenshot attached, please attach the one you were talking about (you can edit your own posts).

    Could you try this in the released VDF 16.1? Without that test, we have no idea if it's something that broke in (the unreleased beta) VDF 17.0 or not.

    Beyond that, I would debug this as I would any deadlock issue. I don't know anything about connecting to a mainframe, so I will have to leave it up to you that you have tested that part of the configuration enough to ensure that the problem isn't caused by that. I would start by checking that all alias tables use the proper DF_File_Alias setting. This setting is currently flagged in the VDF 17.0 beta doc as deprecated, but we are bringing it back. The Studio will generate the code lines for you in the future, but if you created new aliases in VDF 17.0, those lines could be missing in required places (see this post).

  6. #6
    Join Date
    Mar 2012
    Posts
    11

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Good morning Dennis.

    Sorry for the confusion, but the second screen shot was really just the DB2 locking information from the Mainframe session. I was going to post it
    as a screen paste but did it as text instead.

    Thanks for the suggestions. I will work with Chuck to test this under VDF 16.1, but I believe it will work the same way in that release.
    It looks like read committed is the stability level being used, which would be fine, but the LUW is not being ended after the catalog
    tables are read, so shared locks are held even while the application is waiting for end user interaction.

    Over the weekend I was able to reproduce this with another ODBC utility (SQLDBx) that we have. That made me start looking at the
    DB2 Connect "patches" that are available to see if one of them might deal with this behavior in the driver. Nothing jumped out at me
    though.
    http://publib.boulder.ibm.com/infoce...2Fr0008804.htm
    http://publib.boulder.ibm.com/infoce...2Fr0008804.htm

    I posted an ODBC dump from one of the DB2 tables in the "Can't see UNIQUE index in DB2" thread. Not sure if that would
    help or not. I am also looking at some traces I ran in DB2 Connect to see if I can see anything interesting.

    I would appreciate any suggestions and thanks for your time.

  7. #7
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Hi Ed

    VDF works best with UR (Uncommitted read).
    In SQL land this sounds very scary, but this is how an embedded database also works. As soon as a saverecord is done, even while still in a Begin transaction/End Transaction block, the saved data will be visible by another user.

    So, it might be that when you force RC Read Committed, that you run into some bugs not yet fixed where transactions are kept open.?!?

    Try setting the isolation level to UR and give that a go.
    Cheers,
    Marco
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

  8. #8
    Join Date
    Mar 2012
    Posts
    11

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    I have found the resolution to the catalog locking issue. It had nothing do to with VDF and was strictly a DB2 Connect problem. We had a patch
    turned on that was required for SSIS to INSERT into DB2. It caused locks to be held AFTER a cursor was closed which was what we were seeing
    with VDF and a couple of other PC based ODBC applications that used DB2.

    Thanks for all the help and suggestions, I appreciate the tips.

  9. #9
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: VDF holds locks in DB2 Mainframe catalog tables

    Hi Ed

    Great to see you solved the issue! Thanks for posting the solution.

    Cheers,
    Marco
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •