Topic
1 reply Latest Post - ‏2012-10-19T10:27:19Z by SystemAdmin
JamesBrooks
JamesBrooks
2 Posts
ACCEPTED ANSWER

Pinned topic How can I get the numeric uid to use as a column "default" value?

‏2012-10-18T19:41:14Z |
Coming from Oracle I have a alter column definition like this:

create table MYFAVTBL ( "ID_USER" DECFLOAT(16));

ALTER TABLE MYFAVTBL ALTER COLUMN "ID_USER" SET WITH DEFAULT uid ;
Is there a way to do that in DB2 ?
Updated on 2012-10-19T10:27:19Z at 2012-10-19T10:27:19Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: How can I get the numeric uid to use as a column "default" value?

    ‏2012-10-19T10:27:19Z  in response to JamesBrooks
    Hi there,

    In DB2 there is a difference in users respect to Oracle. In Oracle you cannot have an orphan schema, i.e. any schema belong to a user and a user can own one schema or no schema at all. In DB2 you can have as many schemes as you want and any user can own any number of schemes including zero.

    You can create your schemes either implicitly, when a user creates an object (this is controlled by the IMPLICIT_SCHEMA authority), or explicitly by using the CREATE SCHEMA sentence in DB2.

    In Oracle exists the database user, i.e. you create the user by issuing the

    
    CREATE USER username IDENTIFIED BY password ...
    


    In DB2 the users are external users to the database and they do not have the same entity than in Oracle.

    Now, what you want to do depends on the information you need to collect in the ID_USER column. DB2 has a set of special registers that will provide you with the authorization ID. This special registers I am talking about and I think could be useful to you are: CURRENT_USER, SESSION_USER, SYSTEM_USER and USER. You need to check the values and the differences between them and choose the one that suits your purposes.
    Be aware that this special registers will return a VARCHAR(128) data type, therefore you will need to change the ID_USER column data type.

    Finally you can do an alter as follows, using for example CURRENT_USER:

    
    ALTER TABLE MYFAVTBL ALTER COLUMN 
    "ID_USER" SET DEFAULT CURRENT_USER
    


    As I said before there is a difference between users in DB2 and Oracle, I will recomend you to fully understand this difference to avoid pain in advance.

    Hope it helps,

    LooW