Apr 09

Tivoli Storage Manager server upgrade from V6 to V7 fails with ANRI1043E

DB2, Uppgradering Comments Off on Tivoli Storage Manager server upgrade from V6 to V7 fails with ANRI1043E

Problem(Abstract)

A Tivoli Storage Manager server is upgraded from V6 to V7. The upgrade fails with the following error : ANRI1043E: An error occurred while dropping the DB2 instances.

Symptom

The following errors are logged :

=====> IBM Installation Manager> Error
ERROR: Error during "install" phase:
Details: ANRI1043E: An error occurred while dropping the DB2 instances. To review errors and correct any issues, review the log files in /var/ibm/InstallationManager/logs/native.

 

Cause

Orphaned DB2 instance causing the db2idrop command to fail during upgrade

Environment

Tivoli Storage Manager Server on Unix/Linux

 

Diagnosing the problem

 

1. Run the db2ilist command to verify the DB2 instances that are configured on the system. For example : # /opt/tivoli/tsm/db2/instance/db2ilist
tsmi tsmiold
In this case, it shows two instances, tsmi and tsmiold. The tsmiold instance is an instance that is no longer in use.
2. Run the db2greg command to verify the DB2 registry. For example : /opt/tivoli/tsm/db2/bin/db2greg -dump show V,DB2GPRF,DB2SYSTEM,xvotsmsrv01,/opt/tivoli/tsm/db2, I,DB2,9.7.0.6,tsmi,/home/tsmi/sqllib,,1,0,/opt/tivoli/tsm/db2,, V,DB2GPRF,DB2INSTDEF,tsmi,/opt/tivoli/tsm/db2, I,DB2,9.7.0.4,tsmiold,/home/tsmiv/sqllib,,1,0,/opt/tivoli/tsm/db2,, V,DB2GPRF,DB2FCMCOMM,TCPIP4,/opt/tivoli/tsm/db2, S,DB2,9.7.0.6,/opt/tivoli/tsm/db2,,,6,0,,1359560348,0 Again, in this case, the registry shows references to the tsmi and tsmiold instances
3. Run the db2idrop command to remove the old instance (tsmiold). The command fails with the following error : DBI1081E The file or directory /home/tsmiold/sqllib/bin is missing.

Resolving the problem

Remove the orphaned DB2 registry reference to the old instance (tsmiold) with the following command :

/opt/tivoli/tsm/db2/bin/db2greg -delinstrec instancename=tsmiold Retry the upgrade once the orphaned instance is removed.

 

written by Bosse

Mar 31

Tivoli Storage Manager database backup fails with ANR2984E

DB2 Comments Off on Tivoli Storage Manager database backup fails with ANR2984E

Problem(Abstract)

After an upgrade of the Tivoli Storage Manager server, database backup operations fail with error message ANR2984E.

Symptom

The following error is shown in the activity log:

ANR1360I Output volume O00046L5 opened (sequence number 1). ANR4626I Database backup will use 1 streams for processing with the number originally requested 1. ANR2984E Database backup terminated due to environment or setup issue related to DSMI_CONFIG – DB2 sqlcode -2033 sqlerrmc 406 . ANR1361I Output volume O00046L5 closed. ANR0515I Process 3 closed volume O00046L5.

 

Resolving the problem

To solve this issue, complete the following steps:

  1. Verify that the DB2_VENDOR_INI parameter is set correctly to tsmdbmgr.env. Run the following command: db2set -all
    If DB2_VENDOR_INI is not set correctly, issue the following command to update it: db2set -i DB2_VENDOR_INI=<instance_directory>\tsmdbmgr.env
  2. Stop the Tivoli Storage Manager server.
  3. Open a DOS Command window to start the Tivoli Storage Manager server in the foreground.
  4. Back up the Tivoli Storage Manager database.

written by Bosse

Oct 29

Tivoli Storage Manager DB2 ODBC user ID configuration

DB2, SQL, TSM Server 6 Comments Off on Tivoli Storage Manager DB2 ODBC user ID configuration

Problem(Abstract)

To access the Tivoli Storage Manager DB2 instance via an ODBC connection, a user ID must have the necessary permissions.

Resolving the problem

The user ID and password that is configured for ODBC access must be a valid UNIX user that has authority to the Tivoli Storage Manager database. For example, you can use the Tivoli Storage Manager server instance user ID and password. The user ID does not need to be part of a special UNIX group but it must have authority to the DB2 database. The user must also be allowed to log on.

You can grant authority to a separate user ID, other than the Tivoli Storage Manager server instance user ID, to access the database for ODBC query. This document provides two examples to grant authority to the user ID named db2odbc.
Example 1 :
To give the db2odbc user ID access to all tables for the Tivoli Storage Manager database, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant DATAACCESS, CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public

This series of commands grants less authority to the db2odbc user ID than what the Tivoli Storage Manager server instance user ID has. To grant even less authority, use example 2, which gives the user ID only select authority.
Example 2 :
To give the db2odbc user ID select authority only to a specific table, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. db2 grant select on TSMDB1.BACKUP_OBJECTS to user db2odbc

In this example, the db2odbc user ID is granted authority to run select statements only against the BACKUP_OBJECTS table. For each table for which you want the db2odbc user ID to have select authority, repeat the grant select command with that table name.
To view the available Tivoli Storage Manager tables, run the following command: db2 connect to TSMDB1 db2 "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'"
Example 3 :
To grant the db2odbc user ID select authority to all tables of a Tivoli Storage Manager running on AIX, log on with the Tivoli Storage Manager server instance user ID and run the following DB2 commands:

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. for tablename in $(db2 -x "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'") ; do db2 grant select on TSMDB1.${tablename} to user db2odbc ; done

Example 4 :
To grant the db2odbc user ID select authority to all tables of a Tivoli Storage Manager running on Windows, logon with the Windows account that runs the Tivoli Storage Manager server instance. Open a windows command prompt and enter “db2cmd”. This will open a new Windows command prompt. From the new command prompt, run the following db2 commands :

  1. db2 connect to TSMDB1
  2. db2 grant CONNECT on database to db2odbc
  3. db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user db2odbc
  4. db2 grant execute on package nullid.syssh200 to public
  5. db2 grant select on syscat.tables to user db2odbc
  6. db2 -x "select tabname from syscat.tables where tabschema='TSMDB1' and tabname not like '%(TEMP)'" > c:\temp\tsmtb.list
  7. for /F %N in (c:\temp\tsmtb.list) do db2 grant select on TSMDB1.%N to user db2odbc

In step 6, a file name, c:\temp\tsmtb.list, is used to write the output of the command. You can select any appropriate file to write this output into. This same file name must then be specified in step 7.

written by Bosse

Oct 16

Version 6 server fails to start with ANR9999D_3831306406

DB2 Errors Comments Off on Version 6 server fails to start with ANR9999D_3831306406

Problem(Abstract)

After a database restore of V6 Tivoli Storage Manager Server, the Server may fail to start with the following error during startup: . ANR9999D_3831306406 ReportSQLDiagInfo(dbieval.c:1406) Thread<1>: Missing sqlState=58005, sqlCode=-902 from table. Returning rc = 9994.

Symptom

You may see the following in the db2diag.log:

2012-08-27-18.02.55.525533-240 I2765065E1078 LEVEL: Error PID : 5337 TID : 140025270232864PROC : db2fm INSTANCE: tsminst NODE : 000 FUNCTION: DB2 Common, Fault Monitor Facility, fmGetInstallPath, probe:60 MESSAGE : ECF=0x90000521=-1879046879=ECF_FM_NO_PERMISSION_TO_INSTALL_PATH No proper access permisions to the install path

Cause

The database was restored using an instance that is different than the one used in the backed up database.

For example, the default instance name is tsminst1, but the customer used the instance name “tsminst”. You can see evidence of the change in the db2diag.log. The correct name: “INSTANCE: tsminst1” will be replaced with: “INSTANCE: tsminst ”

Resolving the problem

As the root user, run the following command making sure to use the correct instance name:

“db2iupdt <NewInstName>”
The server should now start.

written by Bosse

Sep 28

Collecting Data for Tivoli Storage Manager: Server Database Reorganization

DB2, TSM Server 6 Comments Off on Collecting Data for Tivoli Storage Manager: Server Database Reorganization
Gathering General Information

For supported levels of IBM Tivoli Storage Manager you can use IBM Support Assistant (ISA) to capture general information. Alternatively, you can also manually collect the general information.
Entering general information into an electronically-opened PMR (ESR)eliminates waiting on the phone to provide general information to Level 1 support.

Manually Gathering General Information

From a Tivoli Storage Manager Administrative command line client, enter the following commands:

  • QUERY SYSTEM > querysys.txt
  • QUERY ACTLOG begind=<mm/dd/yyyy> begint=<hh:mm> endd=<mm/dd/yyyy> endt=<hh:mm> > actlog.txt

– where begind and begint are the beginning date and time for the actlog entries being collected – where endd and endt are the ending date and time for the actlog entries being collected – the actlog gather should cover the full time frame of the issue/problem/scenario being diagnosed
Explicitly using the above commands will redirect the output to files called querysys.txt and actlog.txt in the Tivoli Storage Manager servers working directory. The names of these files can be changed and a full path can be specified to place the output in any desired directory using any desired name.
These files along with the following files/info should be included as general information:

  • dsmserv.opt
  • dsmserv.err
  • details of operating system levels
  • Tivoli Storage Manager Server specific version (ex: 6.2.3.0)
Manually Gathering Server Database Reorganization Information

If you are experiencing difficulties with server-initiated reorganization, follow the instructions in this section to gather the information that will be required by IBM Software Support:
1. Verify that you are running V6.1.5.10, 6.2.4, or 6.3.1 or later versions of the Tivoli Storage Manager server.
2. Indicate whether you are running data deduplication.
3. From a DB2 CLP window, run the following commands (for steps #3 thru #8) as the instance user while the Tivoli Storage Manager server is running:
db2 connect to tsmdb1 db2 set schema tsmdb1 db2pd -d tsmdb1 -reorg index > db2pd-reorg-index.txt db2pd -d tsmdb1 -runstats > db2pd-runstats.txt
4. Determine whether the database was created under Tivoli Storage Manager V6.1 or later versions.
In the following select
db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1)) as T1 where tbsp_id in (2,4,5,6)" > reclaimable_space.txt
the reclaimable_space_enabled column will be zero for server V6.1 databases, even if the system was later upgraded to server V6.2 or later.
Note that the mon_get_tablespace does not exist on V6.1 servers.
If the database was created under Tivoli Storage Manager V6.2 or later, the columns will be 1:

5. If you are experiencing unexplained issues with database growth, collect the following information:
db2 reorgchk current statistics on table all > db2reorgchk.txt
Important note: If you do not specify “current statistics,” the default is “update statistics,” which will run RUNSTATS commands on all tables in the database. This will likely have a huge performance impact and will take many days to complete.
db2pd -d tsmdb1 -tablespace > db2pd-tablespace.txt
After reorganization is run on all the tables, the output from
db2 "select count(*) as \"TableCount\" from global_attributes where owner='RDB' and name like 'REORG_TB_%'" > table_count.txt
will be at least 130.
After reorganization is run on all the indices on all the tables, the output from
db2 "select count(*) as \"Indices for TableCount\" from global_attributes where owner='RDB' and name like 'REORG_IX_%'" > index_count.txt
will be at least 130.
The following selects can be used to get the timestamps for table reorganizations and the tables for which indices have been reorganized:
db2 "select cast( substr(name,10,min(30,length(name)-9)) as char(30)) as \"Tablename\", substr(char(datetime),1,10) as \"Last Reorg\" from global_attributes where owner='RDB' and name like 'REORG_TB_%' and datetime is not NULL order by datetime desc" >
table_last_reorg.txt db2 "select cast( substr(name,10,min(30,length(name)-9)) as char(30)) as \"Indices for Tablename\", substr(char(datetime),1,10) as \"Last Reorg\" from global_attributes where owner='RDB' and name like 'REORG_IX_%' and datetime is not NULL order by datetime desc" >
index_last_reorg.txt

Items that will help detect lock-wait conditions:
db2 get snapshot for all applications >application.txt
db2 "select application_handle, elapsed_time_sec,
substr( stmt_text, 1, 512) as stmt_text from sysibmadm.mon_current_sql where elapsed_time_sec > 600" > application_handle.txt
db2pd -d tsmdb1 -wlocks >wlocks.out
db2 "SELECT agent_id FROM sysibmadm.applications
"WHERE appl_name='db2reorg' AND appl_status='LOCKWAIT' " > agent_id.txt

6. From a dsmadmc client, obtain the last 30 days of reorganization activity:
q actlog begindate=today-30 enddate=today search=anr029 > anr029.txt q actlog begindate=today-30 enddate=today search=anr031 > anr031.txt q actlog begindate=today-30 enddate=today search=anr033 > anr033.txt
Those queries are used to get the Tivoli Storage Manager view of table and index reorganization activity, and RUNSTATS activity.
7. Collect a trace collected while the reorganization window (REORGBEGINTIME + REORDURATION hours) is active, and ensure that a database backup is not running because reorganizations cannot run when a database backup is running. From a dsmadmc client, issue the following commands:
trace dis * trace ena TBREORG trace begin <valid_path_and_filename>
-> collect the trace for at least 1 hour
trace flush trace end trace dis *
8. As the instance user collect the output for the

    “db2support -d tsmdb1 -c -s -g”

command from a system shell.

Submitting Information to IBM Support

After a PMR is open, you can submit diagnostic troubleshooting data to IBM.
If using ESR, update the PMR to indicate that data has been sent.

Online Self-Help Resources
  • Review up-to-date product information at the Tivoli Storage Manager Product Support page.
  • Utilize the IBM Electronic Service Requesttool to access the Tivoli Storage Manager Support team when requiring assistance from IBM.
  • Use the IBM Support Assistant (ISA), this free cross product tool assists you in increasing your capacity for self-help. The Tivoli Storage Manager server has a plugin for the ISA tool.
  • Install and use the IBM Support Toolbar. This is a stand-alone application that allows you to easily search IBM.com for all types of software support content plus organizes the major areas of not only Software support, but the individual brand support sites into a concise application.
Related Information

Related information

Database Reorg Technote

written by Bosse

Sep 28

INTRA_PARALLEL

DB2 Comments Off on INTRA_PARALLEL

The exploitation of parallelism within a database and within an application accessing a database can also have a significant benefit for overall database performance, as well as the normal administrative tasks. There are two types of query parallelism that are available with DB2 UDB: inter-query parallelism and intra-query parallelism.

Inter-query parallelism refers to the ability of multiple applications to query a database at the same time. Each query will execute independently of the others, but DB2 UDB will execute them at the same time.

Intra-query parallelism refers to the ability to break a single query into a number of pieces and replicate them at the same time using either intra-partition parallelism or inter-partition parallelism, or both.

 

Intra-Partition Parallelism

Intra-partition parallelism refers to the ability to break up a query into multiple parts within a single database partition and execute these parts at the same time. This type of parallelism subdivides what is usually considered a single database operation, such as index creation, database load, or SQL queries into multiple parts, many or all of which can be executed in parallel within a single database partition. Intra-partition parallelism can be used to take advantage of multiple processors of a symmetric multiprocessor (SMP) server.

Intra-partition parallelism can take advantage of either data parallelism or pipeline parallelism. Data parallelism is normally used when scanning large indexes or tables. When data parallelism is used as part of the access plan for an SQL statement, the index or data will be dynamically partitioned, and each of the executing parts of the query (known as package parts) is assigned a range of data to act on. For an index scan, the data will be partitioned based on the key values, whereas for a table scan, the data will be partitioned based on the actual data pages.

Pipeline parallelism is normally used when distinct operations on the data can be executed in parallel. For example, a table is being scanned and the scan is immediately feeding into a sort operation that is executing in parallel to sort the data as it is being scanned.

Figure 2.2 shows a query that is broken into four pieces that can be executed in parallel, each working with a subset of the data. When this happens, the results can be returned more quickly than if the query was run serially. To utilize intra-partition parallelism, the database must be configured appropriately.

 

Figure 2.2. Intra-partition parallelism.

Intra-partition parallelism must be enabled for the DB2 instance before the queries can be executed in parallel. Once intra-partition parallelism is enabled, the degree of parallelism, or number of pieces of the query that can execute in parallel, can be controlled using database configuration parameters.

 

Configuring Intra-Partition Parallelism

Intra-partition parallelism in DB2 UDB is enabled or disabled using the database manager configuration parameter INTRA_PARALLEL. To enable intra-partition parallelism in DB2 UDB, the INTRA_PARALLEL configuration must be set to YES. This can be done using the following command:

UPDATE DBM CFG USING INTRA_PARALLEL YES

The degree of parallelism can then be controlled at the instance level, the database level, the application, or the statement level. The degree of parallelism can be set to a specific value or to ANY. If the degree of parallelism is set to ANY, the optimizer will determine the degree of parallelism for each individual SQL query that is submitted, based on the query itself and the number of CPUs available to the database or database partition.

Table 2.2 gives an overview of the parameters and options that are related to intra-partition parallelism in DB2 UDB.

Table 2.2. Controlling Intra-Partition Parallelism in DB2 UDB

Parameter Value
INTRA_PARALLEL YES/NODefaults to NO on uni-processor machineDefaults to YES on SMP machineIf changed, packages already bound will automatically be rebound at next execution.
MAX_QUERYDEGREE 1?32767, ANYDefaults to ANY; allows optimizer to choose degree of parallelism based on cost.No SQL executed on a database in this instance can use a degree of parallelism higher than this value.
DFT_DEGREE 1?32767, ANYDefaults to 1 (no parallelism)Provides the default value for:

CURRENT DEGREE special register

 

DEGREE bind option

 

Maximum for any SQL in this database

CURRENT DEGREE 1?32767, ANYSets degree of parallelism for dynamic SQLDefaults to DFT_DEGREE
DEGREE 1?32767, ANYSets degree of parallelism for static SQLDefaults to DFT_DEGREE

To change: PREP STATIC.SQL DEGREE n

RUNTIME DEGREE(SET RUNTIME DEGREE command) 1?32767, ANYSets degree of parallelism for running applicationsTo change:

SET RUNTIME DEGREE FOR (appid) to n

Affects only queries issued after SET RUNTIME is executed

DB2DEGREE(CLI configuration file) 1?32767, ANYDefault is 1Sets degree of parallelism for CLI applicationsCLI application issues a SET CURRENT DEGREE statement after database connection

 

The maximum degree of parallelism for an active application can be specified using the SET RUNTIME DEGREE command. The application can set its own run time degree of parallelism by using the SET CURRENT DEGREE statement. The actual run time degree used is the lower of:

  • MAX_QUERYDEGREE instance configuration parameter
  • Application run time degree
  • SQL statement compilation degree

More information on parallelism support in DB2 Universal Database can be found in the DB2 UDB Administration Guide: Performance.

For a multi-partitioned database on a large SMP server, the maximum degree of parallelism for each partition should be limited so that each partition does not attempt to use all of the CPUs on the server. This can be done using the MAX_QUERYDEGREE instance configuration parameter. For a 32-way SMP server with eight database partitions, the maximum degree of parallelism for each partition could be limited to four, as follows:

UPDATE DBM CFG USING MAX_QUERYDEGREE 4

For an SMP server with 16 CPUs, running two separate DB2 instances, the maximum degree of parallelism for each partition could be limited to eight, as follows:

UPDATE DBM CFG USING MAX_QUERYDEGREE 8

for a DB2 instance with two databases that has intra-partition parallelism enabled. The benefit of intra-partition parallelism is very different if one database is a data mart or data warehouse and has large, complex queries scanning a large amount of data and the other database is used as a journal and is accessed using only INSERT statements. In this case, the default degree of parallelism can be set to different values for each database. If the databases are named DMDB and JRNLDB, this can be done as follows:

UPDATE DB CFG for DMDB USING DFT_DEGREE 8 UPDATE DB CFG for JRNLDB USING DFT_DEGREE 1

For CLI, ODBC, and JDBC applications, the degree of parallelism is controlled using the db2cli.ini file. The following is an example db2cli.ini file where any application that connects to the SAMPLE database will use a degree of parallelism of four.

[common]
TRACE=1                               Turn the trace on
TRACECOMM=1                           Trace communications costs as well
TRACEFLUSH=1                          Flush the trace as it happens
TRACEPATHNAME=d:\trace                Directory for the trace.

; Comment lines start with a
semi-colon.

[sample]
DBALIAS=MYSAMP
DB2DEGREE=4
autocommit=0

To change the degree of parallelism for a currently executing SQL statement, the SYSADM can change the run time degree for an application. For an application with an application ID of 130, to change the degree of parallelism to 2, the following command can be used:

SET RUNTIME DEGREE FOR (130) to 2

NOTE

This change cannot affect the currently executing SQL statement but will be effective for all subsequent SQL statements.

 

 

written by Bosse

Jun 11

ANR0100E Error 105 creating table “OccupancyT”

DB2, Server Command, TSM Server 6, Uppgradering Comments Off on ANR0100E Error 105 creating table “OccupancyT”

Problem(Abstract)

The following message will be seen when trying to start the Tivoli Storage Manager server: ANR0100E adminit.c(2564): Error 105 creating table “OccupancyT”.

Symptom

Unable to start the Tivoli Storage Manager server.

Diagnosing the problem

Attempt to start the Tivoli Storage Manager server in the foreground (dsmserv -k instancename) will show the error. Also the following commands can be run from a DB2 command line to verify what accounts have the needed authority.

set db2instance=server1 db2 connect to TSMDB1 db2 “select grantee,securityadmauth from syscat.dbauth”
This should show that only the system accounts has the authority to create the table

Resolving the problem

There have been a few solutions reported for this error. These solutions will assume a database restore or server migration (moving of the Tivoli Storage Manager server) were not recently done.

1. Rebooting the system may resolve this issue. After a reboot, attempt to start the server again.

2. This error was maybe caused by using the wrong user. Use the same user that did the installation or use instance user.

3. The following can also be done to grant authority to the needed account.
For Windows 2008 – Download psexec
http://technet.microsoft.com/en-us/sysinternals/bb897553
– Once extracted, you can run the following command from a Windows command prompt in the directory where you extracted it. psexec -i -s cmd.exe

For Windows 2003 – From a Windows command prompt enter:
sc create testsvc binpath= “cmd /K start” type= own type= interact sc start testsvc
You should then have a new command prompts at c:\Windows\system32 running whoami at this command prompt should show: nt authority\system
You can then start the db2 command line prompt under this window: “C:\Program Files\Tivoli\TSM\db2\BIN\DB2CW.BAT”
Then to grant grant the needed authority, run the following commands
set db2instance=<instance> db2 connect to TSMDB1 db2 grant dbadm with dataaccess with accessctrl on database to user XXX db2 grant secadm on database to user XXX

written by Bosse

Dec 02

Database size, database reorganization, and performance considerations of Tivoli Storage Manager version 6 server

DB2, TSM Server 6 Comments Off on Database size, database reorganization, and performance considerations of Tivoli Storage Manager version 6 server

Problem(Abstract)

Unexpected database growth and reduced server performance can occur over time on a Tivoli Storage Manager version 6 server.

Symptom

The following symptoms may be encountered:

  1. The amount of space that is used by the database grows continuously over time, and is beyond expectations for the normal increase in client space requirements. Despite the use of server expiration and the number of objects in the server remaining the same or decreasing, the space that is used by the database continues to increase.
  2. Server performance is negatively impacted over time. For example, server operations run slower and slower over time, even though the workload has not changed.

 

Cause

Before server fixing levels 6.1.5.10 or 6.2.3.00, table and index reorganization did not work properly.

 

Resolving the problem

 

Servers running at a level below 6.1.5.10 or 6.2.3.00, should be upgraded to these levels or above. This will ensure the latest versions of the server reorganization code is running, providing the greatest benefit.
Hide details for Expected reorganization activityExpected reorganization activity
If server-initiated table and index reorganization is enabled, every table is reorganized, one-by-one, during the reorganization window. After all table reorganizations complete, table reorganization is followed by reorganization of the indices of each table. Depending on the amount of time that it takes to reorganize each table and its indices and the duration of the reorganization window each day, reorganization can take many days or weeks to finish. After a table or index reorganization completes, runstats is performed on the table. Depending on the size of the table, runstats can take several days to complete. After runstats completes, reorganization activity continues. Each table or index is reorganized once, and is not reorganized again for at least twenty days after it was first reorganized.

After the initial reorganization of all tables and the indices for all tables completes, the data is consolidated in the respective tables and index spaces. Free space is located at the end of the tables and the tablespaces. If this free space must be released to the operating system, see the section Releasing Space to the Operating System for details.

After table and index reorganization completes and twenty days have elapsed, the Tivoli Storage Manager server queries the database to determine whether additional reorganization of any tables or indices is needed.

If a table reorganization is in-flight at the end of the reorganization window, it is paused until the reorganization window starts the next day, when it is resumed.

 
Hide details for Obtaining Reorganization StatusObtaining Reorganization Status

Reorganization status can be obtained from the Tivoli Storage Manager server as follows:

  • For table reorganizations, when a reorganization of a table starts message ANR0293I is issued, and when the reorganization of the table has completed, message ANR0294I is issued.
  • For reorganizations of the indices for a table, when one starts message ANR0317I is issued, and when it completes, message ANR0318I is issued.
  • When a reorganization completes, either for the table or the indices on the table, runstats is run on the table. When runstats begins, message ANR0336I is issued, and when it completes, message ANR0337I is issued.
  • The server trace class TBREORG can be used to obtain a trace of reorganization activity.

Hide details for Miscellaneous DetailsMiscellaneous Details

  1. There are several new server options for tailoring reorganization processing. See the section Reorganization Options below for details.
  2. As the server pauses periodically after performing some reorganization activity, this allows the server to initiate a database backup, if necessary. If a database backup is currently running, no reorganization activity is started until the database backup completes. Thus having a current database backup takes precedence over reorganization activity. Once the database backup completes, and if the reorganization window has not passed, reorganization activity can continue. In addition, if needed, the server initiates a full database backup while an index reorganization is running.
  3. Even though by default table reorganization is enabled 24 hours a day, it is recommended that table reorganization occur during a scheduled reorganization window where there is low server activity.
  4. If index reorganization is enabled, ensure that there is very minimal server activity occurring while index reorganization is running. If reorganizations have not completed when server activity needs to resume, cancel the in-flight index reorganization. See Canceling Index Reorganizations for details.
  5. Tivoli Storage Manager enables table reorganization by default. If the server has table or index reorganization enabled, DB2 reorganization capabilities are disabled. If neither server table nor server index reorganization is enabled, DB2 initiated reorganization is enabled–this is not recommended.

Hide details for Reorganization OptionsReorganization Options

The following sections document the server options which pertain to reorganization. Before starting the server, study these options carefully. It is paramount that these options are set such that reorganization activity does not impact regular server operations. In other words, reorganization activity should not run when the server is under heavy backup, archive or internal processing (expiration, migration, reclamation, etc.) workloads.

ALLOWREORGTABLE

      Server option ALLOWREORGTABLE YES or ALLOWREORGTABLE NO are used to enable or disable server initiated table reorganization. If the option isn’t specified, it defaults to ALLOWREORGTABLE YES. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.

ALLOWREORGINDEX

      Server option ALLOWREORGINDEX YES or ALLOWREORGINDEX NO are used to enable or disable server initiated index reorganization. If the option isn’t specified, it defaults to ALLOWREORGINDEX NO. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.
      If index reorganization is enabled, set the server option DB_DB2_KEEPTABLELOCK. See

DB_DB2_KEEPTABLELOCK

      for details.

REORGBEGINTIME and REORGDURATION

      Table and index reorganization are intensive operations that require significant CPU resources and active and archival log resources. There might be times during the day that these reorganizations should not run, since they will impact server operations. Additionally, reorganization involves obtaining locks on the database that might interfere with server operations and cause performance issues, deadlocks or other such issues. Finally, if the server is halted during the reorganization window, index reorganization activity halts, but an in-flight table reorganization continues since table reorganization is an asynchronous activity from the server.

Define a daily window in which server initiated reorganization work is initiated. This window is defined by two server options, REORGBEGINTIME and REORGDURATION.

For information on REORGBEGINTIME see Techdoc 7021759.

For information on REORGDURATION see Techdoc 7021760.

A nuance of reorganization is that reorganization of a table or index might still be active when the defined window is complete. Under some conditions, a table reorganization will be paused when it runs for some period of time. If after being paused, the current time is outside of the window, it will remain paused until the next window the following day, and will resume then. However, index reorganizations cannot be paused; they run until completed, unless canceled–see Canceling Index Reorganizations below, for details. Consequently, no heavy server activity should be scheduled until at least an hour after the defined schedule window to allow any current index reorganization to complete.

DB_DB2_KEEPTABLELOCK

      Server option DB_DB2_KEEPTABLELOCK NO or DB_DB2_KEEPTABLELOCK YES are used to set the DB2 DB2_KEEPTABLELOCK variable. If the option isn’t specified, it defaults to DB_DB2_KEEPTABLELOCK YES which is consistent with server levels before 6.1.5.10 and 6.2.3.00. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.
      If index reorganization is not running, the server should run with DB_DB2_KEEPTABLELOCK YES, which is the default behavior.
      If index reorganization is running, the server should run with DB_DB2_KEEPTABLELOCK NO. Not doing so may result in the symptoms as documented in APAR IC77773.
      However, there are two cases in which running with DB_DB2_KEEPTABLELOCK NO might cause performance degradations during normal server activities (when index reorganization is not running):

      1. If running a 6.1 Windows server
      2. If running either a 6.1 or 6.2 server and doing large EXPORT NODE or IMPORT NODE operations.
      If performance problems are experienced, do the following for normal server operations (when index reorganization does not need to run):

      1. Halt the server.
      2. Set the server option
        ALLOWREORGINDEX NO
        which will prevent index reorganizations from running.
      3. Set the server option
        DB_DB2_KEEPTABLELOCK YES
        which restores the default server behavior.
      4. Restart the server.
      5. Allow the server to run until index reorganization needs to run–monthly should be sufficient.
    When index reorganization needs to run, do the following:

    1. Halt the server.
    2. Set the server option
      ALLOWREORGINDEX YES
      which will allow index reorganizations to run.
    3. Set the server option
      DB_DB2_KEEPTABLELOCK NO
      which prevents hitting APAR IC77773.
    4. Set the server options REORGBEGINTIME and REORGDURATION appropriately according to installation requirements.
    5. Restart the server.
    6. Once index reorganization completes, disable index reorganization as indicated previously.

Hide details for Canceling index reorganizationsCanceling index reorganizationsIf the server has initiated an index reorganization and it needs to be cancelled, DB2 commands can be used to cancel that process. This must be done if normal server operations need to start because index reorganization and normal server operations can deadlock. Since DB2 reorganization uses redo logging, work that is already completed is not lost. After a reorganization completes normally, the server initiates a DB2 runstats on that table to optimize server access to the data in that table. Canceling that reorganization means that runstats won’t be run.

To cancel an index reorganization through DB2:

  1. Determine the application ID of the reorganization process, by issuing the following commands in a DB2 Command Line Processor window:
    1. db2 connect to tsmdb1
    2. db2 get snapshot for all applications >application.out
  2. Examine the application.out file and find the “Most recent operation” entry like this:
    Most recent operation = ReorganizeIf that line isn’t there, look for an entry like this:
    Application name = db2reorg
  3. Scroll backwards until finding the “Application handle” entry. It will look like something like this:
    Application handle = NNNNN (where NNNNN is the actual application handle)
    Ensure that the correct application handle is found.
  4. Issue the following command in the DB2 Command Line Processor Window substituting in the actual application handle in for NNNNN:
    db2 “force application (NNNNN)”
  5. Because the nature of the command being canceled and that the DB2 FORCE APPLICATION command is asynchronous, it might take up to 30 minutes for the process to be canceled.
  6. To verify that it has been canceled, issue steps 1b and 2 again. If there is no “Most recent operation” of type Reorganize message displayed, it has been canceled.

Attention: The DB2 FORCE APPLICATION command, if issued against a system critical process, can cause server instability and possibly cause the DB2 database to crash. It is crucially important that only the application that is running the index reorganization be forced in this manner. 
Hide details for Releasing Space to the Operating SystemReleasing Space to the Operating System

With the DB2 fix packs shipped with server levels 6.1.5.10 and 6.2.3.00, database space can be safely released to the operating system.

After reorganization completes, free space should be consolidated near the end of the tablespaces and tables. To release space to the operating system, issue the DB2 ALTER TABLESPACE command. For information about databases that were formatted with server V6.1 (even if the server has been upgraded to server V6.2), see Releasing Space in DB2 9.5 Tablespaces. For information about databases that were formatted with server V6.2, see Releasing Space in DB2 9.7 Tablespaces.

Releasing Space in DB2 9.5 Tablespaces

Databases that were formatted by a V6.1 server have DB2 9.5 tablespaces. Upgrading a V6.1 server to V6.2 does not convert the tablespaces. DB2 9.5 has limited capacity for releasing space to the operating system. To free additional space, issue the following commands from a DB2 command-line window:
db2 connect to tsmdb1
db2 ALTER TABLESPACE USERSPACE1 REDUCE
db2 ALTER TABLESPACE IDXSPACE1 REDUCE
db2 ALTER TABLESPACE LARGESPACE1 REDUCE
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE

If issuing these commands do not free sufficient space, you can convert the DB2 9.5 tablespaces to DB2 9.7 tablespaces. For help with this conversion, contact Tivoli Storage Manager service. Be aware, however, that conversion is a time-intensive, multi step manual process that must be done while the server is halted. For this reason, tablespace conversion is strongly discouraged. 

Releasing Space in DB2 9.7 Tablespaces

Databases that are created with server V6.2 define DB2 9.7 tablespaces with reclaimable space enabled. Therefore, issuing ALTER TABLESPACE REDUCE on these tablespaces is much more likely to release free space to the operating system than DB2 9.5 tablespaces.

For DB2 9.7 tablespaces, the values in the reclaimable_space_enabled column of the following select is 1. The values are 0 for DB2 9.5 tablespaces.

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1))    where tbsp_id in (2,4,5,6)"

To release space from DB2 9.7 tablespaces, issue the following commands:

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE IDXSPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGESPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE MAX

These commands start separate processes in DB2 that release space. The commands can increase I/O activity, and might affect server performance. To minimize the impact, issue the next ALTER TABLESPACE command only after the previous command completes. You can monitor the progress of the command (for example, USERSPACE1) by examining the num_extents_left column of the MON_GET_EXTENT_MOVEMENT_STATUS procedure as follows:

db2 connect to tsmdb1
db2 set schema tsmdb1

db2 "select num_extents_left from ​
table(sysproc.MON_GET_EXTENT_MOVEMENT_STATUS(​

'USERSPACE1',-1)) " ​Depending on the tablespace reduction that you are monitoring, replace USERSPACE1 with IDXSPACE1, LARGESPACE1, or LARGEIDXSPACE1. ​
When num_extents_left changes to 0 or -1, the command is finished. ​

 
Hide details for Best practicesBest practices

Following best practices tends to give the best results. Here are some best practices associated with database health:

  1. Ensure that the machine on which the server is running is properly provisioned. This includes memory; available CPU resources; sufficient space in active log and archive log directories; regular database backups including the volume history; and that the database, logs, the database backups, and the volume history are all placed on high-performance reliable storage.
  2. Tivoli Storage Manager server initiated reorganization is the recommended setting.
  3. A reorganization window of a few hours a day will probably provide sufficient time for both server initiated reorganization activity and other DB2 maintenance to be performed once steady-state has been obtained. For databases that are quite fragmented, a larger reorganization window might be needed for some number of weeks until steady-state has been obtained. Steady-state is defined as reorganization having been run on all tables, and indices of tables, if index reorganization has been enabled.
  4. Table reorganization might be sufficient to databases for servers which are not running deduplication. Both table and index reorganization are recommended on servers running deduplication.

Hide details for Compressing indices on V6.2 serversCompressing indices on V6.2 servers6.2 servers ship DB2 9.7, which supports index compression. When a 6.2 server formats a new database, index compression is enabled on it. However, when a 6.1 server is upgraded to 6.2, index compression isn’t enabled. Server APAR IC78604 will automatically enable index compression on 6.2 servers. Note, that the actual compression of the indices occurs when reorganization runs on the indices of that table. Consequently, reorganization of indices must be enabled to compress the indices.

written by Bosse

Dec 02

Tivoli Storage Manager V6 active log files not in correct location

DB LOG, DB2 Comments Off on Tivoli Storage Manager V6 active log files not in correct location

Problem(Abstract)

The log files for a Tivoli Storage Manager V6 server might be being written to a location different from the ACTIVELOGDIR option that is set in the dsmserv.opt.

Resolving the problem

It is possible the active logs might be create/written to a directory other than what is specified in the dsmserv.opt file (ACTIVELOGDIR). If this is discovered, the DB2 configuration will need to be reviewed. Issue the following commands, as the instance owner:

db2 connect to tsmdb1
db2 get db cfg for tsmdb1

The “Path to log files = ” value should be reviewed to see if it matches with what the Tivoli Storage Manager has defined for the active log (‘ActiveLogDir’ from QUERY OPTION output). If these paths are not the same, the DB2 configuration can be updated by using the following steps:

1. Halt the Tivoli Storage Manager server
2. As the instance owner and from the instance home directory, issue the following commands:

db2start
db2 connect to tsmdb1
db2 update db cfg using NEWLOGPATH </path/to/active/logs>
db2 terminate
db2stop
db2start
db2 connect to tsmdb1; db2 get db cfg | grep “Path to log files”

3. If the path has been corrected, then stop DB2 (db2stop) and start the server normally

In order to find out what might have happened, the db2diag.log file should be reviewed. One reason this could happen is if DB2 is not able to access the log directory, DB2 can then update the log path to the default. Below is an example from a db2diag.log:

2010-11-08-13.52.25.668822-360 E38541A479 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2700
MESSAGE : ADM1814E The current log path “/tsmactivelog/NODE0000/” is invalid.

2010-11-08-13.52.25.749264-360 E39021A545 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2960
MESSAGE : ADM1811E DB2 will now switch to the default log path
“/home/tsminst1/NODE0000/SQL00001/SQLOGDIR/”

 

written by Bosse

Aug 22

New server option REORGDURATION

DB2, Server Command Comments Off on New server option REORGDURATION

Abstract

The REORGDURATION option specifies an interval during which server-initiated table or index reorganization can start.

Content

Schedule server-initiated reorganizations to start during periods when server activity is low. Use this option together with the REORGBEGINTIME option. The REORGBEGINTIME option specifies the earliest time that the server can start a reorganization.

You can update this server option without stopping and restarting the server by using the SETOPT command.
See SETOPT (Set a server option for dynamic update).

Syntax
Read syntax diagramSkip visual syntax diagram
>>-REORGDURation–nn——————————————-><

Parameters

nn
Specifies the number of hours during which a reorganization can start. The minimum value is 1, the maximum value is 24. The default value is 24.

Example

Specify an interval of four hours during which a reorganization can start.
reorgduration 4

 

written by Bosse