Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27

    Question Unanswered: How to define a tablespace for a user??

    Platform & software: DB2 for OS/390 v7, DB2 Connect, Brio Reporting tool.

    I have a question - Can you specify a tablespace for a user/secondary auth-id ?

    We have users who are able to create a table via a reporting tool. I want to know from the DB2 mainframe side, how to make all those tables go into one particular tablespace that we can specify. DB2 connect provides an option to provide the database name, but not the tablespace name. So what happens now is that all the tables the users create are going into the database we specify, but since there was no tablespace name provided, tablespace names are getting created dynamically for each table that the user create. The worst part is that the primary and secondary for these dynamically created tablespaces are getting assigned as 12 and 12 which is not enough.

    So my question is how do we say in DB2 for OS/390 .."hey, user A is trying to create a table without providing a tablespace name. Put his table TABLEA into TABLESPACEA".? How do I specify this and where ?

    Thanks in advance..(this has been bugging me for weeks)
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    In the LUW world, you can revoke use of all tablespaces from all and then create a tablespace and grant use to public. I don't know if this will work on OS390

  3. #3
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Even if I create a tablespace and GRANT to PUBLIC, how will I make the tables to go into that?

    In any case we cant have a tablespace with CREATE TABLE access to public.
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    On mainframe QMF there is parameter to define the tablespace used for the save data command (which creates a table). Check with the Brio documentation or support and see if they have a similar parameter.
    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
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Brio support is telling us there should be an option in the host to do this.
    DB2 connect doesnt have an option to specify the tablespace either.

    So the only option left is to explore whether this can be done from the host side.

    it would have been wonderful, IF there was an option to issue a statement like -

    "SET DEFAULT TABLESPACE = '...' FOR USER = '...' ;'

    high hopes?
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    You do not need to grant access to public - you can also grant user by user. DB2 by default will try to create any table create without a tablespace clause in either the tablespace which was first created in the database or the tablespace in which the first table was created - can't remember which one. I think it's the first one.

    Here's what it says in the sql ref

    IN tablespace-name1
    Identifies the table space in which the table will be created. The table space must exist, and be a REGULAR table space over which the authorization ID of the statement has USE privilege. If no other table space is specified, then all table parts will be stored in this table space. This clause cannot be specified when creating a subtable (SQLSTATE 42613), since the table space is inherited from the root table of the table hierarchy. If this clause is not specified, a table space for the table is determined as follows:
    IF table space IBMDEFAULTGROUP over which the user has USE privilege
    exists with sufficient page size
    THEN choose it
    ELSE IF a table space over which the user has USE privilege
    exists with sufficient page size
    (see below when multiple table spaces qualify)
    THEN choose it
    ELSE issue an error (SQLSTATE 42727).

    If more than one table space is identified by the ELSE IF condition, then choose the table space with the smallest sufficient page size over which the authorization ID of the statement has USE privilege. When more than one table space qualifies, preference is given according to who was granted the USE privilege:

    the authorization ID
    a group to which the authorization ID belongs
    PUBLIC
    If more than one table space still qualifies, the final choice is made by the database manager.

    Determination of the table space may change when:

    table spaces are dropped or created
    USE privileges are granted or revoked.
    The sufficient page size of a table is determined by either the byte count of the row or the number of columns. See Row Size for more information.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    According to the SQL Reference, if you don't specify the table space in the create table statement (issued by Brio):

    "The name of the table space is derived from the table name. Its other attributes are those it would have if it were created by a CREATE TABLESPACE statement with all optional clauses omitted."

    If the database name is not specified then DSNDB04 is used. I don't see anything in the SQL Reference about any other way to specify the default table space name or default attributes. Maybe you should ask Brio exactly what option on the host they are talking about.

    I would not expect DB2 Connect to have anything to do with this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    cchattoraj, We are discussing DB2 for OS/390. That looks like a quote from the DB2 LUW SQL Reference. On OS/390, the database and/or tablespace name are optional and will default as I described above.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Originally posted by Marcus_A
    According to the SQL Reference, if you don't specify the table space in the create table statement (issued by Brio):

    "The name of the table space is derived from the table name. Its other attributes are those it would have if it were created by a CREATE TABLESPACE statement with all optional clauses omitted."

    If the database name is not specified then DSNDB04 is used. I don't see anything in the SQL Reference about any other way to specify the default table space name or default attributes. Maybe you should ask Brio exactly what option on the host they are talking about.

    I would not expect DB2 Connect to have anything to do with this.
    I believe UDB works differently from DB2 for OS/390. In UDB, if u dont specify the tablespace it will work as u said. But in OS/390, as Marcus had said, it will create a tablespace EVERY TIME a create statement is given and the name of the tablespace will be a name which is derived from the table name. I have seen this happening. THAT is my problem. I wanted to predefine the tablespace name for the user thus avoiding the random creation of the tablespaces.

    I read the manual too. There is nothing mentioned there..

    Below is the reply what Brio had said. it didnt make sense to me. Also whatever they are saying is for UDB and not OS/390:

    ---------------------------------------------------------------------------
    -----Original Message-----
    From: Jack Burton
    Sent: Tuesday, January 27, 2004 2:30 PM
    To: Kathrin Robbins
    Subject: RE: SR # 2-1895601


    The same is true for DB2. If you do not define a table space the default user and temp table spaces will be used. Creating a separate table space for Hps is recommended but not necessary. It is very easy to you do this via Db2 Command Center. Or from the db2 prompt.

    db2 connect to mydatabase
    db2 create tablespace myspace pagesize 16k managed by system using
    "('/home/inst1/tablespace')"
    db2 connect reset

    It is simplier to use contro center.

    I always use the default at Hyperion on Aix.

    db2 => connect to hps

    Database Connection Information

    Database server = DB2/6000 8.1.2
    SQL authorization ID = BRIO8
    Local database alias = HPS

    db2 => list TABLESPACES

    Tablespaces for Current Database

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 2
    Name = USERSPACE1
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal

    db2 =>

    -----Original Message-----
    From: Kathrin
    Sent: Tuesday, January 27, 2004 1:54 PM
    To: Jack
    Subject: FW: SR # 2-1895601



    Hi Jack,
    Noelle told me you are very familiar with DB2.

    Srini told me that in Oracle, you have to define a tablespace for each user and if you don't, the Oracle default is System or Temp.

    Is the same true for DB2?

    If so, how does my customer set this up?

    Thank you,
    Kathy
    --------------------------------------------------------------------------------
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    They are definitely talking about DB2 LUW (UNIX, Windows, and Linux) and not on the mainframe. A tablespace on the mainframe is a different animal from a tablespace on DB2 LUW.

    You need to specifically tell Brio you are using DB2 for OS/390 and that it works differently from DB2 LUW, but they may not have any good answer.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2002
    Posts
    456
    You cannot assign a tablespace to user as default in OS/390. A new tablespace is created every time a table is created if not explicitly defined.

    Why not ask these Brio guys to customize the tool and add extra control for tablespace to use OR once your user has the table definition the give it to you and then you alter the script to add the tablespace clause!!!!

    My two cents...

    dollar

    Originally posted by gsreejith
    I believe UDB works differently from DB2 for OS/390. In UDB, if u dont specify the tablespace it will work as u said. But in OS/390, as Marcus had said, it will create a tablespace EVERY TIME a create statement is given and the name of the tablespace will be a name which is derived from the table name. I have seen this happening. THAT is my problem. I wanted to predefine the tablespace name for the user thus avoiding the random creation of the tablespaces.

    I read the manual too. There is nothing mentioned there..

    Below is the reply what Brio had said. it didnt make sense to me. Also whatever they are saying is for UDB and not OS/390:

    ---------------------------------------------------------------------------
    -----Original Message-----
    From: Jack Burton
    Sent: Tuesday, January 27, 2004 2:30 PM
    To: Kathrin Robbins
    Subject: RE: SR # 2-1895601


    The same is true for DB2. If you do not define a table space the default user and temp table spaces will be used. Creating a separate table space for Hps is recommended but not necessary. It is very easy to you do this via Db2 Command Center. Or from the db2 prompt.

    db2 connect to mydatabase
    db2 create tablespace myspace pagesize 16k managed by system using
    "('/home/inst1/tablespace')"
    db2 connect reset

    It is simplier to use contro center.

    I always use the default at Hyperion on Aix.

    db2 => connect to hps

    Database Connection Information

    Database server = DB2/6000 8.1.2
    SQL authorization ID = BRIO8
    Local database alias = HPS

    db2 => list TABLESPACES

    Tablespaces for Current Database

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal

    Tablespace ID = 2
    Name = USERSPACE1
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal

    db2 =>

    -----Original Message-----
    From: Kathrin
    Sent: Tuesday, January 27, 2004 1:54 PM
    To: Jack
    Subject: FW: SR # 2-1895601



    Hi Jack,
    Noelle told me you are very familiar with DB2.

    Srini told me that in Oracle, you have to define a tablespace for each user and if you don't, the Oracle default is System or Temp.

    Is the same true for DB2?

    If so, how does my customer set this up?

    Thank you,
    Kathy
    --------------------------------------------------------------------------------

  12. #12
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    Thanks for the reply.

    I am not sure how successful that wud be, to ask Brio to customize the tool just for us.

    We cant alter the DDL since Brio creates the table on the fly. A GUI comes up where one can enter the name of the table and thats it. A dialog box comes up saying that a table has been created and xxxx rows loaded into the table.

    Using a facility in Brio called 'Query log', I can see the DDL, but by that time this wud have been executed.
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would keep after Brio for a suggestion. I know that IBM uses Brio quite a bit, so maybe you could find out how they handle it. Any change that they made would not be just for you.

    As I mentioned, QMF has a tablespace default set up as global default, and which can be modified for each user in the user's QMF profile.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    But how will setting a default tablespace in QMF useful here ?
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is not directly useful for your situation with Brio. I was just explaining how another query tool that uses mainframe DB2 works. That should be a hint to the Brio folks that they should do something similar (i.e., there is a reason that QMF did it that way). However, my knowledge of QMF is a few years old, and it is possible that DB2 has some new way to control it internally at the user level, but (as noted) in looking at the current DB2 SQL Reference, I don’t see anything that would help you.

    I suspect that Brio doesn’t have a lot new sales from DB2 mainframe customers anymore, so maybe they don't care. But there are a lot of people within IBM using Brio with DB2 for OS/390.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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