kofa blogja

Ónos eső - Freezing rain

Why I picked ICDSoft when I got rid of 3ix.org

You may have read about the trouble I had with 3ix.org. Once I decided to leave them, I started searching for a new host. Wanted one that had proper support, and found ICDSoft (note: after taking a short detour into VPS hosting, I'm a happy ICDSoft customer again - 27 May, 2013). I can recommend them - they do provide what they offer, and their support is superb. What they offer is somewhat limited, though, e.g. you cannot get replies to be sent to the mailing list, as they cannot filter out out-of-office autoreplies. Also, there's no way to set up a web-based archive for ezmlm, as they don't make the necessary files (ezmlm archives) available to you. However, if you are not so much into mailing lists, and require a very reliable and affordable shared host, check them out. As for support, have a look at Network Status, Online Manual and Maintenance at suresupport.com.

They say a picture is worth a thousand words - check out support response times below:

 

DB2 SQL0901N column number out of range; using db2move; using db2look to transfer values of identity sequences

I ran into the error DB2 SQL error: SQLCODE: -901 (SQL0901N), SQLSTATE: 58004, SQLERRMC: column number out of range. I was using DB2 9.1.0 (Express-C). The problem came after migrating a database using a mixture of

  • ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE data_type
  • ALTER TABLE table_name DROP COLUMN colum_name

on a number of tables, always followed by REORG TABLE table_name. I had also enabled automatic maintenance using

UPDATE DB CFG USING auto_maint ON auto_db_backup OFF auto_tbl_maint ON auto_runstats ON auto_stats_prof ON auto_prof_upd ON auto_reorg ON stat_heap_sz 65536;
UPDATE DBM CONFIG USING HEALTH_MON ON;

This may have nothing to do with the problem.

Self-tuning had also been enabled as follows (again, I don't know whether this has anything to do with the problem):

UPDATE DBM CFG USING SHEAPTHRES 0;
UPDATE DB CFG FOR database_name USING SELF_TUNING_MEM ON;
UPDATE DB CFG FOR database_name USING DATABASE_MEMORY AUTOMATIC;
UPDATE DB CFG FOR database_name USING SORTHEAP AUTOMATIC;
UPDATE DB CFG FOR database_name USING SHEAPTHRES_SHR AUTOMATIC;
UPDATE DB CFG FOR database_name USING MAXLOCKS AUTOMATIC;
UPDATE DB CFG FOR database_name USING LOCKLIST AUTOMATIC;
UPDATE DB CFG FOR database_name USING SORTHEAP AUTOMATIC;
UPDATE DB CFG FOR database_name USING PCKCACHESZ AUTOMATIC;
ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 250 AUTOMATIC;

The query that caused trouble was of the form

SELECT * FROM table_name_1 t1 LEFT OUTER JOIN table_name_2 t2 ON t1.id = t2.t1_id.

A simple query

SELECT * FROM table_name_1 t1 JOIN table_name_2 t2 ON t1.id = t2.t1_id

worked fine.

The only solution I found was to create a new database and use db2move to move data to it, then drop and recreate the original database and run db2move again to move everything back.

If db2move reports SYSTOOLSPACE doesn't exist on the source database, you can create it manually:

db2 connect to database_name
db2 create tablespace systoolspace

You'll need to run db2move as follows (you'll need to create your temporary database beforehand):

db2move original_database_name copy -sn schema_name_to_copy -u user_name -p password -co TARGET_DB temporary_database_name NONRECOVERABLE

Note that when running db2move, the restart values of identity sequences are NOT moved. To do that, use db2look:

db2look -z schema_name -d original_database_name -e | find /i "restart with" >restart.sql

On UNIX/Linux, you should use "grep -i" instead of "find /i".

This will create a SQL script you can use on the new database:

db2 connect to new_database name
db2 -tf restart.sql

You'll need to do this after each db2move to transfer the correct values!

Oldalak

Subscribe to RSS - kofa blogja