IBM DB2 - Databases, Users, Schemas and Tables

I knocked this up for an IBM colleague, as a basic illustration of the difference between users and schemas.

Hope it's (a) right and (b) of some use: -

Connect to the DB as the DB administrator

db2 connect to sample

Create a table

- The schema is foobar

Create db2 create table foobar.snafu(name varchar(20))

DB20000I  The SQL command completed successfully.

Grant access to the table for a user

- The user is sklmdb31

db2 grant all on table foobar.snafu to user sklmdb31

DB20000I  The SQL command completed successfully.

Connect to the DB

db2 connect to sample user sklmdb31 using Qp455w0rd@

   Database Connection Information

 Database server        = DB2/NT64 11.1.4.4

 SQL authorization ID   = SKLMDB31

 Local database alias   = SAMPLE

Query the table

db2 select * from foobar.snafu

NAME

--------------------

  0 record(s) selected.

Insert a new row

db2 insert into foobar.snafu(name) values('Dave')

DB20000I  The SQL command completed successfully.

Query the table

db2 select * from foobar.snafu

NAME

--------------------

Dave

  1 record(s) selected.

Set the current schema

db2 set current schema foobar

List the table

db2 list tables for schema foobar

Table/View                      Schema          Type  Creation time

------------------------------- --------------- ----- --------------------------

SNAFU                           FOOBAR          T     2019-01-25-11.42.03.697001

  1 record(s) selected.

Validate connection

db2 connect

   Database Connection Information

Database server        = DB2/NT64 11.1.4.4

SQL authorization ID   = SKLMDB31

Local database alias   = SAMPLE

Terminate the connection

db2 terminate