This discussion is archived
5 Replies Latest reply: Nov 13, 2012 5:29 AM by 818574 RSS

Issue with GetNextNumberWithGapsCommit function

701459 Newbie
Currently Being Moderated
I am using the GetNextNumberWithGapsCommit function to get the transaction number or sequence number to field of one record. But this function returns one of the sequence numbers that are already in use in the record.Have any one faced this issue?. My requirement is like -- I have to get a unique identification number for every row in my record. Issue here is that the component is highly used by the end users and the transaction/sequence numbers are getting duplicated sometimes. Hence I have used this function but still the problem persists. Any ideas or solutions are higly appreciated.
  • 1. Re: Issue with GetNextNumberWithGapsCommit function
    TBower-Oracle Explorer
    Currently Being Moderated
    Hmm.... I have not heard of this problem before. But one thing to understand and check is that GNNWGC is designed to do it's processing in a secondary database connection so that the update will be outside of the current transaction boundary.

    So, one thing I would suggest is to be sure that secondary database connections have not been completely disabled in the Application Server domain (assuming this is on-line...not batch)..i.e. be sure DbFlags does not equal 4 (or 5, 6, or 7). If your PeopleTools version is prior to 8.50, I would also suggesting setting DbFlags=8 to enable the secondary connections, but disable their persistence (various bugs in Tools 8.46 thru 8.49...a longer story). If 8.50+, would suggest DbFlags=0.

    I suppose that what could be happening is that without the secondary db connection GNNWGC is reading your number from a "read-consistent" view of your table (i.e. rollback segs if Oracle), and getting into a race condition between transactions to update the number. But without the secondary connection, the "Commit" part of GNNWGC should behave just like "GetNextNumberWithGaps", so I don't really understand how you could get dups.
  • 2. Re: Issue with GetNextNumberWithGapsCommit function
    user8671473 Newbie
    Currently Being Moderated
    Is there a chance some other function / page / component is using it's own (less sophisticated) logic to grab a transaction ID?
  • 3. Re: Issue with GetNextNumberWithGapsCommit function
    731696 Newbie
    Currently Being Moderated
    Hey I have another idea to implement this try this one,
    Suppose you have one table PS_EMP on level 0 where two flds are EMPID and EMPNAME
    in this two fields EMPID you are on Add New Value Pge(Search Key on record).

    on this EMPID write peoplecode on its Record field rowinit event like this,

    if %mode="A" then
    SQLEXEC("Select MAX(EMPID) from PS_EMP",&max_empid); /* to get maximum employee id from database */
    EMP.EMPID.Value=&max_empid+1; /* Assign maximum employee id from database + 1 to EMPLID field on page */
    end-if;

    Gray(EMP.EMPID);/* make field non editable or dispaly only*/

    Hope it will work!!!!.

    Thanks,
    Amit
  • 4. Re: Issue with GetNextNumberWithGapsCommit function
    921638 Newbie
    Currently Being Moderated
    It is beacuse the GetNextNumberWithGapsCommit function makes Secondary DB connection for updating & retriving the seqnumber. This can be resolved by setting the DbFlag value of appsrvr.cfg to 0 or 8.

    The following values are valid for the DbFlags parameter:

    Value

    Description

    0

    Enable the %UpdateStats meta-SQL construct.

    1

    Disable the %UpdateStats meta-SQL construct.

    2

    Ignore the Truncate command for DB2 UNIX/NT. Use Delete instead.

    4

    Disable a secondary database connection (used with the
    GetNextNumberWithGapsCommit PeopleCode function).

    This prevents the creation of a secondary database connection, bundling all
    SQL into a single unit of work. Without the additional database connection,
    the database row lock is held for a longer time, reducing concurrency in a
    multiple-user environment.

    Note. Analytic instance processing requires a secondary database
    connection, so if you're using analytic servers, ensure that this value is
    not set.

    8

    Disable a persistent second database connection (used with the
    GetNextNumberWithGapsCommit PeopleCode function).

    This creates a second database connection in each
    GetNextNumberWithGapsCommit call, then immediately closes the second
    connection. This keeps the number of database connections to a minimum, but
    requires each call to create a new database connection on demand.

    Note. The performance impact of making a new database connection is
    significant, especially in high volume user production environments. Don't
    use this setting without carefully considering its effect.
  • 5. Re: Issue with GetNextNumberWithGapsCommit function
    818574 Newbie
    Currently Being Moderated
    It's old question, hope my finds can be useful to others

    My case: we have 3 environments, program will popup error message in log(ORA-00022: invalid session ID; access denied) when using GetNextNumberWithGapsCommit function in TST and DEV environment , but it works in another environment.
    I searched form website, most solutoin tells need to set DBflag from 0 to 8 to fixed this issue, but in our case, 2 environment have same DBflag value, so still try to find another possibilities.

    It works after we Clean Cache of App server in TST and DEV environment...I don't know why.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points