How do I determine the schema of an existing database on an IBM AS/400
running OS/400 V5.1, to connect to it with DB2 Connect?

---

Request for Question Clarification by tar_heel_v-ga on 30 Oct 2002 11:15 PST

Are you using SNA or TCP/IP?

---

Clarification of Question by comprodguy-ga on 30 Oct 2002 11:27 PST

I am using TCP/IP--as I am using DB2 Connect Personal Edition Version
7 for Linux to connect to the DB2 database on the AS/400 (version 7
for Linux no longer has SNA support). The connection is good, I can
connect to the AS/400 via DB2 connect (I also have it running on a
Windows machine in the network to make testing easier)... I just have
no idea how to find out what schema the database I want is in, to
actually do something with my connection. :-P

---

Request for Question Clarification by tar_heel_v-ga on 30 Oct 2002 11:37 PST

Ahh...I misunderstood.  I thought you needed to connect.  So, if I
understand correctly, you simply need to be able to find out how to
see the schema of the DB2 database.  Am I on the right track here?  I
want to have it perfectly clear before I provide an answer.

Thanks!

-THV

---

Clarification of Question by comprodguy-ga on 30 Oct 2002 11:44 PST

Yup! That's exactly it. I have access to a terminal connected to the
AS/400, as well as the machine running DB2 Connect. So, any method to
determine the schema would be a great help.

In order to view the schema of the database, you will need to have
schema privileges.  This can be accomplished for users or for user
groups.  To grant schema privileges for users, in the Control Center,
expand the object tree until you see the Schemas folder.  Open the
folder and then right click over the schema you want to grant
privileges to.  This will open the Schema Privileges notebook.  In the
User box, you will see all the users with privileges for that schema. 
To add a user, click on Add User. Select the user from the Users list.
 Then you can Grant All or grant individual privileges.  From that
point, users with privileges will be able to alter the schema.

To Alter a schema, from the Control Center, get to the Schemas Folder
again. Right click over the schema you want and select Alter.  The
Alter Schema window will then open.


To create schemas, again, in Control Center, get to the Schema's
window (see above)  Right click the Schemas folder and choose Create. 
The Create Schema window will open.  Type a name for the new schema.
The name must:

-Consist of 1 to 8 characters, including uppercase letters (A-Z),
digits (0-9), underscore (_), dollar sign ($), pound sign (#), and the
at sign (@)
-Must begin with a letter (A-Z) 
-Must not begin with SYS 
-Can be an ordinary or delimited identifier

After naming, in the Authorization Name, select the owner of the
schema.

Thanks for your question.  If you need any further clarification or
assistance, please let me know and I will do my best to help.

Regards,

-THV

References:
DB2 Administrators Guide
http://www-3.ibm.com/software/data/db2/udb/ad/v7/adg/db2a0/frame3.htm#db2a0238

DB2 Survival Guide
http://www.michael-thomas.com/db2/

---

Request for Answer Clarification by comprodguy-ga on 30 Oct 2002 12:36 PST

The only thing is, the DB2 database itself is on the AS/400, so the
interface is the AS/400's green screen--not the Windows control center
in this case, which only affects the DB2 Connect software, not the DB2
database. So the answer I'm looking for would probably involve some
command I would enter into the command center in the control center,
or a command I would enter on the actual AS/400 terminal.

Thanks!

---

Clarification of Answer by tar_heel_v-ga on 30 Oct 2002 13:21 PST

I apologize for the misunderstanding.  Let me continue my research and
I will get back to you as soon as possible.

-THV

---

Clarification of Answer by tar_heel_v-ga on 30 Oct 2002 13:34 PST

Give this a shot:
LIST PACKAGES/TABLES

Lists packages or tables associated with the current database. 

Authorization 

For the system catalogs SYSCAT.PACKAGES (LIST PACKAGES) and
SYSCAT.TABLES (LIST TABLES), one of the following is required:

sysadm or dbadm authority 
CONTROL privilege 
SELECT privilege. 
Required Connection 

Database. If implicit connect is enabled, a connection to the default
database is established.

Command Syntax 
>>-LIST----+-PACKAGES-+---+--------------------------------+---><
           '-TABLES---'   |      .-USER-----------------.  |
                          '-FOR--+-ALL------------------+--'
                                 +-SCHEMA--schema-name--+
                                 '-SYSTEM---------------'
 
Command Parameters 

FOR 
If the FOR clause is not specified, the packages or tables for USER
are listed.
ALL 
Lists all packages or tables in the database. 
SCHEMA 
Lists all packages or tables in the database for the specified schema
only.
SYSTEM 
Lists all system packages or tables in the database. 
USER 
Lists all user packages or tables in the database for the current
user.


This should provide you with the tables within a schema.

Let me know if this works for you.

-THV

I know this is an older question so I don't know if you still need
assistance, but I thought I'd add some info anyway.

First let me preface the command with a little background information.
 DB2/400 (the AS/400's database) is an integrated piece of the
operating system.  Unlike Windows or Linux servers which can run many
databases (Oracle, MSSQL, etc..) the AS/400 has DB2 and nothing else. 
That being said, the AS/400 file structure is that of Libraries and
Objects.  Libraries are similar in nature to a folder on a PC. 
Libraries contain programs, files, and other objects.

Now to answer your question.  To display the structure or schema of a
specific database table (called a physical file or logical file in
AS/400 speak), you need to know what library it is located in.  Lets
assume your file exists in a library called ACCOUNTING.  Lets also
assume your file is called INVOICES.  So, to display the database
schema or structure on screen you would type:

DSPFFD FILE(ACCOUNTING/INVOICES)

To send the same listing to a printer you would type:

DSPFFD FILE(ACCOUNTING/INVOICES) OUTPUT(*PRINT)

This command will give you a listing of all fields contained in the
database file, their data type, length, buffer offsets, field usage,
and description.

Hope this helps!