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)

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

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.

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.

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?

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.

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.

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.

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
--------------------------------------------------------------------------------

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.

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

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.

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.

But how will setting a default tablespace in QMF useful here ?

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.