See list of clients connected to server

The command is LIST APPLICATIONS

Exporting and importing data from specific tables

You export data from specific table/s using the command -

db2 export to filename of ixf select * from table 

where ixf is the file-type

You import into specific tables using the command -

db2 import from filename of fileformat import_mode into table


where there are 3 import modes. The commonly used one, for backing up and versio - ning is-

REPLACE - to keep table structures and indexes, and replace data

What to do when the transaction log becomes full

You have to run the following commands

Connect to database using -
     db2 connect to DBNAME USER <USERBANEM> USING <PASSWORD>

See the details for the database using -
    db2 get db cfg for DBNAME show detail

Increase the number of primary log files using -
    db2 update db cfg for DBNAME using logprimary 2

Increase the number of secondary log files using -
    update db cfg for DBNAME using logsecond 2

Increase the number of pages of size 4 kB each using -
   update db cfg for DBNAME using logfilsiz 100000

The total log size you saw in details and which you set, is number of pages x page size x (number of primary logs _ number of secondary logs)

Resolving Error Code 7

You might get error code 7 after altering a table. In that case run the command REORG TABLE <TABLE NAME> in the DB2 Command Line Processor (Command line client) to resolve the issue.

Fetching current date in IBM's DB2 database

The command to do it is -

select current date from sysibm.sysdummy1

Looking at all table names in IBM DB2

You can look at the name of all tables in your database through the tables table in the syscat schema. Its structure can be seen using -

describe table syscat.tables

Some DB2 tips and tricks

I will outline a few DB2 tips and tricks.

You can see a list of all tables in the current database using the command select tablename from syscat.tables. syscat.tables is the name of the table in the syscat (system catalog) schema whose structure you can look up to see more traits of the database that you can query. You look up a table's structure through describe table <table name>

You can see the current date by querying a runtime table called sysdummy1 in the sysibm schema,  as in select current date from sysibm.sysdummy1.

Similarly you can query the current schema through select current schema from sysibm.sysdummy1.

DB2 has this concept called schema, which groups together database objects like tables, indexes, triggers etc. This schema is the same as your login user name by default. You have to prefix the schema to your table name if you're querying a table from another schema.

You can look at all the schema present through select schemaname from syscat.schemata

You can change the current schema by using set current schema = '<schema name'>

Installing IBM DB2 Database System's Client

DB2 is a relational database system from IBM which follows all of Codd's commandments for database systems. Below I outline the steps to install and configure client software for it on Windows. Using this client you can connect to a DB2 server.

First you have got to download DB2 Data Server Driver package. Just look for it using a search engine and you'll get a link to IBM's site.

You have to first unzip the file and then click on Setup/Install. A point to remember is to shorten the name of the folder into which the software is installed, because otherwise the installer complains about the same.

You can then install the software in the usual manner by clicking on Next, and keeping the default selections intact.

After you have installed the client software, you have got to open a command prompt, and run the db2cmd command to set up the environment variables for db2. 

Then you run the db2 command to get a db2 prompt. This step and the ones after are the same on Linux because the db2 utility is portable across Linux, Unix and Windows. You now have to add the remote server (which I'm presuming to be a common scenario in a production environment) to the system directory on the client machine. You do this by issuing the command catalog tcpip node <node_name> remote <ip address> server <port>.

You then have to add the remote database to the system directory using the command catalog database <database name> as <alias> at node <node name>

That's it! You are good to connect to the remote DB2 server, either through the command line, or through an application.

You can connect to the server through the command line by using connect to <database alias> user <username> using <password>, and query/manipulate the database.