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 Profile Log in to check your private messages Log in
 
Generate SEQUENCE on a column.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Sat Apr 03, 2010 11:19 am    Post subject: Generate SEQUENCE on a column.
Reply with quote

SEQ_NUM CLASS_ROOM STUDENT
1 ROOM1 STUD1
2 ROOM1 STUD2
3 ROOM1 STUD3

1 ROOM2 STUD2
2 ROOM2 STUD5

1 ROOM4 STUD4

1 ROOM9 STUD3

I have above values stored in a table. I want to generate the SEQ_NUM field on UNIQUE combination of (CLASS_ROOM and STUDENT) fields. Say if I want to INSERT one row for CLASS_ROOM,STUDENT as (ROOM2,STUD1), the SEQ_NUM field should be generated as 3. Already there are 2 students in ROOM2, so who ever coming new, should be assigned a SEQ_NUM value 3. Same SEQ_NUM can be used for a different CLASS_ROOM,STUDENT combinations.I can do this by using a user written program but can we do it through JCL itself,by using some utilies or by setting SEQ_NUM column as IDENTITY column or something? I tried to use a SEQUENCE, but I have no control over it. It keeps on generating new values for every insert. I have such (CLASS_ROOM,STUDENT)1000 rows in a flat file and would like to insert it into above table.
    Back to top
    View user's profile Send private message

    dbzTHEdinosauer

    Global Moderator


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

    PostPosted: Sat Apr 03, 2010 6:08 pm    Post subject:
    Reply with quote

    Vimal wrote:
    we do it through JCL


    since you have been a board member for only a couple of months,
    you probably have not had the chance to notice,
    THAT NOTHING IS DONE THRU JCL.

    But, you may invoke a program/utility with your JCL to accomplish said task.

    now, I figure this question stems from a class requirement,
    but I will give you the benefit of doubt and
    assume some business requirement for a real live production system has suddenly arisen.

    you were not clear about the 'user written program' and what it would accomplish.

    clearly, you need to assign a sequence number that is one greater than the existing sequence number for a CLASS_ROOM, STUDENT combination.

    to perform this with a db2 load utility:

    I would define the SEQUENCE column as integer.
    I would put no constraints on the SEQUENCE column
    I would define an index on CLASS_ROOM, STUDENT, SEQUENCE
    I would populate the flat file sequence number with zero
    I would write an after INSERT Trigger to:
    1. calculate the next SEQUENCE for a CLASS_ROOM, STUDENT combination
    2. UPDATE the current row with the calculated SEQUENCE
    Back to top
    View user's profile Send private message
    PeterHolland

    Global Moderator


    Joined: 27 Oct 2009
    Posts: 2441
    Location: Netherlands, Amstelveen

    PostPosted: Sat Apr 03, 2010 6:41 pm    Post subject:
    Reply with quote

    STUD1 etc.?

    Being a stud myself i never got a sequence number from the thousands
    of my girl friends. icon_cool.gif
    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 -> DB2 All times are GMT + 6 Hours
    Page 1 of 1

     

    Search our Forum:

    Similar Topics
    Topic Author Forum Replies Posted
    No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
    No new posts Generate public-private key in batch ... vasanthz All Other Mainframe Topics 3 Fri Sep 15, 2017 9:20 am
    No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
    No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
    No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

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