Db2

From Null-pointer

Jump to: navigation, search

Contents

Running sql files

db2 -tvf mySql.sql

MYSQL commands

v9.7.2 onwards

you can get DB2 to accept mysql commands [1]

[root@localhost ~]# db2set DB2_COMPATIBILITY_VECTOR=MYS

[root@localhost ~]# db2stop

[root@localhost ~]# db2start

Importing data

comma delimited list

import FROM DATA.csv of del REPLACE INTO my_table

from a del file

  • export 
export TO 13846257_wh.del of del 
SELECT member_id, amount, currency_id, count, start_time, end_time, min_amount, max_amount FROM wager_history WHERE member_id = 13846257
  • import 
LOAD FROM 13846257_wh.del of del modified BY noeofchar noheader 
method P (1, 2, 3, 4, 5, 6, 7, 8) 
INSERT INTO wager_history (member_id, amount, currency_id, count, start_time, end_time, min_amount, max_amount)
[2]


[db2inst1@db00 /home/db2inst1/wager_history_test]# db2 "load from 13846257_wh.del of del modified by noeofchar noheader method P (1, 2, 3, 4, 5, 6, 7, 8) insert into wager_history (member_id, amount, currency_id, count, start_time, end_time, min_amount, max_amount)" SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database. SQL3109N The utility is beginning to load data from file "/home/db2inst1/wager_history_tests/13846257_wh.del". SQL3500W The utility is beginning the "LOAD" phase at time "19/01/2012 10:12:55.979120". SQL3112W There are fewer input file columns specified than database columns. SQL3519W Begin Load Consistency Point. Input record count = "0". SQL3520W Load Consistency Point was successful. SQL3110N The utility has completed processing. "181323" rows were read from the input file. SQL3519W Begin Load Consistency Point. Input record count = "181323". SQL3520W Load Consistency Point was successful. SQL3515W The utility has finished the "LOAD" phase at time "19/01/2012 10:12:59.443059". SQL3500W The utility is beginning the "BUILD" phase at time "19/01/2012 10:12:59.443352". SQL3213I The indexing mode is "REBUILD". SQL3515W The utility has finished the "BUILD" phase at time "19/01/2012 10:13:00.239502". Number of rows read = 181323 Number of rows skipped = 0 Number of rows loaded = 181323 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 181323

REORG

needs to be performed after an ALTER

Export a Blob

[root@localhost /users/paul.kane]# db2 "EXPORT TO myfile.del OF del LOBS TO /users/paul.kane MODIFIED BY lobsinfile SELECT blob_column FROM table_name"


Type 2 connection

DB2 Version 8 Connectivity Cheat Sheet[3]

[root@localhost ~]# db2 catalog tcpip node nodeName remote remoteIp server serverPort

[root@localhost ~]# db2 catalog db remoteDB at node nodeName

Error codes

db2 ? [error code]
db2 ? SQL[int]N

Error SQL1072C

[db2inst1@localhost ~]# db2stop SQL1072C The database manager resources are in an inconsistent state. The database manager might have been incorrectly terminated, or another application might be using system resources in a way that conflicts with the database manager's use of system resources. System resource cleanup might be required.

For single-node instances, perform the following as the instance owner from the sqllib directory [4]:

  • delete the .ftok file

[db2inst1@localhost sqllib]# rm .ftok

  • create a new .ftok file

[db2inst1@localhost sqllib]# touch .ftok



What settings does a table have

SELECT tabname, colname, identity FROM syscat.COLUMNS WHERE tabname LIKE = 'my_table_%'
[5]

Details about the identity column

SELECT colname, maxvalue FROM syscat.COLIDENTATTRIBUTES WHERE tablename = 'my_table'
[6]

How long does a query take

db2batch

Use db2batch

[root@localhost ~]# db2batch -d database -f sql.sql

[7]
  • ensure tables are fully qualified, e.g. user.table_name.
  • place the ; at the end of the statement.

time

[root@localhost ~]# time db2 "query"

  • time a simple command or give resource usage

See also


References

  1. ↑ Raul Chong. (10 Jun 2010). "What's new with DB2 Express-C 9.7.2" Retrieved 14 Mar 2011.
  2. ↑ "LOAD Command" Retrieved 19 Jan 2012.
  3. ↑ Raul Chong. (23 Jan 2003). "DB2 Version 8 Connectivity Cheat Sheet" Retrieved 17 Jan 2011.
  4. ↑ "SQL1072C" Retrieved 5 Apr 2011.
  5. ↑ "SYSCAT.COLUMNS catalog view" DB2 Version 9.7 for Linux, UNIX, and Windows. Retrieved 21 Sep 2011.
  6. ↑ "SYSCAT.COLIDENTATTRIBUTES catalog view" DB2 Version 9.7 for Linux, UNIX, and Windows. Retrieved 23 Sep 2011.
  7. ↑ "Tuning" Administration Guide: Performance.. Retrieved 8 Aug 2011.
Retrieved from "http://www.null-pointer.co.uk/wiki/index.php/Db2"
Personal tools