odifiac

Supply Chain
Intelligence

Database Setup
Guidelines
2013
Supply Chain Intelligence

Copyright and Disclaimer

Copyright © 2000 - 2013 Manhattan Associates, Inc. All rights reserved.

This documentation, as well as the software described in it, is furnished under license and may be used
or copied only in accordance with the terms of such license. The information in this documentation is
furnished for informational use only, is subject to change without notice, and should not be construed as a
commitment by Manhattan Associates, Inc. (“Manhattan”). No third party patent liability is assumed with
respect to the use of the information contained herein. While every precaution has been taken in the
preparation of this documentation, Manhattan assumes no responsibility for errors or omissions.

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, ALL CONTENT, MATERIALS AND

INFORMATION, ARE PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. MANHATTAN
EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS
FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT.

Except as permitted by license, no part of this documentation may be reproduced, stored in a retrieval
system, or transmitted, in any form by any means, electronic, mechanical, recording, or otherwise, without
the prior written permission of Manhattan Associates.

Manhattan Associates is a registered trademark of Manhattan Associates, Inc.

All other brands, products, or services are trademarks, registered trademarks, or service marks of their
respective companies or organizations.

Contact Address:

Manhattan Associates, Inc.

2300 Windy Ridge Parkway,
Atlanta, Georgia 30339

http://www.manh.com/

Region Telephone Email

+1 877.756.7435 (U.S. and Canada
Americas only) [email protected]
+1 404.965.4025

Database Setup Guidelines 2013

(00) 800.988.0885 (China only)
Asia [email protected]
+86 21 3311 3499
Europe, Middle East, +44 (0) 1344 318400 (UK)
[email protected]
and Africa +31 (0)30 214 3400 (NL)
Australia +61 1300787050 [email protected]

Unsure whom to contact? Call +1 404.965.4025 and you will be routed to the appropriate support
group.

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Table of Contents
Oracle Database Setup Guidelines ........................................................................................................... 4
Oracle Installation .................................................................................................................................. 4
Application/Database Server Time Zone Settings ................................................................................. 4
Pre-requisites for Oracle DB Creation ................................................................................................... 5
Configuring parameters for Oracle ........................................................................................................ 5
Creating table spaces ............................................................................................................................ 7
Content data store setup ....................................................................................................................... 7
Creating users and schema ................................................................................................................... 8
Remote Support: Database Server ........................................................................................................ 8
Backup Requirements: Database Server .............................................................................................. 8
DB2 Database Setup Guidelines ............................................................................................................... 9
DB2 Installation ...................................................................................................................................... 9
Application/Database Server Time Zone Settings ................................................................................. 9
Table space creation............................................................................................................................ 10
Configuring basic parameters for DB2................................................................................................. 11
User creation ........................................................................................................................................ 12
Recommended parameters for DB2 optimal performance .................................................................. 12
Remote Support: Database Server ...................................................................................................... 18
Backup Requirements: Database Server ............................................................................................ 18
Appendix for DB2 Environments: Verifying recommended parameters are implemented properly........ 19

Database Setup Guidelines 2013

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Oracle Database Setup Guidelines

This section provides information on installing and configuring Oracle on DB Server for SCI 2013
deployment. The server hardware recommendation is described in the Manhattan Associates’ SCI 2013
Technical Component guide.

Note: Manhattan Associates works with the customer to evaluate the sizing needs of the Database
Server if the customer wants to run additional applications on the same server.

Oracle Installation
Install the required Oracle version as per the vendor’s installation manual (Oracle AIX/Linux/HP-UX install
document.) Detailed explanation on the installation and setup of database servers is beyond the scope of
this installation guide.

Note: System Administrator root level privilege is essential for the Oracle server software installation.

Note: The server hosting the Application Server should have the required database client software
installed.

Application/Database Server Time Zone Settings

Ensure that both the application server and database server (hosting Oracle database) are in the same
time and time zone.

Database Setup Guidelines 2013

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Pre-requisites for Oracle DB Creation

The following step needs to be configured by your DBA before setting up the Oracle DB.

db_block_size 16384

Configuring parameters for Oracle

1. Configure the init.ora/spfile.ora file based on the parameters tabulated below.

*.db_files=500

*.open_cursors=2048

*.open_links=16

*.session_cached_cursors=1024

*.Processes=2000

*.db_file_multiblock_read_count=32

*.aq_tm_processes=1

*.db_file_multiblock_read_count=32

*.query_rewrite_enabled=TRUE

*.query_rewrite_integrity=trusted

*.star_transformation_enabled=TRUE

*.plsql_optimize_level=1

*.filesystemio_options=SETALL

*.DB_WRITER_PROCESSES=4

*.audit_trail=db_extended

*.nls_length_semantics=CHAR

*.nls_date_format='YYYY-MM-DD HH24:MI:SS' Database Setup Guidelines 2013

*.nls_timestamp_format='YYYY-MM-DD HH24:MI:SS:FF'

*.deferred_segment_creation=FALSE

undo_management AUTO

optimizer_mode ALL_ROWS

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

For more information about changing an instance configuration parameter, see the Oracle
Documentation.

2. Determine if the database is Unicode.

Tip: One method is to type the following select statement:

select * from NLS_DATABASE_PARAMETERS

The query above returns the following Oracle KEY parameters:

NLS_LENGTH_SEMANTICS=CHAR

(This could be changed after DB creation but before SCI schema setups )

NLS_CHARACTERSET=AL32UTF8

( Change to this require new DB instance creation )

NLS_NCHAR_CHARACTERSET=AL16UTF16

( change to this require new DB instance creation )

These parameters are mandatory for the SCI database to work.

Tip: To host more than one content store on your Oracle instance that will e used at the same
time, use a different user account for each content store to ensure that each Cognos 8 instance is
fully isolated from the others.

Database Setup Guidelines 2013

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Creating table spaces

This step must be performed by your DBA. Tablespaces in the below table needs to be created before
installing SCI.

Table spaces needed Purpose of the table space Example Tablespace Name
The tablespace that would hold SCI_DT_TBS
Data Tablespace the SCI schema tables and data

Index Tablespace The tablespace that would hold SCI_IDX_TBS

the SCI schema indexes
Temp Tablespace The temporary tablespace for TEMP
processing queries

It is recommended to use auto extend on all tablespaces for any production database.

• Database block size (DB_BLOCK_SIZE) for SCI will be set as16KB.

• Redo log file size / no. of groups as Log file of 500 MB each, with 6 different log groups.

Note: Enable Automatic Memory Management upon database creation. Set the memory_target,
memory_max_size and pga_aggregate_target parameters to the upper limit allowed by Oracle for this
database. Oracle automatically increases and decreases memory parameters as needed, to give
maximum performance.

Set the properties of the table’s space individual entries in createschema.properties before installing SCI.
See the below example – on how to configure it in createschema.properties file. (Refer SCI 2013
Installation Guide for more details).

Example:

_tablespace=SCI_DT_TBS

_temp_tablespace=TEMP

_index_tablespace=SCI_IDX_TBS

Note: It is critical that each tablespace be successfully created. Failure to do so results in errors further Database Setup Guidelines 2013
along the SCI installation process.

Content data store setup

Note: Ensure that the parameter for the database instance compatibility level of the content store
database is set to 9.0.1 or higher.

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Creating users and schema

The Oracle users and schema used by SCI are created during the SCI installation.

For details refer to the section Oracle Installation of the document SCI_2013 installation guide.

Remote Support: Database Server

In order to receive remote technical support from Manhattan Associates, the customer must provide
remote access to the Database Server.

Refer Manhattan Associates’ SCI 2013 Installation guide for more detail.

Backup Requirements: Database Server

Manhattan Associates recommends that the Database Server be included in any existing network backup
strategy.

Refer Manhattan Associates’ SCI 2013 Installation guide for more detail.

Database Setup Guidelines 2013

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

DB2 Database Setup Guidelines

This section provides information on installing and configuring DB2 on DB Server for SCI 2013
deployment. The server hardware recommendation is described in the Manhattan Associates’ SCI 2013
Technical Component guide.

Note: Manhattan Associates works with the customer to evaluate the sizing needs of the Database
Server if the customer wishes to run additional applications on the same server.

DB2 Installation
Install the required DB2 version (see SCI 2013 Technical Components guide for the certified/supported
versions) as per the vendor’s installation manual. (DB2 AIX install document.) Detailed explanation on the
installation and setup of database servers is beyond the scope of this installation guide.

Note: System Administrator root level privilege is essential for the DB2 server software installation. Root
privileges needed for DB2Connect install as well.

Note: The server hosting the Application Server should have the required database client software
installed.

Application/Database Server Time Zone Settings

Ensure that both the application server and database server (hosting DB2 database) are in the same
time and time zone.

Database Setup Guidelines 2013

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Table space creation

This step must be performed by your DBA. Below Tablespaces need to be created before installing SCI.

• Create SCI Database with CODESET UTF-8 ( Unicode) and PAGESIZE 32 K

• Create different buffer pools with require page size

Table spaces Purpose of the table Example Tablespace Example Buffer

Type space Name Pool
Data Tablespace This Tablespace will be SCI_DT_TBS BUFFERPOOL32
used for storing SCI (Created with
tables and table’s data. PAGESIZE of 32 K)
Index Tablespace This Tablespace will be SCI_IDX_TBS BUFFERPOOL32
used for storing SCI (Created with
indexes and indexed PAGESIZE of 32 K)
columns data.
Temp Tablespace This Tablespace will be USER_TEMPSPACE16K BUFFERPOOL16
used for storing SCI (Created with
temporary tables and PAGESIZE of 16 K)
query processing.

Set the properties of the table space individual entries in createschema.properties before installing SCI.
Refer to the below example on how to configure in createschema.properties file.

(Refer to SCI 2013 Installation guide for more detail on this).

Example,

_tablespace=SCI_DT_TBS

_temp_tablespace=TEMP

_index_tablespace=SCI_IDX_TBS

Note: It is critical that each tablespace be successfully created. Failure to do so results in errors further

Database Setup Guidelines 2013

along the SCI installation process.

10

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Configuring basic parameters for DB2

1. Determine if the database is Unicode by typing the following at the command prompt:

db2 get database configuration for database_name

The codepage is Unicode if it has a value of 1208.

2. If the codepage is not Unicode, create a new database that has a codepage value of 1208.
3. Ensure that you set the following database configuration parameters. These settings are also
provided in the scripts mentioned in the section further below.

Property Settings
Application heap AUTOMATIC
size(applheapsz)
LOCK Timeout 4 min. (or 240 sec).
Note: Do not set this to an
infinite timeout value.

Note: More configuration parameters recommended for optimal performance are listed under section –
Recommended parameters for DB2 optimal performance.

4. If you have already created the content store and are now creating a logging database, create an
additional regular user tablespace with a page size of 8K.
5. Determine which user account Cognos 8 will use to access the database.
6. Grant creates and drop table privileges on the database to the user account.
o Ensure that the user account has CREATETAB, CONNECT and IMPLICITSCHEMA
privileges for the database.
o Ensure that the user account has USE privileges for the USER TEMPORARY tablespace
and other appropriate tablespaces associates with the database.
o Ensure that the user account has GRANT privileges for the USER TEMPORARY
tablespace.

Database Setup Guidelines 2013

11

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

User creation
Create UNIX users corresponding to the DB2 DB schema before installing SCI.
Note: Refer DB2 Installation section in SCI Installation Guide for details of DB schema configuration in
createschema.properties file

Recommended parameters for DB2 optimal performance

The below steps must be performed by your DBA.

a) DB2 registry variables settings:

Execute the below shell scripts on db2 prompt or from a shell script file.

db2set DB2COMM=tcpip

db2set DB2_COMPATIBILITY_VECTOR=0F

db2set DB2_EVALUNCOMMITTED=ON

db2set DB2_FORCE_NLS_CACHE=TRUE

db2set DB2_PARALLEL_IO=*

db2set DB2_SELUDI_COMM_BUFFER=ON

db2set DB2_SKIPDELETED=ON

db2set DB2_SKIPINSERTED=ON

db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL REOPT ONCE"

db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON

db2set DB2_USE_IOCP=OFF

Database Setup Guidelines 2013

12

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

b) DB2 database manager configuration parameter settings

Apply the below database manager configuration settings to your SCI database.

db2 attach to <DB2 Instance name>

#for example db2inst1

db2 update dbm cfg using NUMDB 8

db2 update dbm cfg using FEDERATED NO

db2 update dbm cfg using DIAGLEVEL 3

db2 update dbm cfg using NOTIFYLEVEL 3

db2 update dbm cfg using DIAGPATH < Diagnostic data directory path >

db2 update dbm cfg using DFT_MON_BUFPOOL OFF

db2 update dbm cfg using DFT_MON_LOCK OFF

db2 update dbm cfg using DFT_MON_SORT OFF

db2 update dbm cfg using DFT_MON_STMT OFF

db2 update dbm cfg using DFT_MON_TABLE OFF

db2 update dbm cfg using DFT_MON_TIMESTAMP OFF

db2 update dbm cfg using DFT_MON_UOW OFF

db2 update dbm cfg using SYSADM_GROUP DB2SYADM

db2 update dbm cfg using SYSCTRL_GROUP DB2SYCTL

db2 update dbm cfg using SYSMAINT_GROUP DB2MAINT

db2 update dbm cfg using SYSMON_GROUP DB2MON

db2 update dbm cfg using SRV_PLUGIN_MODE UNFENCED

Database Setup Guidelines 2013

db2 update dbm cfg using SRVCON_AUTH NOT_SPECIFIED

db2 update dbm cfg using AUTHENTICATION SERVER

db2 update dbm cfg using CATALOG_NOAUTH NO

db2 update dbm cfg using TRUST_ALLCLNTS YES

db2 update dbm cfg using TRUST_CLNTAUTH CLIENT

db2 update dbm cfg using FED_NOAUTH NO

db2 update dbm cfg using DFTDBPATH <give a path here>

13

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

db2 update dbm cfg using MON_HEAP_SZ AUTOMATIC

db2 update dbm cfg using JAVA_HEAP_SZ 16384

db2 update dbm cfg using AUDIT_BUF_SZ 0

db2 update dbm cfg using INSTANCE_MEMORY AUTOMATIC

db2 update dbm cfg using BACKBUFSZ 1024

db2 update dbm cfg using RESTBUFSZ 1024

db2 update dbm cfg using AGENT_STACK_SZ 2048

db2 update dbm cfg using SHEAPTHRES 0

db2 update dbm cfg using DIR_CACHE YES

db2 update dbm cfg using ASLHEAPSZ 15

db2 update dbm cfg using RQRIOBLK 32767

db2 update dbm cfg using QUERY_HEAP_SZ 1000

db2 update dbm cfg using UTIL_IMPACT_LIM 10

db2 update dbm cfg using AGENTPRI SYSTEM

db2 update dbm cfg using NUM_POOLAGENTS AUTOMATIC

db2 update dbm cfg using NUM_INITAGENTS 0

db2 update dbm cfg using MAX_COORDAGENTS AUTOMATIC

db2 update dbm cfg using MAX_CONNECTIONS AUTOMATIC

db2 update dbm cfg using KEEPFENCED YES

db2 update dbm cfg using FENCED_POOL AUTOMATIC

db2 update dbm cfg using NUM_INITFENCED 0

Database Setup Guidelines 2013

db2 update dbm cfg using INDEXREC RESTART

db2 update dbm cfg using TM_DATABASE 1ST_CONN

db2 update dbm cfg using RESYNC_INTERVAL 180

db2 update dbm cfg using SPM_LOG_FILE_SZ 256

db2 update dbm cfg using SPM_MAX_RESYNC 20

db2 update dbm cfg using SVCENAME < TCP/IP service name >

db2 update dbm cfg using DISCOVER SEARCH

14

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

db2 update dbm cfg using DISCOVER_INST ENABLE

db2 update dbm cfg using MAX_QUERYDEGREE ANY

db2 update dbm cfg using INTRA_PARALLEL YES

db2 update dbm cfg using FEDERATED_ASYNC 0

db2 update dbm cfg using FCM_NUM_BUFFERS AUTOMATIC

db2 update dbm cfg using FCM_NUM_CHANNELS AUTOMATIC

db2 update dbm cfg using CONN_ELAPSE 10

db2 update dbm cfg using MAX_CONNRETRIES 5

db2 update dbm cfg using MAX_TIME_DIFF 60

db2 update dbm cfg using START_STOP_TIME 10

db2 detach

c) DB2 Database configuration parameter settings

Apply the below database configuration settings to your SCI database

db2 connect to <Database name>

db2 update db cfg using DISCOVER_DB ENABLE

db2 update db cfg using DFT_QUERYOPT 5

db2 update db cfg using DFT_DEGREE ANY

db2 update db cfg using DFT_SQLMATHWARN NO

db2 update db cfg using DFT_REFRESH_AGE 0

db2 update db cfg using DFT_MTTB_TYPES SYSTEM

db2 update db cfg using NUM_FREQVALUES 10

Database Setup Guidelines 2013

db2 update db cfg using NUM_QUANTILES 20

db2 update db cfg using DECFLT_ROUNDING ROUND_HALF_EVEN

db2 update db cfg using SELF_TUNING_MEM ON

db2 update db cfg using DATABASE_MEMORY AUTOMATIC

db2 update db cfg using DB_MEM_THRESH 25

db2 update db cfg using LOCKLIST AUTOMATIC

db2 update db cfg using MAXLOCKS AUTOMATIC

15

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

db2 update db cfg using PCKCACHESZ AUTOMATIC

db2 update db cfg using SHEAPTHRES_SHR AUTOMATIC

db2 update db cfg using SORTHEAP AUTOMATIC

db2 update db cfg using DBHEAP AUTOMATIC

db2 update db cfg using CATALOGCACHE_SZ 4096

db2 update db cfg using LOGBUFSZ 4096

db2 update db cfg using UTIL_HEAP_SZ 32768

db2 update db cfg using BUFFPAGE 4096

db2 update db cfg using STMTHEAP AUTOMATIC

db2 update db cfg using APPLHEAPSZ AUTOMATIC

db2 update db cfg using APPL_MEMORY AUTOMATIC

db2 update db cfg using STAT_HEAP_SZ AUTOMATIC

db2 update db cfg using DLCHKTIME 10000

db2 update db cfg using LOCKTIMEOUT 240

db2 update db cfg using CHNGPGS_THRESH 30

db2 update db cfg using NUM_IOCLEANERS AUTOMATIC

db2 update db cfg using NUM_IOSERVERS AUTOMATIC

db2 update db cfg using INDEXSORT YES

db2 update db cfg using SEQDETECT YES

db2 update db cfg using DFT_PREFETCH_SZ AUTOMATIC

db2 update db cfg using TRACKMOD OFF

Database Setup Guidelines 2013

db2 update db cfg using DFT_EXTENT_SZ 32

db2 update db cfg using MAXAPPLS AUTOMATIC

db2 update db cfg using AVG_APPLS AUTOMATIC

db2 update db cfg using MAXFILOP 61440

db2 update db cfg using LOGPRIMARY 50

db2 update db cfg using LOGSECOND 100

db2 update db cfg using LOGFILSIZ <value>

16

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

#. Production sizing requirement should decide this value. This

parameter in combination with LOGPRIMARY AND LOGSECOND decides
TRANSACTION LOG File space. For example .

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

Number of primary log files (LOGPRIMARY) = 50

Number of secondary log files (LOGSECOND) = 100

This works out to approximately 128 MB Log file size

( 32768*4096 (4KB) /1024/1024) and Total of 18.75 GB

Transaction Log file space.

db2 update db cfg using NEWLOGPATH < Location of log files >

db2 update db cfg using BLK_LOG_DSK_FUL NO

db2 update db cfg using MAX_LOG 0

db2 update db cfg using NUM_LOG_SPAN 0

db2 update db cfg using SOFTMAX 100

db2 update db cfg using NUMARCHRETRY 5

db2 update db cfg using ARCHRETRYDELAY 20

db2 update db cfg using AUTORESTART ON

db2 update db cfg using INDEXREC SYSTEM

db2 update db cfg using LOGINDEXBUILD OFF

db2 update db cfg using DFT_LOADREC_SES 1

db2 update db cfg using NUM_DB_BACKUPS 12

db2 update db cfg using REC_HIS_RETENTN 366

db2 update db cfg using AUTO_DEL_REC_OBJ OFF Database Setup Guidelines 2013

db2 update db cfg using AUTO_MAINT OFF

db2 update db cfg using AUTO_DB_BACKUP OFF

db2 update db cfg using AUTO_TBL_MAINT OFF

db2 update db cfg using AUTO_RUNSTATS OFF

db2 update db cfg using AUTO_STMT_STATS OFF

17

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

db2 update db cfg using AUTO_REORG OFF

db2 update db cfg using ENABLE_XMLCHAR YES

db2 update db cfg using WLM_COLLECT_INT 0

db2 connect reset

Note: Once the settings are applied recycle the SCI database instance

Note: Runstats is performed internally by SCI application, so no need to explicitly perform it.

d) Altering buffer pool (optional )

Production DBA can alter the buffer pools. To determine the best size for your buffer pools, run your
workload with the buffer pool sizes set to different values and measure the differences in operations
per second.

Remote Support: Database Server

In order to receive remote technical support from Manhattan Associates, the customer must provide
remote access to the Database Server.

Refer Manhattan Associates’ SCI 2013 Installation guide for more detail.

Backup Requirements: Database Server

Manhattan Associates recommends that the Database Server be included in any existing network backup
strategy.

Refer Manhattan Associates’ SCI 2013 Installation guide for more detail.

Database Setup Guidelines 2013

18

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

Appendix for DB2 Environments: Verifying

recommended parameters are implemented
properly
The sqls below can be used as checkpoints to run queries on the dictionary views, to verify that the SCI
recommended parameters are implemented properly in the DB2 environment. This applies to DB2
environments only.

i. For checking if Registry parameters are implemented properly.

WITH SUGG_REG_VARIABLES (REG_VAR_NAME, REG_VAR_VALUE) AS

(VALUES

('DB2COMM', 'TCPIP'),

('DB2_COMPATIBILITY_VECTOR', '0F'),

('DB2_EVALUNCOMMITTED', 'ON'),

('DB2_FORCE_NLS_CACHE', 'TRUE'),

('DB2_PARALLEL_IO', '*'),

('DB2_SELUDI_COMM_BUFFER', 'ON'),

('DB2_SKIPDELETED', 'ON'),

('DB2_SKIPINSERTED', 'ON'),

('DB2_SQLROUTINE_PREPOPTS', 'ISOLATION UR BLOCKING ALL REOPT

ONCE'),

('DB2_USE_ALTERNATE_PAGE_CLEANING', 'ON'),

('DB2_USE_IOCP', 'OFF')

SELECT REG_VAR_NAME, REG_VAR_VALUE FROM SUGG_REG_VARIABLES Database Setup Guidelines 2013

MINUS

SELECT SUBSTR(UPPER(REG_VAR_NAME),1,50) AS REG_VAR_NAME,

SUBSTR(UPPER(REG_VAR_VALUE),1,50) AS REG_VAR_VALUE FROM
SYSIBMADM.REG_VARIABLES ;

19

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

ii. For checking if Instance parameters are implemented properly.

SELECT UPPER(NAME) AS DBM_PARAMETER_NAME, SUBSTR(UPPER(VALUE),1,100) AS

CURRENT_VALUE FROM SYSIBMADM.DBMCFG

WHERE

(UPPER(NAME) = 'NUMDB' AND (UPPER(VALUE) <> '8' AND

UPPER(VALUE_FLAGS) <> '8')) OR

(UPPER(NAME) = 'FEDERATED' AND (UPPER(VALUE) <> 'NO' AND

UPPER(VALUE_FLAGS) <> 'NO')) OR

(UPPER(NAME) = 'DIAGLEVEL' AND (UPPER(VALUE) <> '3' AND

UPPER(VALUE_FLAGS) <> '3')) OR

(UPPER(NAME) = 'NOTIFYLEVEL' AND (UPPER(VALUE) <> '3' AND

UPPER(VALUE_FLAGS) <> '3')) OR

(UPPER(NAME) = 'DFT_MON_BUFPOOL' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF'))

OR

(UPPER(NAME) = 'DFT_MON_LOCK' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_MON_SORT' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_MON_STMT' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_MON_TABLE' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_MON_TIMESTAMP' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF'))

OR

Database Setup Guidelines 2013

(UPPER(NAME) = 'DFT_MON_UOW' AND (UPPER(VALUE) <> 'OFF' AND
UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'SRV_PLUGIN_MODE' AND (UPPER(VALUE) <> 'UNFENCED' AND

UPPER(VALUE_FLAGS) <> 'UNFENCED'))

OR

(UPPER(NAME) = 'SRVCON_AUTH' AND (UPPER(VALUE) <> 'NOT_SPECIFIED' AND

UPPER(VALUE_FLAGS) <> 'NOT_SPECIFIED'))

20

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

OR

(UPPER(NAME) = 'AUTHENTICATION' AND (UPPER(VALUE) <> 'SERVER' AND

UPPER(VALUE_FLAGS) <> 'SERVER'))

OR

(UPPER(NAME) = 'CATALOG_NOAUTH' AND (UPPER(VALUE) <> 'NO' AND

UPPER(VALUE_FLAGS) <> 'NO')) OR

(UPPER(NAME) = 'TRUST_ALLCLNTS' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'TRUST_CLNTAUTH' AND (UPPER(VALUE) <> 'CLIENT' AND

UPPER(VALUE_FLAGS) <> 'CLIENT'))

OR

(UPPER(NAME) = 'FED_NOAUTH' AND (UPPER(VALUE) <> 'NO' AND

UPPER(VALUE_FLAGS) <> 'NO')) OR

(UPPER(NAME) = 'MON_HEAP_SZ' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'JAVA_HEAP_SZ' AND (UPPER(VALUE) <> '16384' AND

UPPER(VALUE_FLAGS) <> '16384')) OR

(UPPER(NAME) = 'AUDIT_BUF_SZ' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'INSTANCE_MEMORY' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'BACKBUFSZ' AND (UPPER(VALUE) <> '1024' AND

UPPER(VALUE_FLAGS) <> '1024')) OR

(UPPER(NAME) = 'RESTBUFSZ' AND (UPPER(VALUE) <> '1024' AND

Database Setup Guidelines 2013

UPPER(VALUE_FLAGS) <> '1024')) OR

(UPPER(NAME) = 'AGENT_STACK_SZ' AND (UPPER(VALUE) <> '1024' AND

UPPER(VALUE_FLAGS) <> '1024'))

OR

(UPPER(NAME) = 'SHEAPTHRES' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'DIR_CACHE' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

21

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

(UPPER(NAME) = 'ASLHEAPSZ' AND (UPPER(VALUE) <> '15' AND

UPPER(VALUE_FLAGS) <> '15')) OR

(UPPER(NAME) = 'RQRIOBLK' AND (UPPER(VALUE) <> '32767' AND

UPPER(VALUE_FLAGS) <> '32767')) OR

(UPPER(NAME) = 'QUERY_HEAP_SZ' AND (UPPER(VALUE) <> '1000' AND

UPPER(VALUE_FLAGS) <> '1000'))

OR

(UPPER(NAME) = 'UTIL_IMPACT_LIM' AND (UPPER(VALUE) <> '10' AND

UPPER(VALUE_FLAGS) <> '10')) OR

(UPPER(NAME) = 'AGENTPRI' AND (UPPER(VALUE) <> 'SYSTEM' AND

UPPER(VALUE_FLAGS) <> 'SYSTEM' AND UPPER(VALUE) <> '-1'))

OR

(UPPER(NAME) = 'NUM_POOLAGENTS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'NUM_INITAGENTS' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'MAX_COORDAGENTS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'MAX_CONNECTIONS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'KEEPFENCED' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'FENCED_POOL' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

Database Setup Guidelines 2013

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'NUM_INITFENCED' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'INDEXREC' AND (UPPER(VALUE) <> 'RESTART' AND

UPPER(VALUE_FLAGS) <> 'RESTART')) OR

(UPPER(NAME) = 'TM_DATABASE' AND (UPPER(VALUE) <> '1ST_CONN' AND

UPPER(VALUE_FLAGS) <> '1ST_CONN'))

22

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

OR

(UPPER(NAME) = 'RESYNC_INTERVAL' AND (UPPER(VALUE) <> '180' AND

UPPER(VALUE_FLAGS) <> '180')) OR

(UPPER(NAME) = 'SPM_LOG_FILE_SZ' AND (UPPER(VALUE) <> '256' AND

UPPER(VALUE_FLAGS) <> '256')) OR

(UPPER(NAME) = 'SPM_MAX_RESYNC' AND (UPPER(VALUE) <> '20' AND

UPPER(VALUE_FLAGS) <> '20')) OR

(UPPER(NAME) = 'DISCOVER' AND (UPPER(VALUE) <> 'SEARCH' AND

UPPER(VALUE_FLAGS) <> 'SEARCH')) OR

(UPPER(NAME) = 'DISCOVER_INST' AND (UPPER(VALUE) <> 'ENABLE' AND

UPPER(VALUE_FLAGS) <> 'ENABLE'))

OR

(UPPER(NAME) = 'MAX_QUERYDEGREE' AND (UPPER(VALUE) <> 'ANY' AND

UPPER(VALUE_FLAGS) <> 'ANY' AND UPPER(VALUE) <> '-1'))

OR

(UPPER(NAME) = 'INTRA_PARALLEL' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'FEDERATED_ASYNC' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'FCM_NUM_BUFFERS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'FCM_NUM_CHANNELS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC'))

OR

(UPPER(NAME) = 'CONN_ELAPSE' AND (UPPER(VALUE) <> '10' AND

Database Setup Guidelines 2013

UPPER(VALUE_FLAGS) <> '10')) OR

(UPPER(NAME) = 'MAX_CONNRETRIES' AND (UPPER(VALUE) <> '5' AND

UPPER(VALUE_FLAGS) <> '5')) OR

(UPPER(NAME) = 'MAX_TIME_DIFF' AND (UPPER(VALUE) <> '60' AND

UPPER(VALUE_FLAGS) <> '60')) OR

(UPPER(NAME) = 'START_STOP_TIME' AND (UPPER(VALUE) <> '10' AND

UPPER(VALUE_FLAGS) <> '10'))

23

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

iii. For checking if Database parameters are implemented properly.

SELECT UPPER(NAME) AS DB_PARAMETER_NAME, SUBSTR(UPPER(VALUE),1,100) AS

CURRENT_VALUE FROM SYSIBMADM.DBCFG

WHERE

(UPPER(NAME) = 'DISCOVER_DB' AND (UPPER(VALUE) <> 'ENABLE' AND

UPPER(VALUE_FLAGS) <> 'ENABLE')) OR

(UPPER(NAME) = 'DFT_QUERYOPT' AND (UPPER(VALUE) <> '5' AND

UPPER(VALUE_FLAGS) <> '5')) OR

(UPPER(NAME) = 'DFT_DEGREE' AND (UPPER(VALUE) <> 'ANY' AND

UPPER(VALUE_FLAGS) <> 'ANY' AND UPPER(VALUE) <> '-1')) OR

(UPPER(NAME) = 'DFT_SQLMATHWARN' AND (UPPER(VALUE) <> 'NO' AND

UPPER(VALUE_FLAGS) <> 'NO')) OR

(UPPER(NAME) = 'DFT_REFRESH_AGE' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'DFT_MTTB_TYPES' AND (UPPER(VALUE) <> 'SYSTEM' AND

UPPER(VALUE_FLAGS) <> 'SYSTEM')) OR

(UPPER(NAME) = 'NUM_FREQVALUES' AND (UPPER(VALUE) <> '10' AND

UPPER(VALUE_FLAGS) <> '10')) OR

(UPPER(NAME) = 'NUM_QUANTILES' AND (UPPER(VALUE) <> '20' AND

UPPER(VALUE_FLAGS) <> '20')) OR

(UPPER(NAME) = 'DECFLT_ROUNDING' AND (UPPER(VALUE) <> 'ROUND_HALF_EVEN'

AND UPPER(VALUE_FLAGS) <> 'ROUND_HALF_EVEN')) OR

(UPPER(NAME) = 'SELF_TUNING_MEM' AND (UPPER(VALUE) <> 'ON' AND

UPPER(VALUE_FLAGS) <> 'ON' AND UPPER(VALUE) NOT LIKE 'ON (ACTIVE)')) OR

(UPPER(NAME) = 'DATABASE_MEMORY' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'DB_MEM_THRESH' AND (UPPER(VALUE) <> '25' AND

Database Setup Guidelines 2013

UPPER(VALUE_FLAGS) <> '25')) OR

(UPPER(NAME) = 'LOCKLIST' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'MAXLOCKS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'PCKCACHESZ' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

24

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

(UPPER(NAME) = 'SHEAPTHRES_SHR' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'SORTHEAP' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'DBHEAP' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'CATALOGCACHE_SZ' AND (UPPER(VALUE) <> '4096' AND

UPPER(VALUE_FLAGS) <> '4096')) OR

(UPPER(NAME) = 'LOGBUFSZ' AND (UPPER(VALUE) <> '4096' AND

UPPER(VALUE_FLAGS) <> '4096')) OR

(UPPER(NAME) = 'UTIL_HEAP_SZ' AND (UPPER(VALUE) <> '32768' AND

UPPER(VALUE_FLAGS) <> '32768')) OR

(UPPER(NAME) = 'BUFFPAGE' AND (UPPER(VALUE) <> '4096' AND

UPPER(VALUE_FLAGS) <> '4096')) OR

(UPPER(NAME) = 'STMTHEAP' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'APPLHEAPSZ' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'APPL_MEMORY' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'STAT_HEAP_SZ' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'DLCHKTIME' AND (UPPER(VALUE) <> '10000' AND

UPPER(VALUE_FLAGS) <> '10000')) OR

(UPPER(NAME) = 'LOCKTIMEOUT' AND (UPPER(VALUE) <> '240' AND

UPPER(VALUE_FLAGS) <> '240')) OR

(UPPER(NAME) = 'NUM_IOCLEANERS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'NUM_IOSERVERS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND Database Setup Guidelines 2013
UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'INDEXSORT' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'SEQDETECT' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'DFT_PREFETCH_SZ' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

25

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

(UPPER(NAME) = 'TRACKMOD' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_EXTENT_SZ' AND (UPPER(VALUE) <> '32' AND

UPPER(VALUE_FLAGS) <> '32')) OR

(UPPER(NAME) = 'MAXAPPLS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'AVG_APPLS' AND (UPPER(VALUE) <> 'AUTOMATIC' AND

UPPER(VALUE_FLAGS) <> 'AUTOMATIC')) OR

(UPPER(NAME) = 'MAXFILOP' AND (UPPER(VALUE) <> '61440' AND

UPPER(VALUE_FLAGS) <> '61440')) OR

(UPPER(NAME) = 'LOGFILSIZ' AND (UPPER(VALUE) <> '32768' AND

UPPER(VALUE_FLAGS) <> '32768')) OR

(UPPER(NAME) = 'LOGPRIMARY' AND (UPPER(VALUE) <> '50' AND

UPPER(VALUE_FLAGS) <> '50')) OR

(UPPER(NAME) = 'LOGSECOND' AND (UPPER(VALUE) <> '100' AND

UPPER(VALUE_FLAGS) <> '100')) OR

(UPPER(NAME) = 'BLK_LOG_DSK_FUL' AND (UPPER(VALUE) <> 'NO' AND

UPPER(VALUE_FLAGS) <> 'NO')) OR

(UPPER(NAME) = 'MAX_LOG' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'NUM_LOG_SPAN' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0')) OR

(UPPER(NAME) = 'NUMARCHRETRY' AND (UPPER(VALUE) <> '5' AND

UPPER(VALUE_FLAGS) <> '5')) OR

(UPPER(NAME) = 'ARCHRETRYDELAY' AND (UPPER(VALUE) <> '20' AND

UPPER(VALUE_FLAGS) <> '20')) OR

Database Setup Guidelines 2013

(UPPER(NAME) = 'AUTORESTART' AND (UPPER(VALUE) <> 'ON' AND
UPPER(VALUE_FLAGS) <> 'ON')) OR

(UPPER(NAME) = 'INDEXREC' AND (UPPER(VALUE) <> 'SYSTEM' AND

UPPER(VALUE_FLAGS) <> 'SYSTEM')) OR

(UPPER(NAME) = 'LOGINDEXBUILD' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'DFT_LOADREC_SES' AND (UPPER(VALUE) <> '1' AND

UPPER(VALUE_FLAGS) <> '1')) OR

26

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.

Supply Chain Intelligence

(UPPER(NAME) = 'NUM_DB_BACKUPS' AND (UPPER(VALUE) <> '12' AND

UPPER(VALUE_FLAGS) <> '12')) OR

(UPPER(NAME) = 'REC_HIS_RETENTN' AND (UPPER(VALUE) <> '366' AND

UPPER(VALUE_FLAGS) <> '366')) OR

(UPPER(NAME) = 'AUTO_DEL_REC_OBJ' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_MAINT' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_DB_BACKUP' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_TBL_MAINT' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_RUNSTATS' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_STMT_STATS' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'AUTO_REORG' AND (UPPER(VALUE) <> 'OFF' AND

UPPER(VALUE_FLAGS) <> 'OFF')) OR

(UPPER(NAME) = 'ENABLE_XMLCHAR' AND (UPPER(VALUE) <> 'YES' AND

UPPER(VALUE_FLAGS) <> 'YES')) OR

(UPPER(NAME) = 'WLM_COLLECT_INT' AND (UPPER(VALUE) <> '0' AND

UPPER(VALUE_FLAGS) <> '0'))

Database Setup Guidelines 2013

27

Copyright © 2013 Manhattan Associates, Inc. All rights reserved.