Archive-name: comp.databases.db/db2-faq Posting-Frequency: weekly Last-modified: 2000-09-28 Version: 0.09 URL: http://www.harddiskcafe.de/db2faq/index.html xp: comp.databases.db2,news.answers,comp.answers +--------------------------------------------------------------+ | Mostly Harmless - The Hitchikers Guide for the IBM DB2 UDB | +--------------------------------------------------------------+ The simple DB2 FAQ v0.09 28.09.00 hohmann@harddiskcafe.de I can give only the warranty, that this document is full of spelling bugs and wastes space on your various media. Contributions, errata, additions, bug & spelling-fixes, reports of broken links are welcome. If your contribution is not listed in the next issue w/o notice, please resend your suggestion to me. The current and previous versions of this FAQ can be found here: http://www.harddiskcafe.de/db2faq/index.html ================================================================ Changes (+ new - removed * changed): to do -> + typical memory consumptions DB2JD, TCP/IP close connection... + faq-header according to guidelines found in http://www.faqs.org/faqs/news-answers/guidelines/index.html + additions in the header + additions in the contributions ================================================================ Chapters: 1. Preface 2. Related Links 3. Installation, starting and stopping DB2, using the tools 4. Common Problems & Questions (nearly unsorted) 5. Backup & Restore 6. Troubles with Java 7. Fixpacks 8. To Do Appendix A. Some remarkable Numbers X. Contributions ================================================================ Chapter 1. PREFACE ------ Common prerequisites, settings & sites to make the life easier: I. OS/2 1) a good source for files. Try hobbes.nmsu.edu or ftp.leo.org 2) a port scanner. A good one is available from Ralf Christen at http://www.horgen.net/rem/software/ 3) Theseus 3 (a Memory Analysis Program) available at ftp.software.ibm.com/ps/products/os2/fixes/v4warp/theseus3 4) "set sckillfeatureenabled=1" in your config.sys so you can watch & kill most Processes with ctrl + left Mouse Button on the Windowlist of the Warpcenter (the 2nd Icon from the Left) II. Windows III. Linux There is a "DB2 V7.1 for Linux Installation HOWTO" at http://www.linuxdoc.org/HOWTO/DB2-HOWTO/index.html, which describes how to install DB2 on the most common distributions and tells you how to get the Control Center running. IV. OS/390 ----- Homepage of DB2 http://www-4.ibm.com/software/data/db2/ ----- Support On the web The DB2 Technical Library (Manuals etc...) is here: http://www-4.ibm.com/software/data/db2/library/ Other searchable help is here: http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report Fixpacks The DB2 fixpacks are here: ftp://ftp.software.ibm.com/ps/products/db2/fixes (there are also so called 'interim fixpacks' which fixes some errors in advance to the next 'big' fixpack. See 'can i mix different language versions ?') Development The DB2 Application Development website can be found here: http://www.ibm.com/software/data/db2/udb/ad It contains updated information on appliation development issues (for example, documented the compiler settings for all supported platforms, document the JDBC methods and features that are not supported by the DB2 JDBC drivers, etc.) Newsgroups news://comp.databases.ibm-db2 Some of the developers and supporters from IBM around the world are here. Per voice Ask your local sales representative (never try 'hello ibm' or other efforts of IBM to centralise incoming calls). HINT: IBM is a very, very large and very decentralised company. So it's difficult to get somebody on the phone who can really help. On the other side: DB2 is expensive so IBM is interested to sell. If you're asking very (I mean VERY) polite you'll get masses of phone numbers and email addresses in and around the IBM world. Do not misuse but cultivate your connections - so you can get a lot of help even in the pre-sales phase. ----- Obtaining a legal copy of DB2 Direct download of the Developer Editions http://www-4.ibm.com/software/data/db2/udb/downloads.html#download It contains a single-user, full-function UDB licensed for evaluation, demonstrating and development of application programs (but NOT for a production environment). Ordering an evaluation copy on CD http://www-4.ibm.com/software/data/db2/udb/downloads.html#cd Order a 90 day evaluation copy of the Enterprise Edition. Hint: if you installed this evaluation copy, went to holidays, broke your legs and are now returning to your workstation again after 90 days, try to delete DB2SYSLT in \SQLLIB. Ordering with massive developer rebates: Try to obtain a commercial membership in the developer program of IBM. Its located at http://www.developer.ibm.com You should have a good reason (e.g. a software under development which uses some of the bigger tools of IBM) before knocking on the registration door. Hint: Although not clearly pointed out on the website you can also mention planned software or future projects of your company (if you are a standalone developer, you're company and member in one person). If your request for commercial membership is denied, try again with larger projects of your company. Microsoft can produce vapourware, why not you? When you got your ID and password,try http://www.developer.ibm.com/welcome/softmall.html and be surprised. Due to the fact, that you're reading this FAQ you're fully qualified to be a developer and using the Developer Editions. Make a deal with your local sales representative Torture your IBM Sales Office. Novell & Oracle are selling a Netware 5.1 + Oracle 8i Bundle with 5 Users for about 2.500 EURO. IBM supports startup companies, ask for a very good price. Rent a bazaar proven trader If you cannot haggle, look for somebody who can :-) ----- Which flavour of DB2 do I need for my Business? There are basically 4 Versions of DB2 (as far as I understand): 1) Personal Edition - good for notebooks. one user, very small 2) Workgroup Edition - You pay per processor and per user. 3) Enterprise Edition - You pay per processor, can connect to mainframes, no user limitations. 4) Enterprise Extended Edition (EEE) - (Don't know what the licensing is like) The other versions are only different in licensing. For example, there is a special "Internet Edition" of the Workgroup Edition with one user but unlimited connections. Ask your local IBM sales office. ----- ================================================================ Chapter 2. RELATED LINKS IBM Download Page (Visual Age for Java, WebSphere, Drivers...) http://www3.software.ibm.com/download/ The DB2 Magazine http://www.db2mag.com/ Latest News from the Personal Systems http://ps.software.ibm.com/pbin-usa-ps/getobj.pl?/pdocs-usa/fixnews.html Product suggestion OS/2: http://www2.software.ibm.com/os/warp/webreqs.nsf/page1?OpenForm DB2 UDB Cookbooks by Graeme Birchall PDF-Files with over 550 sample SQL statements, hints & tricks. http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM ------ ================================================================ Chapter 3. INSTALLATION, STARTING AND STOPPING DB2, USING THE TOOLS ----- When starting the Command Centre (DB2CC) or playing around with Java I get the Error "CLI0616E Error opening socket. SQLSTATE 08S01" 1) The DB2 java demon process is not running on the server or it is running on an unexpected port. Normally DB2JD is running on port 6790 but sometimes it starts on port 6789 or somewhere. a) make sure that DB2JD is running (if not, try DB2JSTRT in \SQLLIB\BIN b) start a port scanner and scan your server from port 6700 to 6800. c) You can either give a port number on the command line (DB2JSTRT 1234) or set an environment variable "DB2JD_PORT_NUMBER=1234" to use another port. If your demon is not listening on port 6790 you must either kill & restart it on the right port or change the startup scripts DB2CC[.bat/.cmd] and DB2IC[.bat/.cmd] to reflect the current port. 2) Your local TCP/IP configuration is not 100% perfect. YES, you can use the WWW, news and E-Mail with a not-so-perfect TCP/IP installation but fail with DB2 because the whole communication relies on some prerequisites in your intranet (and your Workstation is an intranet). You've some virtual devices on your computer. LAN0 for example is your network card, SLIP/PPP are devices to send TCP/IP data over a modem or ISDN and LO is your local interface which is normally assigned to the IP 127.0.0.1. It's NOT enough that you can 'ping' 127.0.0.1, your LO device must be UP. You can check this with 'ifconfig lo' (at least under OS/2 and most *NIX boxes) if its up. It should be flagged "UP, LOOPBACK, MULTICAST" on address 127.0.0.1. If not, try 'ifconfig lo 127.0.0.1 up' and check, if something like this is in your \mptn\bin\startup.cmd (at least under OS/2). Your HOSTS file in %ETC% (OS/2: \MPTN\ETC) should contain a line like "127.0.0.1 localhost" (without the quotes). After this operation you should connect to your local copy of DB2 via TCP/IP. 3) Your external TCP/IP configuration is not 100% perfect. Same as above. If you have no need to give your workstation a unique name, you should set your hostname to "127.0.0.1". If you have several workstations in your network, you should setup an intranet. There are some reserved holes in the IP address space for private use. You can use 192.168.1.0 to 192.168.255.255 or 10.0.0.1 to 10.0.255.255 for your private network. It's a good idea to install a DNS in your network, but planning and installing your intranet is beyond the scope of this document. 4) Use SNIFFLE /P (/P in Uppercase) to determine problems 5) If the advanced client tools are installed, you should take a look on PCTI.EXE, PCTN.EXE & PCTT.EXE in \SQLLIB\BIN (try /? as Parameter). These tools can act as a client/server tandem to check the communication. ----- I'd tried all above, my Java GUI is still not running :-( Sounds like you might have a JDK/JRE above 1.1.8, which is incompatible with DB2 UDB V6.1's Java GUI tools. Your choices are to download and install at least V6.1 fixpack 4 (APAR JR144790) or to remove your JDK/JRE and reinstall UDB (which will install the java 1.1.8 level). ----- When DB2 starts sometimes you will receive a message that "SQL5043N one of the communications protocols failed to start but base functionality is still available". This message can be caused by the settings for the NETBIOS NCBs, SESSIONS and NETBIOS names available for DB2 to access. You can either increase the values for these resources for an adapter in MPTS or you can decrease the number of these resources used by the local LAN Requester by modifying the "NET" line in the IBMLAN.INI file. This only applies if you have chosen NETBIOS as one of the communications protocols for DB2 to use. ----- After DB2 has started i find an entry like "SPM0438 DB2 Syncpoint Manager recovery log is bad" in my DB2DIAG.LOG The DB2 Syncpoint Manager recovry log is inconsistent due to indoubt transactions. To get rid of this message, find all SPM*.LOG files and remove them (AIX has a directory named 'SPMLOG' under \SQLLIB). If you do not need the Syncpoint Manager you can stop him by "DB UPDATE DBM CFG USING SPM_NAME NULL". For details search for SPM0438 in the techlib. ----- DB2STOP fails In most cases there are still users connected or applications running. You can verify this by "DB2 LIST APPLICATIONS". If you're sure that all remaining applications are dead or unused (very common during development) you can perform a "DB2 FORCE APPLICATIONS ALL" and then "DB2STOP" or simply "DB2STOP FORCE" which does the same thing. But take care: DB2 FORCE APPLICATIONS ALL and DB2STOP FORCE does not free all allocated ressources in DB2JD and DB2SYSC ! ----- I have trouble installing DB2 for NT, creating an instance, and or logging on. DB2 Version 5 and 6 installation requires userID's of 8 characters or less with Administrator authority. The default NT userID (Administrator) has too many characters. Create an userID with administrator authority that has 8 or less characters. ----- How to stop DB2 when re-installing on NT ? When running SETUP, I get a "DB2 is currently running and therefore can not be updated. Please stop all DB2 processes and/or services and try again." but I've done all of this. I've changed my system so that no DB2 services start and there are no DB2 related processes running on my system. Remember to do a db2admin stop. If you still have this problem, the usual culprit is Tivoli (TME), or other Systemview-related products. Look through the list of running services from the Control Panel and stop anything called TME, Tivoli, Systemview, or NetFinity. Other potential executables that you may want to stop from the task manager: - HTTPDL.exe (the mini-webserver for the HTML versions of the DB2 Doc's) - ntvdm.exe (NetView) - anything to do with Net.Question ----- I cannot enter SQL and DB2 commands from a system command prompt on NT or Windows. I get an error saying that the Command line environment is not initialised. You must issue DB2 commands and SQL from a DB2 Command window or DB2 Command Line Processor (try entering DB2CMD from the command prompt). Some information about this: The DB2 command line processor has a front-end process and a back-end process. On other operating systems (OS/2, Unix) matching these two processes is easy: if the parent process is killed, the child process gets terminated too. On NT, the operating system does not do this for us, so we cannot provide DB2 Command Line Processor support in an ordinary operating system command prompt. ----- I have installed the DB2 UDB client with tools to control remote databases, e.g, Control Center. I can connect to my a DB2 UDB server database. However, my authority to see things is curtailed. When clicking on the database name, only three items are listed: "Tables / Replication Sources / Replication Subscriptions" When you are performing remote administration, running the Control Center on a client against a DB2 server, and both client and server are DB2 Universal Database Version, manually cataloging the database node will not allow the remote Control Center to fully manage the server: for example, you could not issue remote backup against the server, and database server objects viewed from the Control Center would be limited to Tables and Replication. Manually cataloging the database node includes cataloging from the Command Center, the Command Line Processor, and manual configuration from the Client Configuration Assistant. To avoid this problem, you can configure the server from the Client Configuration Assistant by searching the network, or using a directed discover (where you supply the TCP/IP host name of the server, or the NetBIOS NNAME of the Administration Server at the database server). To get the NetBIOS NNAME of the Administration Server, issue: DB2 GET ADMIN CONFIGURATION To find the hostname, type hostname at an operating system prompt. If you have already cataloged the database and node manually, and your Control Center view of the database is restricted to Tables and Replication, you can catalog the Database Administration Server instance from the Control Center on the client by: a. Right-clicking on the workstation object (one level under Systems: it might be the NNAME of the database server or the hostname if TCP/IP is used) b. Select 'Change'. Under Protocol Parameters, enter the required connectivity information for the "DB2 Administration Server" (DAS) and click on "Retrieve". If you are using TCP/IP, the same host name used for the database server is used for the DAS at that workstation. If you are using NetBIOS, the DAS will have its own NNAME (which you can see by issuing "DB2 GET ADMIN CONFIGURATION" at the server) Make sure the operating system type is correct. Note, by default if the operating system type is "unknown", it will choose "Windows NT". Click on "OK" when finished. ================================================================ Chapter 4. COMMON PROBLEMS & QUESTIONS ----- Create an unique Row ID (autoincrement, identity ...) In DB2 < V7: 1) Use "GENERATE_UNIQUE()": ResultSet res = stmt.executeQuery("VALUES(GENERATE_UNIQUE())"); res.next(); String strUID = res.getString(1); GENERATE_UNIQUE() creates a 26char wide key, which is unique through your whole cluster and excellent to use it for an object identifier. 2) Use a Trigger (if your counter-column is your primary key it is a good idea to define it ass NOT NULL WITH DEFAULT. The trigger will always overwrite the default value, but this will allow rows to be inserted without a dummy value for the column). 3) For a trigger that handle multi-row inserts: Look in the samples section for the sample function called "db2Udf". In it you will find a method call ctr. Load the function as follows: CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME 'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION; Then do your insert with a trigger of (this is one for a ADDRESSES Table (adjust to fit)): CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER() FROM NULLID.ADDRESSES); END 4) By using a scalar subquery expression which refers to the highest given number in the table: INSERT INTO Foobar (key_col, ...) VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...); In DB2 V7.1: 1) Create your table with (or change it into): CREATE TABLE MYTABLE( MY_ID INT GENERATED ALWAYS AS IDENTITY START WITH 1 INCREMENT BY 1, ... MY_DATA VARCHAR(10), ....) 2) Insert your data as usual. 3) Grab the last created value with "VALUES(IDENTITY_VAL_LOCAL())" which should be "1" after the first insert in our example. NOTE: If IDENTITY_VAL_LOCAL() returns NULL (-), you probably have auto commit turned on. Use the command centre options to turn it off or try the +c option in CLP. You can also create before insert for each row triggers to generate a new key in pre V7.1 versions. Use the scratchpad counter UDF in sqllib\samples for multi row inserts. You can only have ONE identity column per table. ----- Retrieving only the first n-rows / Limit return from query: SELECT ... FETCH FIRST n ROWS ONLY OPTIMIZE FOR n ROWS -- OR -- FETCH FIRST ROWS can be also expressed like this: "SELECT FROM (SELECT , ROWNUMBER() OVER(ORDER BY ) as rn) AS ot WHERE rn <= " -- OR -- SELECT col1, col2, col3 FROM TABLE_1 t1 WHERE 10 > (SELECT COUNT(*) from TABLE_1 t2 WHERE t1.col1 < t2.col1) ----- Retrieving chunks or range of rows/data: If you haven't a special counter-row to retrieve the results in blocks (eg. for displaying the results on a website) you can help yourself by fetching a block with the FETCH FIRST clause and take the result as input to your next query. This only works, when your 'key' contains no duplicates. 1) "SELECT MYNAME FROM ADDRESS WHERE MYNAME >'' ORDER BY MYNAME FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS" Result: 'aaaaaaaa' 2) "SELECT MYNAME FROM ADDRESS WHERE MYNAME > 'aaaaaaaa' ORDER BY MYNAME FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS" Result: 'ffffffff' 3) Go on until no more rows can be fetched -- OR -- (DB2 v6 or later) SELECT * FROM (SELECT name, rownumber() over (order by name) AS rn FROM address) AS tr WHERE rn between 10 and 20 (The rownumber() function is documented in OLAP functions [in the v7 SQL Reference] or in the release-notes for v6) ----- Eliminating duplicates from a table Imagine a table like this (some sort of article-history): NUMBER NAME AMOUNT DATE ------ ------------ ------ ---------- 1 DB2 5 1999-06-02 2 Via Voice 10 1999-07-11 1 DB2 8 2000-04-21 3 Lotus Notes 8 2000-03-03 Lets delete duplicate article-numbers while keeping the newest entry now: 1) Create a view over the table (this is needed because DELETE cannot range over a query): CREATE VIEW tmp(rn) AS SELECT rownumber() OVER (PARTITION BY product_number ORDER BY date DESC) FROM inventory 2) Now delete the duplicates: DELETE FROM tmp WHERE rn > 1; 3) Check the result: SELECT * FROM inventory ORDER BY 1 NUMBER NAME AMOUNT DATE ------ ------------ ------ ---------- 1 DB2 8 04/21/2000 2 Via Voice 10 07/11/1999 3 Lotus Notes 8 03/03/2000 3 record(s) selected. ----- "Order by" in subqueries: The standard does not support ORDER BY in subquery. However SQL99 supports OLAP functions. In DB2 V6 and higher: INSERT INTO T1 (SELECT c1 FROM (SELECT c1, rownumber() over (order by c2) as rn FROM T2) AS T2 WHERE rn <= 20); ----- Migrating from Oracle: NLV <=> COALESCE DECODE <=> simple CASE expression V7.1 supports simple SQL functions which can be used to map functions and simplify migration. CONNECT BY <=> recursive common table expression (WITH rec() AS SELECT ... FROM root UNION ALL SELECT .. FROM rec, child WHERE rec.child = child.key SELECT * FROM rec;) Sequences can often be simulated with dedicated tables containing identity columns. Use stored procedures which insert a row and retrieve the identity for nextval. Caveat: Avoid updates and any indexes on the "sequence" tables to avoid concurrency problems. ----- Duplicating a Table You can use an export/import combination with ixf file format, or simply do CREATE TABLE newTable LIKE oldTable INSERT INTO newtable SELECT FROM oldTable ----- Is there a replacement for Oracle's "TRUNCATE TABLE" ? How can i delete the content of a Table fast ? 1) Create an empty file and run import in replace mode on the table with it: IMPORT FROM empty.del OF DEL REPLACE INTO YourTable 2) Do a LOAD REPLACE with an empty file 3) If you had created the table with the "NOT LOGGED INITIALLY" option you can use it to 'truncate' your table as follows: ALTER TABLE mytable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE (Please read up on the limitations for the NLI clause). ----- How can i change the name / type of an existing column ? If the table exists you can only add columns, change the description of a column or change the length of a varchar-field. There is now way to change the name or type of an existing column. You can't even drop columns. The only way is export the definitions of the table, rename the table, change the definition and recreate the table again. 1) db2look -d MYDATABASE -t MYTABLE -a -e -l -x -o MYTABLE.SQL 2) Now edit MYTABLE.SQL to your needs 3) Rename the table (or drop it if there wasn't any useful data in there) 4) Recreate the table with "DB2 -t -l MYTABLE.SQL" 5) INSERT into the new table by SELECTing from the old table. ----- Getting the output of a select without headings: 1) Use the -x flag for db2 command line processor. This is available in db2 v7.1 and greater 2) Use DB2BATCH to do similar stuff. "db2batch -q on -s off -d -f " ================================================================ Chapter 5. BACKUP & RESTORE ----- Can I back up a DB2 database on one operating system, like OS/2, and restore this to DB2 on another operating system, like NT? No. If you want to move a database from another operating system to another, you must export each table from the source database, and then import or LOAD each table into DB2 on the new location. You can capture the DDL (CREATE and ALTER statements) for the source database using the DB2LOOK tool with the -e option. You can automate the movement of data using the DB2MOVE tool, which is shipped with UDB or can be found on: ftp://ftp.software.ibm.com/ps/products/db2/tools/ (-> see "How can i change the name / type of an existing column" for an example of DB2LOOK) ----- What was it with "online backup, offline backup and the logfiles" ? Offlinebackups do not require the log files to restore the database. Online backups require that you have the log files available to restore the database and has a longer amount of recovery time.. ----- I ran into error SQL200C "There is not enough available memory available to run this utility when trying to restore from a backup imaga Try to increase DBHEAP size and UTIL_HEAP_SZ ----- Using Microsoft NT I am not able to backup to a network drive, or got an error message like "SYSTEM Does not have the authority to perform this command" Check the DB2 services under Services in Control Panel. Create an NT account with the proper authority, you can usually use whatever the server logs on with. Change the Startup values from Log on as System account to This account and use the account name you created. Change these on all services associated with DB2. As far as we know no one has been able to modify the rights for the SYSTEM account. ----- ================================================================ Chapter 6. TROUBLES WITH JAVA ----- Where can i find the drivers ? Any hints and warnings ? The java-drivers are in \SQLLIB\JAVA\DB2JAVA.ZIP. Don't extract this file, just add it into your classpath or link it with the -cp command of JRE (jre -cp \sqllib\java\db2java.zip ...) on runtime. BIG WARNING: the fixpacklevel of DB2JAVA.ZIP used on the client and DB2JD.EXE (the java-daemon on the server) MUST match or you'll get everything from strange behaviour to broken data. So you cannot even use the control center from v6, fixpack #3 to control a database v6 with fixpack #4. When a Server or Workstation gets a fixpack, so all others should updated too to avoid trouble. Unfortunately (in very rare situations) some files are NOT updated when applying a fixpack. If your applications shows more errors as before or you find unnormal entries in JDBCERR.LOG you should check at least that DB2JD.EXE has the same date/time as DB2JAVA.ZIP and both have the date/time of the fixpack. If not, you have to extract them by hand from the fixpack-distribution. How to use the driver ? Some words to the technique used by java.sql.*... The classes in java.sql.* are mainly stubs and interfaces, it is the work of the driver to fill them with life. A driver can be registered to the java-drivermanager with the procedure described in the documentation to java.sql.DriverManager or by hand in the code: try { Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance(); } catch (IllegalAccessException e) { } catch (InstantiationException e) { } catch (ClassNotFoundException e) { } With this command the driver registers itself to the DriverManager (you can register additional drivers for other databases too). How does the DriverManager know, which driver is good for which database ? The information is 'decoded' into the URL and the manager simply sends the URL to all registered drivers. If nobody feels responsible in charge of the URL, the answer will be the famous "No suitable driver" response. So if you get this, you should better look into the URL. ----- How to connect to DB2 from Java ? The magic URL is "jdbc:db2://hostname:port/database" // simple connector. needs try/catchblock String strHost = "192.168.2.32"; String strPort = "6790"; String strDatabase = "testdata"; String strUser = "someuser"; String strPwd = "somepassword"; String url = "jdbc:db2://" + strHost + ":" + strPort + "/" + strDatabase; con = DriverManager.getConnection(url, strUser, strPwd); // useful statements to do next Statement stmt = con.createStatement(); con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); stmt.execute("set schema " + strSchema.trim()); con.commit(); ----- Any other common traps ? Yes, a lot. DB2 is not a toy and the DB2JD-deamon is a harsh mistress. A java-coder will think, that the close of a connection will free all ressources allocated on the Server. But me and others did the painful experience, that it doesn't (at least under NT and OS/2). This leads to clogged memory and other nasty side-effects on the server. My explanation is speculative but it is (at least) good enough for a hypothesis to work with and to build a workaround: 1) a ressource like a resultset or statement is allocated 2) the connection will be closed some times later 3) the java garbage collection will call .finalize() in the resultsets / statements which tries to send a close-request to the DB2JD. But this is impossible because the connection is already closed - the ressorces on the server were not de-allocated. The proposed 'workaround' is simply to close the ressources by hand. The garbage collection should do this for you in the order 'last created - first finalized' but this needs at least a call to 'Runtime.runFinalizersOnExit(true)' before 'Sytem.exit(0)' because "by default, finalization on exit is disabled" (from the documentation of this method). And even this attempt is not problem-free because a lot of JVMs have trouble with the finalisation on exit. A possible (well used and tested) structure looks like this: public class MyReader { // declare all used ressources Connection con = null; Statement stmt = null; Resultset res = null; boolean blnActive = true; public MyReader() { con = .. get connection from somewhere } public void doSomething() { try { stmt = con.createStatement(); res = stmt.executeQuery("select * from...") ... stmt.close(); // closes resultset too ! stmt = con.createStatement(); res = stmt ..... stmt.close(); return; } catch (SQLException e) { System.out.println(e.getMessage()); throw e; // notify caller } finally { dispose(); // housekeeping } } public void dispose() { try { if (res != null) { res.close(); res = null; } if (stmt != null) { stmt.close(); stmt = null; } if (con != null) { con.close(); con = null; } blnActive = false; } catch (SQLException e) { //.. } } public void finalize() { if (blnActive) { System.out.println("Somebody forget to dispose() me: "+this.toString()); dispose(); } } } We put most of this stuff into a superclass and capsuled our SQL-operations into subclasses of it. It should be a good idea to create a separate tool with some static classes as helpers to close ressources (and open connections) without big try/catch-blocks. Example: public SQLutil { public static boolean close(Object o) { if (o == null) return true; try { if (o instanceof ResultSet) ((Resultset) o).close(); if (o instanceof Connection) ((Connection) o).close(); return true; } catch (SQLException e) { return false; } } } ----- How to insert/update a BLOB / read a BLOB ? Use a PreparedStatement and the .set????Stream(...) methods of it. byte [] data = ... String strQuery = "UPDATE myTable SET myBlob=? WHERE myKey=?"; PreparedStatement pstmt = con.prepareStatement(strQuery); pstmt.setBinaryStream(1,new ByteArrayInputStream(data),data.length); pstmt.setString(2,strMyKey); pstmt.execute(); // here we go ! pstmt.close(); You can use everything as source which is a subclass of InputStream (for strings use StringBufferInputStream). You can also store Objects: Object data = ... (Serializable !) String strQuery = "UPDATE byTable SET myBlob=? WHERE myKey=?"; PreparedStatement pstmt = con.prepareStatement(strQuery); ByteArrayOutputStream b = new ByteArrayOutputStream(); ObjectOutputStream out = new ObjectOutputStream(b); out.writeObject(data); byte [] dataAsByteArray = b.toByteArray(); pstmt.setBinaryStream(1,new ByteArrayInputStream(dataAsByteArray),dataAsByteArray.length); pstmt.execute(); pstmt.close(); Instead of pstmt.setBinaryStream(1,new ByteArrayInputStream(dataAsByteArray),dataAsByteArray.length); you can use "pstmt.setBytes(1, dataAsByteArray)" but this method seems to have a limit of 33124 bytes ! Reading a BLOB is easier: define the column in the query and grab it as an InputStream from the ResultSet. ----- How about some multithreading ? No problem - simply open a new connection per thread if you've concurrent queries. You can use one single connection but you've to synchronize on the connection to avoid that 2 or more queries are executed on the same connection at the same time. ----- ================================================================ Chapter 7. FIXPACKS ----- Can i mix different fixpack language versions ? Can i deploy the english interim fixpack on my latest german/italian... installation ? As far as i know: You can if the fixpack is the next one in the row (or an interims fix to your current fixpacklevel). But you have to live with responses in different languages. (Don't know what happens when a Chinese fixpack is installed over a western installation). ----- Fixpack-Installation needs LOT of time The Fixpack-Installation freezes at db2cc.ex_ Ignore it, get a large Cup of Coffee and relax. DB2CC.EX_ consists of a large Number of small Files and need about 15min to extract (even on a fast Machine). The Fixpack should be deployed after 25min. ================================================================ Chapter 8. TO DO ----- Sybase migration: -> SERGE, FASS !!!! :-) ================================================================ ================================================================ Appendix A: Some remarkable & magic numbers - the maximum length of tablenames are in v5: 18 chars in v6: 128 chars in v7: 128 chars - the maximum length of columnnames are in v5: 18 chars in v6: 30 chars in v7: 30 chars - Some precisions and remarks to datatypes INTEGER 11 digits SMALLINT 5 digits BIGINT 19 digits DECIMAL up to 21 digits precision. DECIMAL fields are stored and calculated in BCD arithmetic so you have not to deal with internal rounding-problems. ================================================================ Appendix X: Contributions ----- Mrs. Karin Leissner (karin at sternenpost.de) - for outstanding moral support - I love you, Karin The usual gang of idiots at conacom GmbH (http://www.conacom.de) - coffee, tea & cigarettes - webspace, big machines - a DB2 UDB under the wonderful OS/2 operating system Serge Rielau (srielau at ca dot ibm dot com) - patience answering my questions - unique rows in v7 - Order by in subquery - FETCH FIRST ROWS - Migrating from Oracle - Eliminating duplicate rows Melanie Mayer (melanie.mayer at dplanet.ch) - inserting blobs - inserting objects as blobs Lorne Sunley (lsunley at mb.sympatico.ca) - 'one of the communications protocols failed to start...' Daniel Scott (daniels at ca dot ibm dot com) - Pointer to Linux-HOWTO - Pointer to DB2 Application Development website Juan Lanus (jlanus at interar.com.ar) - Note on 'obtaining a legal copy of db2' - Link on db2mag Eveline Grosse (egrosse at de dot ibm dot com) - SPM0438 Syncpoint Manager recovery log Larry Edelstein (lsedels at us.ibm.com) - Java Version above 1.1.8 and DB2 UDB 6.1 Java GUI Fred Bloggs (iam_green at hotmail.com) - Another 'Fetch n-rows' variant Blair Adamache (adamache at ca dot ibm dot com) - sizes of tables/columns - autoincrements with triggers (v6) - getting a particular row or range of rows - note on "TRUNCAT TABLE" - restore to other operating systems Haider Rizvi, IBM DB2 UDB Performance Team - replacement for Oracle's "TRUNCATE TABLE" ? - Getting the output of a select without headings Mice Mcneer (mike.mcneer at umusic.com) - online/offline backups - SQL2009C - backup on a networkdrive with NT Doug Doole (doole at ca dot ibm dot com) - - Johann Sebastian Bach - the passions