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.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
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:
calculate the next SEQUENCE for a CLASS_ROOM, STUDENT combination
UPDATE the current row with the calculated SEQUENCE
