db2 basic commands

29 Jun

need to start database manager in db2 before isntalling Information Server
or if it is says “No start database manager command was issued”
$> start database manager

to stop:
$> stop database manager

need to create database “xmeta” in db 2 before isntalltion IS
$> create database xmeta

to start the database you probably need to be the db2inst1 user and then:
$> db2start

need to grant permissions on xmeta to xmeta

 $> grant on  to 
 $> grant DBADM on database to user  # This is if you have already connected to the database
 $> grant DBADM on database xmeta to xmeta   # note the password is used up on the system.. (DBADM is all privileges)

need to create unix user as “xmeta” and password
or if you are on windows, you can do “net use /add” to create a user that db2 can authenticate against

connect to database from within db2
$> connect to user using

disconnet to database from within db2
$> disconnect

list all databases
$> list db directory

list active database
$> list active databases

list or show tables
$> list tables

If you get “SQL1025N The database manager was not stopped because databases are still active.”
$> stop database manager

Try:
$> list active databases

And to stop everything:
$> force application all

To see db2 profile variables”
$> db2set -all
$> db2set -? # for help

You can set these variables:
$> db2set db2comm=tcpip

To see current configuration:
$> db2 get dbm cfg

I was getting the following error when running ‘db2start’:
“SQL5043N Support for one or more communications protocols failed to start successfully. However, core database manager funtionality started successfully”
– This was because TCP/IP stuff was not setup properly (i.e. you could not telnet to port 50000).
To fix:
1. $> db2set db2comm=tcpip
2. ensure that /etc/services has a line like: “db2cdb2inst1 50000/tcp” in it
3. $> db2 update database manager configuration using svcename db2cdb2inst1

from: http://www-1.ibm.com/support/docview.wss?uid=swg21009013
Problem
Does DB2® for Linux®, Windows® and UNIX® have something like Oracle's DUAL table?

Solution
Yes, DB2 database products have a dummy table to select against: SYSIBM.SYSDUMMY1.

DB2 database products also support a VALUES statement.

Either of these can be used to query the values of special registers that can tell you the current time or connected user.

For example:

db2 connect to sample

db2 values user

db2 values current timestamp

db2 select user, current timestamp from sysibm.sysdummy1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: