DB2 command reference

Invoke interpreter:
shell> db2

List available databases:
db2_shell> list database directory

List DB2 shell history:
db2_shell> history
# or
db2_shell> h

Edit and Execute command from DB2 shell history:
# command_num is from the above history command
db2_shell> edit command_num
# or
db2_shell> e command_num

Connect to database from within the db2 interpreter (CLP):
db2_shell> connect to databasename

Connect to database from UNIX shell:
# lasts for duration of current shell session.
db2 connect to database_name

Run SQL command from UNIX shell:
# first connect to database via UNIX shell (above)
# then run the following
db2 -vf scriptname.sql

List tables in database:
db2_shell> list tables

List indexes on a table:
SELECT indschema,INDNAME from syscat.indexes where tabname = 'MYTABLE' and tabschema = 'MYSCHEMA'

Analyze table:
runstats on table schema.tablename

List connections to database:
unix> db2 list applications
# or
db2_shell> list applications
# for detail information
unix> db2 list applications show detail

Get current user:
SELECT CURRENT USER FROM SYSIBM.SYSDUMMY1

Disconnect all connections:
force applications all
#or 
db2 terminate # this might work


Drop database:
drop database databasename

Create database:
create database databasename

Create the explain tables:
connect to databasename
db2 -vtf /opt/ibm/db2/V9.7/misc/EXPLAIN.DDL

Stop DB2:
db2stop

Start DB2: 
db2start

Allow TCP connections (probably unsafe):
update database manager configuration using svcename 3700
get database manager configuration    # verify change
db2set DB2COMM=tcpip
db2stop

db2start
sudo /sbin/iptables -A INPUT -p tcp --dport 7300 -j ACCEPT

sudo /sbin/service iptables save

sudo /sbin/service iptables restart

Update config params:
update db cfg for database_name using logfilsiz 8000
 # or
update database configuration for database_name using logprimary 9 logfilsiz 8000
force applications all

Sources:
http://www.tek-tips.com/viewthread.cfm?qid=128876

SQLAlchemy Notes

Summarized From 
http://www.sqlalchemy.org/docs/core/tutorial.html


This covers the basics of the Non-orm usage of SQLAlchemy

# Define connection engine
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

# Create some database tables 
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)

addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
)

metadata.create_all(engine)

# Insert into the tables
ins = users.insert()
ins.values(name='jack', fullname='Jack the Pumpkin king')

conn = engine.connect()
result = conn.execute(ins)
result.inserted_primary_key

# Multiple insert into the tables
conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])

result = engine.execute(users.insert(), name='fred', fullname="Fred Flintstone")

# Select from the tables
from sqlalchemy.sql import select

# Select all fields in user table
s = select([users])
result = conn.execute(s)
list(result)

# Select name and fullname columns from user table
s = select([users.c.name, users.c.fullname])

# Join two user and address tables
#  Cartesian product 
list(conn.execute(select([users, addresses])))
# Join on id columns
list(conn.execute(select([users, addresses], users.c.id==addresses.c.user_id)))

# create conditions for the where clause
from sqlalchemy.sql import and_, or_, not_
print and_(users.c.name.like('j%'), users.c.id==addresses.c.user_id, )


s = select([(users.c.fullname + "--" + addresses.c.email_address).label('title')])
print conn.execute(s).fetchall()

print users.join(addresses)
print users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))


s = select([users.c.fullname], from_obj=[
   users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
   ])
print conn.execute(s).fetchall()

# Delete records based on a condition
conn.execute(users.delete().where(users.c.name > 'm'))

Query Django Admin Log

Show Django Admin Log entries


select u.username, u.first_name, u.last_name, l.action_time, c.name, c.app_label,
       l.object_id as "Object Modified", l.object_repr, l.action_flag, l.change_message
from (auth_user u inner join django_admin_log l on u.id = l.user_id )
      inner join django_content_type c on c.id = l.content_type_id
where l.object_id = '9'  /* filter by object id if desired */
       and l.action_time > to_date('2010-10-04 01:00','YYYY-MM-DD HH:MI')  /* filter by date range */
order by l.action_time;

Postgresql Basic Commands

Login to postgresql:
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

Default Admin Login:
sudo -u postgres psql -U postgres
sudo -u postgres psql

List databases on postgresql server:
psql -l [-U myuser] [-W]

Turn off line pager pagination in psql:
\pset pager

Determine system tables:
select * from pg_tables where tableowner = 'postgres';

List databases from within a pg shell:
\l

List databases from UNIX command prompt:
psql -U postgres -l

Describe a table:
\d tablename

Quit psql:
\q

Switch postgres database within admin login shell:
\connect databasename

Reset a user password as admin:
alter user usertochange with password 'new_passwd';

Show all tables:
\dt

List all Schemas:
\dn

List all users:
\du

Load data into posgresql:
psql -W -U username -H hostname < file.sql

Dump (Backup) Data into file:
pg_dump -W -U username -h hostname database_name > file.sql

Increment a sequence:
SELECT nextval('my_id_seq');

Create new user:
CREATE USER jjasinski WITH PASSWORD 'myPassword';
# or
sudo -u postgres createuser jjasinski -W

Change user password:
ALTER USER Postgres WITH PASSWORD 'mypass';

Grant user createdb privilege:
ALTER USER myuser WITH createdb;

Create a superuser user:
create user mysuper with password '1234' SUPERUSER
# or even better
create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
# or
sudo -u postgres createuser jjasinski -W -s

Upgrade an existing user to superuser: 
alter user mysuper with superuser;
# or even better
alter user mysuper with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION

Show Database Version:
SELECT version();

Change Database Owner:
alter database database_name owner to new_owner;

Copy a database:
CREATE DATABASE newdb WITH TEMPLATE originaldb;
http://www.commandprompt.com/ppbook/x14316

View Database Connections:
SELECT * FROM pg_stat_activity;

View show data directory (works on 9.1+; not on 7.x):
show data_directory;

Show run-time parameters:

show all;
select * from pg_settings;

Show the block size setting:
# show block_size;
 block_size
------------
 8192
(1 row)

Show stored procedure source:
SELECT prosrc FROM pg_proc WHERE proname = 'procname'

Grant examples:
# readonly to all tables for myuser
grant select on all tables in schema public to myuser;
# all privileges on table1 and table2 to myuser
grant all privileges on table1, table2, table3 to myuser;

Restore Postgres .dump file:
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
source

Find all active sessions and kill them (i.e. for when needing to drop or rename db)
Source: http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

# Postgres 9.2 and above
SELECT pg_terminate_backend(pg_stat_activity.pid) 

FROM pg_stat_activity 

WHERE pg_stat_activity.datname = 'TARGET_DB' 

 AND pid <> pg_backend_pid();

# Postgres 9.1 and below
SELECT pg_terminate_backend(pg_stat_activity.procpid) 

FROM pg_stat_activity 

WHERE pg_stat_activity.datname = 'TARGET_DB' 

AND procpid <> pg_backend_pid();


Re-read postgres config without dropping connections (i.e. if postgres.conf or pg_hba.conf changes)
Source: http://www.heatware.net/databases/postgresql-reload-config-without-restarting/

/usr/bin/pg_ctl reload
or
SELECT pg_reload_conf();

Per user query logging (logs to to postgres logs):
alter role myuser set log_statement = 'all';


Sources:
http://www.devdaily.com/blog/post/postgresql/log-in-postgresql-database
http://forums.devshed.com/postgresql-help-21/how-do-you-turn-off-more-scroll-lock-at-psql-174831.htm
http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
http://archives.postgresql.org/pgsql-general/1998-08/msg00050.php
http://stackoverflow.com/questions/876522/creating-a-copy-of-a-database-in-postgres
http://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database

Djanog show model query

Show the raw SQL generated by a model access: 

# DEBUG needs to be set True 

>>> from django.db import connection 

>>> connection.queries [{'sql': 'SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date FROM polls_polls', 'time': '0.002'}]

sql # The raw SQL statement 
time # How long the statement took to execute, in seconds.

Reset the queries list returned above:
>>> from django import db
>>> db.reset_queries()

Show SQL for a given query (newer django version; 1.5+?)
>>> Product.objects.all().query.sql_with_params()

Show SQL for a given query (older django version; 1.2-1.5+?)
>>> Product.objects.all().query.as_sql()

http://docs.djangoproject.com/en/dev/faq/models/
http://docs.djangoproject.com/en/dev/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

http://stackoverflow.com/questions/2314920/django-show-log-orm-sql-calls-from-python-shell