Powerhouse - Allbase problem
Michael Lee
mcl_systems@bc.sympatico.ca
Thu, 20 May 1999 07:43:17 -0700
I'm not entirely sure, but I think that the locking strategy has little to do
with when the commit happens. If you are getting locked up you will get
rollbacks. If you can reduce the locking rollbacks will happen less (obviously).
When the commit happens is not dependant on either of these (other than it won't
happen if you rollback). There is a separate SET statement that controls the
commits that can be set as required.
Michael Lee
MCL Systems Inc.
Byron Welch wrote:
> Andreas,
> If you are just want the application to wait longer for the lock their are
> two settings in the QKGO file you can use. This might eliminate the need
> for the record lock. This is a two step process.
>
> in your QTP runs use
> SET LOCK FILE UPDATE
> -> This will only lock the record at update time
>
> using SETQKGO change the QKGO "Execution-Time Parameter Values" option 3.
> You will need to change "Lock retry interval" and "Lock attempts" using
> these parameters you can force QTP to wait longer for its locks. This may
> solve your problem.
>
> As far as committing a transaction is concerned I believe that a transaction
> is only committed once all locks have been achieved.
>
> Byron Welch
> Manager In-House Projects
> Creative Computing, Inc.
>
> -----Original Message-----
> From: Noack, Andreas M. [mailto:Andreas.Noack@BetzDearborn.com]
> Sent: Thursday, May 20, 1999 6:28 AM
> To: POWERH-L@lists.swau.edu
> Subject: RE: Powerhouse - Allbase problem
>
> Hallo Jeff and all,
>
> thanks for the quick reply. The solution seems to work, but we are now
> afraid of follow-up problems. Having the default "lock file" we were always
> sure that no commit was done if e.g. step 1 of the QTP succeeded, but step 2
> failed. The rollback then always brought back the state before the QTP. If
> we now set "lock record" what will be the behaviour in this case? Can we
> also trust in a rollback for both steps or are actions from step 1 already
> committed? Thanks again for pointing out.
>
> Kind regards
> Andreas Noack
> BetzDearborn
> A division of Hercules Inc.
> Development Group Europe
> Tel. : +32.16.395.290
> Fax : +32.16.395.368
>
> The opinions expressed above are my very own. Due to this fact they may
> change anytime without further notice.
>
> -----Original Message-----
> From: Jeff Hoffman [mailto:J.Hoffman@cchs.usyd.edu.au]
> Sent: Wednesday, May 19, 1999 10:53 PM
> To: POWERH-L@lists.swau.edu
> Subject: RE: Powerhouse - Allbase problem
>
> At 11:28 am 19/05/99 -0400, Byron Welch wrote:
> >Andreas,
> > I think the default locking strategy in QTP is set lock run so everything
> >is locked for the duration of the run. I would suggest using
> >SET LOCK RECORD UPDATE
>
> I would also recommend
>
> set file xxxxxxxx open DBmode 5 (read share)
>
> for files accessed but not being updated.
>
> > This should limit locking to update time and on a record by record basis.
> >
> >Byron Welch
> >Manager In-House Programming
> >Creative Computing
> >Lincoln, RI
> >
> >-----Original Message-----
> >From: Noack, Andreas M. [mailto:Andreas.Noack@BetzDearborn.com]
> >Sent: Wednesday, May 19, 1999 11:07 AM
> >To: 'POWERH-L@lists.swau.edu'
> >Subject: FW: Powerhouse - Allbase problem
> >
> >
> >Dear fellow users of PowerHouse,
> >
> >as I already pointed out in a previous mail we are currently Y2K-testing on
> >HP3000/MPEiX 6.0 with Allbase G3.02 and PH819C4. We are experiencing
> locking
> >problems which my colleague described in a mail to Cognos. As always this
> is
> >urgent so I want to put the question also to this list, somebody might have
> >experienced similar things already.
> >
> >Your help is kindly appreciated. THX in advance.
> >
> >Kind regards
> >Andreas Noack
> >Hercules BetzDearborn
> >Development Group Europe
> >Tel. : +32.16.395.290
> >Fax : +32.16.395.368
> >
> >The opinions expressed above are my very own. Due to this fact they may
> >change anytime without further notice.
> >
> >--- forwarded mail ---
> >
> >
> >> Case :
> >> I have two sessions open on the same Allbase environment and run the same
> >> QTP simultaneously. I am not selecting the same record in the file. The
> >> second session gives the SQL deadlock. But it seems if I do the same
> thing
> >> again without leaving QTP this doesn't happen. I have tried it several
> >> times and with several files and each time I have the same result. Below
> >> you can find the code that I used and a description of the file in SQL.
> >>
> >> /* This file was created with a user language environment of */
> >> /* NATIVE-3000 */
> >>
> >>
> >> CREATE PUBLIC TABLE GEN.IC_INV_HEADER
> >> (PLANT_NBR SMALLINT NOT NULL,
> >> IC_INV_NBR INTEGER NOT NULL,
> >> SUB_CD CHAR( 1) NOT NULL,
> >> CUST_NBR INTEGER NOT NULL,
> >> APPL_SEQ SMALLINT NOT NULL,
> >> INV_DT DATE NOT NULL,
> >> INV_AMT DECIMAL(10, 2) NOT NULL,
> >> SHIPM_NBR INTEGER NOT NULL,
> >> NEXT_D_SEQ_NBR SMALLINT NOT NULL,
> >> NEXT_C_SEQ_NBR SMALLINT NOT NULL,
> >> IC_INV_STAT CHAR( 1) NOT NULL,
> >> AUTO_CN_STAT CHAR( 1)
> >> DEFAULT ' ' NOT NULL
> >> )
> >> IN DISTRFS;
> >>
> >> COMMIT WORK;
> >>
> >> CREATE UNIQUE INDEX IC_INV_KEY
> >> ON GEN.IC_INV_HEADER
> >> (PLANT_NBR ASC,
> >> IC_INV_NBR ASC);
> >>
> >> COMMIT WORK;
> >>
> >> ------------
> >>
> >> NV :qtp
> >>
> >> Q T P (PowerHouse 8.19.C4)
> >> Copyright 1999 COGNOS INCORPORATED
> >> BETZ EUROPE INC
> >>
> >> > access ic_inv_header in haasrode
> >> > choose plant_nbr 90, ic_inv_nbr parm
> >> Choose viaindex IC_INV_KEY.
> >> > output ic_inv_header update
> >> > item auto_cn_stat final "X"
> >> > go
> >>
> >> Executing request 1 ...
> >>
> >> I/C invoice nbr:: 500030
> >> I/C invoice nbr::
> >>
> --------------------------------------------------------------------------
> >> -----
> >> Data access error.
> >>
> >> File: IC_INV_HEADER
> >> Linkitem: PLANT_NBR
> >> Linkitem: IC_INV_NBR 500030
> >>
> >> Action Taken: Run terminated.
> >>
> --------------------------------------------------------------------------
> >> -----
> >> DMS-E-DEADLOCK, A lock conflict with another process has been encountered
>
> >> during operation 'open cursor'.
> >> File: IC_INV_HEADER
> >> Linkitem: PLANT_NBR
> >> Linkitem: IC_INV_NBR 500030
> >>
> --------------------------------------------------------------------------
> >> -----
> >> Deadlock detected. (DBERR 14024)
> >> File: IC_INV_HEADER
> >> Linkitem: PLANT_NBR
> >> Linkitem: IC_INV_NBR 500030
> >>
> --------------------------------------------------------------------------
> >> -----
> >> Deadlock detected. (DBERR 14024)
> >> File: IC_INV_HEADER
> >> Linkitem: PLANT_NBR
> >> Linkitem: IC_INV_NBR 500030
> >>
> --------------------------------------------------------------------------
> >> -----
> >>
> >> Records read:
> >> IC_INV_HEADER 0
> >>
> >> Transactions processed: 0
> >>
> >> Records processed: Added Updated Unchanged
> >> Deleted
> >> IC_INV_HEADER 0 0 0
> >> 0
> >>
> >> Finished.
> >>
> --------------------------------------------------------------------------
> >> -----
> >> Changes made since the last commit have been rolled back to a stable
> >> state.
> >>
> >> Therefore, statistics reported may be incorrect.
> >>
> --------------------------------------------------------------------------
> >> -----
> >>
> >--- end of forwarded mail ---
> >= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> >Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> >Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> >powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> >This list is closed, thus to post to the list, you must be a subscriber.
> >= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> >Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> >Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> >powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> >This list is closed, thus to post to the list, you must be a subscriber.
> >
> >
> +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
> +
> + Jeff Hoffman + e-mail: J.Hoffman@cchs.usyd.EDU.AU
> +
> + Director +
> +
> + WoniCon Pty Ltd + ,-_|\
> +
> + 21 Prices Circuit + voice: +61 2 9542 1527 / \
> +
> + Woronora NSW 2141 + fax: +61 2 9521 3979 \_,-._*
> +
> + AUSTRALIA + v
> +
> +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
> +
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> This list is closed, thus to post to the list, you must be a subscriber.
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> This list is closed, thus to post to the list, you must be a subscriber.
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.