Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    19

    Unanswered: Generating alphanumeric sequences in DB2

    Hi,
    We are having a requirement in our project to generate alphanumeric sequences in DB2 Like A0001, A0002...etc instead of pure numeric sequences.
    So can you please let me know how can we create such sequences.?
    Does DB2 provide any tool/utility/script which will help to generate such sequences?

    Thanks
    Ravi

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will have to do that yourself.

    You need to create a table that holds the current value of the sequence. Something like:

    create table MySequence (current_alpha char(1), current_number int)

    Then create a stored procedure that reads the current value of the table and generate the next value. It then updates the table with that value so the next invocation will get a different sequence. You must have the stored procedure perform in its own Unit of Work. This will prevent duplicate from being generated.

    Andy

  3. #3
    Join Date
    Apr 2010
    Posts
    19

    Generating alphanumeric sequences in DB2

    Hi,
    Sorry to ask but can you please give me a sample code of how to do it.

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Something like this:

    Code:
    CREATE PROCEDURE MySchema.P_GET_NEXT_SEQUENCE ( out Sequence varchar(30),
                                                 OUT SQLSTATE_OUT CHAR(5),
                                                 OUT SQLCODE_OUT INT,
                                                 OUT MESSAGE_TEXT_OUT VARCHAR(1000))
        SPECIFIC LCADUSER.P_GET_NEXT_SEQ
        DYNAMIC RESULT SETS 0
        MODIFIES SQL DATA
        NOT DETERMINISTIC
        CALLED ON NULL INPUT
        LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL STORED PROCEDURE
    ------------------------------------------------------------------------
    P1: BEGIN
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE z_current_alpha char(1) default 'A';
        DECLARE z_current_number INT DEFAULT 1;
    
        DECLARE CURSOR1 CURSOR  FOR
           SELECT current_alpha,current_number FROM MySchema.MySequence FOR UPDATE WITH RS;
    
        -- DECLARE HANDLER
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
           BEGIN
              GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
              SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
                  FROM SYSIBM.SYSDUMMY1;
              SET MESSAGE_TEXT_OUT = MESSAGE_TEXT_OUT || ' [IN P_GET_NEXT_SEQ]';
           END;
    
        DECLARE EXIT HANDLER FOR SQLWARNING
           BEGIN
              GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
              SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
                  FROM SYSIBM.SYSDUMMY1;
              SET MESSAGE_TEXT_OUT = MESSAGE_TEXT_OUT || ' [IN P_GET_NEXT_SEQ]';
           END;
    
    
        ROLLBACK; -- MUST START A NEW UOW
    
        OPEN CURSOR1;    -- SET UPDATE LOCK ON ROW
        FETCH CURSOR1 INTO z_currrent_alpha,z_current_number;
    
        -- If the alpha need to change based on some criteria, then you would do that logic here
        
        SET z_current_number = z_current_number + 1;
    
        IF (z_current_number = 1)  -- THIS WILL INSERT IF THERE WAS NO ROW 
            THEN 
               set z_current_alpha = 'A';
              INSERT INTO MySchema.MySequence  VALUES (z_current_alpha,z_current_number);
            ELSE UPDATE MySchema.MySequence SET (current_alpha,current_number) = (z_current_alpha,z_current_number);
         END IF;
    
        CLOSE CURSOR1;
        COMMIT WORK;
    
        SET Sequence = z_current_alpha||char(z_current_number);
    
        SET SQLSTATE_OUT = SQLSTATE;
        SET SQLCODE_OUT = SQLCODE;
        SET MESSAGE_TEXT_OUT = NULL;
    
    
    END P1

  5. #5
    Join Date
    Apr 2010
    Posts
    19

    Generating alphanumeric sequences in DB2

    Thanks a lot for the sample code.
    I will try it out and will update you accordingly.

  6. #6
    Join Date
    Apr 2010
    Posts
    19

    Generating alphanumeric sequences in DB2

    i think we will have to add extra logic to generate sequences like
    B00O1,C0001,AB0001
    once A9999 i reached then we can start with B0001, till z9999..
    After that it should start with AB001..sth like this and it should continue like this

  7. #7
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by infyravi View Post
    i think we will have to add extra logic to generate sequences like
    B00O1,C0001,AB0001
    once A9999 i reached then we can start with B0001, till z9999..
    After that it should start with AB001..sth like this and it should continue like this
    if you want to maintain a sort sequence, - as in which came first, suggest:

    A0001
    ...
    A9999
    AA001
    AA999
    ...
    ZZZZ9
    ZZZZZ
    Dick Brenholtz, Ami in Deutschland

  8. #8
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    We are having a requirement in our project to generate alphanumeric sequences in DB2 Like A0001, A0002...etc instead of pure numeric sequences.
    love these threads when there is a requirement.

    PERSONAL OPINION ON
    just because some second-rate-fat-head remembered from a statistics class,
    that the permutations of 5 char 1-0,a-z > 6,000,000
    and he thinks this bit-saving-BS should be implemented,
    does not mean that it should.
    on the other-hand some fool possibly designed a form which will only contain
    5 characters and CAN'T be changed.

    the calculation of the next sequence number should be performed in application code and not by DB2. The resources involved to generated this kind of convoluted sequence is not worth the effort.

    PERSONAL OPINION OFF
    Dick Brenholtz, Ami in Deutschland

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The travel industry has long used such alpha-numeric codes for reservation numbers (also known as PNR or Record Locator in the airline business). There are programs to generate such numbers, but this subject is probably outside the realm of databases or SQL.

    For my company, I am given the list of numbers by the developers (that they generate using a program they have) and I have to load the codes in a "next available number" table for use as by applications in other tables. I have loaded 2 billion codes at one time for future use by our application.

    We also have the requirement that numbers be in random order (not consecutive), because it is considered to be proprietary information about how many reservations are booked each day. Obviously, even though the numbers are in random order, they are all unique. In addition the program that generates the alpha-numeric number has to skip any "objectional" words or combination of letters that might randomly occur.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by Marcus_A View Post
    The travel industry has long used such alpha-numeric codes for reservation numbers (also known as PNR or Record
    ...
    that generates the alpha-numeric number has to skip any "objectional" words or combination of letters that might randomly occur.

    that's all very interesting, but has nothing to do with the question posed in this thread.
    Dick Brenholtz, Ami in Deutschland

  11. #11
    Join Date
    Apr 2010
    Posts
    19

    Generating alphanumeric sequences in DB2

    I am somewhat confused here now..
    Should we use DB2 sp to create such alphanumeric numbers ?

  12. #12
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by infyravi View Post
    I am somewhat confused here now..
    Should we use DB2 sp to create such alphanumeric numbers ?
    First, you should define your rules about the 'generated alphanumeric sequence numbers'.

    after that, I imagine someone
    (there is some amazing talent on this board and they are capable of turning water into wine)
    can provide a DB2-SQL solution for you.

    The first solution provided by ARWinner is no longer applicable because you decided to change the rules of the algorithm.

    None of the members are being paid for the time they invest in creating a solution for you.

    This is not an easy task, DB2 deals strictly within data types,
    and you are mixing two data types in one column,
    which means that each char position within the column must be handled in a method that is not inherent to db2,
    something an application language can do easily.
    Dick Brenholtz, Ami in Deutschland

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dbzTHEdinosaur View Post
    that's all very interesting, but has nothing to do with the question posed in this thread.
    It has to do with your assertion that such requirements are bogus.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by Marcus_A View Post
    It has to do with your assertion that such requirements are bogus.
    no the bogus requirement is to call a 5 char alpha-numeric field a sequence number.
    as apposed to the airline requirements, the sequence matters for the TS (Topic starter)
    as well as I doubt that the airline requirements call for a 5 char number.

    with 5 char you are limited to 6,000,000+ (minus 00001 thru 99999) permutations,
    and 6 million is not a very big number now-a-days.

    The TS's application is being short-sighted.
    limiting to 5 char sounds like vsam technology and not db2.
    Dick Brenholtz, Ami in Deutschland

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by infyravi View Post
    I am somewhat confused here now..
    Should we use DB2 sp to create such alphanumeric numbers ?
    No. If the application developers have this requirement, then tell them to give you a file of all available numbers that you will load into a table that will be used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •