DB2 Tips

  • List of tables within a specific schema:
    SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = ‘[schema]’ AND TYPE = ‘T’ ORDER BY NAME
  • List of columns in a particular database table:
    SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = ‘[schema]’ AND TBNAME = ‘[table]’ ORDER BY NAME
  • Database backup from DB2 command line:
    db2 backup database [db name] to [path to target folder]
  • Database restore from DB2 command line
    db2 restore database [db name in the backup] from [path to backup folder] [optional: into [another db name]]
    – into [another db name] is used if the database name needs to be different from the database name in the backup
  • Start and stop database engine from DB2 command line
    db2start
    db2stop [optional: force]
    – the optional parameter ‘force’ is used for stopping the database when there are applications still using the database, and the command ‘db2stop’ by itself would not work.
  • Get the current value of a sequence
    SELECT PREVVAL FOR [sequence name] FROM SYSIBM.SYSDUMMY1
  • Get the next value of a sequence
    SELECT NEXTVAL FOR [sequence name] FROM SYSIBM.SYSDUMMY1
    – causes the sequence number to increment as well
    SELECT PREVVAL FOR [sequence name] + 1 FROM SYSIBM.SYSDUMMY1
    – no increment, assuming the increment is 1, otherwise:
    SELECT VAL.VAL + INC.INC
    FROM (SELECT PREVVAL FOR [sequence name] AS VAL FROM SYSIBM.SYSDUMMY1) VAL,
                 (SELECT INCREMENT INC FROM SYSIBM.SYSSEQUENCES WHERE SEQNAME=’[sequence name]‘) INC
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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

%d bloggers like this: