IBM Support

SELECT command V6.x - known problems

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:

http://publib.boulder.ibm.com/infocenter/tsminfo/v6/topic/com.ibm.itsm.srv.upgrd.doc/r_srv_upgrd_cmd_select.html



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
    For these tables, if a column name must be qualified by the table name then the use of a table name alias that is different from the actual table name must be used. The following example will fail in V6 but worked in V5:

    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


    • 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
    If you use the DB2 program to access the Tivoli Storage Manager database directly, do not issue the 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

  • Documentation additions and errors related to 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": The following command example is incorrect:
    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

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Server","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"6.1;6.2","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Product Synonym

TSM ITSM ADSM

Document Information

Modified date:
17 June 2018

UID

swg21380830

Page Feedback