MVSFORUMS.com

[vani]

At offshore we are using UDB 7.2 on NT. Onsite we are having UDB 8.1.2 on AIX 5.2.
We have a Stored Procedure which executes on UDB on NT for about 5 minutes. The same Stored Procedure executes UDB on AIX for about 1 hour 15 minutes. Both the cases, the number of records in the input and output tables are the same in both NT and AIX. The only change in the SP is to use Permanent table with Indexes instead of Temporary table with indexes, as version 7.2 does not allow Indexes on temporary table.
Appendix A contains the Database parameters for UDB on NT.
Appendix B contains the Database parameters for UDB on AIX.

We are not sure why the same SP is taking more time in AIX than in NT. Please help us to resolve.

Appendix A(UDB on NT config.)
-----------------------------
Database Configuration for Database cmf_dev



Database configuration release level = 0x0900

Database release level = 0x0900



Database territory = US

Database code page = 1252

Database code set = IBM-1252

Database country code = 1



Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE



Directory object name (DIR_OBJ_NAME) =

Discovery support for this database (DISCOVER_DB) = ENABLE



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

Number of frequent values retained (NUM_FREQVALUES) = 10

Number of quantiles retained (NUM_QUANTILES) = 20



Backup pending = NO



Database is consistent = NO

Rollforward pending = NO

Restore pending = NO



Multi-page file allocation enabled = NO



Log retain for recovery status = NO

User exit for logging status = NO



Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60

Data Links Number of Copies (DL_NUM_COPIES) = 1

Data Links Time after Drop (days) (DL_TIME_DROP) = 1

Data Links Token in Uppercase (DL_UPPER) = NO

Data Links Token Algorithm (DL_TOKEN) = MAC0



Database heap (4KB) (DBHEAP) = 10000

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 16

Log buffer size (4KB) (LOGBUFSZ) = 8

Utilities heap size (4KB) (UTIL_HEAP_SZ) = 6000

Buffer pool size (pages) (BUFFPAGE) = 1000

Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000

Number of extended storage segments (NUM_ESTORE_SEGS) = 0

Max storage for lock list (4KB) (LOCKLIST) = 100



Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 512



Sort list heap (4KB) (SORTHEAP) = 2046

SQL statement heap (4KB) (STMTHEAP) = 2048

Default application heap (4KB) (APPLHEAPSZ) = 2048

Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)

Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384



Interval for checking deadlock (ms) (DLCHKTIME) = 10000

Percent. of lock lists per application (MAXLOCKS) = 10

Lock timeout (sec) (LOCKTIMEOUT) = -1



Changed pages threshold (CHNGPGS_THRESH) = 60

Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1

Number of I/O servers (NUM_IOSERVERS) = 3

Index sort flag (INDEXSORT) = YES

Sequential detect flag (SEQDETECT) = YES

Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32



Track modified pages (TRACKMOD) = OFF



Default number of containers = 1

Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32



Max number of active applications (MAXAPPLS) = 40

Average number of active applications (AVG_APPLS) = 1

Max DB files open per application (MAXFILOP) = 64



Log file size (4KB) (LOGFILSIZ) = 5500

Number of primary log files (LOGPRIMARY) = 2

Number of secondary log files (LOGSECOND) = 20

Changed path to log files (NEWLOGPATH) =

Path to log files = C:DB2NODE0000SQL00003SQLOGDIR

First active log file =



Group commit count (MINCOMMIT) = 1

Percent log file reclaimed before soft chckpt (SOFTMAX) = 100

Log retain for recovery enabled (LOGRETAIN) = OFF

User exit for logging enabled (USEREXIT) = OFF



Auto restart enabled (AUTORESTART) = ON

Index re-creation time (INDEXREC) = SYSTEM (ACCESS)

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



TSM management class (TSM_MGMTCLASS) =

TSM node name (TSM_NODENAME) =

TSM owner (TSM_OWNER) =

TSM password (TSM_PASSWORD) =
========================================================================

Appendix B(UDB on AIX config.)
------------------------------
Database Configuration for Database btdcmfdr

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

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
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = NO

Log retain for recovery status = NO
User exit for logging status = NO

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 10000
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 20000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 100

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 512

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 2046
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 2048
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 5500
Number of primary log files (LOGPRIMARY) = 2
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files = /db2/d04-05c2p3114log/cpsdev/btdcmfdr/newlogpath/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active 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) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM (RESTART)
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

TSM management class (TSM_MGMTCLASS) = temp
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

[kolusu]

Vani,

I fully did not go thru your post but for starters , codeset and code page are totally different in the 2 environments.

[vani]

kolusu,

I am a junior DBA

[kolusu]

vani,

Good to know that. I would also appreciate if you can update all your posts with feedback if the proposed solutions worked out for you. This will help future search of the topics.

Kolusu

[vani]

sure will do that