GetNextNumberWithGapsCommit function

Syntax

GetNextNumberWithGapsCommit(record.field, max_number, increment [, WHERE_Clause, paramlist])

Where paramlist is an arbitrary-length list of values in the form: 

var1 [, var2] ...

Description

Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.

This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note:

A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to multiplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

Parameters

Parameter Description

record.field

Specify the record and field names for the field for which you want the new number. This is the recommended way to identify the field.

max_number

Specify the highest allowed value for the field you're incrementing. You can specify up to 31 digits for this value.

increment

Specify the value you want the numbers incremented by. You can specify up to 31 digits for this value.

WHERE_Clause

Specify a SQL Where clause for maintaining multiple sequence numbers.

paramlist

Specify the parameters for the SQL Where clause.

Returns

A number value equal to the highest value of the field specified plus one increment.

The GetNextNumberWithGapsCommit function returns an error if the value to be returned would be greater than max_number. The function returns one of the following:

Numeric Value Constant Value Description

Number

None

The new number

-1

%GetNextNumber_SQLFailure

SQL failure

-2

%GetNextNumber_TooBig

Number returned is too large, beyond max_number.

-3

%GetNextNumber_NotFound

No number found, invalid data format.

Example

The following PeopleCode increments the MCF_EMAIL_ID field by one and returns the new value, committing immediately. 

&LAST_AUTO_NBR = GetNextNumberWithGapsCommit(MCF_INSTALL.MCF_EMAIL_ID, 2147483647,
 1);

The above code produces output similar to the following:

1-192    10.39.54    0.320 Cur#2.1980.DB844901 RC=0 Dur=0.320 Connect=Secondry
/DB844901/testdb2/
1-193    10.39.54    0.000 GNNWGC ---- Successful obtain Second DB connection 
1-194    10.39.54    0.010 Cur#2.1980.DB844901 RC=0 Dur=0.010 COM Stmt=UPDATE PS_
MCF_INSTALL SET MCF_EMAIL_ID = MCF_EMAIL_ID + 1 
1-195    10.39.54    0.000 Cur#2.1980.DB844901 RC=0 Dur=0.000 COM Stmt=SELECT MCF_
EMAIL_ID FROM PS_MCF_INSTALL 
1-196    10.39.54    0.000 Cur#2.1980.DB844901 RC=0 Dur=0.000 Commit
1-197    10.39.54    0.000 Cur#2.1980.DB844901 RC=0 Dur=0.000 Disconnect

Considerations Using GetNextNumberWithGapsCommit

The following restrictions apply to the GetNextNumberWithGapsCommit function:

  • PeopleSoft does not recommend Using both the GetNextNumberWithGapsCommit function and the GetNextNumber function in the same application, on the same table, in the same unit of work. This can lead to lock contention or deadlocking.

  • For a DB2 for z/OS database, isolate the table that contains the sequence number to its own tablespace and set the locksize parameter to row.

Related Topics