IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

What is a table space in db2 for z/os?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
martin schlatter

New User


Joined: 06 Jun 2013
Posts: 9
Location: Germany

PostPosted: Sat Nov 24, 2018 11:19 pm
Reply with quote

Is a table space an object inside a database or is it independend from a database?
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace? Or are they only in the same tablespace if the have the same dbname.tsname ?
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Sat Nov 24, 2018 11:51 pm
Reply with quote

This is a forum for experts, and experts should know how to open a manual.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Mon Nov 26, 2018 7:40 pm
Reply with quote

Quote:
Is a table space an object inside a database or is it independend from a database?
It is within the Database.
Quote:
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace?
Yes
Quote:
Or are they only in the same tablespace if the have the same dbname.tsname
It should match certainly. When you create any table then you mention the Database Name and Table Space Name but they are totally optional.If you don't specify neither of them then they get created.
Back to top
View user's profile Send private message
martin schlatter

New User


Joined: 06 Jun 2013
Posts: 9
Location: Germany

PostPosted: Mon Nov 26, 2018 11:56 pm
Reply with quote

Rohit Umarjikar wrote:

Quote:
When I "select * from sysibm.systables where TSNAME='TSID1'", then these tables are all in the same tablespace?
Yes


We have many tables with TSNAME='TSID1' but with different DBNAMEs, so it seems they are not in the same tablespace because they are not in the same database?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Tue Nov 27, 2018 12:53 am
Reply with quote

Quote:
We have many tables with TSNAME='TSID1' but with different DBNAMEs, so it seems they are not in the same tablespace because they are not in the same database?
I already said so, why do you think database Name has to be same across TS name ? One TS name can have one or many DB Name(s).
Back to top
View user's profile Send private message
martin schlatter

New User


Joined: 06 Jun 2013
Posts: 9
Location: Germany

PostPosted: Tue Nov 27, 2018 1:36 am
Reply with quote

Ah ok I understand.
So table T1 in database DB1/tablespace TS1 and table T2 in database DB2/tablespace TS1 share the same tablespace TS1?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Tue Nov 27, 2018 1:38 am
Reply with quote

Quote:
So table T1 in database DB1/tablespace TS1 and table T2 in database DB2/tablespace TS1 share the same tablespace TS1?
Yes again. the TS name is same but you nust know which Database connection is made before you issue sql and it will pick it up corresponding details from there. you can check this information in any third party tool at your site or with DBA.
Back to top
View user's profile Send private message
martin schlatter

New User


Joined: 06 Jun 2013
Posts: 9
Location: Germany

PostPosted: Tue Nov 27, 2018 1:56 am
Reply with quote

My question is if table T1 gets very big if takes the space and prevents table T2 to grow.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Nov 27, 2018 1:59 am
Reply with quote

Rohit Umarjikar wrote:
Quote:
So table T1 in database DB1/tablespace TS1 and table T2 in database DB2/tablespace TS1 share the same tablespace TS1?
Yes again
No, those are 2 different tablespaces.

DB2 Basics-Tablespaces and Bufferpools

Quote:
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace?
Not necessarily
Quote:
Or are they only in the same tablespace if the have the same dbname.tsname ?
Yes.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Tue Nov 27, 2018 2:00 am
Reply with quote

Quote:
My question is if table T1 gets very big if takes the space and prevents table T2 to grow.
What is your final question? So far you are trying to understand what is DB and TS and How they related to each other. If you have a DBA then he must know what TS and DB to be assigned. As a developer you tell what approximate size of the table could be and all other attributes. if two tables uses same TS within same DB which is most common then the TS space will be managed accordingly by DBA else if two DB(s) having same TS name then its different story all together.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Tue Nov 27, 2018 10:49 pm
Reply with quote

Arun Raj wrote:
Rohit Umarjikar wrote:
Quote:
So table T1 in database DB1/tablespace TS1 and table T2 in database DB2/tablespace TS1 share the same tablespace TS1?
Yes again
No, those are 2 different tablespaces.

DB2 Basics-Tablespaces and Bufferpools

Quote:
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace?
Not necessarily
Quote:
Or are they only in the same tablespace if the have the same dbname.tsname ?
Yes.

You got to read complete sentence before making conclusion. Plus the link is old , here is latest one.Db2 table spaces
the TS name is same but you nust know which Database connection is made before you issue sql and it will pick it up corresponding details from there. you can check this information in any third party tool at your site or with DBA.

"If there are two different databases (servers) then certainly it will be different and that's what I said. One can have same TS name across multiple DB(s) but One should know which TS belongs to which DB before you make a call."
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Nov 27, 2018 10:56 pm
Reply with quote

Quote:
You got to read complete sentence before making conclusion
I did read the questions and the responses before posting.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Thu Nov 29, 2018 9:25 am
Reply with quote

Hello Rohit,

Quote:

Arun Raj wrote:
Rohit Umarjikar wrote:
Quote:
So table T1 in database DB1/tablespace TS1 and table T2 in database DB2/tablespace TS1 share the same tablespace TS1?
Yes again
No, those are 2 different tablespaces.

DB2 Basics-Tablespaces and Bufferpools

Quote:
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace?
Not necessarily
Quote:
Or are they only in the same tablespace if the have the same dbname.tsname ?
Yes.

You got to read complete sentence before making conclusion. Plus the link is old , here is latest one.Db2 table spaces
the TS name is same but you nust know which Database connection is made before you issue sql and it will pick it up corresponding details from there. you can check this information in any third party tool at your site or with DBA.

"If there are two different databases (servers) then certainly it will be different and that's what I said. One can have same TS name across multiple DB(s) but One should know which TS belongs to which DB before you make a call."

Read more: ibmmainframes.com/viewtopic.php?t=66976#ixzz5YDDeVQI6


I do not agree with your comment. I agree with Arun we can have same tablespace name in different database optionally these tablespace can have tables with the same name but schema/owner might be different.

Like Employee table can reside in EMP tablespace of DB EMPDTST with schema TEST1 also we can have Employee table in EMP Tablspace of DB EMPDTST with schema TEST2

So if we give below query
When I "select * from sysibm.systables where TSNAME='TSID1'", then are these tables all in the same tablespace?

Not necessarily .... they can be in different Database.

Please let me know in case of questions.


so if we give below query
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Thu Nov 29, 2018 9:43 am
Reply with quote

Did I not said the same what you said?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 3
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top