Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    21

    Autoincrement fields

    Hi all!!
    Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
    Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

    Thank you very much!!!

    Cheers

  2. #2
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Autoincrement fields

    Hey Garcia,

    If you want to use an identity field, try "Generated by Default" instead of "Generated Always".

    Take a look at the Application Development Guide for more details on Generated Columns.

    HTH
    Fernando

    Originally posted by rgarcia
    Hi all!!
    Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
    Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

    Thank you very much!!!

    Cheers

  3. #3
    Join Date
    Jan 2003
    Posts
    21
    Hi Fernando!
    So how would be the whole syntax??
    instead of:
    "CODE INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"

    it should be:
    "BOARDTYPECODE INTEGER NOT NULL GENERATED DEFAULT (START WITH 1, INCREMENT BY 1)"

    Thank you again!!!

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    Garcia,

    The whole syntax should be:
    "BOARDTYPECODE INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)"

    But note: the main diference between "GENERATED ALWAYS" and "GENERATED BY DEFAULT" is the behaviour when the value for the identity field is provided.
    When you use the 'ALWAYS' type, you canīt provide the value for the Identity field, for it is generated ALWAYS using the auto-increment.
    When using the 'BY DEFAULT' the auto-increment will only be used when you donīt provide the value for the identity field. In case you specify the value for the field, the auto-increment value is not used and thus not incremented. It works exactly like a "DEFAULT" value for a column. So you have to be very careful when using this kind of column generation, IMO.

    Hope this cleared out your doubts.

    Fernando

    Originally posted by rgarcia
    Hi Fernando!
    So how would be the whole syntax??
    instead of:
    "CODE INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"

    it should be:
    "BOARDTYPECODE INTEGER NOT NULL GENERATED DEFAULT (START WITH 1, INCREMENT BY 1)"

    Thank you again!!!

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    You will need to be a bit cautious when using 'GENERATED BY DEFAULT'

    When you INSERT explicitly the identity column, the autoincrement field is not incremented(as Fernando has said) ie

    Table T1 : I INT GENERATED By DEFAULT AS IDENTITY, J INT

    INSERT INTO T1(j) VALUES(5)
    INSERT INTO T1(J) VALUES(10)
    SELECT * FROM T1

    I J
    -------
    1 5
    2 10

    INSERT INTO T1 values(3,15)

    SELECT * FROM T1

    I J
    -------
    1 5
    2 10
    3 15

    INSERT INTO T1(J) VALUES(20)


    SELECT * FROM T1

    I J
    -------
    1 5
    2 10
    3 15
    3 20

    Note the last two lines ... As I's value in the third row was inserted by the user, DB2 did not consider it when auto-incrementing ...

    You might face problems when using this type of column ...

    Cheers

    Sathyaram



    Originally posted by F.OHANA
    Garcia,

    The whole syntax should be:
    "BOARDTYPECODE INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)"

    But note: the main diference between "GENERATED ALWAYS" and "GENERATED BY DEFAULT" is the behaviour when the value for the identity field is provided.
    When you use the 'ALWAYS' type, you canīt provide the value for the Identity field, for it is generated ALWAYS using the auto-increment.
    When using the 'BY DEFAULT' the auto-increment will only be used when you donīt provide the value for the identity field. In case you specify the value for the field, the auto-increment value is not used and thus not incremented. It works exactly like a "DEFAULT" value for a column. So you have to be very careful when using this kind of column generation, IMO.

    Hope this cleared out your doubts.

    Fernando

  6. #6
    Join Date
    Aug 2003
    Posts
    1

    Re: Autoincrement fields

    Hi did you figure out the solution for this problem. I am stuck with the same problem.

    Thanks in advance.

    Originally posted by rgarcia
    Hi all!!
    Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
    Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

    Thank you very much!!!

    Cheers

Posting Permissions

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