Troubleshooting
Problem
The SELECT command in V6.x has known problems or inconsistencies with V5.x for some uses. Workarounds are given. Corrections to the documentation about the SELECT command are also included.
Symptom
SELECT commands do not work as expected in V6.x.
Resolving The Problem
If you are having problems with SELECT commands, first review the changes to the SELECT command that are caused by the use of DB2 as the database manager for the server, starting with V6.1:
These changes might affect SELECT commands that you used for V5 and earlier releases of the server. These changes are not considered to be program problems.
Several problems that were in the initial V6.1 release have been fixed in subsequent fix packs. You might need to install a fix pack.
The following known problems are expected to be fixed in future releases or fix packs for the server or are recognized as new limitations since the above document was published. Note that this is subject to change at the discretion of IBM.
- Problems in V6.1 and V6.2
SYSCAT.COLUMNS and SYSCAT.TABLES are not consistent with previous releases
The results for SELECT commands for SYSCAT.COLUMNS or SYSCAT.TABLES might not be consistent with what was previously reported for V5 servers.
Also the results for SELECT commands from these two SYSCAT tables might not accurately report what is available to be used for SELECT statement processing. One example of this are the results listed for TABNAME='NODESVIEW'.
Updates have been made in the 6.1.5.0 and 6.2.2.0 levels of the Tivoli Storage Manager server to make the SYSCAT tables as close as possible between the V5.x and the V6.x server.
Explicit specification of the schema causes errors
Explicitly specifying the schema for a table in a SELECT statement might result in errors or unexpected results. The only tables for which an explicit schema specification is needed are for tables such as SYSCAT.COLUMNS and SYSCAT.TABLES.
If a SELECT command is intended to be run against a typical Tivoli Storage Manager table, such as BACKUPS or LICENSES, do not specify a schema.
Explicit specification of the table name causes errors
Explicitly specifying the table name for a column in a SELECT statement might result in errors or unexpected results. The following table names are maintained for backward compatibility in the SELECT command, however, the table name in the FROM clause of the SELECT statement will be changed internally:
- FILESPACES
LICENSES
NODES
SERVER_GROUP
ACTIVITY_SUMMARY
PROFILES
SUBSCRIPTIONS
DOMAINS
VFSMAPPINGS
LICENSE_DETAILS
SELECT OCCUPANCY.NODE_NAME, NODES.TCP_NAME, NODES.TCP_ADDRESS, OCCUPANCY.TYPE, OCCUPANCY.FILESPACE_NAME, OCCUPANCY.STGPOOL_NAME, OCCUPANCY.NUM_FILES, OCCUPANCY.PHYSICAL_MB, OCCUPANCY.LOGICAL_MB, OCCUPANCY.FILESPACE_ID FROM NODES, OCCUPANCY WHERE NODES.NODE_NAME = OCCUPANCY.NODE_NAME
The following message is received from the above command:
ANR0162W Supplemental database diagnostic information: -1:42S22:-206
([IBM][CLI Driver][DB2/AIX64] SQL0206N "NODES.NODE_NAME" is not valid in the
context where it is used. SQLSTATE=42703
).
Change the actual table name to a table name alias and the command will work:
SELECT OCCUPANCY.NODE_NAME, NV.TCP_NAME, NV.TCP_ADDRESS, OCCUPANCY.TYPE, OCCUPANCY.FILESPACE_NAME, OCCUPANCY.STGPOOL_NAME, OCCUPANCY.NUM_FILES, OCCUPANCY.PHYSICAL_MB, OCCUPANCY.LOGICAL_MB, OCCUPANCY.FILESPACE_ID FROM NODES AS NV, OCCUPANCY WHERE NV.NODE_NAME = OCCUPANCY.NODE_NAME
This is a limitation in the processing of the SELECT command in the Tivoli Storage Manager server in V6.x. When needing to use a table name qualifier it is best practices to alias the table name in the FROM clause and use the alias name in the qualification for the columns. This limitation might also cause the ODBC client to not work correctly since the ODBC client will fully qualify the column name with both the table name and the schema name.
APAR IC76823 has been opened to remove this restriction.
Keyword ORDER BY does not work for some tables
The ORDER BY keyword results in the ANR2944E error message when used in a SELECT command for the following tables:
- AUDITOCC
CLIENTOPTS
CLOPTSETS
COLLOCGROUP
DATAMOVERS
DOMAINS
DRMEDIA
DRMMACHINE
DRMMACHINECHARS
DRMMACHINENODE
DRMMACHINERECINST
DRMMACHINERECMEDIA
DRMRECOVERYMEDIA
DRMSRPF
DRMSTANZA
DRMTRPF
GROUP_MEMBER
LICENSE_DETAILS
LOG
MGMTCLASSES
PROFILES
PATHS
PROCESSES
SERVER_GROUP
RESTORES
SAN
SCRIPT_NAMES
SUBSCRIPTIONS
SHREDSTATUS
STGSPACETRIGGER
VFSMAPPINGS
SUMMARY
Other keywords on the SELECT statement might also have the same problem. Updates have been made in the 6.1.5.0 and 6.2.2.0 levels of the Tivoli Storage Manager server to resolve this problem.
No results returned from a SELECT command
If a table is included in the SELECT statement from the above list and joined in that statement with a table that is not in the above list then the output of the SELECT command may show no results returned when there should have been results returned.
Updates have been made in the 6.1.5.0 and 6.2.2.0 levels of the Tivoli Storage Manager server to resolve this problem.
PROCESSES table is not available in SYSCAT.TABLES and SYSCAT.COLUMNS
The PROCESSES table is not available in syscat.tables and
syscat.columns. A SELECT command results in a message that no match is found. For example:
select tabname from syscat.tables where tabname='PROCESSES'
The following messages are received:
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
An example of a command to display the columns for the processes table, and the results:
select colname from columns where tabname='PROCESSES'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
Updates have been made in the 6.1.5.0 and 6.2.2.0 levels of the Tivoli Storage Manager server to resolve this problem.
Syntax errors may occur on SELECT statements that worked in V5.
The parsing of the SELECT statement in V6 is more strict in syntax checking than in the V5 Tivoli Storage Manager server. For example, the following statement worked in V5:
select * from backups where node_name='MYNODENAME' and type=file
but will receive the following error message in V6:
ANR0162W Supplemental database diagnostic information: -1:42S22:-206
([IBM][CLI Driver][DB2/AIX64] SQL0206N "FILE" is not valid in the context
where it is used. SQLSTATE=42703
).
Changing the SELECT statement to the following will work:
select * from backups where node_name='MYNODENAME' and type='FILE'
This is a restriction, the syntax checking in V6 is more strict than in V5.
A tilde character ~ may not display correctly in output from the SELECT command.
The tilde character has a special meaning internally to the Tivoli Storage Manager server in the processing of output from commands. APAR IC72130 fixes part of this problem if there is a tilde in the output from the SELECT command. If the tilde character is at the end of the line the Tivoli Storage Manager server will interpret it as a new line and the tilde will not be displayed in the output of the command.
RIGHT scalar function padding changed.
With a V5 server the RIGHT scalar function will pad to the left if the number of characters selected is greater than the string length. With a V6 server the padding occurs to the right. In V6 this function is handled by DB2 and DB2 (along with most data base vendors) document that the padding for this scalar function occurs on the right.
Comparison of a non-date field using a date format may be incorrect.
A V5 server supported non-standard processing of date and timestamps. This can result in queries that contain timestamp or date looking data to be processed incorrectly. For example:
select node_name from nodes where node_name = '2011-07-29'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
select node_name from nodes where node_name like '%011-07-29'
NODE_NAME
------------------------------------------------------------------
2011-07-29
This problem is caused because the '2011-07-29' in the where clause is converted internally to a complete timestamp which will not match on the node name. Changing this to a like expression will prevent that conversion from taking place.
Subtraction of timestamps results in a different output format.
With a V5 server the subtraction of timestamps resulted in a clearly readable value. With a V6 server there is a special format used by DB2. With V6.x DB2 returns a timestamp duration, for example:
11327.000000
which is in the format
yyyymmddhhmmss.nnnnnn
where yyyy, mm, dd, hh, mm, ss, and nnnnnn represent, respectively, the
number of years, months, days, hours, minutes, seconds, and fractional
seconds.
It is possible to manipulate the SELECT statement to display a value that is similar to how it was displayed in a V5 server. For example:
SELECT end_time-start_time as "TIMESTAMP_DURATION",
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "DURATION"
from tsmdb1.summary where entity like 'NODENAME'
TIMESTAMP_DURATION DURATION
---------------------- ----------
1.000000 0 00:00:01
1.000000 0 00:00:01
152.000000 0 00:01:52
3 record(s) selected.
- Problem in V6.2 only
- Documentation additions and errors related to the SELECT command
Restrictions on SELECT commands for nodes information
Do not issue the command
SELECT * FROM NODES
, either from the administrative command line or from the Administration Center command-line interface. Do not reference the following columns in any
SELECT column_name FROM NODES
command:
- COLLOCGROUP_NAME
PROXY_TARGET
PROXY_AGENT
EMAIL_ADDRESS
SELECT * FROM NODESVIEW
command, or reference any of the preceding columns in a SELECT column_name FROM NODESVIEW
operation. Performing either action causes the DB2 program to crash.
This problem has been fixed in the 6.2.1.0 level of the Tivoli Storage Manager server code. At a level of 6.2.1.0 or higher it is now safe to include these columns in the SELECT command
The items listed in this section apply to the V6.1 and V6.2 documentation.
Time stamp difference calculations give results in decimal form
In V5 and earlier versions of the server, time stamp difference calculations gave the result in the form of a time stamp. For V6.1 and later servers, the result of a time stamp difference calculation is in decimal form, in units of seconds.
Implicit cast for time stamp is not accepted
V5 and earlier servers accepted an implicit cast of time stamps in a SELECT command. For example:
select * from actlog where date_time > {ts '2010-02-10 00:00:00'}
V6 and later servers do not accept this format, and do not require the cast operation when a SELECT command includes a database column that contains time stamps. The database manager recognizes the type of data in the column. For example, you can use this command:
select * from actlog where date_time > '2010-02-10 00:00:00'
Incorrect example for SELECT command for activity log information
The following topic contains an incorrect example, under the heading "SELECT statements for time calculation":
- V6.1:
V6.2:
select * from actlog where second(current_time-date_time) <= 60
The command produces the following errors:
ANR0162W Supplemental database diagnostic information:
-1:42819:-402 ([IBM][CLI Driver][DB2/NT64] SQL0402N
The data type of an operand of an arithmetic function or
operation "-" is not numeric. SQLSTATE=42819).
ANR0516E SQL processing for statement select * from actlog where
second (current_time-date_time ) <= 60 failed.
ANS8001I Return code 3.
The example incorrectly uses current_time when current_timestamp should be used. Also, the intent of the command is to provide the actlog entries from the last 60 seconds. To achieve this result, you can use the DB2 function, TIMESTAMPDIFF.
To list the actlog entries for the last 60 seconds, use the command:
select * from actlog where TIMESTAMPDIFF(2,CHAR(current_timestamp-date_time)) <= 60
To list the actlog entries for the last 60 minutes, use the command:
select * from actlog where TIMESTAMPDIFF(4,CHAR(current_timestamp-date_time)) <= 60
For more information on the DB2 TIMESTAMPDIFF function, see the following topic in the DB2 information center:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000861.html
Related Information
Product Synonym
TSM ITSM ADSM
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21380830