Posts tagged clp

IBM DB/2: Introduction and .csv export

posted on 2015-03-16 11:12:00

overview

IBM DB/2 is a relational database, but sports quite a bit more features than i.e. mysql. But it differs quite a bit from the latter. This here should serve as on overview on how to use it's cli and some basic commands, when you are in dire need. ;)

structure

db2 uses linux system users. This means, to access the database you have to be logged as the right user, which has database access granted.

For finding out which user is the one you need, simply login as each one (su db2username, try looking them up in /etc/passwd/) and issue a db2 at the shell prompt.

If it was the right user, it should look like this:

[user@host root]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 =>

Trying with the wrong user will simply end in a bash: db2: command not found or the like.

basic commands

These should be the most used db2 sql commands when using the CLI via the db2 frontend.

To start simply write db2 while being logged in as the right user.

using help

# show commands
?
# show help on command
? <command>

connecting / disconnecting

# open connection so you can use sql statements
connect to <dbname>
# disconnect, but leave db2 cli running
connect reset
# disconnect and exit db2 cli
terminate
# exit client
quit

getting information on the database and its structure

# list databases
list database directory

If this is too unwieldy, try this from a shell prompt:

# list database's name from shell prompt
db2 list database directory | grep -i 'database name' | awk '{print $4}'

Now onto the internal structure:

# show all tables from all schemas
list tables for all

# show all tables for a specific schema
list tables for schema <schemaname>

# get table structure
describe tables <tablename>.<schemaname>

# show shemas
select distinct tabschema from syscat.tables
## also, but i prefer the above for it's more terse output
select schemaname from syscat.schemata

# show users
select distinct owner from syscat.tables

In syscat.tables there is also other information you might want to know, it's like the counterpart of the mysql table in a mysql database of a mysqld installation, as far as I can tell. (The mysql table in database mysql in a mysql database management system installation is correct. If you do not get it, read up on your basics, seriously.)

export to .csv

Easiest this is done from a shellscript. Developing it may take some more time, but usually you will need it in the future again, and grepping through the shell's history ain't the way to go.

touch mydb2script.sh
chmod 755 mydb2script.sh

Open the file mydb2script.sh and edit it to look like this:

#!/bin/bash
db2 connect to <databasename>
db2 "export to <filename>-$(date +%Y%m%d-%H.%M).csv of del modified by chardel\"\" coldel; decpt. select * from <databaseschemaname>.<tablename>"
db2 terminate

Read the above like export of sql-query, so the 'strange' syntax will make sense. The delimiter stuff is just sort of changing export settings.

I'd indent this like here, no idea if this makes sense to you:

export
    filename
of
    delimiter
        modified by
            chardelimiter '""'
            columndelimiter ';'
            decimalpoint '.'
<SQL QUERY>

I honestly do not know for sure if the terminate at the end is neccessary, but it does not hurt either, I guess. (Always close your resources if you do not need them anymore...) Since this is intended to be used as a cronjob, testing this without the conn reset is not an option since the system I am working on is produktive, and I sure as hell do not want to shoot it down some time in the future due to too many database connections. (When I have forgotten about the cron already, of course, or a colleague of mine will have to hunt it down without knowing anything about the changes.) There are quite a lot connections to the DB already, so troubleshooting this one-connection-at-a-time is also... NOT an option. :)

Redirect the commands output to /dev/null in case you want this as a cron job.

That should be about enough to start working with a db2 install you do not know much about. :)

This blog covers .csv, .htaccess, .pfx, .vmx, /etc/crypttab, /etc/network/interfaces, /etc/sudoers, /proc, 10.04, 14.04, AS, ASA, ControlPanel, DS1054Z, GPT, HWR, Hyper-V, IPSEC, KVM, LSI, LVM, LXC, MBR, MTU, MegaCli, PHP, PKI, R, RAID, S.M.A.R.T., SNMP, SSD, SSL, TLS, TRIM, VEEAM, VMware, VServer, VirtualBox, Virtuozzo, XenServer, acpi, adaptec, algorithm, ansible, apache, apachebench, apple, arcconf, arch, architecture, areca, arping, asa, asdm, awk, backup, bandit, bar, bash, benchmarking, binding, bitrate, blackarmor, blowfish, bochs, bond, bonding, booknotes, bootable, bsd, btrfs, buffer, c-states, cache, caching, ccl, centos, certificate, certtool, cgdisk, cheatsheet, chrome, chroot, cisco, clamav, cli, clp, clush, cluster, coleslaw, colorscheme, common lisp, console, container, containers, controller, cron, cryptsetup, csync2, cu, cups, cygwin, d-states, database, date, db2, dcfldd, dcim, dd, debian, debug, debugger, debugging, decimal, desktop, df, dhclient, dhcp, diff, dig, display manager, dm-crypt, dmesg, dmidecode, dns, docker, dos, drivers, dtrace, dtrace4linux, du, dynamictracing, e2fsck, eBPF, ebook, efi, egrep, emacs, encoding, env, error, ess, esx, esxcli, esxi, ethtool, evil, expect, exportfs, factory reset, factory_reset, factoryreset, fail2ban, fbsd, fedora, file, filesystem, find, fio, firewall, firmware, fish, flashrom, forensics, free, freebsd, freedos, fritzbox, fsck, fstrim, ftp, ftps, g-states, gentoo, ghostscript, git, git-filter-branch, github, gitolite, gnutls, gradle, grep, grml, grub, grub2, guacamole, hardware, haskell, hdd, hdparm, hellowor, hex, hexdump, history, howto, htop, htpasswd, http, httpd, https, i3, icmp, ifenslave, iftop, iis, imagemagick, imap, imaps, init, innoDB, innodb, inodes, intel, ioncube, ios, iostat, ip, iperf, iphone, ipmi, ipmitool, iproute2, ipsec, iptables, ipv6, irc, irssi, iw, iwconfig, iwlist, iwlwifi, jailbreak, jails, java, javascript, javaws, js, juniper, junit, kali, kde, kemp, kernel, keyremap, kill, kpartx, krypton, lacp, lamp, languages, ldap, ldapsearch, less, leviathan, liero, lightning, links, linux, linuxin3months, lisp, list, livedisk, lmctfy, loadbalancing, locale, log, logrotate, looback, loopback, losetup, lsblk, lsi, lsof, lsusb, lsyncd, luks, lvextend, lvm, lvm2, lvreduce, lxc, lxde, macbook, macro, magento, mailclient, mailing, mailq, manpages, markdown, mbr, mdadm, megacli, micro sd, microsoft, minicom, mkfs, mktemp, mod_pagespeed, mod_proxy, modbus, modprobe, mount, mouse, movement, mpstat, multitasking, myISAM, mysql, mysql 5.7, mysql workbench, mysqlcheck, mysqldump, nagios, nas, nat, nc, netfilter, networking, nfs, nginx, nmap, nocaps, nodejs, numberingsystem, numbers, od, onyx, opcode-cache, openVZ, openlierox, openssl, openvpn, openvswitch, openwrt, oracle linux, org-mode, os, oscilloscope, overview, parallel, parameter expansion, parted, partitioning, passwd, patch, pdf, performance, pfsense, php, php7, phpmyadmin, pi, pidgin, pidstat, pins, pkill, plesk, plugin, posix, postfix, postfixadmin, postgres, postgresql, poudriere, powershell, preview, profiling, prompt, proxmox, ps, puppet, pv, pvecm, pvresize, python, qemu, qemu-img, qm, qmrestore, quicklisp, r, racktables, raid, raspberry pi, raspberrypi, raspbian, rbpi, rdp, redhat, redirect, registry, requirements, resize2fs, rewrite, rewrites, rhel, rigol, roccat, routing, rs0485, rs232, rsync, s-states, s_client, samba, sar, sata, sbcl, scite, scp, screen, scripting, seafile, seagate, security, sed, serial, serial port, setup, sftp, sg300, shell, shopware, shortcuts, showmount, signals, slattach, slip, slow-query-log, smbclient, snmpget, snmpwalk, software RAID, software raid, softwareraid, sophos, spacemacs, spam, specification, speedport, spi, sqlite, squid, ssd, ssh, ssh-add, sshd, ssl, stats, storage, strace, stronswan, su, submodules, subzone, sudo, sudoers, sup, swaks, swap, switch, switching, synaptics, synergy, sysfs, systemd, systemtap, tar, tcpdump, tcsh, tee, telnet, terminal, terminator, testdisk, testing, throughput, tmux, todo, tomcat, top, tput, trafficshaping, ttl, tuning, tunnel, tunneling, typo3, uboot, ubuntu, ubuntu 16.04, udev, uefi, ulimit, uname, unetbootin, unit testing, upstart, uptime, usb, usbstick, utf8, utm, utm 220, ux305, vcs, vgchange, vim, vimdiff, virtualbox, virtualization, visual studio code, vlan, vmstat, vmware, vnc, vncviewer, voltage, vpn, vsphere, vzdump, w, w701, wakeonlan, wargames, web, webdav, weechat, wget, whois, wicd, wifi, windowmanager, windows, wine, wireshark, wpa, wpa_passphrase, wpa_supplicant, x11vnc, x2x, xfce, xfreerdp, xmodem, xterm, xxd, yum, zones, zsh


Unless otherwise credited all material Creative Commons License by sjas