Question Re: db2 commands in Technology Q&A

db2 commands

Former Member — Tue, 03 Jun 2008 08:02:03 GMT

hi ,

I am new to db2 database

I have the following query

1.) How can we check the scheme owner of the db2 database .

2.) I wanted to reset the DDIC passsword from the database command prompt , can anybody give me the syntax of the command.

3.) How to connect to db2 database from command prompt.

Regards

Anthony

Edited by: Anthony D'souza on Jun 3, 2008 1:33 PM

Re: db2 commands

Former Member — Tue, 03 Jun 2008 11:34:07 GMT

Hi,

Refer thsi link

http://help.sap.com/saphelp_nw2004s/helpdata/en/c8/db104275a2e72ce10000000a1550b0/content.htm

Regards

usha

Re: db2 commands

Former Member — Tue, 03 Jun 2008 14:59:38 GMT

This link gives a doc which is not precisely what is wanted.

Would you please correct?

Thanks!

Re: db2 commands

Former Member — Tue, 03 Jun 2008 17:26:33 GMT

1. After connecting to database, the following will give schema name and owner.

db2 "select schemaname,owner from syscat.schemata"

2. DDIC password reset - Do not know. Let you know if I find.

3. Connecting to db2 database depends on from where the connection is coming from like local or remote....so on....

As local

db2 connect to dbname -- this will connect as db2<sid> user

to connect as different user like sap<sid>

db2 connect to dbname user sap<sid> -- it will prompt for sap<sid> user password

As remotely i.e from db2 client (pre-requisite is that particular db need to catalogued at local client)

db2 connect to dbname user sap<sid> -- it will prompt for sap<sid> user password

Re: db2 commands

Former Member — Tue, 03 Jun 2008 18:07:14 GMT

Could you give the syntax for catalogue the remote DB?

Thanks!

Re: db2 commands

Former Member — Tue, 03 Jun 2008 18:50:26 GMT

Hi Anthony,

I would like to complement the answer of Anil Lella by providing other information related to your questions:

1.) How can we check the schema owner of the db2 database .

DB2 UDB for Unix and Windows (let's call it up to now DB6) uses different authorizations and privileges. One of them is the DBADM authorization, which is granted by default to the creator of the database.

You can execute the following query to get a list of users, who have the DBADM authorization:

SELECT DISTINCT GRANTEE, GRANTEETYPE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y'

Besides whenever you create a table in DB6, a default schema is associated with it; therefore, if you are connected with the user db2bn7 and you create a table, i.e.: mytable, then the table will be fully referenced by other users using the name db2bn7.mytable.

Therefore, it might be possible you get more than one schema, that by running the query against syscat.schemata.

2.) I wanted to reset the DDIC password from the database command prompt , can anybody give me the syntax of the command.

I am not pretty sure, whether it is possible to perform such a reset from the database command prompt. As far as I know the DDIC user is a user from you SAP system, there is no correlation of this user to an operating system user or something like that. Therefore I would suspect, there is no way to do it.

Besides, there is no such utility from DB6, which can interact with users created inside an SAP system.

3.) How to connect to db2 database from command prompt.

The answer of Anil is right. It depends on the kind of client you are, either local or remote. Just to complement the answer, I would mention as additional step, that you first must catalog the host holding the database as a TCP/IP node in your remote client. After that, you can catalog the database at the remote machine stating that the database at that node.

When connecting from a local client, you do not need to catalog any database, because as part of the database creation process it is cataloged automatically.

As general information source about DB6, I would suggest you to read the IBM documentation. In the following websites you have access to each DB2 UDB version documentation (listed from DB2 9.5, 9 and 8):

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp

For question 1.) you will get further information by reading the entry for the CREATE DATABASE command.

For question 2.) you will get further information by reading the entry for the CONNECT statement and/or the CATALOG DATABASE/CATALOG TCPIP NODE.

Another interesting source of information is the preparation documentation for the DB2 Family Fundamentals Certification. You get access to this documentation in this site:

http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert730.html

Part 2 and Part 3 have further information about your questions.

Best Regards, Edgardo

Re: db2 commands

Former Member — Tue, 03 Jun 2008 19:48:01 GMT

Catalog node first on remote client

[CATALOG TCP/IP NODE|http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0001944.htm]

[CATALOG DB|http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0001936.htm]

Another way is using IBM DB2 Congiguration Assistant tool