i have a big problem with imports. I have only see this problem on 9.5, i have never goten it on 9.1.
99% of time db2 works great ( version 9.5 fixpack 5 ) on windows 2008 ( microsoft cluster solution ).
When we do imports we get the following error ( once in a while ... but it is frequent enough to worry us ).
Error from log:
Expand|Select|Wrap|Line Numbers
- 16.09.2010 15:17:32.679 -> MBSCPTU : Running command = CALL ADMIN_CMD('IMPORT FROM \\10.1.18.147\d$\MBS\HIPERM\VERSION\SERVER\REPLIKACIJA\MBSCPTU\IDPODATKINABLAGAJNI.IXF OF IXF LOBS FROM \\10.1.18.147\d$\MBS\HIPERM\VERSION\SERVER\REPLIKACIJA\MBSCPTU MODIFIED BY LOBSINFILE METHOD N (IDPOBL_ID,IDTBLA_ID,IDSKLA_ID,IDPOBL_TABELA,IDPOBL_DATUM,IDPOBL_STEVILOVRSTIC,IDPOBL_STEVILOSYSDANES,IDPOBL_SUMPK,IDPOBL_PRENESENO,SYS_UPORABNIK,SYS_HANDLER,SYS_SPREMEMBA,SYS_VERZIJA) MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.IDPODATKINABLAGAJNI (IDPOBL_ID,IDTBLA_ID,IDSKLA_ID,IDPOBL_TABELA,IDPOBL_DATUM,IDPOBL_STEVILOVRSTIC,IDPOBL_STEVILOSYSDANES,IDPOBL_SUMPK,IDPOBL_PRENESENO,SYS_UPORABNIK,SYS_HANDLER,SYS_SPREMEMBA,SYS_VERZIJA)')
- 16.09.2010 15:17:32.710 -> MBSCPTU : WARNING = COM.ibm.db2.jdbc.app.DB2Warning: [IBM][CLI Driver][DB2/NT64] SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL3037", was encountered during the execution. More information is available.
- 16.09.2010 15:17:32.710 -> MBSCPTU : #################### WARNING ############################
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows read = 0
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows skipped = 0
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows inserted = 0
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows updated = 0
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows rejected = 0
- 16.09.2010 15:17:32.710 -> MBSCPTU : Number of rows committed = 0
- 16.09.2010 15:17:32.726 -> MBSCPTU : SQL0437W = Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602
Please advise. Nothing in db2diaglog.
I have changed instance memory to maximum, also set it to automatic... I have changed appl_memory to maximum and to automatic, same with database memory parameter.
Current parameters ( DBM ):
Expand|Select|Wrap|Line Numbers
- Database Manager Configuration
- Node type = Enterprise Server Edition with local and remote clients
- Database manager configuration release level = 0x0c00
- Maximum total of files open (MAXTOTFILOP) = 16000
- CPU speed (millisec/instruction) (CPUSPEED) = 4,093650e-007
- Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1,000000e+002
- Max number of concurrently active databases (NUMDB) = 64
- Federated Database System Support (FEDERATED) = YES
- Transaction processor monitor name (TP_MON_NAME) =
- Default charge-back account (DFT_ACCOUNT_STR) =
- Java Development Kit installation path (JDK_PATH) = C:\PROGRA~1\IBM\SQLLIB\java\jdk
- Diagnostic error capture level (DIAGLEVEL) = 3
- Notify Level (NOTIFYLEVEL) = 3
- Diagnostic data directory path (DIAGPATH) =
- Default database monitor switches
- Buffer pool (DFT_MON_BUFPOOL) = OFF
- Lock (DFT_MON_LOCK) = ON
- Sort (DFT_MON_SORT) = OFF
- Statement (DFT_MON_STMT) = OFF
- Table (DFT_MON_TABLE) = OFF
- Timestamp (DFT_MON_TIMESTAMP) = OFF
- Unit of work (DFT_MON_UOW) = OFF
- Monitor health of instance and databases (HEALTH_MON) = OFF
- SYSADM group name (SYSADM_GROUP) =
- SYSCTRL group name (SYSCTRL_GROUP) =
- SYSMAINT group name (SYSMAINT_GROUP) =
- SYSMON group name (SYSMON_GROUP) =
- Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
- Client Kerberos Plugin (CLNT_KRB_PLUGIN) = IBMkrb5
- Group Plugin (GROUP_PLUGIN) =
- GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
- Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
- Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
- Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
- Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
- Cluster manager (CLUSTER_MGR) =
- Database manager authentication (AUTHENTICATION) = SERVER
- Cataloging allowed without authority (CATALOG_NOAUTH) = NO
- Trust all clients (TRUST_ALLCLNTS) = YES
- Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
- Bypass federated authentication (FED_NOAUTH) = NO
- Default database path (DFTDBPATH) = G:
- Database monitor heap size (4KB) (MON_HEAP_SZ) = 16384
- Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
- Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
- Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(62359672)
- Backup buffer default size (4KB) (BACKBUFSZ) = 1024
- Restore buffer default size (4KB) (RESTBUFSZ) = 1024
- Agent stack size (AGENT_STACK_SZ) = 31
- Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32
- Private memory threshold (4KB) (PRIV_MEM_THRESH) = 20000
- Sort heap threshold (4KB) (SHEAPTHRES) = 0
- Directory cache support (DIR_CACHE) = YES
- Application support layer heap size (4KB) (ASLHEAPSZ) = 32
- Max requester I/O block size (bytes) (RQRIOBLK) = 32767
- Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
- Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 50
- Priority of agents (AGENTPRI) = SYSTEM
- Agent pool size (NUM_POOLAGENTS) = 100
- Initial number of agents in pool (NUM_INITAGENTS) = 0
- Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(1500)
- Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
- Keep fenced process (KEEPFENCED) = YES
- Number of pooled fenced processes (FENCED_POOL) = 1500
- Initial number of fenced processes (NUM_INITFENCED) = 0
- Index re-creation time and redo index build (INDEXREC) = RESTART
- Transaction manager database name (TM_DATABASE) = 1ST_CONN
- Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
- SPM name (SPM_NAME) = MBSHIP2
- SPM log size (SPM_LOG_FILE_SZ) = 256
- SPM resync agent limit (SPM_MAX_RESYNC) = 20
- SPM log path (SPM_LOG_PATH) =
- NetBIOS Workstation name (NNAME) =
- TCP/IP Service name (SVCENAME) = db2c_DB2
- Discovery mode (DISCOVER) = SEARCH
- Discover server instance (DISCOVER_INST) = ENABLE
- Maximum query degree of parallelism (MAX_QUERYDEGREE) = 1
- Enable intra-partition parallelism (INTRA_PARALLEL) = NO
- Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0
- No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
- No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
- Node connection elapse time (sec) (CONN_ELAPSE) = 10
- Max number of node connection retries (MAX_CONNRETRIES) = 5
- Max time difference between nodes (min) (MAX_TIME_DIFF) = 60
- db2start/db2stop timeout (min) (START_STOP_TIME) = 10
Expand|Select|Wrap|Line Numbers
- Database Configuration for Database
- Database configuration release level = 0x0c00
- Database release level = 0x0c00
- Database territory = si
- Database code page = 1208
- Database code set = UTF-8
- Database country/region code = 386
- Database collating sequence = IDENTITY
- Alternate collating sequence (ALT_COLLATE) =
- Number compatibility = OFF
- Varchar2 compatibility = OFF
- Database page size = 4096
- Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
- Discovery support for this database (DISCOVER_DB) = ENABLE
- Restrict access = NO
- Default query optimization class (DFT_QUERYOPT) = 5
- Degree of parallelism (DFT_DEGREE) = 1
- Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
- Default refresh age (DFT_REFRESH_AGE) = 0
- Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
- Number of frequent values retained (NUM_FREQVALUES) = 10
- Number of quantiles retained (NUM_QUANTILES) = 20
- Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN
- Backup pending = NO
- Database is consistent = NO
- Rollforward pending = NO
- Restore pending = NO
- Multi-page file allocation enabled = NO
- Log retain for recovery status = RECOVERY
- User exit for logging status = YES
- Self tuning memory (SELF_TUNING_MEM) = OFF
- Size of database shared memory (4KB) (DATABASE_MEMORY) = 30063408
- Database memory threshold (DB_MEM_THRESH) = 10
- Max storage for lock list (4KB) (LOCKLIST) = 250000
- Percent. of lock lists per application (MAXLOCKS) = 97
- Package cache size (4KB) (PCKCACHESZ) = 327680
- Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 65536
- Sort list heap (4KB) (SORTHEAP) = 131072
- Database heap (4KB) (DBHEAP) = 524288
- Catalog cache size (4KB) (CATALOGCACHE_SZ) = AUTOMATIC
- Log buffer size (4KB) (LOGBUFSZ) = 150
- Utilities heap size (4KB) (UTIL_HEAP_SZ) = 262144
- Buffer pool size (pages) (BUFFPAGE) = 6000
- SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(128000)
- Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(16384)
- Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(524288)
- Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(65536)
- Interval for checking deadlock (ms) (DLCHKTIME) = 10000
- Lock timeout (sec) (LOCKTIMEOUT) = -1
- Changed pages threshold (CHNGPGS_THRESH) = 80
- Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(23)
- Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(3)
- Index sort flag (INDEXSORT) = YES
- Sequential detect flag (SEQDETECT) = YES
- Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
- Track modified pages (TRACKMOD) = ON
- Default number of containers = 1
- Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
- Max number of active applications (MAXAPPLS) = 1500
- Average number of active applications (AVG_APPLS) = 15
- Max DB files open per application (MAXFILOP) = 65535
- Log file size (4KB) (LOGFILSIZ) = 16384
- Number of primary log files (LOGPRIMARY) = 210
- Number of secondary log files (LOGSECOND) = 25
- Changed path to log files (NEWLOGPATH) =
- Path to log files = W:\HIPERM\logs\NODE0000\NODE0000\
- Overflow log path (OVERFLOWLOGPATH) =
- Mirror log path (MIRRORLOGPATH) =
- First active log file = S0417772.LOG
- Block log on disk full (BLK_LOG_DSK_FUL) = NO
- Block non logged operations (BLOCKNONLOGGED) = NO
- Percent max primary log space by transaction (MAX_LOG) = 0
- Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
- Group commit count (MINCOMMIT) = 1
- Percent log file reclaimed before soft chckpt (SOFTMAX) = 440
- Log retain for recovery enabled (LOGRETAIN) = RECOVERY
- User exit for logging enabled (USEREXIT) = OFF
- HADR database role = STANDARD
- HADR local host name (HADR_LOCAL_HOST) =
- HADR local service name (HADR_LOCAL_SVC) =
- HADR remote host name (HADR_REMOTE_HOST) =
- HADR remote service name (HADR_REMOTE_SVC) =
- HADR instance name of remote server (HADR_REMOTE_INST) =
- HADR timeout value (HADR_TIMEOUT) = 120
- HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
- HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
- First log archive method (LOGARCHMETH1) = TSM
- Options for logarchmeth1 (LOGARCHOPT1) =
- Second log archive method (LOGARCHMETH2) = OFF
- Options for logarchmeth2 (LOGARCHOPT2) =
- Failover log archive path (FAILARCHPATH) =
- Number of log archive retries on error (NUMARCHRETRY) = 5
- Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
- Vendor options (VENDOROPT) =
- Auto restart enabled (AUTORESTART) = ON
- Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
- Log pages during index build (LOGINDEXBUILD) = OFF
- Default number of loadrec sessions (DFT_LOADREC_SES) = 1
- Number of database backups to retain (NUM_DB_BACKUPS) = 12
- Recovery history retention (days) (REC_HIS_RETENTN) = 366
- Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF
- TSM management class (TSM_MGMTCLASS) =
- TSM node name (TSM_NODENAME) =
- TSM owner (TSM_OWNER) =
- TSM password (TSM_PASSWORD) =
- Automatic maintenance (AUTO_MAINT) = OFF
- Automatic database backup (AUTO_DB_BACKUP) = OFF
- Automatic table maintenance (AUTO_TBL_MAINT) = OFF
- Automatic runstats (AUTO_RUNSTATS) = OFF
- Automatic statement statistics (AUTO_STMT_STATS) = OFF
- Automatic statistics profiling (AUTO_STATS_PROF) = OFF
- Automatic profile updates (AUTO_PROF_UPD) = OFF
- Automatic reorganization (AUTO_REORG) = OFF
- Enable XML Character operations (ENABLE_XMLCHAR) = YES
- WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
With regards,
Marko