Newbie Questions (need your help) easy questions for you gurus. 1

[Sina]

Hello everyone.
I have couple of newbie questions, if you could kindly help.
here they are:
1. what is the command to get a list of the
database and tables in each database in DB2 ?
2. How do I get a list of all the primary keys on a table ?
3. How do I get a list of all the foriegn keys on a table ?
4. How do I get a list of all the indexes on a table ?
5. How do I get a description on a table in DB2 (like field defenitions).
6. How do I find out the list of users with access to a specific table / database (get user privilages on each db)?
7. How do I get a list of all the valid table fileds TYPES in DB2?

thank you all very much.

 

[Crox]

Hi,

look up the manuals at IBM's bookmanager:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/library

There you can find the right manuals.

In the manuals you can find a description of the SYSIBM tables, in which you can find almost anything you want to know of the tables in your environment. But it takes some effort to find the right table.

Regards,

Crox

 

[gregsimpson]

A good starting point for you would be to enquire of the sysibm tables.

Try selecting sysibm.systables & sysibm.sysindexes to get you going.

you can see what these tables look like by issuing

describe table sysibm.sysindexes

The columns are generally sensibly named and you can then select what your interested in.

Be aware that the mainframe and the unix/windows versions do have certain differences in the sysibm tables, even to the extent that some of these system tables only exist on certain platforms.

If you've got access to DB2 Command Center you should be able to use the GUI interface to drill down quite easily for everything your after.

 

[Guest_imported]

1. what is the command to get a list of the
database and tables in each database in DB2 ?

connect to the database...

db2 list tables for all

...or use the catalog views (SQL Ref).

5. How do I get a description on a table in DB2 (like field defenitions).

connect to the database...

db2 describe table <table name>

...or use the catalog views.

6. How do I find out the list of users with access to a specific table / database (get user privilages on each db)?

not sure about this one.

All of the other questions can be answered in the SQL Ref.


David

 

[Sina]

Hello everyone,

New in db2. I have to do the following using the command line and not the gui.

How to create a database
how to create a table
how to create index and primary key

Any help.
Thank you all

http://www.realsoftstudio.com

SQLPorter Database Migration Tool

 

[DbaDan]

SQL reference is what you should be using. Here is an example of what you ask. There are many variations of these commands.


CREATE DATABASE DB00027T
STOGROUP SG00000T
CREATE TABLE TEST.RESTORE
(
ID INTEGER NOT NULL,
CRTE_ID TEST.CRTE_ID NOT NULL,
CRTE_TS TEST.CRTE_TS NOT NULL WITH DEFAULT,
WORK_CMPL_DTE DATE NOT NULL,
UPDT_ID TEST.UPDT_ID NOT NULL,
UPDT_TS TEST.UPDT_TS NOT NULL WITH DEFAULT,
PRIMARY KEY (ID)
)
IN DB00027T.TSRSTR0T

CREATE TYPE 2 UNIQUE
INDEX TEST.XXRSTR0T
ON TEST.RESTORE
(ID )
USING STOGROUP SG00000T
PRIQTY 100 SECQTY 100
FREEPAGE 10 PCTFREE 5
CLUSTER
CLOSE NO