5 Replies Latest reply on Nov 13, 2012 1:29 PM by 818574

    Issue with GetNextNumberWithGapsCommit function

      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
          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
            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
              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 */

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

              Hope it will work!!!!.

              • 4. Re: Issue with GetNextNumberWithGapsCommit function
                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:




                Enable the %UpdateStats meta-SQL construct.


                Disable the %UpdateStats meta-SQL construct.


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


                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.


                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
                  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.