|
View previous topic :: View next topic
|
| Author |
Message |
martin schlatter
New User
Joined: 06 Jun 2013
Posts: 9
Location: Germany
|
|
|
|
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 |
|
 |
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1316
Location: Vilnius, Lithuania
|
|
|
|
| This is a forum for experts, and experts should know how to open a manual. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
martin schlatter
New User
Joined: 06 Jun 2013
Posts: 9
Location: Germany
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
martin schlatter
New User
Joined: 06 Jun 2013
Posts: 9
Location: Germany
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
martin schlatter
New User
Joined: 06 Jun 2013
Posts: 9
Location: Germany
|
|
|
|
| My question is if table T1 gets very big if takes the space and prevents table T2 to grow. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| You got to read complete sentence before making conclusion |
I did read the questions and the responses before posting. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Did I not said the same what you said? |
|
| Back to top |
|
|
|
|
