DB2 – Difference Between a Schema and a Database

db2schematable

I can not figure out what is the difference between schema and database in db2 I find this SQL so confusing for example :

CREATE TABLE schema1.table1
 ....
 ....
 ....
IN database1.tabspace1

Can we say that the database in this case contains the tabspace whereas the schema contains the tables?

Best Answer

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source.

The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized.

A table space is a storage structure containing tables, indexes, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system.

A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.

Related Solutions

Sql-server – SQL Server Schema Security

Yes. Simply grant the user that you create that lets them log into the SQL Server SELECT rights only on the view and not the table.

You could actually do all this through a single view.

Create a function that looks up the CustomerId based on the username. Then call that function as part of the view.

create view YourView as 
select * 
from YourTable where CustomerID = dbo.YourFunction(suser_sname())

Db2 – copy schema across different physical databases in db2

JDBC is not really going to answer your problem directly. It is a generic programming API for working with any database. So it can't possibly know about utilities that are specific to an implementation, be it DB2, Oracle, MySQL, etc.

I would look at using one of the utilities you mentioned above. That is their purpose. They assist with moving databases and/or data across environments. (There is also the restore redirect option as well, which is good if you are doing a backup/restore option).

If you insist on doing this through application code, then you will have to pass DDL through the JDBC API's (which dependent on your driver and your permissions, may or may not work). Or you would have to try to execute the DB2 utilities from "command line" through you program (look at the Runtime class to do this).

Again, I would keep this out of the application (perhaps you can share more of why you want to do this? That would help with answering your question). If you insist on doing it inside your application, you may also want to check out Stack Overflow, as you may get answers from a "programmatic" perspective.

Best Answer

Related Solutions

Sql-server – SQL Server Schema Security

Db2 – copy schema across different physical databases in db2

Related Question