Shulou(Shulou.com)05/31 Report--

How to analyze the schema and user of db2 database, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

There are two meanings of Schema in the database. One is conceptual Schema, which refers to a set of DDL statements that completely describe the structure of the database. The other is physical Schema, which refers to a namespace in a database that contains a set of named objects such as tables, views, and stored procedures. To put it simply, a Schema is a database object owned by a user.

There can be data tables of multiple applications in a database, and these tables of different applications can be placed in different schema. At the same time, each schema corresponds to a user, and different applications can connect to the database with different users, so that a large database can separate and manage its tables according to the application. There is no direct relationship between different schema. Tables between different shcema can have the same name or reference each other (but must have permission). Without the right to operate on other schema, each user can only manipulate all tables under its own schema. Tables with the same name under different schema can store different data (that is, schema users' own data).

What exactly is the relationship between User and Schema in the database? In fact, User is the owner of every Schema. If the user currently operating on the database has a default Schema (specified when creating the user), then the new table is created on the default Schema. Through this method, it is mainly to facilitate management.

The concept of schema in DB2 is essentially different from that in ORACLE: in ORACLE, schema and user are the same; in DB2, schema is not necessarily a user, because there is no concept of user within db2, and the connection user must be an operating system user.

Query the existing Schema

Db2 "select SCHEMANAME,owner,CREATE_TIME from syscat.schemata"

In some cases, the Schema to be accessed by the currently logged-in user may not be the Schema assigned to him by default, and a prefix is required before the query table to constrain the Schema to be accessed.

Such as:

Db2 "SELECT * FROM wanted_schema_name.table_name"

Or you can modify the current Schema with the following command

SET CURRENT SCHEMA = 'PAYROLL'

SET SCHEMA 'PAYROLL'

During development, you can modify the Schema/Sqlid of the default connection by modifying the connection string

Take JDBC as an example (the final semicolon must not be omitted):

Jdbc:db2://database_server_uri:port/database_name:currentSchema=schema_name

Db2 switches schema and executes scripts

Tested under windows

1. Open the cmd command

2 、 db2cmd

3. Db2 connect to test user meta using portaldb-connect to the database

4. Db2 select current schema from sysibm.sysdummy1-view the default schema of the current database

5. Db2set current schema bast-set schema

6. Db2 select current schema from sysibm.sysdummy1-check whether the setting is successful

7. Db2-tvf d:\\ meta\ meta_ddl.sql-- execute the table-building statement on the specified schema

DB2 grants permissions to one user for all tables under schema

Db2 "select 'db2 grant select on table DB2INST2.' | | tabname | |' to db2inst1' from syscat.tables where tabschema='DB2INST2'"

Authorize tables under a Schema separately

C:\ Users\ Administrator > db2 grant select on table zssqsong.kc_team_user to user zssqding

The DB20000I SQL command completed successfully.

This is the answer to the question about how to analyze the schema and user of db2 database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.