Tag Archives: DB2
DB2 crashes when LOAD used on generated field
When trying to LOAD table data where one of the rows are generated, DB2 9.1 and 9.5 on Linux and AIX will crash if the generated field uses a user defined function (fenced or unfenced). IBM is aware of the … Continue reading
PostgreSQL to DB2 export conversion
PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and … Continue reading
DB2 The maximum number of open files has been reached
After a fresh install of DB2 Express C (free download from IBM) 9.5.2 on Linux AMD 64bit, I got the following error trying to load some tables for testing: SQL3500W The utility is beginning the “LOAD” phase at time “01/22/2009 … Continue reading
DB2 List of Tables
To retrieve to list of tables in DB2, you need to use the system tables, and then a little bit of sed. Here is how it goes: db2 “SELECT tabschema || ‘.’ || tabname FROM syscat.tables” | sed -e ‘/ … Continue reading
Clearing Locked Applications in DB2
When developing queries and stored procedures, it is possible to lock up access to the database. this can happen most frequently when autocommit is off, and a transaction is left hanging in a window. If all else fails, the application … Continue reading
DB2 INSERT INTO vs SELECT INTO
In DB2 if you need to populate a table, you need to use INSERT INTO, like in this example: INSERT INTO new_table SELECT col1,col2 FROM source_table WHERE col1=’something’; and if you need to populate query variable, you need to use … Continue reading
DB2 Epoch Conversion
As we have progresses with our move from PostgreSQL to DB2, we discovered that DB2 doesn’t have internal Epoch functions to deal with the epoch time format. Luckily, we are running DB2 on AIX, and most Unix tools are readily … Continue reading
Escape SQL apostrophe
Many drivers have ways to escape SQL strings to make sure no malicious activity is going on. Usually you can use a function in the driver that can take care of that. However, if all you need is too escape … Continue reading
DB2 Table Structure Alterations
When an attribute on a table is dropped, or the data type changes, DB2 sets the table into REORG PENDING state. That prevents a number of operations on the table, which can be problematic for a production database. If there … Continue reading
DB2 Query Optimization: Custom Stats Collection
One of the challenges in query optimization is to make sure that the query optimizer works with accurate estimates on cost of the different possible decision branches in the optimization process. The accuracy of the estimates depends on the efficiency … Continue reading
