Next available number...

[bryce4president]

Hey Guy,
I know this has been discussed before, probably about 1000 times, but I can't seem to find anything. As many of you have done before, I need to store a value that is the Next Number to be used for some purpose. Common uses would be customer number, order number, etc. What are some of the ways to do this, which would you think is easiest? This is really just a down and dirty type of thing that will only be needed for about the next year... I need to generate a Catalog Number... basically a 6 digit number, probably seeded above 100000. Is there some user space or something? Or use a file and increment with SQL? What do you think?

[tomholden]

personally for a Q&D short term process I'd simply use a data area for this.

[DeadManWalks]

id go with max(CatalogNumber), then update it with Max + 1. The data area is good, if only one user is going to be using it at a time, or it could blow up. A simple (R) will fix the blow up, but I would not want the calls.

[Mark A Smith]

An interface control file with a serial number field works as well.

Choose your flavor.

[B.Hauser]

Why not using an SQL SEQEUENCE Object, which is under the cover nothing else than a data area, but you do not have to care about locking and managing the next number:

CREATE SEQUENCE MySchema/MySeqObj 
	AS Integer Start With 1 Increment by 1;

To get the next value you can use it for example in an insert statement as follows:

Insert into MySchema/MyTable (Col1, Col2, .... ColN)
Values (Next Value For MySeqObj, col2Val, ... ColNVal);

You also can retrieve the next value with embedded SQL:

/Free
   Exec SQL   Set :NextValue = Next Value For MySeqObj;
   ...

Because a sequence is a independent object, it can be used where ever needed, for example for multiple columns within the same table, for a single or multiple columns in multiple tables, or whenever you simply need a unique value.

Birgitta

[dhanuxp]

Why not create table with auto-increment field ?

Create Table  qtemp/mytable                            
(MyId Integer Generated Always as Identity             
        (Start with 1 Increment By 1), XX CHAR (10 ) )

PS:
I found difficulties in calling insert statement with sequence object using Java.
(When we have to mention two libraries in single statement)
but if you use hibernate, this also works fine!!

Insert into MySchema.MyTable (Col1, Col2, .... ColN)
Values (Next Value For QGPL.MySeqObj, col2Val, ... ColNVal);

dhanuxp

[B.Hauser]

PS:
I found difficulties in calling insert statement with sequence object using Java.
(When we have to mention two libraries in single statement)
but if you use hibernate, this also works fine!!

dhanuxp

I assume it is not because you are using JAVA but because you are using SQL Naming instead of System Naming. The difference between the naming conventions is much more than simply separating the library and (database) object by either a slash or a period.
When using SQL naming all unqualified specified tables or views are searched within a single Schema/Library (=Default or Current Schema).
The current schema can be set by executing the SQL command SET CURRENT SCHEMA.

If you have to access tables or views in multiple schemas with SQL naming, you either have to qualify them or create aliases in your main data library (Default Schema) that point to the database objects in the other schemas.

All other database objects such as sequences or stored procedures are searched in the SQL path. Contrary to the current schema, in the SQL path multiple schemas/libraries can be listed and are searched like the library list.
The SQL PATH can be set by executing the SQL Command SET PATH. The special value *LIBL can be specified with the SET PATH statement.

Birgitta

[dhanuxp]

I assume it is not because you are using JAVA but because you are using SQL Naming instead of System Naming.

Birgitta

That is it Birgitta.. changed the naming into "system" & works great!
everyday I learn new things from you... Thank you so much!

[bryce4president]

I really like the sequence object idea. I didn't even know about that. I think that's the way to go. Quick, easy, down and dirty... and oh so effective.

[gcraill]

Why not using an SQL SEQEUENCE Object, which is under the cover nothing else than a data area, but you do not have to care about locking and managing the next number:

Birgitta

*Like*

[bryce4president]

Ok, I'm about to set up this SQL Sequence Object. How would you document that its been created? Its kind of one of those things that you just plop the SQL statement into STRSQL and bam, its created... Do you guys do something special to document these kinds of things in your systems??

[B.Hauser]

I normally use either System iNavigator - Database, position on the database object, right click and execute generate SQL to store the SQL statement either as source physical file member or SQL script/IFS file. A source physical file member can be executed with the CL command RUNSQLSTM and an SQL Script can be executed with System iNavigator.

Currently I have to store all my SQL scripts as source physical file member (in File QSQLDDL - All Tables, Views, Indexes, Sequences and QSQLDML - All stored procedures , User defined (Table) functions.

Birgitta