IBM Mainframe Forum

by **mainframe_novice** » Wed May 12, 2010 8:48 pm

Does Identity column which has values auto generated work if it is part of Primary Key

I have a table STUDENT_PAYMENT

STUDENT_ID CHAR(4) NOT NULL PRIMARY KEY,
PAYMENT_ID SMALLINT NOT NULL PRIMARY KEY,
AMOUNT DECIMAL (6,2)

I get Student Payment Data in a File each month which would be added to the table .

STUDENT_ID AMOUNT
A001 400.45
A003 701.00

If I Load this data to the table I want PAYMENT_ID auto generated sequentially for each STUDENT_ID + PAYMENT_ID combination .
Output should look like

Select all

STUDENT_ID PAYMENT_ID AMOUNT
A001 1 400.45
A003 1 701.00

Your help is greatly appreciated !

by **dick scherrer** » Sat May 15, 2010 12:39 am

Hello,

Suggest you use only standard terminology. "Auto generated" is not a db2 term i've ever seen.

A "sequence number" might be generated, but i do not believe this is what you want.

It appears from the small amount of sample posted, that you simply want to establish a default value of "1" for each row added, so when the table is CREATEd, specify this default value for the column.

IBM Mainframe Forum

Identity Column or Sequence Object

Identity Column or Sequence Object

Re: Identity Column or Sequence Object