IDS Forum

Need help, OLTP vs. DSS system

Posted By:
Date:

hi!
i have one big database(around 500 tbls, 15 tbls foreach over 1 million recs) in one instance which supports many OLTP systems and some DSS systems.The diskIO is extremely busy for some tables which belongs to project AA and resides in one SSA loop. Other tables resides in another SSA loop are not so hot. this db resides in IBM-S80 machine that owns 12 CPU and 8GB memory. we don't enable LOG bc of older progs.
Question1: Should i make this db into 2 instances,one for OLTP and the other for DSS? if yes, how to make a hourly copy(we don't have LOG)?
Question2: What should i have to enhance the onconfig file also?(the onconfig file is listed below)
...
#-------------------------------------------------------------------****
#onconfig file, i only cut some lines that might need to be tuned
# System Configuration

SERVERNUM 1 # Unique id corresponding to a Dynamic Server
DBSERVERNAME A3 # Name of default database server
DBSERVERALIASES A1,A2 # List of alternate dbservernames
NETTYPE ipcshm,4,225,CPU # Configure poll thread(s) for nettype
NETTYPE soctcp,6,125,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 10 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters

LOCKS 200000 # Maximum number of locks
BUFFERS 160000 # Maximum number of shared buffera(old 160000)
NUMAIOVPS 16 # Number of IO vps
PHYSBUFF 256 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)
LOGSMAX 60 # Maximum number of logical log files
CLEANERS 8 # Number of buffer cleaner processes
SHMBASE 0x30000000 # Shared memory base address
SHMVIRTSIZE 1536000 # initial virtual shared memory segment size
SHMADD 512000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 150 # Check point interval (in sec)
LRUS 128 # Number of LRU queues
LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 3 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 96 # Stack size (Kbytes)

# CDR Variables
CDR_LOGBUFFERS 2048# size of log reading buffer pool (Kbytes)
CDR_EVALTHREADS 1,2# evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5# DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096# Maximum amount of memory for any CDR queue (Kb
CDR_LOGDELTA 30# % of log space allowed in queue memory
CDR_NUMCONNECT 16# Expected connections per server
CDR_NIFRETRY 300# Connection retry (seconds)
CDR_NIFCOMPRESS 0# Link level compression (-1 never, 0 none, 9 ma

# Read Ahead Variables
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 16 # Number of pages left before next group

DBSPACETEMP db_tmp # Default temp dbspaces

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 80# Maximum allowed pdqpriority
DS_MAX_QUERIES 6# Maximum number of decision support queries
DS_TOTAL_MEMORY 480000# Decision support memory (Kbytes)
DS_MAX_SCANS 1048576# Maximum number of decision support scans
DATASKIPoff# List of dbspaces to skip

OPTCOMPIND 0 # To hint the optimizer
ONDBSPACEDOWN 2 # NUE, 1 = ABORT,
LBU_PRESERVE 1 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1

# Optimizer DIRECTIVES ON (1/Default) or OFF (0)
DIRECTIVES 1

# Status of restartable restore
RESTARTABLE_RESTORE off

#--------------------------------------------------------------------****

any suggestion will be appreciated!...
Sean

IDS Forum is maintained by Administrator with WebBBS 5.12.