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.