Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: Creating database

    Hallo everyone,

    in order to understand the basic administration, I decided to ignore the default instance the DB2-installation created for me. So I created a new instance and all the required groups and users:

    sudo groupadd db2iadm2
    sudo groupadd db2fadm2
    sudo groupadd dasadm2

    sudo useradd -g db2iadm2 -m -d /home/db2inst2 db2inst2
    sudo useradd -g db2fadm2 -m -d /home/db2fenc2 db2fenc2
    sudo useradd -g dasadm2 -m -d /home/dasusr2 dasusr2

    sudo ./db2icrt -u db2fenc2 db2inst2

    I have now a new instance called db2inst2.
    The user of this instance is also called db2inst2.

    I am kinda confused, what I suppose to do next. Would be the next step to directly create a database? In order to do so, is it necessery to attach to the instance db2inst2 first or I can create a database directly as user db2inst2?

    Moreover, I got some suggestionst NOT to use the user db2inst2 to create a database. Instead, I should create a user just for creating a database, after that I should remove this user.

    But, I am thinking: if I will remove this user afterwards, why shouldnt I create the database directly from user db2inst2?

    Thank you for your comments.

    Regards,
    Ratna

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?


    You need to configure the instance first. Then you need to start it. Then you can create databases under it.

    Andy

  3. #3
    Join Date
    May 2012
    Posts
    155
    Hallo Andy,

    thanks for the reply. I am using DB2 9.7 Express-C under Ubuntu. Could you please tell me the things I have to configure in the instance you meant?

    Thank you.

    Regards,
    Ratna

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ratnalein View Post
    Moreover, I got some suggestionst NOT to use the user db2inst2 to create a database. Instead, I should create a user just for creating a database, after that I should remove this user.
    Logon as db2inst2 and create the database. db2inst2 will be the instance owner and should not be deleted.You got a bad "suggestion" from somewhere, and just ignore it. You can create a new id in Linux and grant authority to it such as grant dbadm on the database, but don't get rid of db2inst2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2012
    Posts
    155
    Hallo Marcus_A,

    1. Log on as user db2inst2 and create the database.
    2. Leaving user db2inst with all the authorites as they are.
    3. Create a user for the database designing.
    4. Create users for the applications(SELECT, INSERT, etc)

    If this is an acceptable constellation, which user should get SECADM?
    Should the users on point 4 get DBADM or just DATAACCESS?

    Thank you..

    Regards,
    Ratna

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ratnalein View Post
    Hallo Marcus_A,

    1. Log on as user db2inst2 and create the database.
    2. Leaving user db2inst with all the authorites as they are.
    3. Create a user for the database designing.
    4. Create users for the applications(SELECT, INSERT, etc)

    If this is an acceptable constellation, which user should get SECADM?
    Should the users on point 4 get DBADM or just DATAACCESS?

    Thank you..

    Regards,
    Ratna
    Create one or more users for the applications, and grant them insert, update, delete, and select access as required. You may have to grant use or execute on SP's, sequences, etc, if you have those.

    It is not necessary to use every security feature of the database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    As per what Marcus said, give them select, insert, update, delete as REQUIRED and not by default e.g. someone running reports only shouldn't get update access
    Don't allow ordinary users to have DBADM.
    DATAACCESS gives the user load auth and if they don't handle that correctly you may end up with tablespaces in backup pending.
    Also, we don't allow ordinary users to have access to the catalog views that have authorisations etc

  8. #8
    Join Date
    May 2012
    Posts
    155
    Hallo Marcus and Andy,

    okay, thank you..

    One question:

    I created the instance db2inst2 (instance owner is db2inst2):

    sudo ./db2icrt -u db2fenc2 db2inst2

    I am assuming, before creating a database, I dont need to configuration the instance first?

    Because, after the instance is created, I found this:
    In the folder from instance owner db2inst1 (installation wizard created automatically this user), I have 2 folders:
    1. db2inst1
    2. sqllib

    But in the folder from instance owner db2inst2 (I created with the above command), there is only one folder:
    1. sqllib

    Is it because in the instance db2inst2 still no database created?

    Thank you..

    Regards,
    Ratna

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ratnalein View Post
    Hallo Marcus and Andy,

    okay, thank you..

    One question:

    I created the instance db2inst2 (instance owner is db2inst2):

    sudo ./db2icrt -u db2fenc2 db2inst2

    I am assuming, before creating a database, I dont need to configuration the instance first?

    Because, after the instance is created, I found this:
    In the folder from instance owner db2inst1 (installation wizard created automatically this user), I have 2 folders:
    1. db2inst1
    2. sqllib

    But in the folder from instance owner db2inst2 (I created with the above command), there is only one folder:
    1. sqllib

    Is it because in the instance db2inst2 still no database created?

    Thank you..

    Regards,
    Ratna
    I don't think you need to worry about the other folder. Try and it and see.

    However, there is a problem in that you did not specify the port (or service name that references a port) when you created the instance. So you will have to update the database manager configuation and put in a port/service name, and you may have to run a db2set command to enable TCPIP access. Check the db2set -all output from db2inst1 and make sure db2inst2 is the same.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, you should configure the instance.

    The only time you will get something implicitly under the HOME directory other than sqllib is when you create a database in the HOME directory. You can create it elsewhere and nothing new should show up under the HOME directory.

    Andy

  11. #11
    Join Date
    May 2012
    Posts
    155
    Hallo Marcus_A and ARWinner,

    I was trying to configure the instance. In order to do so, I did this as suggested:

    ratna@ratnaslaptop:~$ sudo su - db2inst2
    [sudo] password for ratna:
    $ db2start
    SQL1026N The database manager is already active.
    $ db2set -all
    [g] DB2SYSTEM=ratnaslaptop
    [g] DB2INSTDEF=db2inst1
    [g] DB2COMM=TCPIP
    [g] DB2ADMINSERVER=dasusr1
    $ exit
    ratna@ratnaslaptop:~$ db2set -all
    [i] DB2COMM=tcpip
    [g] DB2SYSTEM=ratnaslaptop
    [g] DB2INSTDEF=db2inst1
    [g] DB2COMM=TCPIP
    [g] DB2ADMINSERVER=dasusr1

    There is a wierd thing: as we can see above, in the user file from db2inst2, the value from DB2INSTDEF=db2inst1

    This is not correct right? I am sure, I created the instance with this command:
    +sudo ./db2icrt -u db2fenc2 db2inst2+

    So why is the value DB2INSTDEF=db2inst1 ?

    If this is okay and everything is right, what would be the next step to configure the instance db2inst2 ?

    Thank you for the comments.

    Regards,
    Ratna

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you look up the meaning of DB2INSTDEF in the manual? This is the default instance (usually first one created). Since it is a "[g]" setting, it is global, and there can only be one per system. Please read the DB2 documentation before asking questions.

    Notice there is an [i] setting missing on db2inst2. Not sure if it matters due to global setting, but I would set if I were you. Next time include the port when you use the create instance command.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    May 2012
    Posts
    155
    Hallo Marcus_A,

    thank you for the advice. I did try to look at the documentation, but to be honest, I could not understand directly what it meant. But with your help, I now understand what these [g] and [i] meant. You said:

    [I]Notice there is an setting missing on db2inst2

    I think, you meant this one [i] DB2COMM=tcpip, which is missing in the new created instance db2inst2.

    I have set it now, with this command (at the prompt of user db2inst2):
    db2set -i db2inst2 DB2COMM=TCPIP

    After that, I checked if it is set(with the command db2set -all). It seems to be set. I have now 2 Variables regarding db2comm, one globally one at instance level.

    Regarding the setting of the port number. I checked the configuration file for the instance db2inst2:

    db2 get database manager configuration
    ...
    ...
    TPC/IP Service name (SVCENAME) =
    ...
    ...


    So, as seen, it is still blank. I checked at the configuration file of the instance db2inst1:

    TPC/IP Service name (SVCENAME) = db2c_db2inst1

    And in the file /etc/services there is a line:

    db2c_db2inst1 50000/tcp

    So, db2c_db2inst1 seems to refer to this port number.

    I think, I should now just setting the database manager configuration for db2inst2 with the same service name (db2c_db2inst1). I have looked in the documentation and found this command:

    update dbm cfg using SVCENAME 50000

    Would you please tell me if it is right? I am so worry of getting the instance broken if I set it wrong. Because it doenst seem right to me, since I am expecting the service name db2c_db2inst1 in the command.

    Thank you.

    Regards,
    Ratna

  14. #14
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    Every instance requires a different service name and port number

  15. #15
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    you can apply the port number directly in to the dbm cfg or you can use the service name

Posting Permissions

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