Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Generic Sequence number generator program
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Tue Jun 12, 2012 7:23 pm    Post subject: Generic Sequence number generator program
Reply with quote

Hi,
I need to write a generic 9 byte (Numeric) Sequence number generator program. This unique Sequence number is going to be stored it in a table.
The flow of the program is below mentioned,
1) Select the maximum value of the table and increment it.
Select max(Col1)
from table1.
2) Insert into the table.

This works fine for the batch. When i thought to implement the same logic in online.
If i implement this logic in online,
if a two users using same online program concurrently for generating a 9 byte unique sequence number then
i may fell into this below scenarios,
a) If the table have maximum value as 000000100 then for both user will get 000000101 and trying to insert it.
however it can be handled during insert, if there is second row inserting then it will be throw a sqlcode -803 and again the second user who missed(failed) it need to try the same transaction again.

So to make it in general i would need to change the logic in batch also.

Whether there is any other way to make the sequence number in generic to both online and batch application ?

Code:
 PROCEDURE DIVISION.                                           
                                                               
 0000-MAIN-LOGIC.                                               
                                                               
     EXEC SQL                                                   
        SELECT MAX(PART_NUMBER)                                 
               INTO                                             
               :WS-SEQ-NUM                                     
        FROM COST_TABLE1                                       
        FOR READ ONLY                                           
     END-EXEC                                                   
                                                               
     EVALUATE SQLCODE                                           
         WHEN 0                                                 
               COMPUTE WS-TEMP-SEQ-NUM = WS-SEQ-NUM + 1         
               END-COMPUTE                                     
               PERFORM 1000-INSERT-TABLE THRU                   
                       1000-EXIT                               
                                                               
            WHEN OTHER                                               
                                                                     
               MOVE +16                  TO WS-ABEND-CODE           
               MOVE SQLCODE          TO WS-DB2-SQLCODE         
               MOVE '0000'                TO WS-DB2-PARA           
                       
        END-EVALUATE 
                                               
        GOBACK.                                                     
                                                                     
0000-EXIT.                                                       
   EXIT.         

1000-INSERT-TABLE.                                             
                                                               
                                                               
    MOVE WS-TEMP-SEQ-NUM             TO                       
                                        COS-PART-NUMBER       
                                                               
    EXEC SQL                                                   
          INSERT INTO COST_TABLE1                               
                  (                                           
                   PART_NUMBER                                 
                  ,COST                                       
                 )                                             
                 VALUES                                       
                 (                                             
                   :COS-PART-NUMBER                           
                  ,:COS-COST                                   
                 )                                             
    END-EXEC               
         EVALUATE SQLCODE                                       
             WHEN 0                                             
                  CONTINUE                                     
             WHEN OTHER                                         
                MOVE +16                   TO WS-ABEND-CODE     
                                 
                MOVE SQLCODE               TO WS-DB2-SQLCODE   
                   
                MOVE '1000'                TO WS-DB2-PARA       
         END-EVALUATE                                           
         .                                                     
                                                               
     1000-EXIT.                                                 
         EXIT                                                   
         .                                                                                                                                       
                                                                     

Output:-
Code:
          PART_NUMBER  COST         
          INTEGER      DECIMAL(11,2)
          -PKEY1-----  -------------
******    **************************
000001    1               +222.00       
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jun 12, 2012 7:53 pm    Post subject:
Reply with quote

1. generic? it is anything but. it is a very specific value based on the existing max value.

2. why not have the same module that 'calculates the next sequence no'
CALLed by both batch and cics?

As far as batch, there is no need to have a separate routine,
because someone from the online could be working at the same time as the batch job.
i.e. loop on -803.

and, why SELECT, add, INSERT?

just insert max + 1.

what are the rules on 999999999 + 1?
billion - 1 is not uncommon these days.

and i image the sequence no (part no) is a decimal.
should be large integer.

PLUS, THIS IS A DB2 QUESTION. WHY IS IT IN COBOL?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 12, 2012 8:03 pm    Post subject:
Reply with quote

Have a field with Latest_sq_used something like that .

Update this field what was the last sequence + 1 that is being generated so the program which is going to need the next sequence can read from this field next time will pick the sequence from there.

So the first run you might need insert a static data

Just a thought
Back to top
View user's profile Send private message
Bill O'Boyle

CICS Moderator


Joined: 14 Jan 2008
Posts: 2502
Location: Atlanta, Georgia, USA

PostPosted: Tue Jun 12, 2012 8:33 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

Write a small called Assembler sub-program (can be used in Batch or On-Line), passing a 9-Byte Display-Numeric parm as well a 24-Byte workarea (for re-entrancy), which issues a Store-Clock into a Doubleword, then load the Doubleword into an 8-Byte register (LG), followed by a CVDG (Convert to Decimal Grande) into a 16-Byte Quadword, ensure the Quadword-sign nibble is 'F' and finally, unpack the Quadword into the 9-Byte Display-Numeric parm.

I can guarantee you that the returned-value in the 9-Byte parm will be unique 99.99% of the time.

The Doubleword and Quadword are composed of the 24-Byte workarea passed from the Caller.
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Wed Jun 13, 2012 4:27 pm    Post subject:
Reply with quote

Wouldn't CREATE SEQUENCE help?

Experts please correct me if I am wrong.
Back to top
View user's profile Send private message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Wed Jun 13, 2012 7:15 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

Thank you for replying Dick!

Dick, Yes The above piece of code which i have tried can be called in batch and CICS.But still my program is a Table specific(COST_TABLE1).
The Requirement is to write a Generic module which needs to populate 9 byte value for inserting a key for any clustering index for a DB2 table.

As per the requirement it wont available for that count( APPROXIMATELY 9,999,999). Hence 9 byte will be hold good for our requirement.

I tried it with DB2, but even accomplishing it with cobol or anyother solutions will be good. Going forward i will post it appropriately.
Back to top
View user's profile Send private message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Wed Jun 13, 2012 7:15 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

Thanks Pandora, the suggestion provided is of table specific.
The Requirement is to write a Generic module which needs to populate 9 byte value for inserting a key for any clustering index for a DB2 table.
Back to top
View user's profile Send private message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Wed Jun 13, 2012 7:16 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

Thanks Bill,
As i am new to the assembler level coding, i googled it and went through some of the assembler language manuals.
1) IBM High Level Assembler for MVS & VM & VSE Release 5
2) IBM High Level Assembler for MVS & VM & VSE
Release 2 Presentation Guide

It would be of great help if you could provide me with any other manuals or guidance for me to start with assmbler level coding.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 13, 2012 8:30 pm    Post subject:
Reply with quote

is this sequence no in the high order portion of the key?

do you need a random sequence no
or
a sequenced number based on table?
or
an unused no that is > last sequence generated.

random unique you will not get from Bill's solution.

the only way you will be 100percent certain that the sequence no generated has not been used before is to have some control over
the sequence no generated in relation to sequence no's previously generated.

you prattle on about generic and not specific to a table,
yet you showed in your opening post,
the INSERT SQL, which is not generic, it is specific to a table.

I think that you are deluding yourself in thinking billion - 1 is enough.

today, next year maybe, but it a couple of years you better be working at a new site,
because i have seen this "oh, a billion is more than enough"
bite many applications in the ass.
some could solve it by going negative, some could roll and start from 1 again,
but many were caught in a trap created by their own optimism.

sorta like voting for obama and thinking things would get better
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 13, 2012 8:41 pm    Post subject:
Reply with quote

just define the column as
- identifier if it has to be unique per table
- sequence if it has to be unique .
Back to top
View user's profile Send private message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Thu Jun 14, 2012 9:16 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

The requirement is to create a random unique number and also that the previously used number should not be used again . It should be unique always.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 14, 2012 9:21 pm    Post subject:
Reply with quote

Hello,

Quote:
The requirement is to create a random unique number and also that the previously used number should not be used again .
Who came up with such a "requirement" and why is there some need for it to be random?

If this is across multiple tables/keys how will you know if the "number" has been used elsewhere or has been used but later deleted?

It sounds like there is a rather major design flaw in this way of thinking.

Suggest you reread whay GUYC has provided.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Jun 14, 2012 9:44 pm    Post subject: Re: Reply to: Generic Sequence number in Cobol
Reply with quote

Muralikrishnan_Ram wrote:
The requirement is to create a random unique number and also that the previously used number should not be used again . It should be unique always.


Random and unique are conflicting requirements.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 14, 2012 9:45 pm    Post subject:
Reply with quote

The TS is caught in a trap.

if he uses the concept provided by GuyC
(and incidentally used by most professional IT shops throughout the planet)
his module is not needed

also, the notion of adding new rows at various 'random' positions has been proven - BS.

New rows (inserted) should not be out-of-sequence with the basic key.
we used to take a timestamp, and move 1 for 4 to the end, 20-26 to the begining,
(there were other idiot attempts at spacing out additional rows)
but it has been proven that it saves nothing,
helps nowhere,
and in general a waste of cycles to create this randomness.
old BS design.
Back to top
View user's profile Send private message
Muralikrishnan_Ram

New User


Joined: 28 May 2012
Posts: 6
Location: India

PostPosted: Thu Jun 14, 2012 9:46 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

It is not going to be used across multiple tables. It should be a random but unique number generator. It will be called to generate unique numbers concurrently .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 14, 2012 9:54 pm    Post subject: Re: Reply to: Generic Sequence number in Cobol
Reply with quote

Muralikrishnan_Ram wrote:
It is not going to be used across multiple tables. It should be a random but unique number generator. It will be called to generate unique numbers concurrently .


WTF does that mean?

like arguing politics with a 'progressive'.

ignore facts, change the requirements with each post,

but,
i imagine this is just some poor guy given a requirement by
jerks held in 1990's methodology.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 14, 2012 9:57 pm    Post subject:
Reply with quote

Take the current timestamp and remove dots and dashes and apend with 1 billionth of a second
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Jun 14, 2012 10:01 pm    Post subject: Reply to: Generic Sequence number in Cobol
Reply with quote

the topic is just becoming a useless waste of resources
will be locked in a while icon_cool.gif
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Thu Jun 14, 2012 10:06 pm    Post subject:
Reply with quote

Experts, as I have previously mentioned-
Quote:
Wouldn't CREATE SEQUENCE help?

Experts please correct me if I am wrong.

Won't selecting the NEXT VALUE of the SEQUENCE help?

One of our teams will soon be changing one of the systems with such a design for online to avoid contention. Could you guide me if this would go wrong some how. As far as I see this looks promising.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 14, 2012 10:11 pm    Post subject:
Reply with quote

Hello,

For whatever reason, there is a belief that the number needs to be random.

What business reason might there be for such a "requirement"?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts A sample of exit program for exit XMN... lind sh CICS 2 Mon Oct 03, 2016 5:07 pm
No new posts Need points to tune IMS Checkpoint Pr... satish.ms10 IMS DB/DC 2 Fri Sep 30, 2016 4:12 pm
No new posts ISPF program to set SCRNAME prino TSO/ISPF 5 Fri Sep 30, 2016 2:18 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us